CONSUMER TABLE 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 Y Using EXCEPT show the codes of the vendors who did not supply any products Using NOT IN show the codes and names of the vendors who did not supply any products List the 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 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

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 23, 2024

CONSUMER TABLE 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

CONSUMER TABLE

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	Y

Using EXCEPT show the codes of the vendors who did not supply any products.

Using NOT IN show the codes and names of the vendors who did not supply any products.

List the 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 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