Consider the following set of database tables (same tables from Assignment 6-1). Please take note of foreign keys (most of them carry the same names as the corresponding primary keys they reference): CUS_CODE in INVOICE, INV_NUMBER & P_CODE in LINE, and V_CODE in PRODUCT. The only exception to the naming convention is the EMP_MGR foreign key in EMPLOYEE which references the EMPLOYEE table in a recursive relationship.

The SQL script file myCompany.SQL (same one from Assignment 6-1) creates the tables below and inserts data into them. You WILL need to rerun the script again for this assignment since the contents of some of the files were altered during assignment 6-1. You should also run the script again before final submission of this assignment. Click HERE if you need the script again.

PS: As a reminder, your SQL statements should be generic enough to produce proper results EVEN if the data inside the database changes. For example, if I ask you to display the products provided by vendors located in TN, you can't manually extract the V_CODE for vendors in TN and use the results to search table PRODUCT for the corresponding products; instead, your query should be written using a join between the two tables or something similar.

I. Part 1: Writing More Complex SQL Queries: Save the SQL code for the following queries a single script file called Part1.sql. In addition, include each query along with its output in your report.

1. Create an SQL query that uses set operations to display the union of the last names in table CUSTOMER and the last names in table EMPLOYEE. Do not include duplicates in the output.

2. Create an SQL query that uses set operations to display the union of the last names in table CUSTOMER and the last names in table EMPLOYEE. This time, include duplicates in the output.

3. Create an SQL query that USES AN UNCORRELATED SUBQUERY1 AND NO JOINS2 to display the descriptions for products provided by a vendor in area code 615.

4. Create an SQL query that USES A CORRELATED SUBQUERY AND NO JOINS to display the descriptions for products provided by a vendor in area code 615.

II. Part 2: Creating and Executing a Stored Procedure: Save the SQL code for this stored procedure in a separate script file called Part2.sql. In addition, include the test steps - see below - along with their output in your report. Finally, answer the question below in your report.

Create an SQL Stored Procedure called prc_inv_delete that takes an invoice number as a parameter and deletes the invoice from table INVOICE. TEST STEPS: To test your procedure, do the following:

a) Run the following two SQL commands: SELECT * from INVOICE WHERE INV_NUMBER IN (1001, 1008); SELECT * from LINE WHERE INV_NUMBER IN (1001, 1008);

b) Execute your procedure to delete invoices 1001 and 1008.

c) Run the same SQL commands again: SELECT * from INVOICE WHERE INV_NUMBER IN (1001, 1008); SELECT * from LINE WHERE INV_NUMBER IN (1001, 1008);

d) Undo all changes made by issuing a rollback command.

Answer this question: Did the corresponding lines for invoices 1001 and 1008 in table LINE get deleted automatically? Can you explain why?

Posted Date: 2/15/2013 5:42:17 AM | Location : United States

Ask an Expert

Related Discussions:- An active database in pl-sql, Assignment Help, Ask Question on An active database in pl-sql, Get Answer, Expert's Help, An active database in pl-sql Discussions

Write discussion on An active database in pl-sql
Your posts are moderated

Write your message here..

Related Questions

Use bulk binds - improve performance of application, Use Bulk Binds If... Use Bulk Binds If SQL statements execute inside a loop using the collection elements as bind variables, context switching between the PL/SQL & SQL engines can slow down the ex

Joining in sql, Joining in SQL Joining IS_CALLED and IS_ENROLLED_ON in... Joining in SQL Joining IS_CALLED and IS_ENROLLED_ON in SQL SELECT * FROM IS_CALLED NATURAL JOIN IS_ENROLLED_ON This is an example of an SQL table expression. I have been

Features of pl/sql, Main features of PL/SQL A good way to get familiar ... Main features of PL/SQL A good way to get familiar with PL/SQL is to look at a sample program. The below program processes an order for tennis rackets. At first, it declares a

S, Question 1. Update stock levels when the order is cancelled At times, c... Question 1. Update stock levels when the order is cancelled At times, customers make mistakes in submitting their orders and call to cancel the order. Brewbean’s wants to create a

Solve the business problems using sql, Use the MASCOT tables CREDITRS, PORD... Use the MASCOT tables CREDITRS, PORDS and PAYMENTS to write SQL queries to solve the following business problems. These tables / data are available to you via the USQ Oracle server

Write a stored procedure, a. Create a table odetails_new. It has all the a... a. Create a table odetails_new. It has all the attributes of odetails and an additional column called cost, whose values are the product of the quantity and price of the part bein

Do you know anyone that can do this type of coding or not?, Task 2 [12 mark... Task 2 [12 marks] Write the package body for the following package specification (the detailed description of each function and procedure is provided in the appendix below). Place

Forward declarations - subprograms, Forward Declarations The PL/SQL ne... Forward Declarations The PL/SQL needs that you declare an identifier before using it. And hence, you should declare a subprogram before calling it. For illustration, the decla

Expressions - syntax, Expressions   An expression is a randomly comple... Expressions   An expression is a randomly complex combination of the constants, variables, literals, operators, & function calls. The simplest expression is the single variabl

Set operators - sql operators, Set Operators The Set operators combine... Set Operators The Set operators combine the results of the two queries into one result. The INTERSECT returns all the distinct rows selected by both queries. The MINUS returns