SQL & PostgreSQL

1. 1.Explain the different types of joins: INNER, LEFT, RIGHT, FULL OUTER, CROSS and self join.easy

   A join combines rows from two tables based on a condition. The types differ in what happens to rows that don't match.

   • INNER JOIN: only rows that match on both sides.
   • LEFT JOIN: every row from the left table, plus matching right rows; where there's no match, the right-side columns are NULL.
   • RIGHT JOIN: the mirror image. Most people just swap the tables and write a LEFT JOIN.
   • FULL OUTER JOIN: every row from both sides, with NULLs wherever there's no partner. MySQL doesn't support it; you emulate it with a UNION of a left and a right join.
   • CROSS JOIN: the Cartesian product, every row paired with every row, so 6 × 3 rows gives 18.
   • Self join: not a keyword, just a table joined to itself under two aliases, such as employees to their managers.

   -- Flo has no department; HR has no employees
   SELECT e.name, d.name AS dept
   FROM employees e
   LEFT JOIN departments d ON d.id = e.dept_id;
   
   -- INNER JOIN -> 5 rows (Flo dropped)
   -- LEFT JOIN  -> 6 rows (Flo with dept NULL)
   -- RIGHT JOIN -> 6 rows (HR with name NULL)
   -- FULL JOIN  -> 7 rows (both kept)

   What interviewers listen for

   • INNER keeps only matching rows
   • LEFT/RIGHT keep all rows of one side, NULL-padded
   • FULL keeps unmatched rows from both sides
   • CROSS is the Cartesian product (m × n rows)
   • Self join = same table, two aliases

   Likely follow-up: How would you find employees who have no department? · How do you emulate a FULL OUTER JOIN in MySQL?

   answer it out loud →
2. 2.What is the difference between WHERE and HAVING?easy

   Both filter, but at different stages of the query.

   • WHERE filters individual rows before grouping. It can't contain aggregates, so WHERE count(*) > 1 is an error.
   • HAVING filters groups after GROUP BY has run, so it's where conditions on count, sum or avg go.

   The logical order is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. That's also why, in PostgreSQL, you can't use a SELECT alias in WHERE or HAVING: the alias doesn't exist yet. MySQL is more lenient and allows aliases in HAVING.

   Rule of thumb: put any condition on plain columns in WHERE, so fewer rows get grouped, and keep HAVING for conditions on aggregates. HAVING without GROUP BY treats the whole result as one group.

   SELECT dept_id, count(*) AS n, avg(salary) AS avg_sal
   FROM employees
   WHERE salary > 95          -- row filter, before grouping
   GROUP BY dept_id
   HAVING count(*) >= 2;      -- group filter, after grouping

   What interviewers listen for

   • WHERE filters rows before grouping
   • HAVING filters groups after GROUP BY
   • Aggregates are allowed in HAVING, not WHERE
   • Push plain-column filters into WHERE

   Likely follow-up: Can you use a SELECT alias in HAVING? · What is the logical order of evaluation of a SELECT?

   answer it out loud →
3. 3.Write a query to find the second (or Nth) highest salary. How do you handle ties and the case where it doesn't exist?mid

   First clarify what "second highest" means with ties: salaries 200, 150, 150 have a second-highest distinct salary of 150. Three common approaches:

   • DENSE_RANK: rank distinct salaries and pick rank N. It handles ties and generalizes to any N, and with PARTITION BY dept_id it works per department.
   • DISTINCT + OFFSET: ORDER BY salary DESC OFFSET N-1 LIMIT 1. Short, but forgetting DISTINCT gives the wrong answer on ties.
   • Nested MAX: max(salary) WHERE salary < (SELECT max(salary) ...). Only practical for N = 2.

   If there's no Nth salary, the OFFSET query returns zero rows. Wrapping it in a scalar subquery turns that into a single NULL, which is often what the grader expects.

   -- Nth highest with DENSE_RANK (N = 2)
   SELECT DISTINCT salary
   FROM (SELECT salary,
                DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
         FROM employees) t
   WHERE rnk = 2;
   
   -- Returns NULL instead of no rows when N is too large
   SELECT (SELECT DISTINCT salary FROM employees
           ORDER BY salary DESC OFFSET 1 LIMIT 1) AS second_highest;

   What interviewers listen for

   • Clarify how ties should count
   • DENSE_RANK generalizes to any N and per group
   • DISTINCT is required with the OFFSET approach
   • Scalar subquery returns NULL when no Nth value

   Likely follow-up: How would you get the highest-paid employee in each department? · Why would RANK give a different answer than DENSE_RANK here?

   answer it out loud →
