ANY / ALL Subquery comparison operators. ANY (or SOME) returns true if the comparison holds for at least one value in the subquery result. ALL returns true only if the comparison holds for every value. Semantically related to EXISTS and IN but more flexible with comparison operators. 4/5 BETWEEN Test whether a value falls within an inclusive range. BETWEEN a AND b is equivalent to val >= a AND val <= b — both endpoints are included. 5/5 CORRELATED SUBQUERY A subquery that references columns from the outer query. The subquery is re-evaluated once per row of the outer query, creating a row-by-row dependency. Powerful but potentially slow on large datasets — often replaceable with a JOIN or window function. 5/5 CUBE An extension of GROUP BY ROLLUP that generates subtotals for every possible combination of the specified columns, not just hierarchical rollups. Produces 2^n grouping combinations for n columns. 3/5 DISTINCT Eliminate duplicate rows in a result set. 5/5 EXISTS Test whether a subquery returns any rows. Returns TRUE if the subquery produces at least one row, FALSE otherwise. Short-circuits on the first match. 5/5 FETCH FIRST / OFFSET-FETCH Standard SQL syntax for skipping a number of rows and limiting the result set size, used for pagination. 5/5 GROUP BY Aggregate rows sharing common values. 5/5 GROUPING SETS An explicit list of grouping combinations to compute in a single pass, giving precise control over which subtotals are produced. More flexible than ROLLUP or CUBE. 3/5 HAVING Filter the results of a GROUP BY aggregation. WHERE filters individual rows before grouping; HAVING filters groups after aggregation. 5/5 IN / NOT IN Test whether a value matches any value in a list or subquery. IN is shorthand for multiple OR equality conditions; NOT IN is the negation. 5/5 IS NULL / IS NOT NULL Test whether a value is NULL (missing/unknown). NULL cannot be compared with = or != — only IS NULL and IS NOT NULL work correctly. 5/5 LATERAL Allows a subquery in the FROM clause to reference columns from tables to its left, enabling advanced row-by-row operations. 4/5 LIKE Pattern matching within strings using % (any sequence of characters) and _ (exactly one character) wildcards. 5/5 LIMIT / TOP / FETCH FIRST Restrict the number of rows returned by a query. Every engine supports this but with different syntax. 5/5 NAMED WINDOW CLAUSE Define a reusable window specification with a name in a WINDOW clause, then reference it by name across multiple window functions in the same query. Eliminates duplicated OVER(...) definitions. 3/5 ORDER BY Sort the result set by one or more columns. 5/5 PIVOT Rotates rows into columns, creating cross-tab reports. 2/5 QUALIFY A post-aggregation filter clause for window functions, analogous to HAVING for aggregate functions, avoiding the need for a subquery. 0/5 QUERY HINTS / OPTIMIZER HINTS Directives embedded in a SQL statement that instruct the query optimizer to use a specific join order, index, or execution strategy. 4/5 RECURSIVE CTE A CTE that references itself for hierarchies or graph traversals. 5/5 REGEX Match strings against regular expression patterns, enabling flexible search beyond simple LIKE wildcards. 3/5 ROLLUP An extension of GROUP BY that generates subtotal and grand-total rows automatically. Produces a result set with multiple levels of aggregation in a single query. 4/5 SET OPERATIONS Combine rows from multiple queries: UNION, INTERSECT, EXCEPT/MINUS. 5/5 SUBQUERY A SELECT statement nested inside another query, used in WHERE, FROM, SELECT, or HAVING clauses. 5/5 TABLESAMPLE Return a random sample of rows from a table efficiently, without scanning and sorting the full table. 3/5 UNPIVOT Transform columns into rows — the inverse of PIVOT. Takes values spread across multiple columns and normalizes them into a key-value row structure. Useful for reshaping wide, denormalized data. 2/5 VALUES CLAUSE Construct an inline table from literal values without referencing a persistent table. Useful for multi-row inserts, testing, and constructing ad-hoc result sets to join against. 4/5 WINDOW FRAME (ROWS/RANGE) Control which rows are included in a window function's calculation relative to the current row. The frame clause (ROWS or RANGE BETWEEN ... AND ...) determines the sliding window boundaries. 5/5 WINDOW FUNCTIONS Perform calculations across a set of rows related to the current row. 5/5 WITH (COMMON TABLE EXPRESSIONS) Common Table Expressions (CTEs) provide a way to write auxiliary statements for use in a larger query. 5/5 WITH TIES An extension to FETCH FIRST / LIMIT that includes additional rows that tie on the last ORDER BY value rather than cutting them off arbitrarily. 3/5

