Banner

 

Reference 2

Answers to exercises

The queries were tested using MySQL.

1.
List the green items of type C. 
SELECT itemname, itemcolor FROM qitem
   WHERE itemcolor = 'Green' AND itemtype = 'C';
2.
Find the names of green items sold by the Recreation department. 
SELECT qitem.itemname FROM qitem JOIN qsale
   ON  qitem.itemname = qsale.itemname
   WHERE itemcolor = 'Green'
   AND qsale.deptname = 'Recreation' ;
itemname
3.
Of t hose items delivered, find the items not delivered to the Books department. 
SELECT DISTINCT itemname FROM qdel
   WHERE itemname NOT IN
	  (SELECT DISTINCT itemname FROM qdel
		 WHERE deptname = 'Books');
4.
Find the departments that have never sold a geopositioning system. 
SELECT DISTINCT deptname FROM qsale
   WHERE deptname NOT IN
	  (SELECT deptname FROM qsale
		 WHERE itemname = 'Geopositioning system');
deptname
Clothes
Equipment
Furniture
Recreation
5.
Find the departments that have sold compasses and at least two other items.
SELECT deptname FROM qsale
   WHERE itemname = 'Compass'
   AND deptname IN
	  (SELECT deptname FROM qsale 
		 GROUP BY deptname HAVING COUNT(DISTINCT itemname) > 2);

MS Access reports a syntax error for this SQL command because it cannot handle COUNT(DISTINCT column-name)

6.
Find the departments that sell at least four items. 
SELECT deptname FROM qsale
   GROUP BY deptname HAVING COUNT(itemname) > 3;
deptname
Books
Clothes
Navigation
Recreation
7.
Find the employees who are in a different department from their manager's department. 
WITH
emp AS (SELECT * FROM qemp),
boss AS (SELECT * FROM qemp)
SELECT emp.empfname FROM emp JOIN boss
   ON emp.bossno = boss.empno
   WHERE emp.deptname <> boss.deptname;
8.
Find the employees whose salary is less than half that of their manager's. 
WITH
wrk AS (SELECT * FROM qemp),
boss AS (SELECT * FROM qemp)
SELECT wrk.empno, wrk.empfname
   FROM wrk JOIN boss
	  ON wrk.bossno = boss.empno
	  WHERE wrk.empsalary < .5* boss.empsalary;
empno worker boss
4 Clare Ned
9 Sophie Alice
14 Paul Andrew
9.
Find the green items sold by no department on the second floor. 
SELECT qitem.itemname FROM qitem
   WHERE itemcolor = 'Green'
   AND itemname NOT IN
	  (SELECT itemname FROM qsale JOIN qdept
		 ON qsale.deptname = qdept.deptname
		 WHERE deptFLOOR = 2);
10.
Find the items delivered 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));
itemname
Pith helmet
Sextant
11.
Find the items delivered by at least two suppliers. 
SELECT DISTINCT itemname FROM qdel
   GROUP BY itemname HAVING COUNT (DISTINCT splno) >= 2;

MS Access reports a syntax error for this SQL command because it cannot handle COUNT(DISTINCT column-name)

12.
Find the items not delivered by Nepalese Corp. 
SELECT itemname FROM qitem
   WHERE itemname NOT IN
	  (SELECT itemname FROM qdel JOIN qspl
		 ON qdel.splno = qspl.splno
		 WHERE qspl.Splname = 'Nepalese Corp.');
itemname
Boots - snakeproof
Camel saddle
Elephant polo stick
Hammock
Hat - polar explorer
Safari chair
Safari cooking kit
Stetson
13.
Find the items sold by at least two departments. 
SELECT itemname FROM qsale
   GROUP BY itemname HAVING COUNT(deptname) >= 2;
14.
Find the items delivered for which there have been no sales. 
SELECT DISTINCT itemname FROM qdel
   WHERE itemname NOT IN
	  (SELECT itemname FROM qsale);
itemname
Map case
Pocket knife - Avon
Stetson
Tent - 2 person
Tent - 8 person
15.
Find the items delivered to all departments except Administration. 
SELECT itemname FROM qitem
   WHERE 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));
16.
Find the name of the highest-paid employee in the Marketing department. 
SELECT empfname FROM qemp
   WHERE deptname = 'Marketing'
   AND empsalary IN
	  (SELECT MAX(empsalary) FROM qemp
		 WHERE deptname = 'Marketing');
