CSE 344: Section 3

CSE 344

Section 3

​

Announcements

• Homework 2
• Late deadline tonight at 10pm!
• Homework 3
• Due at 10:00 pm on Tuesday, April 22
• Similar to HW2, but with outer joins + grouping/aggregation
• Questions?

​

Where we started

FWS

(From, Where, Select)

3

And now...

FWGHOS^TM

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

4

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

...

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
• Else, SQLite will guess (arbitrarily pick a value)¯\_(ツ)_/¯
• SQL Server will throw an error ง •̀_•́)ง

6

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;

​

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

Having

• Apply predicate to entire groups
• where clause: applies to single tuple
• having: applies to an entire group – either keep the group or drop it

​

• Can only check attributes in GROUP-BY or aggregates

​

​

Having Example

Minor modification to previous query:

​

Enrolled(stu_id, course_num)

​

​

​

SELECT stu_id, count(course_num)

FROM Enrolled

GROUP BY stu_id

HAVING count(course_num) > 2;

11

Only one row output!

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]

12

Subqueries

Subqueries

• 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

Subquery in SELECT

15

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

FROM Product P

WHERE P.cid = C.cid)

FROM Company C;

​

Subquery in SELECT

Unnest using JOIN and GROUP BY

​

16

SELECT C.cname, COUNT(P.cid)

FROM Company C LEFT OUTER JOIN

Product P ON C.cid = P.cid

GROUP BY C.cname;

​

Subquery in FROM

17

SELECT X.pname

FROM (SELECT *

FROM Product

WHERE price > 20) AS X

WHERE X.price < 500;

​

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

Subquery in FROM

18

WITH price_more_20 AS (

SELECT *

FROM Product

WHERE price > 20

)

SELECT X.pname

FROM price_more_20 AS X

WHERE X.price < 500;

Subquery in FROM

Unnest using WHERE

​

19

SELECT X.pname

FROM Product AS X

WHERE X.price < 500 AND X.price > 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>);

20

Subquery in WHERE (example 1)

21

SELECT DISTINCT C.cname

FROM Company C

WHERE EXISTS (SELECT *

FROM Product P

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

Subquery in WHERE (example 1)

Unnest to:

​

22

SELECT DISTINCT C.cname

FROM Company C, Product P

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

Subquery in WHERE (example 2)

23

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

Subquery in WHERE (example 2)

24

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

Stay tuned for subqueries in WHERE!

Useful Operators

CASE Expressions

• Conditional expression
• Execute different actions based on conditions
• Often used in SELECT statements

​

CASE

WHEN condition1 THEN result1

WHEN condition2 THEN result2

…

ELSE default

END

CASE Expression Example

Using the Payroll Table from Lecture:

Let’s arbitrarily say that I know undergrad TAs make less than 50k and grad TAs make over 50k. I want to display TAs with their enrolled degree level.

28

CAE Expression Example Continued

Query:

Output:

SELECT

Name,

CASE

WHEN Salary < 50000 THEN ‘Undergrad’

ELSE ‘Grad’

END AS ‘Degree Level’

FROM Payroll

WHERE Job = ‘TA’;

Name Degree Level

- - - - - - - - - - - -

Leslie Undergrad

Frances Grad

UNION

• Combines results from multiple queries into 1 set
• UNION gets rid of duplicates
• UNION ALL preserves duplicates
• Rules
• Both queries have same number of columns
• Matching columns have compatible data types
• Column names of first query are final output names
• GROUP BYs and HAVINGs apply to individual queries
• ORDER BY applies to final result

Query1

UNION [ALL]

Query2;

UNION Example

Take the tables:

Students Staff

I want to combine these two tables, so that I get a list of everyone I want to add to the CSE344 Ed Board.

31

UNION Example Continued

Query:

Output:

Things to consider - think about the context:

• Maybe I should have used a UNION ALL?
• IDs would probably serve me better than names
• If I am giving this list to someone else, I should probably rename the output column

SELECT StudentNames FROM Students

UNION

SELECT StaffNames FROM Staff;

StudentNames

- - - - - - - -

Alice

Bob

Hannah

Ryan

Worksheet