DBMS and SQL interview questions, with answers
Module 7: Creating tables
Why should money not be stored in FLOAT?
FLOAT stores numbers in binary, and most decimal fractions (like 0.1) have no exact binary form, so sums drift: 0.1 + 0.2 gives 0.30000000000000004. Use NUMERIC(p,s)/DECIMAL, which stores decimal digits exactly, or store cents as a BIGINT.
Lesson: CREATE TABLE and data types
What is the difference between CHAR, VARCHAR and TEXT?
CHAR(n) always holds exactly n characters and pads shorter values with spaces. VARCHAR(n) holds up to n characters with no padding. In Postgres TEXT is variable length with no limit and performs the same as VARCHAR; the limit in VARCHAR(n) is just a rule. In MySQL, TEXT is capped at 64 KB.
Lesson: CREATE TABLE and data types
When would you choose BIGINT over INT?
When values can pass about 2.1 billion: row ids in a large or fast-growing table, transaction ids, counters in cents. Running out of INT ids in production means an urgent and slow migration, so busy tables usually start with BIGINT.
Lesson: CREATE TABLE and data types
What is the difference between a primary key and a unique key?
A table has one primary key, and it can never be NULL; it is the row's identity, which foreign keys reference. A table can have many UNIQUE constraints, and they allow NULLs (several of them in Postgres). Both create a unique index behind the scenes.
Lesson: Constraints: PRIMARY KEY, NOT NULL, UNIQUE, CHECK, DEFAULT
What are candidate, primary, alternate and composite keys?
A candidate key is any minimal set of columns that uniquely identifies a row. The one you choose as the main identity is the primary key; the other candidates are alternate keys (declare them UNIQUE). A composite key is a key made of more than one column, like (show_id, seat_no).
Lesson: Constraints: PRIMARY KEY, NOT NULL, UNIQUE, CHECK, DEFAULT
Does a CHECK constraint stop NULL values?
No. CHECK rejects a row only when the condition is false; with NULL it is unknown, so the row passes. Add NOT NULL when the value is required: amount NUMERIC NOT NULL CHECK (amount > 0).
Lesson: Constraints: PRIMARY KEY, NOT NULL, UNIQUE, CHECK, DEFAULT
Why enforce rules in the database when the app already validates input?
Many apps, scripts and people write to the same database, and any of them can have a bug or skip the check. Constraints are enforced on every write, can't be bypassed, and hold even under concurrent writes, where an app's "check then insert" can race.
Lesson: Constraints: PRIMARY KEY, NOT NULL, UNIQUE, CHECK, DEFAULT
What is a foreign key and what is referential integrity?
A foreign key is a column (or set of columns) whose values must exist in another table's primary key or unique column. Referential integrity is the guarantee that results: no child row points at a parent that does not exist. The database checks it on every insert, update and delete.
Lesson: Foreign keys and ON DELETE
Explain ON DELETE CASCADE, SET NULL and RESTRICT.
When a parent row is deleted: CASCADE deletes the child rows too, SET NULL keeps them and sets the FK column to NULL, and RESTRICT (or the default NO ACTION) refuses the delete while children exist. Example: order_items CASCADE from orders; deliveries SET NULL when a courier leaves; orders RESTRICT a seller from being deleted.
Lesson: Foreign keys and ON DELETE
Can a foreign key be NULL? Can it reference a column that is not the primary key?
Yes to both. A NULL FK skips the check (add NOT NULL if a parent is required). It can reference any column with a PRIMARY KEY or UNIQUE constraint, such as email.
Lesson: Foreign keys and ON DELETE
What is a self-referencing foreign key?
A foreign key that points at the same table, like employees.manager_id REFERENCES employees(emp_id) or customers.referred_by REFERENCES customers(customer_id). The top of the tree (the CEO) has NULL.
Lesson: Foreign keys and ON DELETE
What is the difference between DELETE, TRUNCATE and DROP?
DELETE is DML: it removes the rows matching a WHERE, one by one, and fires row triggers. TRUNCATE is DDL: it empties the whole table at once, much faster, and keeps the structure. DROP removes the table itself, structure and all. In Postgres all three are transactional; in MySQL/Oracle, TRUNCATE and DROP commit immediately.
Lesson: ALTER, DROP, TRUNCATE, identity columns and sequences
SERIAL vs IDENTITY in Postgres?
Both number rows from a sequence. SERIAL is an old Postgres shortcut that creates a sequence and a default. GENERATED ALWAYS AS IDENTITY is the SQL standard, ties the sequence to the column properly, and with ALWAYS blocks hand-typed ids. Prefer identity in new tables.
Lesson: ALTER, DROP, TRUNCATE, identity columns and sequences
Why do auto-increment ids have gaps?
A sequence hands out numbers outside the transaction so that concurrent inserts never wait for each other. If an insert fails or rolls back, its number is used up. Deletes also leave holes. So ids are unique, not gap-free.
Lesson: ALTER, DROP, TRUNCATE, identity columns and sequences
How do you add a NOT NULL column to a table that already has rows?
Give it a DEFAULT in the same statement: ALTER TABLE users ADD COLUMN plan VARCHAR(10) NOT NULL DEFAULT 'free'. Without a default the existing rows would be NULL and the ALTER fails. In modern Postgres a constant default is added instantly, without rewriting the table.
Lesson: ALTER, DROP, TRUNCATE, identity columns and sequences
Module 8: Changing data
How do you copy data from one table to another?
Into an existing table: INSERT INTO target (a, b) SELECT a, b FROM source WHERE …. To create a new table from a query: CREATE TABLE target AS SELECT … (SQL Server/MySQL also have SELECT … INTO). Columns are matched by position, so list them in the same order.
Lesson: INSERT: one row, many rows, from a SELECT, with RETURNING
How do you get the id of a row you just inserted?
In Postgres, INSERT … RETURNING id returns it from the same statement. In MySQL, call LAST_INSERT_ID() after the insert; in SQL Server, OUTPUT inserted.id or SCOPE_IDENTITY(). Never use SELECT MAX(id): another session may have inserted in between.
Lesson: INSERT: one row, many rows, from a SELECT, with RETURNING
What is the fastest way to insert many rows?
One multi-row INSERT (or INSERT … SELECT) instead of many single-row ones, inside one transaction, so there is one round trip and one commit. For very large loads, Postgres COPY (MySQL LOAD DATA INFILE) is faster still.
Lesson: INSERT: one row, many rows, from a SELECT, with RETURNING
What is the difference between DELETE and TRUNCATE?
DELETE is DML: it removes rows one by one, takes a WHERE, fires row triggers and can RETURN the rows. TRUNCATE is DDL: it empties the whole table at once, much faster, with no WHERE, and can reset identity counters. In Postgres both can be rolled back in a transaction.
How do you update a table using values from another table?
In Postgres: UPDATE orders o SET shipping_fee = 0 FROM customers c WHERE c.customer_id = o.customer_id AND c.is_premium;. In MySQL: UPDATE orders o JOIN customers c ON … SET …. A correlated subquery in SET (SET x = (SELECT … WHERE …)) works everywhere.
How would you safely run an UPDATE or DELETE on production data?
Run the same WHERE as a SELECT first and check the rows and count. Then BEGIN;, run the change with RETURNING or check the affected count, and COMMIT; only if it matches (else ROLLBACK). Take a backup or log for big changes, and batch huge deletes so locks stay short.
Why can DELETE … WHERE id NOT IN (subquery) delete nothing?
If the subquery returns even one NULL, x NOT IN (…, NULL) is never true, so no rows match. Filter NULLs out in the subquery, or use NOT EXISTS, which handles NULLs correctly.
How do you insert a row only if it does not already exist?
Use INSERT … ON CONFLICT (key) DO NOTHING in Postgres (MySQL: INSERT IGNORE), backed by a PRIMARY KEY or UNIQUE constraint. SELECT-then-INSERT is a race: two sessions can both see "not there" and both insert.
Lesson: Upsert with ON CONFLICT, and MERGE
What is an upsert, and how is it written in Postgres and MySQL?
Insert the row, or update it if the key already exists, in one atomic statement. Postgres: INSERT … ON CONFLICT (id) DO UPDATE SET qty = t.qty + EXCLUDED.qty. MySQL: INSERT … ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty).
Lesson: Upsert with ON CONFLICT, and MERGE
ON CONFLICT vs MERGE?
ON CONFLICT is Postgres's insert-or-update for rows you are inserting, keyed on a unique constraint, and is safe under concurrency. MERGE is standard SQL that joins a whole source to a target, with WHEN MATCHED (update or delete) and WHEN NOT MATCHED (insert) branches; it needs no unique constraint but can still hit duplicate errors under concurrent inserts.
Lesson: Upsert with ON CONFLICT, and MERGE
Why is REPLACE INTO in MySQL dangerous?
On a duplicate key it deletes the old row and inserts a new one. Columns you did not supply are reset to defaults, delete triggers and ON DELETE CASCADE fire (removing child rows), and auto-increment ids can change. Prefer ON DUPLICATE KEY UPDATE.
Lesson: Upsert with ON CONFLICT, and MERGE
Module 9: ER modelling
What is a weak entity? Give an example.
An entity that cannot be identified by its own attributes and depends on an owner entity, like a room in a hotel or a dependent of an employee. It has a partial key (room_no) that is unique only within one owner. Its table's primary key is the owner's key plus the partial key, with a foreign key to the owner and usually ON DELETE CASCADE.
Lesson: Entities, attributes and relationships
How do you store a multivalued attribute such as phone numbers?
In a separate table with the owner's key and the value, for example student_phones(student_id, phone) with PRIMARY KEY (student_id, phone). Never as a comma-separated string, because you can't search, index or constrain one value inside it.
Lesson: Entities, attributes and relationships
Should you store a derived attribute like age or order total?
Usually not: compute it from the base data (AGE(dob), SUM(qty * price)) so it can't go stale. Store it only as a deliberate optimisation, such as a cached total on a hot read path, and keep it in sync.
Lesson: Entities, attributes and relationships
How do you model a many-to-many relationship in a relational database?
With a junction (bridge) table that holds the two foreign keys, for example enrollments(student_id, course_id), with the pair as the primary key. Attributes of the relationship itself, like marks or quantity, go in that table.
Lesson: Cardinality and participation
In a one-to-many relationship, which table gets the foreign key?
The "many" side. Each employee row has one dept_id, so the FK goes in employees. Putting it on the "one" side would allow only one child per parent.
Lesson: Cardinality and participation
How do you enforce a one-to-one relationship?
A foreign key with a UNIQUE constraint on it (or make the FK also the primary key of the child table). Add NOT NULL if participation on that side is total.
Lesson: Cardinality and participation
Walk me through converting an ER diagram into tables.
Each strong entity becomes a table with its key. Weak entities get owner key + partial key. 1:N becomes an FK on the many side, 1:1 an FK with UNIQUE, and M:N a junction table keyed by both FKs. Multivalued attributes get their own table, composite ones are split into columns, and total participation becomes NOT NULL.
Lesson: From ER diagram to tables
Design the tables for an online store.
customers, addresses(customer_id, line_no) as a weak entity, sellers, products(seller_id FK), couriers, orders(customer_id, seller_id, courier_id FKs, status) and the junction order_items(order_id, product_id, qty) keyed by the pair. A self-FK customers.referred_by handles referrals. Mention that order_items often also stores the price at order time, because product prices change.
Lesson: From ER diagram to tables
What is a recursive relationship and how is it stored?
An entity related to itself, like an employee managed by another employee or a customer referred by another customer. It is a foreign key pointing at the same table (manager_id REFERENCES employees(emp_id)), read with a self-join or a recursive CTE.
Lesson: From ER diagram to tables
Module 10: Normalization
What is normalization and why do we do it?
Normalization is splitting tables so that each fact is stored exactly once, guided by functional dependencies and the normal forms (1NF, 2NF, 3NF, BCNF). It removes redundancy and with it the update, insert and delete anomalies, so the data cannot contradict itself.
Lesson: Redundancy and the three anomalies
Explain update, insert and delete anomalies with an example.
In a table of (student, course, dept, head): changing a head needs every row of that department updated, or copies disagree (update). A new department with no students can't be added because the key needs a student (insert). Deleting a department's last student also deletes who its head is (delete).
Lesson: Redundancy and the three anomalies
Is redundancy always bad?
Uncontrolled redundancy is, because copies drift apart. Controlled redundancy (denormalisation, caches, materialised views, read-optimised reporting tables) is a deliberate trade for faster reads, as long as you have a plan to keep the copies in sync.
Lesson: Redundancy and the three anomalies
What is a functional dependency?
X → Y means any two rows with the same X value must have the same Y value, for example emp_id → salary. FDs come from business rules, and they are what normalization uses to decide how to split tables.
Lesson: Functional dependencies, closure and keys
Superkey vs candidate key vs primary key?
A superkey is any set of columns that uniquely identifies a row. A candidate key is a minimal superkey: no column can be removed. The primary key is the candidate key you choose; the others usually get UNIQUE constraints.
Lesson: Functional dependencies, closure and keys
How do you find the candidate keys from a set of FDs?
Attributes that appear on no right-hand side must be in every key, so start with them and compute their closure. If it is not everything, try adding one attribute at a time (then two), keep each set whose closure is all attributes, and drop any that contain a smaller key.
Lesson: Functional dependencies, closure and keys
How would you check in SQL whether a column determines another?
SELECT x FROM t GROUP BY x HAVING COUNT(DISTINCT y) > 1; lists every x with more than one y. An empty result means the data does not violate x → y (it doesn't prove the rule).
Lesson: Functional dependencies, closure and keys
Explain 1NF, 2NF and 3NF with an example.
1NF: one atomic value per cell (no phone lists). 2NF: no column depends on part of a composite key; in (student_id, course_id, student_name, marks), student_name depends only on student_id, so it moves to students. 3NF: no non-key column depends on another non-key column; dept → head moves to departments.
What is the difference between 2NF and 3NF?
2NF removes partial dependencies (a non-prime attribute depending on part of a candidate key), which only exist with composite keys. 3NF also removes transitive dependencies (a non-prime attribute depending on another non-key attribute).
Here is a table: (order_id, product_id, product_name, customer_id, customer_city, qty). Normalise it.
Key: (order_id, product_id). product_id → product_name is partial, so products(product_id, product_name). order_id → customer_id is partial too, so orders(order_id, customer_id). customer_id → customer_city is transitive, so customers(customer_id, customer_city). What is left is order_items(order_id, product_id, qty), with foreign keys to orders and products.
What is the difference between 3NF and BCNF?
BCNF requires the left side of every non-trivial FD to be a superkey. 3NF also allows X → A when A is a prime attribute. So a table can be in 3NF but not BCNF only when it has overlapping candidate keys, like (student, course, teacher) with teacher → course.
Lesson: BCNF, lossless joins and when to denormalise
What is a lossless join decomposition?
Splitting R into R1 and R2 such that R1 ⋈ R2 gives exactly R, with no spurious rows. It is lossless if the common attributes are a superkey of R1 or of R2. In SQL you can check it: join back and EXCEPT the original in both directions; both should be empty.
Lesson: BCNF, lossless joins and when to denormalise
When would you denormalise?
When a read path is hot and the joins are measured to be the bottleneck: feeds, order history, dashboards, counters. You store copies (a cached total, a name snapshot, a materialised view) and keep them in sync with triggers, jobs or application code. Normalise first; denormalise specific paths with a reason.
Lesson: BCNF, lossless joins and when to denormalise
Why not always go to BCNF?
A BCNF decomposition is always lossless, but it may lose a functional dependency, which then can't be enforced by a key in one table. 3NF always allows a lossless, dependency-preserving design, so some teams stop at 3NF when BCNF would cost a dependency.
Lesson: BCNF, lossless joins and when to denormalise
Module 11: Window functions
What is the difference between GROUP BY and a window function?
GROUP BY collapses each group into one row, so you can only show grouped columns and aggregates. A window function keeps every row and adds a value computed over related rows, e.g. SUM(goals) OVER (PARTITION BY match_id) shows each appearance with its match total.
Lesson: OVER and PARTITION BY: windows vs GROUP BY
Can you use a window function in WHERE or HAVING?
No. Windows are computed after WHERE, GROUP BY and HAVING. Compute the window in a subquery or CTE and filter in the outer query: SELECT * FROM (SELECT ..., RANK() OVER (...) AS r FROM t) x WHERE r = 1. Some warehouses (Snowflake, BigQuery) offer QUALIFY for this.
Lesson: OVER and PARTITION BY: windows vs GROUP BY
What does OVER () with empty brackets mean?
The window is every row of the result (after WHERE). COUNT(*) OVER () puts the total row count on each row, handy for percent-of-total: 100.0 * amount / SUM(amount) OVER ().
Lesson: OVER and PARTITION BY: windows vs GROUP BY
Explain ROW_NUMBER vs RANK vs DENSE_RANK.
All number rows in the window's ORDER BY. On ties: ROW_NUMBER still gives 1, 2, 3; RANK gives 1, 1, 3 (same number, then skips); DENSE_RANK gives 1, 1, 2 (same number, no gap). Pick ROW_NUMBER for exactly one row, RANK for leaderboards, DENSE_RANK for Nth distinct value.
Lesson: ROW_NUMBER, RANK, DENSE_RANK and top-N per group
Find the Nth highest salary.
Rank distinct salaries with DENSE_RANK and filter: SELECT DISTINCT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) r FROM employees) t WHERE r = N;. It handles ties at the top, unlike LIMIT 1 OFFSET N-1 on non-distinct rows.
Lesson: ROW_NUMBER, RANK, DENSE_RANK and top-N per group
Get the top 3 earners in every department.
Rank inside each department and filter outside: SELECT * FROM (SELECT *, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) r FROM employees) t WHERE r <= 3;. Say whether ties are kept (RANK/DENSE_RANK) or not (ROW_NUMBER).
Lesson: ROW_NUMBER, RANK, DENSE_RANK and top-N per group
Delete duplicate rows but keep one copy.
Number the copies with ROW_NUMBER() OVER (PARTITION BY the duplicate columns ORDER BY id) and delete where it is above 1: DELETE FROM t WHERE id IN (SELECT id FROM (SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) rn FROM t) x WHERE rn > 1);.
Lesson: ROW_NUMBER, RANK, DENSE_RANK and top-N per group
How do you calculate month-over-month growth in SQL?
Aggregate to one row per month, then compare with LAG: SELECT month, revenue, ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month), 1) AS growth_pct FROM monthly;. The first month is NULL; guard a zero previous month with NULLIF.
Lesson: LAG, LEAD and FIRST_VALUE: match-to-match change
LAG vs a self join: why prefer LAG?
A self join on id = prev.id + 1 assumes ids have no gaps and costs another scan and join. LAG follows the real order (dates, scores) inside each partition, skips gaps naturally and reads the table once.
Lesson: LAG, LEAD and FIRST_VALUE: match-to-match change
What do LAG and LEAD return at the edges of a partition?
NULL, unless you pass a default as the third argument: LAG(goals, 1, 0). Say why you chose NULL or a default, since a fake 0 can look like real data.
Lesson: LAG, LEAD and FIRST_VALUE: match-to-match change
How do you compute a running total?
SUM(amount) OVER (PARTITION BY account ORDER BY txn_at, txn_id). ORDER BY makes it cumulative; PARTITION BY restarts it per account. Add a unique tie-breaker (or a ROWS frame) so rows with the same timestamp do not jump together.
Lesson: Running totals and moving averages: frames
What is the difference between ROWS and RANGE frames?
ROWS counts physical rows: "2 PRECEDING" is exactly two rows back. RANGE works on the ORDER BY value: all rows with the same value (peers) are in or out together. The default with ORDER BY is RANGE UNBOUNDED PRECEDING to CURRENT ROW, which is why ties share one running total.
Lesson: Running totals and moving averages: frames
Why does LAST_VALUE return the current row?
Because the default frame ends at the current row. Use LAST_VALUE(x) OVER (PARTITION BY g ORDER BY y ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), or simply FIRST_VALUE(x) OVER (PARTITION BY g ORDER BY y DESC).
Lesson: Running totals and moving averages: frames
Write a 7-day moving average of daily orders.
Aggregate to one row per day, then AVG(orders) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). If some days have no orders, first build a full calendar (generate_series) and LEFT JOIN, or the "7 rows" will span more than 7 days.
Lesson: Running totals and moving averages: frames
Module 12: CTEs and recursive queries
What is a CTE and why use one over a subquery?
A Common Table Expression is a named subquery defined with WITH at the top of a statement. It does the same job as a derived table but reads top to bottom, can be referenced several times in the statement, and can be recursive.
Lesson: WITH: multi-step queries you can read
Is a CTE slower than a subquery in Postgres?
Not since Postgres 12: a CTE referenced once is inlined like a subquery. A CTE referenced several times may be computed once. You can force the choice with AS MATERIALIZED or AS NOT MATERIALIZED. Before version 12, every CTE was an optimisation fence.
Lesson: WITH: multi-step queries you can read
CTE vs temp table vs view?
A CTE lives for one statement. A temp table lives for the session, stores rows and can be indexed, good for reusing a heavy intermediate result. A view is saved in the database and reused by every query.
Lesson: WITH: multi-step queries you can read
How does a recursive CTE work?
It has an anchor query (the starting rows) and a recursive query that references the CTE, joined by UNION ALL. Postgres runs the anchor, then runs the recursive part on the rows produced by the previous round, again and again, until a round produces no rows. The result is all rounds together.
Lesson: Recursive CTEs: org charts, paths and generated rows
Find all employees under a given manager, at any depth.
WITH RECURSIVE t AS (SELECT emp_id FROM employees WHERE manager_id = :id UNION ALL SELECT e.emp_id FROM employees e JOIN t ON e.manager_id = t.emp_id) SELECT * FROM t; Add a level + 1 column to know the depth.
Lesson: Recursive CTEs: org charts, paths and generated rows
How do you stop a recursive CTE from looping forever?
Make sure the recursive part eventually returns nothing: a WHERE on a counter or depth (level < 20), tracking visited ids in an array and skipping them, or Postgres 14's CYCLE clause. UNION (not ALL) also stops when rows repeat exactly.
Lesson: Recursive CTEs: org charts, paths and generated rows
How would you fill in missing dates in a report?
Generate every date (recursive CTE or generate_series), LEFT JOIN the data to it, and use COALESCE or COUNT(column) so empty days show 0.
Lesson: Recursive CTEs: org charts, paths and generated rows
What is the difference between ROLLUP, CUBE and GROUPING SETS?
GROUPING SETS lists exactly the groupings you want. ROLLUP (a, b, c) is the hierarchy (a,b,c), (a,b), (a), (): n+1 groupings for subtotals. CUBE (a, b, c) is every combination: 2ⁿ groupings. All run in one pass instead of a UNION ALL of several GROUP BYs.
Lesson: Reports: ROLLUP, STRING_AGG and pivots
How do you get a comma-separated list of values per group?
Postgres: STRING_AGG(name, ', ' ORDER BY name). MySQL: GROUP_CONCAT(name ORDER BY name SEPARATOR ', '). Always add the ORDER BY inside, or the list order is not guaranteed.
Lesson: Reports: ROLLUP, STRING_AGG and pivots
How do you pivot rows into columns?
Conditional aggregation: group by the row key and write one aggregate per column, e.g. SUM(CASE WHEN month = 1 THEN amount END) AS jan, or in Postgres SUM(amount) FILTER (WHERE month = 1). The columns must be known when you write the query.
Lesson: Reports: ROLLUP, STRING_AGG and pivots
How do you tell a ROLLUP total row from a real NULL?
Use GROUPING(col): it returns 1 when the column was rolled up (subtotal or total row) and 0 for a real group, even when that group's value is NULL.
Lesson: Reports: ROLLUP, STRING_AGG and pivots
What is a LATERAL join?
A join to a subquery that can reference columns of the tables before it in FROM. Postgres evaluates it for each left row, like a for-each loop. SELECT c.name, o.* FROM customers c JOIN LATERAL (SELECT * FROM orders WHERE customer_id = c.customer_id ORDER BY ordered_at DESC LIMIT 3) o ON true; gives the 3 latest orders per customer.
Lesson: LATERAL joins: a subquery per row
LATERAL vs ROW_NUMBER for top-N per group: which is faster?
It depends on the shape. With few groups, many rows per group and an index on (group, sort column), LATERAL with LIMIT reads only N index entries per group. When you need most rows anyway, or need ties kept with RANK, the window function over one scan is usually better. Say both and pick by the data.
Lesson: LATERAL joins: a subquery per row
How do you keep left rows when the LATERAL subquery returns nothing?
Use LEFT JOIN LATERAL (...) t ON true. A plain JOIN LATERAL (or a comma with LATERAL) drops those rows, just like an inner join.
Lesson: LATERAL joins: a subquery per row
What is CROSS APPLY?
SQL Server and Oracle's name for LATERAL. CROSS APPLY is JOIN LATERAL ... ON true, and OUTER APPLY is LEFT JOIN LATERAL ... ON true. MySQL supports LATERAL from 8.0.14.
Lesson: LATERAL joins: a subquery per row
Module 13: Transactions and concurrency
What is ACID?
Atomicity: all statements of a transaction happen or none do. Consistency: every constraint holds before and after. Isolation: concurrent transactions don't see each other's unfinished work. Durability: once committed, the change survives a crash (Postgres writes it to the WAL before COMMIT returns).
The app debits one account and crashes before crediting the other. What happens?
If both updates were inside one BEGIN … COMMIT, the transaction never committed, so on restart Postgres treats it as rolled back: the debit vanishes. If they ran as two autocommit statements, the debit is already permanent and money is lost. That is why transfers must be one transaction.
What is a savepoint?
A named bookmark inside a transaction. ROLLBACK TO SAVEPOINT name undoes only the work after it and leaves the transaction open, which is also how you recover from an error in the middle of a long transaction without losing everything.
What is a lost update and how do you prevent it?
Two transactions read the same value, each computes a new one, and the second write overwrites the first. Prevent it by making the database do the maths (SET stock = stock - 1), by guarding the write (WHERE booked_by IS NULL), by locking the row first (SELECT … FOR UPDATE), or with optimistic locking using a version column.
Lesson: Concurrency problems: lost updates and friends
Two drivers accept the same ride at the same moment. How do you make sure only one gets it?
Make the accept a guarded UPDATE: UPDATE trips SET driver = ?, status = 'accepted' WHERE trip_id = ? AND status = 'requested'. The row lock makes the second UPDATE wait, then it re-checks the WHERE and updates 0 rows. The app tells that driver the trip is taken. No extra lock or isolation level is needed.
Lesson: Concurrency problems: lost updates and friends
Difference between a non-repeatable read and a phantom read?
A non-repeatable read is the same row returning a different value the second time (someone updated it). A phantom is the same query returning a different set of rows (someone inserted or deleted a matching row).
Lesson: Concurrency problems: lost updates and friends
Can you get a dirty read in Postgres?
No. Postgres accepts READ UNCOMMITTED but runs it as READ COMMITTED, so a session only ever sees committed data (plus its own changes).
Lesson: Concurrency problems: lost updates and friends
Explain the isolation levels and what each prevents.
READ UNCOMMITTED allows dirty reads; READ COMMITTED stops them; REPEATABLE READ also stops non-repeatable reads; SERIALIZABLE also stops phantoms, so the result equals some one-at-a-time order. In Postgres, READ UNCOMMITTED acts as READ COMMITTED and REPEATABLE READ also prevents phantoms.
Lesson: Isolation levels and MVCC
What is MVCC?
Multi-version concurrency control: an update writes a new row version instead of overwriting. Each transaction reads the versions visible in its snapshot, so readers never block writers and writers never block readers. Old versions are removed later by VACUUM.
Lesson: Isolation levels and MVCC
What is write skew?
Two transactions read the same data, each decides based on it, and each updates a different row, so no row conflict is detected, yet together they break a rule (both on-call doctors go off duty). Snapshot isolation (Postgres REPEATABLE READ) allows it; SERIALIZABLE detects it and aborts one transaction. You can also prevent it by locking the rows you read with FOR UPDATE.
Lesson: Isolation levels and MVCC
What is SELECT … FOR UPDATE and when do you use it?
It reads rows and locks them like an UPDATE would, until the transaction ends. Use it for read-check-write logic (check balance, then debit; check seat free, then book) so nobody changes the rows between your check and your write. Use it inside BEGIN … COMMIT and keep the transaction short.
Lesson: Locks, FOR UPDATE and deadlocks
What is a deadlock and how do you prevent one?
Two or more transactions each hold a lock the other needs, so they wait in a cycle. Postgres detects it and aborts one. Prevent it by locking rows in a consistent order (e.g. by primary key), keeping transactions short, and retrying the victim.
Lesson: Locks, FOR UPDATE and deadlocks
Optimistic vs pessimistic locking?
Pessimistic locks rows up front (FOR UPDATE): best when clashes are frequent and transactions short. Optimistic takes no lock; it checks a version column at write time (WHERE id = ? AND version = ?) and retries on 0 rows: best when clashes are rare or the user holds the data for a long time, like an edit form.
Lesson: Locks, FOR UPDATE and deadlocks
Thousands of people try to book the last 50 seats on a flight. How do you allocate seats without double booking or a long queue?
Each booking runs SELECT … FROM seats WHERE flight_no = ? AND booked_by IS NULL ORDER BY seat LIMIT 1 FOR UPDATE SKIP LOCKED, books that seat and commits. SKIP LOCKED makes each session grab a different free seat without waiting on the others, and the row lock stops two sessions taking the same one. The same pattern builds a job queue.
Lesson: Locks, FOR UPDATE and deadlocks
Module 14: Indexes and query plans
What is an index and how does it speed up a query?
A separate sorted structure (in Postgres, a B+ tree) mapping column values to row addresses. Instead of scanning every row, the database descends the tree in a few page reads and fetches only the matching rows. The cost is extra disk space and slower writes.
Lesson: Why indexes, and the B+ tree idea
Why B+ trees and not hash tables or binary trees?
B+ trees have huge fan-out, so they are only 3-4 levels deep even for hundreds of millions of rows (few disk reads), and their sorted, linked leaves support ranges, ORDER BY and prefix searches. A hash index only does equality; a binary tree is far too deep on disk.
Lesson: Why indexes, and the B+ tree idea
Does a foreign key create an index?
In Postgres, no: only PRIMARY KEY and UNIQUE do. You usually add an index on the foreign key column yourself, because joins and lookups use it and deleting a parent row has to search it. MySQL InnoDB creates one automatically.
Lesson: Why indexes, and the B+ tree idea
How do you find out why a query is slow?
Run EXPLAIN ANALYZE on it. Look for a Seq Scan on a large table with a selective filter (missing index), estimated rows far from actual rows (stale stats: run ANALYZE), big sorts or hashes spilling to disk, and nested loops over many outer rows.
Lesson: Reading EXPLAIN: scans and joins
Index Scan vs Bitmap Index Scan vs Index Only Scan?
An Index Scan fetches table rows one by one in index order. A Bitmap scan first collects all matching row addresses, then reads each table page once in physical order: better for many scattered rows, and it can combine indexes with AND/OR. An Index Only Scan never touches the table because the index has every needed column.
Lesson: Reading EXPLAIN: scans and joins
Explain nested loop, hash and merge joins.
Nested loop: for each outer row, look up the inner side; great when the outer side is small and the inner is indexed. Hash join: build a hash table on the smaller input, probe with the larger; best for big unsorted equality joins. Merge join: walk two inputs sorted on the key; best when both are already sorted.
Lesson: Reading EXPLAIN: scans and joins
What is the leftmost-prefix rule?
A composite index on (a, b, c) is sorted by a, then b, then c, so it can seek on a, (a, b) or (a, b, c), but not on b or c alone, just as a phone book sorted by surname can't find all people named Maya.
Lesson: Composite and covering indexes
How do you pick the column order of a composite index?
Equality-filtered columns before range-filtered ones (a range stops further seeking), and the column most often queried alone goes first so the index also serves those queries. Selectivity matters less than these two rules.
Lesson: Composite and covering indexes
What is a covering index?
An index that contains every column a query needs, so the database answers from the index alone (an Index Only Scan) without reading the table. In Postgres you add non-key columns with INCLUDE; MySQL InnoDB secondary indexes always carry the primary key.
Lesson: Composite and covering indexes
What is selectivity?
The fraction of rows a condition matches. Low fraction (high selectivity, like an ID or phone) means an index is worth it; a condition that matches most of the table (like status = delivered) is cheaper as a sequential scan.
Lesson: Composite and covering indexes
I added an index but the query still does a full scan. Why?
Common reasons: the WHERE wraps the column in a function or cast (lower(email), date(created_at)); a LIKE starts with %; the condition matches a large share of rows so a scan is cheaper; the column isn't the leading column of a composite index; statistics are stale; or the table is small. EXPLAIN shows which.
Lesson: When an index can't help
What are partial and expression indexes?
A partial index covers only rows matching a WHERE (… WHERE status = 'pending'), so it is small and cheap and serves queries with that same condition. An expression index stores a computed value (lower(email)), so queries filtering on that expression can use it.
Lesson: When an index can't help
Why not index every column?
Each index slows every INSERT, many UPDATEs and DELETEs, takes disk and memory, and the planner won't use most of them. Index what frequent queries filter, join and sort on, prefer composite indexes that serve several queries, and drop unused ones.
Lesson: When an index can't help
What happens when you run a SQL query?
The parser checks syntax and builds a tree; the rewriter resolves names and expands views; the planner considers scan methods, join orders and join algorithms, costs each with table statistics, and keeps the cheapest; the executor runs that plan and returns rows. EXPLAIN shows the chosen plan.
Lesson: How a query actually runs: the planner and its costs
Why would the optimizer choose a bad plan?
Almost always because its row estimates are wrong: stale statistics after a big load, correlated columns it treats as independent, or a value it has never seen. Compare estimated and actual rows in EXPLAIN ANALYZE; run ANALYZE, and for correlated columns consider extended statistics (CREATE STATISTICS).
Lesson: How a query actually runs: the planner and its costs
What do the cost numbers in EXPLAIN mean?
Estimated work in planner units, not time: startup cost before the first row, then total cost. They are built from settings like seq_page_cost (1.0), random_page_cost (4.0) and cpu_tuple_cost (0.01) times estimated pages and rows. They are only useful for comparing plans of the same query.
Lesson: How a query actually runs: the planner and its costs
Hash index vs B+ tree index: when would you use each?
Use a B+ tree by default: it supports equality, ranges, prefix LIKE, ORDER BY, UNIQUE and multi-column keys in O(log n). Use a hash index only for pure equality lookups on long keys (tokens, URLs) where its smaller size matters; it can't do ranges, sorting or uniqueness.
Lesson: Hash vs B+ tree indexes: which one wins
Why do databases use B+ trees as the default index?
They are shallow (3-4 levels for huge tables), keep keys sorted so one structure serves equality, ranges and sorting, and stay balanced as rows are inserted and deleted. A hash index is only better for equality, and only slightly.
Lesson: Hash vs B+ tree indexes: which one wins
What are GIN, GiST and BRIN indexes?
GIN indexes the items inside a value (array elements, JSONB keys, words for full-text search). GiST handles geometric and range data, including nearest-neighbour searches. BRIN stores a min and max per block range, so it is tiny and works well on huge tables whose rows arrive in order, like time-stamped logs.
Lesson: Hash vs B+ tree indexes: which one wins
Module 15: Views, functions and security
What is the difference between a view and a materialised view?
A view stores only its query and runs it on every read, so it is always current but only as fast as the query. A materialised view stores the result rows, so reads are fast and can be indexed, but the data is stale until REFRESH MATERIALIZED VIEW. Use views to simplify and secure, materialised views for slow reports that may lag.
Lesson: Views and materialised views
Does a view make a query faster?
A plain view, no: Postgres expands the view into the outer query and plans the whole thing, so it costs the same as writing the query by hand. Only a materialised view (or an index on the base tables) changes the speed.
Lesson: Views and materialised views
Can you INSERT or UPDATE through a view?
Yes, if it is a simple view: one table, no GROUP BY, DISTINCT, aggregates, set operations or LIMIT. Postgres turns the change into a change on the base table. Add WITH CHECK OPTION so rows the view's WHERE would hide are rejected. Complex views need an INSTEAD OF trigger.
Lesson: Views and materialised views
What is the difference between a function and a stored procedure?
A function returns a value or a set of rows and is called inside SQL (SELECT vat(amount)); it cannot commit. A procedure is called with CALL, returns nothing (or INOUT values) and can COMMIT or ROLLBACK inside. Use functions for calculations and lookups, procedures for multi-step jobs.
Lesson: Functions and procedures in PL/pgSQL
Why put business logic in the database, and why not?
For: one rule shared by every app, fewer round trips (one CALL instead of five queries), and data checks that no client can skip. Against: logic is harder to version, test and debug than app code, it ties you to one database's dialect, and heavy logic on the database server is hard to scale out. Most teams keep integrity rules in the database and business workflows in the app.
Lesson: Functions and procedures in PL/pgSQL
How do you raise an error from a PL/pgSQL function?
RAISE EXCEPTION 'Account % is frozen', p_acc; stops the function and aborts the statement (and the transaction, unless it is caught with an EXCEPTION block). RAISE NOTICE only prints a message and carries on.
Lesson: Functions and procedures in PL/pgSQL
What is a trigger, and when would you use one?
A trigger is a function the database runs automatically when a row is inserted, updated or deleted. Good uses: audit logs, filling updated_at, keeping a summary table in sync, and rules that span tables. Avoid using them for core business flows, because the logic is hidden from anyone reading the application code.
Lesson: Triggers: audit logs, updated_at and rules
BEFORE vs AFTER, row vs statement triggers?
BEFORE triggers run before the row is written and can change NEW or reject the row; AFTER triggers run once it is written and are for side effects. A row-level trigger fires once per affected row with OLD/NEW; a statement-level trigger fires once per statement, even if it changes zero rows.
Lesson: Triggers: audit logs, updated_at and rules
Trigger or constraint for a rule?
Use a constraint whenever one can express the rule (CHECK, UNIQUE, FOREIGN KEY, NOT NULL): it is declarative, fast and visible in the schema. Use a trigger only when the rule needs other rows or tables, such as "no payments to frozen accounts".
Lesson: Triggers: audit logs, updated_at and rules
What is SQL injection and how do you prevent it?
It happens when user input is concatenated into a SQL string, so a quote in the input ends the string and the rest runs as SQL (' OR '1'='1). Prevent it with parameterised queries or prepared statements, where the query is parsed before the values arrive. Add least privilege so a hole can do little damage, and validate input types.
Lesson: Roles, GRANT/REVOKE and SQL injection
What is the difference between a user and a role in Postgres?
Postgres has only roles. A role with the LOGIN attribute acts as a user; a role that other roles are granted into acts as a group. CREATE USER maya is just CREATE ROLE maya LOGIN.
Lesson: Roles, GRANT/REVOKE and SQL injection
How would you let an analyst see totals but not personal data?
Create a view that exposes only the safe columns or aggregates, GRANT SELECT on the view, and do not grant the base table. The view reads the table with its owner's privileges. Column-level grants (GRANT SELECT (city) ON customers) and row-level security policies are other options.
Lesson: Roles, GRANT/REVOKE and SQL injection
What does WITH GRANT OPTION do?
GRANT SELECT ON orders TO lead WITH GRANT OPTION; lets lead grant SELECT on orders to others. Revoking it later with REVOKE ... CASCADE also removes what lead passed on.
Lesson: Roles, GRANT/REVOKE and SQL injection
Module 16: Inside the DBMS
How is SQL different from relational algebra?
Algebra works on sets: no duplicate rows and no order. SQL works on bags, so SELECT keeps duplicates unless you write DISTINCT, and it adds NULLs, aggregates, ORDER BY and LIMIT. UNION, INTERSECT and EXCEPT remove duplicates like algebra; their ALL versions keep them.
Lesson: Relational algebra and its SQL
What is a natural join and why is it risky?
It joins on all columns with the same name and keeps one copy of each. Adding an unrelated column with a shared name (like name or city) silently changes the join. Write JOIN ... ON or USING (col) instead.
Lesson: Relational algebra and its SQL
What does "predicate pushdown" mean?
The optimizer moves a filter (σ) as close to the table as it can, so it runs before joins and fewer rows flow through the plan. It is allowed because the algebra guarantees the result is the same. It is also why a filter on a joined table can use that table's index.
Lesson: Relational algebra and its SQL
Why do databases use B+ trees instead of binary search trees?
A binary tree node holds one key, so a million keys need about 20 levels, each a separate disk read. A B+ tree node is a whole page holding hundreds of keys, so the same million keys fit in 3 levels, and the tree stays balanced on every insert. Linked leaves also make range scans cheap.
Lesson: Storage: pages, a B+ tree that stays balanced, hashing
B tree vs B+ tree?
In a B+ tree every key with its row address lives in the leaves, internal nodes hold only signposts, and leaves are linked in order. In a B tree, internal nodes hold row addresses too, so each node fits fewer keys and range scans must walk up and down the tree. Databases use B+ trees.
Lesson: Storage: pages, a B+ tree that stays balanced, hashing
When would you choose a hash index?
When the only queries are exact matches on the key, like a session id lookup: a hash goes straight to one bucket. It can't help ranges, prefixes or ORDER BY. In Postgres, a btree handles equality almost as well, which is why it is the default.
Lesson: Storage: pages, a B+ tree that stays balanced, hashing
How do you test whether a schedule is conflict serializable?
Draw the precedence graph: one node per transaction and an edge Ti → Tj for each conflicting pair where Ti's operation comes first. If the graph has no cycle, the schedule is conflict serializable, and following the arrows gives an equivalent serial order.
Lesson: Serializability and two-phase locking
What is two-phase locking?
A transaction takes all its locks before releasing any (a growing phase, then a shrinking phase), which guarantees serializable schedules. Strict 2PL holds write locks until commit, so no one reads uncommitted data. It can still deadlock, so the database must detect and break deadlocks.
Lesson: Serializability and two-phase locking
How does Postgres provide SERIALIZABLE isolation?
Not with read locks. Each transaction reads from an MVCC snapshot, and Postgres tracks read/write dependencies between concurrent transactions (Serializable Snapshot Isolation). If a pattern could break serializability, it aborts one transaction with a serialization failure, and the application retries it.
Lesson: Serializability and two-phase locking
What is write-ahead logging?
Every change is first written to an append-only log, and a page may reach disk only after its log records have. A transaction is committed once its commit record is flushed. After a crash the log is replayed to redo committed work and undo uncommitted work, which gives atomicity and durability without writing every data page at commit.
Lesson: Logging and recovery: WAL, checkpoints, undo and redo
Why do databases take checkpoints?
To bound recovery time and log size. A checkpoint flushes dirty pages and records which transactions are active, so recovery can start from it instead of the start of the log, and older log files can be recycled. Postgres takes one every few minutes (checkpoint_timeout) or when enough WAL has been written.
Lesson: Logging and recovery: WAL, checkpoints, undo and redo
How do atomicity and durability map to undo and redo?
Atomicity is guaranteed by undo: uncommitted changes that reached disk are rolled back using old values from the log. Durability is guaranteed by redo: committed changes that were still in memory are reapplied using new values from the log.
Lesson: Logging and recovery: WAL, checkpoints, undo and redo
Module 17: Beyond one database
What is the difference between synchronous and asynchronous replication?
With synchronous replication, COMMIT waits until at least one replica has the change, so a failover loses no committed data, but writes are slower. With asynchronous replication, COMMIT returns at once and replicas trail behind, so writes are fast but the last few seconds can be lost if the primary dies.
Lesson: Replication and backups
Explain RPO and RTO.
RPO (Recovery Point Objective) is how much data, measured in time, you can afford to lose: the gap between the last safe copy and the failure. RTO (Recovery Time Objective) is how long the system may be down while you recover. Nightly backups give an RPO up to 24 hours; WAL archiving or sync replicas bring it near zero.
Lesson: Replication and backups
A user updates their profile and the page still shows the old value. Why, and how do you fix it?
The write went to the primary but the read went to an async replica that has not replayed it yet (replication lag). Fixes: read a user's own data from the primary for a short time after they write, route reads by session, or wait until the replica has reached the write's WAL position.
Lesson: Replication and backups
Is a read replica a backup?
No. A replica replays every change, including an accidental DELETE or DROP. It protects against hardware failure, not against bad queries. For that you need backups, ideally a base backup plus archived WAL so you can do point-in-time recovery.
Lesson: Replication and backups
What is the difference between partitioning and sharding?
Both split rows into pieces. Partitioning keeps the pieces on one database server, which routes rows and prunes partitions for you. Sharding puts pieces on different servers, so it scales writes and storage beyond one machine, but cross-shard joins and transactions become hard.
Lesson: Partitioning and sharding
How do you choose a shard key?
Pick a key with many distinct values that spreads load evenly and that most requests filter on, so one request touches one shard (for an online store, customer_id or seller_id). Avoid keys that create hot shards, like city or a timestamp where all new writes hit the newest shard.
Lesson: Partitioning and sharding
What is partition pruning?
When a query filters on the partition key, the planner skips partitions that cannot contain matching rows. WHERE ordered_at >= '2025-07-01' AND ordered_at < '2025-08-01' reads only the July partition. It does not help when the filter is on another column.
Lesson: Partitioning and sharding
What problem does consistent hashing solve?
With hash(key) % N, adding one server changes N, so almost every key moves. Consistent hashing places servers and keys on a ring; adding a server moves only the keys between it and its neighbour, about 1/N of the data.
Lesson: Partitioning and sharding
When would you choose NoSQL over a relational database?
When the access pattern is simple and known (lookups by key, append-only event streams), the scale is beyond what one server or a few replicas can handle, and you can live without joins or multi-row transactions. For money, inventory or anything with relationships and reports, start relational; add Redis or a document store for the specific job.
Lesson: SQL vs NoSQL, CAP and JSONB
Explain the CAP theorem.
In a distributed database, when a network partition splits the servers, you must choose consistency (reject requests that could see stale data) or availability (answer anyway, maybe with stale data). P is not optional in practice, so "pick 2 of 3" is misleading. A bank ledger chooses CP; a like counter chooses AP.
Lesson: SQL vs NoSQL, CAP and JSONB
What is the difference between JSON and JSONB in Postgres?
JSON stores the exact text and re-parses it on every read; JSONB stores a parsed binary form, drops duplicate keys and whitespace, and does not keep key order. JSONB is faster to query and supports GIN indexes and operators like @> and ?, so it is the usual choice.
Lesson: SQL vs NoSQL, CAP and JSONB
What is eventual consistency?
Replicas may disagree for a while after a write, but if no new writes come in, they all converge to the same value. AP systems like Cassandra or DynamoDB (by default) work this way; it is fine for feeds and counters, not for balances.
Lesson: SQL vs NoSQL, CAP and JSONB
Interview pack: SQL interview problems
Find the second highest salary. What edge cases do you check?
Ties at the top (use distinct salaries), fewer than two distinct salaries (return NULL, not an empty result), and NULL salaries. SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); handles all three; for the Nth, use DENSE_RANK() and WHERE r = N inside MAX(...).
RANK, DENSE_RANK or ROW_NUMBER for "Nth highest salary"?
DENSE_RANK. It gives equal salaries the same rank and does not skip numbers, so rank N is the Nth distinct salary. RANK skips after a tie (1, 1, 3), and ROW_NUMBER gives tied rows different numbers.
Why does `NOT IN` sometimes return nothing?
If the subquery returns a NULL, x NOT IN (a, b, NULL) means x <> a AND x <> b AND x <> NULL, and the last part is unknown, so the whole condition is never true. Use NOT EXISTS, or filter NULLs out of the subquery.
How do you get the top 3 salaries in each department?
Rank inside each department with a window, then filter outside: SELECT * FROM (SELECT e.*, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS r FROM employees e) t WHERE r <= 3;. Ask first whether ties count; that decides DENSE_RANK, RANK or ROW_NUMBER.
How do you compute each row's percent of the total?
Divide by a window total: 100.0 * amount / SUM(amount) OVER (), or per group with OVER (PARTITION BY …). Use 100.0 so the division is not integer division, and ROUND at the end.
Why might a running total give two rows the same value?
With ORDER BY in OVER and no frame, Postgres uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and rows with equal ORDER BY values are peers that share one total. Add a unique tie-breaker or use a ROWS frame.
How do you find users who logged in on 3 consecutive days?
Take distinct (user, date) pairs, compute login_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date), which is constant within a run of consecutive days, then GROUP BY user_id, that_key HAVING COUNT(*) >= 3. An alternative is LAG/LEAD: a day whose previous day is date - 1 and whose day before that is date - 2.
How do you compute day-1 retention?
Group users by signup date (the cohort). For each user, ask yes or no: EXISTS (SELECT 1 FROM logins l WHERE l.user_id = u.user_id AND l.login_date = u.signup_date + 1). Then COUNT(*) FILTER (WHERE …) * 100.0 / COUNT(*) per cohort. EXISTS avoids double-counting users with two logins.
How do you show days with zero sales in a daily report?
Generate the full calendar with generate_series(start, end, INTERVAL '1 day') (or a calendar table, or a recursive CTE in MySQL), LEFT JOIN the sales to it, and use COALESCE(amount, 0).
How would you compute month-over-month growth?
Aggregate to one row per month in a CTE, then 100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month). Mention that months with no data are missing unless you generate them, and guard against dividing by zero with NULLIF.
How do you delete duplicate rows but keep one?
Self-join and delete the copies with a higher id: DELETE FROM t a USING t b WHERE a.email = b.email AND a.id > b.id;. Or number rows with ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) and delete where it is above 1. Then add a UNIQUE constraint so it cannot happen again.
NOT IN, NOT EXISTS or LEFT JOIN … IS NULL?
NOT EXISTS is the safe default: it ignores NULLs and Postgres runs it as an anti-join. LEFT JOIN … WHERE right.pk IS NULL is equivalent. NOT IN returns nothing if the subquery yields a NULL, so avoid it on nullable columns.
Why did my LEFT JOIN behave like an INNER JOIN?
A WHERE condition on the right table (WHERE o.status = 'delivered') is false or unknown for the NULL rows the LEFT JOIN added, so they are removed. Move that condition into the ON clause.
How do you list every pair of rows that share something, each pair once?
Self-join the table and add a.id < b.id: SELECT a.name, b.name FROM customers a JOIN customers b ON b.city = a.city AND a.customer_id < b.customer_id;. With <> instead of <, every pair comes back twice, as (a, b) and (b, a); with neither, everyone is also paired with themselves.
How do you find a median in SQL?
In Postgres, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x). Without it, number the rows with ROW_NUMBER and COUNT them, then average the rows whose number is in (n + 1) / 2 and (n + 2) / 2 (integer division), which picks one middle row for odd n and two for even n.
How do you split clickstream events into sessions?
Use LAG to get each event's previous timestamp per user, flag a new session when the gap exceeds the timeout (or there is no previous event), then a running SUM(flag) OVER (PARTITION BY user ORDER BY ts) gives a session number. Group by user and session number for start, end and counts.
How do you pivot rows into columns?
Conditional aggregation: COUNT(*) FILTER (WHERE status = 'x') in Postgres, or SUM(CASE WHEN status = 'x' THEN 1 ELSE 0 END) anywhere. The column list is fixed in the query; a truly dynamic pivot needs the app (or dynamic SQL) to build it.
How do you swap every pair of adjacent seats?
Do not move the students; give each row a new id. CASE WHEN id % 2 = 1 AND id = (SELECT MAX(id) FROM seats) THEN id WHEN id % 2 = 1 THEN id + 1 ELSE id - 1 END, then ORDER BY the new id. The first branch keeps the last student in place when the number of seats is odd.
How do you write an upsert in Postgres and in MySQL?
Postgres: INSERT … ON CONFLICT (key) DO UPDATE SET col = EXCLUDED.col (or DO NOTHING). MySQL: INSERT … ON DUPLICATE KEY UPDATE col = VALUES(col), or with a row alias in newer versions. Both need a unique key or primary key to detect the clash; MySQL fires on any unique key.
Lesson: MySQL vs Postgres: what changes in the interview
What is the Postgres version of AUTO_INCREMENT?
An identity column, id INT GENERATED ALWAYS AS IDENTITY, or the older SERIAL. Both use a sequence. To get the new id back, Postgres has INSERT … RETURNING id; MySQL uses LAST_INSERT_ID().
Lesson: MySQL vs Postgres: what changes in the interview
Why does a GROUP BY query run in MySQL but fail in Postgres?
It selects a column that is neither grouped nor aggregated. Postgres refuses (unless you grouped by the table's primary key), while MySQL without ONLY_FULL_GROUP_BY picks any value from the group. Group by every shown column or aggregate it.
Lesson: MySQL vs Postgres: what changes in the interview
Design the tables for an online store.
customers (unique email), categories (with parent_id), products (category_id, price NUMERIC), orders (customer_id, status, placed_at), order_items (PK order_id + product_id, qty, unit_price), payments (order_id, amount, status), reviews (PK customer_id + product_id, rating). Then name the hot queries and index orders(customer_id, placed_at) and order_items(product_id).
Lesson: Design this schema: an online store
Is storing unit_price in order_items denormalisation?
No. It is a different fact: the price agreed at the time of sale. products.price is the current price. If you derived old orders from the current price, a price change would rewrite history.
Lesson: Design this schema: an online store
Should you store the order total on the orders table?
It is derived, so the normalised answer is no: compute it from the lines. Many real systems do store it for speed and for the invoice record, but then it must be written in the same transaction as the lines and never edited on its own. Say which you choose and why.
Lesson: Design this schema: an online store
How would you model the life of a ride?
One rides row with a status (requested, accepted, completed, cancelled) and a timestamp per step. driver_id and fare are NULL until they are known, and CHECK constraints stop invalid states, such as a completed ride with no driver. If every change must be kept, add a ride_events table.
Lesson: Design this schema: a ride-hailing app
Where would you store live driver locations?
Not in the relational trip tables. They change every few seconds and are only useful for a short time, so they go to an in-memory or time-series store with a geo index. The trip table stores the start and end points and the result.
Lesson: Design this schema: a ride-hailing app
Which indexes would you add for this app?
rides(rider_id, requested_at) for trip history, rides(driver_id, completed_at) for earnings, and a partial index on rides(city_id, requested_at) WHERE status = 'requested' for matching open requests. Each one is tied to a query the app runs constantly.
Lesson: Design this schema: a ride-hailing app
How do you prevent two people booking the same seat?
Enforce it in the database. A partial unique index ON bookings (flight_id, seat_no) WHERE status <> 'cancelled' makes the second concurrent insert wait and then fail, whatever the app does. The app catches the unique violation and offers another seat. Row locks with SELECT … FOR UPDATE are an alternative.
Lesson: Design this schema: flight seat booking
How do you handle a seat held during payment?
Insert the booking as held with a timestamp. Payment success updates it to confirmed. A scheduled job cancels holds older than the time limit, which frees the seat because the unique index ignores cancelled rows.
Lesson: Design this schema: flight seat booking
Why is seats a weak entity?
A seat number like 12A only identifies a seat within one flight. Its key is the owner's key plus its own partial key, (flight_id, seat_no), and deleting the flight deletes its seats (ON DELETE CASCADE).