SQL Cheat Sheet

Updated 2026-04-29

Next Topic: SQL for Data Analysis Cheat Sheet

SQL (Structured Query Language) is the standard language for managing and manipulating relational databases, used across virtually all industries for data retrieval, analysis, and transformation. Whether querying a simple table or orchestrating complex multi-table joins with aggregations and window functions, SQL provides a declarative syntax where you specify what you want, not how to get it — the database engine handles optimization. Mastering SQL means understanding not just the syntax, but the logical execution order (FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY), which fundamentally differs from how queries are written and explains many common pitfalls.

Quick Index190 entries · 27 tables

Mind Map

27 tables, 190 concepts. Select a concept node to jump to its table row.

Preparing mind map...

Table 1: Basic Query Structure

Clause	Example	Description
SELECT	`SELECT name, salary FROM employees`	• Specifies which columns to retrieve • executed after FROM, WHERE, GROUP BY, and HAVING in logical order.
FROM	`FROM employees`	First clause executed logically — establishes the dataset before any filtering or transformation.
WHERE	`WHERE salary > 50000`	• Filters individual rows before grouping • cannot reference aggregate functions or SELECT aliases.
GROUP BY	`GROUP BY department`	Aggregates rows into groups — enables aggregate functions like SUM, COUNT.
HAVING	`HAVING COUNT(*) > 10`	• Filters groups after aggregation • can use aggregate functions unlike WHERE.
ORDER BY	`ORDER BY salary DESC`	• Sorts the final result set • executed last; can reference SELECT aliases; ASC is default.
DISTINCT	`SELECT DISTINCT country FROM customers`	• Removes duplicate rows from the result • applies to all selected columns as a combination.
LIMIT / TOP / FETCH FIRST	`LIMIT 10` `TOP 10` `FETCH FIRST 10 ROWS ONLY`	• Restricts the number of rows returned • syntax varies: LIMIT in MySQL/PostgreSQL, TOP in SQL Server, FETCH FIRST in Oracle/SQL standard.
OFFSET	`LIMIT 10 OFFSET 20`	• Skips a specified number of rows before returning results • requires ORDER BY for deterministic results.
WINDOW clause	`SELECT name, RANK() OVER w` `FROM employees` `WINDOW w AS (PARTITION BY dept ORDER BY salary DESC)`	• Defines a named window reusable across multiple window function calls in the same query • reduces repetition for complex window expressions.

Table 2: DDL Statements

Statement	Example	Description
CREATE TABLE	`CREATE TABLE users (` `id INT PRIMARY KEY,` `name VARCHAR(100) NOT NULL,` `email VARCHAR(255) UNIQUE` `)`	Creates a new table with column definitions, data types, and constraints.
ALTER TABLE ADD COLUMN	`ALTER TABLE employees` `ADD COLUMN phone VARCHAR(20)`	• Adds a new column to an existing table • existing rows get NULL for the new column by default.
ALTER TABLE MODIFY / ALTER COLUMN	`ALTER TABLE products` `ALTER COLUMN price DECIMAL(12,2)`	• Changes column definition (data type, constraints) • syntax varies by database (MODIFY in MySQL, ALTER COLUMN in SQL Server/PostgreSQL).
ALTER TABLE DROP COLUMN	`ALTER TABLE users DROP COLUMN legacy_flag`	Permanently removes a column and all its data from the table.
ALTER TABLE RENAME	`ALTER TABLE customer RENAME TO customers`	• Renames an existing table • syntax varies (RENAME TO in PostgreSQL/Oracle, sp_rename in SQL Server).
DROP TABLE	`DROP TABLE IF EXISTS temp_staging`	• Permanently deletes a table, all its data, and its indexes • `IF EXISTS` prevents error if already absent.
TRUNCATE TABLE	`TRUNCATE TABLE audit_log`	• Removes all rows while preserving table structure • faster than DELETE with no WHERE; cannot be rolled back in some databases; resets identity counters.
CREATE DATABASE	`CREATE DATABASE analytics`	Creates a new database within the database server.

Table 3: DML Statements

Statement	Example	Description
INSERT INTO ... VALUES	`INSERT INTO orders (customer_id, amount, status)` `VALUES (42, 299.99, 'pending')`	• Inserts a single row (or multiple rows) into a table • column list is optional if all columns supplied in order.
INSERT INTO ... SELECT	`INSERT INTO archive` `SELECT * FROM orders WHERE order_date < '2025-01-01'`	• Bulk-inserts rows from a query result • efficient for copying or archiving data.
UPDATE ... SET	`UPDATE employees` `SET salary = salary * 1.1` `WHERE department = 'Engineering'`	• Modifies existing rows • always use WHERE unless intentionally updating all rows.
UPDATE with JOIN	`UPDATE e` `SET e.dept_name = d.name` `FROM employees e` `JOIN departments d ON e.dept_id = d.id`	• Updates rows using data from a joined table • syntax varies (FROM in SQL Server, JOIN in MySQL, correlated subquery in PostgreSQL/Oracle).
DELETE FROM	`DELETE FROM sessions` `WHERE last_active < NOW() - INTERVAL '30 days'`	• Deletes matching rows • without WHERE, deletes all rows (unlike TRUNCATE, can be rolled back and fires triggers).
MERGE (UPSERT)	`MERGE target AS t` `USING source AS s ON t.id = s.id` `WHEN MATCHED THEN UPDATE SET t.val = s.val` `WHEN NOT MATCHED THEN INSERT (id, val) VALUES (s.id, s.val)` `WHEN NOT MATCHED BY SOURCE THEN DELETE;`	• Combines INSERT, UPDATE, and DELETE in one statement based on a match condition • must end with semicolon in SQL Server; ideal for SCD and data warehouse sync.

