4.2

Write the following queries in SQL:
a. Display a list of all instructors, showing each instructor’s ID and the number of sections taught. Make sure to show the number of sections as 0 for instuctors who have not taught any section. Your query should use an outer join, and should not use subqueries.
b. Write the same query as in part a, but using a scalar subquery and not using outer join.
c. Display the list of all course sections offered in Spring 2018, along with the ID and name of each instructor teaching the section. If a section has more than one instructor, that section should appear as many times in the result as it has instructors. If a section does not have any instructor, it should still appear in the result with the instructor name set to “-”.
d. Display the list of all departments, with the total number of instructors, in each department, without using subqueries. Make sure to show departments that have no instructors, and list those departments with an instructor count of zero.

  1. Display a list of all instructors, showing each instructor’s ID and the number of sections taught. Make sure to show the number of sections as 0 for instuctors who have not taught any section. Your query should use an outer join, and should not use subqueries
SELECT ID, COUNT(sec_id) AS Number_of_sections
FROM instructor NATURAL LEFT OUTER JOIN teaches
GROUP BY ID

The above query should not be written using COUNT(*) since that would count null values also. It could be written using any attribute from teaches which does not occur in instructor, which would be correct although it may be confusing to the reader. (Attributes that occur in instructor would not be null even if the instructor has not taught any section.)

  1. Write the same query as in part a, but using a scalar subquery and not using outerjoin.
SELECT ID, (
    SELECT COUNT(*) AS Number_of_sections
    FROM teaches T
    WHERE T.id = I.id
)
FROM instructor I
  1. Display the list of all course sections offered in Spring 2018, along with the ID and name of each instructor teaching the section. If a section has more than one instructor, that section should appear as many times in the result as it has instructors. If a section does not have any instructor, it should still appear in the result with the instructor name set to “-”.
SELECT course_id,sec_id,ID,decode(name,null,'-',name) as name
FROM (section NATURAL LEFT OUTER JOIN teaches)
    NATURAL LEFT OUTER JOIN instructor
WHERE semester = 'Spring' AND year = 2018;

The query may also be written using the coalesce operator, by replacing decode(..) with coalesce(name,‘-’). A more complex version of the query can be written using union of join result with another query that uses a subquery to find courses that do not match; refer to Exercise 4.3.

-- using coalesce
SELECT course_id,sec_id,ID,coalesce(name,'-') as name
FROM (section NATURAL LEFT OUTER JOIN teaches)
    NATURAL LEFT OUTER JOIN instructor
WHERE semester = 'Spring' AND year = 2018;
  1. Display the list of all departments, with the total number of instructors, in each department, without using subqueries. Make sure to show departments that have no instructors, and list those departments with an instructor count of zero.
SELECT dept_name, COUNT(id)
FROM department NATURAL LEFT OUTER JOIN instructor
GROUP BY dept_name;