SQL Playbook

All the queries in the SQL Playbook reference are available for copying and pasting so that you can run them without needing to retype..

1. A slow full toss

• Find the names of employees in the Marketing department.

2. Skinning a cat

• Find the items sold by a department on the second floor.

3. Another full toss

• Find the names of items sold on floors other than the second floor.

SELECT DISTINCT itemname FROM qsale, qdept

WHERE qsale.deptname = qdept.deptname

4. Subtracting from all

• Find the items sold by no department on the second floor.

SELECT DISTINCT itemname FROM qsale

WHERE itemname NOT IN

(SELECT DISTINCT itemname FROM qsale, qdept

WHERE qsale.deptname = qdept.deptname

5. Dividing

• Find the items sold by all departments on the second floor.

SELECT DISTINCT itemname FROM qitem

WHERE NOT EXISTS

(SELECT * FROM qdept WHERE deptfloor = 2

AND NOT EXISTS

(SELECT * FROM qsale

WHERE qsale.itemname = qitem.itemname

AND qsale.deptname = qdept.deptname));

SELECT qsale.itemname FROM qsale, qdept

WHERE qsale.deptname = qdept.deptname

and qdept.deptfloor = 2

GROUP BY qsale.itemname

HAVING count(distinct qdept.deptname) =

(SELECT count(distinct deptname)

from qdept where deptfloor = 2);

6. At least some number

• Find the items sold by at least two departments on the second floor.

SELECT itemname FROM qsale, qdept

WHERE qsale.deptname = qdept.deptname and deptfloor = 2

GROUP BY itemname

7. A friendly IN for an SQL traveler

• Find the salary of Clare's manager.

SELECT empfname, empsalary FROM qemp

WHERE empno IN

8. Joining a table with itself

• Find numbers and names of those employees who make more than their manager.

SELECT wrk.empno, wrk.empfname FROM qemp wrk, qemp boss

WHERE wrk.bossno = boss.empno

9. A combination of subtract from all and a self-join

• Find the departments where all the employees earn less than their manager.

SELECT DISTINCT deptname FROM qemp

WHERE deptname <> 'Management'

AND deptname NOT IN

(SELECT wrk.deptname FROM qemp wrk, qemp boss

WHERE wrk.bossno = boss.empno

AND wrk.empsalary >= boss.empsalary);

10. Self-join with GROUP BY

• Count the number of direct employees of each manager.

SELECT boss.empno, boss.empfname, COUNT(*)

FROM qemp wrk, qemp boss

WHERE wrk.bossno = boss.empno

11. A self-join with two matching conditions

• Find the names of employees who are in the same department as their manager (as an employee). Report the name of the employee, the department, and the boss's name.

SELECT wrk.empfname, wrk.deptname, boss.empfname

FROM qemp wrk, qemp boss

WHERE wrk.bossno = boss.empno

12. Averaging with GROUP BY

• List the departments having an average salary over $25,000.

SELECT deptname, AVG(empsalary) FROM qemp

GROUP BY deptname

13. inner query GROUP BY and HAVING

• List the departments where the average salary of the employees of each manager is more than $25,000.

SELECT wrk.deptname, AVG(wrk.empsalary)

FROM qemp wrk

WHERE wrk.empno NOT IN

(SELECT qdept.empno FROM qdept

WHERE wrk.empno = qdept.empno

AND wrk.deptname = qdept.deptname)

GROUP BY wrk.deptname

14. An IN with GROUP BY and COUNT

• List the name and salary of the managers with more than two employees.

SELECT empfname, empsalary FROM qemp

WHERE empno IN

(SELECT bossno FROM qemp

15. A self-join with some conditions

• List the name, salary, and manager of the employees of the Marketing department who have a salary over $25,000.

SELECT wrk.empfname, wrk.empsalary, boss.empfname

FROM qemp wrk, qemp boss

WHERE wrk.bossno = boss.empno

AND wrk.deptname = 'Marketing'

16. Making comparisons

• List the names of the employees who earn more than any employee in the Marketing department.

SELECT empfname, empsalary FROM qemp

WHERE empsalary >

(SELECT MAX(empsalary) FROM qemp

17. An IN with GROUP BY and SUM

• Among all the departments with total salary greater than $25,000, find the departments that sell Stetsons.

18. A double divide!

• List the items delivered by every supplier that delivers all items of type N.

SELECT DISTINCT itemname FROM qdel del

WHERE NOT EXISTS

(SELECT * FROM qspl

WHERE NOT EXISTS

(SELECT * FROM qitem WHERE itemtype = 'N'

AND NOT EXISTS

(SELECT * FROM qdel

WHERE qdel.itemname = qitem.itemname

AND qdel.splno = qspl.splno))

AND NOT EXISTS

(SELECT * FROM qdel WHERE qdel.itemname = del.itemname

AND qdel.splno = qspl.splno));

19. A slam dunk

• Find the suppliers that deliver compasses.

SELECT DISTINCT qspl.splno, splname FROM qspl, qdel

20. A 6-inch putt for a birdie

• Find the suppliers that do not deliver compasses.

SELECT splno, splname FROM qspl

WHERE splno NOT IN

21. Making the count

• Find the suppliers that deliver both compasses and an item other than compasses.

SELECT DISTINCT qdel.splno, splname FROM qspl, qdel

WHERE qdel.splno = qspl.splno

AND itemname <> 'Compass'

AND qdel.splno IN

22. Minus and divide

• List the departments that have not recorded a sale for all the items oftype N.

SELECT deptname FROM qdept WHERE deptname NOT IN

(SELECT deptname FROM qdept

WHERE NOT EXISTS

(SELECT * FROM qitem WHERE itemtype = 'N'

AND NOT EXISTS

(SELECT * FROM qsale

WHERE qsale.deptname = qdept.deptname and

23. Division with copies

• List the departments that have at least one sale of all the items delivered to them.

SELECT DISTINCT deptname FROM qdel del1

WHERE NOT EXISTS

(SELECT * FROM qdel del2

WHERE del2.deptname = del1.deptname

AND NOT EXISTS

(SELECT * FROM qsale

where del2.itemname = qsale.itemname

24. A difficult pairing

• List the supplier-department pairs where the department sells all items delivered to it by the supplier.

SELECT splname, deptname FROM qdel del1, qspl

WHERE del1.splno = qspl.splno

AND NOT EXISTS

(SELECT * FROM qdel

WHERE qdel.deptname = del1.deptname

AND qdel.splno = del1.splno

AND itemname NOT IN

(SELECT itemname FROM qsale

25. Two divides and an intersection

• List the items delivered to all departments by all suppliers.

SELECT itemname FROM qitem

WHERE NOT EXISTS

(SELECT * FROM qspl

WHERE NOT EXISTS

(SELECT * FROM qdel

where qdel.itemname = qitem.itemname

and qdel.splno = qspl.splno))

AND NOT EXISTS

(SELECT * FROM qdept WHERE deptname

NOT IN ('Management', 'Marketing', 'Personnel','Accounting', 'Purchasing')

AND NOT EXISTS

(SELECT * FROM qdel

where qdel.itemname = qitem.itemname

26. A divide with a matching condition

• List the items sold only by departments that sell all the items delivered to them.

SELECT DISTINCT itemname FROM qsale sale

WHERE deptname IN

(SELECT deptname FROM qdept dept1

WHERE NOT EXISTS

(SELECT * FROM qdel

WHERE qdel.deptname = dept1.deptname

AND itemname NOT IN

(SELECT itemname FROM qsale

WHERE qsale.deptname = dept1.deptname)))

AND NOT EXISTS

(SELECT * FROM qsale

WHERE itemname = sale.itemname

AND deptname NOT IN

(SELECT deptname FROM qdept dept2

WHERE NOT EXISTS

(SELECT * FROM qdel

WHERE qdel.deptname = dept2.deptname

AND itemname NOT IN

(SELECT itemname FROM qsale

27. Restricted divide

• Who are the suppliers that deliver all the items of type N?

SELECT splno, splname FROM qspl

WHERE NOT EXISTS

(SELECT * FROM qitem WHERE itemtype = 'N'

AND NOT EXISTS

(SELECT * FROM qdel

where qdel.splno = qspl.splno

28. A NOT IN variation on divide

• List the suppliers that deliver only the items sold by the Books department.

SELECT splname FROM qspl

WHERE splno IN (SELECT splno FROM qdel)

AND NOT EXISTS

(SELECT * FROM qdel

WHERE qdel.splno = qspl.splno

AND itemname NOT IN

(SELECT itemname FROM qsale

29. All and only

• List the suppliers that deliver all and only the items sold by the Equipment department.

SELECT splname FROM qspl

WHERE NOT EXISTS

(SELECT * FROM qsale

WHERE deptname = 'Equipment'

AND itemname NOT IN

(SELECT itemname FROM qdel

WHERE qdel.splno = qspl.splno))

AND NOT EXISTS

(SELECT * FROM qdel

WHERE qdel.splno = qspl.splno

AND itemname NOT IN

(SELECT itemname FROM qsale

30. Divide with an extra condition

• List the suppliers that deliver every item of type C to the same department on the second floor.

SELECT splname FROM qspl

WHERE EXISTS (SELECT * FROM qdept

WHERE deptfloor = 2

AND NOT EXISTS (SELECT * FROM qitem

WHERE itemtype = 'C'

AND NOT EXISTS (SELECT * FROM qdel

WHERE qdel.splno = qspl.splno

AND qdel.itemname = qitem.itemname

31. At least some COUNT

• List the suppliers that deliver at least two items of type N to departments.

SELECT qspl.splno, splname FROM qdel, qspl, qitem

WHERE itemtype = 'n'

AND qdel.splno = qspl.splno

AND qdel.itemname = qitem.itemname

GROUP BY qspl.splno, splname

32. Double divide with a restriction

• List the suppliers that deliver all the items of type B to departments on the second floor who sell all the items of type R.

CREATE VIEW v32 AS

(SELECT deptname FROM qdept

WHERE deptfloor = 2

AND NOT EXISTS (SELECT * FROM qitem

WHERE itemtype = 'R'

AND NOT EXISTS (SELECT * FROM qsale

WHERE qsale.itemname = qitem.itemname

33. Triple divide with an intersection

• List the suppliers that deliver all the items of type B to the departments that also sell all the items of type N.

CREATE VIEW v33a AS

(SELECT deptname FROM qdept

WHERE NOT EXISTS (SELECT * FROM qitem

WHERE itemtype = 'N'

AND NOT EXISTS (SELECT * FROM qsale

WHERE qsale.deptname = qdept.deptname

34. An easy one COUNT

• List the items delivered by exactly one supplier (i.e., list the items always delivered by the same supplier).

SELECT itemname FROM qdel

35. The only one

• List the supplier and the item, where the supplier is the only deliverer of some item.

SELECT DISTINCT qspl.splno, splname, itemname

FROM qspl, qdel del1

WHERE qspl.splno = del1.splno

AND itemname NOT IN

(SELECT itemname FROM qdel

36. At least some number

• List the suppliers that deliver at least 10 items.

SELECT qspl.splno, splname FROM qdel, qspl

WHERE qdel.splno = qspl.splno

GROUP BY qspl.splno, splname

37. A three-table join

• For each item, give its type, the departments that sell the item, and the floor location of these departments.

SELECT qitem.itemname, itemtype, qdept.deptname, deptfloor

FROM qitem, qsale, qdept

WHERE qsale.itemname = qitem.itemname

38. Using NOT IN like NOT EXISTS

• List the departments for which each item delivered to the department isdelivered to some other department as well.

SELECT DISTINCT deptname FROM qdel del1

WHERE NOT EXISTS

(SELECT * FROM qdel del2

WHERE del2.deptname = del1.deptname

AND itemname NOT IN

(SELECT itemname FROM qdel del3

39. Minus after GROUP By

• List each item delivered to at least two departments by each supplier that delivers it.

SELECT DISTINCT itemname FROM qdel

WHERE itemname NOT IN

(SELECT itemname FROM qdel

GROUP BY itemname, splno

40. Something to all

• List the items that are delivered only by the suppliers that deliver something to all the departments.

SELECT DISTINCT itemname FROM qdel del1

WHERE NOT EXISTS

(SELECT * FROM qdel del2

WHERE del2.itemname = del1.itemname

AND splno NOT IN

(SELECT splno FROM qdel

GROUP BY splno HAVING COUNT(DISTINCT deptname) =

(SELECT COUNT(*) FROM qdept

wHERE deptname NOT IN ('Management',

'Marketing', 'Personnel',

41. Intersection (AND)

• List the items delivered by Nepalese Corp. and sold in the Navigation department.

SELECT DISTINCT itemname FROM qitem

WHERE itemname IN

(SELECT itemname FROM qdel, qspl

WHERE qdel.splno = qspl.splno

AND splname = 'Nepalese corp.')

AND itemname IN

(SELECT itemname FROM qsale

42. Union (OR)

• List the items delivered by Nepalese Corp. or sold in the Navigation department.

SELECT DISTINCT itemname FROM qitem

WHERE itemname IN

(SELECT itemname FROM qdel, qspl

WHERE qdel.splno = qspl.splno

AND splname = 'Nepalese corp.')

OR itemname IN

(SELECT itemname FROM qsale

43. Intersection/union

• List the departments selling items of type E that are delivered by Nepalese Corp. and/or which are sold by the Navigation department.

SELECT DISTINCT deptname FROM qsale

WHERE itemname IN

(SELECT qitem.itemname FROM qitem, qdel, qspl

WHERE qitem.itemname = qdel.itemname

AND qdel.splno = qspl.splno

AND splname = 'Nepalese corp.'

AND itemtype = 'e')

or itemname in

(SELECT itemname FROM qsale

44. Averaging with a condition

• Find the average salary of the employees in the Clothes department.

SELECT AVG(empsalary) FROM qemp

45. Averaging with grouping

• Find, for each department, the average salary of the employees.

SELECT deptname, AVG(empsalary) FROM qemp

46. Average with a join, condition, and grouping

• Find, for each department on the second floor, the average salary of the employees.

SELECT qdept.deptname, AVG(empsalary) FROM qemp, qdept

WHERE qemp.deptname = qdept.deptname

AND deptfloor = 2

47. Averaging with multiple joins

• Find, for each department that sells items of type E, the average salary of the employees.

SELECT qdept.deptname, AVG(empsalary)

FROM qemp, qdept, qsale, qitem

WHERE qemp.deptname = qdept.deptname

AND qdept.deptname = qsale.deptname

AND qsale.itemname = qitem.itemname

AND itemtype = 'E'

48. Complex counting

• What are the number of different items delivered by each supplier that delivers to all departments?

SELECT splname, COUNT(DISTINCT itemname)

FROM qdel del1, qspl

WHERE del1.splno = qspl.splno

AND NOT EXISTS

(SELECT * FROM qdept

WHERE deptname NOT IN

(SELECT deptname FROM qdel

WHERE qdel.splno = del1.splno)

AND deptname NOT IN

('Management', 'Marketing', 'Personnel',

'Accounting', 'Purchasing'))

49. Summing with joins and conditions

• Find the total number of items of type E sold by the departments on thesecond floor.

SELECT SUM(saleqty) FROM qitem, qsale, qdept

WHERE qitem.itemname = qsale.itemname

AND qdept.deptname = qsale.deptname

AND itemtype = 'E'

50. Summing with joins, conditions, and grouping

• Find, for each item, the total quantity sold by the departments on the second floor.

SELECT qitem.itemname, SUM(saleqty) FROM qitem, qsale, qdept

WHERE qitem.itemname = qsale.itemname

AND qdept.deptname = qsale.deptname

AND deptfloor = 2

51. Advanced summing

• List suppliers that deliver a total quantity of items of types C and N that is altogether greater than 100.

SELECT qdel.splno, splname FROM qspl, qdel, qitem

WHERE qspl.splno = qdel.splno

AND qitem.itemname = qdel.itemname

AND (itemtype = 'C' or itemtype = 'N')

GROUP BY qdel.splno, splname HAVING SUM(delqty) > 100;

52. Comparing to the average with a join

• List the employees in the Accounting department and the difference between their salaries and the average salary of the department.

CREATE VIEW v52(deptname, dpavgsal) AS

SELECT deptname, AVG(empsalary) FROM qemp

53. Comparing to the average with a product

• List the employees in the Accounting department and the difference between their salaries and the average salary of all the departments.

CREATE VIEW v53(allavgsal) AS

54. Averaging with multiple grouping

• What is, for each supplier, the average number of items per department that the supplier delivers?

SELECT qdel.splno, splname, deptname, AVG(delqty)

FROM qspl, qdel

WHERE qspl.splno = qdel.splno

55. More than the average with grouping

• For each department, find the average salary of the employees who earn more than the average salary of the department.

SELECT deptname, AVG(empsalary) FROM qemp outt

WHERE empsalary > (SELECT AVG(empsalary) FROM qemp inn

WHERE outt.deptname = inn.deptname)

56. The simplest average

• Give the overall average of the salaries in all departments.

57. Difference from the average

• List each employee's salary, the average salary within that person's department, and the difference between the employees' salaries and the average salary of the department.

SELECT empfname, empsalary,

dpavgsal, (empsalary - dpavgsal)

FROM v52, qemp

58. Averaging with multiple joins, multiple grouping, and a condition

• What is the average delivery quantity of items of type N delivered by each company who delivers them?

SELECT qdel.splno, splname, qdel.itemname, AVG(delqty)

FROM qdel, qspl, qitem

WHERE qdel.splno = qspl.splno

AND qdel.itemname = qitem.itemname

AND itemtype = 'N'

59. Detailed averaging

• What is the average delivery quantity of items of type N delivered by each supplier to each department (given that the supplier delivers items of type N to the department)?

SELECT qdel.splno, splname, deptname, qdel.itemname, AVG(delqty)

FROM qdel, qspl, qitem

WHERE qdel.splno = qspl.splno

AND qdel.itemname = qitem.itemname

AND itemtype = 'N'

60. Counting pairs

• What is the number of supplier-department pairs in which the supplier delivers at least one item of type E to the department?

CREATE VIEW v60 AS

(SELECT DISTINCT splno, deptname FROM qdel, qitem

WHERE qdel.itemname = qitem.itemname

61. No Booleans

• Is it true that all the departments that sell items of type C are located on the third floor? (The result can be a Boolean 1 or 0, meaning yes or no.)

SELECT COUNT(*) FROM qdept

WHERE deptfloor <> 3

and exists

(SELECT * FROM qsale, qitem

WHERE qsale.itemname = qitem.itemname

AND qsale.deptname = qdept.deptname

Data for tables

qsale

qspl

qitem

qdept

qdel

qemp

qdept