Table 4: Join Types

Type	Example	Description
INNER JOIN	`SELECT * FROM orders o` `INNER JOIN customers c ON o.customer_id = c.id`	• Returns rows with matching values in both tables • most common join • excludes non-matching rows from both sides.
LEFT JOIN (LEFT OUTER JOIN)	`SELECT * FROM customers c` `LEFT JOIN orders o ON c.id = o.customer_id`	• Returns all rows from left table plus matching rows from right • non-matching right rows produce NULL.
RIGHT JOIN (RIGHT OUTER JOIN)	`SELECT * FROM orders o` `RIGHT JOIN customers c ON o.customer_id = c.id`	• Returns all rows from right table plus matching rows from left • mirror of LEFT JOIN, less commonly used.
FULL OUTER JOIN	`SELECT * FROM customers c` `FULL OUTER JOIN orders o ON c.id = o.customer_id`	• Returns all rows from both tables • non-matching rows from either side produce NULL for the opposite table's columns.
CROSS JOIN	`SELECT * FROM colors CROSS JOIN sizes`	• Produces a Cartesian product — every row from the first table paired with every row from the second • no ON condition.
SELF JOIN	`SELECT e.name, m.name AS manager` `FROM employees e` `JOIN employees m ON e.manager_id = m.id`	• Joins a table to itself using aliases • commonly used for hierarchical data (employee–manager) or comparing rows within the same table.
LATERAL JOIN (CROSS JOIN LATERAL)	`SELECT d.name, e.name, e.salary` `FROM departments d` `CROSS JOIN LATERAL (` `SELECT name, salary FROM employees` `WHERE department_id = d.id` `ORDER BY salary DESC LIMIT 3` `) e`	• Allows the subquery to reference columns from tables earlier in the FROM clause • useful for top-N-per-group and complex per-row calculations; `LEFT JOIN LATERAL ... ON true` keeps rows with no subquery results.
CROSS APPLY / OUTER APPLY	`SELECT d.name, e.name` `FROM departments d` `CROSS APPLY (` `SELECT TOP 3 name FROM employees` `WHERE department_id = d.id` `ORDER BY salary DESC` `) e`	• SQL Server equivalent of LATERAL — CROSS APPLY excludes rows with no results (like INNER JOIN) • OUTER APPLY includes rows with no results (like LEFT JOIN).
NATURAL JOIN	`SELECT * FROM employees NATURAL JOIN departments`	• Automatically joins on all columns with identical names in both tables • risky in production — schema changes can silently break queries.

Table 5: Aggregate Functions

Function	Example	Description
COUNT()	`SELECT COUNT(*) FROM orders` `SELECT COUNT(DISTINCT customer_id) FROM orders`	• `COUNT()` counts all rows* including NULLs • `COUNT(col)` skips NULLs; `DISTINCT` counts unique values.
SUM()	`SELECT SUM(amount) FROM payments`	• Totals all non-NULL values in a column • returns NULL if all values are NULL.
AVG()	`SELECT AVG(salary) FROM employees WHERE dept = 'Sales'`	• Arithmetic mean of non-NULL values • NULLs are excluded, not treated as zero.
MAX()	`SELECT MAX(order_date) FROM orders`	• Returns the largest value in a column • works on dates and strings too.
MIN()	`SELECT MIN(price) FROM products WHERE active = 1`	Returns the smallest value in a column.
GROUP_CONCAT / STRING_AGG	`STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY name)`	• Concatenates string values from a group into a single delimited string • SQL Server/PostgreSQL use STRING_AGG • MySQL uses GROUP_CONCAT.
VARIANCE / VAR_SAMP	`SELECT VAR(salary) FROM employees` `SELECT VARIANCE(salary) FROM employees`	• Statistical variance of the values in a group • VAR/VAR_SAMP (sample), VARP/VAR_POP (population) • name varies by database.
STDDEV / STDEV	`SELECT STDEV(salary) FROM employees`	• Standard deviation of values • STDEV/STDDEV_SAMP (sample), STDEVP/STDDEV_POP (population) • name varies by database.

Table 6: Window Functions — Ranking

Function	Example	Description
ROW_NUMBER()	`ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)`	• Assigns a unique sequential integer per row within a partition • ties receive different numbers.
RANK()	`RANK() OVER (ORDER BY score DESC)`	• Assigns rank with gaps after ties — two rows tied at rank 2 are both rank 2; next row is rank 4 • use for competition-style rankings.
DENSE_RANK()	`DENSE_RANK() OVER (ORDER BY score DESC)`	• Like RANK but no gaps — two rows tied at rank 2 are both rank 2 • next row is rank 3.
NTILE()	`NTILE(4) OVER (ORDER BY salary)`	• Divides the result into N roughly equal buckets and assigns a bucket number to each row • useful for quartiles and deciles.
PERCENT_RANK()	`PERCENT_RANK() OVER (ORDER BY salary)`	• Calculates relative rank as `(rank - 1) / (total rows - 1)` — always returns 0 to 1 • first row is always 0.0.
CUME_DIST()	`CUME_DIST() OVER (ORDER BY salary)`	• Cumulative distribution — fraction of rows with value ≤ current row value • returns (0, 1] • ties receive the same value.

