In SQL 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.