-- CSE 344 section 2 -- more SQL review

-- Same database as last week:
--   Class(dept, number, title)
--   Instructor(username, fname, lname, started_on)
--   Teaches(username, dept, number)
-- See section2-schema.sql for CREATE TABLE and sample data for this database.

-- Semantics of joins:
-- FROM clause takes the Cartesian product of all the named relations.
-- WHERE conditions that relate tuples in two tables implement the join by
-- filtering the Cartesian product to only those matchings of tuples that
-- meet the conditions.

/* Review of joins */

-- Who teaches CSE 451?
SELECT i.fname, i.lname
FROM Teaches AS t, Instructor AS i
WHERE t.username = i.username AND
      t.dept = 'CSE' AND
      t.number = 451
;

-- What courses does Dr. Zahorjan teach?
SELECT c.dept, c.number
FROM Class c, Teaches t, Instructor i
WHERE c.dept = t.dept AND
      c.number = t.number AND
      t.username = i.username AND
      i.username = 'zahorjan'
;

-- Which courses do both Dr. Levy and Dr. Zahorjan teach?
SELECT c.dept, c.number, c.title
FROM Class c, Teaches t1, Teaches t2, Instructor i1, Instructor i2
WHERE c.dept = t1.dept AND c.dept = t2.dept AND
      c.number = t1.number AND c.number = t2.number AND 
      t1.username = i1.username AND
      i1.username = 'levy' AND
      t2.username = i2.username AND
      i2.username = 'zahorjan'
;
-- We can use the same table multiple times in the same query.
-- In fact, in this query, we can't use Teaches or Instructor just once.
-- Why? Because with just one of both, we'd be asking for tuples
-- where the uid is levy and zahorjan in the same tuple.
-- (Similar example from Friday's lecture: find Chinese companies
--  that make both toys and gadgets.)


/* Queries using aggregation functions */

-- How many classes are there in the course catalog?
SELECT COUNT(*)
FROM Class
;

-- What are the highest and lowest class numbers?
SELECT MIN(number), MAX(number)
FROM Class
;

-- How many classes are being taught by at least one instructor?

-- Special case: all classes have the same department
-- First attempt - wrong!
SELECT COUNT(*) AS class_count
FROM Teaches;
-- Second attempt - also wrong
SELECT COUNT(number) AS class_count
FROM Teaches;
-- Even if you specify a column name in an aggregate function,
-- it does not eliminate duplicate values of that column, only 
-- null values.  So try this:
SELECT COUNT(DISTINCT number) AS class_count
FROM Teaches;

-- General case: this is tricky because SQL does not allow aggregation
-- functions to take more than one column as argument, unless that
-- argument is * (which only works for COUNT()).
--
-- We'll solve using subqueries and grouping - first, we group the 
-- Teaches table by department and number in a subquery, then
-- we count the number of groups in the top-level query. 
--
-- Note that we don't care what the subquery tuples are, only how many 
-- tuples/groups there are, so we return dummy tuples containing only the
-- constant 1.
--
SELECT COUNT(*) AS class_count
FROM (
  SELECT 1
  FROM Teaches
  GROUP BY dept, number
) x
;


/* Queries with both grouping and aggregation */

-- How many instructors teach each class?
SELECT dept, number, COUNT(DISTINCT username) AS teacher_count
FROM Teaches
GROUP BY dept, number
;

-- Which instructors teach more than 1 class?
-- Without grouping -- uses subquery
SELECT i.username, i.fname, i.lname
FROM Instructor i
WHERE 1 < (
  SELECT COUNT(*)
  FROM Teaches t
  WHERE t.username = i.username
)
;
-- With grouping -- no subquery
SELECT i.username
FROM Instructor i, Teaches t
WHERE i.username = t.username
GROUP BY i.username
HAVING COUNT(*) > 1
;
-- Notice that:
--  * conditions on groups go in the HAVING clause, not WHERE
--  * we dropped columns fname, lname -- how do we restore them?
-- Third version -- restores missing columns
SELECT i.username, i.fname, i.lname
FROM Instructor i, Teaches t
WHERE i.username = t.username
GROUP BY i.username, i.fname, i.lname
HAVING COUNT(*) > 1
;
-- What if we omit fname, lname from GROUP BY?  Why is that so?


/* Another subquery problem */

-- Which courses do neither Dr. Levy nor Dr. Wetherall teach?
-- wrong --- why?
SELECT c.dept, c.number, c.title
FROM Class c, Teaches t, Instructor i
WHERE c.dept = t.dept AND 
      c.number = t.number AND 
      t.username = i.username AND
      i.username NOT IN ('levy', 'djw')
;
-- This query incorrectly returns CSE 451 and 461 (twice, in fact), 
-- because there are tuples in the join where the uid is neither 
-- levy or djw, but the class is 451 and 461 -- this comes about 
-- from the fact that tom and zahorjan teach those classes.

-- Here's a corrected version that tests that the *class number* 
-- is not in the list that Hank and David teach:
SELECT *
FROM Class c
WHERE c.dept = 'CSE' AND 
      c.number NOT IN (
        SELECT c.number
        FROM Class c, Teaches t, Instructor i
        WHERE c.dept = t.dept AND c.number = t.number AND
              t.username = i.username AND
              i.username IN ('levy', 'djw')
)
;
-- This (correctly) returns only CSE 378.

