SQL Playbook

This page is part of the promotional and support material for Data Management (fourth edition) by Richard T. Watson Original page is available at http://richardtwatson.com/dm4e/Reader/sql/playbook.html  Updated By Baihua Zheng (December 28, 2005)

Preparation

• ER-model

 

  

• Relational Model

 

• Create table SQL (code download)

• Insert data SQL  (code download)

  • You have to work out the insertion order of different tables!

  • The relations and data are shown at the end of this page.

1. A slow full toss

• Find the names of employees in the Marketing department.

2. Skinning a cat

• Return the names of 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

AND DEPTFLOOR <> 2;

4. Subtracting from all

• Find the names of the sold items that are not sold by by any 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 AND 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

HAVING COUNT(DISTINCT QDEPT.DEPTNAME) > 1;

7. A friendly IN for an SQL traveler

• Find the salary of Clare's manager.

       

SELECT EMPFNAME, EMPSALARY FROM QEMP

WHERE EMPNO IN (SELECT BOSSNO FROM QEMP WHERE EMPFNAME = 'Clare');

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 AND BOSS.EMPSALARY < WRK.EMPSALARY;

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

• Find the departments where all the employees earn less than their manager (Note: Management Dept does not have any boss).

       

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

• Return EMPNO, EMPNAME of each manager, and number of employees she/he manages directly (Employee A is a direct employee of Manager B if and only if B is A's boss).

       

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

• 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 AND WRK.DEPTNAME = BOSS.DEPTNAME;

12. Averaging with GROUP BY

• List DEPTNAME and the average salary of those departments having an average salary over $25,000.

       

SELECT DEPTNAME, AVG(EMPSALARY) FROM QEMP

GROUP BY DEPTNAME

HAVING AVG(EMPSALARY) > 25000;

13. Inner query GROUP BY and HAVING

• List the departments where the average salary of the non-manager employees is more than $25,000 (the manager employee of a department refers to the employee listed under EMPNO column of QDEPT table, for example, employee with EMPNO = 4 is the manager of BOOKS department).

       

SELECT WRK.DEPTNAME, AVG(WRK.EMPSALARY)

FROM QEMP WRK

WHERE WRK.EMPNO NOT IN

(SELECT QDEPT.EMPNO FROM QDEPT

WHERE WRK.DEPTNAME = QDEPT.DEPTNAME)

GROUP BY WRK.DEPTNAME

HAVING AVG(WRK.EMPSALARY) > 25000;

14. An IN with GROUP BY and COUNT

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

       

Approach 1:

SELECT EMPFNAME, EMPSALARY FROM QEMP

WHERE EMPNO IN

(SELECT BOSSNO FROM QEMP

GROUP BY BOSSNO HAVING COUNT(*) > 2);

Approach 2:

SELECT BOSS.EMPFNAME, BOSS.EMPSALARY

FROM QEMP A, QEMP BOSS

WHERE A.BOSSNO = BOSS.EMPNO

GROUP BY BOSS.EMPFNAME, BOSS.EMPSALARY

HAVING COUNT( * ) >2;

 

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' AND WRK.EMPSALARY > 25000;

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 WHERE DEPTNAME = 'Marketing');

17. An IN with GROUP BY and SUM

• Among all the departments with total salary greater than $25,000, find DEPTNAME of those departments that sell Pocket knife-Nile.

       

SELECT DISTINCT DEPTNAME FROM QSALE

WHERE ITEMNAME = 'Pocket knife-Nile' AND DEPTNAME IN

(SELECT DEPTNAME FROM QEMP

GROUP BY DEPTNAME HAVING SUM(EMPSALARY) > 25000);

19. A slam dunk

• Find SPLNO and SPLNAME of those suppliers that deliver compasses.

       

SELECT DISTINCT QDEL.SPLNO, SPLNAME FROM QSPL, QDEL

WHERE QSPL.SPLNO = QDEL.SPLNO and ITEMNAME = 'compass';

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

(SELECT SPLNO FROM QDEL WHERE ITEMNAME = 'compass');

21. Making the count

• Find the SPLNO and SPLNAME of all the suppliers that deliver both 'compass' and an item other than 'compass'.

       

Approach 1:

SELECT * FROM QSPL

WHERE SPLNO IN (SELECT SPLNO FROM QDEL WHERE ITEMNAME = 'Compass')

AND SPLNO IN (SELECT SPLNO FROM QDEL WHERE ITEMNAME <> 'Compass');

Approach 2:

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');

Approach 3:

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;

31. At least some COUNT

• List SPLNO and SPLNAME of all the suppliers that deliver at least two items of type N.

       

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;

34. An easy one COUNT

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

       

35. The only one

