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