COPY / BULK LOAD High-speed mechanisms for loading large volumes of data from flat files or streams into a table, bypassing row-by-row INSERT overhead. The fastest way to populate or refresh large tables. 4/5 DELETE Remove rows from a table. 5/5 INSERT Add new rows to a table. 5/5 INSERT INTO SELECT Copy rows from one query result into a table, optionally transforming them in transit. The fundamental pattern for populating summary tables, migrating data, and archiving rows. 5/5 MERGE Performs insert, update, or delete operations on a target table based on a join to a source table. 3/5 MULTI-TABLE UPDATE / DELETE Modifying or deleting rows in one table based on a join condition with one or more other tables in a single DML statement. 5/5 OUTPUT / RETURNING INTO VARIABLE Capturing values from rows affected by an INSERT, UPDATE, or DELETE into a variable or result set within the same statement. 5/5 RETURNING Return column values from rows affected by an INSERT, UPDATE, or DELETE — without a separate SELECT round-trip. 4/5 SELECT Retrieve rows from one or more tables. 5/5 SELECT INTO A shorthand for creating a new table from the result of a SELECT statement. 4/5 TRUNCATE Remove all rows from a table instantly, without logging individual row deletions. 4/5 UPDATE Modify existing rows in a table. 5/5 UPSERT Insert rows that don't exist and update those that do in one statement. 5/5

JSON EXTRACT Extract a scalar value or sub-document from a JSON column using a path expression. 5/5 JSON MODIFY / JSON SET Update, insert, or delete values within a JSON document stored in a column, returning the modified JSON. Avoids deserializing the entire document in application code just to change one field. 5/5 JSON OPERATORS (-> / ->>) Shorthand operators for navigating JSON document structure. -> extracts a JSON value; ->> extracts a text (unquoted) value. Availability and exact semantics vary significantly by engine. 3/5 JSON_AGG / JSON_ARRAYAGG Aggregate multiple rows into a single JSON array (or object), collapsing a one-to-many relationship into a nested JSON structure within a query. 4/5 JSON_TABLE Extracts JSON data and returns it as a relational table. 4/5 LATERAL FLATTEN Expanding an array or JSON array column into multiple rows using a lateral (correlated) join, common in analytical SQL dialects. 5/5

COVERING INDEX / INCLUDE COLUMNS An index that contains all columns needed to satisfy a query, allowing the engine to return results from the index alone without accessing the base table. 5/5 CREATE INDEX Create an index on one or more columns to speed up queries at the cost of write overhead. 5/5 EXPRESSION INDEX Function-based indexes allow indexing on expressions. 5/5 FULL TEXT SEARCH Specialized indexing and querying for efficient text searches. 5/5 INDEX VISIBILITY / DISABLE Making an index invisible or disabled so the query optimizer ignores it, without dropping it — useful for testing the impact of index removal. 3/5 PARTIAL / FILTERED INDEX An index that covers only a subset of rows, defined by a WHERE condition. Produces smaller, faster indexes by excluding rows that are never queried via that index path. 3/5

