Question 1

What do the ACID properties mean, and how does each one protect your data?

Accepted Answer

Atomicity ensures a transaction either commits fully or rolls back entirely — no partial updates. Consistency ensures every committed transaction leaves the database in a valid state that satisfies all defined constraints. Isolation ensures concurrent transactions do not see each other's intermediate state. Durability ensures a committed transaction survives crashes because its changes are flushed to non-volatile storage.

Question 2

What aggregate functions does SQL provide, and what are the subtle behaviours of MIN/MAX on non-numeric types?

Accepted Answer

SQL's standard aggregate functions are COUNT, SUM, AVG, MIN, and MAX. MIN and MAX work on any orderable type including strings and dates, using the type's collation or sort order, which surprises analysts who expect numeric-only semantics.

Question 3

What is the difference between COUNT(*), COUNT(column), and COUNT(DISTINCT column)?

Accepted Answer

COUNT(*) counts every row including those with NULLs. COUNT(column) counts only rows where that column is non-NULL. COUNT(DISTINCT column) counts unique non-NULL values in the column.

Question 4

What does a CROSS JOIN do, and when is it actually useful?

Accepted Answer

A CROSS JOIN produces the Cartesian product of two tables — every row from the left paired with every row from the right — giving M x N output rows with no join condition. It is useful for generating date spines, creating all combinations of dimension values, or populating test data grids.

Question 5

What is a derived table, and how does it differ from a correlated subquery or a CTE?

Accepted Answer

A derived table is an inline subquery in the FROM clause that acts as a virtual table for the duration of the query; it is not correlated to the outer query and has no name reuse. A CTE is named and can be referenced multiple times, while a correlated subquery executes per-row in WHERE or SELECT.

Question 6

What is the difference between DISTINCT and GROUP BY for deduplication?

Accepted Answer

Both eliminate duplicate rows, but GROUP BY is the right choice when you also want aggregate values per group. DISTINCT is cleaner when you only need unique rows with no aggregation. In practice most optimizers produce identical plans for simple cases, but semantics and intent differ.

Question 7

How does operator precedence work with AND and OR in a WHERE clause?

Accepted Answer

AND binds more tightly than OR, just like multiplication binds more tightly than addition. Without parentheses, the expression A OR B AND C is evaluated as A OR (B AND C), not (A OR B) AND C. Always use parentheses when mixing AND and OR to make intent explicit.

Question 8

How does GROUP BY behave with multiple columns, and what does each combination represent?

Accepted Answer

GROUP BY on multiple columns creates one group per unique combination of those column values. Each row in the result represents a distinct tuple, and every non-aggregated column in SELECT must appear in the GROUP BY list.

Question 9

Why does SQL require every non-aggregated SELECT column to appear in GROUP BY?

Accepted Answer

Because after grouping, multiple source rows collapse into one output row. Any column not in the GROUP BY key could have different values across those collapsed rows, making a single deterministic output value impossible without an aggregate function.

Question 10

What is the difference between WHERE and HAVING in SQL, and when must you use HAVING?

Accepted Answer

WHERE filters individual rows before any grouping occurs; HAVING filters groups after GROUP BY is evaluated. You must use HAVING when the filter condition references an aggregate function like SUM or COUNT.

Question 11

When would you use IN versus BETWEEN in a WHERE clause?

Accepted Answer

Use IN for a discrete set of values; use BETWEEN for a contiguous range. BETWEEN is inclusive on both ends. IN does not imply any order and is ideal for filtering against a known list, while BETWEEN is cleaner for numeric or date ranges.

Question 12

What is the difference between INNER JOIN, LEFT OUTER JOIN, and FULL OUTER JOIN?

Accepted Answer

INNER JOIN returns only rows where the join condition matches in both tables. LEFT OUTER JOIN returns every row from the left table plus matching rows from the right, filling NULLs where there is no match. FULL OUTER JOIN returns all rows from both sides, with NULLs wherever one side has no match.

Question 13

How does the LIKE operator work in SQL, and when is it a performance problem?

Accepted Answer

LIKE matches string patterns using % (any sequence of characters) and _ (exactly one character). A leading wildcard like '%smith' forces a full table scan because the index cannot be used from the left side; a trailing wildcard like 'smith%' can use a B-tree index prefix scan.

