Banner

 

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

SELECT empfname FROM qemp WHERE deptname = 'Marketing';

2. Skinning a cat

2.1 Join

SELECT DISTINCT itemname FROM qsale, qdept
	WHERE qdept.deptname = qsale.deptname
	AND deptfloor = 2;

2.2 In

SELECT DISTINCT itemname FROM qsale     
    WHERE deptname IN       
    (SELECT deptname FROM qdept WHERE deptfloor = 2);

2.3 Correlated subquery

SELECT DISTINCT itemname FROM qsale     
    WHERE deptname IN     
    (SELECT deptname FROM qdept     
    WHERE qdept.deptname = qsale.deptname       
    AND deptfloor = 2);

2.4 Exists

SELECT DISTINCT itemname FROM qsale     
    WHERE EXISTS     
    (SELECT * FROM qdept     
    WHERE qsale.deptname = qdept.deptname       
    AND deptfloor = 2);

3. Another full toss

SELECT DISTINCT itemname FROM qsale, qdept
  WHERE qsale.deptname = qdept.deptname
    AND deptfloor < > 2;

4. Subtracting from all

SELECT DISTINCT itemname FROM qsale
  WHERE itemname NOT IN
  (SELECT DISTINCT itemname FROM qsale, qdept
  WHERE qsale.deptname = qdept.deptname
    AND deptfloor = 2);

5. Dividing

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

SELECT itemname FROM qsale, qdept
  WHERE qsale.deptname = qdept.deptname AND deptfloor = 2
  GROUP BY itemname
  HAVING COUNT(DISTINCT qdept.deptname) > 1;

7. A friendly IN for an SQL traveler

SELECT empfname, empsalary FROM qemp
  WHERE empno IN
    (SELECT bossno FROM qemp WHERE empfname = 'Clare');

8. Joining a table with itself

SELECT wrk.empno, wrk.empfname FROM qemp wrk, qemp boss
  WHERE wrk.bossno = boss.empno
    AND boss.empsalary < wrk.empsalary;

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

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

SELECT boss.empno, boss.empfname, COUNT(*)
  FROM qemp wrk, qemp boss
  WHERE wrk.bossno = boss.empno
    GROUP BY boss.empno, boss.empfname;

11. A self-join with two matching conditions

SELECT wrk.empfname, wrk.deptname, boss.empfname
  FROM qemp wrk, qemp boss
  WHERE wrk.bossno = boss.empno
    AND wrk.deptname = boss.deptname;

12. Averaging with GROUP BY

SELECT deptname, AVG(empsalary) FROM qemp
  GROUP BY deptname
    HAVING AVG(empsalary) > 25000;

13. Inner query GROUP BY AND HAVING

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
    HAVING AVG(wrk.empsalary) > 25000;

14. An IN with GROUP BY AND COUNT

SELECT empfname, empsalary FROM qemp
  WHERE empno IN
  (SELECT bossno FROM qem
    GROUP BY bossno HAVING COUNT(*) > 2);

15. A self-join with some conditions

SELECT wrk.empfname, wrk.empsalary, boss.empfname
  FROM qemp wrk, qemp boss
  WHERE wrk.bossno = boss.empno
  AND wrk.deptname = 'Marketing'
    AND wrk.empsalary > 25000;

16. Making comparisons

SELECT empfname, empsalary FROM qemp
  WHERE empsalary >
  (SELECT MAX(empsalary) FROM qemp
    WHERE deptname = 'Marketing');

17. An IN with GROUP BY AND SUM

18. A double divide!

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

SELECT DISTINCT qspl.splno, splname FROM qspl, qdel
    WHERE qspl.splno = qdel.splno AND itemname = 'Compass';

20. A 6-inch putt for a birdie

SELECT splno, splname FROM qspl
  WHERE splno NOT IN
    (SELECT splno FROM qdel WHERE itemname = 'Compass');

21. Making the count

SELECT DISTINCT qdel.splno, splname FROM qspl, qdel
  WHERE qdel.splno = qspl.splno
  AND itemname < > 'Compass'
  AND qdel.splno IN
    (SELECT splno FROM qdel WHERE itemname = 'Compass');
SELECT DISTINCT qdel.splno, splname FROM qspl, qdel
    WHERE qdel.splno = qspl.splno
    AND qdel.splno IN
    (SELECT splno FROM qdel WHERE itemname = 'Compass')
    GROUP BY qdel.splno, splname HAVING COUNT(DISTINCT itemname) > 1;

22. Minus AND divide

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
    qsale.itemname = qitem.itemname)));

23. Division with copies

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
    AND del1.deptname = qsale.deptname));
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 qsale
  
    WHERE deptname = del1.deptname));

24. A difficult pairing

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
    WHERE qsale.deptname = del1.deptname));

25. Two divides AND an intersection

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
    AND qdel.deptname = qdept.deptname));

26. A divide with a matching condition

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
    WHERE qsale.deptname = dept2.deptname))));

