SQL Mastery

This deck walks you through the foundational concepts of SQL and relational databases, starting from the basics of what SQL is and how a relational database management system works. You'll get comfortable with the building blocks of a database—tables, rows, and columns—before moving into the ideas that hold relational data together, like primary keys, foreign keys, composite keys, and candidate keys. It's a great way to build a solid mental framework before you start writing your own queries.

The second half of the deck shifts into database design principles, covering referential integrity, normalization, and the different normal forms. These topics are essential for designing well-structured databases and avoiding common pitfalls like data redundancy and update anomalies. If you're preparing for a technical interview or starting out in backend development, this kind of theory often comes up right alongside practical SQL skills.

Because many of these cards are definition-style questions, spaced repetition is your best friend here. Try reviewing a little each day rather than cramming, since the concepts build on one another—understanding keys, for instance, makes referential integrity much easier to grasp. When you hit a tricky topic like normal forms, try explaining the idea out loud in your own words before flipping the card; teaching it back to yourself is one of the strongest ways to lock it in.

SQL, or Structured Query Language, is the standardized language used to manage relational databases—databases that organize data into tables connected through defined relationships. A relational database is managed by software called an RDBMS, with popular implementations including PostgreSQL, MySQL, SQLite, Oracle, and SQL Server. Each of these systems speaks a common core of SQL but often extends it with proprietary features known as dialects.

A table is the fundamental unit of storage in a relational database. It consists of rows and columns: a row represents a single record, while a column represents an attribute or field shared by every row in the table. To make rows addressable and to link tables together, SQL uses keys. A primary key uniquely identifies each row and cannot be NULL, while a foreign key in one table references the primary key of another, establishing a relationship between them. More advanced key types include composite keys (formed from multiple columns acting together) and candidate keys (any column or set that could serve as the primary key).

The reliability of these relationships is governed by referential integrity, which ensures that every foreign key points to an existing primary key. Underpinning everything is the requirement that each cell in a well-designed table hold a single atomic value—introducing the first rule of normalization explored in the next chapter.

Designing tables thoughtfully is essential to avoid anomalies when data is inserted, updated, or deleted. Normalization is the systematic process of organizing tables to reduce redundancy and eliminate unwanted dependencies. It is expressed as a series of progressively stricter rules called normal forms. The first normal form (1NF) requires that every cell hold a single atomic value, rejecting repeating groups or arrays inside a column. The second normal form (2NF) builds on 1NF by prohibiting partial dependencies on a composite primary key—every non-key column must depend on the entire key, not just part of it.

The third normal form (3NF) further requires that no non-key column depend transitively on the key through another non-key column. Boyce-Codd Normal Form (BCNF) tightens 3NF so that every determinant is a candidate key. There are higher normal forms (4NF and 5NF) addressing multi-valued and join dependencies, though they are encountered less frequently in everyday work. Throughout this hierarchy, the guiding principle is the same: the more normalized a schema, the less duplication it carries, but the more joins it tends to require when querying.

Sometimes performance demands outweigh the purity of normalization. Denormalization deliberately reintroduces redundancy—usually by combining tables or duplicating columns—to make reads faster. The tradeoff is increased storage and the need for careful write logic to keep duplicated data consistent. Most production databases strike a balance, normalizing for core transactional tables and selectively denormalizing for analytical or reporting workloads.

The SELECT statement is the heart of SQL data retrieval. A query begins by specifying source tables with FROM, then filters rows using WHERE, groups rows with similar values using GROUP BY, and further filters those groups with HAVING. Finally, SELECT chooses which columns and expressions to output, ORDER BY sorts the result, and LIMIT and OFFSET restrict how many rows are returned. The actual logical order of execution is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT—understanding this order is key to reasoning about query results and debugging unexpected output.

Several operators refine what rows pass the filters. BETWEEN tests whether a value falls within an inclusive range, while IN checks membership against a list or subquery. LIKE performs pattern matching using % to mean any sequence of characters and _ to mean any single character; in PostgreSQL, ILIKE does the same case-insensitively, and REGEXP supports fuller regular-expression matching whose syntax varies by RDBMS. The DISTINCT keyword eliminates duplicate rows from the result set, and AS provides a temporary alias to rename columns or tables for readability.

Handling missing data is a recurring concern. NULL is not the same as zero or an empty string—it is a marker for an unknown or missing value. The standard tests IS NULL and IS NOT NULL are required because NULL fails any ordinary equality comparison. To substitute a fallback, COALESCE returns the first non-NULL value in a list of expressions, while NULLIF returns NULL when two expressions are equal and the first expression otherwise. Choosing the right NULL-handling function is essential to producing trustworthy aggregations and clean reports.

