Sefism SQL and Databases Cheat Sheet

What this cheat sheet is

The SQL and databases cheat sheet is the companion to the SQL and databases roadmap, for students and early-career engineers learning SQL on a real database. It runs from the first SELECT to schema design, transactions, indexes and using SQL safely from code. Each concept is revised in a line or two, with the syntax for your database and the classic mistake to spot before you make it.

Who it is for

Students taking a databases course who want the syntax and the reasoning on one page; developers who use an ORM every day and want to know what it is doing underneath; and anyone following the roadmap who needs a quick check mid-exercise. It assumes you have a database installed and a sample database loaded, which is exactly where chapter 1 starts. It is not a replacement for writing queries: every entry is short on purpose, so you go back to your own SQL file and try it.

Editions

There are three editions, matching the roadmap: PostgreSQL, MySQL and SQL Server. Most concepts are shared, and the dialect details change with the edition. The setup chapter names your GUI (pgAdmin, MySQL Workbench, or SSMS and the VS Code MSSQL extension), and the indexes chapter has keyset pagination written for PostgreSQL and MySQL or for T-SQL. Each edition has 77 concepts across nine chapters, plus a glossary of shorter terms.

Chapter by chapter

1. Relational model and your setup. The relational model, primary and foreign keys, DDL, DML and DCL, the command-line client, your GUI and the sample database.
2. Querying one table (9 concepts). SELECT and FROM, WHERE, NULL and three-valued logic, ORDER BY, limiting rows, DISTINCT, CASE, string and date functions, and the logical order of a query.
3. Joins and set operations (8 concepts). INNER JOIN, outer joins, self join, CROSS JOIN, join fan-out, UNION and UNION ALL, INTERSECT and EXCEPT, and the anti-join with NOT EXISTS.
4. Aggregation, subqueries and windows (11 concepts). Aggregate functions, GROUP BY, HAVING, conditional aggregation, subqueries, correlated subqueries and EXISTS, CTEs, recursive CTEs, window functions, ROW_NUMBER, RANK and DENSE_RANK, and LAG and LEAD.
5. Schema design (9 concepts). The ER model, many-to-many and junction tables, natural and surrogate keys, auto-generated ids, normalisation to 3NF, denormalisation, choosing data types, constraints and views.
6. Changing data safely (9 concepts). INSERT, UPDATE and DELETE, upserts, transactions, ACID, isolation levels, concurrency anomalies, row locks and deadlocks, and ON DELETE and ON UPDATE actions.
7. Indexes and query plans. B-tree, composite, covering and clustered indexes, EXPLAIN and execution plans, full scan versus index seek, index-friendly predicates, the cost of indexes, table statistics and keyset pagination.
8. SQL in applications and operations (9 concepts). SQL injection, parameterised queries, ORMs and query builders, the N+1 problem, schema migrations, connection pools, users, roles and least privilege, backups and restore tests, and where NoSQL fits.
9. NoSQL and MongoDB (5 concepts). Documents and collections, SQL to MongoDB, embedding and referencing, MongoDB indexes and transactions, and JSON columns.

Tips worth pinning

• Before any UPDATE or DELETE, run the same WHERE clause as a SELECT and check the row count.
• When a total looks too big after a join, check the grain: one row per what?
• Use NOT EXISTS rather than NOT IN when the subquery column can be NULL.
• Read the plan before adding an index, and read it again afterwards to confirm it is used.
• Every value that comes from a user goes in as a parameter, never pasted into the SQL string.
• A backup you have never restored is a hope, not a backup.

How to use it with the roadmap

• While working through a stage, keep the matching chapter open. The roadmap gives you the problems; the sheet gives you the syntax and the trap.
• Before a technical round or a SQL assessment, chapters 3 and 4 are the ones to reread: joins, grouping and window functions are where most SQL questions live.
• Before touching production data, reread chapter 6. Most data accidents are an UPDATE without the check-first routine, or a transaction left open.

How it works on Sefism

Everything on Sefism is free to learn from. The public page at SQL and databases cheat sheet shows every chapter title, with the first two chapters open in full. Later chapters show their title, a short introduction and how many concepts they hold.

Keeping the sheet uses credits, and it is yours for good after that. Adding the SQL and databases roadmap to your dashboard also opens this sheet, so if you plan to follow the roadmap you do not need to keep the sheet separately. New members get starting credits when they join, and the first roadmap is free. Create a free account to get started, and if a concept still does not make sense after reading it, ask Tauseef a question.

Related on Sefism

• SQLBolt for interactive practice
• Use The Index, Luke for chapter 7 in depth
• The MongoDB cheat sheet, if chapter 9 leaves you wanting more

Frequently asked questions

Can I use the PostgreSQL edition if I work on MySQL? You can, but the dialect details will trip you up. The sheet's own tip is to learn your own database's documentation, because dialect details decide whether a query works.

Does it cover window functions? Yes, in chapter 4: window functions in general, ranking with ROW_NUMBER, RANK and DENSE_RANK, and LAG and LEAD.

Is it only for beginners? It starts at the first SELECT, but chapters 6 to 8 (locking, plans, pools, permissions and backups) are what many working engineers never learnt properly.