• Find all the pairs of supplier (SPLNO, SPLNAME) and item  (ITEMNAME) such that the supplier is the only supplier of that item.

       

Approach 1:

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)

Approach 2:

SELECT DISTINCT QDEL.SPLNO, SPLNAME, ITEMNAME

FROM QDEL, QSPL

WHERE QDEL.SPLNO = QSPL.SPLNO

AND ITEMNAME IN

(SELECT ITEMNAME FROM QDEL

GROUP BY ITEMNAME

HAVING count( DISTINCT SPLNO ) =1);

 

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

HAVING COUNT(DISTINCT QDEL.ITEMNAME) >= 10;

37. A three-table join

• For each item, list ITEMNAME, ITEMTYPE, DEPTNAME of 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 AND QSALE.DEPTNAME = QDEPT.DEPTNAME ;

39. Minus after GROUP By

• List ITEMNAME of 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

HAVING COUNT(DISTINCT DEPTNAME) < 2);

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

WHERE DEPTNAME = 'Navigation');

42. Union (OR)

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

       

APPROACH 1:

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');

APPROACH 2:

SELECT ITEMNAME FROM QDEL, QSPL WHERE QDEL.SPLNO = QSPL.SPLNO AND SPLNAME = 'Nepalese corp.'

UNION

SELECT ITEMNAME FROM QSALE WHERE DEPTNAME = 'Navigation';

43. Intersection/union

• List DEPTNAME of all the departments selling items of type E that are delivered by Nepalese Corp, or selling items that 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

WHERE DEPTNAME = 'Navigation');

44. Averaging with a condition

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

       

SELECT AVG(EMPSALARY) FROM QEMP

WHERE DEPTNAME = 'Clothes';

45. Averaging with grouping

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

       

SELECT DEPTNAME, AVG(EMPSALARY) FROM QEMP

GROUP BY DEPTNAME;

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

GROUP BY QDEPT.DEPTNAME

47. Averaging with multiple joins

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

       

Approach 1:

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;

Approach 2:

SELECT DEPTNAME, AVG( EMPSALARY )

FROM QEMP

GROUP BY DEPTNAME

HAVING DEPTNAME IN (

SELECT DEPTNAME  FROM QSALE, QITEM

WHERE QSALE.ITEMNAME = QITEM.ITEMNAME  AND ITEMTYPE = 'E');

49. Summing with joins and conditions

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

       

SELECT SUM(SALEQTY) AS SMUQTY 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

• Find those items sold by the departments on the second floor. For each such an item, list ITEMNAME and the total quantity sold by the departments on the second floor.

       

SELECT QITEM.ITEMNAME, SUM(SALEQTY) AS SMUQTY  FROM QITEM, QSALE, QDEPT

WHERE QITEM.ITEMNAME = QSALE.ITEMNAME

AND QDEPT.DEPTNAME = QSALE.DEPTNAME

AND DEPTFLOOR = 2

GROUP BY QITEM.ITEMNAME;

51. Advanced summing

• List SPLNO and SPLNAME of those suppliers that deliver a total quantity of items of types C and N that is altogether greater than 50.

       

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) > 50;

54. Averaging with multiple grouping

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

       

SELECT SPLNO, DEPTNAME, AVG(DELQTY) AS AVGDELQTY

FROM QDEL

GROUP BY SPLNO, DEPTNAME;

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 Tab1

WHERE EMPSALARY >

(SELECT AVG(EMPSALARY) FROM QEMP

WHERE DEPTNAME =  Tab1.DEPTNAME)

GROUP BY DEPTNAME;

56. The simplest average

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

       

Data for tables

QSALE

SALENO	SALEQTY	ITEMNAME	DEPTNAME
1001	2	Boots-snakeproof	Clothes
1002	1	Pith helmet	Clothes
1003	1	Sextant	Navigation
1004	3	Hat-polar explorer	Clothes
1005	5	Pith helmet	Equipment
1006	1	Pocket knife-Nile	Clothes
1007	1	Pocket knife--Nile	Recreation
1008	1	Compass	Navigation
1009	1	Geo positioning system	Navigation
1010	5	Map measure	Navigation
1011	1	Geo positioning system	Books
1012	1	Sextant	Books
1013	3	Pocket knife-Nile	Books
1014	1	Pocket knife-Nile	Navigation
1015	1	Pocket knife-Nile	Equipment
1016	1	Sextant	Clothes
1017	1	Sextant	Equipment
1018	1	Sextant	Recreation
1019	1	Sextant	Furniture
1020	1	Pocket knife-Nile	Furniture
1021	1	Exploring in 10 Easy Lessons	Books
1022	1	How to Win Foreign Friends	Books
1023	1	Compass	Books
1024	1	Pith helmet	Books
1025	1	Elephant polo stick	Recreation
1026	1	Camel saddle	Recreation

