CSE 414: Section 2

A SeQueL to SQL

October 3rd, 2024

Announcements

• HW1 due tomorrow, 10/4 at 11 PM
• You can use up to 2 late days per assignment
• HW2 released tomorrow, due ____
• Name the files EXACTLY as specified

Importing Files (HW2)

First, create the tables (ie Population, GDP)�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

Helpful tip: On Mac, to get the /path/to/file: Find the file in Finder -> right clicking on it -> hold down ‘Option’ -> click ‘copy as Pathname’ -> path will be copied to your clipboard and can be pasted wherever. However this will only work if you are in your home directory (‘~’ right after your machine name in terminal).

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

• 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

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

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/

​

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

INNER JOIN gives matched rows only.

LEFT OUTER JOIN gives all rows from the left table and matches from the right, filling in NULL if no match exists.

RIGHT OUTER JOIN gives all rows from the right table and matches from the left, filling in NULL if no match exists.

FULL OUTER JOIN gives all rows from both tables, with NULL where there are no matches.

CARTESIAN JOIN (cross join) returns all possible combinations of rows between the two tables.

​

10

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 sqlite3 does not support this operation. It also doesn’t support full outer join, which you most likely won’t need for this class.

11

Where we started

FWS

(From, Where, Select)

12

And now...

FWGHOS^TM

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

13

*Everything after Group by (e.g. Having, Order by, Select) can only have aggregates or the attributes Group by used.

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

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

...

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

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

18

Grouping and Ordering

GROUP BY [attribute], …, [attribute_n]

HAVING [predicate] - operates on groups that you have grouped by (like WHERE but for groups*), chooses to keep or remove the entire group

ORDER BY [attribute] [ASC/DESC]

19

*The main difference between the WHERE and HAVING clauses comes when used together with the GROUP BY clause – WHERE is used to filter rows before grouping, and HAVING is used to exclude records after grouping.

^^^Remember needs to be on groups or aggregates if there is a GROUP BY in your query!

Worksheet