Table 7: Window Functions — Aggregate & Analytical

Function	Example	Description
SUM() OVER()	`SUM(amount) OVER (PARTITION BY dept ORDER BY date)`	• Running total within a partition • without ORDER BY, computes group total for each row.
LAG()	`LAG(close_price, 1) OVER (ORDER BY date)`	• Returns value from a previous row within the same window • first row returns NULL by default.
LEAD()	`LEAD(close_price, 1) OVER (ORDER BY date)`	• Returns value from a subsequent row within the same window • last row returns NULL by default.
FIRST_VALUE()	`FIRST_VALUE(salary) OVER (PARTITION BY dept ORDER BY hire_date)`	• Returns the first value in the window frame • use `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING` to get the partition's true first value regardless of ordering.
LAST_VALUE()	`LAST_VALUE(salary) OVER (PARTITION BY dept ORDER BY hire_date` `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)`	• Returns the last value in the window frame • requires explicit ROWS BETWEEN to avoid default frame stopping at CURRENT ROW.
NTH_VALUE()	`NTH_VALUE(salary, 2) OVER (` `PARTITION BY dept ORDER BY salary DESC` `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)`	• Returns the Nth row's value within the window frame • requires expanding the frame to UNBOUNDED FOLLOWING to correctly access rows after the current position.
AVG() OVER()	`AVG(salary) OVER (PARTITION BY department_id)`	Moving or partition average — with PARTITION BY and no ORDER BY, computes partition mean for every row.
COUNT() OVER()	`COUNT(*) OVER (PARTITION BY customer_id)`	• Returns the number of rows in the partition for every row • useful to add a total count alongside detail rows.

Table 8: Window Frame Specification

Clause	Example	Description
ROWS BETWEEN ... AND ...	`SUM(amount) OVER (` `ORDER BY date` `ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)`	• Defines frame as physical row offsets — counts actual rows regardless of values • precise and generally preferred for moving calculations.
RANGE BETWEEN ... AND ...	`SUM(amount) OVER (` `ORDER BY date` `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)`	• Defines frame using logical value ranges — includes all rows with equal ORDER BY values as the current row • default when ORDER BY is specified without explicit frame clause.
UNBOUNDED PRECEDING	`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`	Frame starts at the first row of the partition — used to compute cumulative aggregations from the partition start.
UNBOUNDED FOLLOWING	`ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING`	Frame extends to the last row of the partition — needed for LAST_VALUE and NTH_VALUE to see all subsequent rows.
N PRECEDING / N FOLLOWING	`ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING`	• Frame spans N rows before to M rows after the current row • enables fixed-size rolling windows (e.g., 7-day average).
CURRENT ROW	`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`	• Represents the current row as a frame boundary • default end point when ORDER BY is present.

Table 9: Subqueries & CTEs

Technique	Example	Description
Subquery (WHERE)	`SELECT * FROM employees` `WHERE dept_id IN (SELECT id FROM departments WHERE budget > 100000)`	• Embeds a query inside another query for filtering • the inner query runs once (non-correlated) or per row (correlated).
Correlated Subquery	`SELECT * FROM employees e1` `WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.dept = e1.dept)`	• References an outer query's columns inside the subquery • executes once per outer row — can be slow on large datasets.
Subquery (FROM — Derived Table)	`SELECT dept, avg_sal FROM` `(SELECT dept, AVG(salary) AS avg_sal FROM employees GROUP BY dept) t`	• A subquery in the FROM clause • must have an alias • computed once and referenced like a table.
EXISTS	`SELECT name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)`	• Tests for row existence — more efficient than IN when the subquery returns large result sets • stops at first match.
CTE (Common Table Expression)	`WITH ranked AS (` `SELECT , RANK() OVER (ORDER BY salary DESC) AS rk FROM employees` `)` `SELECT FROM ranked WHERE rk <= 10`	• Named temporary result set scoped to the query; improves readability vs nested subqueries • can be referenced multiple times in the same query.
Recursive CTE	`WITH RECURSIVE org AS (` `SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL` `UNION ALL` `SELECT e.id, e.name, e.manager_id FROM employees e JOIN org o ON e.manager_id = o.id` `)` `SELECT * FROM org`	• CTE that references itself to traverse hierarchical or graph data (org charts, bills of materials) • requires base case UNION ALL recursive case; always include a termination condition.
Scalar Subquery	`SELECT name, (SELECT MAX(salary) FROM employees) AS max_sal FROM employees`	• A subquery in the SELECT clause that returns a single value • errors if it returns more than one row.

Table 10: Set Operations