ALTER TABLE Modify an existing table’s structure. 5/5 CHECK CONSTRAINT A constraint that enforces a boolean condition on column values at the row level — the database rejects any INSERT or UPDATE that would violate it. 5/5 CREATE SCHEMA Create a named namespace within a database that groups related tables, views, and other objects. Schemas provide organizational structure and a layer of access control. 3/5 CREATE TABLE Define a new table and its columns. 5/5 CREATE VIEW Define a named virtual table based on a SELECT query. 5/5 DEFAULT VALUE Specify a value to be used automatically when no value is provided for a column during INSERT. 5/5 DROP TABLE Remove a table and all its data. 5/5 EXCLUSION CONSTRAINTS A generalization of UNIQUE constraints that uses arbitrary operators — most commonly used to prevent overlapping date/time ranges or spatial objects. 1/5 FOREIGN KEY A column or set of columns that references the primary key of another table, enforcing referential integrity between parent and child tables. 5/5 GENERATED COLUMNS Columns whose values are automatically computed from an expression over other columns in the same row. The database maintains the value rather than the application. Also called computed columns or virtual columns. 5/5 IF NOT EXISTS / IF EXISTS (CONDITIONAL DDL) Modifiers on DDL statements that suppress errors when an object already exists (or does not exist), enabling idempotent schema scripts. 5/5 MATERIALIZED VIEW A view whose result set is physically stored on disk and can be refreshed on demand or automatically. Trades storage and staleness for query speed — avoids recomputing expensive aggregations on every query. 3/5 NOT NULL A column constraint that prevents NULL values from being stored, requiring every row to supply a value for that column. 5/5 PRIMARY KEY A column or set of columns that uniquely identifies each row in a table. Enforces uniqueness and NOT NULL, and typically drives the clustered index. 5/5 RENAME TABLE / COLUMN Changing the name of an existing table or column without recreating the object. 5/5 SEQUENCE / AUTOINCREMENT Mechanisms for auto-generating unique sequential integer values, typically used for surrogate primary keys. Syntax and feature names vary significantly across engines. 5/5 TEMPORARY TABLES Tables that exist only for the duration of a session or transaction and are automatically dropped when that session ends. Used to stage intermediate results without polluting the permanent schema. 5/5 UNIQUE CONSTRAINT Enforce that all values in a column or combination of columns are distinct across all rows. Unlike a PRIMARY KEY, a unique constraint allows NULL values (with engine-specific behavior). 5/5

DEADLOCK HANDLING How each engine detects and resolves deadlocks — circular lock dependencies between transactions — and what the application must do in response. 5/5 ISOLATION LEVELS Transaction isolation levels control how and when changes made by one transaction become visible to others, balancing consistency against concurrency. 5/5 SAVEPOINT A named marker within a transaction to which you can partially roll back without aborting the entire transaction. Enables nested or selective error recovery inside a larger unit of work. 5/5 TRANSACTIONS Control units of work with atomicity. 5/5

