EXIN SQL Foundation key terms

A collection of data organised as tables of rows and columns, where relationships between tables are expressed through shared key values rather than physical pointers. Queries combine these tables at run time.

The basic storage structure in a relational database: a named set of rows, each row having the same set of named columns with a defined data type per column. Also called a relation or entity.

A single record in a table, holding one value for each column. Also called a tuple or record. Rows are unordered in the relational model; only an ORDER BY clause gives a result a defined sequence.

A named attribute of a table that holds one value per row, with a declared data type and optional constraints. Also called a field or attribute. All values in a column share the same type.

A column or set of columns whose values uniquely identify each row in a table. It must be unique and non-null, and a table can have only one primary key, though that key may span several columns.

Any minimal set of columns that could serve as the primary key because it uniquely identifies every row. One candidate key is chosen as the primary key; the others are usually enforced as unique constraints.

A column or set of columns in one table whose values must match a candidate key value in another table, or be null. It enforces referential integrity between parent and child tables.

The rule that every non-null foreign key value must correspond to an existing key value in the referenced table. The database rejects inserts, updates and deletes that would break this rule.

A rule that no two rows may hold the same value in the specified column or column combination. Unlike a primary key, a unique constraint usually permits null values and a table may have several of them.

A column rule that forbids the absence of a value, so every row must supply a value for that column. It is a column-level check and is implied automatically by a primary key.

A rule attached to a column or table that a Boolean condition must hold for every row, for example a quantity greater than zero. The database rejects any insert or update that makes the condition false.

A value the database supplies automatically when an INSERT statement omits the column. It applies only when no value is given; explicitly inserting NULL stores NULL rather than the default.

A marker meaning that a value is unknown or not applicable, distinct from zero or an empty string. Comparisons with NULL yield unknown, so tests must use IS NULL or IS NOT NULL.

The rule that SQL conditions evaluate to true, false or unknown, with unknown arising whenever NULL takes part in a comparison or arithmetic expression. A WHERE clause keeps only rows whose condition is true.

A function returning the first of its arguments that is not null, or null if all are null. Commonly used to substitute a default for missing values in query output.

The declared category of values a column may hold, such as integer, decimal, character string, date or Boolean. The type determines allowed operations, storage size and which implicit conversions the database permits.

CHAR stores fixed-length strings padded with spaces to the declared length, while VARCHAR stores variable-length strings up to a maximum. CHAR suits uniform codes; VARCHAR suits names and addresses of varying length.

An operator or function that converts a value from one data type to another, for example turning a string into a number or a date. It may be explicit in the statement or applied implicitly by the database.

Automatic type conversion the database performs when operands of different types meet in an expression or comparison. It can silently change results or prevent an index from being used, so explicit conversion is safer.

The DDL statement that defines a new table by naming it and listing its columns with data types and constraints. Variants can also copy structure from an existing table or create the table only if absent.

The DDL statement that changes an existing table's definition, for example adding or dropping a column, changing a data type, or adding and removing constraints. Existing data may be validated or converted.

The DDL statement that removes a table and its definition permanently. Unlike DELETE, it removes the structure as well as all rows, and in most products it cannot be rolled back after commit.

A statement that removes all rows from a table quickly while keeping the table definition. It is usually treated as DDL, often cannot be filtered by WHERE, and resets or ignores certain identity counters.

A named, stored query that behaves like a virtual table. It holds no data of its own; each reference runs the underlying SELECT, which can simplify complex queries or restrict which columns users see.

A view whose result is physically stored and must be refreshed to reflect changes in base tables. It speeds up expensive aggregations at the cost of stale data between refreshes.

An auxiliary structure, usually a balanced tree, that lets the database find rows without scanning the whole table. It speeds up lookups and joins but adds storage and slows inserts, updates and deletes.

The statement that retrieves data, built from clauses evaluated in a logical order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. Only the SELECT and FROM parts are mandatory.

The SELECT clause that filters individual rows before grouping and before any aggregate is computed. It accepts conditions on columns and expressions but cannot reference aggregate functions.

The clause that sorts a result set by one or more expressions, ascending by default, with ASC or DESC per column and NULL placement controlled by NULLS FIRST or NULLS LAST. It is the only guaranteed ordering.

The clause that collapses rows sharing the same values in the listed expressions into one group each, so aggregate functions report per group. Selected non-aggregated columns must normally appear in the list.

The clause that filters groups after GROUP BY and after aggregates are computed, so it may test aggregate results such as COUNT(*) greater than five. Row-level filtering belongs in WHERE instead.

