Foundations & querying — flashcards

Databases and relational concepts — what are the key points?

A primary key uniquely identifies a row and is never null · A foreign key points at another table's primary key · One-to-many relationships are why joins can multiply rows Read the lesson

The SELECT statement — what are the key points?

Name your columns rather than using SELECT * in saved queries · AS renames output columns; single quotes are for values, not identifiers · SELECT can contain expressions, not just column names Read the lesson

Filtering with WHERE — what are the key points?

BETWEEN is inclusive — use a half-open range for dates · A leading % in LIKE prevents index use · NOT IN returns nothing if the list contains a NULL Read the lesson

Combining conditions with AND, OR and NOT — what are the key points?

AND is evaluated before OR · Mixing AND and OR without parentheses is a common source of silent bugs · Parenthesise mixed conditions even when precedence would be correct Read the lesson

Sorting and limiting results — what are the key points?

ORDER BY can use SELECT aliases; WHERE cannot · LIMIT / TOP / FETCH FIRST are dialect-specific · A limit without ORDER BY returns an arbitrary set of rows Read the lesson

Working with NULL — what are the key points?

Use IS NULL, never = NULL · NOT IN returns nothing when the list contains a NULL — prefer NOT EXISTS · AVG divides by non-null count, not row count Read the lesson

Removing duplicates with DISTINCT — what are the key points?

DISTINCT applies to the whole selected row, not per column · COUNT(DISTINCT ...) is expensive on large tables · Unexpected duplicates usually mean a join problem, not a DISTINCT problem Read the lesson