3.1

Write the following queries in SQL, using the university schema. (We suggest you actually run these queries on a database, using the sample data that we provide on the website of the book, db-book.com. Instructions for setting up a database, and loading sample data, are provided on the above website.)

  1. Find the titles of courses in the Comp. Sci. department that have 3 credits.
  2. Find the IDs of all students who were taught by an instructor named Einstein; make sure there are no duplicates in the result.
  3. Find the highest salary of any instructor.
  4. Find all instructors earning the highest salary (there may be more than one with the same salary).
  5. Find the enrollment of each section that was offered in Fall 2017.
  6. Find the maxium enrollment, across all sections, in Fall 2017.
  7. Find the sections that had the maximum enrollment in Fall 2017.
  1. Find the titles of courses in the Comp. Sci. department that have 3 credits. 
SELECT title 
FROM course
WHERE dept_name = 'Comp. Sci.' AND credits = 3;
  1. Find the IDs of all students who were taught by an instructor named Einstein; make sure there are no duplicates in the result. This query can be answered in several different ways. One way is as follows.
SELECT DISTINCT takes.ID
FROM takes, instructor, teaches
WHERE takes.course_id = teaches.course_id AND 
    takes.sec_id = teaches.sec_id AND 
    takes.semester = teaches.semester AND 
    takes.year = teaches.year AND 
    teaches.id = instructor.id AND 
    instructor.name = 'Einstein'

Another method by using subqueries

SELECT DISTINCT id 
FROM takes
WHERE (course_id, sec_id, semester, year) IN 
(
    SELECT course_id, sec_id, semester, year 
    FROM teaches INNER JOIN instructor 
        ON teaches.id = instructor.id
    WHERE instructor.name = 'Einstein'
);
  1. Find the highest salary of any instructor.
SELECT MAX(salary) 
FROM instructor
  1. Find all instructors earning the highest salary (there may be more than one with the same salary).
SELECT id, name
FROM instructor
WHERE salary = (SELECT MAX(salary) FROM instructor)
  1. Find the enrollment of each section that was offered in Fall 2017.
SELECT course_id, sec_id, (
    SELECT COUNT(id)
    FROM takes
    WHERE takes.year = section.year
        AND takes.semester = section.semester
        AND takes.course_id = section.course_id 
        AND takes.sec_id = section.sec_id
) AS enrollment 
FROM section 
WHERE semester = 'Fall' AND year = 2017

Note that if the result of the subquery is empty, the aggregate function count returns a value of 0. One way of writing the query might appear to be:

SELECT course_id, sec_id, COUNT(id)
FROM takes
WHERE semester = 'Fall' AND year = 2017
GROUP BY course_id, sec_id

But note that if a section does not have any students taking it, it would not appear in the result. One way of ensuring such a section appears with a count of 0 is to use the outer join operation, covered in Chapter 4.

  1. Find the maxium enrollment, across all sections, in Fall 2017. One way of writing this query is as follows:
WITH enrollment_in_fall_2017(course_id, sec_id, enrollment) AS (
    SELECT course_id, sec_id, COUNT(id)
    FROM takes
    WHERE semester = 'Fall' AND year = 2017
    GROUP BY course_id, sec_id
) 
SELECT CASE 
        WHEN MAX(enrollment) IS NOT NULL THEN MAX(enrollment)
        ELSE 0
       END
FROM enrollment_in_fall_2017;
  1. Find the sections that had the maximum enrollment in Fall 2017.
WITH enrollment_in_fall_2017(course_id, sec_id, enrollment) AS (
    SELECT course_id, sec_id, COUNT(id) 
    FROM takes
    WHERE semester = 'Fall' AND year = 2017
    GROUP BY course_id, sec_id
) 
SELECT course_id, sec_id
FROM enrollment_in_fall_2017
WHERE enrollment = (SELECT MAX(enrollment) FROM enrollment_in_fall_2017);

It is also possible to write the query without the with clause, but the subquery to find enrollment would get repeated twice in the query. While not incorrect to add distinct in the count, it is not necessary in light of the primary key constraint on takes.