Glossary

Every concept in this sheet, plus the smaller terms it uses. Select a concept to open its card.

CASE expression: CASE returns a value based on conditions checked in order, like if and else inside a query.
Command-line client: A terminal program that connects to the database server and runs SQL you type or pipe in: psql for PostgreSQL, mysql for MySQL, sqlcmd for SQL Server.

DDL, DML and DCL: The families of SQL statements: DDL defines structure (CREATE, ALTER, DROP), DML reads and changes rows (SELECT, INSERT, UPDATE, DELETE), DCL grants access (GRANT, REVOKE) and transaction control groups changes (COMMIT, ROLLBACK).
DISTINCT: DISTINCT removes duplicate rows from the result, comparing all selected columns together.

Foreign key: A column that stores the primary key of a row in another table, declared with a constraint so the database rejects values that do not exist there.

Limiting rows: Returning only the first N rows of a sorted result.
Logical order of a query: The order a query is evaluated in, which differs from the order you write it: FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY and finally LIMIT or TOP.

ORDER BY: ORDER BY sorts the result by one or more columns or expressions, ascending by default or DESC for descending.

Primary key: A column, or set of columns, whose value is unique and never NULL for every row, so it identifies exactly one row.

Relational model: Data stored in tables: each table describes one kind of thing, each row is one instance of it and each column is one attribute with a fixed type.

Sample database: A ready-made database with realistic tables and thousands of rows that you load locally to practise on, such as pagila for PostgreSQL, sakila for MySQL or AdventureWorks for SQL Server.
SELECT and FROM: SELECT lists the columns or expressions you want back and FROM names the table they come from.
SSMS and the VS Code MSSQL extension: SSMS is the Windows tool for querying and administering SQL Server.
String and date functions: Built-in functions for text (joining, trimming, changing case, extracting parts) and for dates (current time, adding intervals, truncating to a month).

WHERE: WHERE keeps only the rows for which a condition is true, using comparisons, AND, OR, NOT, IN for a list, BETWEEN for an inclusive range and LIKE for simple patterns.