CASE WHEN Conditional expression returning different values based on evaluated conditions. 5/5 CAST Convert a value from one data type to another. 5/5 CHARINDEX / INSTR / POSITION Search for a substring within a string and return the position of the first occurrence. Returns 0 or NULL if not found, depending on the engine. 5/5 COALESCE Returns the first non-NULL value from a list of expressions. 5/5 CONCAT Concatenate two or more strings into a single string. Behavior with NULL values differs significantly between engines. 5/5 COUNT / SUM / AVG / MIN / MAX The five core aggregate functions. COUNT counts rows or non-NULL values. SUM and AVG compute totals and averages. MIN and MAX return the smallest and largest value. All are universally supported. 5/5 CURRENT TIMESTAMP Retrieve the current date and/or time from the database server. Each engine has multiple synonyms with subtle differences around timezone awareness and precision. 5/5 DATE ADD / INTERVAL ARITHMETIC Add or subtract a time interval from a date or timestamp value. Syntax varies more than almost any other operation across SQL engines. 5/5 DATE FORMAT Format a date or timestamp value as a string in a specific display format. Every engine has a different function name and format code system. 5/5 DATE PARSING Functions for parsing strings into dates. 4/5 DATE TRUNC Truncate a date or timestamp to a specified unit of precision (year, month, day, hour, etc.), zeroing out all smaller units. 3/5 DATEDIFF Calculate the difference between two date or timestamp values, returning an integer count of a specified unit (days, months, etc.) or an interval. 5/5 EXTRACT / DATEPART Extract a specific component (year, month, day, hour, etc.) from a date or timestamp as a numeric value. 4/5 FILTER (AGGREGATE) Add a per-aggregate WHERE condition to filter the rows included in that specific aggregate, without a separate subquery. 3/5 FIRST_VALUE / LAST_VALUE Window functions that return the first or last value in the current window frame. Commonly used to carry forward the first value in a group or compare each row to the group's boundary. 5/5 FORMAT / TO_CHAR Formatting a date, number, or other value as a string according to a format pattern. 5/5 GENERATE_SERIES Generate a set of integer, numeric, or timestamp values as a virtual table row source. 3/5 GREATEST / LEAST Return the largest or smallest value from a list of two or more expressions. Evaluate multiple columns or values in a single call without nested CASE expressions. 4/5 IIF / DECODE / CONDITIONAL SHORTHAND Compact conditional expressions that return one of two or more values based on a condition, without the full verbosity of CASE WHEN. Includes IIF (SQL Server, Access), DECODE (Oracle), and IF (MySQL). 3/5 LAG / LEAD Access the value from a row before (LAG) or after (LEAD) the current row within an ordered window partition — without a self-join. 5/5 LENGTH Return the number of characters (or bytes) in a string. 5/5 MATH FUNCTIONS (FLOOR / CEIL / ROUND / ABS) Core numeric functions for rounding, truncating, and taking the absolute value of numeric expressions. 5/5 NTILE Divide rows in a window partition into a specified number of ranked buckets (tiles) as evenly as possible, returning the bucket number for each row. 5/5 NULLIF Returns NULL if two expressions are equal, otherwise returns the first expression. Commonly used to suppress zero-division errors and convert sentinel values to NULL. 5/5 PERCENTILE FUNCTIONS Compute percentile values within a distribution. PERCENTILE_CONT returns an interpolated value; PERCENTILE_DISC returns an actual value from the dataset. Used for medians, quartiles, and statistical analysis. 3/5 RANK / DENSE_RANK Window functions that assign a rank to each row within a partition. RANK skips rank numbers after ties; DENSE_RANK does not skip. 5/5 REGEXP REPLACE A function to replace substrings based on regex patterns. 3/5 REPLACE Replace all occurrences of a substring within a string with a new substring. 5/5 ROW_NUMBER Assign a sequential integer to each row within a result set or partition, starting at 1. Rows with equal values receive different numbers (unlike RANK). 5/5 STRING AGG Concatenates strings separated by a specified separator. In PostgreSQL, this is STRING_AGG; in MySQL and SQLite, use GROUP_CONCAT; in Oracle, use LISTAGG; and in SQL Server, use STRING_AGG (since 2017). Syntax and features may differ between databases. 5/5 STRING PADDING (LPAD / RPAD) Padding a string to a specified length by prepending (LPAD) or appending (RPAD) a fill character. 3/5 STRING SPLIT / STRING TO ROWS Split a delimited string into multiple rows or elements. Commonly needed for storing comma-separated lists in a single column and querying them relationally. 3/5 SUBSTRING Extract a portion of a string starting at a given position, optionally for a given length. 5/5 TRIM Remove leading and/or trailing characters (typically whitespace) from a string. 5/5 UPPER / LOWER Convert a string to all uppercase or all lowercase characters. 5/5 UUID / GEN_RANDOM_UUID Generating a universally unique identifier (UUID/GUID) within a SQL query or default expression. 4/5 WITHIN GROUP Ordered-set aggregate functions that require a sort order to compute their result: PERCENTILE_CONT (interpolated median), PERCENTILE_DISC (exact value), and MODE (most frequent value). 3/5

COMMENTS Annotate SQL code with single-line (--) or block (/* */) comments. All major SQL engines support both styles. 5/5 NULLS ORDERING Specifies the ordering of NULL values in an SQL query. 2/5

