home / study / engineering / computer science / computer science questions and a
ID: 3753226 • Letter: H
Question
home / study / engineering / computer science / computer science questions and answers / consumer table cus_code cus_lname cus_fname cus_initial cus_areacode cus_phone cus_balance ...
Question: CONSUMER TABLE CUS_CODE CUS_LNAME CUS_FNAME CUS_INITIAL CUS_AREACODE CUS_PHONE CUS_BALANCE ...
CONSUMER TABLE
CUS_CODE
Write and run SQL statements to complete the following tasks
List the names and codes of vendors and the number of products each vendor has supplied, i.e. vendor XXX has supplied xxx products, and vendor YYY has supplied yyy products etc.
List all purchases by customer 10011.
Show the details of the employees who are located in area code 615.
Using inner join, list the details of the products whose line price is greater than 100.
CUS_CODE
CUS_LNAME CUS_FNAME CUS_INITIAL CUS_AREACODE CUS_PHONE CUS_BALANCE 10010 Ramas Alfred A 615 844-2573 0 10011 Dunne Leona K 713 894-1238 0 10012 Smith Kathy W 615 894-2285 345.86 10013 Olowski Paul F 615 894-2180 536.75 10014 Orlando Myron 615 222-1672 0 10015 O'Brian Amy B 713 442-3381 0 10016 Brown James G 615 297-1228 221.19 10017 Williams George 615 290-2556 768.93 10018 Farriss Anne G 713 382-7185 216.55 10019 Smith Olette K 615 297-3809 0 EMP TABLE EMP_NUM EMP_TITLE EMP_LNAME EMP_FNAME EMP_INITIAL EMP_DOB EMP_HIRE_DATE EMP_AREACODE EMP_PHONE EMP_MGR 100 Mr. Kolmycz George D 1942-06-15 1985-03-15 615 324-5456 101 Ms. Lewis Rhonda G 1965-03-19 1986-04-25 615 324-4472 100 102 Mr. VanDam Rhett 1958-11-14 1990-12-20 901 675-8993 100 103 Ms. Jones Anne M 1974-10-16 1994-08-28 615 898-3456 100 104 Mr. Lange John P 1971-11-08 1994-10-20 901 504-4430 105 105 Mr. Williams Robert D 1975-03-14 1998-11-08 615 890-3220 106 Mrs. Smith Jeanine K 1968-02-12 1989-01-05 615 324-7883 105 107 Mr. Diante Jorge D 1974-08-21 1994-07-02 615 890-4567 105 108 Mr. Wiesenbach Paul R 1966-02-14 1992-11-18 615 897-4358 109 Mr. Smith George K 1961-06-18 1989-04-14 901 504-3339 108 110 Mrs. Genkazi Leighla W 1970-05-19 1990-12-01 901 569-0093 108 111 Mr. Washington Rupert E 1966-01-03 1993-06-21 615 890-4925 105 112 Mr. Johnson Edward E 1961-05-14 1983-12-01 615 898-4387 100 113 Ms. Smythe Melanie P 1970-09-15 1999-05-11 615 324-9006 105 114 Ms. Brandon Marie G 1956-11-02 1979-11-15 901 882-0845 108 115 Mrs. Saranda Hermine R 1972-07-25 1993-04-23 615 324-5505 105 116 Mr. Smith George A 1965-11-08 1988-12-10 615 890-2984 108 INVOICE TABLE INV_NUMBER CUS_CODE INV_DATE INV_SUBTOTAL INV_TAX INV_TOTAL 1001 10014 2008-01-16 24.9 1.99 26.89 1002 10011 2008-01-16 9.98 0.8 10.78 1003 10012 2008-01-16 153.85 12.31 166.16 1004 10011 2008-01-17 34.97 2.8 37.77 1005 10018 2008-01-17 70.44 5.64 76.08 1006 10014 2008-01-17 397.83 31.83 429.66 1007 10015 2008-01-17 34.97 2.8 37.77 1008 10011 2008-01-17 399.15 31.93 431.08 LINE TABLE INV_NUMBER LINE_NUMBER P_CODE LINE_UNITS LINE_PRICE LINE_TOTAL 1001 1 13-Q2/P2 1 14.99 14.99 1001 2 23109-HB 1 9.95 9.95 1002 1 54778-2T 2 4.99 9.98 1003 1 2238/QPD 1 38.95 38.95 1003 2 1546-QQ2 1 39.95 39.95 1003 3 13-Q2/P2 5 14.99 74.95 1004 1 54778-2T 3 4.99 14.97 1004 2 23109-HB 2 9.95 19.9 1005 1 PVC23DRT 12 5.87 70.44 1006 1 SM-18277 3 6.99 20.97 1006 2 2232/QTY 1 109.92 109.92 1006 3 23109-HB 1 9.95 9.95 1006 4 89-WRE-Q 1 256.99 256.99 1007 1 13-Q2/P2 2 14.99 29.98 1007 2 54778-2T 1 4.99 4.99 1008 1 PVC23DRT 5 5.87 29.35 1008 2 WR3/TT3 3 119.95 359.85 1008 3 23109-HB 1 9.95 9.95 PRODUCT TABLE P_CODE P_DESCRIPT P_INDATE P_QOH P_MIN P_PRICE P_DISCOUNT V_CODE 23114-AA Sledge hammer, 12 lb. 1/2/2008 8 5 14.4 0.05 54778-2T Rat-tail file, 1/8-in. fine 12/15/2007 43 20 4.99 0 21344 89-WRE-Q Hicut chain saw, 16 in. 2/7/2008 11 5 256.99 0.05 24288 PVC23DRT PVC pipe, 3.5-in., 8-ft 2/20/2008 188 75 5.87 0 SM-18277 1.25-in. metal screw, 25 3/1/2008 172 75 6.99 0 21225 SW-23116 2.5-in. wd. screw, 50 2/24/2008 237 100 8.45 0 21231 WR3/TT3 Steel matting, 4'x8'x1/6", .5" mesh 1/17/2008 18 5 119.95 0.1 25595 11QER/31 Power painter, 15 psi., 3-nozzle 11/3/2007 8 5 109.99 0 25595 13-Q2/P2 7.25-in. pwr. saw blade 12/13/2007 32 15 14.99 0.05 21344 14-Q1/L3 9.00-in. pwr. saw blade 11/13/2007 18 12 17.49 0 21344 1546-QQ2 Hrd. cloth, 1/4-in., 2x50 1/15/2008 15 8 39.95 0 23119 1558-QW1 Hrd. cloth, 1/2-in., 3x50 1/15/2008 23 5 43.99 0 23119 2232/QTY B&D jigsaw, 12-in. blade 12/15/2007 8 5 109.92 0.05 24288 2232/QWE B&D jigsaw, 8-in. blade 12/24/2007 6 5 99.87 0.05 24288 2238/QPD B&D cordless drill, 1/2-in. 1/20/2008 12 5 38.95 0.05 25595 23109-HB Claw hammer 1/20/2008 23 10 9.95 0.1 21225 23114-AA Sledge hammer, 12 lb. 1/2/2008 8 5 14.4 0.05 VENDOR TABLE 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 N 25501 Damal Supplies Smythe 615 890-3529 TN N 25595 Rubicon Systems Orton 904 456-0092 FL YExplanation / Answer
create table CONSUMER(CUS_CODE Integer Primary Key,
CUS_LNAME varchar(255),
CUS_FNAME varchar(255),
CUS_INITIAL varchar(20),
CUS_AREACODE Integer,
CUS_PHONE varchar(255),
CUS_BALANCE double);
insert into CONSUMER values(10010,'Ramas','Alfred','A',615,'844-2573',0)
create table EMP(EMP_NUM Integer Primary Key,
EMP_TITLE varchar(255),
EMP_LNAME varchar(255),
EMP_FNAME varchar(20),
EMP_INITIAL varchar(20),
EMP_DOB varchar(255),
EMP_HIRE_DATE varchar(255),
EMP_AREACODE Integer,
EMP_PHONE varchar(255));
insert into EMP values(100,'Mr.','Kolmycz','George','D','1942-06-15','1985-03-15',615,'324-5456')
create table INVOICE(INV_NUMBER Integer Primary Key,
CUS_CODE Integer,
INV_DATE varchar(255),
INV_SUBTOTAL double,
INV_TAX double,
INV_TOTAL double,
FOREIGN KEY (CUS_CODE) REFERENCES CONSUMER(CUS_CODE));
insert into INVOICE values(1001,10014,'2008-01-16',24.9,1.99,26.89)
insert into INVOICE values(1002,10010,'2008-01-16',24.9,1.99,26.89)
create table VENDOR(V_CODE Integer Primary Key,
V_NAME varchar(255),
V_CONTACT varchar(255),
V_AREACODE int,
V_PHONE varchar(255),
V_STATE varchar(255),
V_ORDER varchar(255));
insert into VENDOR values(21225,'Bryson, Inc.','Smithson',615,'223-3234','TN','Y')
create table PRODUCT(P_CODE varchar(255) Primary Key,
P_DESCRIPT varchar(255),
P_INDATE varchar(255),
P_QOH int,
P_MIN int,
P_PRICE double,
P_DISCOUNT double,
V_CODE int,
FOREIGN KEY (V_CODE) REFERENCES VENDOR(V_CODE));
insert into PRODUCT values('23114-AA','Sledge hammer, 12 lb.','1/2/2008',8,5,14.4,0.05,21225)
insert into PRODUCT values('54778-2T','Rat-tail file, 1/8-in. fine, 12 lb.','12/15/2007',8,5,14.4,0.05,21225)
create table LINE(INV_NUMBER Integer,
LINE_NUMBER Integer,
P_CODE varchar(255),
LINE_UNITS int,
LINE_PRICE double,
LINE_TOTAL double,
FOREIGN KEY (INV_NUMBER) REFERENCES INVOICE(INV_NUMBER),
FOREIGN KEY (P_CODE) REFERENCES PRODUCT(P_CODE));
insert into LINE values(1001,1,'23114-AA',1,14.99,14.99)
insert into LINE values(1001,2,'54778-2T',1,148.99,14.99)
insert into LINE values(1002,2,'54778-2T',1,148.99,14.99)
Please insert remaining values using above insert script
List the names and codes of vendors and the number of products each vendor has supplied, i.e. vendor XXX has supplied xxx products, and vendor YYY has supplied yyy products etc.
Ans: SELECT p.V_CODE,V_NAME,count(*) as NoofProducts
FROM VENDOR v INNER JOIN PRODUCT p
ON p.V_CODE=v.V_CODE
List all purchases by customer 10011.
Ans: SELECT c.CUS_CODE,CUS_FNAME,CUS_LNAME,p.P_CODE,P_DESCRIPT
FROM (((CONSUMER c INNER JOIN INVOICE i ON c.CUS_CODE = i.CUS_CODE)
INNER JOIN LINE l ON i.INV_NUMBER = l.INV_NUMBER)
INNER JOIN PRODUCT p ON l.P_CODE=p.P_CODE) where c.CUS_CODE=10011
Show the details of the employees who are located in area code 615.
Ans: select * from EMP where EMP_AREACODE=615
list the details of the products whose line price is greater than 100
Ans: SELECT *
FROM PRODUCT p INNER JOIN LINE l
ON p.P_CODE=l.P_CODE
where LINE_PRICE>100
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.