CSE 414: Section 2

A SeQueL to SQL

October 8, 2026

​

Announcements

• Homework 1
• Due tomorrow, 10/9 at 11:59 PM
• You can use up to 2 late days per assignment
• Late deadline 10/11 at 11:59 PM
• Homework 2
• TBD, but soon
• Name the files EXACTLY as specified

​

• Questions?

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

​

NOTE: SQLite dot commands like .mode and .import do NOT use semicolons

3

Importing Files cont’d (HW2)

Make sure you create and 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.

​

4

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

5

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

6

Aliasing

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

SELECT [attribute] AS [attribute_name]

FROM [table] AS [table_name]

… [table_name].[attribute_name] …

​

​

7

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;

​

8

Joins Review

Join Types

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

Right joins aren’t necessary. Why?

10

Nested-Loop Semantics

11

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

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.

Remaining Q1

Where we started

FWS

(From, Where, Select)

23

And now...

FWGHOS^TM

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

24

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

25

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

​

26

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

27

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

28

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]

29

Remaining Q2