learn.fttgsolutions.com · Cheat Sheets
Query. Filter. Aggregate.
Querying
| SELECT | SELECT col1, col2 FROM table | Retrieve specific columns from a table. |
| SELECT * | SELECT * FROM table | Retrieve all columns from a table. |
| SELECT DISTINCT | SELECT DISTINCT col FROM table | Return only unique values, removing duplicates. |
| AS (alias) | SELECT col AS alias FROM table | Give a column or table a temporary display name. |
| LIMIT | SELECT * FROM table LIMIT 10 | Return only the first n rows of results. |
| OFFSET | SELECT * FROM table LIMIT 10 OFFSET 20 | Skip a number of rows before starting to return results — used for pagination. |
| ORDER BY | SELECT * FROM table ORDER BY col ASC | Sort the result set by one or more columns. ASC (default) or DESC. |
| ORDER BY multiple | ORDER BY col1 ASC, col2 DESC | Sort by a primary column, then a secondary column for ties. |
Filtering
| WHERE | SELECT * FROM table WHERE col = 'value' | Filter rows to only those matching a condition. |
| Comparison operators | = equal
!= not equal
> greater than
< less than
>= greater than or equal
<= less than or equal | Compare a column value against a literal or another column. Use these inside WHERE clauses. |
| AND / OR | WHERE col1 = 'a' AND col2 > 10 | Combine multiple conditions. AND requires both; OR requires either. |
| NOT | WHERE NOT col = 'value' | Negate a condition — return rows where the condition is false. |
| IN | WHERE col IN ('a', 'b', 'c') | Match any value in a list — shorthand for multiple OR conditions. |
| NOT IN | WHERE col NOT IN ('a', 'b') | Exclude rows whose column value appears in the list. |
| BETWEEN | WHERE col BETWEEN 10 AND 50 | Filter rows where a value falls within an inclusive range. |
| LIKE | WHERE name LIKE 'J%' | Pattern matching. % matches any string, _ matches exactly one character. |
| NOT LIKE | WHERE name NOT LIKE 'J%' | Exclude rows whose column matches a pattern. Opposite of LIKE. |
| ILIKE | WHERE name ILIKE 'j%' | Case-insensitive pattern matching (PostgreSQL / some dialects). |
| IS NULL | WHERE col IS NULL | Return rows where the column has no value (NULL). |
| IS NOT NULL | WHERE col IS NOT NULL | Return rows where the column has a value. |
| EXISTS | WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.id = t1.id) | Return rows where the subquery produces at least one result. |
Aggregation
| COUNT | SELECT COUNT(*) FROM table | Count the number of rows. COUNT(col) ignores NULLs. |
| SUM | SELECT SUM(col) FROM table | Add up all numeric values in a column. |
| AVG | SELECT AVG(col) FROM table | Return the arithmetic mean of a numeric column. |
| MIN / MAX | SELECT MIN(col), MAX(col) FROM table | Return the smallest and largest value in a column. |
| GROUP BY | SELECT col, COUNT(*) FROM table GROUP BY col | Group rows with the same value so aggregate functions are applied per group. |
| HAVING | GROUP BY col HAVING COUNT(*) > 5 | Filter groups after aggregation — like WHERE but for grouped results. |
| GROUP BY multiple | GROUP BY col1, col2 | Group by a combination of columns — each unique pair becomes its own group. |
| COUNT DISTINCT | SELECT COUNT(DISTINCT col) FROM table | Count the number of unique non-null values in a column. |
Joins
| INNER JOIN | SELECT * FROM a
INNER JOIN b ON a.id = b.a_id | Return only rows that have a match in both tables. |
| LEFT JOIN | SELECT * FROM a
LEFT JOIN b ON a.id = b.a_id | Return all rows from the left table, with matched rows from the right. NULLs where no match. |
| RIGHT JOIN | SELECT * FROM a
RIGHT JOIN b ON a.id = b.a_id | Return all rows from the right table, with matched rows from the left. NULLs where no match. |
| FULL OUTER JOIN | SELECT * FROM a
FULL OUTER JOIN b ON a.id = b.a_id | Return all rows from both tables, with NULLs where there is no match on either side. |
| CROSS JOIN | SELECT * FROM a CROSS JOIN b | Return every combination of rows from both tables (Cartesian product). |
| SELF JOIN | SELECT a.name, b.name
FROM employees a
JOIN employees b ON a.manager_id = b.id | Join a table to itself — useful for hierarchical or comparative queries. |
| Multiple joins | FROM a
JOIN b ON a.id = b.a_id
JOIN c ON b.id = c.b_id | Chain joins across three or more tables in one query. |
Subqueries
| Subquery (WHERE) | SELECT * FROM orders
WHERE customer_id IN (
SELECT id FROM customers WHERE city = 'NYC'
) | Use the result of an inner query as a filter in the outer query. |
| Subquery (FROM) | SELECT * FROM (
SELECT col, COUNT(*) AS n FROM t GROUP BY col
) AS sub
WHERE n > 5 | Use a query result as a derived table — must be given an alias. |
| Correlated subquery | SELECT * FROM orders o
WHERE amount > (
SELECT AVG(amount) FROM orders WHERE customer_id = o.customer_id
) | A subquery that references columns from the outer query — re-runs for each outer row. |
| Scalar subquery | SELECT name, (SELECT MAX(score) FROM scores WHERE user_id = u.id) AS top
FROM users u | A subquery in the SELECT list that returns a single value per row. |
CTEs
| WITH (CTE) | WITH cte AS (
SELECT col, COUNT(*) AS n
FROM table
GROUP BY col
)
SELECT * FROM cte WHERE n > 10 | Common Table Expression — a named temporary result set that can be referenced in the main query like a table. |
| Multiple CTEs | WITH a AS (
SELECT ...
),
b AS (
SELECT ... FROM a
)
SELECT * FROM b | Chain multiple CTEs — later ones can reference earlier ones. |
| Recursive CTE | WITH RECURSIVE cte AS (
SELECT id, parent_id FROM tree WHERE parent_id IS NULL
UNION ALL
SELECT t.id, t.parent_id FROM tree t JOIN cte ON t.parent_id = cte.id
)
SELECT * FROM cte | Self-referencing CTE used to traverse hierarchical data like org charts or category trees. |
Window Functions
| ROW_NUMBER | ROW_NUMBER() OVER (ORDER BY col) | Assign a unique sequential number to each row in the result set. |
| RANK | RANK() OVER (ORDER BY score DESC) | Rank rows — tied rows get the same rank, and the next rank is skipped. |
| DENSE_RANK | DENSE_RANK() OVER (ORDER BY score DESC) | Rank rows — tied rows get the same rank, but no ranks are skipped. |
| PARTITION BY | ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) | Reset the window function for each group — like GROUP BY but without collapsing rows. |
| SUM (running total) | SUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) | Calculate a cumulative sum — adds up all previous rows up to the current one. |
| LAG | LAG(col, 1) OVER (ORDER BY date) | Access the value from the previous row in the result set. |
| LEAD | LEAD(col, 1) OVER (ORDER BY date) | Access the value from the next row in the result set. |
| FIRST_VALUE | FIRST_VALUE(col) OVER (PARTITION BY group ORDER BY date) | Return the first value in the window frame for each partition. |
| LAST_VALUE | LAST_VALUE(col) OVER (PARTITION BY group ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) | Return the last value in the window frame. Requires explicit frame clause for correct results. |
| NTILE | NTILE(4) OVER (ORDER BY score DESC) | Divide rows into n equal buckets and assign a bucket number to each row. |
Set Operations
| UNION | SELECT col FROM a
UNION
SELECT col FROM b | Combine results of two queries, removing duplicate rows. |
| UNION ALL | SELECT col FROM a
UNION ALL
SELECT col FROM b | Combine results of two queries, keeping all rows including duplicates. |
| INTERSECT | SELECT col FROM a
INTERSECT
SELECT col FROM b | Return only rows that appear in both result sets. |
| EXCEPT | SELECT col FROM a
EXCEPT
SELECT col FROM b | Return rows from the first query that do not appear in the second. |
Insert, Update, Delete
| INSERT | INSERT INTO table (col1, col2)
VALUES ('a', 1) | Add a new row to a table. |
| INSERT multiple | INSERT INTO table (col1, col2)
VALUES ('a', 1), ('b', 2), ('c', 3) | Insert multiple rows in a single statement. |
| INSERT SELECT | INSERT INTO table (col1, col2)
SELECT col1, col2 FROM other_table | Populate a table from the results of a query. |
| UPDATE | UPDATE table SET col1 = 'new'
WHERE id = 1 | Modify existing rows that match a condition. |
| UPDATE multiple cols | UPDATE table
SET col1 = 'a', col2 = 10
WHERE id = 1 | Update several columns at once in the same statement. |
| DELETE | DELETE FROM table WHERE id = 1 | Remove rows matching a condition. Omit WHERE to delete all rows. |
| TRUNCATE | TRUNCATE TABLE table | Remove all rows from a table quickly — faster than DELETE, not logged per-row. |
Table DDL
| CREATE TABLE | CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
age INT
) | Create a new table with defined columns and data types. |
| DROP TABLE | DROP TABLE table | Permanently delete a table and all its data. |
| DROP IF EXISTS | DROP TABLE IF EXISTS table | Drop a table only if it exists — prevents an error if it doesn't. |
| ALTER — add column | ALTER TABLE table ADD COLUMN email VARCHAR(255) | Add a new column to an existing table. |
| ALTER — drop column | ALTER TABLE table DROP COLUMN email | Remove a column from an existing table. |
| ALTER — rename column | ALTER TABLE table RENAME COLUMN old TO new | Rename a column without changing its data or type. |
| RENAME TABLE | ALTER TABLE old_name RENAME TO new_name | Rename an existing table. |
| CREATE INDEX | CREATE INDEX idx_name ON table (col) | Create an index to speed up queries that filter or sort on a column. |
| CREATE UNIQUE INDEX | CREATE UNIQUE INDEX idx ON table (col) | Create an index that also enforces uniqueness on the column. |
Constraints
| PRIMARY KEY | id INT PRIMARY KEY | Uniquely identifies each row. Implies NOT NULL and UNIQUE. |
| FOREIGN KEY | FOREIGN KEY (col) REFERENCES other_table(id) | Enforce referential integrity — col must match a value in the referenced table. |
| UNIQUE | email VARCHAR(255) UNIQUE | Ensure all values in a column are distinct. |
| NOT NULL | name VARCHAR(100) NOT NULL | Prevent NULL values from being inserted into a column. |
| DEFAULT | status VARCHAR(20) DEFAULT 'active' | Set a fallback value used when no value is supplied on insert. |
| CHECK | age INT CHECK (age >= 0) | Reject rows where the column value fails a condition. |
Conditional & NULL
| CASE WHEN | CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
ELSE 'C'
END | Return different values based on conditions — like an if/else in SQL. |
| CASE (simple) | CASE status
WHEN 'active' THEN 1
WHEN 'inactive' THEN 0
ELSE NULL
END | Match a column against specific values and return a result for each match. |
| COALESCE | COALESCE(col1, col2, 'default') | Return the first non-NULL value from the list — great for fallback values. |
| NULLIF | NULLIF(col, 0) | Return NULL if both arguments are equal, otherwise return the first argument. Useful to avoid division by zero. |
| IIF / IF | IIF(col > 0, 'positive', 'other') | Inline conditional (SQL Server / Access). Use CASE WHEN for broader compatibility. |
String Functions
| UPPER / LOWER | UPPER(col) / LOWER(col) | Convert a string to all uppercase or all lowercase. |
| LENGTH / LEN | LENGTH(col) / LEN(col) | Return the number of characters in a string. |
| TRIM | TRIM(col) | Remove leading and trailing whitespace from a string. |
| SUBSTRING | SUBSTRING(col, 1, 5) | Extract part of a string — start position and length. |
| CONCAT | CONCAT(first, ' ', last) | Join two or more strings together. |
| REPLACE | REPLACE(col, 'old', 'new') | Replace all occurrences of a substring within a string. |
| LIKE pattern | LIKE '%keyword%' | % matches any sequence of characters; _ matches exactly one character. |
| POSITION / CHARINDEX | POSITION('a' IN col) | Return the position of a substring within a string (1-based). Returns 0 if not found. |
| LEFT / RIGHT | LEFT(col, 3) / RIGHT(col, 3) | Return the first or last n characters of a string. |
Date Functions
| NOW / GETDATE | NOW() / GETDATE() | Return the current date and time. NOW() is standard SQL / PostgreSQL; GETDATE() is SQL Server. |
| CURRENT_DATE | CURRENT_DATE | Return today's date without the time component. |
| DATE_PART / EXTRACT | EXTRACT(YEAR FROM col)
DATE_PART('month', col) | Pull a specific part (year, month, day, hour) out of a date value. |
| DATE_TRUNC | DATE_TRUNC('month', col) | Truncate a date to the start of a period (month, week, year, etc.). |
| DATEDIFF | DATEDIFF(day, start_date, end_date) | Return the difference between two dates in the specified unit (SQL Server / MySQL). |
| DATE_ADD / INTERVAL | col + INTERVAL '7 days' | Add a time interval to a date value. |
| CAST to date | CAST('2024-01-01' AS DATE) | Convert a string or timestamp to a date type. |
| FORMAT date | TO_CHAR(col, 'YYYY-MM-DD') | Format a date as a string in a specific pattern (PostgreSQL). Use FORMAT() in SQL Server. |
Numeric Functions
| ROUND | ROUND(col, 2) | Round a number to a specified number of decimal places. |
| FLOOR / CEILING | FLOOR(col) / CEILING(col) | Round down to the nearest integer or up to the nearest integer. |
| ABS | ABS(col) | Return the absolute (positive) value of a number. |
| MOD | MOD(col, 2) or col % 2 | Return the remainder of a division. |
| POWER | POWER(col, 3) | Raise a number to a specified power. |
| SQRT | SQRT(col) | Return the square root of a number. |
| CAST numeric | CAST(col AS DECIMAL(10,2)) | Convert a value to a specific numeric type, controlling precision and scale. |
Views & Transactions
| CREATE VIEW | CREATE VIEW view_name AS
SELECT col1, col2 FROM table WHERE condition | Save a query as a named virtual table that can be queried like a real table. |
| DROP VIEW | DROP VIEW view_name | Delete a view (does not affect the underlying data). |
| BEGIN | BEGIN | Start a transaction — changes can be rolled back until committed. |
| COMMIT | COMMIT | Permanently save all changes made in the current transaction. |
| ROLLBACK | ROLLBACK | Undo all changes made since the transaction began. |
| SAVEPOINT | SAVEPOINT point1
ROLLBACK TO point1 | Set a named checkpoint within a transaction to partially roll back to. |