CSE 414: Section 2
A SeQueL to SQL
October 8th, 2026
Announcements
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
[ex] price < 25 is FALSE when price = 99
[ex] price < 25 is UNKNOWN when price = NULL
You can’t compare NULL normally - use “IS NULL” instead of “= NULL”
[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 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
Aliasing
SELECT [attribute] AS [attribute_name]
FROM [table] AS [table_name]
… [table_name].[attribute_name] …
6
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; (applies to the entire row)
Btw using LIMIT without ORDER BY gives u that number of rows but doesn’t guarantee which rows u get (its not gonna be the “first three”), so use them together
7
Join Semantics
8
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)
9
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 (SQLite only started supporting this recently) and you most likely won’t need full outer join for this class.
10
Join Types (a summary?)
Inner join - only keep rows from both tables that match given conditions
Left join - keep everything from left table first & append right if it matches
Right join - keep everything from right table first & append left if it matches
Full join - all matching pairs + all unmatched rows
11
Where we started
FWS
(From, Where, Select)
12
And now...
FWGHOSTM
(From, Where, Group By, Having, Order By, Select)
13
Group By
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
johndoe | 311 |
johndoe | 344 |
maryjane | 311 |
maryjane | 351 |
maryjane | 369 |
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 | ? |
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 |
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