How to code summary queries

 Chapter 5

How to code summary queries

Exercises

1. Write a SELECT statement that returns these columns:

The count of the number of instructors in the Instructors table

The average of the AnnualSalary column in the Instructors table

Include only those rows where the Status column is equal to “F” (Fulltime).

2. Write a SELECT statement that returns one row for each department that has instructors with these

columns:

The DepartmentName column from the Departments table

The count of the instructors in the Instructors table

The annual salary of the highest paid instructor in the Instructors table

Sort the result set so the department with the most instructors appears first.

3. Write a SELECT statement that returns one row for each instructor that has courses with these

columns:

The instructor first and last names from the Instructors table in this format: John Doe

(Note: If the instructor first name has a null value, the concatenation of the first and last

name will result in a null value.)

A count of the number of courses in the Courses table

The sum of the course units in the Courses table

(Hint: You will need to concatenate the instructor first and last names again in the

GROUP BY clause.)

Sort the result set in descending sequence by the total course units for each instructor.

4. Write a SELECT statement that returns one row for each course that has students enrolled with these

columns:

The DepartmentName column from the Departments table

The CourseDescription from the Courses table

A count of the number of students from the StudentCourses table

Sort the result set by DepartmentName, then by the enrollment for each course.

5. Write a SELECT statement that returns one row for each student that has courses with these columns:

The StudentID column from the Students table

The sum of the course units in the Courses table

Sort the result set in descending sequence by the total course units for each student.

6. Modify the solution to exercise 5 so it only includes students who haven’t graduated and who are

taking more than nine units.

7. Write a SELECT statement that answers this question: What is the total number of courses taught by

parttime instructors? Return these columns:

The instructor last name and first name from the Instructors table in this format: Doe,

John (Note: If the instructor first name has a null value, the concatenation of the first and

last name will result in a null value.)

The total number of courses taught for each instructor in the Courses table

Use the ROLLUP operator to include a row that gives the grand total