SQL: Queries, Joins, Aggregates & Window Functions

SELECT → FROM → JOIN → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

FROM / JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

WHERE filters rows before grouping. HAVING filters groups after GROUP BY , so it can use aggregates like COUNT(*) > 5 .

SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id HAVING SUM(amount) > 1000 ORDER BY total DESC;

Returns only rows that have a match in both tables.

Returns every row from the left table, plus matching rows from the right. Where there is no match, the right-side columns are NULL .

Full outer: all rows from both tables, with NULLs where there is no match. Cross: every combination of rows (Cartesian product).

SELECT c.id, c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL;

Primary key: uniquely identifies each row; unique and not null. Foreign key: a column that references a primary key in another table, enforcing referential integrity.

None. Any comparison with NULL using = is unknown, not true. Use WHERE phone IS NULL .

COUNT(*) counts all rows. COUNT(column) counts rows where that column is not NULL. COUNT(DISTINCT column) counts unique non-NULL values.

COUNT , SUM , AVG , MIN , MAX . All except COUNT(*) ignore NULL values.

WHERE name LIKE 'J_n%' matches Jan, Jones, Jenny.

Yes. WHERE age BETWEEN 18 AND 25 includes 18 and 25.

UNION combines result sets and removes duplicates. UNION ALL keeps duplicates and is faster. Both need the same number of compatible columns.

SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

A common table expression: a named temporary result defined with WITH name AS (...) and used by the query that follows. It makes complex queries readable and can be recursive.

ROW_NUMBER : 1, 2, 3, 4 RANK : 1, 2, 2, 4 DENSE_RANK : 1, 2, 2, 3

SELECT * FROM ( SELECT name, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employees ) ranked WHERE rn = 1;

DELETE : removes rows (optionally with WHERE). TRUNCATE : removes all rows quickly, keeps the table. DROP : removes the whole table, structure included.

Every row is updated, because there is no WHERE clause.

DDL (data definition): CREATE , ALTER , DROP , TRUNCATE . DML (data manipulation): SELECT , INSERT , UPDATE , DELETE .

Atomicity: all or nothing Consistency: valid state to valid state Isolation: concurrent transactions do not interfere Durability: committed data survives crashes

1NF: atomic values, no repeating groups. 2NF: 1NF, and no non-key column depends on only part of a composite key. 3NF: 2NF, and no non-key column depends on another non-key column.

It speeds up lookups, joins and sorting on the indexed columns (usually a B-tree). It costs storage and slows down INSERT, UPDATE and DELETE, which must update the index too.