Question 14

What does NTILE do, and how would you use it to label customers into quartiles by spend?

Accepted Answer

NTILE(n) divides rows within a partition into n roughly equal buckets and assigns each row a bucket number from 1 to n. Rows are distributed as evenly as possible; when the row count is not evenly divisible, earlier buckets receive one extra row.

Question 15

Why does WHERE column = NULL never return rows in SQL?

Accepted Answer

NULL represents an unknown value. Comparing anything to NULL with = produces NULL (not TRUE or FALSE), and WHERE only passes rows where the condition evaluates to TRUE. The correct syntax is IS NULL or IS NOT NULL.

Question 16

What is the difference between OLTP and OLAP workloads, and how does that drive database design choices?

Accepted Answer

OLTP systems handle many small, latency-sensitive transactions that read and write a few rows at a time, so they are optimized for fast point lookups and row-level locking. OLAP systems run infrequent but wide analytical queries over millions of rows, so they benefit from columnar storage, bulk scans, and denormalized schemas that minimize joins.

Question 17

How does ORDER BY work with multiple columns, and what is the default sort direction?

Accepted Answer

When you list multiple columns in ORDER BY, SQL sorts by the first column, then breaks ties using the second, and so on. The default direction is ASC (ascending). Each column gets its own independent ASC or DESC modifier.

Question 18

What is the difference between PARTITION BY in a window function and GROUP BY in an aggregate query?

Accepted Answer

GROUP BY collapses multiple rows into one row per group and discards individual row data. PARTITION BY divides rows into groups for the window function calculation but preserves every row in the output — each row retains its own columns plus the computed window value.

Question 19

What is the difference between a primary key and a foreign key, and what guarantees do they provide?

Accepted Answer

A primary key uniquely identifies each row in a table and implicitly creates a unique index; it cannot be NULL. A foreign key in a child table references the primary key of a parent table and enforces referential integrity — the database rejects inserts or updates that reference a non-existent parent row, and rejects parent deletes that would orphan child rows unless a cascade rule is defined.

Question 20

What is a scalar subquery, where can it appear in a SQL statement, and what happens if it returns more than one row?

Accepted Answer

A scalar subquery is a subquery that returns exactly one column and one row, and can appear anywhere a single value is valid — SELECT list, WHERE clause, HAVING clause, or even a JOIN ON condition. If it returns more than one row at runtime the database raises a runtime error, not a compile-time error.

Question 21

How do column aliases work in SQL, and where can you reference them?

Accepted Answer

A column alias defined in SELECT can be referenced in ORDER BY but not in WHERE or HAVING, because SELECT runs after those clauses in the logical processing order. To reuse a complex expression in WHERE or HAVING, repeat the expression or wrap the query in a subquery or CTE.

Question 22

Why is SELECT * considered bad practice in production SQL?

Accepted Answer

SELECT * fetches every column at query time, so adding or reordering columns in the table silently breaks downstream code, wastes I/O on columns you never use, and prevents the optimizer from doing index-only scans. Always name the columns you need.

Question 23

What is the difference between WHERE and HAVING in SQL?

Accepted Answer

WHERE filters individual rows before any grouping or aggregation occurs; HAVING filters groups after aggregation. You cannot reference an aggregate function like COUNT() or SUM() in a WHERE clause because those values don't exist yet at that stage of query execution.

Question 24

Why can't you use a window function directly in a WHERE clause, and how do you work around it?

Accepted Answer

Window functions are evaluated in the SELECT phase, after WHERE and HAVING have already filtered rows. Referencing a window function alias in WHERE causes a syntax or evaluation-order error. The fix is to wrap the query in a CTE or subquery so the outer query can filter on the computed window value.

Question 25

How can aggregating after a JOIN produce inflated (double-counted) totals, and how do you fix it?

Accepted Answer

A JOIN that fans out rows — one-to-many or many-to-many — causes the same source row to appear multiple times in the joined result set. Aggregating on that inflated set multiplies values, giving totals larger than the true sum.

Question 26

What is an anti-join, how do you implement one in SQL, and which implementation is most reliable?

Accepted Answer

An anti-join returns rows from the left table that have no matching row in the right table — the inverse of a semi-join. The three implementations are NOT EXISTS, NOT IN, and a LEFT JOIN with a NULL filter; NOT EXISTS is the most reliable because it is NULL-safe and communicates intent clearly.

