Lesson 2 Study Guide

SQL is the language we use to ask relational databases questions. Every query follows one thought: go to the table, filter rows, show columns. About 80% of a data analyst's time is spent in SQL.

Why SQL instead of Excel?

Excel was built for under about 1M records. SQL handles far larger data and appears in many stages of the analytics process.

Excel	SQL
Copy, paste	`SELECT`
Filter	`WHERE`
Sort	`ORDER BY`
Count, sum, pivot	`GROUP BY`
VLOOKUP	`JOIN`
Remove duplicates	`DISTINCT`
Filter top	`TOP / LIMIT`

Terms Where does data live?

Table = a book, rows = pages, columns = what is written about each page. DBMS is the software that manages all of this.

Day in the life How a DA uses SQL

Align and finalise the request with the requester.
Decide which tables hold the data.
Write the conditions that filter the rows.
Validate the result.
Send it on. With an AI assistant, you still validate its output and logic.

Core commands Write in the order the database thinks

1FROMgo to the table

2WHEREkeep the rows you want

3SELECTshow these columns

4ORDER BYsort ASC (default) or DESC

SELECT * returns every column and costs more on big data. Select only what you need.
TOP n (LIMIT in other dialects) previews a few rows. DISTINCT lists unique values, handy to explore a new column.
Several columns are separated by commas.

Data types Pick the right condition

Group	Types	Example
Numeric	INT, DECIMAL, FLOAT	10, 5.5, -4.5
Text	CHAR, VARCHAR, NCHAR, NVARCHAR	'John', N'Việt Nam'
Date/time	DATE, DATETIME	'1990-05-15'
Boolean	BIT / TRUE-FALSE	1 or 0

CHAR pads to a fixed length, VARCHAR uses only what it needs. The N versions store Unicode, so use them for Vietnamese. Dates are written 'yyyy-MM-dd'.

Operators Build conditions

Arithmetic + − * / in SELECT or WHERE.
Comparison = <> > < >= <=, plus BETWEEN (inclusive) and IN (...).
Logic AND OR NOT. Use parentheses when you mix AND with OR.
Pattern LIKE with % (any characters) and _ (one character): LIKE '%9916' = ends with 9916.

NULL The four rules

NULL means no value. It is not 0 and not an empty string ''.
= NULL never works. Use IS NULL and IS NOT NULL.
class <> 'C001' drops NULL rows, because NULL compared with anything is "unknown", not true. Add OR class IS NULL.
SQL has three outcomes: TRUE, FALSE, UNKNOWN. A row is kept only when the condition is TRUE. Try it in the NULL lab.

Watch out Mistakes seen in this lesson's practice

The answer key's mini-challenge 1 is wrong. "Students with no surname" is written surname IS NULL OR surname != '', which returns students who have a surname. It should be = ''.
Mixing AND and OR without brackets changes the meaning (HW 9 and 10). Group each customer segment in its own brackets, then combine with OR.
Hard-coding IDs (HW 6 uses IN ('166','6177','14469')) breaks when the data changes. Use a subquery for the card IDs.
TOP without ORDER BY returns arbitrary rows. HW 7 sorts first, then takes the top 10.

Lab What does WHERE keep when NULL is around?

The Students table is illustrative. Choose a condition and see TRUE, FALSE or UNKNOWN for each row. Only TRUE rows are returned.