-
Notifications
You must be signed in to change notification settings - Fork 0
/
Subquery_Challenge.sql
190 lines (173 loc) · 3.68 KB
/
Subquery_Challenge.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
-- #TASK: Return the following product details for the cheapest product(s) in the oes.products table:
-- product_id
-- product_name
-- list_price
-- category_id
SELECT
product_id,
product_name,
list_price,
category_id
FROM
oes.products
WHERE
list_price = (
SELECT
MIN(list_price)
FROM oes.products
);
-- using TOP
SELECT TOP(1) WITH TIES
product_id,
product_name,
list_price,
category_id
FROM
oes.products
ORDER BY
list_price;
-- #TASK: Use a correlated subquery to return the following product details for the cheapest product(s) in each product category as given by the category_id column:
-- product_id
-- product_name
-- list_price
-- category_id
SELECT
product_id,
product_name,
list_price,
category_id
FROM
oes.products p1
WHERE
list_price = (
SELECT
MIN(list_price)
FROM
oes.products p2
WHERE
p1.category_id = p2.category_id
);
-- #TASK: Return the same result as challenge 2 i.e. the cheapest product(s) in each product category except this time by using an inner join to a derived table.
SELECT
p.product_id,
p.product_name,
p.list_price,
p.category_id
FROM
oes.products p
INNER JOIN
(
SELECT
category_id,
MIN(list_price) as min_list_price
FROM
oes.products
GROUP BY
category_id
) p2
ON p.category_id = p2.category_id
AND p.list_price = p2.min_list_price;
-- #TASK: Return the same result as challenge 2 and 3 i.e. the cheapest product(s) in each product category except this time by using a common table expression.
WITH cheapest_product_by_category
AS
(
SELECT
category_id,
MIN(list_price) as min_list_price
FROM
oes.products
GROUP BY
category_id
)
SELECT
p.product_id,
p.product_name,
p.list_price,
p.category_id
FROM
oes.products p
INNER JOIN
cheapest_product_by_category p2
ON p.category_id = p2.category_id
AND p.list_price = p2.min_list_price;
-- #TASK: Repeat challenge 4, except this time include the product category name as given in the oes.product_categories table.
WITH cheapest_product_by_category
AS
(
SELECT
category_id,
MIN(list_price) as min_list_price
FROM
oes.products
GROUP BY
category_id
)
SELECT
p.product_id,
p.product_name,
p.list_price,
p.category_id,
pc.category_name
FROM
oes.products p
INNER JOIN oes.product_categories pc ON (p.category_id = pc.category_id)
INNER JOIN cheapest_product_by_category p2 ON (p.category_id = p2.category_id AND p.list_price = p2.min_list_price)
-- #TASK: Use the NOT IN operator to return all employees who have never been the salesperson for any customer order. Include the following columns from hcm.employees:
-- employee_id
-- first_name
-- last_name
SELECT
employee_id,
first_name,
last_name
FROM
hcm.employees
WHERE
employee_id NOT IN
(
SELECT
employee_id
FROM
oes.orders
WHERE
employee_id IS NOT NULL
);
-- #TASK: Return the same result as challenge 6, except use WHERE NOT EXISTS
SELECT
e.employee_id,
e.first_name,
e.last_name
FROM
hcm.employees e
WHERE
NOT EXISTS
(
SELECT *
FROM
oes.orders O
WHERE
o.employee_id = e.employee_id
);
-- #TASK: Return unique customers who have ordered the 'PBX Smart Watch 4'. Include:
-- customer-id
-- first_name
-- last_name
-- email
SELECT
c.customer_id,
c.first_name,
c.last_name,
c.email
FROM
oes.customers c
WHERE
c.customer_id IN (
SELECT
o.customer_id
FROM
oes.orders o
JOIN oes.order_items oi
ON oi.order_id = o.order_id
JOIN oes.products p
ON p.product_id = oi.product_id
WHERE product_name = 'PBX Smart Watch 4')