SQL glossary
Each entry names one term, defines it, and links to the section that teaches it.
ACID#
The guarantees a transaction makes: atomicity (its statements are kept together or not at all), consistency (every constraint still holds when it ends), isolation (transactions running at the same time do not see each other's uncommitted changes) and durability (a committed change survives a crash).
Taught in: §25. DML: INSERT, UPDATE, DELETE
Aggregate function#
A function that reads many rows and returns one value. count, sum, avg, min, max and median are the six that cover most business questions, and on their own they collapse the whole table to a single row.
Taught in: §13. Aggregate functions
Alias#
A name you give a column, an expression or a table inside a query, written with AS. An alias that needs a space or a capital letter goes in double quotes.
Taught in: §6. SELECT and FROM · §17. INNER JOIN
Anti-join#
A LEFT JOIN followed by a test that the right side came back empty, which returns every left row with no match anywhere. Test the key you joined on, because a nullable column from the right table also catches rows that matched and had no value there.
Taught in: §18. LEFT JOIN, and what it preserves
Attribute#
One fact about an entity, drawn as a line inside the box on an ERD, and it becomes a column once the table exists.
Taught in: §5. Reading an ERD
Autocommit#
Keeping each statement the moment it succeeds, which is what a database does when no BEGIN TRANSACTION is open. Statements that make up one change can then end up half done, the first kept and the second refused.
Taught in: §25. DML: INSERT, UPDATE, DELETE
CASE WHEN#
SQL's if. It tests conditions in order, stops at the first one that is true, and returns the value attached to it.
Taught in: §11. CASE WHEN
Candidate key#
A column that passes the uniqueness test, so two rows never share its value. Choosing among the candidates is also a question of whether the value is stable and whether every row has one.
Cardinality#
The maximum number of rows of a table that take part in a relationship, drawn as the symbol nearer the box on an ERD line.
Taught in: §3. Relationship cardinality: 1:1, 1:M, M:N · §5. Reading an ERD
Cartesian product#
Every row of one table paired with every row of another, which is what a join with no condition produces. A cross join is the deliberate version of it.
Taught in: §17. INNER JOIN
Cascade#
One of the three answers a design can give to what a delete does to the rows pointing at the deleted one, where the delete goes ahead and those rows go with it. DuckDB refuses to declare it, and ON DELETE CASCADE is a parser error there.
Taught in: §4. Foreign keys and referential integrity
Cast#
Converting a value from one type to another, which DuckDB often does for you when you compare a text column to a number. CAST("OrderDate" AS DATE) and "OrderDate"::DATE are the two ways to ask for it.
Taught in: §7. WHERE · §12. Dates: truncating, differencing, grouping by month
Catalog#
The top level of a database's naming. A catalog holds a schema and a schema holds the tables, so customers written out in full is memory.main.customers.
Taught in: §27. Running SQL from a notebook
Clause#
A keyword and what follows it, such as WHERE "Country" = 'France'. Put each one on its own line, because a one-line query stops being readable at about the third clause.
Taught in: §6. SELECT and FROM
Collation#
The property of a column that decides how its text is compared, including whether the comparison cares about case. Engines ship different defaults for it.
Taught in: §7. WHERE
Column#
One named part of a table, running down it, with one declared type that every row obeys. Also called a field or an attribute.
Taught in: §1. Tables, rows, columns
Common table expression (CTE)#
A query named in front of a statement with WITH name AS (query), which everything after it can read as though it were a table. The step has a name, so somebody can read it and check it on its own.
Taught in: §22. Common table expressions (WITH)
Compound key (composite key)#
A primary key made of two or more columns together, where no single one of them identifies a row. Written PRIMARY KEY (EmployeeID, TerritoryID).
Taught in: §2. Primary keys, compound keys, surrogate keys · §24. DDL: CREATE TABLE, types, constraints
Constraint#
A rule declared on a column or a table that the database enforces the moment a row arrives. NOT NULL, DEFAULT, CHECK, a primary key and a foreign key are all constraints.
Taught in: §4. Foreign keys and referential integrity · §24. DDL: CREATE TABLE, types, constraints
Correlated subquery#
A subquery that mentions a column of the query around it, so its answer can differ for every outer row. Read literally, it runs once per outer row; DuckDB rewrites it into a join before running it. The products priced above the average for their own category can be found with one, or with a CTE and a join.
Taught in: §22. Common table expressions (WITH)
DISTINCT#
A keyword placed immediately after SELECT that removes duplicate rows from the result. It applies to the whole row, so two rows have to match in every selected column before one of them is dropped.
Taught in: §10. DISTINCT, and what it hides
Data definition language (DDL)#
The part of SQL that creates and changes the shape of a database, which is CREATE TABLE with its types and its constraints.
Taught in: §24. DDL: CREATE TABLE, types, constraints
Data manipulation language (DML)#
The part of SQL that changes rows. INSERT adds them, UPDATE changes ones that are already there, and DELETE removes them.
Taught in: §25. DML: INSERT, UPDATE, DELETE
Data type#
What a column is declared to hold, such as INTEGER, VARCHAR, DECIMAL(10,2), DATE or TIMESTAMP. Every row of that column obeys it, which is what lets a column of prices be added up without being inspected first.
Taught in: §1. Tables, rows, columns · §7. WHERE · §24. DDL: CREATE TABLE, types, constraints
Entity#
A thing you keep a table of, drawn as a box on an ERD. The word is there because an ERD is drawn before any table exists.
Taught in: §5. Reading an ERD
Entity relationship diagram (ERD)#
A diagram holding the design of a database on one page: the entities, their attributes, and the lines between them. Each line reads as two sentences, one in each direction.
Taught in: §5. Reading an ERD
FILTER#
A clause attached to one aggregate, written count(*) FILTER (WHERE ...), which applies a condition to that aggregate and leaves the others in the same query alone.
Taught in: §11. CASE WHEN
Fan-out#
What happens when one row on the left of a join matches several rows on the right, so that left row comes out once per match with every column it carries copied onto each of them. A total taken from a left-table column after a fan-out is too large.
Taught in: §19. When a JOIN duplicates rows
Foreign key#
A column holding another table's primary key, which is the mechanism behind every relationship. It sits on the side that has one of the other thing, so orders.CustomerID sits on orders.
Taught in: §4. Foreign keys and referential integrity
GROUP BY#
A clause that splits the rows into groups and runs the aggregates once per group, returning one row for each distinct combination of the grouping columns. Every row lands in exactly one group, so the group counts add back up to the row count you started with.
Taught in: §14. GROUP BY
Granularity (grain)#
What one row of a table or a result means, and grain is the word this course uses for it. The grain of a joined result is the grain of the finest table in it.
Taught in: §19. When a JOIN duplicates rows
HAVING#
A clause that filters groups after they have been formed, which is why it can see aggregates. WHERE count(*) > 8 is rejected because no group exists at the point WHERE runs.
Taught in: §15. HAVING, against WHERE · §16. The order the clauses actually run in
Half-open range#
A date range written >= the start and < the day after the end, which covers a whole month including the orders placed after midnight on its last day.
Taught in: §12. Dates: truncating, differencing, grouping by month
In-process database (embedded database)#
A database whose engine is a library inside your own process, so there is no host name, no port, no user name and no password. import duckdb is the whole installation.
Taught in: §27. Running SQL from a notebook
Intersection table#
The third table that carries a many-to-many relationship, holding one row for each pairing, also called a junction table. It almost always grows columns of its own, such as the grade on an enrollment.
Taught in: §3. Relationship cardinality: 1:1, 1:M, M:N
Join#
Two tables, one condition, and the rows where that condition is true. INNER JOIN, or JOIN on its own, is the plain version, where a row comes out only when it found a partner on the other side.
Taught in: §17. INNER JOIN
LIMIT#
A clause that cuts the result short once the sorting is done, with OFFSET skipping rows before it takes them. A LIMIT with no ORDER BY above it hands back whichever rows the engine found convenient.
Taught in: §8. ORDER BY and LIMIT
Left outer join#
The full name of a LEFT JOIN, which keeps every row of the left table whether or not it found a partner and fills the right side with NULL where there was none. OUTER is optional the way INNER is.
Taught in: §18. LEFT JOIN, and what it preserves
NULL#
What a database holds in a cell when it has no value for it, meaning the value is unknown. Anything you ask about an unknown value answers unknown, so IS NULL and IS NOT NULL are the operators that find one.
Taught in: §9. NULL, and why it is not zero
Natural key#
A key that already exists in the business, such as the student id a school invented for the job. Where the business offers no such column, a compound key or a surrogate key is the way out.
ORDER BY#
The clause that asks for an order, since nothing about a table says which row comes first. DESC reverses it, and a second sort key breaks the ties.
Taught in: §8. ORDER BY and LIMIT
Optionality#
The minimum number of rows of a table that take part in a relationship, drawn as the symbol further from the box on an ERD line. A circle there says zero is allowed and a bar says at least one.
Taught in: §5. Reading an ERD
Orphan#
A row whose foreign key names a parent that is not there. A declared foreign key blocks both ways of creating one, which are inserting a child with no parent and deleting a parent that still has children.
Taught in: §4. Foreign keys and referential integrity
Placeholder#
A ? written where a value goes, with the value handed to con.execute as a list so that it travels beside the query. The parser never sees the value, so an apostrophe inside a company name is one more character, and SQL typed into a text box is never run.
Taught in: §27. Running SQL from a notebook
Predicate#
The condition a WHERE clause takes. It is tested once per row, and WHERE keeps the rows where the answer is true.
Taught in: §7. WHERE
Primary key#
The column, or the group of columns, that identifies exactly one row and never repeats. Somebody decides which column it is, and the data never hands you the answer.
Referential integrity#
The promise that a foreign key always points at a row that exists. It runs one way, so every order must have a customer while nothing requires a customer to have an order.
Taught in: §4. Foreign keys and referential integrity
Relation object#
What con.sql hands back in Python, which is DuckDB's description of a query that has not necessarily run yet. It carries the column names and types, and it turns into rows when something asks for them.
Taught in: §27. Running SQL from a notebook
Relationship#
How one table refers to another, and a business rule written down. Every one of them is one to one, one to many, or many to many.
Taught in: §3. Relationship cardinality: 1:1, 1:M, M:N
Row#
One line across a table, holding one of whatever the table is a table of. Also called a record or a tuple.
Taught in: §1. Tables, rows, columns
Scalar subquery#
A subquery in a position that allows one row and one column, such as the right side of a comparison in WHERE. Two rows is a runtime error.
Taught in: §21. Subqueries
Schema#
The level of a database's naming between the catalog and the tables, so main is the schema in memory.main.customers. The word is also used loosely for the whole design of a set of tables.
Taught in: §27. Running SQL from a notebook
Self join#
A join that puts one table on both sides, which is how you print a manager's name beside an employee's. Two aliases are the only thing keeping the two copies apart.
Taught in: §20. Self joins and multi-table joins
Self-referencing relationship#
A relationship with one table on both ends, such as employees.ReportsTo holding the EmployeeID of that person's manager.
Taught in: §3. Relationship cardinality: 1:1, 1:M, M:N · §20. Self joins and multi-table joins
Semi-join#
What an IN subquery does, keeping the left rows that have a match in the other table. It brings none of that table's columns back, and it never duplicates a row the way a join to a repeating key does.
Taught in: §21. Subqueries
Sequence#
A database object that hands out the next number each time it is asked. A column gets one by naming nextval('seq_name') in its DEFAULT, which is how a DuckDB table gets an id without AUTOINCREMENT.
Taught in: §24. DDL: CREATE TABLE, types, constraints
Set operator#
A keyword that stacks one result on top of another, such as UNION ALL.
Taught in: §7. WHERE
SQL injection#
SQL that somebody types into an input, which runs because an f-string wrote the input into the query. x' OR '1'='1 typed as a customer ID returns every customer's rows. A placeholder stops it.
Taught in: §27. Running SQL from a notebook
Subquery#
A SELECT written inside another statement, which runs so that the query around it can use the result. Where it sits decides what it may return: one value in most of a WHERE clause, a column of values in front of IN, and a whole table in FROM.
Taught in: §21. Subqueries
Surrogate key#
A key column the database made up, such as a counter. It carries no meaning, so nothing that happens in the business can change it, and a rule such as one enrollment per student per course has to be declared separately with UNIQUE.
Table#
Named columns and some number of rows, which is the whole shape of it. Also called a relation.
Taught in: §1. Tables, rows, columns
Three-valued logic#
The three answers SQL gives a condition, which are true, false and unknown. A comparison against NULL gives unknown, and WHERE keeps the rows that answered true.
Taught in: §9. NULL, and why it is not zero
Timestamp#
A data type holding a date and a time of day, down to the second. Comparing one against a plain date compares it against midnight, so = DATE '2016-07-04' finds only the rows that carry no clock time.
Taught in: §12. Dates: truncating, differencing, grouping by month
Transaction#
A BEGIN TRANSACTION, the statements you run, and then a COMMIT to keep them or a ROLLBACK to put everything back the way it was. Nothing outside the transaction sees any of it until the COMMIT. Several statements inside one transaction are kept together or not at all.
Taught in: §25. DML: INSERT, UPDATE, DELETE
View#
A query with a name. Selecting from it runs the query underneath and hands back the rows, so a view stores nothing and is never out of date.
Taught in: §28. Views
Window frame#
The part of an OVER clause that says which rows the window sees, such as ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for a running total.
Taught in: §23. Window functions
Window function#
A function that keeps every row and adds a column computed across a set of rows, so one result carries the row-level detail and a group-level number at once. PARTITION BY inside OVER restarts that set at every new group.
Taught in: §23. Window functions
Last updated: Tuesday, 10/20/2026