/* CREATE_P_V.SQL */ /* Introduction to SQL */ /* Script file for ORACLE DBMS */ /* This script file creates the following tables: */ /* V - default vendor table data */ /* P - default product table data */ /* and loads the default data rows */ /*DROP TABLE P; DROP TABLE V; */ CREATE TABLE VENDOR ( V_CODE INTEGER PRIMARY KEY, V_NAME VARCHAR(35) NOT NULL, V_CONTACT VARCHAR(15) NOT NULL, V_AREACODE CHAR(3) NOT NULL, V_PHONE CHAR(8) NOT NULL, V_STATE CHAR(2) NOT NULL, V_ORDER CHAR(1) NOT NULL); CREATE TABLE P ( P_CODE VARCHAR(10) PRIMARY KEY, P_DESCRIPT VARCHAR(35) NOT NULL, P_INDATE DATE NOT NULL, P_QOH INT NOT NULL, P_MIN INT NOT NULL, P_PRICE DECIMAL(8,2) NOT NULL, P_DISCOUNT DECIMAL(5,2) NOT NULL, V_CODE INT); /* Loading data rows */ /* Turn Escape character on */ /* Default escape character "\" */ /* Used to enter special characters (&) */ --SET ESCAPE ON; /* V rows */ INSERT INTO VENDOR VALUES(21225,'Bryson, Inc.' ,'Smithson','615','223-3234','TN','Y'); INSERT INTO VENDOR VALUES(21226,'SuperLoo, Inc.' ,'Flushing','904','215-8995','FL','N'); INSERT INTO VENDOR VALUES(21231,'D\&E Supply' ,'Singh' ,'615','228-3245','TN','Y'); INSERT INTO VENDOR VALUES(21344,'Gomez Bros.' ,'Ortega' ,'615','889-2546','KY','N'); INSERT INTO VENDOR VALUES(22567,'Dome Supply' ,'Smith' ,'901','678-1419','GA','N'); INSERT INTO VENDOR VALUES(23119,'Randsets Ltd.' ,'Anderson','901','678-3998','GA','Y'); INSERT INTO VENDOR VALUES(24004,'Brackman Bros.' ,'Browning','615','228-1410','TN','N'); INSERT INTO VENDOR VALUES(24288,'ORDVA, Inc.' ,'Hakford' ,'615','898-1234','TN','Y'); INSERT INTO VENDOR VALUES(25443,'B\&K, Inc.' ,'Smith' ,'904','227-0093','FL','N'); INSERT INTO VENDOR VALUES(25501,'Damal Supplies' ,'Smythe' ,'615','890-3529','TN','N'); INSERT INTO VENDOR VALUES(25595,'Rubicon Systems' ,'Orton' ,'904','456-0092','FL','Y'); /* P rows */ INSERT INTO P VALUES('11QER/31','Power painter, 15 psi., 3-nozzle' ,'03-NOV-2011', 8, 5,109.99,0.00,25595); INSERT INTO P VALUES('13-Q2/P2','7.25-in. pwr. saw blade' ,'13-DEC-2011', 32, 15, 14.99,0.05,21344); INSERT INTO P VALUES('14-Q1/L3','9.00-in. pwr. saw blade' ,'13-NOV-2011', 18, 12, 17.49,0.00,21344); INSERT INTO P VALUES('1546-QQ2','Hrd. cloth, 1/4-in., 2x50' ,'15-JAN-2012', 15, 8, 39.95,0.00,23119); INSERT INTO P VALUES('1558-QW1','Hrd. cloth, 1/2-in., 3x50' ,'15-JAN-2012', 23, 5, 43.99,0.00,23119); INSERT INTO P VALUES('2232/QTY','B\&D jigsaw, 12-in. blade' ,'30-DEC-2011', 8, 5,109.92,0.05,24288); INSERT INTO P VALUES('2232/QWE','B\&D jigsaw, 8-in. blade' ,'24-DEC-2011', 6, 5, 99.87,0.05,24288); INSERT INTO P VALUES('2238/QPD','B\&D cordless drill, 1/2-in.' ,'20-JAN-2012', 12, 5, 38.95,0.05,25595); INSERT INTO P VALUES('23109-HB','Claw hammer' ,'20-JAN-2012', 23, 10, 9.95,0.10,21225); INSERT INTO P VALUES('23114-AA','Sledge hammer, 12 lb.' ,'02-JAN-2012', 8, 5, 14.40,0.05,NULL); INSERT INTO P VALUES('54778-2T','Rat-tail file, 1/8-in. fine' ,'15-DEC-2011', 43, 20, 4.99,0.00,21344); INSERT INTO P VALUES('89-WRE-Q','Hicut chain saw, 16 in.' ,'07-FEB-2012', 11, 5,256.99,0.05,24288); INSERT INTO P VALUES('PVC23DRT','PVC pipe, 3.5-in., 8-ft' ,'20-FEB-2012',188, 75, 5.87,0.00,NULL); INSERT INTO P VALUES('SM-18277','1.25-in. metal screw, 25' ,'01-MAR-2012',172, 75, 6.99,0.00,21225); INSERT INTO P VALUES('SW-23116','2.5-in. wd. screw, 50' ,'24-FEB-2012',237,100, 8.45,0.00,21231); INSERT INTO P VALUES('WR3/TT3' ,'Steel matting, 4''x8''x1/6", .5" mesh','17-JAN-2012', 18, 5,119.95,0.10,25595); --SET ESCAPE '/';

-Make sure not to delete any working query as you answer more questions.

-Once all your queries are correct and returning results, set SQL to send responses to a file.

Write the SQL queries to retrieve the following:

1) The description, quantity on hold(P_QOH), the minimum quantity and the price for each product whose price is below $10.00

2) The description, quantity on hold(P_QOH), the minimum quantity, the price and the date for each product whose inventory date (P_INDATE) is on or after January 20, 2012

3) The description, quantity on hold(P_QOH), the unit price and total price of the inventory, as TOTValue for each product.

4) List of all products, the date they were received and the warranty expiration date (90 days from receiving the product) as ExpDate

5) List all the products supplied by vendor 21344 or 24288

6) The description, inventory date (P_INDATE), the price and the date for each product whose inventory date (P_INDATE) is after January 15, 2012 and price is less than $50

7) The description, inventory date (P_INDATE), the price and the date for each product whose inventory date (P_INDATE) is after January 15, 2012 and price is less than $50 OR the vendor number is 24288

8) Retrieve all information on products not supplied by vendor 21344

9) Retrieve all information on products whose price is between $50 and $100.

10) List all products that do not have a vendor assigned to them.

11) List the description and prices of all products ordering them from higher to lower.

12) Retrieve the code, description and price of the most and least expensive product.

13) Retrieve the total customer balance from the table customer

14) Retrieve the product codes, descriptions and the names of the vendors that supply them

15) For each vendor , list all the products they supply