4. 4.What is the difference between a primary key, a unique key and a foreign key?easy

   • A primary key uniquely identifies each row. It's UNIQUE plus NOT NULL, and a table has at most one, though it can span several columns (a composite key).
   • A unique constraint also forbids duplicates, but a table can have many, and the columns can be nullable. In PostgreSQL, NULLs are considered distinct, so a unique column can hold several NULLs; since PostgreSQL 15 you can write UNIQUE NULLS NOT DISTINCT to allow only one.
   • A foreign key says a column's values must exist in a primary key or unique column of another (or the same) table. It enforces referential integrity: you can't insert an order for a customer that doesn't exist, or delete a customer who still has orders, unless you set ON DELETE CASCADE or SET NULL.

   PostgreSQL automatically creates an index for primary keys and unique constraints, but not for the referencing side of a foreign key. Index it yourself, or joins and parent deletes get slow.

   What interviewers listen for

   • PK = unique + not null, one per table
   • Unique allows NULLs and several per table
   • FK references a PK or unique column
   • FK enforces referential integrity; ON DELETE actions
   • PostgreSQL doesn't auto-index the FK column

   Likely follow-up: Natural key or surrogate key: which would you choose and why? · What does ON DELETE CASCADE do?

   answer it out loud →
5. 5.What is the difference between DELETE, TRUNCATE and DROP?easy

   • DELETE is DML. It removes the rows matching a WHERE clause (or all rows), one row at a time, fires row-level DELETE triggers, and can use RETURNING. In PostgreSQL the deleted rows become dead tuples, and the space is reclaimed later by VACUUM.
   • TRUNCATE removes all rows at once by swapping in new, empty storage, so it's much faster on big tables and gives the space back without waiting for VACUUM. It takes an ACCESS EXCLUSIVE lock, doesn't fire DELETE triggers, fails if other tables reference it with a foreign key (unless you add CASCADE), and can reset identity columns with RESTART IDENTITY.
   • DROP TABLE removes the table itself: data, structure, indexes, constraints and triggers.

   A classic gotcha: in PostgreSQL, TRUNCATE and DROP are transactional and can be rolled back. In MySQL and Oracle they cause an implicit commit and can't be.

   What interviewers listen for

   • DELETE: row by row, WHERE, triggers, RETURNING
   • TRUNCATE: all rows, fast, frees space immediately
   • DROP removes the table definition too
   • PostgreSQL can roll back TRUNCATE and DROP
   • MySQL/Oracle auto-commit on TRUNCATE

   Likely follow-up: Does TRUNCATE reset an identity column in PostgreSQL? · Why might DELETE FROM big_table be slow and bloat the table?

   answer it out loud →