Operation	Example	Description
UNION	`SELECT city FROM customers UNION SELECT city FROM suppliers`	• Combines result sets and removes duplicates • slower than UNION ALL due to deduplication.
UNION ALL	`SELECT product_id FROM orders_2024 UNION ALL SELECT product_id FROM orders_2025`	• Combines result sets including all duplicates • faster than UNION — prefer when duplicates are acceptable.
INTERSECT	`SELECT customer_id FROM orders_jan INTERSECT SELECT customer_id FROM orders_feb`	• Returns rows that appear in both result sets • result is deduplicated.
EXCEPT (MINUS)	`SELECT customer_id FROM customers EXCEPT SELECT customer_id FROM orders`	• Returns rows in the first set that do not appear in the second • MySQL uses MINUS • result is deduplicated.

Table 11: Advanced Grouping

Extension	Example	Description
ROLLUP	`SELECT year, quarter, SUM(sales)` `FROM revenue` `GROUP BY ROLLUP(year, quarter)`	• Generates hierarchical subtotals — produces N+1 grouping sets for N columns • e.g., totals by (year, quarter), (year), and grand total in one query.
CUBE	`SELECT dept, year, SUM(revenue)` `FROM sales GROUP BY CUBE(dept, year)`	• Generates all possible combinations of subtotals — 2^N grouping sets for N columns • includes every cross-tabulation plus the grand total.
GROUPING SETS	`GROUP BY GROUPING SETS` `((dept, year), (dept), (year), ())`	• Explicit control over which grouping combinations to compute • avoids computing all CUBE combinations when only specific aggregations are needed.
GROUPING()	`SELECT GROUPING(dept), dept, SUM(sal)` `FROM employees GROUP BY ROLLUP(dept)`	• Returns 1 for super-aggregate rows (where the column was rolled up to NULL) and 0 for regular rows • distinguishes ROLLUP nulls from actual NULL values.
GROUPING_ID()	`SELECT GROUPING_ID(dept, year), dept, year, SUM(sal)` `FROM employees GROUP BY ROLLUP(dept, year)`	• Returns an integer bitmap identifying which combination of columns was aggregated • more efficient than multiple GROUPING() calls when multiple dimensions are involved.

Table 12: PIVOT / UNPIVOT

Operator	Example	Description
PIVOT	`SELECT dept, [2023], [2024], [2025]` `FROM sales_data` `PIVOT (` `SUM(amount) FOR year IN ([2023],[2024],[2025])` `) AS pvt`	• Rotates row values into columns — aggregates a measure and spreads distinct values from one column into separate columns • T-SQL specific; requires fixed column list.
UNPIVOT	`SELECT dept, year, amount` `FROM wide_table` `UNPIVOT (` `amount FOR year IN ([2023],[2024],[2025])` `) AS unpvt`	• Reverses a pivot — transforms column headers back into row values • normalizes wide/denormalized tables.
Dynamic PIVOT (Conditional Aggregation)	`SELECT dept,` `SUM(CASE WHEN year=2024 THEN sales END) AS [2024],` `SUM(CASE WHEN year=2025 THEN sales END) AS [2025]` `FROM sales GROUP BY dept`	• ANSI-standard alternative to PIVOT using CASE expressions • works in all databases; required when column values are determined at runtime.

Table 13: Conditional Logic

Expression	Example	Description
CASE WHEN ... THEN ... END	`CASE WHEN salary > 100000 THEN 'Senior'` `WHEN salary > 60000 THEN 'Mid'` `ELSE 'Junior'` `END`	• SQL's primary conditional expression • evaluates conditions in order and returns the first matching THEN value • supports arbitrary WHERE-like predicates.
Simple CASE	`CASE status` `WHEN 'A' THEN 'Active'` `WHEN 'I' THEN 'Inactive'` `ELSE 'Unknown'` `END`	• Equality check shorthand — compares expression to a list of values • equivalent to `CASE WHEN expr = val THEN ...`.
IIF()	`IIF(salary > 50000, 'High', 'Low')`	• Ternary shorthand for a two-branch CASE (SQL Server / Access) • translates directly to CASE; nesting is limited to 10 levels.
CHOOSE()	`CHOOSE(quarter, 'Q1', 'Q2', 'Q3', 'Q4')`	• Returns the Nth item from a value list based on an integer index (1-based) • SQL Server only; simplifies positional lookups.
DECODE()	`DECODE(status, 1, 'Active', 0, 'Inactive', 'Unknown')`	Oracle proprietary equality-based CASE substitute: DECODE(expr, search, result [, ...] [, default]).

Table 14: String Functions