A function that condenses many rows into one value per group, such as COUNT, SUM, AVG, MIN and MAX. Most ignore nulls, and COUNT(*) counts rows while COUNT(column) counts non-null values.

A function that computes a value across a set of related rows while keeping every row in the output, defined by an OVER clause with optional PARTITION BY and ORDER BY. Examples include ROW_NUMBER and running SUM.

Window functions that number rows within an ordered partition. RANK leaves gaps after ties, giving 1, 1, 3, while DENSE_RANK gives 1, 1, 2. ROW_NUMBER always assigns distinct consecutive numbers.

An operation combining rows from two or more tables on a matching condition. Inner joins keep only matches; outer joins keep unmatched rows from one or both sides with nulls filling the missing columns.

A join returning only row pairs for which the join condition is true, so rows without a match on either side are dropped. It is the default join type when JOIN is written without a qualifier.

A join that keeps every row of the left table, filling columns from the right table with nulls where no match exists. Conditions on the right table in WHERE can silently turn it into an inner join.

The result of combining every row of one table with every row of another, produced by a join without a condition or by a cross join. Row counts multiply, so accidental products are a common performance fault.

A SELECT nested inside another statement, usable in the FROM clause, in WHERE with operators such as IN or EXISTS, or as a scalar value. Correlated subqueries reference columns of the outer query and run per outer row.

A predicate that is true when a correlated subquery returns at least one row. It stops searching at the first match and is often used instead of IN to avoid null-related surprises and improve performance.

A named temporary result defined with WITH before the main statement and referenced like a table within that statement. It improves readability and can be recursive for hierarchical data.

An operator combining the results of two queries: UNION removes duplicates, UNION ALL keeps them, INTERSECT returns common rows and EXCEPT or MINUS returns rows of the first query not in the second. Column counts and types must align.

The DML statement that adds rows to a table, either one row with VALUES or many rows from a SELECT. Omitting the column list requires a value for every column in declared order.

The DML statement that changes existing values in a table, setting columns to expressions for all rows or only those matching its WHERE clause. Without WHERE it rewrites every row.

The DML statement that removes rows matching its WHERE clause, or all rows if none is given, while keeping the table structure. It can be rolled back and fires delete triggers.

A unit of work whose statements either all take effect or none do. It begins implicitly or with BEGIN, ends with COMMIT to make changes permanent or ROLLBACK to undo them, and must satisfy ACID properties.

The statements that end a transaction: COMMIT saves all changes permanently, ROLLBACK discards them and restores the previous state. Savepoints allow partial rollback within a longer transaction.

A setting controlling how much one transaction sees of others' uncommitted or newly committed changes. Levels such as READ COMMITTED and SERIALIZABLE trade consistency against concurrency, allowing or preventing dirty reads and phantom rows.

A mechanism by which the database prevents conflicting simultaneous access to a row, page or table. Shared locks permit concurrent reads; exclusive locks block other writers and can cause blocking or deadlock.

The database's chosen strategy for running a statement, showing access methods such as full scan or index seek, join algorithms and estimated costs. Reading plans is the main way to diagnose slow queries.

A pattern-matching predicate where percent matches any sequence of characters and underscore matches exactly one. Patterns starting with a wildcard usually prevent index use, and matching is case-sensitive in some products.

A string function removing leading, trailing or both kinds of a specified character, spaces by default. It is often applied to CHAR columns whose values were padded to fixed length.

A built-in function that extracts parts of a date, adds intervals or formats values, such as CURRENT_DATE, EXTRACT or DATEADD. Names and behaviour vary between products, so portability requires care.

A conditional expression returning one of several values depending on which WHEN condition is true first, with an optional ELSE fallback. It works inside SELECT, WHERE, ORDER BY and UPDATE assignments.

Clauses restricting a result to a number of rows and skipping a number before returning them, used for paging. Products differ, using TOP, FETCH FIRST or ROWNUM instead, and a stable ORDER BY is essential.

A rule declared on a table or column that the database enforces on every change, covering primary keys, foreign keys, uniqueness, non-null, checks and defaults. Violations cause the statement to fail.

A relationship where the value of one set of columns always determines the value of another, written as A determines B. It underpins normalisation and explains why some columns must appear in GROUP BY.

The process of structuring tables to reduce redundancy and update anomalies by applying normal forms up to third normal form or beyond. Each step removes a class of dependency between columns.

A predicate true when a value equals any member of a list or the rows returned by a subquery. NOT IN behaves unexpectedly when the list contains NULL, because comparisons yield unknown rather than true.

A temporary name given to a column or table in a query, written with AS or by juxtaposition. Column aliases label output; table aliases shorten qualified names and are needed when a table is joined to itself.