6. 6.How do you find duplicate rows in a table, and how do you delete them while keeping one copy?mid

   Finding them is a GROUP BY on the columns that define "duplicate", with HAVING count(*) > 1.

   Deleting them needs a rule for which copy survives, usually the lowest id. Two clean ways:

   • ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) numbers each copy; delete everything with rn > 1. This is the most flexible because the ORDER BY decides who survives.
   • A self-join delete: in PostgreSQL, DELETE FROM t a USING t b WHERE a.email = b.email AND a.id > b.id.

   If the table has no key at all, PostgreSQL's system column ctid (the row's physical location) can stand in for the id within a single statement.

   Then fix the cause: add a UNIQUE constraint so duplicates can't come back, and do the cleanup inside a transaction so you can check the count before committing.

   -- Find
   SELECT email, count(*)
   FROM employees
   GROUP BY email
   HAVING count(*) > 1;
   
   -- Delete all but the lowest id per email
   DELETE FROM employees
   WHERE id IN (
     SELECT id FROM (
       SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
       FROM employees) t
     WHERE rn > 1);

   What interviewers listen for

   • GROUP BY the duplicate columns, HAVING count > 1
   • ROW_NUMBER per partition, delete rn > 1
   • Decide explicitly which copy survives
   • ctid works when there is no key (PostgreSQL)
   • Add a UNIQUE constraint afterwards

   Likely follow-up: How would you do this on a 500-million-row table without long locks?

   answer it out loud →
7. 7.What is the difference between UNION and UNION ALL, and which should you prefer?easy

   Both stack the results of two queries vertically. The queries must return the same number of columns with compatible types, and the column names come from the first query.

   • UNION removes duplicate rows from the combined result, so the database has to sort or hash everything to find them. For deduplication, two NULLs count as equal.
   • UNION ALL keeps every row, including duplicates, and just appends the results. It's cheaper and can stream rows without waiting.

   Prefer UNION ALL unless you actually need deduplication: it's faster and it doesn't silently drop rows that happen to be identical, such as two equal payments. Related operators are INTERSECT and EXCEPT (Oracle calls it MINUS), which also deduplicate unless you add ALL. An ORDER BY at the end applies to the whole combined result.

   What interviewers listen for

   • UNION deduplicates, UNION ALL keeps everything
   • Deduplication costs a sort or hash
   • Same column count and compatible types
   • Default to UNION ALL unless you need distinct rows

   Likely follow-up: What do INTERSECT and EXCEPT do?

   answer it out loud →
8. 8.What is normalization? Explain 1NF, 2NF and 3NF with examples.mid

   Normalization organizes tables so each fact is stored once, which prevents update, insert and delete anomalies.

   • 1NF: every column holds a single atomic value and there are no repeating groups. A phones column containing "555-1, 555-2" violates it; move phones to their own table.
   • 2NF: 1NF, and every non-key column depends on the whole primary key. In order_items(order_id, product_id, qty, product_name), product_name depends only on product_id, so it belongs in products. 2NF only matters with composite keys.
   • 3NF: 2NF, and no non-key column depends on another non-key column (no transitive dependency). In employees(id, dept_id, dept_name), dept_name depends on dept_id, not on id, so it moves to departments.

   The summary people quote: every non-key column depends on "the key, the whole key, and nothing but the key". Most OLTP schemas aim for 3NF and denormalize deliberately where reads demand it.

   What interviewers listen for

   • Goal: store each fact once, avoid anomalies
   • 1NF: atomic values, no repeating groups
   • 2NF: no partial dependency on a composite key
   • 3NF: no transitive dependency between non-key columns
   • OLTP usually targets 3NF

   Likely follow-up: What is BCNF and how does it differ from 3NF? · When would you denormalize?

   answer it out loud →
9. 9.What is an index, how does it speed up queries, and what does it cost?easy

   An index is a separate data structure that maps column values to the rows that contain them, like the index at the back of a book. Without one, finding WHERE email = 'a@x.com' means reading every row (a sequential scan). The default index type, a B-tree, keeps values sorted in a shallow balanced tree, so a lookup takes a handful of page reads, and it also helps with ranges, ORDER BY and joins.

   The costs:

   • Slower writes: every INSERT, UPDATE of an indexed column and DELETE has to maintain every index.
   • Storage and memory: indexes take disk space and compete for cache.
   • Not always used: if a query matches a large fraction of the table, a sequential scan is cheaper, and the planner will choose it.

   PostgreSQL creates indexes automatically for primary keys and unique constraints. For others, use CREATE INDEX, or CREATE INDEX CONCURRENTLY on a live table so writes aren't blocked.

   What interviewers listen for

   • Separate structure mapping values to rows
   • B-tree: sorted, logarithmic lookups, supports ranges
   • Costs: write overhead, storage, cache
   • Low-selectivity queries still use a seq scan
   • PK and unique constraints are indexed automatically

   Likely follow-up: When would an index make things worse? · What is a composite index and does column order matter?

   answer it out loud →