Function	Example	Description
CONCAT()	`CONCAT(first_name, ' ', last_name)`	• Joins strings • ignores NULLs (SQL Server/MySQL) • PostgreSQL uses `&#124• &#124•` operator.
CONCAT_WS()	`CONCAT_WS(', ', city, state, country)`	• Concatenates with a separator — first argument is the separator • automatically skips NULLs (does not emit separator for NULL values); SQL Server 2017+, PostgreSQL, MySQL.
UPPER() / LOWER()	`UPPER(last_name)`	Converts a string to uppercase or lowercase.
TRIM() / LTRIM() / RTRIM()	`TRIM(LEADING ' ' FROM name)`	• Removes leading, trailing, or both whitespace (or specified characters) • TRIM supports LEADING/TRAILING/BOTH in standard SQL.
SUBSTRING() / SUBSTR()	`SUBSTRING(email, 1, CHARINDEX('@', email) - 1)`	Extracts a portion of a string given a start position and optional length.
LEFT() / RIGHT()	`LEFT(phone, 3)` `RIGHT(zip_code, 4)`	• Extracts N characters from the left or right end of a string • a convenient shorthand for SUBSTRING.
LEN() / LENGTH()	`WHERE LEN(description) > 500`	• Returns the number of characters in a string • LEN in SQL Server (excludes trailing spaces), LENGTH in PostgreSQL/MySQL.
REPLACE()	`REPLACE(phone, '-', '')`	Substitutes all occurrences of a substring with another string.
CHARINDEX() / INSTR() / POSITION()	`CHARINDEX('@', email)`	• Returns the character position of a substring • CHARINDEX in SQL Server, INSTR in Oracle/MySQL, POSITION in PostgreSQL.
STRING_SPLIT()	`SELECT value FROM STRING_SPLIT('a,b,c', ',')`	• Splits a string on a delimiter and returns a table • SQL Server 2016+ • PostgreSQL uses `string_to_table`.
LPAD() / RPAD()	`LPAD(CAST(id AS VARCHAR), 8, '0')` `RPAD(name, 20, ' ')`	• Pads a string to a specified length by prepending/appending fill characters • PostgreSQL/MySQL/Oracle; SQL Server uses `RIGHT('00000000' + CAST(id AS VARCHAR), 8)` as equivalent.
INITCAP()	`INITCAP(LOWER(full_name))`	• Capitalizes the first letter of each word • PostgreSQL and Oracle • no direct equivalent in SQL Server (requires custom expression).
FORMAT()	`FORMAT(amount, 'C2', 'en-US')`	• Formats a value as a string using .NET format strings (SQL Server only) • supports currency, dates, decimals with locale awareness.

Table 15: Date & Time Functions

Function	Example	Description
GETDATE() / NOW() / CURRENT_TIMESTAMP	`SELECT GETDATE()`	• Returns the current date and time • GETDATE() in SQL Server, NOW() in MySQL/PostgreSQL, CURRENT_TIMESTAMP is ANSI standard.
DATEADD() / DATE_ADD() / INTERVAL	`DATEADD(month, 3, hire_date)` `hire_date + INTERVAL '3 months'`	• Adds a time interval to a date • DATEADD in SQL Server, INTERVAL syntax in PostgreSQL/MySQL.
DATEDIFF()	`DATEDIFF(day, start_date, end_date)`	• Returns the difference between two dates in a specified unit (day, month, year, etc.) • SQL Server specific • PostgreSQL uses `date_part('day', end - start)`.
DATEPART() / EXTRACT()	`DATEPART(year, hire_date)` `EXTRACT(YEAR FROM hire_date)`	• Returns a specific date component (year, month, day, hour, etc.) • DATEPART in SQL Server, EXTRACT in ANSI SQL / PostgreSQL.
CAST()	`CAST('2025-01-15' AS DATE)`	• Converts a string to a date type • ANSI standard.
FORMAT() / TO_CHAR()	`TO_CHAR(order_date, 'YYYY-MM')` `FORMAT(order_date, 'yyyy-MM')`	• Formats a date as a string with a template pattern • TO_CHAR in PostgreSQL/Oracle, FORMAT in SQL Server • useful for grouping by year-month.
EOMONTH()	`EOMONTH(GETDATE())`	• Returns the last day of the month for a given date • SQL Server 2012+.
DATE_TRUNC()	`DATE_TRUNC('month', created_at)`	• Truncates a timestamp to a specified precision (year, quarter, month, day, hour) • PostgreSQL / Snowflake.
CONVERT() (date format)	`CONVERT(VARCHAR, GETDATE(), 103)`	• Formats date to string using a style code (SQL Server) • style 103 = DD/MM/YYYY, 120 = ISO format.

Table 16: Numeric Functions

Function	Example	Description
ROUND()	`ROUND(price, 2)`	• Rounds to N decimal places • negative N rounds to tens, hundreds, etc.
FLOOR() / CEILING()	`FLOOR(4.7)` → `4` `CEILING(4.2)` → `5`	• FLOOR returns the largest integer ≤ value • CEILING returns the smallest integer ≥ value.
ABS()	`ABS(balance - target)`	Returns absolute value — removes the sign.
POWER() / SQRT()	`POWER(2, 10)` → `1024` `SQRT(144)` → `12`	• POWER raises to an exponent • SQRT returns the square root.
MOD() / %	`SELECT 17 % 5` → `2` `MOD(17, 5)` → `2`	• Returns the remainder after integer division • `%` in SQL Server/PostgreSQL, MOD() in Oracle/MySQL.
LOG() / LOG10() / LN()	`LOG(100, 10)` → `2` `LOG(EXP(1))` → `1`	• LOG(x) = natural log in most databases; LOG(x, base) in SQL Server for arbitrary base • LOG10 is base-10; LN = natural log in Oracle/PostgreSQL.
SIGN()	`SIGN(balance)`	• Returns -1, 0, or 1 based on whether value is negative, zero, or positive • useful for directional logic without CASE.
RAND() / RANDOM()	`SELECT RAND()` (SQL Server) `SELECT RANDOM()` (PostgreSQL)	• Returns a pseudo-random float between 0 and 1 • RAND in SQL Server/MySQL, RANDOM() in PostgreSQL.
TRUNC() / TRUNCATE()	`TRUNC(3.987, 2)` → `3.98`	• Truncates (not rounds) to N decimal places • Oracle/PostgreSQL use TRUNC, MySQL uses TRUNCATE.

