SQL Sorcery
Interview Questions

1

DDL vs DML

What is the difference between DELETE, TRUNCATE, and DROP?

All three remove data but operate at completely different levels — DELETE removes specific rows, TRUNCATE empties a table in one sweep, and DROP destroys the table itself.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

2

Query Writing

Write a query to find the second-highest salary from an Employee table. What should it return if there is no second salary?

Find the largest salary that is strictly less than the overall maximum — and wrap it in MAX() so the query returns NULL (not zero rows) when no second salary exists.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

3

Aggregation

Write a query to find all duplicate email addresses in a Person table.

Group by email and use HAVING to keep only groups that appear more than once — the duplicate check must go in HAVING, not WHERE, because you can only count after grouping.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

4

Set Operators

What is the difference between UNION and UNION ALL? Which is faster and why?

Both combine results from two queries into one list — UNION ALL keeps every row including duplicates (faster), UNION deduplicates first (slower).

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

5

Joins

Write a query to list every department along with its employee count — including departments that have zero employees.

Use a LEFT JOIN starting from Department so departments with no employees still appear — and count COUNT(e.id), not COUNT(*), or empty departments wrongly show 1.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

6

Keys

What is the difference between a PRIMARY KEY, a UNIQUE key, and a FOREIGN KEY?

Primary key is the row's unique identifier (never NULL, only one per table), unique key enforces no-duplicate values (can hold NULLs, multiple per table), and foreign key enforces referential integrity by linking to another table's…

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

7

Joins

What are the main types of JOINs in SQL?

JOIN types control which rows from two tables appear in the result — INNER keeps only matching rows, LEFT keeps all rows from the left table, FULL OUTER keeps everything from both sides, and CROSS…

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

8

Filtering

What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping; HAVING filters groups after GROUP BY. Any filter using COUNT(), SUM(), or AVG() must go in HAVING because those values don't exist until after grouping.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

9

Schema Design

What is database normalization? Briefly explain 1NF, 2NF, and 3NF.

Normalization organises tables to eliminate data repetition — store each fact once, in one place, so that a change requires only one update.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

10

Basics

What does the DISTINCT keyword do?

DISTINCT removes duplicate rows from the result set so each unique value (or combination of values) appears only once.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

11

Data Types

What is the difference between CHAR and VARCHAR?

CHAR(n) is fixed-length (always stores exactly n characters, padding with spaces), VARCHAR(n) is variable-length (stores only as many characters as needed, up to n).

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

12

NULL Handling

How do you test for and filter NULL values in SQL?

NULL means "unknown" or "missing" — never use = to test for it. Use IS NULL and IS NOT NULL instead, because any comparison with NULL evaluates to unknown (not true), so WHERE phone =…

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

13

Query Execution

In what order does SQL actually run the parts of a SELECT query?

You write SELECT first, but the database runs it in a different order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

14

Pattern Matching

How does the LIKE operator work, and what do % and _ mean?

LIKE performs pattern matching on text columns — % matches any sequence of characters (including none), _ matches exactly one character.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

15

Aggregation

What are aggregate functions? Name the common ones.

Aggregate functions collapse many rows into a single summary value — COUNT, SUM, AVG, MIN, and MAX are the five core ones.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

16

Date Filtering

Write a query to find all employees hired in the last 30 days.

Calculate the cutoff date on the right side of the comparison and keep the column bare — this lets the database use an index on hire_date for fast filtering.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

17

Sorting

How does ORDER BY work, including sorting by multiple columns?

ORDER BY sorts results by one or more columns — ASC (ascending, the default) or DESC (descending). Multiple columns are sorted left to right, with later columns breaking ties.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

18

Subqueries

What is a subquery? Give a simple example.

A subquery is a query nested inside another query — the inner query runs first and its result is used by the outer query.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

19

Operators

What is the difference between the IN and BETWEEN operators?

IN checks if a value matches any item in a specific list. BETWEEN checks if a value falls within a continuous range (inclusive of both ends).

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