ANTI-JOIN The NOT EXISTS pattern or LEFT JOIN ... IS NULL form for a performance advantage. 5/5 CROSS JOIN Produces the cartesian product of two tables — every row of the left combined with every row of the right. No join condition. 5/5 FULL OUTER JOIN Returns all rows from both tables; NULLs fill columns where no match exists on either side. 4/5 INNER JOIN Returns only rows where the join condition matches in both tables. 5/5 JOIN Combine rows from two or more tables based on a related column. All core join types are universally supported. Search for a specific type: <a href="#" onclick="searchCommand('inner join'); return false;">INNER JOIN</a>, <a href="#" onclick="searchCommand('left join'); return false;">LEFT JOIN</a>, <a href="#" onclick="searchCommand('right join'); return false;">RIGHT JOIN</a>, <a href="#" onclick="searchCommand('full outer join'); return false;">FULL OUTER JOIN</a>, <a href="#" onclick="searchCommand('cross join'); return false;">CROSS JOIN</a>. 5/5 LEFT JOIN Returns all rows from the left table and matching rows from the right; NULLs fill unmatched right-side columns. 5/5 NATURAL JOIN / USING Join syntax that matches columns by name automatically (NATURAL JOIN) or by an explicit shared column list (JOIN ... USING), avoiding repeated column references. 4/5 RIGHT JOIN Returns all rows from the right table and matching rows from the left; NULLs fill unmatched left-side columns. 5/5

CURSOR A database object that lets procedural code iterate over a result set one row at a time. 4/5 DYNAMIC SQL Constructing and executing a SQL statement as a string at runtime, allowing the query structure to vary based on input. 4/5 STORED PROCEDURE A named, reusable block of SQL and procedural logic stored in the database. 4/5 TRIGGER A procedure that fires automatically in response to INSERT, UPDATE, or DELETE events on a table. 5/5 TRY / CATCH / ERROR HANDLING Structured error handling that traps runtime exceptions within a SQL block and routes execution to a handler. 4/5 USER-DEFINED FUNCTIONS Named, reusable routines that accept parameters, compute a value, and return a scalar or table result. 5/5

COLLATION AND CHARACTER SETS Collation defines the rules for comparing and sorting string data — which characters are equal, how case and accents are handled, and sort order. Character set (charset) defines which characters can be stored. Together they determine string behavior throughout the engine. 5/5 EXPLAIN Display the execution plan the query optimizer chose, optionally running the query to show actual statistics. 5/5 GRANT / REVOKE Basic permission management commands. 4/5 INFORMATION SCHEMA The INFORMATION_SCHEMA or equivalent system catalogs for introspection. 4/5 LISTEN/NOTIFY Asynchronous publish/subscribe messaging built into the database — no external message broker needed. 1/5 TABLE PARTITIONING Dividing a large table into smaller physical segments (partitions) based on a column value, improving query performance and maintenance operations. 4/5 VACUUM / ANALYZE Maintenance operations that reclaim storage from dead rows (VACUUM) and update query planner statistics (ANALYZE), keeping performance stable over time. 5/5

ARRAY DATA TYPE A column type that stores an ordered list of values of a uniform type within a single cell. Eliminates the need for a child table for simple multi-value attributes, at the cost of normalization. 2/5 BOOLEAN TYPE A native data type representing a true/false value, stored and queried without numeric or string proxies. 4/5 ENUM TYPE A column type that restricts values to a predefined set of named string constants, enforced at the database level. 2/5 INTERVAL TYPE A data type representing a span of time (e.g., '3 days', '2 hours 30 minutes') that can be stored, compared, and used in date arithmetic. 3/5 NUMERIC TYPES Precision differences between numeric types in SQL databases. 5/5 RANGE TYPES First-class data types representing a continuous range of values (integers, dates, timestamps) with built-in operators for containment, overlap, and adjacency. 1/5 TEXT TYPES Character string handling in SQL, including VARCHAR and TEXT types. 5/5 UUID TYPE A native 128-bit column type for storing universally unique identifiers, distinct from storing UUIDs as plain strings. 2/5

ROW-LEVEL SECURITY Attach policies to a table so different users or roles automatically see or can modify only the rows they are permitted to access. 3/5