- Script file used to create five tables. CREATE TABLE Customer( CUS_CODE NUMBER
ID: 3910093 • Letter: #
Question
- Script file used to create five tables.
CREATE TABLE Customer( CUS_CODE NUMBER(5), CUS_LNAME VARCHAR2(20), CUS_FNAME VARCHAR2(20), CUS_INITIAL CHAR(1), CUS_AREACODE CHAR(3), CUS_PHONE CHAR(8), CUS_BALANCE NUMBER(12,2), CONSTRAINT CUST_PK PRIMARY KEY(CUS_CODE));
CREATE TABLE Vendor( V_CODE CHAR(5), V_NAME VARCHAR2(20), V_CONTACT VARCHAR2(20), V_AREACODE CHAR(3), V_PHONE CHAR(8), V_STATE CHAR(2), V_ORDER CHAR(1), CONSTRAINT Vend_pk PRIMARY KEY(V_CODE));
CREATE TABLE Product( P_CODE VARCHAR2(20), P_DESCRIPT VARCHAR2(35), P_INDATE DATE, P_ONHAND NUMBER(12,2), P_PRICE NUMBER(12,2), P_DISCOUNT NUMBER(12,2), V_CODE CHAR(5), CONSTRAINT Product_Pk PRIMARY KEY(P_CODE), CONSTRAINT pRODUCT_vEND_fk FOREIGN KEY(V_CODE) REFERENCES VENDOR(V_CODE));
CREATE TABLE Invoice( INV_NUMBER VARCHAR2(4), CUS_CODE NUMBER(5), INV_DATE DATE, CONSTRAINT Invoice_Pk PRIMARY KEY (INV_NUMBER), CONSTRAINT Inv_Cust_Code_FK FOREIGN KEY (CUS_CODE) REFERENCES CUSTOMER);
CREATE TABLE Line( INV_NUMBER varchar2(4), LINE_NUMBER Number(3), P_CODE varchar2(20), LINE_UNITS number(15,2), LINE_PRICE number(12,2), PRIMARY KEY (INV_NUMBER, LINE_NUMBER), CONSTRAINT LINE_INV_INV_NUM_FK FOREIGN KEY (INV_NUMBER) REFERENCES INVOICE, CONSTRAINT LINE_Prod_FK FOREIGN KEY(P_CODE) REFERENCES PRODUCT);
INSERT INTO CUSTOMER VALUES(10010, 'Ramas', 'Alfred', 'A', '615', '844-2573', 0.00); INSERT INTO CUSTOMER VALUES(10011, 'Dunne', 'Leona', 'K', '713', '894-1238', 0.00); INSERT INTO CUSTOMER VALUES(10012, 'Smith', 'Kathy', 'W', '615', '894-2285', 345.86); INSERT INTO CUSTOMER VALUES(10013, 'Olowski', 'Paul', 'F', '615', '894-2180', 536.75); INSERT INTO CUSTOMER VALUES(10014, 'Orlando', 'Myron', ' ', '615', '222-1672', 0.00); INSERT INTO CUSTOMER VALUES(10015, 'O''Brian', 'Amy', 'B', '713', '442-3381', 0.00); INSERT INTO CUSTOMER VALUES(10016, 'Brown', 'James', 'G', '615', '297-1228', 221.19); INSERT INTO CUSTOMER VALUES(10017, 'Williams', 'George', ' ', '615', '290-2556', 768.93); INSERT INTO CUSTOMER VALUES(10018, 'Farriss', 'Anne', 'G', '713', '382-7185', 216.55); INSERT INTO CUSTOMER VALUES(10019, 'Smith', 'Olette', 'K', '615', '297-3809', 0.00);
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 and 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 and 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 Sis.', 'Orton', '904', '456-0092', 'FL', 'Y');
INSERT INTO PRODUCT VALUES('11QER/31', 'Power painter, 15 psi., 3-nozzle', '02-Jun-99', 8, 109.99, 0.00, '21225'); INSERT INTO PRODUCT VALUES('13-Q2/P2', '7.25-in. pwr. saw blade', '12-jul-99', 32, 14.99, 0.05,'21225'); INSERT INTO PRODUCT VALUES('14-Q1/L3', '9.00-in. pwr. saw blade', '12-jun-99', 18, 17.49, 0.00, '21225'); INSERT INTO PRODUCT VALUES('1546-QQ2', 'Hrd. cloth, 1/4-in., 2x50', '14-aug-99', 15, 39.95, 0.00, '21225'); INSERT INTO PRODUCT VALUES('1558-QW1', 'Hrd. cloth, 1/2-in., 3x50', '14-aug-99', 23, 43.99, 0.00, '21226'); INSERT INTO PRODUCT VALUES('2232/QTY', 'BandD jigsaw, 12-in. blade', '29-jul-99', 8, 109.92, 0.05, '21226'); INSERT INTO PRODUCT VALUES('2232/QWE', 'BandD jigsaw, 8-in. blade', '23-jul-99', 6, 99.87, 0.05,'21231'); INSERT INTO PRODUCT VALUES('2238/QPD', 'BandD cordless drill, 1/2-in.', '19-aug-99', 12, 38.95, 0.05,'21231'); INSERT INTO PRODUCT VALUES('23109-HB', 'Claw hammer', '19-aug-99', 23, 9.95, 0.10,'21344'); INSERT INTO PRODUCT VALUES('23114-AA', 'Sledge hammer, 12 lb.', '01-aug-99', 8, 14.40,0.05,'22567'); INSERT INTO PRODUCT VALUES('54778-2T', 'Rat-tail file, 1/8-in. fine', '14-jul-99', 43, 4.99, 0.00,'22567'); INSERT INTO PRODUCT VALUES('89-WRE-Q', 'Hicut chain saw, 16-in.', '6-sep-99', 11, 256.99, 0.05,'23119'); INSERT INTO PRODUCT VALUES('PVC23DRT', 'PVC pipe, 3.5-in., 8-ft', '19-sep-99', 188, 5.87, 0.00,'24004'); INSERT INTO PRODUCT VALUES('SM-18277', '1.25-in. metal screw, 25', '28-sep-99', 172, 6.99, 0.00,'24288'); INSERT INTO PRODUCT VALUES('SW-23116', '2.5-in. wd. screw, 50', '23-sep-99', 237, 8.45, 0.00,'25443'); INSERT INTO PRODUCT VALUES('WR3/TT3', 'Steel matting, 4''x8''x1/6", .5" mesh', '16-aug-99', 18, 119.95, 0.10,'25501');
INSERT INTO INVOICE VALUES('1001', '10014', '18-JAN-2008'); INSERT INTO INVOICE VALUES('1002', '10011', '18-JAN-2008'); INSERT INTO INVOICE VALUES('1003', '10012', '18-JAN-2008'); INSERT INTO INVOICE VALUES('1004', '10011', '19-JAN-2008'); INSERT INTO INVOICE VALUES('1005', '10018', '19-JAN-2008'); INSERT INTO INVOICE VALUES('1006', '10014', '19-JAN-2008'); INSERT INTO INVOICE VALUES('1007', '10015', '19-JAN-2008'); INSERT INTO INVOICE VALUES('1008', '10011', '19-JAN-2008');
insert into LINE values('1001', '1', '13-Q2/P2', 1, 14.99); insert into LINE values('1001', '2', '23109-HB', 1, 9.95); insert into LINE values('1002', '1', '54778-2T', 2, 4.99); insert into LINE values('1003', '1', '2238/QPD', 1, 38.95); insert into LINE values('1003', '2', '1546-QQ2', 1, 39.95); insert into LINE values('1003', '3', '13-Q2/P2', 5, 14.99); insert into LINE values('1004', '1', '54778-2T', 3, 4.99); insert into LINE values('1004', '2', '23109-HB', 2, 9.95); insert into LINE values('1005', '1', 'PVC23DRT', 12, 5.87); insert into LINE values('1006', '1', 'SM-18277', 3, 6.99); insert into LINE values('1006', '2', '2232/QTY', 1, 109.92); insert into LINE values('1006', '3', '23109-HB', 1, 9.95); insert into LINE values('1006', '4', '89-WRE-Q', 1, 256.99); insert into LINE values('1007', '1', '13-Q2/P2', 2, 14.99); insert into LINE values('1007', '2', '54778-2T', 1, 4.99); insert into LINE values('1008', '1', 'PVC23DRT', 5, 5.87); insert into LINE values('1008', '2', 'WR3/TT3', 3, 119.95); insert into LINE values('1008', '3', '23109-HB', 1, 9.95);
commit; - Script file used to create five tables.
CREATE TABLE Customer( CUS_CODE NUMBER(5), CUS_LNAME VARCHAR2(20), CUS_FNAME VARCHAR2(20), CUS_INITIAL CHAR(1), CUS_AREACODE CHAR(3), CUS_PHONE CHAR(8), CUS_BALANCE NUMBER(12,2), CONSTRAINT CUST_PK PRIMARY KEY(CUS_CODE));
CREATE TABLE Vendor( V_CODE CHAR(5), V_NAME VARCHAR2(20), V_CONTACT VARCHAR2(20), V_AREACODE CHAR(3), V_PHONE CHAR(8), V_STATE CHAR(2), V_ORDER CHAR(1), CONSTRAINT Vend_pk PRIMARY KEY(V_CODE));
CREATE TABLE Product( P_CODE VARCHAR2(20), P_DESCRIPT VARCHAR2(35), P_INDATE DATE, P_ONHAND NUMBER(12,2), P_PRICE NUMBER(12,2), P_DISCOUNT NUMBER(12,2), V_CODE CHAR(5), CONSTRAINT Product_Pk PRIMARY KEY(P_CODE), CONSTRAINT pRODUCT_vEND_fk FOREIGN KEY(V_CODE) REFERENCES VENDOR(V_CODE));
CREATE TABLE Invoice( INV_NUMBER VARCHAR2(4), CUS_CODE NUMBER(5), INV_DATE DATE, CONSTRAINT Invoice_Pk PRIMARY KEY (INV_NUMBER), CONSTRAINT Inv_Cust_Code_FK FOREIGN KEY (CUS_CODE) REFERENCES CUSTOMER);
CREATE TABLE Line( INV_NUMBER varchar2(4), LINE_NUMBER Number(3), P_CODE varchar2(20), LINE_UNITS number(15,2), LINE_PRICE number(12,2), PRIMARY KEY (INV_NUMBER, LINE_NUMBER), CONSTRAINT LINE_INV_INV_NUM_FK FOREIGN KEY (INV_NUMBER) REFERENCES INVOICE, CONSTRAINT LINE_Prod_FK FOREIGN KEY(P_CODE) REFERENCES PRODUCT);
INSERT INTO CUSTOMER VALUES(10010, 'Ramas', 'Alfred', 'A', '615', '844-2573', 0.00); INSERT INTO CUSTOMER VALUES(10011, 'Dunne', 'Leona', 'K', '713', '894-1238', 0.00); INSERT INTO CUSTOMER VALUES(10012, 'Smith', 'Kathy', 'W', '615', '894-2285', 345.86); INSERT INTO CUSTOMER VALUES(10013, 'Olowski', 'Paul', 'F', '615', '894-2180', 536.75); INSERT INTO CUSTOMER VALUES(10014, 'Orlando', 'Myron', ' ', '615', '222-1672', 0.00); INSERT INTO CUSTOMER VALUES(10015, 'O''Brian', 'Amy', 'B', '713', '442-3381', 0.00); INSERT INTO CUSTOMER VALUES(10016, 'Brown', 'James', 'G', '615', '297-1228', 221.19); INSERT INTO CUSTOMER VALUES(10017, 'Williams', 'George', ' ', '615', '290-2556', 768.93); INSERT INTO CUSTOMER VALUES(10018, 'Farriss', 'Anne', 'G', '713', '382-7185', 216.55); INSERT INTO CUSTOMER VALUES(10019, 'Smith', 'Olette', 'K', '615', '297-3809', 0.00);
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 and 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 and 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 Sis.', 'Orton', '904', '456-0092', 'FL', 'Y');
INSERT INTO PRODUCT VALUES('11QER/31', 'Power painter, 15 psi., 3-nozzle', '02-Jun-99', 8, 109.99, 0.00, '21225'); INSERT INTO PRODUCT VALUES('13-Q2/P2', '7.25-in. pwr. saw blade', '12-jul-99', 32, 14.99, 0.05,'21225'); INSERT INTO PRODUCT VALUES('14-Q1/L3', '9.00-in. pwr. saw blade', '12-jun-99', 18, 17.49, 0.00, '21225'); INSERT INTO PRODUCT VALUES('1546-QQ2', 'Hrd. cloth, 1/4-in., 2x50', '14-aug-99', 15, 39.95, 0.00, '21225'); INSERT INTO PRODUCT VALUES('1558-QW1', 'Hrd. cloth, 1/2-in., 3x50', '14-aug-99', 23, 43.99, 0.00, '21226'); INSERT INTO PRODUCT VALUES('2232/QTY', 'BandD jigsaw, 12-in. blade', '29-jul-99', 8, 109.92, 0.05, '21226'); INSERT INTO PRODUCT VALUES('2232/QWE', 'BandD jigsaw, 8-in. blade', '23-jul-99', 6, 99.87, 0.05,'21231'); INSERT INTO PRODUCT VALUES('2238/QPD', 'BandD cordless drill, 1/2-in.', '19-aug-99', 12, 38.95, 0.05,'21231'); INSERT INTO PRODUCT VALUES('23109-HB', 'Claw hammer', '19-aug-99', 23, 9.95, 0.10,'21344'); INSERT INTO PRODUCT VALUES('23114-AA', 'Sledge hammer, 12 lb.', '01-aug-99', 8, 14.40,0.05,'22567'); INSERT INTO PRODUCT VALUES('54778-2T', 'Rat-tail file, 1/8-in. fine', '14-jul-99', 43, 4.99, 0.00,'22567'); INSERT INTO PRODUCT VALUES('89-WRE-Q', 'Hicut chain saw, 16-in.', '6-sep-99', 11, 256.99, 0.05,'23119'); INSERT INTO PRODUCT VALUES('PVC23DRT', 'PVC pipe, 3.5-in., 8-ft', '19-sep-99', 188, 5.87, 0.00,'24004'); INSERT INTO PRODUCT VALUES('SM-18277', '1.25-in. metal screw, 25', '28-sep-99', 172, 6.99, 0.00,'24288'); INSERT INTO PRODUCT VALUES('SW-23116', '2.5-in. wd. screw, 50', '23-sep-99', 237, 8.45, 0.00,'25443'); INSERT INTO PRODUCT VALUES('WR3/TT3', 'Steel matting, 4''x8''x1/6", .5" mesh', '16-aug-99', 18, 119.95, 0.10,'25501');
INSERT INTO INVOICE VALUES('1001', '10014', '18-JAN-2008'); INSERT INTO INVOICE VALUES('1002', '10011', '18-JAN-2008'); INSERT INTO INVOICE VALUES('1003', '10012', '18-JAN-2008'); INSERT INTO INVOICE VALUES('1004', '10011', '19-JAN-2008'); INSERT INTO INVOICE VALUES('1005', '10018', '19-JAN-2008'); INSERT INTO INVOICE VALUES('1006', '10014', '19-JAN-2008'); INSERT INTO INVOICE VALUES('1007', '10015', '19-JAN-2008'); INSERT INTO INVOICE VALUES('1008', '10011', '19-JAN-2008');
insert into LINE values('1001', '1', '13-Q2/P2', 1, 14.99); insert into LINE values('1001', '2', '23109-HB', 1, 9.95); insert into LINE values('1002', '1', '54778-2T', 2, 4.99); insert into LINE values('1003', '1', '2238/QPD', 1, 38.95); insert into LINE values('1003', '2', '1546-QQ2', 1, 39.95); insert into LINE values('1003', '3', '13-Q2/P2', 5, 14.99); insert into LINE values('1004', '1', '54778-2T', 3, 4.99); insert into LINE values('1004', '2', '23109-HB', 2, 9.95); insert into LINE values('1005', '1', 'PVC23DRT', 12, 5.87); insert into LINE values('1006', '1', 'SM-18277', 3, 6.99); insert into LINE values('1006', '2', '2232/QTY', 1, 109.92); insert into LINE values('1006', '3', '23109-HB', 1, 9.95); insert into LINE values('1006', '4', '89-WRE-Q', 1, 256.99); insert into LINE values('1007', '1', '13-Q2/P2', 2, 14.99); insert into LINE values('1007', '2', '54778-2T', 1, 4.99); insert into LINE values('1008', '1', 'PVC23DRT', 5, 5.87); insert into LINE values('1008', '2', 'WR3/TT3', 3, 119.95); insert into LINE values('1008', '3', '23109-HB', 1, 9.95);
commit; Study the database structure attached. Use Notepad program to write eight queries and save them as a text file named as P1 Solution.txt. Make sure you label each query using a comment line (line preceded with two hyphens). Include your names at the top of the program file as comment lines. Before you submit the file for grading. make sure the file can execute to generate correct query results without error messages. See attached sample assignment format. Watch my DBMS&SQL; videos on database management system and SQL. Follow the steps below to test your SQL code. i. Register a free account on Oracle Live SQL (a cloud database program provider) a) Go to (b) Click on Start Coding Now à Create Account à Fill in the form e) Follow instruction to confirm your email address. 2. Download my script file to set up a database you will need to do this assignment. a. Go to D2L Week 5 b. Download P1 Setup.txt and save it on your pen drive or SCSU X drive . Upload P1 Setup script to Oracle Live SQL and execute set up code a) Login your Oracle Live SQL account à Select My Script à Upload a script from your computer. [use browse to locate P1 Setup.txt file you downloaded earlier, enter P1_setup or a name of your choice in Script Name] field, then click on Upload Script button. b) Click on My Scripts again, you should see the script name you just uploaded. e) Double click on the script name (P1 Setup or whatever name you entered earlier) Click on Run Script button. [you should see messages confirming table and record created.] Setup Work is done!! 4. Time to write your own SQL commands; Try the following exercises: a) Click on SQL Worksheet Type: SELECT*FROM TAB; Click on Run button. [You should see a list of tables; some of the tables were created by the P1 Setup script earlier] b) Type: SELECT FROM Customer; then Run (c) Type: SELECT cus Fname, cus Lname., Inv Number, Inv Date FROM
Explanation / Answer
Ans 4:
SELECT C.CUS_CODE, C.CUS_BALANCE, Z."TotalPurchases"
FROM Customer "C", (SELECT I.CUS_CODE, Sum(L.TOTAL) as "TotalPurchases"
FROM Invoice "I",
(SELECT INV_NUMBER, Sum(LINE_UNITS * LINE_PRICE) as "TOTAL"
FROM Line GROUP BY INV_NUMBER) "L"
WHERE I.INV_NUMBER = L.INV_NUMBER
GROUP BY I.CUS_CODE) "Z"
WHERE C.CUS_CODE = Z.CUS_CODE
ORDER BY C.CUS_CODE
Ans 5:
SELECT C.CUS_CODE, C.CUS_BALANCE, Z."TotalPurchases", Z."NoOfPurchases"
FROM Customer "C", (SELECT I.CUS_CODE, Sum(L.TOTAL) as "TotalPurchases", Sum(L.NP) as "NoOfPurchases"
FROM Invoice "I",
(SELECT INV_NUMBER, COUNT(LINE_NUMBER) as "NP", Sum(LINE_UNITS * LINE_PRICE) as "TOTAL"
FROM Line GROUP BY INV_NUMBER) "L"
WHERE I.INV_NUMBER = L.INV_NUMBER
GROUP BY I.CUS_CODE) "Z"
WHERE C.CUS_CODE = Z.CUS_CODE
ORDER BY C.CUS_CODE
Ans 8:
SELECT INV_NUMBER, Sum(LINE_UNITS * LINE_PRICE) as "Invoice Total"
FROM Line GROUP BY INV_NUMBER
ORDER BY INV_NUMBER
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.