Assignment 7-1 SQL Lab 3: SQL Queries II Solved

The purpose of this lab is to gain SQL experience using Oracle. You will get practice creating SQL queries from an established database given to you to load into your own schema in Oracle.

ACTION ITEMS

Initial Setup – Read & Complete First

The diagram below shows a relational schema made up of 6 tables with all primary keys underlined. Please make 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.









• Click HERE to download an SQL script file called myCompany.sql to your PC or the virtual desktop. This file contains the SQL commands needed to create the tables above and add data to them in Oracle using SQL Developer.

• Transfer the file and/or its contents to the virtual desktop and execute it’s content two (2) times in a row in SQL Developer to see that all errors go away with the second running of the file.

• Go through the file and make sure you understand every line of code in it before you proceed. You will not need to execute this file again for this lab. The tables and data are stored permanently in your database schema in Oracle.



Part 1: Write SQL Queries into lab3.sql script file



Script File


Create a new lab3.sql script file using Notepad or Notepad++ on your VD. The first two commands in this file should be (you should only have these in your lab3.sql file once):



set echo on;

set serveroutput on;



Follow the format given to you in the myCompany.sql file above. Each SQL query should have a comment before it stating the question number in it. For example, let’s say the first question asks you to display all records in the line table and the second question asks you to display all records in the vendor table. The content you should add to the lab3.sql file is as follows:



/* 1 */

select * from line;



/* 2 */

select * from vendor;



You will use this file to insert your comments and SQL query commands for the 15 questions detailed below. NOTE: You should test your commands first in SQL Developer before placing them into your lab3.sql file to make sure they give you the output you desire.



P. S. Your queries should be generic enough to return proper results EVEN if the data inside the database tables 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 the PRODUCT table for the corresponding products; instead, your query should be written using a join between the two tables with a WHERE clause for TN or something similar.



Questions (0-30 points)


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, 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.



12. 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. You should use the CREATE OR REPLACE VIEW VENDOR_PRODUCTS_TOTALS AS SELECT … command for this question.



13. Execute a select * from product; statement. 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. Issue another select * from product; statement after the update. Do a rollback.



14. Execute a select * from product; statement. Create an SQL update statement which doubles the P_DISCOUNT for all products provided by vendors in TN or FL. Issue another select * from product; statement after the update. Do a rollback.



15. Execute a select * from LINE; statement. Create an SQL delete statement to delete invoice lines that include 2 or more units of product code ‘23109-HB’. Issue another select * from LINE; statement after the delete. Do a rollback.



Part 2: Execute Your lab3.sql script File and Save Results in a Word Document called lab3.docx


From within SQL Developer, execute the content of your lab3.sql script file (which should now include all commands for the 15 questions above. Copy and paste the output into a Word document called lab3.docx with your name, date, assignment number, and class in the header. The output should include a comment with each question clearly marked, the SQL command, and its corresponding output immediately after. For example, the content of my Word document with the commands listed above would be the following (I changed the font size to 8 and the font type to Courier New to get the columns to line up):









set serveroutput on

/* 1 */

select * from line

INV_NUMBER LINE_NUMBER P_CODE LINE_UNITS LINE_PRICE

---------- ----------- ---------- ---------- ----------

1001 1 13-Q2/P2 1 14.99

1001 2 23109-HB 1 9.95

1002 1 54778-2T 2 4.99

1003 1 2238/QPD 1 38.95

1003 2 1546-QQ2 1 39.95

1003 3 13-Q2/P2 5 14.99

1004 1 54778-2T 3 4.99

1004 2 23109-HB 2 9.95

1005 1 PVC23DRT 12 5.87

1006 1 SM-18277 3 6.99

1006 2 2232/QTY 1 109.92

1006 3 23109-HB 1 9.95

1006 4 89-WRE-Q 1 256.99

1007 1 13-Q2/P2 2 14.99

1007 2 54778-2T 1 4.99

1008 1 PVC23DRT 5 5.87

1008 2 WR3/TT3 3 119.95

1008 3 23109-HB 1 9.95



18 rows selected



/* 2 */

select * from vendor

V_CODE V_NAME V_CONTACT V_AREACODE V_PHONE V_STATE V_ORDER

---------- ----------------------------------- --------------- ---------- -------- ------- ------

21225 Bryson, Inc. Smithson 615 223-3234 TN Y

21226 SuperLoo, Inc. Flushing 904 215-8995 FL N

21231 D&E Supply Singh 615 228-3245 TN Y

21344 Gomez Bros. Ortega 615 889-2546 KY N

22567 Dome Supply Smith 901 678-1419 GA N

23119 Randsets Ltd. Anderson 901 678-3998 GA Y

24004 Brackman Bros. Browning 615 228-1410 TN N

24288 ORDVA, Inc. Hakford 615 898-1234 TN Y

25443 B&K, Inc. Smith 904 227-0093 FL N

25501 Damal Supplies Smythe 615 890-3529 TN N 25595 Rubicon Systems Orton 904 456-0092 FL Y



11 rows selected