Table 17: NULL Handling

Technique	Example	Description
IS NULL / IS NOT NULL	`WHERE phone IS NULL` `WHERE email IS NOT NULL`	• Tests for NULL values • NULL cannot be compared with = or !=.
COALESCE()	`COALESCE(mobile, home_phone, 'N/A')`	• Returns the first non-NULL argument • ANSI standard • accepts any number of arguments • equivalent to nested NVL.
NULLIF()	`NULLIF(denominator, 0)`	• Returns NULL if both arguments are equal, otherwise returns the first argument • prevents division-by-zero: `value / NULLIF(denom, 0)`.
ISNULL() / IFNULL() / NVL()	`ISNULL(discount, 0)`	• Two-argument NULL substitution • ISNULL in SQL Server, IFNULL in MySQL, NVL in Oracle • less portable than COALESCE.
NULL in aggregates	`AVG(score)` (NULL ignored) `COUNT(*)` (NULL included)	• Most aggregate functions exclude NULLs automatically • `COUNT(*)` counts all rows; `COUNT(col)` skips NULLs.
NULL in JOINs	`a LEFT JOIN b ON a.id = b.id -- NULL rows from b`	• NULL never equals NULL in join conditions — rows with NULL keys are not matched • use IS NULL filter to find unmatched rows.

Table 18: Pattern Matching

Technique	Example	Description
LIKE	`WHERE name LIKE 'A%'` `WHERE code LIKE '_-[0-9][0-9]'`	• `%` matches any sequence of characters; `_` matches exactly one character • SQL Server also supports character classes `[a-z]`.
ILIKE	`WHERE name ILIKE '%smith%'`	• Case-insensitive LIKE • PostgreSQL only • equivalent to `LIKE` with `LOWER()`.
NOT LIKE	`WHERE email NOT LIKE '%@example.com'`	Inverts pattern match — returns rows where the value does not match the pattern.
REGEXP_LIKE / SIMILAR TO / ~ (tilde)	`REGEXP_LIKE(phone, '^\d{3}-\d{4}$')` (Oracle/MySQL) `phone ~ '^\d{3}-\d{4}$'` (PostgreSQL)	• Full regular expression matching • REGEXP_LIKE in Oracle/MySQL, `~` in PostgreSQL, SIMILAR TO is SQL standard but limited; REGEXP_MATCH in PostgreSQL extracts matches.
CONTAINS	`WHERE CONTAINS(description, 'fast AND reliable')`	• Full-text search predicate (SQL Server) • requires a full-text index; supports boolean operators and proximity searches.
ESCAPE clause	`WHERE path LIKE '100\%' ESCAPE '\'`	Treats special LIKE metacharacters (`%`, `_`) as literals when prefixed with the escape character.

Table 19: Data Type Conversion

Function	Example	Description
CAST()	`CAST('3.14' AS DECIMAL(10,2))`	• ANSI standard type conversion • explicit • raises an error if conversion fails.
CONVERT()	`CONVERT(INT, '42')`	• SQL Server / MySQL type conversion • in SQL Server also accepts an optional style code for date formatting.
TRY_CAST()	`TRY_CAST(user_input AS INT)`	• Safe CAST (SQL Server) — returns NULL instead of an error when conversion fails • useful for validating user input or dirty data.
TRY_CONVERT()	`TRY_CONVERT(DATE, date_string, 101)`	• Safe CONVERT (SQL Server) — returns NULL on conversion failure • supports style codes.
TRY_PARSE()	`TRY_PARSE('€1.234,56' AS MONEY USING 'de-DE')`	• Safe locale-aware string parsing (SQL Server) — returns NULL on failure • supports culture parameter for locale-specific number and date formats.
TO_NUMBER() / TO_DATE() / TO_TIMESTAMP()	`TO_NUMBER('1,234.56', '9,999.99')` `TO_DATE('01/15/2025', 'MM/DD/YYYY')`	• Oracle / PostgreSQL format-model conversions • allow precise format strings for parsing and output.
PARSE()	`PARSE('15 Janvier 2025' AS DATE USING 'fr-FR')`	• SQL Server culture-aware string-to-type parse; raises an error on failure (use TRY_PARSE for safe version) • depends on .NET CLR.
STR()	`STR(amount, 10, 2)`	Converts numeric to character string (SQL Server) with specified length and decimal precision.

Table 20: Constraints & Keys

Constraint	Example	Description
PRIMARY KEY	`id INT PRIMARY KEY`	• Uniquely identifies each row • enforces NOT NULL + UNIQUE • one per table • creates a clustered index by default in SQL Server.
FOREIGN KEY	`FOREIGN KEY (dept_id) REFERENCES departments(id)`	Enforces referential integrity — value must exist in the referenced table or be NULL.
NOT NULL	`name VARCHAR(100) NOT NULL`	• Rejects NULL values for the column • data must always be provided.
UNIQUE	`email VARCHAR(255) UNIQUE`	• Ensures all values in the column are distinct • allows one NULL in most databases; creates a unique index.
CHECK	`salary DECIMAL CHECK (salary >= 0)`	Validates data against a Boolean expression at insert/update time.
DEFAULT	`created_at DATETIME DEFAULT GETDATE()`	Supplies a value automatically when no value is specified during INSERT.
ON DELETE CASCADE	`FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE`	• Automatically deletes child rows when the referenced parent row is deleted • ON DELETE SET NULL is the null-propagating alternative.