Real-world queries almost always span more than one table, which is where joins come in. A JOIN combines rows from two or more tables based on a related column, typically a primary key matched against a foreign key. An INNER JOIN returns only rows where the join condition matches in both tables, while a LEFT JOIN keeps every row from the left table, filling in NULLs where the right table has no match; a RIGHT JOIN does the mirror image, and a FULL OUTER JOIN returns rows whenever there is a match in either table. A CROSS JOIN produces the Cartesian product of both inputs—every combination of rows—and is useful for generating pairings but must be used carefully to avoid explosive result sizes.

Specialized joins cover trickier cases. A SELF JOIN treats a single table as if it were two, which is invaluable for hierarchical data such as employee-manager chains. A NATURAL JOIN automatically matches on columns that share the same name, but because it relies on naming conventions it is generally avoided in production code. The USING clause is a safer explicit alternative when both tables share identically named join columns. A common point of confusion is the difference between ON and WHERE: ON expresses the join condition and preserves unmatched rows in outer joins, while WHERE filters rows after the join has been performed; applying a filter with WHERE on the unmatched side of an outer join will silently remove those rows.

Sometimes the goal is to combine result sets rather than rows from related tables. UNION stacks the results of two SELECT queries and removes duplicates, while UNION ALL keeps every row and runs faster. INTERSECT returns rows that appear in both queries, and EXCEPT (called MINUS in some dialects) returns rows in the first query but not the second. Subqueries—queries nested inside another query—offer another way to combine data: a correlated subquery references the outer query and is evaluated once per outer row, while EXISTS and NOT EXISTS test whether a subquery produces any rows at all.

Aggregate functions summarize sets of rows. COUNT(*) tallies every row including NULLs, COUNT(column) tallies non-NULL values, and COUNT(DISTINCT column) counts unique non-NULL values. SUM totals a numeric column, AVG computes its mean, and MIN and MAX return the smallest and largest values. When used without GROUP BY, aggregates apply to the entire result set; with GROUP BY, they produce one summary row per group. Aggregates also appear combined with set functions like GROUP_CONCAT (MySQL), STRING_AGG (PostgreSQL), and LISTAGG (Oracle), which concatenate string values across rows into a single delimited string.

Window functions perform calculations across rows related to the current row without collapsing them into summary groups. They are written with an OVER() clause that defines the partition—the set of rows the function considers—and optionally an ordering and a frame clause. ROW_NUMBER assigns a unique sequential integer to each row in the partition, RANK assigns the same rank to ties while skipping subsequent numbers, and DENSE_RANK assigns the same rank to ties without skipping. NTILE(n) buckets rows into n approximately equal groups, useful for percentile-style reporting.

Other powerful window functions navigate the partition directly. LAG returns a value from a previous row and LEAD from a following row, making it easy to compute differences from one period to the next, while FIRST_VALUE and LAST_VALUE fetch the boundary values of the window. The frame clause, written as ROWS BETWEEN or RANGE BETWEEN, controls exactly which rows within the partition contribute to the calculation—ROWS uses physical positions while RANGE uses logical value ranges. The distinction between GROUP BY and PARTITION BY is fundamental: GROUP BY produces one row per group, while PARTITION BY performs the calculation within rows that remain individually visible.

Beyond reading data, SQL defines ways to create, modify, and remove both rows and the structures that hold them. The CRUD operations—Create, Read, Update, Delete—correspond to INSERT, SELECT, UPDATE, and DELETE. INSERT INTO ... SELECT copies results from a query into a target table. UPDATE modifies existing rows matched by a condition, while DELETE removes them. The RETURNING clause available in PostgreSQL gives back the affected rows so an application can see what changed without an extra round trip.

Sometimes a write needs to insert or update depending on whether a row already exists—this is the role of UPSERT. PostgreSQL implements it via ON CONFLICT DO UPDATE, MySQL via ON DUPLICATE KEY UPDATE, and the SQL standard via MERGE, which can also perform conditional deletes based on a join condition. Schema changes are managed with CREATE TABLE (defining columns and constraints), ALTER TABLE (modifying structure), TRUNCATE TABLE (emptying all rows quickly without logging individual deletions), and DROP TABLE (removing the table entirely). DELETE removes specific rows slowly and transactionally, TRUNCATE empties the table fast and may reset identity counters, and DROP destroys the table and its data completely.

