80%
SQL Joins, aggregation, window functions, indexes, transactions, normalisation0/29 questions known
1. Query order and basics
DefinitionA relational database stores data in tables with a fixed schema, linked by keys, queried with SQL, and protected by ACID transactions (MySQL, PostgreSQL, SQL Server…
FlowLogical order SQL runs in — why WHERE can't use a SELECT aliasFROM / JOIN→WHERE→GROUP BY→HAVING→SELECT→ORDER BY→LIMIT
Primary key: unique, not null row id.
WHERE filters rows before grouping; HAVING filters groups after.
NULL means unknown: use IS NULL, never = NULL; COUNT(col) skips NULLs, COUNT(*) doesn't.
2. Joins
INNER JOIN: only matching rows in both tables
LEFT JOIN: all rows from the left table, NULLs where there's no match
RIGHT JOIN: all rows from the right table
FULL OUTER JOIN: all rows from both
3. Aggregation and subqueries
Example: GROUP BY, HAVING, subquery
4. Window functions (top interview topic)
DefinitionWindow functions compute across related rows without collapsing them (unlike GROUP BY): fn() OVER (PARTITION BY … ORDER BY …).
LAG() / LEAD() look at the previous / next row (month-over-month change).
Running total: SUM(amount) OVER (ORDER BY day).
CTEs (WITH …) name subqueries; recursive CTEs walk hierarchies.
5. Indexes and performance
DefinitionAn index (usually a B-tree) lets the database find rows without scanning the whole table — faster reads, slightly slower writes and extra storage.
Index columns used in WHERE, JOIN and ORDER BY; high-selectivity columns first.
Composite index (a, b) helps WHERE a = ? and WHERE a = ? AND b > ?, but not WHERE b = ? alone (leftmost-prefix rule).
Functions on a column (WHERE LOWER(email) = …) skip a normal index.
Use EXPLAIN / EXPLAIN ANALYZE to see scans vs index use; avoid SELECT *; fix N+1 queries with joins or batching.
6. Transactions and isolation
ACID: Atomic (all or nothing), Consistent (constraints hold), Isolated (concurrent transactions don't interfere), Durable…
Read uncommitted: nothing (dirty reads possible)
Read committed (Postgres default): dirty reads
Repeatable read (MySQL InnoDB default): + non-repeatable reads
Serializable: + phantom reads (safest, slowest)
7. Schema design and normalisation
1NF: atomic values, no repeating groups (no "tags: a,b,c" column)
2NF: 1NF + every column depends on the whole primary key
3NF: 2NF + no column depends on another non-key column
Many-to-many needs a junction table (student_course(student_id, course_id)).
Denormalise deliberately for read-heavy reporting (with care to keep copies in sync).
Constraints keep data correct: NOT NULL, UNIQUE, CHECK, FOREIGN KEY … ON DELETE CASCADE/RESTRICT.
Soft delete (deleted_at) when you need history or undo.
Most asked (10)
QIn what order does SQL logically execute a SELECT query?You write SELECT … FROM … WHERE …, but the database logically evaluates it in this order:
QWhat is the difference between WHERE and HAVING?WHERE filters rows before grouping.
QExplain the different types of joins.- INNER JOIN: only rows that match in both tables.
QExplain primary, foreign, unique and composite keys.- Primary key: uniquely identifies each row; unique and never NULL; one per table.
QFind the second (or Nth) highest salary.Ask first: should ties count once?
QWhat are window functions? Compare ROW_NUMBER, RANK and DENSE_RANK.A window function computes a value over a set of related rows without collapsing them the way GROUP BY does.
QWhat is the difference between DELETE, TRUNCATE and DROP?- DELETE (DML) removes rows, optionally with a WHERE.
QWhat is an index, how does it work, and when can it hurt?An index is a separate sorted structure, usually a B-tree (B+ tree), that maps column values to rows.
QExplain normalisation: 1NF, 2NF, 3NF and BCNF. When do you denormalise?Normalisation organises tables to remove redundancy, so each fact is stored once and update anomalies disappear.
QWhat does ACID mean?ACID is the set of guarantees a transaction gives: