CSE 344: Section 3
Grouping and (Un)Nesting
January 24th, 2019
1
Group By
2
Group By - Examples
Do these queries work?
Enrolled(stu_id, course_num)
SELECT stu_id, course_num
FROM Enrolled
GROUP BY stu_id
SELECT stu_id, count(course_num)
FROM Enrolled
GROUP BY stu_id
3
johndoe | 311 |
johndoe | 344 |
maryjane | 311 |
maryjane | 351 |
maryjane | 369 |
Group By - Examples
Do these queries work?
Enrolled(stu_id, course_num)
SELECT stu_id, course_num
FROM Enrolled
GROUP BY stu_id
SELECT stu_id, count(course_num)
FROM Enrolled
GROUP BY stu_id
4
johndoe | ? |
maryjane | ? |
Group By - Examples
Do these queries work?
Enrolled(stu_id, course_num)
SELECT stu_id, course_num
FROM Enrolled
GROUP BY stu_id
SELECT stu_id, count(course_num)
FROM Enrolled
GROUP BY stu_id
5
johndoe | 2 |
maryjane | 3 |
johndoe | 311 |
johndoe | 344 |
maryjane | 311 |
maryjane | 351 |
maryjane | 369 |
Witnessing (i.e. argmax)
Find the student who is taking the most classes.
Student(stu_id, id_num)
Enrolled(id_num, class)
SELECT S.stu_id
FROM Student S, Enrolled E
WHERE S.id_num = E.id_num
GROUP BY S.stu_id
HAVING COUNT(E.class) >= ALL(
SELECT COUNT(E1.class)
FROM Enrolled E1
GROUP BY E1.id_num);
6
johndoe | 973 |
maryjane | 712 |
alsmith | 899 |
973 | CSE 311 |
973 | CSE 344 |
712 | CSE 311 |
899 | CSE 351 |
Witnessing (i.e. argmax)
Find the student who is taking the most classes.
Student(stu_id, id_num)
Enrolled(id_num, class)
SELECT S.stu_id
FROM Student S, Enrolled E
WHERE S.id_num = E.id_num
GROUP BY S.stu_id
HAVING COUNT(E.class) = (
SELECT MAX(C) FROM (
SELECT COUNT(E1.class) AS C
FROM Enrolled E1
GROUP BY E1.id_num));
7
johndoe | 973 |
maryjane | 712 |
alsmith | 899 |
973 | CSE 311 |
973 | CSE 344 |
712 | CSE 311 |
899 | CSE 351 |
Alternative to “ALL”
Nested Queries
8
Subquery in SELECT
SELECT DISTINCT C.cname, (SELECT count(*)
FROM Product P
WHERE P.cid=C.cid)
FROM Company C
9
Subquery in SELECT
Unnest using JOIN and GROUP BY
SELECT C.cname, count(P.cid)
FROM Company C LEFT OUTER JOIN
Product P ON C.cid = P.cid
GROUP BY C.cname;
10
Subquery in FROM
SELECT X.pname � FROM (SELECT *
FROM Product
WHERE price > 20) AS X
WHERE X.price < 500
More readable: WITH <alias> AS (<subquery>)
11
Subquery in FROM
Unnest using WHERE
SELECT X.pname � FROM Product AS X
WHERE X.price < 500 AND X.price > 20;
12
Subquery in WHERE
SELECT DISTINCT C.cname
FROM Company C
WHERE EXISTS (SELECT *� FROM Product P� WHERE C.cid = P.cid AND P.price < 200)
13
Subquery in WHERE
SELECT DISTINCT C.cname
FROM Company C, Product P
WHERE C.cid = P.cid AND P.price < 200
14
Subquery in WHERE Syntax
15
(Non-)monotonic Queries
SELECT count(*)
FROM T1
GROUP BY T1.attr
16
(Non-)monotonic Queries
SELECT count(*)
FROM T1
GROUP BY T1.attr
No! This query does not satisfy set containment.
Ex:
Current output: {(6), (23), (10)}
After more data: {(6), (23), (11)}
{(6), (23), (10)} ⊄ {(6), (23), (11)}
17
To Nest or Not to Nest
18