Advanced SQL: GROUP BY, NULL, subqueries, DML and views

In week six you selected and joined individual rows. This week we summarize rows (how many, what is the average), put queries inside queries, and change the data. The hard questions in the SQL part of the midterm almost always rest on one of these three things: the GROUP BY/HAVING distinction, the strange behavior of NULL, and the “those who did every X” (division) question.

Golden rules:

1. WHERE filters rows, HAVING filters groups. An aggregate function cannot go inside WHERE.
2. A comparison with NULL gives UNKNOWN, not TRUE or FALSE. WHERE keeps only the rows that are TRUE.
3. NOT IN and NULL together are poison. If there is a NULL in the subquery, NOT IN returns no rows.
4. A question with “every/all” is a division question. Write it with a double NOT EXISTS.
5. COUNT(*) counts rows, COUNT(column) counts non-NULL values.

The sample database is the same as in week 6 (department, instructor, student, course, teaches, takes).

Aggregate functions

There are five basic aggregates: COUNT, SUM, AVG, MIN, MAX. They reduce the many values in a column to one value.

SELECT COUNT(*), COUNT(grade), COUNT(DISTINCT stu_id) FROM takes;

There are 14 records. One has a NULL grade (Cem, CS101), so COUNT(grade) is 13. The number of distinct students who take courses is 6 (Firat is missing).

SELECT SUM(salary), AVG(salary), MIN(salary), MAX(salary) FROM instructor;

In SQLite, AVG always returns REAL (typeof = real), and SUM stays INTEGER on an integer column. That is why AVG(credits) gives 3.4285714285714284. But 7/2 is still 3: only AVG switches to decimals on its own.

On an empty set COUNT returns 0 and the others return NULL: with WHERE salary > 100000, COUNT(*), SUM(salary), AVG(salary) gives 0, NULL, NULL.

GROUP BY and HAVING

GROUP BY splits rows into groups by a common value, and the aggregate is applied to each group separately.

SELECT dept_name, COUNT(*) AS n, AVG(salary) AS avg_sal
FROM instructor GROUP BY dept_name;

HAVING puts a condition on the groups:

SELECT dept_name, AVG(salary) AS avg_sal
FROM instructor GROUP BY dept_name HAVING AVG(salary) > 80000;

WHERE and HAVING together

“Departments that have at least 2 students among those with more than 20 credits”:

SELECT dept_name, COUNT(*) AS n
FROM student
WHERE tot_cred > 20
GROUP BY dept_name
HAVING COUNT(*) >= 2;

Step by step: WHERE first drops Ece (15), Firat (0) and Gizem (10). The rest are grouped: CS 2 (Ada, Bora), EE 1 (Cem), MATH 1 (Defne). HAVING leaves only CS.

FROM student          7 rows
WHERE tot_cred > 20   4 rows: Ada, Bora, Cem, Defne
GROUP BY dept_name    CS{Ada,Bora}  EE{Cem}  MATH{Defne}
HAVING COUNT(*) >= 2  CS
SELECT                CS, 2

PITFALL · an ungrouped column. In standard SQL, every column in the SELECT list that is not an aggregate must be in GROUP BY. SELECT dept_name, name, MAX(salary) FROM instructor GROUP BY dept_name gives an error in PostgreSQL. SQLite gives no error and picks a row from the group for name (when used with MAX, the name of the highest paid: Aksoy, Kaya, Celik, Yildiz). In the exam, count this query as wrong.

Counting zeros

The number of students in each course, including those with no students:

SELECT c.course_id, COUNT(t.stu_id) AS enrolled
FROM course c LEFT JOIN takes t ON c.course_id = t.course_id
GROUP BY c.course_id;

If you had written COUNT(*), BI100 would give 1, because LEFT JOIN brings it with the row (BI100, NULL, NULL).

NULL and three-valued logic

NULL means “unknown” or “absent”. In arithmetic it is contagious: 5 + NULL = NULL. In comparison the result is UNKNOWN: even NULL = NULL is UNKNOWN.