Constraints enforce business rules at the database level. NOT NULL disallows missing values, UNIQUE enforces distinctness (allowing NULLs unlike a primary key), CHECK evaluates a custom boolean expression such as price > 0, and DEFAULT supplies a fallback when no value is provided. SQL offers a rich type system: variable-length text types like TEXT or VARCHAR contrast with the fixed-length CHAR; integer sizes range from SMALLINT through BIGINT; DECIMAL and NUMERIC store exact fixed-precision numbers while FLOAT and DOUBLE are approximate; DATE, TIME, and TIMESTAMP handle temporal values, with TIMESTAMP WITH TIME ZONE storing moments converted to UTC. Many modern RDBMS also support JSON columns, UUID identifiers for distributed systems, ENUM types for restricted value sets, and specialized features like PostgreSQL's JSONB and range types.

Beyond plain queries, SQL offers ways to encapsulate logic and reshape results. A Common Table Expression (CTE), introduced with WITH, names a subquery so that the main query can reference it like a table; recursive CTEs reference themselves and are ideal for traversing hierarchical data such as org charts or comment threads. Temporary tables exist only for the lifetime of a session or transaction, providing a scratch space without polluting the schema. Views are virtual tables defined by a stored query—convenient for security, reuse, and abstraction—while materialized views store the result physically and refresh it periodically, trading freshness for performance.

Programmability extends to stored procedures (reusable sets of SQL statements), SQL functions (routines that return a value), and triggers (code that runs automatically on data events such as inserts or updates). These let developers push business logic into the database, though doing so requires care to keep logic portable and testable. Some databases, such as PostgreSQL with PL/pgSQL and Oracle with PL/SQL, offer rich procedural languages, while T-SQL extends SQL Server with similar capabilities.

Several specialized operations reshape data. PIVOT rotates rows into columns—useful for cross-tab reports—and UNPIVOT does the reverse. Modern systems also offer native JSON column types and functions, enabling hybrid document-relational designs, as well as full-text search capabilities. PostgreSQL exemplifies this with tsvector and tsquery types and specialized GIN and GiST indexes that make text and JSONB searches fast. These advanced features let a single SQL engine cover use cases that would otherwise require separate systems.

Indexes are the most important tool for query performance. A B-tree index—the most common kind—supports equality and range lookups, a hash index supports only equality, a unique index enforces distinctness, and a composite index spans multiple columns. A clustered index determines the physical order of rows in a table (one per table), while non-clustered indexes are separate structures that point back to rows. A covering index contains every column a query needs, enabling an index-only scan that never touches the underlying table. Higher index selectivity (a greater fraction of distinct values) generally makes an index more useful, but every index slows writes and consumes storage, so they must be added deliberately.

To understand why a query is slow, EXPLAIN shows the planned execution steps and EXPLAIN ANALYZE runs the query and reports actual timings. The query optimizer—the component that picks the plan—can choose between a sequential scan (reading every row, fine for small tables) and an index scan, and between join algorithms such as nested loop (good when one side is small), sort-merge (good when both inputs are pre-sorted), and hash join (good when one side fits in memory). On large databases, periodic maintenance like rebuilding fragmented indexes, vacuuming dead tuples in PostgreSQL via autovacuum, and updating statistics keeps the optimizer making good choices.

Transactions package a unit of work with the ACID guarantees: atomicity (all or nothing), consistency (invariants preserved), isolation (concurrent transactions don't interfere), and durability (commits survive crashes). The statements BEGIN, COMMIT, and ROLLBACK control the lifecycle, with SAVEPOINT allowing partial rollback within a transaction. SQL defines four isolation levels—READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE—each preventing a different set of anomalies: dirty reads, non-repeatable reads, and phantom reads. Concurrency control ranges from pessimistic row-level and table-level locks (including SELECT FOR UPDATE) to MVCC, where readers don't block writers; deadlocks arise when transactions lock each other in a cycle and must be detected and broken.

The operational side of a database is just as important as its querying model. OLTP systems optimize for many small transactions, while OLAP systems support complex analytical queries on large data through column-oriented storage, data warehouses organized into star or snowflake schemas of fact and dimension tables, and even data lakes for raw storage. Scaling out uses partitioning (splitting a table into smaller pieces), sharding (horizontal partitioning across databases), and replication with read replicas for distributing read traffic, where the primary handles writes and standbys stay read-only. Backups come in two flavors—logical SQL dumps and physical byte-level copies—and combined with the Write-Ahead Log enable point-in-time recovery. Schema changes are managed via migration tools such as Flyway, Liquibase, or Django migrations, ideally without downtime using patterns like the strangler approach. Finally, application-facing concerns include pagination—cursor or keyset pagination vastly outperforms OFFSET on large tables—ORM frameworks like SQLAlchemy, Hibernate, and Sequelize (and the N+1 problem they can introduce through lazy loading), and security: parameterized queries and prepared statements bind user input separately from SQL text, preventing SQL injection attacks.