Querying with SELECT
SQL is the standard language of relational databases. Its SELECT statement retrieves exactly the data you describe, from simple lookups to complex reports.
A brief history of SQL
SQL grew out of SEQUEL, a language designed at IBM in the early 1970s by Donald Chamberlin and Raymond Boyce for the System R project. It became an ANSI standard in 1986 and an ISO standard in 1987, with major revisions since, adding recursive queries, window functions, JSON support, and more. Each database product implements the standard with its own extensions.
The shape of a query
A basic query is SELECT columns FROM tables WHERE conditions. Although written in that order, it is logically evaluated as FROM (gather rows), WHERE (filter rows), GROUP BY (form groups), HAVING (filter groups), SELECT (compute output columns), and ORDER BY (sort). Knowing this order explains why a column alias defined in SELECT cannot be used in WHERE.
Filtering rows
WHERE accepts comparisons (=, <>, <, >, <=, >=) combined with AND, OR, and NOT. BETWEEN tests ranges inclusively; IN tests membership in a list, such as country IN ('FR', 'DE'); LIKE matches patterns, with % for any sequence and _ for one character, so name LIKE 'Mc%' finds names starting with Mc. IS NULL and IS NOT NULL test for missing values.
Computed columns and aliases
SELECT can compute expressions: SELECT name, price * quantity AS total. AS gives an alias, a name for the output column. Functions transform values: UPPER and LOWER for case, LENGTH, COALESCE(x, default) to replace nulls, and CASE WHEN ... THEN ... ELSE ... END for conditional logic, such as labelling orders as 'large' or 'small'.
Sorting and limiting
ORDER BY sorts results by one or more columns, ascending by default or descending with DESC, such as ORDER BY last_name, first_name. Without ORDER BY, row order is not guaranteed and can change between runs. LIMIT n, or FETCH FIRST n ROWS ONLY in standard SQL, returns only the first n rows; OFFSET skips rows, which supports pagination.
Removing duplicates
SELECT DISTINCT city FROM customers returns each city once. Because SQL tables are multisets, queries can return duplicate rows unless DISTINCT is used or keys make duplicates impossible. DISTINCT applies to the whole output row, not just the first column.
Aggregate functions
COUNT, SUM, AVG, MIN, and MAX summarise many rows into one value. COUNT(*) counts rows; COUNT(column) counts non-null values; COUNT(DISTINCT column) counts distinct non-null values. Aggregates ignore NULLs except COUNT(*), so AVG(score) averages only known scores, which may or may not be what you intend.
Worked example
Table orders(id, customer, amount, placed_on). Query: SELECT customer, amount FROM orders WHERE placed_on >= '2026-01-01' AND amount BETWEEN 50 AND 500 ORDER BY amount DESC LIMIT 5. It filters 2026 orders worth 50 to 500 inclusive, sorts them from largest to smallest, and returns the top five with customer names.
Key takeaways
SQL descends from IBM's 1970s SEQUEL and became an ANSI (1986) and ISO (1987) standard. Queries are written SELECT, FROM, WHERE but evaluated FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. WHERE uses comparisons, BETWEEN, IN, LIKE with % and _, and IS NULL. SELECT computes expressions with aliases, COALESCE, and CASE. ORDER BY is needed for guaranteed order; LIMIT and OFFSET paginate. DISTINCT removes duplicate rows. COUNT, SUM, AVG, MIN, and MAX summarise; most ignore NULLs.
Flashcard
Standard relational query language
Check your understanding
Why does SELECT price * qty AS total FROM sales WHERE total > 100 fail in standard SQL?