For OR: TRUE OR UNKNOWN = TRUE, FALSE OR UNKNOWN = UNKNOWN. NOT UNKNOWN = UNKNOWN. In SQLite, SELECT NULL = NULL, NULL AND 0, NULL OR 1, NULL AND 1, NOT NULL gives NULL, 0, 1, NULL, NULL (0 = FALSE, 1 = TRUE).

Consequences:

• WHERE dept_name = NULL returns no rows. The right way is WHERE dept_name IS NULL → Gizem.
• WHERE dept_name <> 'CS' returns 3 rows (Cem, Defne, Firat). Gizem does not come: NULL <> 'CS' is UNKNOWN.
• WHERE grade = 'A' OR grade <> 'A' gives 13 rows, not 14. A condition that looks like “everything” misses NULL.
• GROUP BY, on the other hand, treats NULLs as one group: GROUP BY dept_name on students gives NULL 1, CS 3, EE 2, MATH 1.

Subqueries

IN

Students who took a course taught by I1 (Aksoy):

SELECT name FROM student
WHERE stu_id IN (SELECT t.stu_id FROM takes t JOIN teaches te ON t.course_id = te.course_id
                 WHERE te.inst_id = 'I1');

Result: Ada, Bora, Cem, Defne, Ece. Aksoy teaches CS101 and CS305; CS101 was taken by S1, S2, S3, S5, and CS305 by S1, S4. IN produces no duplicates, so Ada appears once.

The NOT IN trap and NOT EXISTS

Let us look for departments with no students:

SELECT dept_name FROM department
WHERE dept_name NOT IN (SELECT dept_name FROM student);

The result is empty. The subquery also returns Gizem’s NULL. 'BIO' NOT IN (CS, EE, MATH, NULL) actually means 'BIO' <> 'CS' AND ... AND 'BIO' <> NULL, and the last term is UNKNOWN. The right way:

SELECT d.dept_name FROM department d
WHERE NOT EXISTS (SELECT * FROM student s WHERE s.dept_name = d.dept_name);

PITFALL · NOT IN + NULL. If the subquery returns even a single NULL, NOT IN is never TRUE. Either use NOT EXISTS or add WHERE ... IS NOT NULL to the subquery. IN has no such problem.

EXISTS and the correlated subquery

EXISTS (subquery) is TRUE if the subquery returns at least one row. If the subquery refers to the outer row, it is called correlated; it is re-evaluated for each outer row.

SELECT c.course_id FROM course c
WHERE EXISTS (SELECT * FROM takes t WHERE t.course_id = c.course_id AND t.grade = 'A');

Result: CS101, CS305, EE210, MA101, MA202 (courses in which at least one A was given).

Those who earn more than their own department’s average:

SELECT i.name, i.salary FROM instructor i
WHERE i.salary > (SELECT AVG(i2.salary) FROM instructor i2 WHERE i2.dept_name = i.dept_name);

Scalar subquery

A subquery that returns one row and one column can be used like a value, even in the SELECT list:

SELECT d.dept_name,
       (SELECT COUNT(*) FROM instructor i WHERE i.dept_name = d.dept_name) AS n_inst
FROM department d;

Result: BIO 1, CS 2, EE 1, MATH 2. A subquery can also be in FROM: SELECT ... FROM (SELECT dept_name, AVG(salary) AS avg_sal FROM instructor GROUP BY dept_name) WHERE avg_sal > 80000 gives the same result as the HAVING example.

ALL and SOME

In standard SQL, x > ALL (set) means “greater than every element of the set”, and x > SOME (set) (or ANY) means “greater than at least one”.

PITFALL · no ALL/SOME in SQLite. > ALL (...) gives a syntax error in SQLite. In the exam, know the standard form, and in SQLite write it with MAX/MIN. Celik at 72000 is greater than the 70000 in MATH but not greater than 72000 (himself): he is in SOME, not in ALL.

Division: “those who took all of them”

There is no direct SQL counterpart of the $\div$ operation in relational algebra. Translate the question “students who took all CS courses” into “students for whom there is no CS course not taken”:

