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';
3.
Of t hose items delivered, f ind 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');
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)

7.
Find the employees who are in a different department from their manager's department. 
SELECT emp.empfname FROM qemp AS emp, qemp AS boss
   WHERE emp.bossno = boss.empno
   AND emp.deptname <> boss.deptname;
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, qdept
		 WHERE qsale.deptname = qdept.deptname
		 AND deptFLOOR = 2);
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)

13.
Find the items sold by at least two departments. 
SELECT itemname FROM qsale
   GROUP BY itemname HAVING COUNT(deptname) >= 2;
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));
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);
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');
21.
Find the number of items of type A sold by the departments on the third floor. 
SELECT COUNT(qsale.itemname) FROM qsale, qitem, qdept
   WHERE qitem.itemname = qsale.itemname 
   AND qdept.deptname = qsale.deptname
   AND qdept.deptFLOOR = 3
   AND qitem.itemtype = 'A';

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

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?

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));
27.
Find the suppliers that have never delivered a compass. 
SELECT DISTINCT splname FROM qdel, qspl
   WHERE qdel.splno = qspl.splno
   AND qdel.splno NOT IN
	  (SELECT splno FROM qdel
		WHERE itemname = 'Compass');
29.
Find, for each department, its floor and the average salary in the department. 
SELECT qdept.deptname, qdept.deptfloor, AVG(empsalary) FROM qemp, qdept
   WHERE qdept.deptname = qemp.deptname
	  GROUP BY qdept.deptname, qdept.deptfloor;
31.
List each employee and the difference between his or her salary and the average salary of his or her department.

This query needs to be done in two parts (see 52 on page 303). First create a a view 

CREATE VIEW avgdeptsal(deptname, dpavgsal) AS
   SELECT deptname, AVG(empsalary) AS dpavgsal FROM qemp
	  GROUP BY deptname;

Then complete the query 

SELECT empfname, empsalary-dpavgsal FROM qemp, avgdeptsal
   WHERE qemp.deptname = avgdeptsal.deptname;
33.
List the departments on the second floor. 
SELECT deptname FROM qdept
   WHERE deptfloor = 2;
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, qdel
   WHERE qdel.splno = qspl.splno
	  ORDER BY splname, itemname;
37.
List the number of employees in each department. 
SELECT deptname, COUNT(deptname) FROM qemp
   GROUP BY deptname;
39.
Whom does Todd manage? 
SELECT empfname FROM qemp
   WHERE bossno IN
	  (SELECT empno FROM qemp
		 WHERE empfname = 'Todd'); 
SELECT empfname FROM qemp
   WHERE empno IN
	  (SELECT bossno FROM qemp
		 WHERE empfname = 'Sophie');
43.
List the names of each manager and their employees arranged by manager's name and employee's name within manager. 
SELECT boss.empfname, emp.empfname FROM qemp AS emp, qemp AS boss
   WHERE emp.bossno = boss.empno
	  ORDER BY boss.empfname, emp.empfname;
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');
47.
List the department and the item where the department is the only seller of that item.

Very similar to 35 on page 300. 

SELECT DISTINCT deptname, itemname FROM qsale sale1
   WHERE itemname NOT IN
	  (SELECT itemname FROM qsale sale2
		 WHERE sale1.deptname <> sale2.deptname);
49.
Which department has the highest average salary?
This query needs to be done in two parts (see 52 on page 258). First create a view. 
CREATE VIEW aavgdeptsal(deptname, dpavgsal) AS
   SELECT deptname, AVG(empsalary) AS dpavgsal FROM qemp
	  group BY deptname;

Then complete the query: 

SELECT deptname FROM avgdeptsal
   WHERE dpavgsal = (SELECT MAX(dpavgsal) FROM avgdeptsal);
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)

7.
Find the employees who are in a different department from their manager's department. 
SELECT emp.empfname FROM qemp AS emp, qemp AS boss
   WHERE emp.bossno = boss.empno
   AND emp.deptname <> boss.deptname;
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, qdept
		 WHERE qsale.deptname = qdept.deptname
		 AND deptFLOOR = 2);
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)

13.
Find the items sold by at least two departments. 
SELECT itemname FROM qsale
   GROUP BY itemname HAVING COUNT(deptname) >= 2;
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));
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);
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');
21.
Find the number of items of type A sold by the departments on the third floor. 
SELECT COUNT(qsale.itemname) FROM qsale, qitem, qdept
   WHERE qitem.itemname = qsale.itemname 
   AND qdept.deptname = qsale.deptname
   AND qdept.deptFLOOR = 3
   AND qitem.itemtype = 'A';

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

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?

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));
27.
Find the suppliers that have never delivered a compass. 
SELECT DISTINCT splname FROM qdel, qspl
   WHERE qdel.splno = qspl.splno
   AND qdel.splno NOT IN
	  (SELECT splno FROM qdel
		WHERE itemname = 'Compass');
29.
Find, for each department, its floor and the average salary in the department. 
SELECT qdept.deptname, qdept.deptfloor, AVG(empsalary) FROM qemp, qdept
   WHERE qdept.deptname = qemp.deptname
	  GROUP BY qdept.deptname, qdept.deptfloor;
31.
List each employee and the difference between his or her salary and the average salary of his or her department.

This query needs to be done in two parts (see 52 on page 303). First create a a view 

CREATE VIEW avgdeptsal(deptname, dpavgsal) AS
   SELECT deptname, AVG(empsalary) AS dpavgsal FROM qemp
	  GROUP BY deptname;

Then complete the query 