20

Pagination

How do you return just one page of results, like rows 21 to 30?

Use LIMIT with OFFSET (MySQL/Postgres) — OFFSET skips a number of rows, LIMIT takes the next N. Always pair with ORDER BY or the same rows may appear on different pages.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

21

Constraints

What is a foreign key, and what does ON DELETE CASCADE do?

A foreign key links a column to the primary key of another table, and blocks invalid links — you can't add an order for a customer who doesn't exist.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

22

Aggregation

Write a query to find total sales for each product category.

GROUP BY category bundles all rows of the same category together, SUM(amount) adds up the sales in each bundle, and ORDER BY puts the biggest category on top.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

23

NULL Handling

How do you replace or neutralize a NULL value? (COALESCE, NULLIF)

Both are standard SQL. (SQL Server's ISNULL is similar to COALESCE, but takes only two arguments.)

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

24

Joins

In a JOIN, what is the difference between a condition in the ON clause and one in the WHERE clause?

For an INNER JOIN they behave the same. But for a LEFT JOIN they're very different — and this trips people up.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

25

Aggregation

If both can remove duplicates, what is the difference between DISTINCT and GROUP BY?

For *just* removing duplicate rows, SELECT DISTINCT col and SELECT col ... GROUP BY col give the same result.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

26

SQL Categories

What are the four categories of SQL commands — DDL, DML, DCL, and TCL?

SQL commands fall into four families by what they do.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

27

Constraints

What are the common column constraints, and what does each enforce? (NOT NULL, UNIQUE, CHECK, DEFAULT)

Constraints are rules the database enforces automatically, so bad data can't get in.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

28

Indexing

What is an index, and when should you add one?

An index is a separate, sorted lookup structure the database keeps for a column, so it can find rows *without scanning the whole table*.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

29

String Functions

What are the common string functions in SQL?

The everyday string toolkit.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

30

Date Functions

What are the common date/time functions, and how do you work with dates?

The common ones (names vary a little by database).

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

31

Views

What is a view, and why would you use one?

A view is a *saved query* you can treat like a table. It stores no data of its own — each time you query it, the underlying SELECT runs and gives fresh results.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

32

Filtering

What is the difference between WHERE and HAVING? When do you use each?

Both filter rows, but at different stages of the query.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

33

Query Writing

Write a query to find each customer's total order count, showing only customers who have placed more than 5 orders.

Key points the interviewer is listening for.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

34

Query Writing

Write a query to find all employees who work in the 'Engineering' department.

Also valid — subquery approach.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

35

Filtering

How do you find rows where a column is NULL? Why can't you use = NULL?

NULL means "unknown" — and in SQL, comparing anything to "unknown" gives "unknown", not true or false. So column = NULL never matches.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

36

Query Writing

Write a query to get the top 5 most expensive products.

On SQL Server use TOP instead of LIMIT.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

37

Query Writing

What does DISTINCT do, and write an example where it actually matters?

DISTINCT removes duplicate rows from the result — any two rows that are identical across all selected columns are collapsed into one.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

38

Query Writing

How do you rename a column in a query result? Show an example.

Use AS to give a column a new label in the output (the underlying table is unchanged).

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

39

Query Writing

Write a query to find all customers who placed an order in the last 30 days.

On MySQL.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

40

Joins

What is the difference between INNER JOIN and LEFT JOIN? When would you use each?

When to use which.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

41

Query Writing

Write a query to list every customer and how many orders they have placed, including customers with zero orders.

Why LEFT JOIN, not INNER JOIN?

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

42

DML

Write a query to give all products in the 'Electronics' category a 10% price increase.

Before running any UPDATE in an interview or in production, always test your WHERE clause with a SELECT first.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

43

Filtering

What is the difference between = and LIKE in a WHERE clause?

Performance note: LIKE with a leading % (e.g. LIKE '%smith') cannot use a standard B-tree index — it must scan the whole table. LIKE without a leading wildcard (e.g. LIKE 'Sm%') can still use an…

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

44

Query Writing

Write a query to find all products with a price between 100 and 500.

Important: BETWEEN is *inclusive* — it includes both 100 and 500. If you want to exclude the boundaries, use the explicit >= / <= form with your chosen limits.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

45

Query Writing

Write a query to display each customer's full name as a single column, combining first and last name.

Edge case the interviewer might probe: what if either name is NULL?

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

46

Aggregation

Write a query to get the total, average, minimum, maximum, and count of salaries from an Employee table.

Per department.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

47

Filtering

Write a query to find all customers who live in either London or New York.

IN is preferred when checking against 3 or more values — more readable and the database can optimise it better than a long OR chain.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

48

Query Writing

Write a query to find all duplicate email addresses in a Customers table and show how many times each appears.

If you also want to see the full rows (not just the emails).

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

49

Practical

You ran a DELETE or UPDATE without a WHERE clause and affected all rows. What do you do?

This is a critical situation — act immediately.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

50

Query Writing

Write a query to insert a new product only if a product with the same name does not already exist in the table.

Approach 1 — INSERT with NOT EXISTS (portable).

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

1

Window Functions

Find the top 3 highest-paid employees in each department.

Rank employees within each department using DENSE_RANK() OVER (PARTITION BY department), then keep ranks 1–3. Use DENSE_RANK, not ROW_NUMBER, so tied salaries share a rank rather than arbitrarily dropping one.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

2

Self Join

Given an Employee table where each row has a managerId pointing to another employee, find all employees who earn more than their manager.

Join the Employee table to itself — one alias as the employee, one as their manager — then filter where the employee's salary exceeds the manager's.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

3

Window Functions

Explain the difference between ROW_NUMBER(), RANK(), and DENSE_RANK() with an example.

All three number rows in order, but differ in how ties are handled — ROW_NUMBER gives every row a unique number, RANK skips numbers after ties, DENSE_RANK never skips.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

4

Window Functions

Write a query that returns each day's sales along with a running (cumulative) total of sales over time.

Use SUM() as a window function with ORDER BY to create a running total — each row's cumulative total grows as you move through the ordered dataset.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

5

Indexing

What is the difference between a clustered and a non-clustered index? How many of each can a table have?

A clustered index determines the physical order rows are stored — only one allowed per table. A non-clustered index is a separate sorted lookup structure with pointers to the actual rows — many allowed per…

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

6

Window Functions

How would you show the month-over-month change in sales?

Use LAG() to pull the previous row's value onto the current row, then subtract — this gives the change without a self-join or subquery.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

7

Query Writing

How do you find the median salary?

Use PERCENTILE_CONT(0.5) for databases that support it — for others, rank from both ends and take the middle value where ascending and descending row numbers meet.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

8

Pivoting

How do you turn rows into columns — for example, total sales per quarter shown as four columns?

Use CASE inside aggregate functions — one CASE expression per output column — or the PIVOT keyword on databases that support it (SQL Server/Oracle).

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

9

Subqueries

Find customers who bought product A but never bought product B.

Find A-buyers, then exclude anyone who also appears in the B-buyers set — use NOT EXISTS for NULL safety, LEFT JOIN...IS NULL for join-based thinking, or EXCEPT for the most readable set-theory approach.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

10

CTEs

What is a CTE, and when would you use one instead of a subquery?

A CTE (Common Table Expression) is a named, temporary result defined with WITH that can be referenced like a table in the main query — making complex multi-step queries readable and avoiding repeated subquery duplication.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

11

Recursive CTEs

How would you list an employee and everyone below them in the org chart?

Use a recursive CTE — the anchor (first part) selects the starting employee, the recursive (second part) keeps joining to find the next level down, until no new rows are found.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

12

CASE

How do you group salaries into bands like Low / Medium / High?

Use CASE to assign a label to each row, then GROUP BY that label. CASE evaluates conditions top to bottom and stops at the first match — so condition order matters.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

13

Subqueries

What is the difference between IN, EXISTS, and a JOIN for checking related rows?

All three can answer "does a related row exist?" — IN checks against a list, EXISTS short-circuits on the first match, JOIN combines rows (use when you need the other table's columns). For large datasets,…

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

14

NULL Gotchas

Why can NOT IN with a subquery suddenly return no rows?

If the subquery returns even one NULL, NOT IN returns zero rows for the entire outer query — silently, with no error. The fix is NOT EXISTS, which handles NULLs correctly.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

15

Aggregation

Find the department with the highest average salary.

Group by department, compute AVG(salary), sort descending, and take the top row. Use RANK() instead of LIMIT 1 if you want all tied winners.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

16

Window Functions

How do you show each category as a percentage of total sales?

Divide each category's total by the grand total — use a window SUM() OVER() to put the grand total on every row in one pass, or a scalar subquery for the same result with simpler…

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

17

Window Functions

For each customer, find their most recent order.

Use ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) to rank each customer's orders newest-first, then keep only rank 1.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

18

Subqueries

What is a correlated subquery, and why can it be slow?

A correlated subquery references columns from the outer query, forcing it to re-execute once for every outer row — which is slow on large tables. The fix is to pre-calculate the repeated result with GROUP…

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

19

Window Functions

How do you find missing numbers (gaps) in a column of sequential IDs?

Compare each row to the next with LEAD, and flag where the jump is more than 1.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

20

Window Functions

Find all employees who earn more than the average salary of their own department.

Approach 1 — correlated subquery (easiest to read). For each employee, compare their salary to their own department's average.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

21

Indexing

Why might a query ignore an index on a column, and how do you fix it?

Wrapping the column in a function — YEAR(order_date) — means the database can't use the index on order_date, because the index stores the raw dates, not the result of YEAR(). This is a "non-sargable" condition,…

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

22

Data Quality

How do you find rows that are duplicated across several columns, not just one?

Approach 1 — GROUP BY + HAVING (easiest). Group by all the columns that together define a duplicate, and keep groups with more than one row.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

23

Aggregation

How do you get subtotals and a grand total in the same result?

ROLLUP adds subtotal and grand-total rows automatically.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

24

Self Join

Find all pairs of employees who work in the same department.

Join the table to itself on department, and use < to avoid duplicates and self-pairs.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

25

Window Functions

How do you split rows into 4 equal groups, like salary quartiles?

NTILE(4) divides the ordered rows into 4 buckets as evenly as possible.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

26

Window Functions

What is the difference between GROUP BY and PARTITION BY?

Both group rows by a column — the difference is what comes *out*.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

27

Transactions

How do transactions work — BEGIN, COMMIT, ROLLBACK, and SAVEPOINT?

A transaction groups several statements so they succeed or fail *together*.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

28

Programmability

What is the difference between a stored procedure, a function, and a trigger?

All three are saved bits of logic that live inside the database.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

29

Data Modification

How do you "insert or update" a row in a single statement (an upsert)?

An upsert inserts a new row, or updates it if it already exists — in one atomic step, avoiding a race between a separate SELECT and INSERT. The syntax differs by database.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

30

Joins

Find all customers who have never placed an order.

Approach 1 — LEFT JOIN ... IS NULL (the classic anti-join). Keep customers whose matching order is missing.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

31

Set Operators

What do EXCEPT and INTERSECT do?

They combine two result sets like UNION, but by *set logic* (both queries must return matching columns).

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

32

Performance

A query that ran in 2 seconds now takes 3 minutes after the table grew from 1M to 50M rows. How do you investigate and fix it?

Step 1 — Get the execution plan.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

33

Window Functions

Write a query showing each salesperson's total revenue alongside the overall team average, in the same row.

Why a window function? A plain AVG(amount) in a GROUP BY would aggregate everything into one row. The window function OVER () (empty window = whole result set) computes the average *across all salespeople* while…

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

34

Query Writing

Write a query to find the first purchase date and the most recent purchase date for each customer.

A common follow-up: also show the amount of their first and last order.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

35

Practical

You ran UPDATE without a WHERE clause and updated every row in a large table in production. What do you do?

This is a production incident — respond immediately.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

36

Data Modification

How do you find duplicate rows in a table and delete them, keeping only one row per group?

Step 1 — Find the duplicates.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

37

Window Functions

Write a query to calculate the running total of daily sales.

Breaking it down.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

38

Query Writing

Write a query to find gaps in a sequential ID column — IDs that are missing from the sequence.

Approach 1 — self-join to find missing numbers.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

39

Performance

Your query has 4 JOINs and is very slow. What do you look at first?

Start with EXPLAIN ANALYZE — don't guess. The plan tells you where the time is actually going.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

40

Query Writing

Write a query to find the percentage contribution of each product category to total revenue.

Breaking it down.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

41

Window Functions

How do you compare a value in the current row with the value in the previous row — for example, month-over-month revenue change?

Use the LAG window function to look back one row.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

42

Joins

Your JOIN query is returning more rows than expected — duplicates in the result. What are the likely causes and how do you fix them?

Duplicate rows from JOINs almost always come from one of these.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

43

Query Writing

Write a query to list every department and its employee headcount, including departments with zero employees.

Key points.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

44

Aggregation

How does NULL behave inside aggregate functions like SUM, COUNT, and AVG? Show an example where it matters.

The rule: all aggregate functions *except* COUNT(*) silently ignore NULL values.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

45

Query Writing

Write a query to find customers who placed at least one order in every month of the current year.

Breaking it down.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

46

Query Writing

How do you pivot rows into columns in SQL? Show a practical example.

Approach 1 — Conditional aggregation (most portable).

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

47

Performance

How do you identify which queries are consuming the most resources on the database right now?

Each database has its own tooling, but the approach is the same: look at the query stats views.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

48

Window Functions

Write a query to rank customers by their total spending, with the highest spender ranked 1. Handle ties correctly.

Which to use.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

49

Practical

How do you test a complex stored procedure or SQL function before deploying it to production?

Testing a stored procedure is systematic, not ad-hoc.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

50

Window Functions

Write a query to show month-over-month revenue change — both the absolute difference and the percentage change.

Why NULLIF(..., 0)? If last month's revenue was zero, dividing by it causes a divide-by-zero error. NULLIF returns NULL instead of 0, so the division produces NULL gracefully — which is correct ("undefined % change…

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

1

Transactions

Explain the four SQL transaction isolation levels and which read phenomena each one prevents.

Isolation levels are a trade-off between safety and speed. There are three problems they protect against.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

2

Gaps & Islands

Write a query to find every user who has logged in on 3 or more consecutive days, returning the start date and length of each streak.

This is the classic gaps-and-islands problem. The trick: on a run of back-to-back dates, date − row_number stays the *same*, so we can use it to group each streak.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

3

Concurrency

Your production application is hitting deadlocks under load. How do you diagnose the cause and resolve them?

What a deadlock is: two transactions each hold something the other one needs, so both wait forever. The database notices, picks one as the "loser", and cancels it with an error.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

4

Data Cleanup

How would you delete duplicate rows from a large table while keeping exactly one copy of each, efficiently?

Approach 1 — ROW_NUMBER (the standard, flexible). Number the copies of each email, then delete all but the first.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

5

Performance at Scale

A query that used to be fast has become slow as its table grew to 500M rows. Walk me through your diagnosis and the levers you would pull.

1. Look at the actual query plan. Run EXPLAIN ANALYZE. Compare the *guessed* row counts to the *real* ones — if they're way off, the database's stats are out of date and it's choosing a…

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

6

Transactions

What does ACID stand for in databases?

ACID is four guarantees that keep transactions reliable.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

7

Scaling

What is the difference between sharding, partitioning, and replication?

All three deal with big or busy databases, but solve different problems.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

8

Indexing

How does a database index actually make lookups fast?

Most indexes are a B-tree — a sorted, branching structure. Instead of reading every row, the database starts at the top and follows a few branches down to the value, like a decision tree.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

9

Concurrency

What is the difference between optimistic and pessimistic locking?

Both stop two people from overwriting each other's changes, but in opposite ways.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

10

Architecture

What is the difference between OLTP and OLAP systems?

They're databases tuned for opposite jobs.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

11

Schema Design

When would you deliberately denormalize a database?

Normalisation avoids duplicate data, but it can make reads slow because you join many tables. You denormalise — store some data redundantly — when read speed matters more than tidy storage.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

12

Views

What is the difference between a view and a materialized view?

Use a plain view to simplify and reuse a query; use a materialized view when a heavy query runs often and slightly stale data is acceptable.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

13

Indexing

Does the column order in a composite (multi-column) index matter?

Yes — a lot. A composite index on (a, b) is sorted by a first, then by b within each a. This "leftmost prefix" rule means.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

14

Indexing

What is a covering index?

A covering index contains *every column a query needs* — both the columns it filters on and the columns it returns. Because everything is right there in the index, the database never has to go…

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

15

Distributed Systems

What is the CAP theorem?

For a distributed database (data spread across servers), the CAP theorem says that when a network problem splits the servers apart, you can only keep *two* of these three.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

16

Migrations

How would you add a new NOT NULL column to a huge, live table without downtime?

Doing it in one step can lock the table and take it offline. Instead, break it into safe stages.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

17

Performance

What is the N+1 query problem, and how do you fix it?

It's when code runs 1 query to get a list, then 1 *more* query for each item in that list — so fetching 100 orders fires 1 + 100 = 101 queries. It's slow because…

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

18

Replication

What are read replicas, and what is replication lag?

A read replica is a copy of your main database that handles read queries, taking load off the primary. Writes go to the primary, then copy across to the replicas.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

19

Window Functions

How do you find the top 10% of earners?

Approach 1 — ORDER BY + LIMIT (simplest). Sort by salary and take the top tenth of the row count.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

20

Query Optimization

How does the database decide how to run a query?

Modern databases use a cost-based optimiser. For a given query there are many possible plans — which index to use, which join order, which join method. The optimiser estimates the "cost" of each, based on…

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

21

Durability

How does a database avoid losing committed data in a crash?

Most use a write-ahead log (WAL): before changing the actual data, the database first writes the change to a sequential log on disk and confirms it. So when you COMMIT, the change is safely in…

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

22

Performance

How do you update millions of rows without locking the table for everyone?

Don't do it in one giant statement — that holds locks for ages and bloats the log. Instead, update in small batches in a loop.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

23

Debugging

A query is fast in your dev database but slow in production. What is going on?

Almost always it's about scale and stats, not the SQL itself.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

24

Data Warehousing

In a data warehouse, how do you keep history when a value changes — like a customer moving city?

That's a slowly changing dimension, and the common approach is Type 2: instead of overwriting the old value, you add a *new row* for the customer and mark which one is current.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

25

Reliability

How do you stop a retried request from charging a customer twice?

You make the operation idempotent — safe to run more than once with the same result. The usual trick is an idempotency key: the client sends a unique key with the request, and the database…

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

26

Schema Design

How would you design the database tables for a simple e-commerce site?

Start by finding the entities (the nouns) and the relationships between them.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

27

Data Warehousing

What is the difference between a star schema and a snowflake schema?

Both are data-warehouse layouts with a central fact table (the measurements — sales, clicks) surrounded by dimension tables (the context — date, product, store).

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

28

Design

What is the difference between a soft delete and a hard delete, and when would you use each?

Example (soft delete).

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

29

Performance

Why does OFFSET pagination get slow on large tables, and what is the better approach?

The problem: LIMIT 10 OFFSET 100000 still makes the database *walk through and throw away* the first 100,000 rows before returning 10. The deeper the page, the slower it gets.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

30

Distributed Systems

What is the difference between ACID and BASE?

They're two philosophies for how a system handles data correctness.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

31

Indexing

When should you NOT add an index?

Indexes speed up reads but cost you on writes and storage, so they aren't free. Avoid (or rethink) one when.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

32

Performance

Production database CPU is at 100% and queries are timing out. Walk me through your investigation.

Don't restart first — investigate first. A restart hides the evidence.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

33

Schema Design

You need to migrate a 500-million-row table with zero downtime. How do you approach it?

A full table migration with a lock is not an option at this scale. The approach is a dual-write / shadow table migration.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

34

Indexing

What is a covering index and how does it eliminate a table lookup?

A covering index is an index that contains every column a query needs — so the database can answer the query entirely from the index, without ever touching the actual table rows.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

35

Performance

EXPLAIN shows a table scan despite an index existing on the filtered column. What are the possible reasons?

This is a common senior interview question — there are several reasons the planner might ignore an index.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

36

Schema Design

How do you design a table to store hierarchical data — an org chart or category tree?

There are three main approaches, each with real trade-offs.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

37

Schema Design

How do you design a full audit trail system that records every INSERT, UPDATE, and DELETE on a critical table?

An audit trail must capture who changed what, when, and what the old value was. Two main approaches.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

38

Performance

How do you partition a large table and what types of partitioning are available?

Partitioning splits one large table into smaller physical pieces (partitions) while keeping the logical appearance of one table. The database can then skip entire partitions when querying — called partition pruning.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

39

Concurrency

A long-running query is blocking all other queries. How do you resolve it without taking down the system?

Identify the blocker first.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

40

Security

How do you implement row-level security — different users see different rows of the same table?

Postgres — Row-Level Security (RLS).

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

41

Internals

What is MVCC (Multi-Version Concurrency Control) and why does it mean readers don't block writers in Postgres?

MVCC keeps multiple versions of every row simultaneously — each transaction sees a *snapshot* of the data as it existed at the moment the transaction started, regardless of what other transactions are doing.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

42

Performance

You are asked to tune a legacy database with no documentation, no query history, and no one to ask. Where do you start?

Start by observing, not changing anything.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

43

Schema Design

What is the difference between a regular view and a materialized view, and when do you choose each?

Regular view: a saved query with no stored data. Every time you query it, the underlying SELECT runs fresh. Up to date always; no extra storage; can be as slow as the underlying query.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

44

Incident Management

Your database log file (WAL / transaction log) has filled the disk and the database is now refusing writes. What do you do?

This is a production outage — act fast and methodically.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

45

Architecture

What is connection pooling, why does it matter at scale, and how do you configure it correctly?

Opening a database connection is expensive — it involves authentication, memory allocation, and process/thread creation. At scale, with hundreds of web workers each opening their own connections, this becomes the bottleneck.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

46

Testing

How do you verify a database migration script is safe before running it on production?

A bad migration on a production database with 500M rows can mean hours of downtime or permanent data loss. Verify it rigorously.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

47

Best Practices

How do you write SQL that stays readable and maintainable as the codebase grows?

SQL that works on day one becomes a maintenance nightmare two years later without intentional style. The principles that survive in real teams.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

48

Security

A developer asks for read access to the database. How do you grant access to specific tables only, not the whole database?

Use principle of least privilege — grant only what is needed, nothing more.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

49

Performance

How do you identify and resolve index fragmentation on a production database?

Index fragmentation builds up over time as INSERT, UPDATE, and DELETE operations scatter index pages out of logical order. A fragmented index takes more I/O to traverse.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →

50

Architecture

How do you approach database capacity planning for a system that is growing rapidly?

Capacity planning is about knowing *when* you'll hit a limit — not finding out when the system falls over.

↳ Includes walked-through example, real-world QA scenario & rule of thumb

Get the full answer on QAVeda →