empfname
Ned
17.
Find the names of employees who make 10 percent less than the average salary. 
SELECT empfname, empsalary FROM qemp
   WHERE empsalary <
	 (SELECT .90*AVG(empsalary) FROM qemp);
18.
Find the names of employees with a salary greater than the minimum salary paid to a manager. 
WITH
emp AS (SELECT * FROM qemp),
boss AS (SELECT * FROM qemp)
SELECT empfname FROM emp
   WHERE emp.empsalary >
	  (SELECT MIN(boss.empsalary) FROM emp JOIN boss
		 ON emp.bossno = boss.empno)
		 AND empfname NOT IN
			(SELECT boss.empfname FROM emp JOIN boss
			   ON emp.bossno = boss.empno);
empfname
Sarah
Sophie
19.
Find the names of suppliers that do not supply compasses or geopositioning systems. 
SELECT splname FROM qspl
   WHERE splno NOT IN
	  (SELECT splno FROM qdel
		 WHERE itemname = 'Compass'
		 OR itemname = 'Geopositioning System');
20.
Find the number of employees with a salary under $10,000. 
SELECT COUNT(empfname) FROM qemp
   WHERE empsalary < 10000;
expr1000
0
21.
Find the number of items of type A sold by the departments on the third floor. 
SELECT COUNT(qsale.itemname) FROM qsale JOIN qitem
   ON qsale.itemname = qitem.itemname 
   JOIN qdept
   ON qdept.deptname = qsale.deptname
   WHERE qdept.deptFLOOR = 3
   AND qitem.itemtype = 'A';

The result is 0. If you try items of type 'C' you will get 1.

22.
Find the number of units sold of each item. 
SELECT qsale.itemname, SUM(saleqty) FROM qitem JOIN qsale
   ON qsale.itemname = qitem.itemname 
   GROUP BY qsale.itemname;
itemname SUM(saleqty)
Boots - snakeproof 2
Camel saddle 1
Compass 2
Elephant polo stick 1
Exploring in 10 Easy Lessons 1
Geopositioning system 2
Hat - polar explorer 3
How to Win Foreign Friends 1
Map measure 5
Pith helmet 7
Pocket knife - Nile 8
Sextant 6
23.
Find the green items delivered by all suppliers. 
SELECT qitem.itemname FROM qitem
   WHERE qitem.itemcolor = 'Green'
   AND NOT EXISTS
	  (SELECT * FROM qspl
		 WHERE NOT EXISTS
			(SELECT * FROM qdel 
			   WHERE qdel.itemname = qitem.itemname
			   AND qdel.splno = qspl.splno));

There are no items satisfying this query. What about for khaki?

24.
Find the supplier that delivers no more than one item. 
SELECT splname FROM qspl WHERE splno IN
   (SELECT splno FROM qdel 
	  GROUP BY splno HAVING COUNT(DISTINCT itemname) = 1);
splname
25.
Find the suppliers that deliver to all departments.

Don't forget to exclude the administrative departments, which don't sell items. 

SELECT splname FROM qspl 
   WHERE NOT EXISTS 
	 (SELECT * FROM qdept 
		WHERE deptname NOT IN ('Management', 'Marketing', 'Personnel', 
			 'Accounting', 'Purchasing') 
		AND NOT EXISTS 
		   (SELECT * FROM qdel 
			  WHERE qdel.splno = qspl.splno 
			  AND qdel.deptname = qdept.deptname));
26.
Find the suppliers that deliver to all the departments that also receive deliveries from supplier 102. 
SELECT splname FROM qspl 
   WHERE NOT EXISTS 
	 (SELECT * FROM qdept 
		WHERE deptname NOT IN ('Management', 'Marketing', 'Personnel', 
			 'Accounting', 'Purchasing') 
		AND deptname IN (SELECT deptname FROM qdel WHERE splno = 102)
		AND NOT EXISTS 
		   (SELECT * FROM qdel 
			  WHERE qdel.splno = qspl.splno 
			  AND qdel.deptname = qdept.deptname));
splname
Nepalese Corp.
All Points, Inc.
27.
Find the suppliers that have never delivered a compass. 
SELECT DISTINCT splname FROM qdel JOIN qspl
   ON qdel.splno = qspl.splno
   WHERE qdel.splno NOT IN
	  (SELECT splno FROM qdel
		WHERE itemname = 'Compass');
28.
Find the type A items delivered by Sao Paulo Manufacturing.
SELECT qitem.itemname FROM qitem JOIN qdel
   ON qitem.itemname = qdel.itemname
   JOIN qspl
   ON qdel.splno = qspl.splno
   WHERE splname = 'Sao Paulo Manufacturing'
   AND itemtype = 'A';
