cmis563lab5.htm; updated 9/6/2007

CMIS 563 Lab 5

Writing Querieswith Aggregate Functions(15)

This lab reinforces Chapter 5 learning objectives as follows:

  • Write queries with aggregate functions: SUM, AVG, COUNT, MAX, and MIN.
  • Use the GROUP BY clause to answer complex managerial questions.
  • Nest aggregate functions.
  • Use the GROUP BY clause with NULL values.
  • Use the GROUP BY clause with the WHERE and ORDER BY clauses.
  • Use the HAVING clause to filter out rows from a result table.

In answering the SQL exercises and questions, submit a copy of each command that you execute and any messages that Oracle generates while executing your SQL commands. Also list the output for any result table that is generated by your SQL statements.

You may submit your project solution as a lab report by copying your solution to the drive Y: server. Name your report as follows: LastName-FirstName-Lab4; example, if your name is Doug Bock, your report name is Bock-Doug-Lab4.doc or Bock-Doug-Lab4.txt (depending on the tool used to create your lab report).

USE THE COURIER FONT TO ALIGN RESULT TABLES.

Unless otherwise specified, your report should display all rows of the result table and all output should be formatted so that the result table is easy to read.

1. A manager from the human resources department needs you to write a query to count the number of dependents of employees of the company. Count the number of rows in the dependent table. Label the output column Number of Dependents.

2. A manager from the human resources department needs you to write a query to count the number of employees of the company that are registered nurses (Title = 'R.N.'). Label the output column Number of RNs.

3. The BirthDate column in the dependent table stores date of birth information for dependents of employees of the company. Write a query to display the date of birth of the youngest dependent listed in the table. No special output column label is required.

4. Accountants working on the company's annual budgeting process need to know the average salary (Salary) for employees and the sum of all employee salaries. The information is stored in the employee table. The result table should have two columns based on a single query. Label the columns Average Salary and TotalSalary. Format the output as $999,999.

5. A new government reporting regulation requires you to develop a query that will count the number of male dependents of employees of the company. The information is stored in the Gender column of the dependent table. The result table should have a single output column labeled Number Male Dependents.

6. A new government reporting regulation the hospital to report the number of regular beds in use by the hospital. The bed_type codes for these beds R1, R2, or R3. The information is stored in the BedType column of the bed table. The result table should have a single output column labeled Number of Regular Beds.

7. A revision to the government reporting regulation cited in question 5 requires the report to count separately the number of male and female dependents of the company. Display the information as two columns, one for gender and one for the associated count. The result table should have two rows, one for each gender. Use a single query. Additionally, the gender output column should be formatted as A6 and have a heading of Gender. The count column should have a heading of Number Counted.

8. The government reporting regulation cited in question 1 also requires a report of the count of each type of dependent (spouse, daughter, and son). Write a query that will produce a result table with two columns labeled Dependent Type (use the RelationshipToEmployee columnfrom the dependent table) and Dependent Count.

9. Modify the query written for question 8 to restrict output to the result table such that spouses are not listed. Only a count of daughters and sons should be listed in the output. Sort the output by the count of the number of dependents in each category with the largest counts listed first. Label the columns as specified in question 10.

10. Modify the query written for question 8 to only count dependents in each category born after December 31, 1980. Use the BirthDate column in your query to refer to each dependent's date of birth. Use the TO_DATE function for the BirthDate column and the TO_DATE('31-DEC-80') expression in your query. Label the columns as specified in question 8.

11. The company's executive director or project manager needs to know the number of projects each department is working on based on the information stored in the project table. The result table should have two columns labeled Department and Project Count. You will need to refer to the DepartmentNumbercolumn that stores the department number and the ProjectNumber column that stores the project number information.

12. Rewrite the query from question 11 to exclude projects located in Edwardsville. You will need to use the Location column that stores the project location in writing your query. Label the columns as specified in question 11.

13. Rewrite the query from question 11 to exclude any group that does not have at least two projects. Label the columns as specified in question 11.

14. Write a query to provide the Executive Director with the total hours worked per project. Use the ProjectNumber and HoursWorked columns from the ProjectAssignment table to obtain the project numbers and hours worked, respectively. Label the two columns Project Number and Total Hours, respectively. Format the output for the Total Hours column as 999.99.

15. Rewrite the query for question 14 to exclude projects where the average HoursWorked column value is less than 15 hours. Sort the output by Total Hours from smallest to largest.

End of Lab.

1