27. Restricted divide

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
    AND qdel.itemname = qitem.itemname));

28. A NOT IN variation on divide

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
    WHERE deptname = 'Books'));

29. All AND only

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
    WHERE deptname = 'Equipment'));

30. Divide with an extra condition

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
    AND qdel.deptname = qdept.deptname)));

31. At least some COUNT

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
    HAVING COUNT(DISTINCT qdel.itemname) > 1;

32. Double divide with a restriction

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
    AND qsale.deptname = qdept.deptname)));
SELECT splname FROM qspl
    WHERE NOT EXISTS (SELECT * FROM qitem
    WHERE itemtype = 'B'
    AND NOT EXISTS (SELECT * FROM qdel
    WHERE qdel.itemname = qitem.itemname
    AND qdel.splno = qspl.splno
    AND deptname IN (SELECT deptname FROM v32)));

33. Triple divide with an intersection

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
	AND qsale.itemname = qitem.itemname)));
CREATE VIEW v33b AS
    (SELECT deptname FROM qdept
    WHERE NOT EXISTS (SELECT * FROM qitem
    WHERE itemtype = 'B'
    AND NOT EXISTS (SELECT * FROM qdel
    WHERE qdel.deptname = qdept.deptname 
    AND qdel.itemname = qitem.itemname)));
SELECT splname FROM qspl
    WHERE NOT EXISTS (SELECT * FROM qitem
    WHERE itemtype = 'B'
    AND NOT EXISTS (SELECT * FROM qdel
    WHERE qdel.splno = qspl.splno
    AND qdel.itemname = qitem.itemname
    AND EXISTS
    (SELECT * FROM v33a
    WHERE qdel.deptname = v33a.deptname)
    AND EXISTS
    (SELECT * FROM v33b 
    WHERE qdel.deptname = v33b.deptname)));

34. An easy one COUNT

SELECT itemname FROM qdel
    GROUP BY itemname HAVING COUNT(DISTINCT splno) = 1;

35. The only one

SELECT DISTINCT qspl.splno, splname, itemname
  FROM qspl, qdel del1
  WHERE qspl.splno = del1.splno
  AND itemname NOT IN
  (SELECT itemname FROM qdel
    WHERE qdel.splno < > del1.splno);

36. At least some number

SELECT qspl.splno, splname FROM qdel, qspl
  WHERE qdel.splno = qspl.splno
  GROUP BY qspl.splno, splname
    HAVING COUNT(DISTINCT qdel.itemname) > = 10;

37. A three-table join

SELECT qitem.itemname, itemtype, qdept.deptname, deptfloor
  FROM qitem, qsale, qdept
  WHERE qsale.itemname = qitem.itemname
    AND qsale.deptname = qdept.deptname;

38. Using NOT IN like NOT EXISTS

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
    WHERE del3.deptname < > del1.deptname));

39. Minus after GROUP By

SELECT DISTINCT itemname FROM qdel
  WHERE itemname NOT IN
  (SELECT itemname FROM qdel
  GROUP BY itemname, splno
    HAVING COUNT(DISTINCT deptname) < 2);

40. Something to all

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', 'Accounting', 'Purchasing'))));

41. Intersection (AND)

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
    WHERE deptname = 'Navigation');

42. Union (OR)

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
    WHERE deptname = 'Navigation');

43. Intersection/union

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
    WHERE deptname = 'Navigation');

44. Averaging with a condition

SELECT AVG(empsalary) FROM qemp
    WHERE deptname = 'clothes';

45. Averaging with grouping

SELECT deptname, AVG(empsalary) FROM qemp
    GROUP BY deptname;

46. Average with a join, condition, AND grouping

SELECT qdept.deptname, AVG(empsalary) FROM qemp, qdept
  WHERE qemp.deptname = qdept.deptname
  AND deptfloor = 2
    GROUP BY qdept.deptname;

47. Averaging with multiple joins

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'
    GROUP BY qdept.deptname;

48. Complex counting

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'))
    GROUP BY splname;

49. Summing with joins AND conditions

SELECT SUM(saleqty) FROM qitem, qsale, qdept
  WHERE qitem.itemname = qsale.itemname
  AND qdept.deptname = qsale.deptname
  AND itemtype = 'E'
    AND deptfloor = 2;

50. Summing with joins, conditions, AND grouping

SELECT qitem.itemname, SUM(saleqty) FROM qitem, qsale, qdept
  WHERE qitem.itemname = qsale.itemname
  AND qdept.deptname = qsale.deptname
  AND deptfloor = 2
    GROUP BY qitem.itemname;

51. Advanced summing

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

CREATE VIEW v52(deptname, dpavgsal) AS
  SELECT deptname, AVG(empsalary) FROM qemp
    GROUP BY deptname;
SELECT empfname, (empsalary - dpavgsal) FROM v52, qemp
    WHERE v52.deptname = qemp.deptname
    AND qemp.deptname = 'Accounting';

53. Comparing to the average with a product

CREATE VIEW v53(allavgsal) AS
    SELECT AVG(empsalary) FROM qemp;