10. 10.What is a transaction, and what do the ACID properties mean?easy

    A transaction is a group of statements that succeed or fail as a unit, between BEGIN and COMMIT (or ROLLBACK). The classic example is a transfer: debit one account, credit another, and never do just one of them.

    • Atomicity: all or nothing. If anything fails, or you roll back, none of the changes are visible.
    • Consistency: a transaction takes the database from one valid state to another; constraints, foreign keys and checks hold at commit.
    • Isolation: concurrent transactions don't see each other's uncommitted work. How strictly depends on the isolation level; PostgreSQL defaults to Read Committed.
    • Durability: once COMMIT returns, the change survives a crash. PostgreSQL writes it to the write-ahead log (WAL) and flushes that to disk before acknowledging, unless you relax synchronous_commit.

    In PostgreSQL, a statement outside BEGIN runs in its own implicit transaction and commits automatically.

    What interviewers listen for

    • Transaction = unit of work, all or nothing
    • Atomicity, Consistency, Isolation, Durability
    • Isolation strength depends on the isolation level
    • Durability via WAL flushed at commit

    Likely follow-up: What isolation levels exist and what anomalies do they allow? · What is a savepoint?

    answer it out loud →
11. 11.How does GROUP BY work with aggregate functions, and what is the rule about non-aggregated columns in the SELECT list?easy

    GROUP BY collapses rows that share the same values in the grouping columns into one output row per group, and aggregate functions such as count, sum, avg, min and max summarize each group.

    The rule: every column in the SELECT list must either appear in GROUP BY or be inside an aggregate. Otherwise the database wouldn't know which row's value to show. PostgreSQL relaxes this in one safe case: if you group by a table's primary key, you can select that table's other columns, because they're functionally dependent on it. Old MySQL versions silently picked an arbitrary value; modern MySQL enables ONLY_FULL_GROUP_BY by default.

    Other details interviewers like: aggregates ignore NULLs (except count(*)), all NULLs in a grouping column form one group, and without GROUP BY an aggregate treats the whole table as one group. PostgreSQL also supports count(*) FILTER (WHERE ...) for conditional aggregates and ROLLUP for subtotals.

    What interviewers listen for

    • One output row per distinct group
    • Non-aggregated SELECT columns must be in GROUP BY
    • Aggregates skip NULLs, except count(*)
    • NULLs group together into one group
    • FILTER (WHERE ...) for conditional aggregates

    Likely follow-up: What is the difference between count(*), count(col) and count(DISTINCT col)?

    answer it out loud →
12. 12.What is the difference between ROW_NUMBER, RANK and DENSE_RANK?mid

    All three are window functions that number rows according to the window's ORDER BY. They only differ on ties:

    • ROW_NUMBER() gives every row a unique, consecutive number. Tied rows get different numbers in an arbitrary order unless you add a tiebreaker to the ORDER BY.
    • RANK() gives tied rows the same rank and then skips: 1, 2, 2, 4.
    • DENSE_RANK() gives tied rows the same rank without gaps: 1, 2, 2, 3.

    Choosing between them is really a requirements question. Use ROW_NUMBER when you need exactly one row per group, such as deduplication or "latest order per customer". Use DENSE_RANK for "Nth highest distinct value". Use RANK for competition-style rankings, where two people tied for second means nobody is third.

    Add PARTITION BY to restart the numbering for each group.

    SELECT name, salary,
           ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
           RANK()       OVER (ORDER BY salary DESC) AS rnk,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
    FROM employees;
    
    -- name | salary | rn | rnk | drnk
    -- Ada  |    200 |  1 |   1 |    1
    -- Bob  |    150 |  2 |   2 |    2
    -- Cy   |    150 |  3 |   2 |    2
    -- Di   |    120 |  4 |   4 |    3

    What interviewers listen for

    • They differ only in how ties are numbered
    • ROW_NUMBER: unique numbers, ties broken arbitrarily
    • RANK: ties share a rank, then gaps
    • DENSE_RANK: ties share a rank, no gaps
    • Add a tiebreaker for deterministic ROW_NUMBER

    Likely follow-up: Which would you use to get the top 3 salaries per department?

    answer it out loud →
