Introduction to Structured Query Language (SQL)

SQL on one table: command and data types, SELECT with WHERE, BETWEEN, IN, LIKE, NULL, DISTINCT and ORDER BY.

Key ideas

• DDL: CREATE, ALTER, DROP (structure); DML: INSERT, UPDATE, DELETE (data); DQL: SELECT (queries).
• Types: INT, FLOAT (decimals), CHAR(n) fixed-length text, VARCHAR(n) varying text, DATE ('2009-04-12').
• WHERE keeps rows whose condition is TRUE; BETWEEN includes both ends; IN matches any value in its list.
• In LIKE, % is any run of characters (even none) and _ exactly one; text comparisons and LIKE ignore case.
• NULL means unknown: a comparison with it, even = NULL, is never TRUE, so test with IS NULL or IS NOT NULL.
• DISTINCT drops repeats; ORDER BY sorts ascending (NULL first), or with DESC descending (NULL last).
• UPDATE and DELETE affect only rows matching WHERE (all rows without it); DROP TABLE removes the table.

Remember

Watch out

• Writing = NULL instead of IS NULL: it never matches anything.
• Running UPDATE or DELETE without WHERE, which changes every row.
• Forgetting where NULL goes in ORDER BY: first when ascending, last with DESC.

Worked example