SELECT empfname, (empsalary - allavgsal) FROM v53, qemp
    WHERE deptname = 'Accounting';

54. Averaging with multiple grouping

SELECT qdel.splno, splname, deptname, AVG(delqty)
  FROM qspl, qdel
  WHERE qspl.splno = qdel.splno
    GROUP BY qdel.splno, splname, deptname;

55. More than the average with grouping

SELECT deptname, AVG(empsalary) FROM qemp outt
  WHERE empsalary >
(SELECT AVG(empsalary) FROM qemp inn
  WHERE outt.deptname = inn.deptname)
    GROUP BY deptname;

56. The simplest average

SELECT AVG(empsalary) FROM qemp;
SELECT AVG(dpavgsal) FROM v52;

57. Difference from the average

SELECT empfname, empsalary,
  dpavgsal, (empsalary - dpavgsal)
  FROM v52, qemp
    WHERE v52.deptname = qemp.deptname;

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

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'
    GROUP BY qdel.splno, splname, qdel.itemname;

59. Detailed averaging

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'
    GROUP BY qdel.splno, splname, deptname, qdel.itemname;

60. Counting pairs

CREATE VIEW v60 AS
  (SELECT DISTINCT splno, deptname FROM qdel, qitem
  WHERE qdel.itemname = qitem.itemname
    AND itemtype = 'E' );
SELECT COUNT(*) FROM v60;

61. No Booleans

SELECT COUNT(*) FROM qdept
  WHERE deptfloor <> 3
  AND exists
  (SELECT * FROM qsale, qitem
  WHERE qsale.itemname = qitem.itemname
  AND qsale.deptname = qdept.deptname
    AND itemtype = 'C');
SELECT COUNT(*) FROM qdept
    WHERE deptfloor = 3
    AND EXISTS
    (SELECT * FROM qsale, qitem
    WHERE qsale.itemname = qitem.itemname
    AND qsale.deptname = qdept.deptname 
    AND itemtype = 'C');

Questions

  1. List the green items of type C.

  2. Find the names of green items sold by the Recreation department.

  3. Of those items delivered, find the items not delivered to the Books department.

  4. Find the departments that have never sold a geopositioning system.

  5. Find the departments that have sold compasses and at least two other items.

  6. Find the departments that sell at least four items.

  7. Find the employees who are in a different department from their manager’s department.

  8. Find the employees whose salary is less than half that of their manager’s.

  9. Find the green items sold by no department on the second floor.

  10. Find the items delivered by all suppliers.

  11. Find the items delivered by at least two suppliers.

  12. Find the items not delivered by Nepalese Corp.

  13. Find the items sold by at least two departments.

  14. Find the items delivered for which there have been no sales.

  15. Find the items delivered to all departments except Administration.

  16. Find the name of the highest-paid employee in the Marketing department.

  17. Find the names of employees who make 10 percent less than the average salary.

  18. Find the names of employees with a salary greater than the minimum salary paid to a manager.

  19. Find the names of suppliers that do not supply compasses or geopositioning systems.

  20. Find the number of employees with a salary under $10,000.

  21. Find the number of items of type A sold by the departments on the third floor.

  22. Find the number of units sold of each item.

  23. Find the green items delivered by all suppliers.

  24. Find the supplier that delivers no more than one item.

  25. Find the suppliers that deliver to all departments.

  26. Find the suppliers that deliver to all the departments that also receive deliveries from supplier 102.

  27. Find the suppliers that have never delivered a compass.

  28. Find the type A items delivered by São Paulo Manufacturing.

  29. Find, for each department, its floor and the average salary in the department.

  30. If Nancy’s boss has a boss, who is it?

  31. List each employee and the difference between his or her salary and the average salary of his or her department.

  32. List the departments on the second floor that contain more than one employee.

  33. List the departments on the second floor.

  34. List the names of employees who earn more than the average salary of employees in the Shoe department.

  35. List the names of items delivered by each supplier. Arrange the report by supplier name, and within supplier name, list the items in alphabetical order.

  36. List the names of managers who supervise only one person.

  37. List the number of employees in each department.

  38. List the green items delivered by exactly one supplier.

  39. Whom does Todd manage?

  40. List the departments that have not sold all green items.

  41. Find the first name of Sophie’s boss

  42. Find the names of employees who make less than half their manager’s salary.

  43. List the names of each manager and their employees arranged by manager’s name and employee’s name within manager.

  44. Who earns the lowest salary?

  45. List the names of employees who earn less than the minimum salary of the Marketing department.

  46. List the items sold by every department to which all brown items have been delivered.

  47. List the department and the item where the department is the only seller of that item.

  48. List the brown items sold by the Books department and delivered by All Seasons.

  49. Which department has the highest average salary?

  50. List the supplier that delivers all and only brown items.

This page is part of the promotional and support material for Data Management (sixth edition) by Richard T. Watson
For questions and comments please contact the author

Date revised: 23-Jan-2017