SQL> select * from customer; CUS CUSTOMER_NAME STREET CITY ST POSTA BALANCE CRED
ID: 3918510 • Letter: S
Question
SQL> select * from customer;
CUS CUSTOMER_NAME STREET CITY ST POSTA BALANCE CREDIT_LIMIT RE
126 Toys Galore 28 Laketon St. Fullton CA 90085 1210.25 7500 15
260 Brookings Direct 452 Columbus Dr. Grove CA 90092 575 10000 30
334 The Everything 342 Magee St. Congaree CA 90097 2345.75 7500 45
386 Johnson's Dept 124 Main St. Northfield CA 90098 879.25 7500 30
440 Grove Historical 3456 Central Ave. Fullton CA 90085 345 5000 45
502 Cards and More 167 Hale St. Mesa CA 90104 5025.75 5000 15
586 Almondton 3345 Devon Ave. Almondton CA 90125 3456.75 15000 45
665 Cricket Gift Shop 372 Oxford St. Grove CA 90092 678.9 7500 30
713 Cress Store 12 Rising Sun Ave. Congaree CA 90097 4234.6 10000 15
796 Unique Gifts 786 Passmore St. Northfield CA 90098 124.75 7500 45
824 Kline's 945 Gilham St. Mesa CA 90104 2475.99 15000 30
893 All Season Gifts 382 Wildwood Ave. Fullton CA 90085 935.75 7500 15
SQL> select
2
SQL> select * from orders;
ORDER ORDER_DAT CUS
51608 12-OCT-15 126
51610 12-OCT-15 334
51613 13-OCT-15 386
51614 13-OCT-15 260
51617 15-OCT-15 586
51619 15-OCT-15 126
51623 15-OCT-15 586
51625 16-OCT-15 796
8 rows selected.
SQL> select * from order_line;
ORDER ITEM NUM_ORDERED QUOTED_PRICE
----- ---- ----------- ------------
51608 CD33 5 86.99
51610 KL78 25 10.95
51610 TR40 10 13.99
51613 DL51 5 104.95
51614 FD11 1 124.95
51617 NL89 4 115.99
51617 TW35 3 116.95
51619 FD11 2 121.95
51623 DR67 5 29.95
51623 FH24 12 36.95
51623 KD34 10 13.1
51625 MT03 8 45.79
12 rows selected.
SQL> select * from rep;
RE LAST_NAME FIRST_NAME STREET CITY ST POSTA COMMISSION RATE
15 Campos Rafael 724 Vinca Dr. Grove CA 90092 23457.5 .06
30 Gradey Megan 632 Liatris St. Fullton CA 90085 41317 .08
45 Tian Hui 1785 Tyler Ave. Northfield CA 90098 27789.25 .06
60 Sefton Janet 267 Oakley St. Congaree CA 90097 0 .06
SQL> select * from item;
ITEM DESCRIPTION ON_HAND CAT S PRICE
---- ------------------------------ ---------- --- - ----------
AH74 Patience 9 GME 3 22.99
BR23 Skittles 21 GME 2 29.99
CD33 Wood Block Set (48 piece) 36 TOY 1 89.49
DL51 Classic Railway Set 12 TOY 3 107.95
DR67 Giant Star Brain Teaser 24 PZL 2 31.95
DW23 Mancala 40 GME 3 50
FD11 Rocking Horse 8 TOY 3 124.95
FH24 Puzzle Gift Set 65 PZL 1 38.95
KA12 Cribbage Set 56 GME 3 75
KD34 Pentominoes Brain Teaser 60 PZL 2 14.95
KL78 Pick Up Sticks 110 GME 1 10.95
MT03 Zauberkasten Brain Teaser 45 PZL 1 45.79
NL89 Wood Block Set (62 piece) 32 TOY 3 119.75
TR40 Tic Tac Toe 75 GME 2 13.99
TW35 Fire Engine 30 TOY 2 118.95
15 rows selected.
1.Which customers did not order any GME Category items?
Explanation / Answer
To get the customers how did not order GME category items:-
select DISTINCT * from customer c
join(
select ol.cus from orders ol
join (
select o.order from order_line o
join (select * from item where cat!='GME')b
On o.ITEM=b.ITEM) og On ol.order=og.order) al On c.cus=al.cus
Please let me know in case of any doubt.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.