CSE 344: Section 2

Joins, SQL Aggregates

Oct 5th, 2023

​

Announcements

• Homework 2:
• Due at 10:00 pm on Tuesday, October 10th.
• Will still be using sqlite3
• Get comfortable making files and running them through sqlite3
• Ask today if you’re still confused on how to do this!!
• We will also learn how to import the datasets today if you are still confused

​

• Go to the class website and open the worksheet. It will be important to follow along with it today!

Joins Review

Joins

• Foreign keys describe a relationship between tables

​

• Joins realize combinations of data
• Sometimes the data is combined using a foreign key, but not always!

Inner Joins

• Bread and butter of SQL queries
• “Inner join” is often interchangeable with just “join”

5

Payroll

Regist

Nested-Loop Semantics

6

SELECT P.Name, R.Car

FROM Payroll AS P JOIN Regist AS R

ON P.UserID = R.UserID;

foreach row1 in Payroll:

foreach row2 in Regist:

if row1.userID == row2.userID

output(row1.name, row2.car)

Payroll

Regist

Join Types

There will be times we use inner join, full join, and left outer join.

There is never a scenario in this class we need to use a right outer join and sqlite3 does not support this operation. It also doesn’t support full outer join, which you most likely won’t need for this class.

7

Worksheet Exercise 1: Joins

Worksheet Exercise 1: Joins

CREATE TABLE A (a int);

CREATE TABLE B (b int);

INSERT INTO A VALUES (1), (2), (3), (4);

INSERT INTO B VALUES (3), (4), (5), (6);

Given tables created with these commands:

1.1:

​

SELECT *

FROM A INNER JOIN B

ON A.a=B.b;

1.2:

​

SELECT *

FROM A RIGHT OUTER JOIN B

ON A.a=B.b;

What’s the output for each of the following:

Worksheet Exercise 1: Joins

1.1:

​

SELECT *

FROM A INNER JOIN B

ON A.a=B.b;

A

B

Worksheet Exercise 1: Joins

1.1:

​

SELECT *

FROM A INNER JOIN B

ON A.a=B.b;

A

B

Worksheet Exercise 1: Joins

1.1:

​

SELECT *

FROM A INNER JOIN B

ON A.a=B.b;

A

B

Output:

a b

- -

3 3

4 4

Worksheet Exercise 1: Joins

1.2:

​

SELECT *

FROM A RIGHT OUTER JOIN B

ON A.a=B.b;

A

B

Worksheet Exercise 1: Joins

1.2:

​

SELECT *

FROM A RIGHT OUTER JOIN B

ON A.a=B.b;

A

B

Worksheet Exercise 1: Joins

1.2:

​

SELECT *

FROM A RIGHT OUTER JOIN B

ON A.a=B.b;

A

B

Worksheet Exercise 1: Joins

1.2:

​

SELECT *

FROM A RIGHT OUTER JOIN B

ON A.a=B.b;

A

B

?

?

Worksheet Exercise 1: Joins

1.2:

​

SELECT *

FROM A RIGHT OUTER JOIN B

ON A.a=B.b;

A

B

Output:

a b

- -

3 3

4 4

5

6

NULL

NULL

NULL is SQL’s placeholder value.

​

Depending on your data, you can interpret it to mean unknown, not applicable, etc.

Importing Files (HW2)

First, create the table.�Then, import the data.

.mode csv� .import population.csv Population� .import gdp.csv GDP

.import /path/to/file NameOfTable

Make sure you import the tables in the order you create them so there are no foreign key constraint issues. For example, if GDP had a foreign key constraint to Population, it would be illegal to import GDP before Population.

18

Aliasing

• Good style for renaming attribute operations to more intuitive labels
• Essential for self joins (ex: FROM [table] AS T1, [table] AS T2)
• You can alias without “AS” in the FROM clause (i.e. “AS” keyword can be omitted)

SELECT [attribute] AS [attribute_name]

FROM [table] AS [table_name]

… [table_name].[attribute_name] …

​

​

19

Misc. Filters

LIMIT number - limits the amount of tuples returned

[ex] SELECT * FROM table LIMIT 1;