QSPL

SPLNO	SPLNAME
101	Global Books & Maps
102	Nepalese Corp.
103	All Sports Manufacturing
104	Sweatshops Unlimited
105	All Points, Inc.
106	São Paulo Manufacturing

QITEM

ITEMNAME	ITEMTYPE	ITEMCOLOR
Boots-snakeproof	C	Green
Camel saddle	R	Brown
Compass	N	--
Elephant polo stick	R	Bamboo
Exploring in 10 Easy Lessons	B	--
Geo positioning system	N	--
Hammock	F	Khaki
Hat-polar explorer	C	White
How to Win Foreign Friends	B	--
Map case	E	Brown
Map measure	N	--
Pith helmet	C	Khaki
Pocket knife-Avon	E	Brown
Pocket knife-Nile	E	Brown
Safari chair	F	Khaki
Safari cooking kit	F	--
Sextant	N	--
Stetson	C	Black
Tent-2 person	F	Khaki
Tent-8 person	F	Khaki

QDEPT

DEPTNAME	DEPTFLOOR	DEPTPHONE	EMPNO
Management	5	34	1
Books	1	81	4
Clothes	2	24	4
Equipment	3	57	3
Furniture	4	14	3
Navigation	1	41	3
Recreation	2	29	4
Accounting	5	35	5
Purchasing	5	36	7
Personnel	5	37	9
Marketing	5	38	2

 

QDEL

DELNO	DELQTY	ITEMNAME	DEPTNAME	SPLNO
51	50	Pocket knife-Nile	Navigation	105
52	10	Pocket knife-Nile	Books	105
53	10	Pocket knife-Nile	Clothes	105
54	10	Pocket knife-Nile	Equipment	105
55	10	Pocket knife-Nile	Furniture	105
56	10	Pocket knife-Nile	Recreation	105
57	50	Compass	Navigation	101
58	10	Geo positioning system	Navigation	101
59	10	Map measure	Navigation	101
60	25	Map case	Navigation	101
61	2	Sextant	Navigation	101
62	1	Sextant	Equipment	105
63	20	Compass	Equipment	103
64	1	Geo positioning system	Books	103
65	15	Map measure	Navigation	103
66	1	Sextant	Books	103
67	5	Sextant	Recreation	102
68	3	Sextant	Navigation	104
69	5	Boots-snakeproof	Clothes	105
70	15	Pith helmet	Clothes	105
71	1	Pith helmet	Clothes	101
72	1	Pith helmet	Clothes	102
73	1	Pith helmet	Clothes	103
74	1	Pith helmet	Clothes	104
75	5	Pith helmet	Navigation	105
76	5	Pith helmet	Books	105
77	5	Pith helmet	Equipment	105
78	5	Pith helmet	Furniture	105
79	5	Pith helmet	Recreation	105
80	10	Pocket knife-Nile	Navigation	102
81	1	Compass	Navigation	102
82	1	Geo positioning system	Navigation	102
83	10	Map measure	Navigation	102
84	5	Map case	Navigation	102
85	5	Compass	Books	102
86	5	Pocket knife-Avon	Recreation	102
87	5	Tent-2 person	Recreation	102
88	2	Tent-8 person	Recreation	102
89	5	Exploring in 10 Easy Lessons	Navigation	102
90	5	How to Win Foreign Friends	Navigation	102
91	10	Exploring in 10 Easy Lessons	Books	102
92	10	How to Win Foreign Friends	Books	102
93	2	Exploring in 10 Easy Lessons	Recreation	102
94	2	How to Win Foreign Friends	Recreation	102
95	5	Compass	Equipment	105
96	2	Boots-snakeproof	Equipment	105
97	20	Pith helmet	Equipment	106
98	20	Pocket knife-Nile	Equipment	106
99	1	Sextant	Equipment	106
100	3	Hat-polar explorer	Clothes	105
101	3	Stetson	Clothes	105

QEMP

EMPNO	EMPFNAME	EMPSALARY	DEPTNAME	BOSSNO
1	Alice	75000	Management
2	Ned	45000	Marketing	1
3	Andrew	25000	Marketing	2
4	Clare	22000	Marketing	2
5	Todd	38000	Accounting	1
6	Nancy	22000	Accounting	5
7	Brier	43000	Purchasing	1
8	Sarah	56000	Purchasing	7
9	Sophie	35000	Personnel	1
10	Sanjay	15000	Navigation	3
11	Rita	15000	Books	4
12	Gigi	16000	Clothes	4
13	Maggie	16000	Clothes	4
14	Paul	11000	Equipment	3
15	James	15000	Equipment	3
16	Pat	15000	Furniture	3
17	Mark	15000	Recreation	3