1 of 37

CSE 414: Section 2

A SeQueL to SQL

April 6th, 2023

2 of 37

Announcements

HW1 late due date tomorrow

You can use up to 2 late days per assignment

HW2 released yesterday, due April 14th

Name the files EXACTLY as specified

3 of 37

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.

3

4 of 37

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

4

5 of 37

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

5

6 of 37

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] …

6

7 of 37

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;

7

8 of 37

Join Semantics

Think as “nested loops”.

NOT the most efficient implementation on a large database! (we will talk about other ways to join later in the course)
Hash Join
Sort-Merge Join

8

For more information and different types of joins see:

https://blogs.msdn.microsoft.com/craigfr/2006/08/16/summary-of-join-properties/

9 of 37

Nested Loop Semantics

SELECT x_1.a_1, …, x_n.a_n�FROM x_1, …, x_n�WHERE <cond>

for each tuple in x_1:

…

for each tuple in x_n:

if <cond>(x_1, …, x_n):

output(x_1.a_1, …, x_n.a_n)

9

10 of 37

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.

10

11 of 37

Where we started

FWS

(From, Where, Select)

11

12 of 37

And now...

FWGHOS^TM

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

12

13 of 37

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 ง •̀_•́)ง

13

14 of 37

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

...

14

15 of 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

15

johndoe	311
johndoe	344
maryjane	311
maryjane	351
maryjane	369

16 of 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

16

johndoe	?
maryjane	?

17 of 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

17

johndoe	2
maryjane	3

johndoe	311
johndoe	344
maryjane	311
maryjane	351
maryjane	369

18 of 37

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]

18

19 of 37

Worksheet

20 of 37

Question 1

SELECT * FROM A INNER JOIN B ON A.a=B.b;

SELECT * FROM A LEFT OUTER JOIN B ON A.a=B.b;

SELECT * FROM A RIGHT OUTER JOIN B ON A.a=B.b;

SELECT * FROM A FULL OUTER JOIN B ON A.a=B.b;

SELECT * FROM A INNER JOIN B; (Challenging question!)

20

a
1
2
3
4

b
3
4
5
4

21 of 37

Question 1

SELECT * FROM A INNER JOIN B ON A.a=B.b;

SELECT * FROM A LEFT OUTER JOIN B ON A.a=B.b;

SELECT * FROM A RIGHT OUTER JOIN B ON A.a=B.b;

SELECT * FROM A FULL OUTER JOIN B ON A.a=B.b;

SELECT * FROM A INNER JOIN B; (Challenging question!)

21

a	b
3	3
4	4

22 of 37

Question 1

SELECT * FROM A INNER JOIN B ON A.a=B.b;

SELECT * FROM A LEFT OUTER JOIN B ON A.a=B.b;

SELECT * FROM A RIGHT OUTER JOIN B ON A.a=B.b;

SELECT * FROM A FULL OUTER JOIN B ON A.a=B.b;

SELECT * FROM A INNER JOIN B; (Challenging question!)

22

a	b
1
2
3	3
4	4

23 of 37

Question 1

SELECT * FROM A INNER JOIN B ON A.a=B.b;

SELECT * FROM A LEFT OUTER JOIN B ON A.a=B.b;

SELECT * FROM A RIGHT OUTER JOIN B ON A.a=B.b;

SELECT * FROM A FULL OUTER JOIN B ON A.a=B.b;

SELECT * FROM A INNER JOIN B; (Challenging question!)

23

a	b
3	3
4	4
	5
	6

24 of 37

Question 1

SELECT * FROM A INNER JOIN B ON A.a=B.b;

SELECT * FROM A LEFT OUTER JOIN B ON A.a=B.b;

SELECT * FROM A RIGHT OUTER JOIN B ON A.a=B.b;

SELECT * FROM A FULL OUTER JOIN B ON A.a=B.b;

SELECT * FROM A INNER JOIN B; (Challenging question!)

24

a	b
1
2
3	3
4	4
	5
	6

25 of 37

Question 1

SELECT * FROM A INNER JOIN B ON A.a=B.b;

SELECT * FROM A LEFT OUTER JOIN B ON A.a=B.b;

SELECT * FROM A RIGHT OUTER JOIN B ON A.a=B.b;

SELECT * FROM A FULL OUTER JOIN B ON A.a=B.b;

SELECT * FROM A INNER JOIN B; (Challenging question!)

25

a	b
1	3
1	4
1	5
1	6
2	3
…	…
4	6

26 of 37

Question 2

CREATE TABLE Employees (id int, bossOf int);

Suppose all employees have an id which is not null. How would we find all distinct pairs of employees with the same boss?

27 of 37

Question 2

CREATE TABLE Employees (id int, bossOf int);

Suppose all employees have an id which is not null. How would we find all distinct pairs of employees with the same boss?

SELECT E1.id, E2.id

FROM Employees AS E1, Employees AS E2

WHERE E1.id < E2.id AND E1.bossOf = E2.bossOf;

28 of 37

Question 3

CREATE TABLE Movies ( id int, name varchar(30), budget int, gross int, rating int, year int, PRIMARY KEY (id) );

CREATE TABLE Actors ( id int, name varchar(30), age int, PRIMARY KEY (id) );

CREATE TABLE ActsIn ( mid int, aid int, FOREIGN KEY (mid) REFERENCES Movies (id), FOREIGN KEY (aid) REFERENCES Actors (id) );

Movies

Acts in

Actor

29 of 37

Question 3