SQL (Part 2)

Topic / section title

Handling NULL

Handling NULL — what can NULL mean?

NULL can mean: Unknown (not added), Unavailable (can’t be given out), or Not applicable.

NULL and equality (key rule)

NULL can’t be used for equality.

Why NULL can’t be used for equality

Two NULL values may be the same, but we don’t know that.

NULL in WHERE clauses — the question

If NULL appears in a comparison in WHERE, is the result True, False, or something else?

Comparing 2 known (non-NULL) values

Result is clearly TRUE or FALSE.

If NULL is one of the compared values

Depends on meaning: if “not applicable” (and we somehow knew it), answer would be FALSE; if “unknown” or “unavailable”, we can’t say true/false → UNKNOWN.

Three-valued logic name

“Three-value logic” (TRUE, FALSE, UNKNOWN).

What does UNKNOWN generally mean in WHERE?

Generally means “do not select this row”.

UNKNOWN with one condition

Easy: if the condition evaluates to UNKNOWN, don’t select the row.

UNKNOWN with multiple conditions

More complicated with ANDs, ORs, and NOTs (but “common sense” per slides).

Three-valued logic — AND table (overall rule)

AND: either being FALSE means FALSE; both TRUE means TRUE; otherwise UNKNOWN (could go either way).

AND(TRUE, TRUE)

TRUE

AND(TRUE, FALSE)

FALSE

AND(TRUE, UNKNOWN)

UNKNOWN

AND(FALSE, TRUE)

FALSE

AND(FALSE, FALSE)

FALSE

AND(FALSE, UNKNOWN)

FALSE

AND(UNKNOWN, TRUE)

UNKNOWN

AND(UNKNOWN, FALSE)

FALSE

AND(UNKNOWN, UNKNOWN)

UNKNOWN

Three-valued logic — OR table (overall rule)

OR: either being TRUE means TRUE; both FALSE means FALSE; otherwise UNKNOWN (could go either way).

OR(TRUE, TRUE)

TRUE

OR(TRUE, FALSE)

TRUE

OR(TRUE, UNKNOWN)

TRUE

OR(FALSE, TRUE)

TRUE

OR(FALSE, FALSE)

FALSE

OR(FALSE, UNKNOWN)

UNKNOWN

OR(UNKNOWN, TRUE)

TRUE

OR(UNKNOWN, FALSE)

UNKNOWN

OR(UNKNOWN, UNKNOWN)

UNKNOWN

Three-valued logic — NOT table (overall rule)

NOT: UNKNOWN stays UNKNOWN because we don’t know what the opposite is, either.

NOT(TRUE)

FALSE

NOT(FALSE)

TRUE

NOT(UNKNOWN)

UNKNOWN

Checking for NULL in SQL (example query)

SELECT * FROM PERSON WHERE Phone_number IS NOT NULL;

How to test NULL in SQL (important rule)

Use IS and IS NOT for NULL instead of = and <>.

Section / heading

SELECT Math

IN operator purpose

Select a defined set of values.

IN operator works on what types?

Can be numeric; can be strings.

IN operator syntax

… WHERE IN (, , …);

IN operator extra context source (slides)

Extra context from https://www.sqltutorial.org/sql-in/

IN example question

“Find all students who received a C or higher (in any course)”

IN example query

SELECT Studentid, Lettergrade FROM TAKES WHERE Letter_grade IN ('A+','A','A-','B+','B','B-','C+','C');

TAKES table columns shown

Studentid, Courseid, Letter_grade

Aggregation meaning

SQL can do statistics (aggregation functions).

Aggregation functions listed

SUM, MIN/MAX, COUNT, AVG

Where aggregation is done

Aggregation is done in the SELECT clause, not the WHERE.

Why aggregation is in SELECT (slide explanation)

Because the statistics are what we want back!

Aggregation extra context source (slides)

Extra context from https://www.sqltutorial.org/sql-in/

DISTINCT meaning (re-stated)

DISTINCT means “all unique” values.

Opposite of DISTINCT meaning

“All values, including duplicates”.

DISTINCT extra context source (slides)

Extra context from https://www.sqltutorial.org/sql-in/

SUM example question

“Find how much we pay employees per year”

SUM example query

SELECT SUM(Salary) FROM EMPLOYEE;

EMPLOYEE table columns shown in SUM slide

Id, Name, Salary

SUM extra context source (slides)

Extra context from https://www.sqltutorial.org/sql-in/

COUNT example question (departments)

“Find the total number of departments”

COUNT departments example query

SELECT COUNT(DISTINCT Department) FROM EMPLOYEE;

EMPLOYEE table columns shown in COUNT(DISTINCT) slide

Id, Name, Salary, Department

COUNT(DISTINCT …) meaning

Counts distinct (unique) values of the attribute (e.g., unique departments).

COUNT example question (employees)

“Find the total number of employees”

COUNT employees example query

SELECT COUNT(*) FROM EMPLOYEE;

COUNT(*) star note from slides

• here means “all rows”, not “all attributes”.

COUNT extra context source (slides)

Extra context from https://www.sqltutorial.org/sql-in/

MAX and MIN meaning

“What you’d expect” (max/min values).

MAX example question

“Give me the maximum salary”

MAX example query

SELECT MAX(Salary) FROM EMPLOYEE;

EMPLOYEE table columns shown around MAX slide

Id, Name, Salary, Department

MIN function meaning

Returns the minimum value (paired with MAX as “what you’d expect”).

MAX/MIN extra context source (slides)

Extra context from https://www.sqltutorial.org/sql-in/

AVG meaning

Also what you’d expect (average).

AVG example question

“Give me the average salary”

AVG example query

SELECT AVG(Salary) FROM EMPLOYEE;

EMPLOYEE table columns shown around AVG slide

Id, Name, Salary, Department

AVG extra context source (slides)

Extra context from https://www.sqltutorial.org/sql-in/

Section / heading

DROP

DROP meaning

DROP is the opposite of CREATE (it removes things).

What can you DROP (per slides)?

You can DROP: a table you CREATEd, a domain, or an entire schema (and all the tables inside).

DROP TABLE options

RESTRICT or CASCADE

RESTRICT meaning (DROP TABLE)

Don’t drop the table if it is referenced elsewhere.

RESTRICT example reference

Example: a foreign key refers to this table.

CASCADE meaning (DROP TABLE)

Drop relationships as well.

PostgreSQL CASCADE note

In PostgreSQL (for example), the foreign key constraint will be removed, but the table that had the foreign key will remain.

PostgreSQL dependency reference (slides)

https://www.postgresql.org/docs/current/ddl-depend.html

DROP TABLE effect on data

If your table had data, DROP TABLE will delete that data.

Does DROP TABLE require the table to be empty?

No — the table doesn’t have to be empty.

DROP TABLE missing-table behavior

DROP TABLE will error if the table doesn’t exist.

Avoid error when table might not exist

Use: DROP TABLE IF EXISTS

DROP TABLE IF EXISTS note (slides)

“DROP TABLE IF EXISTS

” avoids the error if the table doesn’t exist.