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
