1 of 24

CSE 344: Section 3

Azure setup + Subqueries

Oct 10th, 2024

2 of 24

Announcements

Homework 3:

Due at 11:00 pm on Wednesday, October 23
Similar to HW2, but harder queries + on Azure

We will be setting up the Azure database for HW3 together

This is long and confusing at times and is not something staff will be able to help with at the last minute
The video is a good reference of each step if you fall behind

Questions?

3 of 24

HW3 Setup Demo

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

@2:28:

You must set “Allow services and resources to access this server” to YES (not mentioned in video. See step L in the doc)

4 of 24

Nested Queries Review!

5 of 24

Nested Queries

Queries inside other queries

Usually simplifies or factors out part of the outer query
Use case is similar to helper methods

Can go in multiple places!

SELECT
FROM
WHERE/HAVING
... basically anywhere we use a table

6 of 24

Nested Queries

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

6

7 of 24

Subquery in SELECT

7

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

FROM Product P

WHERE P.cid = C.cid)

FROM Company C;

8 of 24

Subquery in SELECT

Unnest using JOIN and GROUP BY

8

SELECT C.cname, count(P.cid)

FROM Company C LEFT OUTER JOIN

Product P ON C.cid = P.cid

GROUP BY C.cname;

9 of 24

Nested Queries

If the SQL subquery returns exactly one value it can be used where we use a field

“one value” = one tuple with one attribute

Otherwise, a SQL subquery can be thought of as an "extra table"

Can name the table, its columns, etc

9

10 of 24

Subquery in FROM

10

SELECT X.pname

FROM (SELECT *

FROM Product

WHERE price > 20) AS X

WHERE X.price < 500;

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

11 of 24

Subquery in FROM

11

WITH price_more_20 AS (

SELECT *

FROM Product

WHERE price > 20

)

SELECT X.pname

FROM price_more_20 AS X

WHERE X.price < 500;

12 of 24

Subquery in FROM

Unnest using WHERE

12

SELECT X.pname

FROM Product AS X

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

13 of 24

Subquery in WHERE (example 1)

13

SELECT DISTINCT C.cname

FROM Company C

WHERE EXISTS (SELECT *

FROM Product P

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

14 of 24

Subquery in WHERE (example 1)

14

SELECT DISTINCT C.cname

FROM Company C, Product P

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

15 of 24

Subquery in WHERE (example 2)

15

SELECT S1.major, S1.num_of_students, S1.school_name

FROM Schools S1

WHERE S1.num_of_students >= ALL (SELECT S2.num_of_students

FROM Schools S2

WHERE S1.school_name =

S2.school_name);

16 of 24

Subquery in WHERE (example 2)

16

SELECT S1.major, S1.num_of_students, S2.school_name

FROM Schools S1, Schools S2

WHERE S1.school_name = S2.school_name

GROUP BY S1.major, S1.num_of_students, S2.school_name

HAVING S1.num_of_students = MAX(S2.num_of_students);

17 of 24

Subquery in WHERE Syntax

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

17

Note equivalence

EXISTS will tell you whether a query returned any results. E.g.:

SELECT *

FROM Orders o

WHERE EXISTS

( SELECT *

FROM Products p

WHERE p.ProductNumber = o.ProductNumber

)

IN is used to compare one value to several, and can use literal values, like this:

SELECT *

FROM Orders

WHERE ProductNumber IN (1, 10, 100)

You can also use query results with the IN clause, like this:

SELECT *

FROM Orders

WHERE ProductNumber IN

(

SELECT ProductNumber

FROM Products

WHERE ProductInventoryQuantity > 0

)

The ANY and ALL operators allow you to perform a comparison between a single column value and a range of other values. Best for min/max problems but have other applications. For instance:

select *

from Table1 t1

where t1.Col1 < ANY

(

select value

from Table2

)

ANY means that the condition will be satisfied if the operation is true for any of the values in the range.

ALL means that the condition will be satisfied only if the operation is true for all values in the range.

18 of 24

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

18

johndoe	973
maryjane	712
alsmith	899

973	CSE 311
973	CSE 344
712	CSE 311
899	CSE 351

19 of 24

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

19

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;

20 of 24

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

20

21 of 24

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 then 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.

22 of 24

(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

22

23 of 24

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

23

24 of 24

Worksheet!