Home

Exercises

Skill builders

Instructor

Slide exercises

Support

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.
SELECT empfname FROM qemp WHERE deptname = 'Marketing';

2. Skinning a cat
Find the items sold by a department on the second floor.
2.1 Join
SELECT DISTINCT itemname FROM qsale, qdept
WHERE qdept.deptname = qsale.deptname
AND deptfloor = 2;
open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

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

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

open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

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
AND deptfloor = 2);

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

open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

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.
SELECT DISTINCT deptname FROM qemp
WHERE deptname < > 'Management'
AND deptname NOT IN
(SELECT wrk.deptname FROM qemp wrk, qemp boss
open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

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
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 the 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

open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

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

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 qem
GROUP BY bossno 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;
open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

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 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));
open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

19. A slam dunk

Find the suppliers that deliver compasses.
SELECT DISTINCT qspl.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 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
(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;
open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

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

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
AND del1.deptname = qsale.deptname));
SELECT DISTINCT deptname FROM qdel del1
WHERE NOT EXISTS
(SELECT * FROM qdel del2
WHERE del2.deptname = del1.deptname
open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

AND itemname NOT IN

(SELECT itemname FROM qsale
WHERE deptname = del1.deptname));

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

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))
open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

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

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
open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

(SELECT itemname FROM qsale

WHERE qsale.deptname = dept2.deptname))));

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

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

29. All AND only

open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

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

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
AND qdel.deptname = qdept.deptname)));
open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

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

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
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
open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

AND qdel.splno = qspl.splno

AND deptname IN (SELECT deptname FROM v32)));

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
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
open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

(SELECT * FROM v33b

WHERE qdel.deptname = v33b.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
GROUP BY itemname HAVING COUNT(DISTINCT splno) = 1;

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
WHERE qdel.splno < > del1.splno);

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

open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

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

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

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
HAVING COUNT(DISTINCT deptname) < 2);
open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

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

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)

open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

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

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

44. Averaging with a condition

Find the average salary of the employees in the Clothes department.
open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

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.
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;
open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

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

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'
AND deptfloor = 2;

50. Summing with joins, conditions, AND grouping

Find, for each item, the total quantity sold by the departments on the second floor.
open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

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

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
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

open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

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
SELECT AVG(empsalary) FROM qemp;
SELECT empfname, (empsalary - allavgsal) FROM v53, qemp
WHERE deptname = 'Accounting';

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
GROUP BY qdel.splno, splname, 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 outt
WHERE empsalary >
(SELECT AVG(empsalary) FROM qemp inn
WHERE outt.deptname = inn.deptname)
GROUP BY deptname;

56. The simplest average

open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

Give the overall average of the salaries in all departments.

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

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
WHERE v52.deptname = qemp.deptname;

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

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)?
open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

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

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
AND itemtype = 'E' );
SELECT COUNT(*) FROM v60;

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
open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com

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');
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 rev i sed: 22-Sep-2013

open in browser PRO version

Are you a developer? Try out the HTML to PDF API

pdfcrowd.com