13. 13.What is SQL, and what are DDL, DML, DCL and TCL? Give examples of each.easy

    SQL is the standard declarative language for relational databases: you describe the result you want, and the database's optimizer decides how to get it. Its commands are usually grouped into sublanguages:

    • DDL (Data Definition): defines structure. CREATE, ALTER, DROP, TRUNCATE.
    • DML (Data Manipulation): works with the rows. INSERT, UPDATE, DELETE, MERGE, and SELECT (some books split SELECT out as DQL, Data Query Language).
    • DCL (Data Control): permissions. GRANT and REVOKE.
    • TCL (Transaction Control): BEGIN, COMMIT, ROLLBACK, SAVEPOINT.

    A practical difference between databases: in PostgreSQL, most DDL is transactional, so you can run a migration inside BEGIN and roll the whole thing back. In MySQL and Oracle, DDL statements commit implicitly. Some PostgreSQL commands, like CREATE INDEX CONCURRENTLY, can't run inside a transaction block at all.

    What interviewers listen for

    • SQL is declarative; the optimizer picks the plan
    • DDL defines structure: CREATE, ALTER, DROP
    • DML changes data: INSERT, UPDATE, DELETE, SELECT
    • DCL: GRANT/REVOKE; TCL: COMMIT/ROLLBACK/SAVEPOINT
    • PostgreSQL DDL is mostly transactional

    answer it out loud →
14. 14.What is the difference between a clustered and a non-clustered index? Does PostgreSQL have clustered indexes?mid

    A clustered index determines the physical order of the table itself: the table's rows live inside the index's leaf pages, sorted by the key. So there can be only one. A non-clustered (secondary) index is a separate structure whose entries point back to the rows.

    How this plays out depends on the engine:

    • SQL Server: the primary key becomes the clustered index by default; non-clustered indexes point to rows via the clustering key.
    • MySQL InnoDB: every table is clustered on its primary key (or a hidden row id if it has none), and secondary indexes store the primary key value, so a lookup by secondary index does a second lookup by PK. That's why a long PK makes every index bigger.
    • PostgreSQL: no clustered indexes. Tables are unordered heaps, and every index, including the primary key's, stores the row's physical location (its ctid). The CLUSTER command physically reorders a table by an index once, but the order isn't maintained as rows change.

    What interviewers listen for

    • Clustered = table rows stored in index order
    • Only one clustered index per table
    • InnoDB clusters on the PK; secondaries store the PK
    • PostgreSQL tables are heaps; all indexes are secondary
    • CLUSTER reorders once and isn't maintained

    Likely follow-up: Why is a random UUID primary key a problem for InnoDB inserts?

    answer it out loud →