Question 27

Why does column order matter in a composite index, and what is the left-prefix rule?

Accepted Answer

A composite index on (a, b, c) is sorted first by a, then b within each a value, then c. The optimizer can use the index for queries that filter on a, on a + b, or on a + b + c, but not on b alone or c alone, because those values are not contiguous in the index structure.

Question 28

How do you use conditional aggregation to pivot row data into columns without a PIVOT keyword?

Accepted Answer

Wrap a CASE WHEN expression inside an aggregate function — typically SUM or COUNT — to selectively accumulate values for a specific category while leaving all other rows contributing zero or NULL. This produces one column per category from a single GROUP BY pass.

Question 29

What is the difference between a correlated and an uncorrelated subquery, and when does the distinction matter for performance?

Accepted Answer

An uncorrelated subquery executes once and its result is fed into the outer query; a correlated subquery references columns from the outer query and re-executes for every row the outer query processes. The distinction matters because correlated subqueries can silently turn an O(n) query into O(n²).

Question 30

What is a covering index and how does it eliminate heap fetches?

Accepted Answer

A covering index includes every column a query needs — both filter and select columns — so the engine can answer the query entirely from the index pages without touching the main table heap. This removes the costliest part of an index scan: the random I/O for each individual row fetch.

Question 31

When should you choose a CTE over a subquery, and does a CTE always offer a performance advantage?

Accepted Answer

CTEs improve readability and allow a named result to be referenced multiple times in one query, but most databases inline them during optimization, so they carry no inherent performance advantage over equivalent subqueries. Only databases that materialize CTEs by default — like older PostgreSQL versions — show a measurable difference.

Question 32

When should you use EXISTS, IN, or a JOIN for a semi-join, and what are the NULL-safety differences?

Accepted Answer

EXISTS short-circuits as soon as one match is found and is NULL-safe; IN loads the full subquery result and returns no rows when the list contains a NULL; a JOIN can multiply rows if the right side has duplicates. For large datasets, EXISTS or a deduplicated JOIN is generally safest.

Question 33

Given a query that filters on both a raw column and an aggregate result, how do you structure it for correctness and performance?

Accepted Answer

Raw-column filters belong in WHERE so the engine scans fewer rows before grouping. Aggregate filters must go in HAVING. Applying a filter in HAVING that could have been in WHERE forces the engine to aggregate more rows than necessary.

Question 34

How do FIRST_VALUE and LAST_VALUE work, and why does LAST_VALUE often return unexpected results?

Accepted Answer

FIRST_VALUE returns the value from the first row of the window frame; LAST_VALUE returns the value from the last row. LAST_VALUE surprises most users because the default frame ends at CURRENT ROW, not at the end of the partition — the frame must be explicitly extended to ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to reach the true last row.

Question 35

How do you use a FULL OUTER JOIN to detect missing or mismatched rows between two tables?

Accepted Answer

A FULL OUTER JOIN combined with IS NULL checks on each side isolates rows that exist in only one table, making it ideal for data reconciliation, pipeline audits, and finding discrepancies between a source and a target table.

Question 36

Can you GROUP BY a derived expression or a SELECT alias, and how does this differ across databases?

Accepted Answer

You can always GROUP BY a derived expression written directly. Whether you can reference a SELECT alias in GROUP BY depends on the database: MySQL and BigQuery allow it, while PostgreSQL and SQL Server do not because aliases are not resolved until after GROUP BY in the logical order.

Question 37

Can you use HAVING without GROUP BY, and what does it mean?

Accepted Answer

Yes. Without GROUP BY the entire table is treated as one implicit group, so HAVING filters that single aggregate result. It is rarely used this way in practice but is valid SQL and useful for existence checks.

Question 38

How does a B-tree index work, and when does the database choose not to use it?

Accepted Answer

A B-tree index stores key values in a balanced tree of sorted nodes, allowing the engine to reach any value in O(log n) page reads instead of scanning every row. The optimizer skips the index when the estimated cost of random I/O exceeds a full-table scan, when a function wraps the indexed column, or when the query returns such a large fraction of rows that a sequential scan is cheaper.

Question 39

How do you join tables on multiple keys, and why is the key order in a composite index important?

