CSE 344: Section 2

A SeQueL to SQL

CSE 344

Section 2

​

Announcements

• Homework 1:
• Late deadline tonight at 10pm!
• Homework 2:
• Out now!
• Due at 10pm on Tuesday, January 20th
• Creating and importing databases, running queries with SQLite
• We will also learn how to import the datasets today
• 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

Importing Files cont’d (HW2): Demo

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

6

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

7

Worksheet Exercise 3:

3-Valued Logic

Worksheet Exercise 3: 3-Valued Logic

CREATE TABLE A (a int, b int);

INSERT INTO A VALUES (1, 1), (2, 10), (3, NULL);

Given tables created with these commands:

3.1:

​

SELECT A.a FROM A� WHERE A.b < 5

What’s the output for each of the following:

3.2:

​

SELECT A.a FROM A� WHERE A.b >= 5

Worksheet Exercise 3: 3-Valued Logic

(1, 1) 1 < 5

True

​

(2, 10) 10 < 5

False

​

(3, NULL); NULL < 5

Unknown

​

3.1:

​

SELECT A.a FROM A� WHERE A.b < 5

Worksheet Exercise 3: 3-Valued Logic

3.2:

​

SELECT A.a FROM A� WHERE A.b >= 5

(1, 1) 1 >= 5

False

​

(2, 10) 10 >= 5

True

​

(3, NULL); NULL >= 5

Unknown

​

Worksheet Exercise 3: 3-Valued Logic

CREATE TABLE A (a int, b int);

INSERT INTO A VALUES (1, 1), (2, 10), (3, NULL);

Given tables created with these commands:

3.3:

​

SELECT A.a FROM A� WHERE A.b != 1 AND

A.b != 10;

What’s the output for each of the following:

3.4:

​

SELECT A.a FROM A� WHERE A.b < 5 OR

A.b IS NULL;

Worksheet Exercise 3: 3-Valued Logic

3.3:

​

SELECT A.a FROM A� WHERE A.b != 1 AND

A.b != 10;

(1, 1) 1 != 1 AND 1 != 10

False && True

== False

​

(2, 10) 10 != 1 AND 10 != 10

True && False

== False

​

(3, NULL); NULL != 1 AND NULL != 10

Unknown && Unknown

== Unknown

​

Worksheet Exercise 3: 3-Valued Logic

3.4:

​

SELECT A.a FROM A� WHERE A.b < 5 OR

A.b IS NULL;

(1, 1) 1 < 5 OR 1 IS NULL

True || False

== True

​

(2, 10) 10 < 5 OR 10 IS NULL

False || False

== False

​

(3, NULL); NULL < 5 OR NULL IS NULL

Unknown || True

== True

​

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

​

​

15

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;

​

16

Useful Operators

UPPER(x) - returns a copy of input string x with an all-uppercase ASCII equivalent

LOWER(x) - returns an all-lowercase equivalent of input x

​

LIKE - does pattern matching where ‘%’ matches a sequence of 0 or more characters and ‘_’ matches any single character

​

|| - This does string concatenation, it is NOT an OR operator

Operator Examples

CREATE TABLE Courses (name VARCHAR(10));

INSERT INTO Courses VALUES (‘Cse344’);

Given the table created with these commands:

SELECT name

FROM Courses;

What’s the output for each of the following:

Output:

name

- -

Cse344

SELECT UPPER(name)

FROM Courses;

Output:

name

- -

CSE344

SELECT LOWER(name)

FROM Courses;

Output:

name

- -

cse344

Operator Examples Continued

CREATE TABLE Courses (name VARCHAR(10));

INSERT INTO Courses VALUES (‘Cse344’);

Using the same table:

SELECT name

FROM Courses

WHERE name LIKE ‘%444%’;

What’s the output for each of the following:

Output:

SELECT name

FROM Courses

WHERE name LIKE ‘cse%’;

Output:

name

- -

Cse344

SELECT name || name

AS output

FROM Courses;

Output:

output

- -

Cse344Cse344

Nothing in our table contains ‘444’!

Looking for what starts with ‘cse’

(not case sensitive)!

"=" vs "==" in SQL

• SQL queries use "=" instead of "==" for filtering
• SQL is a declarative language, so there is no need to distinguish between "=" for assigning variables, and "==" for conditions

​

​

20

Correct Example:

SELECT *

FROM Payroll

WHERE Payroll.job = ‘TA’;

Incorrect Example:

SELECT *

FROM Payroll

WHERE Payroll.job == ‘TA’;

Joins Review

Join Types

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

Right joins and full joins aren’t necessary. Why?

22

Inner Joins

• Bread and butter of SQL queries
• "Inner join" is often interchangeable with just "join"

23

Payroll

Regist

Nested-Loop Semantics

24

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 Worksheet