Ask PL-SQL Expert

CONSIDER THE FOLLOWING SET OF DATABASE TABLES.

The diagram shows a database made up of 6 tables with all primary keys underlined. Please take note of foreign keys (most of them carry the same names as the corresponding primary keys they reference):

1933_Creating the database and inserting data.png

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.

- In addition to the above six tables, the script creates two tables V (default vendor table data) and P (default product table data). The only purpose for tables V and P is to demonstrate bulk insert - i.e. how to copy data from one table to another. E.g., to copy all records from table V to Vendor we issue the following: INSERT INTO VENDOR (SELECT * FROM V).

- Notice how I didn't include a FOREIGN KEY constraint in EMPLOYEE to reference itself (in order to implement the recursive relationship manages) because we still have not yet created the table. This has to be done separately as shown below AFTER creating the EMPLOYEE table: ALTER TABLE EMPLOYEE ADD FOREIGN KEY (EMP_MGR) REFERENCES EMPLOYEE;

I. Part 1: Understanding the SQL script, creating the database and inserting data

1. Double-click on the myCompany.SQL script file which will automatically put a copy of the script in the command input sub-window of SQL Developer. [or go into SQL Developer and do a File -> open to open it that way if necessary]

2. Go through the file and make sure you understand every single line of code in it before you proceed.

3. When done, press the Run Script (NOT the Run Statement) button in SQL Developer and you should notice the output of all commands appear sequentially in the output area.

II. Part 2: Writing SQL SELECT statements (one SQL statement per problem):

1. Display all product information for products that contain the string ‘saw' in their description.

2. Display all product information for products currently selling for less than $20.00 and which have more than 100 units in stock (recorded in attribute P_QOH or quantity on hand).

3. Display all product information for products currently selling for less than $20.00 or which have more than 100 units in stock (recorded in attribute P_QOH or quantity on hand).

4. Display the product code, description and price for products provided by vendor 21344.

5. Display the product code, description, price and price after discount (a computed column that uses the P_PRICE and P_DISCOUNT (a percent discount) --- name the computed column NewPrice) for products for which no vendor is specified in table PRODUCT.

6. Display the product code, product description and vendor name for products provided by vendors located in the state of TN.

7. Display the full names of employees who manage other employees, ordered by last name.

8. Display the count of distinct products ordered so far by customers (i.e. exist in table LINE).

9. For every manager, display the manager employee code along with the total number of employees s/he manages.

10. Display the product description, the full customer name and the customer balance for products ordered by customers having a customer balance between $100.00 and $500.00 (inclusive of both). Order by customer last name, then first name, then initial.

11. For every invoice, display the invoice number and the total dollar amount for all products purchased in the invoice, ordered by invoice number in descending order.

12. For every invoice, display the invoice number, invoice date and the total dollar amount for all products purchased in the invoice, ordered by invoice number in descending order and then by invoice date in ascending order.

III. Part 3: Creating an SQL VIEW:

13. Create an SQL view called VENDOR_PRODUCTS_TOTALS that shows the vendor name, vendor code and the total number of products provided by the vendor but only for vendors providing two or more products. When done, issue a select * from VENDOR_PRODUCTS_TOTALS statement to display the contents of view VENDOR_PRODUCTS_TOTALS and include them in your report.

IV. Part 4: Writing UPDATE and DELETE SQL statements:

PS: if you ever need to UNDO changes made to the database by an UPDATE or DELETE statement, issue command rollback in the command input sub-window of SQL Developer.

14. Start by issuing a select * from product statement. Copy the results into your report. Then, create and run an SQL update statement which changes the P_DISCOUNT to 0.03 percent for all products that currently have a discount of 0. Finally issue a select * from product statement after the update and copy the results into your report. This will show you the change produced by your update statement.

15. Start by issuing a select * from product statement. Copy the results into your report. Then, create an SQL update statement which doubles the P_DISCOUNT for all products provided by vendors in TN or FL. Finally issue a select * from product statement after the update and copy the results into your report.

16. Start by issuing a select * from LINE statement. Copy the results into your report. Then, create an SQL delete statement to delete invoice lines that include 2 or more units of product code ‘23109-HB'. Finally issue a select * from LINE statement after the delete and copy the results into your report. This should show you the change produced by your delete statement.

PL-SQL, Programming

  • Category:- PL-SQL
  • Reference No.:- M9460013
  • Price:- $90

Guranteed 48 Hours Delivery, In Price:- $90

Have any Question?


Related Questions in PL-SQL

Complete the following tasksin microsoft access create the

Complete the following tasks: In Microsoft Access, create the database and tables that you identified in W3 Assignment 2. In Microsoft Word, write the SQL statements to create the database and tables. Write SQL statement ...

Purpose of the assessment with ulo mapping the purpose of

Purpose of the assessment (with ULO Mapping) The purpose of this assignment is to develop skills in managing data in databases and to gain understanding of data model development and implementation using a commercially a ...

Continuing the project you have worked on in weeks 1-4 in

Continuing the project you have worked on in Weeks 1-4, in this final week, complete the following tasks: Refine your database and SQL statements by incorporating your instructor's feedback. Verify that the database comp ...

For this assignment you will be provided a database backup

For this assignment, you will be provided a database backup for a database called FinanceDB. You will have to restore this backup to your own version of SQL Server. All of the questions in this assignment relate to the F ...

Assignment - queries functions and triggersaimthe aims of

Assignment - Queries, Functions and Triggers Aim The aims of this assignment are to: formulate SQL queries; populate an RDBMS with a real dataset, and analyse the data; design test data for testing SQL queries; create SQ ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

Why might a bank avoid the use of interest rate swaps even

Why might a bank avoid the use of interest rate swaps, even when the institution is exposed to significant interest rate

Describe the difference between zero coupon bonds and

Describe the difference between zero coupon bonds and coupon bonds. Under what conditions will a coupon bond sell at a p

Compute the present value of an annuity of 880 per year

Compute the present value of an annuity of $ 880 per year for 16 years, given a discount rate of 6 percent per annum. As

Compute the present value of an 1150 payment made in ten

Compute the present value of an $1,150 payment made in ten years when the discount rate is 12 percent. (Do not round int

Compute the present value of an annuity of 699 per year

Compute the present value of an annuity of $ 699 per year for 19 years, given a discount rate of 6 percent per annum. As