Table 21: Indexes

Concept	Example	Description
CREATE INDEX	`CREATE INDEX idx_last_name ON customers (last_name)`	Creates a non-unique index to speed up queries that filter or sort on the column(s).
CREATE UNIQUE INDEX	`CREATE UNIQUE INDEX idx_email ON users (email)`	• Enforces uniqueness on the indexed column(s) while accelerating lookups • alternative to declaring a UNIQUE constraint.
Clustered Index	`CREATE CLUSTERED INDEX idx_order_id ON orders (order_id)`	• Physically orders the table rows by the index key • only one per table; the PRIMARY KEY creates a clustered index by default in SQL Server.
Non-clustered Index	`CREATE NONCLUSTERED INDEX idx_dept ON employees (department_id)`	• Maintains a separate structure pointing back to the heap or clustered index • multiple allowed per table; faster for selective queries but adds write overhead.
Composite Index	`CREATE INDEX idx_name_dept ON employees (last_name, department_id)`	• Index on multiple columns — most effective when queries filter on the leading column(s) first • column order matters for index selectivity.
Covering Index (INCLUDE)	`CREATE INDEX idx_cust ON orders (customer_id)` `INCLUDE (amount, order_date)`	• Adds non-key columns to the index leaf level via INCLUDE • allows queries to be satisfied entirely from the index without a key lookup back to the table ("index covers the query").
Filtered Index (Partial Index)	`CREATE INDEX idx_active ON users (email)` `WHERE active = 1`	• Creates an index over a subset of rows matching a WHERE condition • smaller, faster, and less write-overhead than a full index on the column.
DROP INDEX	`DROP INDEX idx_last_name ON customers`	• Removes an index • syntax varies (SQL Server: table qualified • MySQL: DROP INDEX ON table • PostgreSQL: DROP INDEX name).

Table 22: Views

Technique	Example	Description
CREATE VIEW	`CREATE VIEW active_customers AS` `SELECT * FROM customers WHERE active = 1`	• Creates a saved query accessible as a table • data is computed at query time; no storage for standard views.
CREATE OR REPLACE VIEW	`CREATE OR REPLACE VIEW monthly_sales AS` `SELECT EXTRACT(MONTH FROM order_date) AS month, SUM(amount) FROM orders GROUP BY 1`	• Atomically replaces an existing view definition without dropping and re-creating • PostgreSQL/MySQL • SQL Server uses `ALTER VIEW`.
WITH CHECK OPTION	`CREATE VIEW eng_staff AS` `SELECT * FROM employees WHERE dept = 'Engineering'` `WITH CHECK OPTION`	Prevents INSERT or UPDATE through the view that would cause the row to no longer satisfy the view's WHERE clause.
Materialized View	`CREATE MATERIALIZED VIEW report_summary AS` `SELECT dept, SUM(salary) FROM employees GROUP BY dept;` `REFRESH MATERIALIZED VIEW report_summary;`	• Stores query results physically — reads are instant but data must be refreshed manually or on schedule • PostgreSQL / Oracle; SQL Server equivalent is indexed view.
DROP VIEW	`DROP VIEW IF EXISTS active_customers`	• Removes a view definition • does not affect the underlying tables.

Table 23: Transactions

Statement	Example	Description
BEGIN TRANSACTION	`BEGIN TRANSACTION`	• Starts a transaction block — changes are not committed until COMMIT is called • `BEGIN` in PostgreSQL; implicit transactions are the default in some databases.
COMMIT	`COMMIT`	• Permanently saves all changes made since the last BEGIN TRANSACTION • releases locks.
ROLLBACK	`ROLLBACK`	• Undoes all changes since the last BEGIN or SAVEPOINT • used in error recovery.
SAVEPOINT	`SAVEPOINT before_update` `ROLLBACK TO before_update`	• Creates a named checkpoint within a transaction • enables partial rollbacks without aborting the entire transaction.
SET TRANSACTION ISOLATION LEVEL	`SET TRANSACTION ISOLATION LEVEL READ COMMITTED`	• Controls visibility of uncommitted changes to concurrent transactions • Levels (ascending strictness): READ UNCOMMITTED → READ COMMITTED → REPEATABLE READ → SERIALIZABLE; SNAPSHOT available in SQL Server.
WITH (NOLOCK)	`SELECT * FROM orders WITH (NOLOCK)`	• SQL Server dirty read hint — reads without acquiring shared locks; equivalent to READ UNCOMMITTED for that table • can return uncommitted or duplicate rows; use with caution.

Table 24: Error Handling