itemname
29.
Find, for each department, its floor and the average salary in the department. 
SELECT qdept.deptname, qdept.deptfloor, AVG(empsalary) FROM qemp JOIN qdept
   ON qdept.deptname = qemp.deptname
	  GROUP BY qdept.deptname, qdept.deptfloor;
30.
If Nancy's boss has a boss, who is it? 
SELECT empfname FROM qemp
   WHERE empno IN
   (SELECT bossno FROM qemp
	  WHERE empno IN
	 (SELECT bossno FROM qemp
		WHERE empfname = 'Nancy'));
empfname
Alice
31.
List each employee and the difference between his or her salary and the average salary of his or her department. 
WITH
avgdeptsal(deptname,dpavgsal) AS
   (SELECT deptname, AVG(empsalary) AS dpavgsal FROM qemp
	  GROUP BY deptname)
SELECT empfname, ROUND(empsalary-dpavgsal,0) FROM qemp JOIN avgdeptsal
   ON qemp.deptname = avgdeptsal.deptname;
32.
List the departments on the second floor that contain more than one employee. 
SELECT qdept.deptname FROM qdept JOIN qemp
   ON qdept.deptname = qemp.deptname
   WHERE deptfloor = 2
	  GROUP BY qdept.deptname HAVING COUNT(*) > 1;
deptname
Clothes
33.
List the departments on the second floor. 
SELECT deptname FROM qdept
   WHERE deptfloor = 2;
34.
List the names of employees who earn more than the average salary of employees in the Shoe department. 
SELECT empfname FROM qemp WHERE empsalary >
   (SELECT AVG(empsalary) FROM qemp WHERE deptname = 'Shoe');
empfname

Note: There is no Shoe department, so the answer is an empty table. What happens if you try Purchasing?

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. 
SELECT DISTINCT splname, itemname FROM qspl JOIN qdel
   ON qdel.splno = qspl.splno
	  ORDER BY splname, itemname;
36.
List the names of managers who supervise only one person. 
WITH
emp AS (SELECT * FROM qemp),
boss AS (SELECT * FROM qemp)
SELECT boss.empfname FROM emp JOIN boss
 ON emp.bossno = boss.empno
	GROUP BY boss.empfname HAVING COUNT(*) = 1;
empfname
Todd
Brier
37.
List the number of employees in each department. 
SELECT deptname, COUNT(deptname) FROM qemp
   GROUP BY deptname;
38.
List the green items delivered by exactly one supplier. 
SELECT itemname FROM QDEL
   WHERE itemname IN (SELECT itemname FROM qitem WHERE itemcolor = 'Green')
	  GROUP BY itemname HAVING COUNT(DISTINCT splno) = 1;
itemname
Boots - snakeproof
Note: DISTINCT splno does not work with MS Access
39.
Whom does Todd manage? 
SELECT empfname FROM qemp
   WHERE bossno IN
	  (SELECT empno FROM qemp
		 WHERE empfname = 'Todd');
40.
List the departments that have not sold all green items. 
SELECT deptname FROM qdept
   WHERE deptname NOT IN ('Management', 'Personnel', 'Accounting', 'Marketing','Purchasing')
   AND deptname NOT IN
   (SELECT deptname FROM qdept
	  WHERE  NOT EXISTS 
	 (SELECT * FROM qitem
	   WHERE itemcolor = 'Green'
	  AND  NOT EXISTS
	   (SELECT * FROM qsale
		  WHERE qsale.itemname = qitem.itemname
			 AND qsale.deptname = qdept.deptname)));
deptname
Books
Equipment
Furniture
Navigation
Recreation
41.
Find the first name of Sophie's boss. < 
SELECT empfname FROM qemp
   WHERE empno IN
	  (SELECT bossno FROM qemp
		 WHERE empfname = 'Sophie');
42.
Find the names of employees who make less than half their manager's salary. 
WITH
emp AS (SELECT * FROM qemp),
boss AS (SELECT * FROM qemp)
SELECT emp.empfname FROM emp JOIN boss
   ON emp.empsalary < (boss.empsalary/2)
   WHERE emp.bossno = boss.empno;
empfname
Clare
Sophie
Paul
43.
List the names of each manager and their employees arranged by manager's name and employee's name within manager. 
WITH
emp AS (SELECT * FROM qemp),
boss AS (SELECT * FROM qemp)
SELECT boss.empfname, emp.empfname FROM emp JOIN boss
   ON emp.bossno = boss.empno
	  ORDER BY boss.empfname, emp.empfname;