Accepted Answer

You combine conditions in the ON clause with AND to join on multiple columns, which is necessary when no single column is a unique identifier across both tables. For index performance, the most selective column — or the column used in equality predicates — should come first in a composite index.

Question 40

What happens when a join key contains NULLs? Do NULL values ever match in a JOIN?

Accepted Answer

NULL never equals NULL in SQL — join conditions use equality, so rows where either key is NULL are silently excluded from INNER JOIN results and placed in the unmatched set for OUTER JOINs. If you need NULL-to-NULL matching, you must use IS NOT DISTINCT FROM or COALESCE the key to a sentinel value.

Question 41

Does the order of tables in a JOIN clause affect query results or performance?

Accepted Answer

Join order never affects the logical result — SQL is declarative and the engine chooses the physical join order. However, join order in the query does influence the optimiser's starting point, and in complex queries with many tables or when statistics are stale, manually reordering joins or using query hints can significantly change performance.

Question 42

How do LAG and LEAD work, and how would you use them to compute month-over-month revenue change?

Accepted Answer

LAG accesses a value from a previous row within the partition; LEAD accesses a value from a following row. Both accept an optional offset (default 1) and a default value when the referenced row does not exist. They are the standard tool for period-over-period comparisons without a self-join.

Question 43

Why does filtering on a right-table column in the WHERE clause turn a LEFT JOIN into an INNER JOIN?

Accepted Answer

A LEFT JOIN produces NULLs for right-table columns when there is no match. Adding a WHERE condition that demands a specific non-NULL value from the right table then eliminates those NULL rows, leaving only matched rows — identical to an INNER JOIN result.

Question 44

How do LIMIT and OFFSET work, and what is the problem with deep pagination?

Accepted Answer

LIMIT restricts the number of rows returned; OFFSET skips that many rows before returning results. Deep pagination with large OFFSET values is slow because the database must scan and discard all skipped rows — keyset pagination on an indexed column is the production-scale alternative.

Question 45

How would you calculate a 7-day moving average of daily sales, and what frame clause is needed?

Accepted Answer

Use AVG() with a ROWS frame specifying 6 PRECEDING to current row — this captures exactly 7 physical rows regardless of date gaps. RANGE with INTERVAL '6 days' PRECEDING is the alternative when you need a true calendar window, but it requires at most one row per date and may include fewer than 7 rows if days are missing.

Question 46

How do you chain multiple CTEs in a single query, and what are the scoping and execution rules you need to know?

Accepted Answer

Multiple CTEs are defined in a single WITH clause separated by commas; each CTE can reference any CTE defined before it in the list but not one defined after. The entire WITH block is logically evaluated before the final SELECT, and each CTE is visible throughout the rest of the query including inside other CTEs.

Question 47

What are 1NF, 2NF, and 3NF, and when would you intentionally denormalize?

Accepted Answer

1NF eliminates repeating groups and requires atomic column values. 2NF further removes partial dependencies on a composite key. 3NF removes transitive dependencies — every non-key column must depend on the key, the whole key, and nothing but the key. Denormalization trades update anomalies for read performance, and is appropriate when the read path dominates and write correctness can be enforced at the application layer or with materialized views.

Question 48

Why does NOT IN (subquery) return zero rows when the subquery contains a NULL, and how do you fix it?

Accepted Answer

SQL uses three-valued logic: comparing any value to NULL yields UNKNOWN, not FALSE. NOT IN evaluates as NOT (a = v1 OR a = v2 OR ...), so a single NULL in the list makes the entire predicate UNKNOWN for every row, suppressing all results. Use NOT EXISTS or a LEFT JOIN anti-pattern instead.

Question 49

Write a query to find the Nth highest salary in a table, where N is a parameter, handling ties correctly.

Accepted Answer

Use DENSE_RANK() to assign rank without gaps — the Nth distinct salary value is the row where DENSE_RANK equals N. RANK() would produce wrong results when ties occur, and a plain ORDER BY LIMIT/OFFSET approach ignores ties entirely.

Question 50

How do aggregate functions handle NULL values in SQL?

Accepted Answer

All aggregate functions except COUNT(*) silently ignore NULL values. This means AVG divides by the count of non-NULL rows, not total rows, which can silently skew results if NULLs are not accounted for.