Convey your intent

Applying SQL to Real-World Problems

Dmitriy (Dima) Gorenshteyn

Lead Data Scientist, Memorial Sloan Kettering Cancer Center

Why is this important?

who cares smiley face

"...if my code does what I designed it to do, who cares how its written..."

Applying SQL to Real-World Problems

...six months from now

frustrated person

Applying SQL to Real-World Problems

Always use AS

Original

SELECT title film_title
FROM film;

Improved

SELECT title AS film_title
FROM film;
Applying SQL to Real-World Problems

What kind of JOIN?

Original

SELECT category, length
FROM film AS f
JOIN category AS c
ON f.film_id = c.film_id;

Improved

SELECT category, length
FROM film AS f
INNER JOIN category AS c
ON f.film_id = c.film_id;
Applying SQL to Real-World Problems

Good use of aliases

Original

SELECT category, length
FROM film AS x1
INNER JOIN category AS x2
ON x1.film_id = x2.film_id;

Improved

SELECT category, length
FROM film AS f
INNER JOIN category AS c
ON f.film_id = c.film_id;
Applying SQL to Real-World Problems

Good use of aliases

Original

SELECT category, length
FROM film AS x1
INNER JOIN category AS x2
ON x1.film_id = x2.film_id;

Improved

SELECT category, length
FROM film AS f
INNER JOIN category AS c
ON f.film_id = c.film_id;

SELECT category, length
FROM film AS fil
INNER JOIN category AS cat
ON fil.film_id = cat.film_id;
Applying SQL to Real-World Problems

Use comments

/* Use the system table, information_schema.columns to 
generate a comma-separated list of columns for each table */
SELECT table_name, STRING_AGG(column_name, ', ') AS columns
FROM information_schema.columns
-- All our data is stored in the public schema.
WHERE table_schema = 'public'
GROUP BY table_name;

/* Multi-line comment */

-- Single-line comment
Applying SQL to Real-World Problems

What was your intent?

Applying SQL to Real-World Problems

Preparing Video For Download...