Chapter 7 subqueries

SUMMARY

This chapter introduced the topic of subqueries. A subquery is simply a query inside another query. You learned that a subquery can also have a subquery as an object of its WHERE clause. Subqueries can enable the production of result tables without hard coding the criteria used for row selection. In order to use a subquery, the data type of the value(s) produced by a subquery must be join compatible with the expression in the WHERE clause of the outer query.

You also studied the use of various operators with subqueries. These include the IN and NOT IN operators that are useful for processing subqueries that return a list of values. You examined the use of multiple levels of nesting and learned to decompose queries when writing them and when studying the way that they process tables. Additionally, you learned how the ALL and ANY keywords can modify the effect of a comparison operator. Aggregate functions are commonly used in subqueries to return a scalar result table.

The correlated subquery is one where the inner query depends on values provided by the outer query. This type of subquery can produce result tables to answer complex management questions. You also learned to use the EXISTS operator for subqueries that test the existence of rows that satisfy a specified criteria. These subqueries return a TRUE or FALSE value. Finally, you studied the relationship between correlated subqueries that use the EXISTS operator and subqueries that use the IN operator. You also learned where to place the ORDER BY clause for an outer query.

After completing the review exercises given in the next section, you should have good expertise in using subqueries to query a database. In addition, you should understand the method of processing correlated subqueries.

REVIEW EXERCISES

Learn These Terms

1. ALL keyword—a keyword that can modify a comparison operator (e.g., the ALL keyword modifies the greater than comparison operator to mean greater than all values).

2. ANY keyword—a keyword that can modify a comparison operator (e.g., the ANY keyword modifies the greater than operator to mean greater than some value).

3. Correlated subquery—a subquery in which the inner query depends on values that are provided by the outer query.

4. Design time—refers to your mode of operation when you are writing or designing a query.

5. EXISTS operator—an operator used in a subquery to function as an "existence test." The subquery returns a value of TRUE or FALSE.

6. Join compatible—the values returned by a subquery are compatible in terms of data type and the domain of values with the WHERE clause specified in an outer query.

7. Nested subquery—the WHERE clause of a subquery has as its object another subquery.

8. Run time—refers to the actual execution and processing of a query.

9. Scalar result—when a result table is a single column from a single row. Also termed a scalar subquery.

10. Subquery—a query within a query.

Concepts Quiz

1. What term is used to refer to the SELECT statement that contains a subquery?

2. How many levels of subqueries can be used within a WHERE clause?

3. The IN operator is used for what type of subqueries?

4. Name a clause that cannot be used in writing the subquery portion of a query?

5. Complete this sentence: A subquery is always enclosed in ________.

6. Complete this sentence: Generally speaking, the SELECT clause of a subquery must contain ___________, ___________, or ___________.

7. What does the term join compatible mean with respect to data types?

8. Explain conceptually how the following query is processed by the Oracle server:

SELECT LastName "Last Name", FirstName "First Name"

FROM Employee

WHERE EmployeeID IN

(SELECT EmployeeID

FROM Dependent

WHERE Gender = 'M');

9. What are the general, basic rules of thumb regarding when to use a join versus a subquery approach in writing queries?

10. Suppose that you want to produce a listing of employees that are not assigned to work on a specific project listing. What operator is most appropriate for this type of query?

11. Complete this sentence: This ___________ __________ to studying queries can also help you in writing nested subqueries.

12. Study the WHERE clause shown here. What will happen when a query executes with this WHERE clause?

WHERE Salary >

(SELECT Salary

FROM Employee

WHERE Salary > 30000);

13. What type of result table do aggregate functions produce?

14. Suppose that a payroll manager desires a list of employees who have a salary that is greater than the salary of all of the employees in department 5. Which keyword would you use to modify the comparison operator?

15. Suppose that a payroll manager wants to know the employee name and salary of any employee who has a salary that is greater than that of any employee with a salary that exceeds $30,000. Which keyword would you use to modify the comparison operator?

16. The "= ANY" comparison operator and keyword is identical to the _____ operator.

17. What is a correlated subquery?

18. When writing a correlated subquery, how is the table name listed in the outer query's FROM clause referenced in the WHERE clause of the subquery?

19. Complete this sentence: When a subquery uses the EXISTS operator, the subquery functions as an ___________ ___________.

20. Complete this sentence: A subquery that is the object of an EXISTS operator evaluates to either ______ or ______.

SQL Coding Exercises and Questions

Note: You should create meaningful column names for the result tables produced by the queries that you write in working the following exercises.

1. The ProjectAssignment table stores data about the hours that employees are working on specific projects. A senior project manager desires a listing of employees (last and first name) who are currently working on project 3 or 4. Use a subquery approach.

2. Modify the query you wrote for question 1 by sorting the rows of the result table by employee last name.

3. Management needs a listing of employee names (last and first) who have worked on a project, but have not worked on project 3, 4, 5, 6, or 7. You must use the NOT IN operator. Use a subquery approach and sort the rows of the result table by employee last name.

4. Management needs a listing of employee names (last and first) who worked more than 15 hours on the 'Remodel ER Suite' project. You do not know the project number for this particular project. Your query should have a subquery within a subquery.

5. Management is concerned that some employees are not putting in sufficient work hours on assigned projects 1, 2, and 3. List the names of employees (last and first) for employees who worked on one of these three projects worked fewer hours than the average number of hours worked on each of these three projects combined. Hint: Compute the average hours worked on projects 1, 2, and 3 in a subquery.

6. Management is still concerned about work productivity. Write a query that produces a listing of each employee who worked the least on each project (each project will have only a single employee listed unless two employees tied for putting forth the least effort). Include the EmployeeID, HoursWorked, and ProjectNumber columns in the result table. Hint: Use a correlated subquery. Sort the output by ProjectNumber.