15. 15.How does SQL treat NULL? Why doesn't = NULL work, and what do COALESCE and NULLIF do?easy

    NULL means "unknown" or "missing", not zero or an empty string. SQL uses three-valued logic: any comparison with NULL, even NULL = NULL, yields NULL (unknown), and WHERE only keeps rows where the condition is true. So WHERE col = NULL never matches; you write IS NULL or IS NOT NULL.

    Consequences to know:

    • Arithmetic and || concatenation with NULL give NULL.
    • WHERE dept_id <> 1 silently skips rows where dept_id is NULL.
    • Aggregates ignore NULLs, and sum over zero rows is NULL, not 0.
    • IS DISTINCT FROM is a null-safe comparison.

    COALESCE(a, b, c) returns the first non-NULL argument, handy for defaults like COALESCE(sum(amount), 0). NULLIF(a, b) returns NULL when a = b, the classic trick to avoid division by zero: total / NULLIF(count, 0).

    SELECT NULL = NULL;               -- NULL, not true
    SELECT NULL IS NULL;              -- true
    SELECT 10 + NULL, 'a' || NULL;    -- NULL, NULL
    SELECT 1 IS DISTINCT FROM NULL;   -- true
    SELECT COALESCE(NULL, NULL, 'x'); -- 'x'
    SELECT NULLIF(5, 5);              -- NULL

    What interviewers listen for

    • NULL = unknown; three-valued logic
    • Comparisons with NULL yield NULL, not true/false
    • Use IS NULL, never = NULL
    • COALESCE returns the first non-NULL value
    • NULLIF avoids division by zero

    Likely follow-up: Why does NOT IN return no rows when the subquery contains a NULL?

    answer it out loud →
16. 16.When would you use a subquery instead of a join? Is one faster than the other?mid

    They answer different shapes of question, so start with semantics:

    • A join returns columns from both tables and can multiply rows: joining departments to employees returns a department once per employee.
    • A subquery with IN or EXISTS is a semi-join: it only asks "is there a match?", so each outer row appears at most once. That's cleaner than a join plus DISTINCT.
    • A scalar subquery returns one value, such as comparing a salary to the overall average.

    On performance, a good optimizer often produces the same plan. PostgreSQL turns both IN (SELECT ...) and EXISTS into a semi-join and NOT EXISTS into an anti-join. The differences show up at the edges: a correlated subquery in the SELECT list may be executed once per outer row, and NOT IN can't be turned into an anti-join because of NULL semantics.

    So write the version that states the intent most clearly, then check EXPLAIN if it's slow.

    What interviewers listen for

    • Joins can multiply rows; semi-joins don't
    • IN/EXISTS express "has a match" without DISTINCT
    • Optimizers often produce identical plans
    • Correlated SELECT-list subqueries may run per row
    • Verify with EXPLAIN rather than folklore

    Likely follow-up: What is a correlated subquery? · What is the difference between EXISTS and IN?

    answer it out loud →
17. 17.How would you get the top 2 highest-paid employees in each department?mid

    This is the "top N per group" pattern. The standard answer is a window function in a subquery or CTE, because window functions are computed after WHERE, so you can't filter on them directly:

    1. Number the rows inside each department with PARTITION BY dept_id ORDER BY salary DESC. 2. Keep the rows numbered 1 and 2 in the outer query.

    Then settle ties: ROW_NUMBER returns exactly 2 rows per department, breaking ties arbitrarily unless you add a tiebreaker such as id. DENSE_RANK returns everyone in the top two salary levels, which could be more than two people.

    In PostgreSQL, a LATERAL join with ORDER BY ... LIMIT 2 is an alternative that can be much faster when there are few groups and an index on (dept_id, salary DESC), because it does a short index scan per department instead of ranking the whole table.

    SELECT dept_id, name, salary
    FROM (
      SELECT e.*,
             ROW_NUMBER() OVER (PARTITION BY dept_id
                                ORDER BY salary DESC, id) AS rn
      FROM employees e
    ) ranked
    WHERE rn <= 2;

    What interviewers listen for

    • Window function with PARTITION BY the group
    • Filter in an outer query or CTE
    • ROW_NUMBER vs DENSE_RANK decides tie handling
    • LATERAL ... LIMIT N with an index is an alternative

    Likely follow-up: How would you write this with LATERAL? · Why can't you put rn <= 2 in the inner WHERE?

    answer it out loud →
