home
›
cheatsheets
›
database_technologies
›
sql_fundamentals
Free to read Every card and diagram, and Quiz me
2
Free account Star cards. Your Quiz me marks are kept, so next time you drill just what you missed
3
Pro All 44 answers on this sheet, My sheet across every topic, and PDFs
A free account keeps your stars and Quiz me marks. Pro adds all 44 answers on this sheet, My sheet across every topic, and PDFs.
/
Read
Quiz me
Comfortable
Compact
0
Click a command to copy it
Done 0 /0
To review 0
On this sheet
01 How SQL works02 Tables, keys & constraints03 Insert, update & delete04 Filter, group & sort05 Joins & set operators06 Subqueries & CTEs07 Window functions08 Indexes & query speed09 Transactions & locks10 Views, procedures & security
The model SQL is declarative . You describe the result, and the optimizer picks how to get it. EXPLAIN shows its plan. Clauses run in a logical order . WHERE filters rows before grouping, HAVING filters groups after, and SELECT comes sixth. NULL means unknown. A comparison with it, even NULL = NULL, is never true, so WHERE skips the row.A transaction is all or nothing. COMMIT keeps every change, and ROLLBACK undoes them all. An index , usually a B-tree , finds a row in O(log n) steps instead of an O(n) scan. Writes to its columns must update it. WHERE runs before grouping, so it can't use an aggregate. SELECT runs sixth, so WHERE can't see its aliases, but ORDER BY can.
How SQL works the statement types, and how NULL behaves Four kinds of statements Define or change table structure DDL, with CREATE, ALTER, DROP and TRUNCATE.
Read and change rows DML, with SELECT, INSERT, UPDATE and DELETE.
Give or take away access DCL, with GRANT and REVOKE.
Save or undo a group of changes TCL, with COMMIT, ROLLBACK and SAVEPOINT.
Tip DDL is Data Definition Language, DML is Data Manipulation, DCL is Data Control and TCL is Transaction Control.
Work with NULL Find rows with a missing value WHERE bonus IS NULL
Show a default in place of NULL COALESCE(bonus, 0) Returns its first argument that isn't NULL.
Count rows, or count values COUNT(*), COUNT(bonus) The first counts rows. The second skips NULL.
Trap WHERE bonus = NULL returns no rows, because a comparison with NULL is never true. Write IS NULL.
Tables, keys & constraints define tables and the rules that guard their data Create a table with constraints Identify each row id INT PRIMARY KEY Unique and never NULL. One per table, indexed for you.
Require a value in a column name VARCHAR(50) NOT NULL
Reject duplicate values email VARCHAR(255) UNIQUE PostgreSQL and MySQL allow many NULLs, SQL Server only one. A table can have several unique columns.
Require a matching row in another table FOREIGN KEY (cust_id) REFERENCES customers(id)
Reject values that break a rule CHECK (total > 0)
Fill in a value the insert leaves out DEFAULT (CURRENT_DATE)
Trap A CHECK passes when its condition is NULL. Add NOT NULL when the column must have a value.
CHAR or VARCHAR Store a code that always has the same length country CHAR(2) Pads shorter values with spaces.
Store text of varying length, like names name VARCHAR(100) Stores the value plus a length prefix.
Trap Don't pick CHAR for speed. PostgreSQL's docs say it is usually the slowest of its string types.
Relationships and normal forms Model one-to-many, like a customer and their orders A foreign key on the many side, such as orders.cust_id.
Model one-to-one, like a person and a passport A foreign key that is also UNIQUE.
Model many-to-many, like students and courses A junction table, such as enrollments, with a foreign key to each side.
Reach first normal form (1NF) One atomic value per column, no repeating groups.
Reach second normal form (2NF) 1NF, and no non-key column depends on part of a composite key.
Reach third normal form (3NF) 2NF, and no non-key column depends on another non-key column.
Tip BCNF is a stricter 3NF. Every column that determines another must be a candidate key, a column set that could be the primary key.
Trap Normalizing too far slows reads, because queries need more joins.
Insert, update & delete change rows, and know which changes you can undo Insert and update rows Add a row INSERT INTO users (name) VALUES ('Ana');
Change some rows UPDATE emp SET salary = salary * 1.1 WHERE id = 7;
Trap An UPDATE or DELETE with no WHERE changes every row in the table.
DELETE, TRUNCATE or DROP Remove chosen rows DELETE FROM emp WHERE id = 7; Fires delete triggers, and it is slow on big tables.
Empty a table but keep it TRUNCATE TABLE emp; Much faster, with no delete triggers. MySQL also resets auto-increment.
Remove the table itself DROP TABLE emp;
Trap MySQL commits TRUNCATE and DROP at once, so ROLLBACK can't bring them back. PostgreSQL and SQL Server can roll back a TRUNCATE in a transaction.
Insert or update in one statement Upsert in PostgreSQL or SQLite ON CONFLICT (id) DO UPDATE SET qty = EXCLUDED.qty It needs a primary key or unique constraint.
Upsert in MySQL (8.0.19+) AS new ON DUPLICATE KEY UPDATE qty = new.qty new names the row in VALUES.
Upsert with standard MERGE MERGE INTO stock t USING src s ON t.id = s.id SQL Server and PostgreSQL 15+. SQL Server needs HOLDLOCK to be race-free.
Trap Select-then-insert leaves a gap. Another session can insert the same key between your check and your write.
Filter, group & sort pick rows, summarize them and page through them Text search and built-in functions Match a text pattern WHERE name LIKE 'J%' % matches any run of characters, _ exactly one.
Compare text ignoring case, on any engine UPPER(name) = UPPER('ana')
Join text CONCAT(first, ' ', last)
Pick a value by condition CASE WHEN salary > 9000 THEN 'High' ELSE 'Low' END
Tip MySQL's default collation already ignores case. PostgreSQL doesn't, but it has ILIKE.
Trap UPPER(name) hides the column from an ordinary index. An expression index on UPPER(name) fixes that.
Grouping and clause order Count rows per department SELECT dept_id, COUNT(*) FROM emp GROUP BY dept_id;
Filter rows before grouping WHERE salary > 5000
Filter groups after grouping HAVING COUNT(*) > 5
Filter on a SELECT alias WHERE runs before SELECT, so repeat the expression instead.
Sort by a SELECT alias ORDER BY avg_sal DESC This works, because ORDER BY runs after SELECT.
Trap Every column in SELECT must appear in GROUP BY or sit inside an aggregate.
Find duplicate rows List each repeated email with its count GROUP BY email HAVING COUNT(*) > 1
Mark the extra copies, ready to delete ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) Rows numbered above 1 are the extra copies.
Trap A self-join on email finds copies too, but = never matches NULL, so it misses the NULL emails.
Page through results OFFSET skipped 20 page keyset page rows read Page 3 with OFFSET reads 30 rows and throws away 20. Keyset pagination seeks straight to the page through an index.
Get page 3 at 10 rows a page LIMIT 10 OFFSET 20 OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY MySQL and PostgreSQL use the first. SQL Server uses the second, standard form.
Page deep into a large table WHERE id > ? ORDER BY id LIMIT 10 Keyset pagination. Every page costs the same.
Trap Sort by a unique key. Without a stable ORDER BY, pages can repeat or skip rows.
Joins & set operators combine rows from two tables or two queries Join types Match Left only Right only
INNER JOIN Keep only rows that match
LEFT JOIN Keep every row of the left table
RIGHT JOIN Keep every row of the right table
FULL OUTER JOIN MySQL has none. Combine a left and a right join with UNION. Keep every row of both tables
Tip CROSS JOIN has no condition. It pairs every row with every row, the Cartesian product.
Trap A WHERE test like d.name = 'Sales' drops the unmatched rows, so the LEFT JOIN acts like an inner join.
Self-joins Pair each employee with their manager LEFT JOIN emp m ON e.manager_id = m.id LEFT JOIN keeps the CEO, whose manager_id is NULL.
List each pair of colleagues once e1.id < e2.id Not !=, which returns each pair twice, once in each order.
UNION, INTERSECT and EXCEPT Combine two results and drop duplicates UNION
Combine two results and keep every row UNION ALL Faster, with no sort or hash to find repeats.
Keep rows found in both INTERSECT MySQL supports it, and EXCEPT, from 8.0.31.
Keep rows of the first query that the second lacks EXCEPT Order matters. Oracle calls it MINUS.
Tip Both queries need the same number of columns, with compatible types.
Trap UNION drops repeats even within one query. Use UNION ALL when repeats matter.
Subqueries & CTEs nest one query in another, or name its steps Subqueries Compare each salary with the company average WHERE salary > (SELECT AVG(salary) FROM emp) Non-correlated. It runs once.
Compare each salary with its department's average WHERE e2.dept_id = e1.dept_id Correlated. It reads the outer row, so it runs once per row.
Name the four subquery shapes Scalar returns one value, row one row, column a list, and table rows and columns.
Tip Optimizers often turn a correlated subquery into a join, so read the plan first.
EXISTS, IN and NOT IN Keep customers that have an order WHERE EXISTS (SELECT 1 FROM orders o WHERE o.cust_id = c.id) Stops at the first match.
Do the same with IN WHERE id IN (SELECT cust_id FROM orders) Also fits a short list, such as IN (1, 2, 3).
Find customers with no order WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.cust_id = c.id)
Tip The old rule that EXISTS is faster than IN is not reliable. Check the plan.
Trap If the subquery returns even one NULL, NOT IN returns no rows. Use NOT EXISTS when the column can be NULL.
Common table expressions WITH RECURSIVE tree AS ( SELECT id, parent_id, 0 AS level the anchor FROM nodes WHERE parent_id IS NULL UNION ALL then once per level SELECT n.id, n.parent_id, t.level + 1 FROM nodes n JOIN tree t ON n.parent_id = t.id ) SELECT * FROM tree;
Name a step of a query Put it in a WITH clause. The main query then reads it like a table.
Reuse a costly result across statements Use a temporary table. A CTE lasts for one statement.
Store a tree for faster reads Use a nested set, a materialized path such as /1/4/9/, or a closure table.
Tip PostgreSQL and MySQL need WITH RECURSIVE. SQL Server takes plain WITH.
Trap A CTE is for readability, not speed. SQL Server runs its query again for each reference.
Window functions rank, compare and total rows without collapsing them Window functions and ranking salary 100K 90K 90K 80K ROW_NUMBER 1 2 3 4 RANK 1 2 2 4 skips 3 DENSE_RANK 1 2 2 3 no gap
Show the department average on every row AVG(salary) OVER (PARTITION BY dept_id) GROUP BY would collapse each department to one row.
Filter on a window function's result Filter in an outer query or CTE. WHERE runs before window functions.
Pick the top row, or top N, in each group ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn Keep rn = 1, or rn <= 3, in an outer query.
Find the top three distinct salaries DENSE_RANK() OVER (ORDER BY salary DESC)
Rank with a gap after each tie RANK() OVER (ORDER BY salary DESC)
Trap Tied rows get their ROW_NUMBER in any order. Add a column to ORDER BY that breaks the tie.
LAG, LEAD and running totals Read the previous row's value LAG(sales) OVER (ORDER BY sale_date) Returns NULL on the first row, unless you pass a default.
Read the next row's value, with 0 at the end LEAD(sales, 1, 0) OVER (ORDER BY sale_date) Its arguments are the column, the offset and the default.
Keep a running total SUM(sales) OVER (ORDER BY sale_date ROWS UNBOUNDED PRECEDING)
Average the last 3 rows AVG(sales) OVER (ORDER BY sale_date ROWS 2 PRECEDING) The current row and the 2 before it.
Trap With ORDER BY and no frame, the default includes rows tied with the current one. Two sales on one date then share a total, so write ROWS.
Indexes & query speed find rows without reading the whole table Indexes Index two columns together CREATE INDEX ix_name ON emp (last_name, first_name); Serves last_name alone. For first_name alone, it needs a skip scan, in MySQL 8.0.13+ or PostgreSQL 18.
List what an index costs Inserts, deletes and updates to indexed columns must update it. It also takes disk and memory.
Store the rows themselves in key order A clustered index, at most one per table. Ranges on its key are fast.
Add more indexes to a table Non-clustered. A lookup follows a row locator to fetch the row.
Answer a query from the index alone CREATE INDEX ix_cust ON orders (cust_id, total); A covering index holds every column the query reads.
Tip MySQL's InnoDB always clusters a table. PostgreSQL keeps no clustered order.
Trap When a query returns a large share of the table, a full scan can be cheaper. The optimizer may then skip your index.
Speed up a slow query See how the database runs a query EXPLAIN SELECT * FROM orders WHERE cust_id = 42; Look for a full scan where you expected an index seek.
Make a date filter sargable order_date >= '2024-01-01' AND order_date < '2025-01-01' Sargable means an index can serve it. YEAR(order_date) = 2024 can't use one.
Read less data SELECT id, total FROM orders Not SELECT *. An index may then cover the query.
Speed up queries on a huge table Partition it by a key such as date. A filter on that key skips the other partitions.
Trap A query hint forces one plan even after the data changes. Use one only when the optimizer keeps choosing badly.
Transactions & locks keep data correct when many sessions write at once Transactions and ACID Start a transaction START TRANSACTION; You can also write BEGIN. SQL Server uses BEGIN TRANSACTION.
Keep every change, or undo them all COMMIT; ROLLBACK;
Undo back to a marked point SAVEPOINT sp1; ROLLBACK TO SAVEPOINT sp1;
Make a transfer all or nothing Atomicity. Every step succeeds, or the database undoes them all.
Keep the data valid before and after Consistency. Each transaction moves from one valid state to another.
Stop concurrent transactions from interfering Isolation, through locks or multiversion concurrency control (MVCC).
Make a commit survive a crash Durability. The database replays a write-ahead log after a crash.
Trap With autocommit on, each statement commits on its own. A ROLLBACK after it has nothing to undo.
Isolation levels Dirty Re-read New rows
READ UNCOMMITTED Read even uncommitted changes
READ COMMITTED PostgreSQL's default. See only committed data
REPEATABLE READ MySQL InnoDB's default. PostgreSQL's blocks new rows too. Get the same values when you read a row again
SERIALIZABLE Act as if transactions ran one at a time
Tip A dirty read sees uncommitted changes. A non-repeatable read gets a new value when it reads a row again. A phantom is a new row in a rerun query.
Trap At REPEATABLE READ and SERIALIZABLE, PostgreSQL may abort a transaction with a serialization failure. Your code must retry it.
Locks and deadlocks Let many transactions read the same data A shared lock.
Let only one transaction change the data An exclusive lock.
Handle two transactions waiting on each other That's a deadlock. The database detects it and rolls one back.
Tip With MVCC, as in PostgreSQL, readers see an older row version, so reads and writes don't block each other.
Views, procedures & security logic stored in the database, and keeping data safe Views and row-level security Hide sensitive columns behind a view CREATE VIEW emp_public AS SELECT id, name FROM emp;
Speed up a query with a view It can't. A view stores no data, so its query runs on every read.
Update rows through a view Simple views allow it. Joins, grouping or DISTINCT usually prevent it.
Show each user only their department's rows CREATE POLICY p ON emp USING (dept_id = my_dept()); PostgreSQL syntax. Then turn RLS on with ENABLE ROW LEVEL SECURITY.
Say why RLS is safer than filters in the app The database adds the rule to every query, so a missed WHERE can't leak rows.
Tip Superusers bypass RLS, and so do table owners unless you FORCE ROW LEVEL SECURITY.
Trap A view depends on its tables. Changing or dropping a column can break it.
Procedures, functions and triggers Say why teams use stored procedures Less network traffic, logic in one place, and callers need no rights on its tables.
Use your own function inside a query dbo.GetAge(birth_date) A function returns a value. A procedure runs as its own statement.
Change data or control a transaction Use a procedure. A SQL Server function can do neither.
Log every change to a table An AFTER UPDATE trigger that writes to an audit table.
Check or adjust a row before it is saved A BEFORE trigger.
Tip An INSTEAD OF trigger replaces the change, mainly on views. MySQL has only BEFORE and AFTER.
Trap Procedure and trigger code ties you to one engine and hides logic from anyone reading the app.
Stop SQL injection Pass user input into a query safely SELECT * FROM users WHERE name = ? A parameterized query. The value is never parsed as SQL.
Spot a login bypass ' OR '1'='1 closes the string early and makes the test always true.
Let users choose the sort column Check it against an allowlist. A placeholder can't stand for a column name.
Limit the damage of an attack GRANT SELECT ON orders TO app_user; Least privilege. The app's account gets only what it needs.
Trap Escaping special characters is a last resort that OWASP strongly discourages. Use parameters.
Nothing matches. Try a shorter word, or clear the search.
Practice 44 interview questions Full answers, from beginner to expert.
Found this useful? Pass it on.
Share on LinkedIn
Share on X
Copy link
This sheet stays free. With Pro , you can:
Every answer on this sheet. Read the full answer to all 44 questions it links, not just the 6 free ones.My sheet. The cards you star and the ones you miss in Quiz me, from every sheet, on one page to quiz and print.PDFs. Download this sheet and My sheet, laid out for A4, with every question a link to its answer.The rest of Pro. Every answer in the question library, and 1,000 AI credits a month.