DISTINCT - only returns unique values (eliminates duplicates)

[ex] SELECT DISTINCT column_name FROM table;

​

20

SQL 3-Valued Logic

SQL has 3-valued logic

• FALSE = 0

[ex] price < 25 is FALSE when price = 99

• UNKNOWN = 0.5

[ex] price < 25 is UNKNOWN when price = NULL

• TRUE = 1

[ex] price < 25 is TRUE when price = 19

21

SQL 3-Valued Logic (con’t)

Formal definitions:

C1 AND C2 means min(C1,C2)� C1 OR C2 means max(C1,C2)� NOT C means means 1-C

​

The rule for SELECT ... FROM ... WHERE C is the following:� if C = TRUE then include the row in the output� if C = FALSE or C = unknown then do not include it

22

Worksheet Exercise 3:

3-Valued Logic

Worksheet Exercise 3: 3-Valued Logic

CREATE TABLE A (a int, b int);

INSERT INTO A VALUES (1, 1), (2, 10), (3, NULL);

Given tables created with these commands:

3.1:

​

SELECT A.a FROM A� WHERE A.b < 5

What’s the output for each of the following:

3.2:

​

SELECT A.a FROM A� WHERE A.b >= 5

Worksheet Exercise 3: 3-Valued Logic

(1, 1) 1 < 5

True

​

(2, 10) 10 < 5

False

​

(3, NULL); NULL < 5

Unknown

​

3.1:

​

SELECT A.a FROM A� WHERE A.b < 5

Worksheet Exercise 3: 3-Valued Logic

3.2:

​

SELECT A.a FROM A� WHERE A.b >= 5

(1, 1) 1 >= 5

False

​

(2, 10) 10 >= 5

True

​

(3, NULL); NULL >= 5

Unknown

​

Worksheet Exercise 3: 3-Valued Logic

CREATE TABLE A (a int, b int);

INSERT INTO A VALUES (1, 1), (2, 10), (3, NULL);

Given tables created with these commands:

3.3:

​

SELECT A.a FROM A� WHERE A.b != 1 AND

A.b != 10;

What’s the output for each of the following:

3.4:

​

SELECT A.a FROM A� WHERE A.b < 5 OR

A.b IS NULL;

Worksheet Exercise 3: 3-Valued Logic

3.3:

​

SELECT A.a FROM A� WHERE A.b != 1 AND

A.b != 10;

(1, 1) 1 != 1 AND 1 != 10

False && True

== False

​

(2, 10) 10 != 1 AND 10 != 10

True && False

== False

​

(3, NULL); NULL != 1 AND NULL != 10

Unknown && Unknown

== Unknown/False

​

Worksheet Exercise 3: 3-Valued Logic

3.4:

​

SELECT A.a FROM A� WHERE A.b < 5 OR

A.b IS NULL;

(1, 1) 1 < 5 OR 1 IS NULL

True || False

== True

​

(2, 10) 10 < 5 OR 10 IS NULL

False || False

== False

​

(3, NULL); NULL < 5 OR NULL IS NULL

Unknown || True

== True

​

SQL Demo!

30

“=” vs “==” in SQL

• SQL queries use “=” instead of “==” for filtering
• SQL is a declarative language, so there is no need to distinguish between “=” for assigning variables, and “==” for conditions

​

​

31

Correct Example:

SELECT *

FROM Payroll

WHERE Payroll.job = ‘TA’;

Incorrect Example:

SELECT *

FROM Payroll

WHERE Payroll.job == ‘TA’;

Where we started

FWS

(From, Where, Select)

32

And now...

FJWGHOS^TM

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

33

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 ง •̀_•́)ง
• PLEASE GROUP BY ID VALUES IF THEY ARE AVAILABLE
• this is THE MOST COMMON WAY TO MESS UP SQL
• EX: group on uw-net id instead of name as there may be multiple people with the same name

34

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

...

35

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

36

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

37

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

38

Grouping and Ordering

GROUP BY [attribute_1], …, [attribute_n]

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

ORDER BY [attribute] [ASC/DESC]

39

Worksheet