CSE 344: Section 3

Subqueries

January 20th, 2021

1

Announcements

• HW2 deadline extended to last night
• HW3 Due Tuesday 1/25 now
• Please try to follow along as we set up the azure database together during section.
• This is long and confusing at times and is not something staff will be able to help with at the last minute

2

HW3 Setup Demo

https://docs.google.com/document/d/1tFPsFv6-7nSk49zPl6c_G4dFS6oMBxhnwyUMj9T2nO0/edit

​

Nested Queries

• Avoid when possible
• Danger of making simple queries slow and complicated
• Just because you can do it, doesn’t mean you should

4

Monotonic Query

Query that does not lose any output tuples with the addition of new tuples in the database.

​

Theorem/helpful hint: If a query is a SELECT-FROM-WHERE query without nested subqueries or aggregates than it is monotone.

​

Consequence: If a query is not monotonic, then we cannot write it as a SELECT-FROM-WHERE query without nested subqueries or aggregates.

(Non-)monotonic Queries

• “Can we take back outputs by looking at more data?”
• Is this a monotonic query?

​

SELECT count(*)

FROM T1

GROUP BY T1.attr

6

(Non-)monotonic Queries

• “Can we take back outputs by looking at more data?”
• Is this a monotonic query?

​

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)}

7

Subquery in SELECT

SELECT DISTINCT C.cname, (SELECT count(*)

FROM Product P

WHERE P.cid=C.cid)

FROM Company C

​

​

​

8

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;

9

Subquery in FROM

SELECT X.pname � FROM (SELECT *

FROM Product

WHERE price > 20) AS X

WHERE X.price < 500

​

​

More readable: WITH <name> AS (<subquery>)

10

Subquery in FROM

WITH price_more_20 AS

(

SELECT *

FROM Product

WHERE price > 20

)

SELECT X.pname � FROM price_more_20 AS X

WHERE X.price < 500;

​

​

​

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

• SELECT ……… WHERE EXISTS (<sub>);
• SELECT ……… WHERE NOT EXISTS (<sub>);
• SELECT ……… WHERE attribute IN (<sub>);
• SELECT ……… WHERE attribute NOT IN (<sub>);
• SELECT ……… WHERE attribute > ANY (<sub>);
• SELECT ……… WHERE attribute > ALL (<sub>);

15

Witnessing (i.e. argmax)

Find the student(s) 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);

16

Witnessing (i.e. argmax) (another way)

With class_counts as (SELECT count(E1.class)as cnt FROM Enrolled E1 GROUP BY E1.id_num),

max_counts as (SELECT MAX(cnt)as max FROM class_counts)

SELECT S.stu_id

FROM Students S, Enrolled E, max_counts Emax

WHERE S.id_num = E.id_num

GROUP BY S.stu_id, Emax.max

HAVING count(E.class) = Emax.max;

17

To Nest or Not to Nest

• Not an exact science
• Figuring out what is actually wanted will help you find simpler solutions (best way is to practice)
• Trigger words to use sub-querying
• Every, All (universal quantifiers)
• No, None, Never (negation)
• Only

18