Construct	Example	Description
TRY...CATCH	`BEGIN TRY` `INSERT INTO orders VALUES (1, 99.99)` `END TRY` `BEGIN CATCH` `SELECT ERROR_MESSAGE(), ERROR_NUMBER()` `END CATCH`	• Structured error handling (SQL Server / Azure SQL) • any error in TRY block transfers control to CATCH block immediately.
ERROR_MESSAGE()	`SELECT ERROR_MESSAGE()`	Returns the error message text of the error that triggered the CATCH block.
ERROR_NUMBER()	`SELECT ERROR_NUMBER()`	• Returns the error number — correlates to sys.messages for system errors • user-defined errors use 50000+.
XACT_STATE()	`IF XACT_STATE() = -1 ROLLBACK` `IF XACT_STATE() = 1 COMMIT`	• Returns 1 (active committable), -1 (active uncommittable — must rollback), or 0 (no active transaction) • essential check in CATCH blocks before deciding to COMMIT or ROLLBACK.
THROW	`THROW 50001, 'Invalid customer ID', 1` `-- or to re-throw:` `THROW`	• Raises a user-defined error or re-throws the original error (bare THROW with no args in CATCH block) • SQL Server 2012+; preferred over RAISERROR for new code.
RAISERROR()	`RAISERROR('Custom error: %s', 16, 1, @detail)`	• Older mechanism for raising errors (SQL Server); supports severity levels 1–25 and message formatting • still widely used but THROW is simpler for most cases.

Table 25: JSON Functions

Function	Example	Description
JSON_VALUE()	`JSON_VALUE(payload, '$.customer.name')`	• Extracts a scalar value (string, number) from a JSON path expression • returns NULL if path not found.
JSON_QUERY()	`JSON_QUERY(payload, '$.address')`	• Extracts a JSON object or array fragment • returns NULL for scalar values (unlike JSON_VALUE).
JSON_OBJECT()	`JSON_OBJECT('id' VALUE id, 'name' VALUE name)`	• Constructs a JSON object from key-value pairs • SQL Server 2022+, PostgreSQL uses `json_build_object()`.
JSON_ARRAY()	`JSON_ARRAY(1, 'two', NULL)`	• Constructs a JSON array from values • SQL Server 2022+ • PostgreSQL uses `json_build_array()`.
ISJSON()	`WHERE ISJSON(payload) = 1`	• Returns 1 if the string is valid JSON, 0 otherwise • used for validation in CHECK constraints and WHERE filters.
JSON_MODIFY()	`JSON_MODIFY(payload, '$.status', 'active')`	• Returns a new JSON string with a property updated • set to NULL to delete the key • use `append` keyword to add to arrays.
OPENJSON()	`SELECT * FROM OPENJSON(@json)` `WITH (id INT, name VARCHAR(50))`	• Parses a JSON string into a relational rowset (SQL Server 2016+) • WITH clause maps JSON properties to columns with explicit types.

Table 26: Query Execution Order

Step	Example	Description
1. FROM / JOIN	`FROM orders o JOIN customers c ON o.customer_id = c.id`	• First step — identifies all source tables and performs joins • determines the working dataset.
2. WHERE	`WHERE o.status = 'shipped'`	• Filters individual rows before any grouping • cannot reference SELECT aliases or aggregate functions.
3. GROUP BY	`GROUP BY c.country`	Collapses rows into groups for aggregate computation.
4. HAVING	`HAVING COUNT(o.id) > 5`	• Filters groups after aggregation • can use aggregate functions.
5. SELECT (+ window functions)	`SELECT c.country, COUNT(o.id), SUM(o.amount)`	Evaluates expressions and aliases — SELECT is evaluated here but aliases are NOT yet available to WHERE, GROUP BY, or HAVING in most databases.
6. QUALIFY	`QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) = 1`	• Filters on window function results — like HAVING for window functions • Snowflake, BigQuery, DuckDB; not ANSI standard but reduces the need for a wrapping CTE or subquery.
7. DISTINCT	`SELECT DISTINCT country`	Removes duplicate rows from the result after SELECT expression evaluation.
8. ORDER BY	`ORDER BY SUM(o.amount) DESC`	• Sorts the final result set — can reference SELECT aliases and aggregate functions • executed last.
9. LIMIT / OFFSET / TOP	`LIMIT 10 OFFSET 20`	• Restricts and paginates the final ordered result • executed after ORDER BY.

Table 27: Advanced Ordering

Technique	Example	Description
ORDER BY ASC / DESC	`ORDER BY salary DESC, name ASC`	• Multi-column sort — secondary columns resolve ties from the primary sort column • ASC is the default direction.
NULLS FIRST / NULLS LAST	`ORDER BY score DESC NULLS LAST`	• Explicit NULL positioning in the sort • default varies by database — PostgreSQL/Oracle: NULLs high by default (NULLS LAST for ASC) • SQL Server: NULLs sort lowest.
ORDER BY column position	`ORDER BY 1, 2`	• Reference by column index in the SELECT list • 1 = first selected column • fragile if columns are reordered.
CASE in ORDER BY	`ORDER BY CASE status WHEN 'urgent' THEN 1 WHEN 'normal' THEN 2 ELSE 3 END`	• Custom sort priority using a CASE expression • allows arbitrary ordering beyond simple ASC/DESC.
COLLATE	`ORDER BY name COLLATE Latin1_General_CI_AI`	• Overrides the collation for a sort operation — CI = case-insensitive, AI = accent-insensitive • useful for locale-aware sorting.
TOP WITH TIES	`SELECT TOP 3 WITH TIES name, score FROM leaderboard ORDER BY score DESC`	• Includes tied rows that match the last qualifying value • may return more rows than specified • SQL Server only.