7. Management is again concerned with productivity. It is felt that employees may not be paid sufficiently well to motivate their work. Produce a listing of names (last and first) of each employee that receives the lowest salary in each department (some departments may have more than one poorly paid employee). The result table should list the EmployeeID, LastName, FirstName, DepartmentNumber, and Salary columns with meaningful column names, sorted by DepartmentNumber. Based on the results of this question and question 6, is there any evidence that employees who are paid poorly also did poorly in terms of their hours worked on assigned projects?

8. Management also has concerns about the salaries of employees that have not worked very long for the hospital. Write a query to display the last and first names, department number, salary, and date hired of the employee who has worked the least amount of time in each department. Hint: Use the MAX function. Based on the results of this question and question 7, is there any evidence that newer employees are paid poorly?

9. Review the requirements for question 4. Rewrite the query to add the number of hours worked (HoursWorked) and project title (ProjectTitle) to the result table. This challenging modification requires a combination join query and subquery approach.

10. Using a subquery approach, list the names of employees (last and first name) who are managers of departments. Use the EXISTS operator.

11. Alter the query you wrote for question 10 to add the department number to the result table. The department number column should be listed first and the result table should be sorted by department number.

12. The Company's CEO needs to know the department with the highest average salary. The result table should have two columns labeled Department and Highest Average Salary.

13. The treatment table stores data about the treatment services that patients have received. The Chief of Physicians needs a listing of patients (last and first name) that have received either a blood glucose or antibiotic injection (ServiceID = '82947' or '90788'). Use a subquery approach. You need only show the first five rows of the result table in your lab report.

14. Modify the query you wrote for question 13 to sort the output by patient last name, then patient first name. You need only show the first five rows of the result table in your lab report.

15. Modify the query for question 13 to produce a listing of patient names that have NOT received either a blood glucose or antibiotic injection (ServiceID = '82947' or '90788'). Use a subquery approach. Use the NOT IN operator. Sort the output by patient last name, then patient first name. You need only show the first five rows of the result table in your lab report.

16. The prescription table stores data about medicine that has been prescribed for patients. The Chief of Pharmacology needs a listing of patients (last and first name) that have received either valium or flexeril (MedicineCode = '9999003' or '9999008'). Use a subquery approach.

17. Modify the query for question 16 to display the output sorted by patient last name, then patient first name.

18. Management needs a listing of patient names (last and first) who have received a service treatment 'General Panel' (Description column of the service table); however, you do not know the ServiceID for this service. Your query should have a subquery within a subquery using the patient, treatment, and service tables. Sort the output by patient last name, then patient first name.

19. The Director of Human Resources for the hospital is concerned about employee retention. One of the hospital directors has suggested that the hospital has difficulty retaining registered nurses (Title = 'R.N.'). Produce a listing of employees (last and first names) and the date they were hired to work at the hospital, but only list employees who were hired before any nurse with a hospital title of 'R.N.' – Hint: use the MIN aggregate function to determine the date hired for the oldest registered nurse based on the DateHired column. The result table must also list the employee's title and date hired. Use COLUMN commands to format the result table.

20. Management is concerned about the prescribed charge for services versus the actual charge for services in the surgery category of services (CategoryID = 'SUR'). Produce a listing of surgery services listed in the treatment table by ServiceID where the prescribed StandardCharge from the service table differs from the ChargeAmount recorded in the treatment table. The result table should also include the StandardCharge and ChargeAmount. Use a subquery approach to produce a listing of ServiceID values from the service table that belong to the 'SUR' category of services.

21. Alter query 20 to only list services where the difference between the StandardCharge and ChargeAmount is greater than the average difference between these two charges for services in the 'SUR' category of services. The result table should only list the ServiceID and Description columns from the service table. Hint: Compute the average difference between these two charges for 'SUR' services in a subquery.

22. Management wants to know which patients were charged the most for a treatment. The result table should list three columns from the treatment table appropriately labeled: PatientID, ChargeAmount, and ServiceID. Only list rows where the patient was charged the most for a treatment. This may result in some rows where patients were charged identical amounts to other patients. Sort the rows by ServiceID. Use a correlated subquery. Use the following COLUMN commands to format the output. You need only show the first five rows of the result table in your lab report.
COLUMN "Patient" FORMAT A7;

COLUMN "Amt Charged" FORMAT $999,999.99;

COLUMN "Treatment" FORMAT A9;
23. The Chief of Administration for the hospital is concerned with productivity. It is felt that physicians may not be paid sufficiently well to motivate their work. Produce a listing of names (last and first) of each physician (Title = 'M.D.') that receives the lowest salary in each department (departments may have more than one poorly paid physician). The result table should list the LastName, FirstName, DepartmentNumber, and Salary columns with meaningful column names, sorted by DepartmentNumber. Not all departments will have a physician as an employee. Use the COLUMN commands shown here to format the output.
COLUMN "Last Name" FORMAT A15;

COLUMN "First Name" FORMAT A15;

COLUMN "Dept" FORMAT 9999;

COLUMN Salary FORMAT $9,999,999;

24 Modify the query for question 23 above to list physicians from each department who are paid the highest salaries.

25. Management has decided to extend the study of salary payments to employees in all salaried categories (do not include hourly wage employees). Write a query to display the last and first names, department number, salary, and date hired of the employee in each department who has worked the least amount of time at the hospital. Hint: Use the MAX function and a correlated subquery.

26. Review the requirement for question 25 above. Rewrite the query to add the department name to the result table. Delete the employee first name and the department number. This requires a combination join and subquery approach.

7 -
Revised September 22, 2007