Homework Help is Here – Start Your Trial Now!
Engineering
Computer Science
Q17) List the V_CODE and V_NAME of vendors that do not provide products Q18) ?How much is the total value (P_QOH*P_PRICE) of our product inventory

Q17) List the V_CODE and V_NAME of vendors that do not provide products Q18) ?How much is the total value (P_QOH*P_PRICE) of our product inventory

Database System Concepts
Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
See similar textbooks
icon
Related questions
Question

Q17) List the V_CODE and V_NAME of vendors that do not provide products
Q18) ?How much is the total value (P_QOH*P_PRICE) of our product inventory

DROP TABLE LINE cascade constraints; DROP TABLE INVOICE cascade constraints; DROP TABLE CUSTOMER cascade constraints; DROP TABLE PRODUCT cascade constraints; DROP TABLE VENDOR cascade constraints; DROP TABLE EMPLOYEE cascade constraints; DROP TABLE EMP cascade constraints; DROP TABLE P cascade constraints; DROP TABLE V cascade constraints; purge recyclebin; CREATE TABLE PRODUCT ( P_CODE VARCHAR2 (10) CONSTRAINT PRODUCT_P_CODE_PK PRIMARY KEY, P_DESCRIPT P_INDATE P_QOH P_MIN P_PRICE P_DISCOUNT V_CODE CONSTRAINT PRODUCT_V_CODE_FK FOREIGN KEY (V_CODE) REFERENCES VENDOR); VARCHAR2 (35) NOT NULL, DATE NOT NULL, NUMBER NOT NULL, NUMBER NOT NULL, NUMBER (8,2) NOT NULL, NUMBER (4,2) NOT NULL, NUMBER, CREATE TABLEV ( V_CODE V_NAME V_CONTACT V_AREACODE V_PHONE V_STATE V_ORDER INTEGER PRIMARY KEY, VARCHAR ( 35) NOT NULL, VARCHAR (15) NOT NULL, CREATE TABLE CUSTOMER ( Cus_CODE CuS_LNAME CuS_FNAME Cus_INITIAL CUS_AREACODE CuS_PHONE Cus_BALANCE CONSTRAINT CUS_UI1 UNIQUE (CUS_LNAME, CUS_FNAME)); NUMBER PRIMARY KEY, VARCHAR (15) NOT NULL, VARCHAR (15) NOT NULL, CHAR(1), CHAR (5) DEFAULT '615' NOT NULL CHECK (CUS_AREACODE IN ('615',"713', '931')), CHAR(8) NOT NULL, CHAR (5) NOT NULL, CHAR (8) NOT NULL, CHAR (2) NOT NULL, CHAR (1) NOT NULL); NUMBER (9,2) DEFAULT 0.00, CREATE TABLE P ( P_CODE P_DESCRIPT P_INDATE P_QOH P_MIN VARCHAR2 (10) PRIMARY KEY, VARCHAR2 (35) NOT NULL, DATE NOT NULL, NUMBER NOT NULL, NUMBER NOT NULL, NUMBER (8,2) NOT NULL, NUMBER (4,2) NOT NULL, NUMBER); CREATE TABLE INVOICE ( INV_NUMBER Cus_CODE INV_DATE CONSTRAINT INV_CK1 CHECK (INV_DATE > TO_DATE('01-JAN-2819","D0-NON-YYYY"))); NUMBER PRIMARY KEY, NUMBER NOT NULL REFERENCES CUSTOMER(CUS_CODE), DATE DEFAULT SYSDATE NOT NULL, P_PRICE P_DISCOUNT V_CODE CREATE TABLE LINE ( CREATE TABLE VENDOR ( V_CODE V_NAME v_CONTACT V_AREACODE V_PHONE V_STATE v_ORDER PRIMARY KEY (V_CODE)); NUMBER NOT NULL, INV_NUMBER LINE_NUMBER P_CODE LINE_UNITS LINE_PRICE PRIMARY KEY (INV_NUMBER, LINE_NUMBER), FOREIGN KEY (INV_NUMBER) REFERENCES INVOICE ON DELETE CASCADE, FOREIGN KEY (P_CODE) REFERENCES PRODUCT (P_CODE), CONSTRAINT LINE_UI1 UNIQUE (INV_NUMBER, P_CODE)); INTEGER, VARCHAR ( 35) NOT NULL, VARCHAR (15) NOT NULL, CHAR (5) NOT NULL, CHAR (8) NOT NULL, CHAR (2) NOT NULL, CHAR (1) NOT NULL, NUMBER (2,0) NOT NULL, VARCHAR (10) NOT NULL, NUMBER (9,2) DEFAULT 8.00 NOT NULL, NUMBER (9,2) DEFAULT 8.00 NOT NULL,
Transcribed Image Text:DROP TABLE LINE cascade constraints; DROP TABLE INVOICE cascade constraints; DROP TABLE CUSTOMER cascade constraints; DROP TABLE PRODUCT cascade constraints; DROP TABLE VENDOR cascade constraints; DROP TABLE EMPLOYEE cascade constraints; DROP TABLE EMP cascade constraints; DROP TABLE P cascade constraints; DROP TABLE V cascade constraints; purge recyclebin; CREATE TABLE PRODUCT ( P_CODE VARCHAR2 (10) CONSTRAINT PRODUCT_P_CODE_PK PRIMARY KEY, P_DESCRIPT P_INDATE P_QOH P_MIN P_PRICE P_DISCOUNT V_CODE CONSTRAINT PRODUCT_V_CODE_FK FOREIGN KEY (V_CODE) REFERENCES VENDOR); VARCHAR2 (35) NOT NULL, DATE NOT NULL, NUMBER NOT NULL, NUMBER NOT NULL, NUMBER (8,2) NOT NULL, NUMBER (4,2) NOT NULL, NUMBER, CREATE TABLEV ( V_CODE V_NAME V_CONTACT V_AREACODE V_PHONE V_STATE V_ORDER INTEGER PRIMARY KEY, VARCHAR ( 35) NOT NULL, VARCHAR (15) NOT NULL, CREATE TABLE CUSTOMER ( Cus_CODE CuS_LNAME CuS_FNAME Cus_INITIAL CUS_AREACODE CuS_PHONE Cus_BALANCE CONSTRAINT CUS_UI1 UNIQUE (CUS_LNAME, CUS_FNAME)); NUMBER PRIMARY KEY, VARCHAR (15) NOT NULL, VARCHAR (15) NOT NULL, CHAR(1), CHAR (5) DEFAULT '615' NOT NULL CHECK (CUS_AREACODE IN ('615',"713', '931')), CHAR(8) NOT NULL, CHAR (5) NOT NULL, CHAR (8) NOT NULL, CHAR (2) NOT NULL, CHAR (1) NOT NULL); NUMBER (9,2) DEFAULT 0.00, CREATE TABLE P ( P_CODE P_DESCRIPT P_INDATE P_QOH P_MIN VARCHAR2 (10) PRIMARY KEY, VARCHAR2 (35) NOT NULL, DATE NOT NULL, NUMBER NOT NULL, NUMBER NOT NULL, NUMBER (8,2) NOT NULL, NUMBER (4,2) NOT NULL, NUMBER); CREATE TABLE INVOICE ( INV_NUMBER Cus_CODE INV_DATE CONSTRAINT INV_CK1 CHECK (INV_DATE > TO_DATE('01-JAN-2819","D0-NON-YYYY"))); NUMBER PRIMARY KEY, NUMBER NOT NULL REFERENCES CUSTOMER(CUS_CODE), DATE DEFAULT SYSDATE NOT NULL, P_PRICE P_DISCOUNT V_CODE CREATE TABLE LINE ( CREATE TABLE VENDOR ( V_CODE V_NAME v_CONTACT V_AREACODE V_PHONE V_STATE v_ORDER PRIMARY KEY (V_CODE)); NUMBER NOT NULL, INV_NUMBER LINE_NUMBER P_CODE LINE_UNITS LINE_PRICE PRIMARY KEY (INV_NUMBER, LINE_NUMBER), FOREIGN KEY (INV_NUMBER) REFERENCES INVOICE ON DELETE CASCADE, FOREIGN KEY (P_CODE) REFERENCES PRODUCT (P_CODE), CONSTRAINT LINE_UI1 UNIQUE (INV_NUMBER, P_CODE)); INTEGER, VARCHAR ( 35) NOT NULL, VARCHAR (15) NOT NULL, CHAR (5) NOT NULL, CHAR (8) NOT NULL, CHAR (2) NOT NULL, CHAR (1) NOT NULL, NUMBER (2,0) NOT NULL, VARCHAR (10) NOT NULL, NUMBER (9,2) DEFAULT 8.00 NOT NULL, NUMBER (9,2) DEFAULT 8.00 NOT NULL,
CREATE TABLE EMPLOYEE ( EMP_NUM EMP_TITLE EMP LNAME EMP_FNAME EMP_INITIAL EMP DOB EMP_HIRE DATE EMP_YEARS EMP_AREACODE EMP_PHONE /*P rows NUMBER PRIMARY KEY, INSERT INTO P VALUES("11QER/31", "Power painter, 15 psi., 3-nozzle INSERT INTO P VALUES("13-Q2/P2","7.25-in. pwr. saw blade" INSERT INTO P VALUES("14-Q1/L3", "9.e8-in. pwr. saw blade" INSERT INTO P VALUES("1546-Q02", "Hrd. cloth, 1/4-in., 2x5e" INSERT INTO P VALUES("1558-QW1", "Hrd. cloth, 1/2-in., 3x5e INSERT INTO P VALUES( "2232/QTY", "B\&D jigsaw, 12-in. blade INSERT INTO P VALUES("2232/QWE", "B\&D jigsaw, 8-in. blade" INSERT INTO P VALUES("2238/QPD", "B\40 cordless drill, 1/2-in." INSERT INTO P VALUES("23109-HB", "C1law hamner INSERT INTO P VALUES("23114-AA", "Sledge hamner, 12 lb." INSERT INTO P VALUES("54778-21', "Rat-tail file, 1/8-1in. fine' INSERT INTO P VALUES("89-WRE-Q', "Hicut chain saw, 16 in." INSERT INTO P VALUES("PVC230RT", "Pc pipe, 3.5-in., 8-ft" INSERT INTO P VALUES("SM-18277",'1.25-in. netal screw, 25" INSERT INTO P VALUES("SW-23116","2.5-in. wd. screw, se INSERT INTO P VALUES("WR3/TT3' ,"Steel ratting, 4''x8" "x1/6", .5" nesh',"17-JAN-2019", 18, 5,119.95,8.10,25595); e3-NOV-2018', 8, 5,109.99,8.ee,25595); ,'13-DEC-2018", 32, 15, 14.99,8.e5, 21344); ,'13-NOV-2019', 18, 12, 17.49,0.ee,21344); ,'15-JAN-2019', 15, 8, 39.95,0.00,23119); ,'15-JAN-2819", 23, 5, 43.99,8.80, 23119); ,38-DEC-2018', 8, 5,109.92,8.e5,24288); ,"24-DEC-2018', 6, 5, 99.87,8.e5,24288); ,"28-JAN-2019', 12, 5, 38.95,8.e5,25595); ,"20-JAN-2019', 23, 10, 9.95,0.18,21225); ,"e2-JAN-2019', 8, 5, 14.40,0.e5, NULL); ,'15-DEC-2018', 43, 20, 4.99,0.00,21344); ,'87-FEB-2019", 11, 5,256.99,8.05, 24288); ,28-FEB-2019',188, 75, 5.87,0.e0, NULL); e1-MAR-2019", 172, 75, 6.99,8.e0,21225); "24-FLB-2019', 237,100, 8.45,0.e0, 21231); CHAR (10), VARCHAR (15) NOT NULL, VARCHAR (15) NOT NULL, CHAR (1), DATE, DATE, NUMBER, CHAR (3), CHAR (8)); CREATE TABLE EMP ( EMP_NUM EMP_TITLE EMP LNAME EMP_FNAME EMP_INITIAL EMP_DOB EMP_HIRE DATE EMP_AREACODE EMP_PHONE EMP_MGR NUMBER PRIMARY KEY, CHAR (10), VARCHAR(15) NOT NULL, VARCHAR (15) NOT NULL, CHAR (1), DATE, * VENDOR rows INSERT INTO VENDOR (SELECT • FROM V); DATE, CHAR (3), CHAR (8), NUMBER); /* PRODUCT rows INSERT INTO PRODUCT (SELECT • FRROM P); * CUSTOMER rOws INSERT INTO CUSTOMER VALUES (18018, "Ranas INSERT INTO CUSTOMER VALUES(10011, 'Dunne INSERT INTO CUSTOMER VALUES(10012, 'Sith INSERT INTO CUSTOMER VALUES (10013, 'Olowski" , "Paul' ,'F' ,'615', '894-218e",536.75); INSERT INTO CUSTOMER VALUES (18014, 'Orlando" , 'Hyron ,NULL, '615','222-1672',8); INSERT INTO CUSTOMER VALUES (18015, '0""Brian", "Any" INSERT INTO CUSTOMER VALUES (10016, 'Brown" INSERT INTO CUSTOMER VALUES (18017, 'Willians", "George", NULL, '615", "298-2556",768.93); INSERT INTO CUSTOMER VALUES (18018, 'Farriss" ,'Anne ,' ,'713",'382-7185', 216.55); INSERT INTO CUSTOMER VALUES (10019, 'Snith" /* Loading data rows /* Turn Escape character on /* Default escape character "I /* Used to enter special characters (8) ,'Alfred", 'A" ,'615s', '844-2573",8); "Leona" ,"K" ,'713','894-1238',8); "Kathy" , "W ,'615",'894-2285', 345.86); SET ESCAPE ON; ,'B' ,'713','442-3381',8); "Janes' ,'6" ,'615','297-1228', 221.19); /* v rOws INSERT INTO V VALUES (21225, 'Bryson, Inc." INSERT INTO V VALUES (21226, 'SuperLoo, Inc." INSERT INTO V VALUES (21231, 'D\&E Supply" INSERT INTO V VALUES (21344, 'Jabavu Bros." INSERT INTO V VALUES (22567, 'Dome Supply' INSERT INTO V VALUES (23119, 'Randsets Ltd.' INSERT INTO V VALUES (24804, 'Bracknan Bros." INSERT INTO V VALUES (24288, 'ORDVA, Inc." INSERT INTO V VALUES (25443, 'B\&K, Inc." INSERT INTO V VALUES (25501, 'Damal Supplies" INSERT INTO V VALUES (25595, 'Rubicon Systens ,'Du Toit' ,"0lette", "K" ,'615', 297-3829",8); ,'Smithson', '615','223-3234', "TN','Y'); "Flushing', '984','215-8995', "FL','N'); 615','228-3245', "TN','Y'); '615','889-2546', 'KY', 'N'); 981','678-1419', "GA', 'N'); ,'Anderson', '981','678-3998", " GA',"Y'); "Browning', '615', 228-1410, TN', 'N'); ,"Hakford','615','898-1234',"TN',"Y'); *984','227-0093', 'FL','N'); ,"Smythe' ,'615','890-3529","TN', 'N'); 984','456-0092", 'FL', 'Y'); /* INVOICE rows INSERT INTO INVOICE VALUES(1801,18014, '15-JAN-2019'); INSERT INTO INVOICE VALUES(1002, 18811, '16-JAN-2019'); INSERT INTO INVOICE VALUES(1003,10012, '16-JAN-2019'); INSERT INTO INVOICE VALUES(1004, 18e11, '17-JAN-2019'); INSERT INTO INVOICE VALUES(1005,10018, '17-JAN-2819'); INSERT INTO INVOICE VALUES(1006,10014, '17-JAN-2819'); INSERT INTO INVOICE VALUES(1007,18e15, '17-JAN-2019'); INSERT INTO INVOICE VALUES(1808,18011, '17-JAN-2019'); "Singh" ,"Khunalo' Smith" Smith
Transcribed Image Text:CREATE TABLE EMPLOYEE ( EMP_NUM EMP_TITLE EMP LNAME EMP_FNAME EMP_INITIAL EMP DOB EMP_HIRE DATE EMP_YEARS EMP_AREACODE EMP_PHONE /*P rows NUMBER PRIMARY KEY, INSERT INTO P VALUES("11QER/31", "Power painter, 15 psi., 3-nozzle INSERT INTO P VALUES("13-Q2/P2","7.25-in. pwr. saw blade" INSERT INTO P VALUES("14-Q1/L3", "9.e8-in. pwr. saw blade" INSERT INTO P VALUES("1546-Q02", "Hrd. cloth, 1/4-in., 2x5e" INSERT INTO P VALUES("1558-QW1", "Hrd. cloth, 1/2-in., 3x5e INSERT INTO P VALUES( "2232/QTY", "B\&D jigsaw, 12-in. blade INSERT INTO P VALUES("2232/QWE", "B\&D jigsaw, 8-in. blade" INSERT INTO P VALUES("2238/QPD", "B\40 cordless drill, 1/2-in." INSERT INTO P VALUES("23109-HB", "C1law hamner INSERT INTO P VALUES("23114-AA", "Sledge hamner, 12 lb." INSERT INTO P VALUES("54778-21', "Rat-tail file, 1/8-1in. fine' INSERT INTO P VALUES("89-WRE-Q', "Hicut chain saw, 16 in." INSERT INTO P VALUES("PVC230RT", "Pc pipe, 3.5-in., 8-ft" INSERT INTO P VALUES("SM-18277",'1.25-in. netal screw, 25" INSERT INTO P VALUES("SW-23116","2.5-in. wd. screw, se INSERT INTO P VALUES("WR3/TT3' ,"Steel ratting, 4''x8" "x1/6", .5" nesh',"17-JAN-2019", 18, 5,119.95,8.10,25595); e3-NOV-2018', 8, 5,109.99,8.ee,25595); ,'13-DEC-2018", 32, 15, 14.99,8.e5, 21344); ,'13-NOV-2019', 18, 12, 17.49,0.ee,21344); ,'15-JAN-2019', 15, 8, 39.95,0.00,23119); ,'15-JAN-2819", 23, 5, 43.99,8.80, 23119); ,38-DEC-2018', 8, 5,109.92,8.e5,24288); ,"24-DEC-2018', 6, 5, 99.87,8.e5,24288); ,"28-JAN-2019', 12, 5, 38.95,8.e5,25595); ,"20-JAN-2019', 23, 10, 9.95,0.18,21225); ,"e2-JAN-2019', 8, 5, 14.40,0.e5, NULL); ,'15-DEC-2018', 43, 20, 4.99,0.00,21344); ,'87-FEB-2019", 11, 5,256.99,8.05, 24288); ,28-FEB-2019',188, 75, 5.87,0.e0, NULL); e1-MAR-2019", 172, 75, 6.99,8.e0,21225); "24-FLB-2019', 237,100, 8.45,0.e0, 21231); CHAR (10), VARCHAR (15) NOT NULL, VARCHAR (15) NOT NULL, CHAR (1), DATE, DATE, NUMBER, CHAR (3), CHAR (8)); CREATE TABLE EMP ( EMP_NUM EMP_TITLE EMP LNAME EMP_FNAME EMP_INITIAL EMP_DOB EMP_HIRE DATE EMP_AREACODE EMP_PHONE EMP_MGR NUMBER PRIMARY KEY, CHAR (10), VARCHAR(15) NOT NULL, VARCHAR (15) NOT NULL, CHAR (1), DATE, * VENDOR rows INSERT INTO VENDOR (SELECT • FROM V); DATE, CHAR (3), CHAR (8), NUMBER); /* PRODUCT rows INSERT INTO PRODUCT (SELECT • FRROM P); * CUSTOMER rOws INSERT INTO CUSTOMER VALUES (18018, "Ranas INSERT INTO CUSTOMER VALUES(10011, 'Dunne INSERT INTO CUSTOMER VALUES(10012, 'Sith INSERT INTO CUSTOMER VALUES (10013, 'Olowski" , "Paul' ,'F' ,'615', '894-218e",536.75); INSERT INTO CUSTOMER VALUES (18014, 'Orlando" , 'Hyron ,NULL, '615','222-1672',8); INSERT INTO CUSTOMER VALUES (18015, '0""Brian", "Any" INSERT INTO CUSTOMER VALUES (10016, 'Brown" INSERT INTO CUSTOMER VALUES (18017, 'Willians", "George", NULL, '615", "298-2556",768.93); INSERT INTO CUSTOMER VALUES (18018, 'Farriss" ,'Anne ,' ,'713",'382-7185', 216.55); INSERT INTO CUSTOMER VALUES (10019, 'Snith" /* Loading data rows /* Turn Escape character on /* Default escape character "I /* Used to enter special characters (8) ,'Alfred", 'A" ,'615s', '844-2573",8); "Leona" ,"K" ,'713','894-1238',8); "Kathy" , "W ,'615",'894-2285', 345.86); SET ESCAPE ON; ,'B' ,'713','442-3381',8); "Janes' ,'6" ,'615','297-1228', 221.19); /* v rOws INSERT INTO V VALUES (21225, 'Bryson, Inc." INSERT INTO V VALUES (21226, 'SuperLoo, Inc." INSERT INTO V VALUES (21231, 'D\&E Supply" INSERT INTO V VALUES (21344, 'Jabavu Bros." INSERT INTO V VALUES (22567, 'Dome Supply' INSERT INTO V VALUES (23119, 'Randsets Ltd.' INSERT INTO V VALUES (24804, 'Bracknan Bros." INSERT INTO V VALUES (24288, 'ORDVA, Inc." INSERT INTO V VALUES (25443, 'B\&K, Inc." INSERT INTO V VALUES (25501, 'Damal Supplies" INSERT INTO V VALUES (25595, 'Rubicon Systens ,'Du Toit' ,"0lette", "K" ,'615', 297-3829",8); ,'Smithson', '615','223-3234', "TN','Y'); "Flushing', '984','215-8995', "FL','N'); 615','228-3245', "TN','Y'); '615','889-2546', 'KY', 'N'); 981','678-1419', "GA', 'N'); ,'Anderson', '981','678-3998", " GA',"Y'); "Browning', '615', 228-1410, TN', 'N'); ,"Hakford','615','898-1234',"TN',"Y'); *984','227-0093', 'FL','N'); ,"Smythe' ,'615','890-3529","TN', 'N'); 984','456-0092", 'FL', 'Y'); /* INVOICE rows INSERT INTO INVOICE VALUES(1801,18014, '15-JAN-2019'); INSERT INTO INVOICE VALUES(1002, 18811, '16-JAN-2019'); INSERT INTO INVOICE VALUES(1003,10012, '16-JAN-2019'); INSERT INTO INVOICE VALUES(1004, 18e11, '17-JAN-2019'); INSERT INTO INVOICE VALUES(1005,10018, '17-JAN-2819'); INSERT INTO INVOICE VALUES(1006,10014, '17-JAN-2819'); INSERT INTO INVOICE VALUES(1007,18e15, '17-JAN-2019'); INSERT INTO INVOICE VALUES(1808,18011, '17-JAN-2019'); "Singh" ,"Khunalo' Smith" Smith
Expert Solution
steps

Step by step

Solved in 2 steps

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Knowledge Booster
Single Table
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Similar questions
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education
SEE MORE TEXTBOOKS