44.
Who earns the lowest salary? 
SELECT empfname FROM qemp
   WHERE empsalary IN
	  (SELECT MIN(empsalary) FROM qemp);
empfname
Paul
45.
List the names of employees who earn less than the minimum salary of the Marketing department. 
SELECT empfname, empsalary FROM qemp
   WHERE empsalary <
	  (SELECT MIN(empsalary) FROM qemp
		 WHERE deptname = 'Marketing');
46.
List the items sold by every department to which all brown items have been delivered.

Very similar to 18. 

WITH
sale as (SELECT * FROM qsale)
SELECT DISTINCT itemname FROM sale
   WHERE NOT EXISTS
	  (SELECT * FROM qdept
		 WHERE NOT EXISTS
			(SELECT * FROM qitem
			   WHERE itemcolor = 'Brown'
			   AND NOT EXISTS
				  (SELECT * FROM qdel
					 WHERE qdel.deptname = qdept.deptname
					 AND qdel.itemname = qitem.itemname))
					 AND NOT EXISTS (SELECT * FROM qsale
						WHERE qsale.itemname = sale.itemname
						AND qsale.deptname = qdept.deptname));
itemname
Boots - snakeproof
Camel saddle
Compass
Elephant polo stick
Exploring in 10 Easy Lessons
Geo positioning system
Hat - polar explorer
How to Win Foreign Friends
Map measure
Pith helmet
Pocket knife - Nile
Sextant

This query returns no rows. What if the color is changed to black?

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

Very similar to 35WITH
avgdeptsal(deptname,dpavgsal) AS
(SELECT deptname, AVG(empsalary) AS dpavgsal FROM qemp
GROUP BY deptname)
SELECT empfname, ROUND(empsalary-dpavgsal,0) FROM qemp JOIN avgdeptsal
ON qemp.deptname = avgdeptsal.deptname;. 

WITH
sale1 as (SELECT * FROM qsale),
sale2 as (SELECT * FROM qsale)
SELECT DISTINCT deptname, itemname FROM sale1
   WHERE itemname NOT IN
	  (SELECT itemname FROM sale2
		 WHERE sale1.deptname <> sale2.deptname);
48.
List the brown items sold by the Books department and delivered by All Seasons. 
SELECT qsale.itemname FROM qsale JOIN qitem
   ON qsale.itemname = qitem.itemname
   JOIN qdel
   ON qitem.itemname = qdel.itemname
   JOIN qspl
   ON qspl.splno = qdel.splno
   WHERE qitem.itemcolor = 'Brown'
   AND qsale.deptname = 'Books'
   AND qspl.splname = 'All Seasons';
itemname

There are no items satisfying this condition. What happens if the supplier is All Points, Inc.?

49.
Which department has the highest average salary? 
WITH
avgdeptsal(deptname,dpavgsal) AS
   (SELECT deptname, AVG(empsalary) AS dpavgsal FROM qemp
	  GROUP BY deptname)
SELECT deptname FROM avgdeptsal
   WHERE dpavgsal = (SELECT MAX(dpavgsal) FROM avgdeptsal)
50.
List the supplier that delivers all and only brown items. 
SELECT splname FROM qspl
   WHERE NOT EXISTS
	  (SELECT * FROM qitem
		 WHERE itemcolor = 'Brown'
		 AND NOT EXISTS
			(SELECT * FROM qdel
			   WHERE qdel.itemname = qitem.itemname
			   AND qdel.splno = qspl.splno))
			   AND NOT EXISTS
				  (SELECT * FROM qdel
					 WHERE qdel.splno = qspl.splno
					 AND itemname NOT IN
						(SELECT itemname FROM qitem
						   WHERE itemcolor = 'Brown'));
splname

There are no suppliers satisfying this query. However, consider creating a supplier (say 107) and deliveries by this supplier to get a supplier satisfying the query. Then examine what happens when Lyon Leather delivers a Stetson to the Equipment department.

For example:

splno splname
107 Lyon Leather
delno delqty itemname deptname splno
900 1 Camel saddle Equipment 107
901 1 Map case Equipment 107
902 1 Pocket knife - Avon Equipment 107
903 1 Pocket knife - Nile Equipment 107

 

This page is part of the promotional and support material for Data Management (open edition) by Richard T. Watson
For questions and comments please contact the author
Date revised: 10-Dec-2021