| 81 |
irasnyd |
1 |
TO ACCESS YOUR DATABASE (cs435):
|
|
|
2 |
mysql -u root -p cs435
|
|
|
3 |
|
|
|
4 |
|
|
|
5 |
PROBLEM 1:
|
|
|
6 |
--------------------------------------------------------------------------------
|
|
|
7 |
SELECT name FROM members
|
|
|
8 |
WHERE name NOT IN
|
|
|
9 |
(SELECT name FROM orders
|
|
|
10 |
WHERE item='Lettuce')
|
|
|
11 |
;
|
|
|
12 |
|
|
|
13 |
PROBLEM 2:
|
|
|
14 |
--------------------------------------------------------------------------------
|
|
|
15 |
SELECT name,address FROM members
|
|
|
16 |
WHERE name IN
|
|
|
17 |
(SELECT name FROM orders
|
|
|
18 |
WHERE item IN
|
|
|
19 |
(SELECT item FROM suppliers
|
|
|
20 |
WHERE sname='Sunshine Produce')
|
|
|
21 |
)
|
|
|
22 |
;
|
|
|
23 |
|
|
|
24 |
PROBLEM 3:
|
|
|
25 |
--------------------------------------------------------------------------------
|
|
|
26 |
SELECT sname,item,price FROM suppliers
|
|
|
27 |
WHERE item NOT IN
|
|
|
28 |
(SELECT item FROM orders
|
|
|
29 |
WHERE name='Brooks, B.')
|
|
|
30 |
;
|
|
|
31 |
|
|
|
32 |
PROBLEM 4:
|
|
|
33 |
--------------------------------------------------------------------------------
|
|
|
34 |
SELECT sname FROM suppliers
|
|
|
35 |
WHERE item IN
|
|
|
36 |
(SELECT item FROM orders
|
|
|
37 |
WHERE name='Brooks, B.')
|
|
|
38 |
GROUP BY sname
|
|
|
39 |
HAVING COUNT(sname) =
|
|
|
40 |
(SELECT COUNT(DISTINCT item) FROM orders
|
|
|
41 |
WHERE name='Brooks, B.')
|
|
|
42 |
;
|
|
|
43 |
|
|
|
44 |
---------------
|
|
|
45 |
Using JOIN ----
|
|
|
46 |
---------------
|
|
|
47 |
SELECT s.sname FROM suppliers s, orders o
|
|
|
48 |
WHERE o.item=s.item
|
|
|
49 |
AND o.name='Brooks, B.'
|
|
|
50 |
GROUP BY s.sname
|
|
|
51 |
HAVING COUNT(*)=(SELECT COUNT(*) FROM orders
|
|
|
52 |
WHERE name='Brooks, B.');
|
|
|
53 |
|
|
|
54 |
PROBLEM 5:
|
|
|
55 |
--------------------------------------------------------------------------------
|
|
|
56 |
SELECT DISTINCT sname FROM suppliers
|
|
|
57 |
WHERE item IN
|
|
|
58 |
(SELECT item FROM orders
|
|
|
59 |
WHERE name='Brooks, B.')
|
|
|
60 |
;
|
|
|
61 |
|
|
|
62 |
PROBLEM 6:
|
|
|
63 |
--------------------------------------------------------------------------------
|
|
|
64 |
SELECT item,AVG(price) FROM suppliers
|
|
|
65 |
WHERE item IN
|
|
|
66 |
(SELECT DISTINCT item FROM suppliers)
|
|
|
67 |
GROUP BY item;
|
|
|
68 |
|
|
|
69 |
PROBLEM 7: CHECK AGAIN
|
|
|
70 |
--------------------------------------------------------------------------------
|
|
|
71 |
SELECT sname,item FROM suppliers;
|
|
|
72 |
|
|
|
73 |
PROBLEM 8: HERE IS ONE POSSIBLE SOLUTION, USING "AS"
|
|
|
74 |
--------------------------------------------------------------------------------
|
|
|
75 |
SELECT name FROM members
|
|
|
76 |
WHERE name IN
|
|
|
77 |
(SELECT name FROM
|
|
|
78 |
(SELECT name,COUNT(name) FROM orders
|
|
|
79 |
GROUP BY name HAVING COUNT(name)>=2)
|
|
|
80 |
AS temp_tbl1)
|
|
|
81 |
;
|
|
|
82 |
|
|
|
83 |
PROBLEM 9:
|
|
|
84 |
--------------------------------------------------------------------------------
|
|
|
85 |
SELECT sname,COUNT(sname) FROM suppliers
|
|
|
86 |
GROUP BY sname;
|
|
|
87 |
|
|
|
88 |
PROBLEM 10: HERE IS ONE POSSIBLE SOLUTION, USING "AS"
|
|
|
89 |
--------------------------------------------------------------------------------
|
|
|
90 |
SELECT sname FROM suppliers
|
|
|
91 |
WHERE item IN
|
|
|
92 |
(SELECT item FROM
|
|
|
93 |
(SELECT item,COUNT(item) FROM orders
|
|
|
94 |
GROUP BY item
|
|
|
95 |
HAVING COUNT(item)>1)
|
|
|
96 |
AS temp_tbl1)
|
|
|
97 |
;
|
|
|
98 |
|
|
|
99 |
PROBLEM 11:
|
|
|
100 |
--------------------------------------------------------------------------------
|
|
|
101 |
SELECT DISTINCT sname FROM suppliers
|
|
|
102 |
WHERE sname IN
|
|
|
103 |
(SELECT sname FROM suppliers
|
|
|
104 |
WHERE item='Lettuce')
|
|
|
105 |
AND sname IN
|
|
|
106 |
(SELECT sname FROM suppliers
|
|
|
107 |
WHERE item='Whey')
|
|
|
108 |
;
|
|
|
109 |
|
|
|
110 |
PROBLEM 12:
|
|
|
111 |
--------------------------------------------------------------------------------
|
|
|
112 |
SELECT DISTINCT name FROM members
|
|
|
113 |
WHERE name IN
|
|
|
114 |
(SELECT name FROM orders
|
|
|
115 |
WHERE item='Lettuce')
|
|
|
116 |
OR name IN
|
|
|
117 |
(SELECT name FROM orders
|
|
|
118 |
WHERE item='Whey')
|
|
|
119 |
;
|
|
|
120 |
|
|
|
121 |
PROBLEM 13:
|
|
|
122 |
--------------------------------------------------------------------------------
|
|
|
123 |
SELECT name FROM members
|
|
|
124 |
WHERE name NOT IN
|
|
|
125 |
(SELECT name FROM orders
|
|
|
126 |
WHERE item IN
|
|
|
127 |
(SELECT DISTINCT item FROM orders
|
|
|
128 |
WHERE item<>'Whey')
|
|
|
129 |
)
|
|
|
130 |
AND name IN
|
|
|
131 |
(SELECT DISTINCT name FROM orders)
|
|
|
132 |
;
|
|
|
133 |
|