CSE 414: Section 3

(Un)Nesting Queries

October 13th, 2022

​

1

Joke of the Day!

2

Administrivia

• HW2 due October 17th @ 11pm
• HW1 grades out by sometime next week
• Using Microsoft Azure for HW3
• Next week we will do an Azure setup demo in section

3

Where we started

FWS

(From, Where, Select)

4

And now...

FWGHOS^TM

(From, Where, Group By, Having, Order By, Select)

5

Group By

• Powerful tool to handle “categories”
• Groups rows with the same value of an attribute into a “bucket” (think dividing into categories)
• Careful when selecting
• Only select attributes in GROUP BY or aggregates
• SQLite will guess (arbitrarily pick a value)¯\_(ツ)_/¯
• SQL Server will throw an error ง •̀_•́)ง

6

Aggregates

• Computes summary values for a set of tuples.

COUNT(attribute) - counts the number of tuples

SUM(attribute) - sums the value of the attribute among all tuples in set

MIN/MAX(attribute) - min/max value of the attribute among all tuples in the set

AVG(attribute) - avg value of the attribute among all tuples in the set

...

7

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

​

8

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

9

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

10

Grouping and Ordering

GROUP BY [attribute], …, [attribute_n]

HAVING [predicate] - operates on groups, chooses to keep or remove the entire group

ORDER BY [attribute] [ASC/DESC]

11

RECAP: THE WITNESSING PROBLEM

12

Difference?

13

Subquery (Nested Queries)

• Not always the best solution!
• Danger of making simple queries slow and complicated
• Just because you can do it, doesn’t mean you should

14

Subquery in SELECT

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

FROM Product P

WHERE P.cid=C.cid)

FROM Company C

​

​

​

15

Non-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;

16

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>)- Saying FROM X AS (...)

17

Non-Subquery in FROM

Unnest using WHERE

​

SELECT X.pname � FROM Product AS X

WHERE X.price < 500 AND X.price > 20;

18

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)

​

19

Non-Subquery in WHERE

SELECT DISTINCT C.cname

FROM Company C, Product P

WHERE C.cid = P.cid AND P.price < 200

​

20

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

21

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

22

Correlated vs. Decorrelated Subquery

• Uncorrelated subquery
• When the subquery is executed only once and the result of the subquery is reused for every query
• No reference to tables in main query, written as its own query.
• Usually used when you want to create another table, and then write a main query that uses that new table
• Commonly written in SELECT, FROM, WITH … AS …
• Correlated subquery
• When a subquery refers to the main query (attribute in main query) for each execution (and there will be multiple executions performed)
• This means that the sub query runs for EVERY SINGLE tuple in the main query!
• WHERE + EXISTS/NOT EXISTS will usually be used in correlated subqueries

​

23

Correlated vs. Decorrelated Subquery

24

(AGAIN) 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);

25

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

26

Alternative to “ALL”

Section Feedback Form!

27