SELECT empfname, empsalary-dpavgsal FROM qemp, avgdeptsal
   WHERE qemp.deptname = avgdeptsal.deptname;
33.
List the departments on the second floor. 
SELECT deptname FROM qdept
   WHERE deptfloor = 2;
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, qdel
   WHERE qdel.splno = qspl.splno
	  ORDER BY splname, itemname;
37.
List the number of employees in each department. 
SELECT deptname, COUNT(deptname) FROM qemp
   GROUP BY deptname;
39.
Whom does Todd manage? 
SELECT empfname FROM qemp
   WHERE bossno IN
	  (SELECT empno FROM qemp
		 WHERE empfname = 'Todd'); 
SELECT empfname FROM qemp
   WHERE empno IN
	  (SELECT bossno FROM qemp
		 WHERE empfname = 'Sophie');
43.
List the names of each manager and their employees arranged by manager's name and employee's name within manager. 
SELECT boss.empfname, emp.empfname FROM qemp AS emp, qemp AS boss
   WHERE emp.bossno = boss.empno
	  ORDER BY boss.empfname, emp.empfname;
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');
47.
List the department and the item where the department is the only seller of that item.

Very similar to 35 on page 300. 

SELECT DISTINCT deptname, itemname FROM qsale sale1
   WHERE itemname NOT IN
	  (SELECT itemname FROM qsale sale2
		 WHERE sale1.deptname <> sale2.deptname);
49.
Which department has the highest average salary?
This query needs to be done in two parts (see 52 on page 258). First create a view. 
CREATE VIEW aavgdeptsal(deptname, dpavgsal) AS
   SELECT deptname, AVG(empsalary) AS dpavgsal FROM qemp
	  group BY deptname;

Then complete the query: 

SELECT deptname FROM avgdeptsal
   WHERE dpavgsal = (SELECT MAX(dpavgsal) FROM avgdeptsal);
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)

7.
Find the employees who are in a different department from their manager's department. 
SELECT emp.empfname FROM qemp AS emp, qemp AS boss
   WHERE emp.bossno = boss.empno
   AND emp.deptname <> boss.deptname;
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, qdept
		 WHERE qsale.deptname = qdept.deptname
		 AND deptFLOOR = 2);
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)

13.
Find the items sold by at least two departments. 
SELECT itemname FROM qsale
   GROUP BY itemname HAVING COUNT(deptname) >= 2;
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));
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);
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');
21.
Find the number of items of type A sold by the departments on the third floor. 
SELECT COUNT(qsale.itemname) FROM qsale, qitem, qdept
   WHERE qitem.itemname = qsale.itemname 
   AND qdept.deptname = qsale.deptname
   AND qdept.deptFLOOR = 3
   AND qitem.itemtype = 'A';

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

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?

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));
27.
Find the suppliers that have never delivered a compass. 
SELECT DISTINCT splname FROM qdel, qspl
   WHERE qdel.splno = qspl.splno
   AND qdel.splno NOT IN
	  (SELECT splno FROM qdel
		WHERE itemname = 'Compass');
29.
Find, for each department, its floor and the average salary in the department. 
SELECT qdept.deptname, qdept.deptfloor, AVG(empsalary) FROM qemp, qdept
   WHERE qdept.deptname = qemp.deptname
	  GROUP BY qdept.deptname, qdept.deptfloor;
31.
List each employee and the difference between his or her salary and the average salary of his or her department.

This query needs to be done in two parts (see 52 on page 303). First create a a view 

CREATE VIEW avgdeptsal(deptname, dpavgsal) AS
   SELECT deptname, AVG(empsalary) AS dpavgsal FROM qemp
	  GROUP BY deptname;

Then complete the query 

SELECT empfname, empsalary-dpavgsal FROM qemp, avgdeptsal
   WHERE qemp.deptname = avgdeptsal.deptname;
33.
List the departments on the second floor. 
SELECT deptname FROM qdept
   WHERE deptfloor = 2;
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, qdel
   WHERE qdel.splno = qspl.splno
	  ORDER BY splname, itemname;
37.
List the number of employees in each department. 
SELECT deptname, COUNT(deptname) FROM qemp
   GROUP BY deptname;
39.
Whom does Todd manage? 
SELECT empfname FROM qemp
   WHERE bossno IN
	  (SELECT empno FROM qemp
		 WHERE empfname = 'Todd'); 
SELECT empfname FROM qemp
   WHERE empno IN
	  (SELECT bossno FROM qemp
		 WHERE empfname = 'Sophie');
43.
List the names of each manager and their employees arranged by manager's name and employee's name within manager. 
SELECT boss.empfname, emp.empfname FROM qemp AS emp, qemp AS boss
   WHERE emp.bossno = boss.empno
	  ORDER BY boss.empfname, emp.empfname;
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');
47.
List the department and the item where the department is the only seller of that item.

Very similar to 35 on page 300. 

SELECT DISTINCT deptname, itemname FROM qsale sale1
   WHERE itemname NOT IN
	  (SELECT itemname FROM qsale sale2
		 WHERE sale1.deptname <> sale2.deptname);
49.
Which department has the highest average salary?
This query needs to be done in two parts (see 52 on page 258). First create a view. 
CREATE VIEW aavgdeptsal(deptname, dpavgsal) AS
   SELECT deptname, AVG(empsalary) AS dpavgsal FROM qemp
	  group BY deptname;

Then complete the query: 

SELECT deptname FROM avgdeptsal
   WHERE dpavgsal = (SELECT MAX(dpavgsal) FROM avgdeptsal);

 

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: 02-Dec-2022