CSE 414: Section 2

A SeQueL to SQL

October 8th, 2026

Announcements

• HW1 due tomorrow, 10/09 at 11:59 PM
• You can use up to 2 late days per assignment
• Late deadline 10/11 at 11:59pm
• HW2 released tomorrow, due 10/16 @ 11:59pm
• Name the files EXACTLY as specified
• OH update - 1:30-2:30 Mondays @ Allen 2nd Floor Breakout

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

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

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

5

0.5 AND 1 = min(0.5, 1) = 0.5 aka UNKNOWN

0.5 OR 0 = max(0.5, 0) = 0.5 aka UNKNOWN

Examples:

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 SELECT and FROM clause (i.e. “AS” keyword can be omitted)

SELECT [attribute] AS [attribute_name]

FROM [table] AS [table_name]

… [table_name].[attribute_name] …

​

​

6

Misc. Filters

LIMIT number - limits the number of tuples returned

[ex] SELECT * FROM table LIMIT 1;

DISTINCT - only returns unique values (eliminates duplicates)

[ex] SELECT DISTINCT column_name FROM table;

​

7

Joins Review

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

9

For more information and different types of joins see:

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

​

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)

10

Join example:

Join Types

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

There is never a scenario in this class we need to use a right outer join and you most likely won’t need full outer join for this class.

11

Where we started

FWS

(From, Where, Select)

12

And now...

FJWGHOS

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

13

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

14

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

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

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

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

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 LEFT OUTER JOIN B

ON A.a=B.b;

What’s the output for each of the following:

1.3:

​

SELECT *

FROM A INNER JOIN B;

(challenge 😍)

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 - Extra Right Outer Join Example

Extra Example:

​

SELECT *

FROM A RIGHT OUTER JOIN B

ON A.a=B.b;

A

B

Worksheet Exercise 1: Joins

Worksheet Exercise 1: Joins - Extra Right Outer Join Example

Extra Example:

​

SELECT *

FROM A RIGHT OUTER JOIN B

ON A.a=B.b;

A

B

Worksheet Exercise 1: Joins

Worksheet Exercise 1: Joins - Extra Right Outer Join Example

Extra Example:

​

SELECT *

FROM A RIGHT OUTER JOIN B

ON A.a=B.b;

A

B

Worksheet Exercise 1: Joins

Worksheet Exercise 1: Joins - Extra Right Outer Join Example

Extra Example:

​

SELECT *

FROM A RIGHT OUTER JOIN B

ON A.a=B.b;

A

B

?

?

Worksheet Exercise 1: Joins

Worksheet Exercise 1: Joins - Extra Right Outer Join Example

Extra Example:

​

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.