Learn the formal subquery definition and write a subquery.

Learn the subquery restrictions.

Use the IN operator when writing a subquery.

Nest subqueries at multiple levels.

Use comparison operators when writing a subquery.

Use the ALL and ANY keywords when writing a subquery.

Write a correlated subquery including the use of the EXISTS operator.

Use the ORDER BY clause when writing a subquery.

Learn the formal subquery definition and write a subquery.

Learn the subquery restrictions.

Use the IN operator when writing a subquery.

Nest subqueries at multiple levels.

Use comparison operators when writing a subquery.

Use the ALL and ANY keywords when writing a subquery.

Write a correlated subquery including the use of the EXISTS operator.

Use the ORDER BY clause when writing a subquery.

IMPORTANT: Write all queries as subqueries no JOINs unless stated otherwise.

1. The ProjectAssignment table stores data about the hours that employees are working on specific projects. A senior project manager desires needs a listing of employee names (last and first) that have worked on a project, but have not worked on project 3, 4, 5, 6, or 7. Use a subquery approach and sort the rows of the result table by employee last name.

2. 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 which needs to be found at run time.

3. 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 those employees who worked on one of these three projects, but worked fewer hours than the average number of hours worked on each of these three projects combined.

4. 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. Sort the output by ProjectNumber. (Hint: Use a correlated sub-query).

5. 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.

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

7. Alter the query you wrote for question 5 to add the department name to the result table. The department name column should be listed first and the result table should be sorted by department name. Hint: Involves both JOIN and a sub-query.

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

9. 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.

10. 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.