18. 18.What are window functions, and how is PARTITION BY different from GROUP BY?mid

    A window function computes a value over a set of rows related to the current row, without collapsing them. GROUP BY turns each group into one output row; a window function keeps every row and adds a column.

    The syntax is function() OVER (PARTITION BY ... ORDER BY ... frame):

    • PARTITION BY splits rows into groups, like GROUP BY, but only for the calculation.
    • ORDER BY orders rows within the partition, which matters for ranking, LAG/LEAD and running totals.
    • The frame (for example ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) chooses which rows an aggregate sees.

    Families: ranking (ROW_NUMBER, RANK, DENSE_RANK, NTILE), offset (LAG, LEAD, FIRST_VALUE) and ordinary aggregates used with OVER.

    They're evaluated after WHERE, GROUP BY and HAVING, so to filter on one you wrap the query in a subquery or CTE.

    -- Every employee, plus their department's average
    SELECT name, dept_id, salary,
           avg(salary) OVER (PARTITION BY dept_id) AS dept_avg,
           salary - avg(salary) OVER (PARTITION BY dept_id) AS diff
    FROM employees;

    What interviewers listen for

    • Computes across related rows, keeps every row
    • PARTITION BY groups only for the calculation
    • ORDER BY and frame control ordering and scope
    • Ranking, offset and aggregate window functions
    • Can't be used in WHERE; wrap in a subquery

    Likely follow-up: What is the default window frame when you add ORDER BY?

    answer it out loud →
19. 19.In what order are the clauses of a SELECT statement logically evaluated?easy

    The order you write clauses isn't the order they're evaluated. Logically it's:

    • FROM and JOINs: build the combined row set
    • WHERE: filter rows
    • GROUP BY: form groups
    • HAVING: filter groups
    • SELECT: compute expressions, including window functions
    • DISTINCT: remove duplicates
    • ORDER BY: sort
    • LIMIT / OFFSET: take a slice

    This explains many common errors. You can't use a SELECT alias in WHERE, because WHERE runs first; you can in ORDER BY, because it runs after. You can't use aggregates in WHERE, and you can't filter on a window function without wrapping the query.

    It's a logical model, not the physical plan: the optimizer is free to reorder work, such as pushing filters into joins or using an index to avoid a sort, as long as the result is the same.

    What interviewers listen for

    • FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT
    • Aliases work in ORDER BY, not in WHERE
    • Window functions are computed at the SELECT step
    • Logical order, not the physical execution plan

    answer it out loud →
20. 20.Explain the transaction isolation levels and the anomalies each one allows. What does PostgreSQL do by default?hard

    The SQL standard defines four levels by which anomalies they must prevent:

    • Dirty read: seeing another transaction's uncommitted data.
    • Non-repeatable read: re-reading a row and getting a different value because someone committed in between.
    • Phantom read: re-running a query and getting a different set of rows.
    • Serialization anomaly: a result no serial ordering could produce, such as write skew.

    Read Uncommitted allows all of them; Read Committed prevents dirty reads; Repeatable Read also prevents non-repeatable reads; Serializable prevents everything.

    PostgreSQL is stricter than the standard. Its default is Read Committed, where each statement sees a fresh snapshot. Read Uncommitted behaves like Read Committed, so dirty reads never happen. Repeatable Read uses one snapshot for the whole transaction, which also prevents phantoms, and fails with "could not serialize access due to concurrent update" if you modify a row someone else changed. Serializable adds Serializable Snapshot Isolation. At both higher levels, be ready to retry on SQLSTATE 40001.

    MySQL InnoDB defaults to Repeatable Read.

    What interviewers listen for

    • Dirty, non-repeatable, phantom, serialization anomaly
    • PostgreSQL default is Read Committed
    • PG never allows dirty reads; RR prevents phantoms
    • Serializable uses SSI; retry on 40001
    • MySQL InnoDB defaults to Repeatable Read

    Likely follow-up: What is write skew and which level prevents it? · How would you implement a retry loop for serialization failures?

    answer it out loud →

No questions match that filter.