-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathSQL Exercises - SUBQUERIES on Sales Database.sql
558 lines (408 loc) · 17.7 KB
/
SQL Exercises - SUBQUERIES on Sales Database.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
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
/*** SQL Exercises - SUBQUERIES on Sales Database ***/
-- https://www.w3resource.com/sql-exercises/subqueries/index.php
Table: salesman
salesman_id name city commission
----------- ---------- ---------- ----------
5001 James Hoog New York 0.15
5002 Nail Knite Paris 0.13
5005 Pit Alex London 0.11
5006 Mc Lyon Paris 0.14
5003 Lauson Hen 0.12
5007 Paul Adam Rome 0.13
Table: customer
customer_id cust_name city grade salesman_id
----------- ------------ ---------- ---------- -----------
3002 Nick Rimando New York 100 5001
3005 Graham Zusi California 200 5002
3001 Brad Guzan London 5005
3004 Fabian Johns Paris 300 5006
3007 Brad Davis New York 200 5001
3009 Geoff Camero Berlin 100 5003
3008 Julian Green London 300 5002
3003 Jozy Altidor Moscow 200 5007
Table: orders
ord_no purch_amt ord_date customer_id salesman_id
---------- ---------- ---------- ----------- -----------
70001 150.5 2012-10-05 3005 5002
70009 270.65 2012-09-10 3001 5005
70002 65.26 2012-10-05 3002 5001
70004 110.5 2012-08-17 3009 5003
70007 948.5 2012-09-10 3005 5002
70005 2400.6 2012-07-27 3007 5001
70008 5760 2012-09-10 3002 5001
70010 1983.43 2012-10-10 3004 5006
70003 2480.4 2012-10-10 3009 5003
70012 250.45 2012-06-27 3008 5002
70011 75.29 2012-08-17 3003 5007
70013 3045.6 2012-04-25 3002 5001
Table: company_mast
COM_ID COM_NAME
------ -------------
11 Samsung
12 iBall
13 Epsion
14 Zebronics
15 Asus
16 Frontech
Table: item_mast
PRO_ID PRO_NAME PRO_PRICE PRO_COM
------- ------------------------- ---------- ----------
101 Mother Board 3200 15
102 Key Board 450 16
103 ZIP drive 250 14
104 Speaker 550 16
105 Monitor 5000 11
106 DVD drive 900 12
107 CD drive 800 12
108 Printer 2600 13
109 Refill cartridge 350 13
110 Mouse 250 12
Table: emp_department
DPT_CODE DPT_NAME DPT_ALLOTMENT
-------- --------------- -------------
57 IT 65000
63 Finance 15000
47 HR 240000
27 RD 55000
89 QC 75000
Table: emp_details
EMP_IDNO EMP_FNAME EMP_LNAME EMP_DEPT
--------- --------------- --------------- ----------
127323 Michale Robbin 57
526689 Carlos Snares 63
843795 Enric Dosio 57
328717 Jhon Snares 63
444527 Joseph Dosni 47
659831 Zanifer Emily 47
847674 Kuleswar Sitaraman 57
748681 Henrey Gabriel 47
555935 Alex Manuel 57
539569 George Mardy 27
733843 Mario Saule 63
631548 Alan Snappy 27
839139 Maria Foster 57
/* 1. Write a query to display all the orders from the orders table issued by the salesman 'Paul Adam'. */
SELECT *
FROM orders
WHERE salesman_id = (SELECT salesman_id
FROM salesman
WHERE name = 'Paul Adam');
/* 2. Write a query to display all the orders for the salesman who belongs to the city London. *.'
SELECT *
FROM orders
WHERE salesman_id = (SELECT salesman_id
FROM salesman
WHERE city = 'London');
/* 3. Write a query to find all the orders issued against the salesman who may works for customer whose id is 3007. */
SELECT *
FROM orders
WHERE salesman_id = (SELECT salesman_id
FROM orders
WHERE customer_id = 3007);
/* 4. Write a query to display all the orders which values are greater than the average order value for 10th October 2012. */
SELECT *
FROM orders
WHERE purch_amt > (SELECT AVG(purch_amt)
FROM orders
WHERE ord_date = '2012-10-10');
/* 5. Write a query to find all orders attributed to a salesman in New York. */
SELECT *
FROM orders
WHERE salesman_id IN (SELECT salesman_id
FROM salesman
WHERE city = 'New York');
/* 6. Write a query to display the commission of all the salesmen servicing customers in Paris. */
SELECT commission
FROM salesman
WHERE salesman_id IN (SELECT salesman_id
FROM customer
WHERE city = 'Paris');
/* 7. Write a query to display all the customers whose id is 2001 bellow the salesman ID of Mc Lyon. */
SELECT *
FROM customer
WHERE customer_id = (SELECT salesman_id - 2001
FROM salesman
WHERE name = 'Mc Lyon');
/* 8. Write a query to count the customers with grades above New York's average. */
SELECT COUNT(*)
FROM customer
WHERE grade > (SELECT AVG(grade)
FROM customer
WHERE city = 'New York');
/* 9. Write a query to display all customers with orders on October 5, 2012. */
/* Note: These set of exercises focus on subqueries, so I wrote the query below. The 2nd query also works and displays order information as well. */
SELECT *
FROM customer
WHERE customer_id IN (SELECT customer_id
FROM orders
WHERE ord_date = '2012-10-05');
SELECT *
FROM customer a, orders b
WHERE a.customer_id = b.customer_id
AND b.ord_date = '2012-10-05';
/* 10. Write a query to display all the customers with orders issued on date 17th August, 2012. */
/* Note: These set of exercises focus on subqueries, so I wrote the query below. The 2nd query also works and displays order information as well. */
SELECT *
FROM customer
WHERE customer_id IN (SELECT customer_id
FROM orders
WHERE ord_date = '2012-08-17');
SELECT b.*,
a.cust_name
FROM customer a, orders b
WHERE a.customer_id = b.customer_id
AND b.ord_date = '2012-08-17';
/* 11. Write a query to find the name and numbers of all salesmen who had more than one customer. */
SELECT salesman_id,
name
FROM salesman
WHERE salesman_id IN (SELECT salesman_id
FROM customer
GROUP BY salesman_id
HAVING COUNT(*) > 1);
SELECT salesman_id,
name
FROM salesman a
WHERE 1 < (SELECT COUNT(*)
FROM customer
WHERE salesman_id = a.salesman_id);
/* 12. Write a query to find all orders with order amounts which are above-average amounts for their customers. */
SELECT *
FROM orders a
WHERE purch_amt > (SELECT AVG(purch_amt)
FROM orders b
WHERE b.customer_id = a.customer_id);
/* 13. Write a queries to find all orders with order amounts which are on or above-average amounts for their customers. */
SELECT *
FROM orders a
WHERE purch_amt >= (SELECT AVG(purch_amt)
FROM orders b
WHERE b.customer_id = a.customer_id);
/* 14. Write a query to find the sums of the amounts from the orders table, grouped by date, eliminating all those dates where the sum was not at least 1000.00 above the maximum order amount for that date. */
SELECT ord_date,
SUM(purch_amt)
FROM orders a
GROUP BY ord_date
HAVING SUM(purch_amt) > (SELECT MAX(purch_amt) + 1000
FROM orders b
WHERE a.ord_date = b.ord_date);
/* 15. Write a query to extract the data from the customer table if and only if one or more of the customers in the customer table are located in London. */
SELECT *
FROM customer
WHERE EXISTS (SELECT *
FROM customer
WHERE city = 'London');
/* 16. Write a query to find the salesmen who have multiple customers. */
SELECT *
FROM salesman
WHERE salesman_id IN (SELECT salesman_id
FROM customer
GROUP BY salesman_id
HAVING COUNT(*) > 1);
SELECT *
FROM salesman
WHERE salesman_id IN (SELECT DISTINCT salesman_id
FROM customer a
WHERE EXISTS (SELECT *
FROM customer b
WHERE b.salesman_id=a.salesman_id
AND b.cust_name <> a.cust_name));
/* 17. Write a query to find all the salesmen who worked for only one customer. */
SELECT *
FROM salesman
WHERE salesman_id IN (SELECT salesman_id
FROM customer
GROUP BY salesman_id
HAVING COUNT(customer_id) = 1);
/* 18. Write a query that extract the rows of all salesmen who have customers with more than one orders. */
SELECT *
FROM salesman
WHERE salesman_id IN (SELECT salesman_id
FROM customer
WHERE customer_id IN (SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1));
SELECT *
FROM salesman a
WHERE EXISTS (SELECT *
FROM customer b
WHERE a.salesman_id=b.salesman_id
AND 1 < (SELECT COUNT(*)
FROM orders
WHERE orders.customer_id=b.customer_id));
/* 19. Write a query to find salesmen with all information who lives in the city where any of the customers lives. */
SELECT *
FROM salesman
WHERE city IN (SELECT city
FROM customer);
SELECT *
FROM salesman
WHERE city = ANY (SELECT city
FROM customer);
/* 20. Write a query to find all the salesmen for whom there are customers that follow them. */
SELECT *
FROM salesman
WHERE city IN (SELECT city
FROM customer);
/* 21. Write a query to display the salesmen which name are alphabetically lower than the name of the customers. */
SELECT *
FROM salesman a
WHERE EXISTS (SELECT *
FROM customer b
WHERE a.name < b.cust_name);
/* 22. Write a query to display the customers who have a greater gradation than any customer who belongs to the alphabetically lower than the city New York. */
SELECT *
FROM customer
WHERE grade > ANY (SELECT grade
FROM customer
WHERE city < 'New York');
/* 23. Write a query to display all the orders that had amounts that were greater than at least one of the orders on September 10th 2012. */
SELECT *
FROM orders
WHERE purch_amt > ANY (SELECT purch_amt
FROM orders
WHERE ord_date = '2012-09-10');
/* 24. Write a query to find all orders with an amount smaller than any amount for a customer in London. */
SELECT *
FROM orders
WHERE purch_amt < ANY (SELECT purch_amt
FROM orders
WHERE customer_id IN (SELECT customer_id
FROM customer
WHERE city = 'London'));
SELECT *
FROM orders
WHERE purch_amt < ANY (SELECT purch_amt
FROM orders a, customer b
WHERE a.customer_id = b.customer_id
AND b.city = 'London');
/* 25. Write a query to display all orders with an amount smaller than the maximum amount for a customers in London. */
SELECT *
FROM orders
WHERE purch_amt < (SELECT MAX(purch_amt)
FROM orders
WHERE customer_id IN (SELECT customer_id
FROM customer
WHERE city = 'London'));
SELECT *
FROM orders
WHERE purch_amt < (SELECT MAX(purch_amt)
FROM orders a, customer b
WHERE a.customer_id = b.customer_id
AND b.city = 'London');
/* 26. Write a query to display only those customers whose grade are, in fact, higher than every customer in New York. */
SELECT *
FROM customer
WHERE grade > ALL (SELECT grade
FROM customer
WHERE city = 'New York');
/* 27. Write a query to find only those customers whose grade are, higher than every customer to the city New York. */
SELECT *
FROM customer
WHERE grade > ALL (SELECT grade
FROM customer
WHERE city = 'New York');
/* 28. Write a query to get all the information for those customers whose grade is not as the grade of customer who belongs to the city London */
SELECT *
FROM customer
WHERE grade != ANY (SELECT grade
FROM customer
WHERE city = 'London');
/* 29. Write a query to find all those customers whose grade are not as the grade, belongs to the city Paris. */
SELECT *
FROM customer
WHERE grade != ANY (SELECT grade
FROM customer
WHERE city = 'Paris');
SELECT *
FROM customer
WHERE grade NOT IN (SELECT grade
FROM customer
WHERE city = 'Paris');
/* 30. Write a query to find all those customers who hold a different grade than any customer of the city Dallas. */
SELECT *
FROM customer
WHERE grade NOT IN (SELECT grade
FROM customer
WHERE city = 'Dallas');
SELECT *
FROM customer
WHERE NOT grade = ANY (SELECT grade
FROM customer
WHERE city = 'Dallas');
/* 31. Write a SQL query to find the average price of each manufacturer's products along with their name. */
SELECT AVG(pro_price),
com_name
FROM item_mast, company_mast
WHERE item_mast.pro_com = company_mast.com_id
GROUP BY com_name;
/* 32. Write a SQL query to display the average price of the products which is more than or equal to 350 along with their names. */
SELECT c.com_name,
AVG(i.pro_price)
FROM item_mast i
INNER JOIN company_mast c
ON i.pro_com=c.com_id
GROUP BY c.com_name
HAVING AVG(i.pro_price) >= 350;
SELECT AVG(pro_price) AS "Average Price",
company_mast.com_name AS "Company"
FROM item_mast, company_mast
WHERE item_mast.pro_com = company_mast.com_id
GROUP BY company_mast.com_name
HAVING AVG(pro_price) >= 350;
/* 33. Write a SQL query to display the name of each company, price for their most expensive product along with their Name. */
SELECT c.com_name,
i.pro_name,
i.pro_price
FROM item_mast i
INNER JOIN company_mast c
ON i.pro_com = c.com_id
AND i.pro_price = (SELECT MAX(i.pro_price)
FROM item_mast i
WHERE i.pro_com = c.com_id);
SELECT c.com_name,
i.pro_name,
i.pro_price
FROM item_mast i, company_mast c
WHERE i.pro_com = c.com_id
AND i.pro_price = (SELECT MAX(i.pro_price)
FROM item_mast i
WHERE i.pro_com = c.com_id);
/* 34. Write a query in SQL to find all the details of employees whose last name is Gabriel or Dosio. */
SELECT *
FROM emp_details
WHERE emp_lname IN ('Gabriel', 'Dosio');
/* 35. Write a query in SQL to display all the details of employees who works in department 89 or 63. */
SELECT *
FROM emp_details
WHERE emp_dept IN (89, 63);
/* 36. Write a query in SQL to display the first name and last name of employees working for the department which allotment amount is more than Rs.50000. */
SELECT emp_fname,
emp_lname
FROM emp_details
WHERE emp_dept IN (SELECT dpt_code
FROM emp_department
WHERE dpt_allotment > 50000);
/* 37. Write a query in SQL to find the departments which sanction amount is larger than the average sanction amount of all the departments. */
SELECT *
FROM emp_department
WHERE dpt_allotment > (SELECT AVG(dpt_allotment)
FROM emp_department);
/* 38. Write a query in SQL to find the names of departments with more than two employees are working. */
SELECT dpt_name
FROM emp_department
WHERE dpt_code IN (SELECT emp_dept
FROM emp_details
GROUP BY emp_dept
HAVING COUNT(*) > 2);
/* 39. Write a query in SQL to find the first name and last name of employees working for departments which sanction amount is second lowest. */
SELECT emp_fname,
emp_lname
FROM emp_details
WHERE emp_dept IN (SELECT dpt_code
FROM emp_department
WHERE dpt_allotment = (SELECT MIN(dpt_allotment)
FROM emp_department
WHERE dpt_allotment > (SELECT MIN(dpt_allotment)
FROM emp_department)));