SELECT s.name FROM student s
WHERE NOT EXISTS (
  SELECT c.course_id FROM course c WHERE c.dept_name = 'CS'
  EXCEPT
  SELECT t.course_id FROM takes t WHERE t.stu_id = s.stu_id);

For each student, “the CS courses minus the ones they took” is computed. If the set is empty, the student took all of them. The same query can also be written with a double NOT EXISTS (the questions section has the MATH example).

Set operations

SELECT course_id FROM takes WHERE stu_id = 'S1'
INTERSECT
SELECT course_id FROM takes WHERE stu_id = 'S2';

Result: CS101, CS201. If you turn it into EXCEPT (S1 minus S2), you get CS305, MA101. SELECT course_id FROM course EXCEPT SELECT course_id FROM takes gives the course nobody took: BI100.

SELECT dept_name FROM instructor UNION SELECT dept_name FROM student returns 5 rows (NULL, BIO, CS, EE, MATH); with UNION ALL, 6 + 7 = 13. The two sides must have compatible numbers of columns and types. Standard SQL also has INTERSECT ALL and EXCEPT ALL; SQLite does not.

Changing data: INSERT, UPDATE, DELETE

Each example was run on a fresh copy of the database.

INSERT INTO course (course_id, title, dept_name, credits)
VALUES ('CS410', 'Machine Learning', 'CS', 3);

-- enroll the CS students with 30+ credits in the new course
INSERT INTO takes (stu_id, course_id, grade)
SELECT stu_id, 'CS410', NULL FROM student WHERE dept_name = 'CS' AND tot_cred >= 30;

The second statement inserts two rows, for S1 and S2.

UPDATE instructor SET salary = salary * 1.05 WHERE salary < 75000;

Sahin becomes 73500, Celik 75600, Yildiz 63000, and the others do not change. If there are two different rules, use CASE in a single statement; if you write two separate UPDATEs, a row raised by the first may fall under the condition of the second:

UPDATE instructor
SET salary = CASE WHEN salary < 80000 THEN salary + 5000 ELSE salary + 2000 END;

Result: Aksoy 97000, Demir 82000, Kaya 87000, Sahin 75000, Celik 77000, Yildiz 65000.

DELETE FROM takes WHERE grade = 'F';                     -- 14 -> 13 rows
DELETE FROM takes
WHERE stu_id IN (SELECT stu_id FROM student WHERE tot_cred < 20);   -- 13 -> 12

DELETE FROM instructor WHERE salary < (SELECT AVG(salary) FROM instructor) is interesting: does the average change during the deletion? No. SQL computes the subquery once before deleting (77000) and then deletes. On a copy with FK checking off, Sahin, Celik and Yildiz are deleted. With FK on, the statement is rejected, because Sahin and Celik teach courses in the teaches table.

Views

A view is a saved query that has a name. It copies no data; its definition runs again every time it is used.

CREATE VIEW cs_roster AS
SELECT s.name, t.course_id, t.grade
FROM student s JOIN takes t ON s.stu_id = t.stu_id
WHERE s.dept_name = 'CS';

SELECT course_id, COUNT(*) AS n FROM cs_roster GROUP BY course_id;

When a record for a new CS student is added to the takes table, the view grows from 7 to 8 rows on its own. Views are used for security (showing a user only some columns) and for simplifying complex queries. INSERT through a view is not possible in every system; SQLite says cannot modify cs_roster because it is a view.

Common mistakes

SELECT COUNT(*), COUNT(grade)
FROM takes
WHERE course_id = 'CS101';

SELECT dept_name FROM department
WHERE dept_name NOT IN (SELECT dept_name FROM student);

SELECT name FROM instructor
WHERE salary > ALL (SELECT salary FROM instructor
                    WHERE dept_name = 'MATH');

UPDATE student
SET tot_cred = tot_cred + 5
WHERE stu_id IN (SELECT stu_id FROM takes WHERE grade = 'A');

SELECT dept_name
FROM instructor
WHERE AVG(salary) > 75000
GROUP BY dept_name;