What this exercise is about
This exercise gives you the opportunity to work with subqueries.
What you should be able to do
At the end of the lab, you should be able to:
• Code simple subqueries using the = and > operators
• Code more complex subqueries using the keywords ANY, ALL, IN • Code the NOT EXISTS subquery
• Code correlated subqueries
Introduction
See the data model at the start of this Exercise Guide to get the column names and descriptions for each table.
Requirements
• Student handout • SQL Reference
Exclusivo
formación proyecto
C.F.T.I.C.
Problem list
Problem 1
Retrieve all employees who are not involved in a project. Not involved in a project are those employees who have no row in the EMP_ACT table. Display employee number, last name, and department name. Sort the result by employee number.
Problem 2
Retrieve all employees whose yearly salary is more than the average salary of the employees in their department. For example, if the average yearly salary for department E11 is 20998, show all people in department E11 whose individual salary is higher than 20998. Display employee number, yearly salary, and
department number. Sort the result by department number and employee number.
Problem 3
Retrieve all departments having the same number of employees as department A00. List department number and number of employees. Department A00 should not be part of the result.
Problem 4
Display employee number, last name, salary, and department number of employees who earn more than at least one employee in department D11. Employees in department D11 should not be included in the result. In other words, report on any employees in departments other than D11 whose individual yearly salary is higher than that of at least one employee of department D11. List the employees in employee number sequence.
Problem 5
Display employee number, last name, salary, and department number of all employees who earn more than everybody belonging to department D11.
Employees in department D11 should not be included in the result. In other words, report on all employees in departments other than D11 whose individual yearly salary is higher than that of every employee in department D11. List the employees in employee number sequence.
Problem 6
Display employee number, last name, and number of activities of the employee with the largest number of activities. Each activity is stored as one row in the EMP_ACT table.
Exclusivo
formación proyecto
C.F.T.I.C.
Course materials may not be reproduced in whole or in part without the prior written permission of IBM.
© Copyright IBM Corp. 2007, 2013 Exercise 5. Subqueries 5-3
Uempty
Problem 7
Display employee number, last name, and activity number of all activities in the EMP_ACT table. However, the list should only be produced if there were any activities in 1982. Sort the result by employee number.
End of problem list
Exclusivo
formación proyecto
C.F.T.I.C.
Problem list with Expected Results
Problem 1
Retrieve all employees who are not involved in a project. Not involved in a project are those employees who have no row in the EMP_ACT table. Display employee number, last name, and department name. Sort the result by employee number.
Result
EMPNO LASTNAME DEPTNAME
--- --- 000060 STERN MANUFACTURING SYSTEMS
000120 O'CONNELL SPIFFY COMPUTER SERVICE DIV.
Problem 2
Retrieve all employees whose yearly salary is more than the average salary of the employees in their department. For example, if the average yearly salary for department E11 is 20998, show all people in department E11 whose individual salary is higher than 20998. Display employee number, yearly salary, and
department number. Sort the result by department number and employee number.
Result
Course materials may not be reproduced in whole or in part without the prior written permission of IBM.
© Copyright IBM Corp. 2007, 2013 Exercise 5. Subqueries 5-5
Uempty
Problem 3
Retrieve all departments having the same number of employees as department A00. List department number and number of employees. Department A00 should not be part of the result.
Display employee number, last name, salary, and department number of employees who earn more than at least one employee in department D11. Employees in department D11 should not be included in the result. In other words, report on any employees in departments other than D11 whose individual yearly salary is higher than that of at least one employee of department D11. List the employees in employee number sequence. 000090 HENDERSON 29750.00 E11 000100 SPENSER 26150.00 E21 000110 LUCCHESI 46500.00 A00 000120 O'CONNELL 29250.00 A00 000130 QUINTANA 23800.00 C01 000140 NICHOLLS 28420.00 C01 000230 JEFFERSON 22180.00 D21 000240 MARINO 28760.00 D21 000250 SMITH 19180.00 D21 000270 PEREZ 27380.00 D21 000280 SCHNEIDER 26250.00 E11 000320 MEHTA 19950.00 E21
Problem 5
Display employee number, last name, salary, and department number of all employees who earn more than everybody belonging to department D11.
Employees in department D11 should not be included in the result. In other words, report on all employees in departments other than D11 whose individual yearly salary is higher than that of every employee in department D11. List the employees in employee number sequence.
Result
EMPNO LASTNAME SALARY WORKDEPT --- --- --- --- 000010 HAAS 52750.00 A00 000020 THOMPSON 41250.00 B01 000030 KWAN 38250.00 C01 000050 GEYER 40175.00 E01 000070 PULASKI 36170.00 D21 000110 LUCCHESI 46500.00 A00
Problem 6
Display employee number, last name, and number of activities of the employee with the largest number of activities. Each activity is stored as one row in the EMP_ACT table.
Result
EMPNO LASTNAME COUNT_ACT --- --- --- 000250 SMITH 10
Exclusivo
formación proyecto
C.F.T.I.C.
Course materials may not be reproduced in whole or in part without the prior written permission of IBM.
© Copyright IBM Corp. 2007, 2013 Exercise 5. Subqueries 5-7
Uempty
Problem 7
Display employee number, last name, and activity number of all activities in the EMP_ACT table. Duplicate rows should be avoided. However, the list should only be produced if there were any activities in 1982. Sort the result by employee number.
Result
End of Problem list with Expected Results
Exclusivo
formación proyecto
C.F.T.I.C.
Solutions
Problem 1
SELECT EMPNO, LASTNAME, DEPTNAME FROM EMPLOYEE , DEPARTMENT WHERE WORKDEPT = DEPTNO
AND EMPNO NOT IN (SELECT EMPNO FROM EMP_ACT) ORDER BY EMPNO;
Problem 2
SELECT EMPNO, SALARY, WORKDEPT FROM EMPLOYEE E
WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEE
WHERE WORKDEPT = E.WORKDEPT) ORDER BY WORKDEPT, EMPNO;
Problem 3
SELECT WORKDEPT, COUNT(*) AS EMP_COUNT FROM EMPLOYEE
WHERE WORKDEPT <> 'A00' GROUP BY WORKDEPT
HAVING COUNT(*) = (SELECT COUNT(*) FROM EMPLOYEE
WHERE WORKDEPT = 'A00');
Problem 4
SELECT EMPNO, LASTNAME, SALARY, WORKDEPT FROM EMPLOYEE
WHERE WORKDEPT <> 'D11'
AND SALARY > ANY (SELECT SALARY FROM EMPLOYEE
WHERE WORKDEPT = 'D11') ORDER BY EMPNO;
An alternate solution is:
SELECT EMPNO, LASTNAME, SALARY, WORKDEPT FROM EMPLOYEE
Course materials may not be reproduced in whole or in part without the prior written permission of IBM.
© Copyright IBM Corp. 2007, 2013 Exercise 5. Subqueries 5-9