-
Notifications
You must be signed in to change notification settings - Fork 0
/
Chapter 03 - Joins - Exercises.sql
275 lines (249 loc) · 8.96 KB
/
Chapter 03 - Joins - Exercises.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
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
---------------------------------------------------------------------
-- T-SQL Fundamentals Fourth Edition
-- Chapter 03 - Joins
-- Exercises
-- © Itzik Ben-Gan
---------------------------------------------------------------------
-- 1
-- 1-1
-- Write a query that generates 5 copies out of each employee row
-- Tables involved: TSQLV6 database, Employees and Nums tables
--Desired output
empid firstname lastname n
----------- ---------- -------------------- -----------
1 Sara Davis 1
2 Don Funk 1
3 Judy Lew 1
4 Yael Peled 1
5 Sven Mortensen 1
6 Paul Suurs 1
7 Russell King 1
8 Maria Cameron 1
9 Patricia Doyle 1
1 Sara Davis 2
2 Don Funk 2
3 Judy Lew 2
4 Yael Peled 2
5 Sven Mortensen 2
6 Paul Suurs 2
7 Russell King 2
8 Maria Cameron 2
9 Patricia Doyle 2
1 Sara Davis 3
2 Don Funk 3
3 Judy Lew 3
4 Yael Peled 3
5 Sven Mortensen 3
6 Paul Suurs 3
7 Russell King 3
8 Maria Cameron 3
9 Patricia Doyle 3
1 Sara Davis 4
2 Don Funk 4
3 Judy Lew 4
4 Yael Peled 4
5 Sven Mortensen 4
6 Paul Suurs 4
7 Russell King 4
8 Maria Cameron 4
9 Patricia Doyle 4
1 Sara Davis 5
2 Don Funk 5
3 Judy Lew 5
4 Yael Peled 5
5 Sven Mortensen 5
6 Paul Suurs 5
7 Russell King 5
8 Maria Cameron 5
9 Patricia Doyle 5
(45 rows affected)
-- 1-2
-- Write a query that returns a row for each employee and day
-- in the range June 12, 2022 – June 16 2022.
-- Tables involved: TSQLV6 database, Employees and Nums tables
--Desired output
empid dt
----------- -----------
1 2022-06-12
1 2022-06-13
1 2022-06-14
1 2022-06-15
1 2022-06-16
2 2022-06-12
2 2022-06-13
2 2022-06-14
2 2022-06-15
2 2022-06-16
3 2022-06-12
3 2022-06-13
3 2022-06-14
3 2022-06-15
3 2022-06-16
4 2022-06-12
4 2022-06-13
4 2022-06-14
4 2022-06-15
4 2022-06-16
5 2022-06-12
5 2022-06-13
5 2022-06-14
5 2022-06-15
5 2022-06-16
6 2022-06-12
6 2022-06-13
6 2022-06-14
6 2022-06-15
6 2022-06-16
7 2022-06-12
7 2022-06-13
7 2022-06-14
7 2022-06-15
7 2022-06-16
8 2022-06-12
8 2022-06-13
8 2022-06-14
8 2022-06-15
8 2022-06-16
9 2022-06-12
9 2022-06-13
9 2022-06-14
9 2022-06-15
9 2022-06-16
(45 rows affected)
-- 2
-- Explain what’s wrong in the following query and provide a correct alternative
SELECT Customers.custid, Customers.companyname, Orders.orderid, Orders.orderdate
FROM Sales.Customers AS C
INNER JOIN Sales.Orders AS O
ON Customers.custid = Orders.custid;
-- 3
-- Return US customers, and for each customer the total number of orders
-- and total quantities.
-- Tables involved: TSQLV6 database, Customers, Orders and OrderDetails tables
--Desired output
custid numorders totalqty
----------- ----------- -----------
32 11 345
36 5 122
43 2 20
45 4 181
48 8 134
55 10 603
65 18 1383
71 31 4958
75 9 327
77 4 46
78 3 59
82 3 89
89 14 1063
(13 rows affected)
-- 4
-- Return customers and their orders including customers who placed no orders
-- Tables involved: TSQLV6 database, Customers and Orders tables
-- Desired output
custid companyname orderid orderdate
----------- --------------- ----------- -----------
85 Customer ENQZT 10248 2020-07-04
79 Customer FAPSM 10249 2020-07-05
34 Customer IBVRG 10250 2020-07-08
84 Customer NRCSK 10251 2020-07-08
...
73 Customer JMIKW 11074 2022-05-06
68 Customer CCKOT 11075 2022-05-06
9 Customer RTXGC 11076 2022-05-06
65 Customer NYUHS 11077 2022-05-06
22 Customer DTDMN NULL NULL
57 Customer WVAXS NULL NULL
(832 rows affected)
-- 5
-- Return customers who placed no orders
-- Tables involved: TSQLV6 database, Customers and Orders tables
-- Desired output
custid companyname
----------- ---------------
22 Customer DTDMN
57 Customer WVAXS
(2 rows affected)
-- 6
-- Return customers with orders placed on Feb 12, 2022 along with their orders
-- Tables involved: TSQLV6 database, Customers and Orders tables
-- Desired output
custid companyname orderid orderdate
----------- --------------- ----------- ----------
48 Customer DVFMB 10883 2022-02-12
45 Customer QXPPT 10884 2022-02-12
76 Customer SFOGW 10885 2022-02-12
(3 rows affected)
-- 7
-- Write a query that returns all customers, but matches
-- them with their respective orders only if they were placed on February 12, 2022
-- Tables involved: TSQLV6 database, Customers and Orders tables
-- Desired output
custid companyname orderid orderdate
----------- --------------- ----------- ----------
72 Customer AHPOP NULL NULL
58 Customer AHXHT NULL NULL
25 Customer AZJED NULL NULL
18 Customer BSVAR NULL NULL
91 Customer CCFIZ NULL NULL
68 Customer CCKOT NULL NULL
49 Customer CQRAA NULL NULL
24 Customer CYZTN NULL NULL
22 Customer DTDMN NULL NULL
48 Customer DVFMB 10883 2022-02-12
10 Customer EEALV NULL NULL
40 Customer EFFTC NULL NULL
85 Customer ENQZT NULL NULL
82 Customer EYHKM NULL NULL
79 Customer FAPSM NULL NULL
...
51 Customer PVDZC NULL NULL
52 Customer PZNLA NULL NULL
56 Customer QNIVZ NULL NULL
8 Customer QUHWH NULL NULL
67 Customer QVEPD NULL NULL
45 Customer QXPPT 10884 2022-02-12
7 Customer QXVLA NULL NULL
60 Customer QZURI NULL NULL
19 Customer RFNQC NULL NULL
9 Customer RTXGC NULL NULL
76 Customer SFOGW 10885 2022-02-12
69 Customer SIUIH NULL NULL
86 Customer SNXOJ NULL NULL
88 Customer SRQVM NULL NULL
54 Customer TDKEG NULL NULL
20 Customer THHDP NULL NULL
...
(91 rows affected)
-- 8
-- Explain why the following query isn’t a correct solution query for exercise 7.
SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
LEFT OUTER JOIN Sales.Orders AS O
ON O.custid = C.custid
WHERE O.orderdate = '20220212'
OR O.orderid IS NULL;
-- 9
-- Return all customers, and for each return a Yes/No value
-- depending on whether the customer placed an order on Feb 12, 2022
-- Tables involved: TSQLV6 database, Customers and Orders tables
-- Desired output
custid companyname HasOrderOn20220212
----------- --------------- ------------------
...
40 Customer EFFTC No
41 Customer XIIWM No
42 Customer IAIJK No
43 Customer UISOJ No
44 Customer OXFRU No
45 Customer QXPPT Yes
46 Customer XPNIK No
47 Customer PSQUZ No
48 Customer DVFMB Yes
49 Customer CQRAA No
50 Customer JYPSC No
51 Customer PVDZC No
52 Customer PZNLA No
53 Customer GCJSG No
...
(91 rows affected)