Top 25 Placement Questions - Part 1

1. What is the difference between a Clustered and a Non-Clustered index?

Answer: A clustered index decides the physical order of the data rows in a table. A non-clustered index is a separate structure that points to the data rows.

Clustered index: Think of a dictionary. The words are physically sorted alphabetically. The book is stored in that order.

A clustered index works the same way. The actual data rows are stored sorted by the indexed column.

Because the data itself is rearranged, a table can have only one clustered index.

A primary key is usually the clustered index.

Non-clustered index: Think of the index at the back of a textbook. It’s a separate list that says “topic X is on page 42”. The book’s pages aren’t rearranged.

A non-clustered index is a separate structure. It holds the indexed column values and pointers to the actual data rows.

A table can have many non-clustered indexes.

Key differences table:

	Clustered	Non-clustered
Sorts the actual data	Yes	No
Separate structure	No	Yes
How many per table	One	Many
What the primary key usually is	Yes	No
Speed of lookup	Faster	Slower (extra pointer step)

Key takeaway: Clustered indexes rearrange the table itself, so there can be only one. Non-clustered indexes are extra lookup tables, so you can have many.

2. How does an index improve performance, and what are its drawbacks?

Answer: An index speeds up read operations by letting the database find rows quickly. But it slows down write operations and takes extra storage.

How it helps reads: Without an index, the database must read every row to find what you need. That’s called a full table scan.

With an index, the database jumps straight to the matching rows, like using a book’s index instead of reading every page.

Example — without and with an index:

SELECT * FROM employees WHERE department_id = 5;

Without an index, the database scans all one million rows.

With an index on department_id, it finds the matching rows almost instantly.

The drawbacks: Every INSERT, UPDATE, and DELETE must also update the index.

More indexes mean more work on every write.

Each index also uses disk space.

Key differences table:

	With index	Without index
SELECT speed	Fast	Slow on big tables
INSERT/UPDATE/DELETE speed	Slower	Faster
Storage used	Extra	None

Key takeaway: Indexes trade write speed and storage for read speed. Add them on columns you actually query often, not on every column.

3. When should you avoid creating an index?

Answer: Avoid indexes on small tables, on frequently updated tables, and on columns with very few unique values.

1. Small tables: If a table has only 50 rows, the database can read all of them instantly.

An index adds overhead without helping. Scanning the whole table is already fast.

2. Frequently updated tables: Every insert, update, and delete must maintain the index.

If writes happen constantly, the index maintenance cost can be higher than the speed gain.

3. Low-cardinality columns: Cardinality means how many unique values a column has.

A Gender column has only two unique values: male and female.

An index on it doesn’t help much, because almost every row matches. The database might still scan the whole table.

Key takeaway: Only index columns that are highly selective (many unique values) and queried often. Indexing everything slows the database down.

4. What is a Correlated Subquery and how does it differ from a regular subquery?

Answer: A regular subquery runs once and doesn’t depend on the outer query. A correlated subquery depends on the outer query and runs once for every row.

Regular subquery: The inner query runs first, once. Its result is then used by the outer query.

SELECT name
FROM employees
WHERE department_id = (SELECT id FROM departments WHERE name = 'IT');

The inner query finds the IT department’s ID once. Then the outer query uses it.

Correlated subquery: The inner query references a column from the outer query.

Because of that, it must run again for every row of the outer query.

Example — find employees who earn more than their department’s average:

SELECT name, salary
FROM employees e
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department_id = e.department_id
);

For every employee, the inner query recomputes the average salary of that employee’s department.

If there are 1000 employees, the inner query runs up to 1000 times.

Key differences table:

	Regular subquery	Correlated subquery
Depends on outer query	No	Yes
Runs how many times	Once	Once per row
Speed	Faster	Slower
Typical use	Lookup a fixed value	Compare each row with a related value

Key takeaway: Correlated subqueries are powerful but slow. Use a JOIN or a window function when performance matters.

5. When would you use a Subquery instead of a JOIN?

Answer: Use a subquery when you need to filter or aggregate data before it’s combined, or when you need a value to compare against. Use a JOIN when you want to combine rows from multiple tables side by side.

When a subquery is better:

1. When you need one single value:

SELECT name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

A subquery cleanly provides the average to compare against. A JOIN can’t easily do this.

2. When you need to aggregate before joining:

SELECT d.department_name, t.total
FROM departments d
JOIN (
  SELECT department_id, COUNT(*) AS total
  FROM employees
  GROUP BY department_id
) t ON d.department_id = t.department_id;

The subquery builds a small summary first, then joins it.

When a JOIN is better: Use a JOIN when you need columns from both tables side by side.

Joins are usually faster and easier to read for that case.

Key differences table:

	Subquery	JOIN
Returns a value to compare	Yes, easily	Awkward
Combines rows side by side	Possible but clunky	Yes
Usually faster	No	Yes
Readability for complex logic	Good	Good

Key takeaway: Use a subquery to compute a value or pre-aggregate. Use a JOIN to combine rows. Many queries can use either — pick the clearer one.

6. What is a Common Table Expression (CTE) and why use it over a subquery?

Answer: A CTE is a named, temporary result set that exists only for the duration of a query. It’s easier to read than a subquery and supports recursion, which a normal subquery can’t do.

What a CTE looks like:

WITH it_staff AS (
  SELECT * FROM employees WHERE department_id = 5
)
SELECT * FROM it_staff WHERE salary > 50000;

WITH gives a name to a sub-query. Then the main query uses that name.

Why use a CTE over a subquery:

1. Better readability: You can name the intermediate result. The main query reads like a sentence instead of nested parentheses.

2. Reuse the same result multiple times:

WITH high_earners AS (
  SELECT * FROM employees WHERE salary > 100000
)
SELECT * FROM high_earners
UNION
SELECT * FROM high_earners WHERE department_id = 5;

high_earners is used twice. With a subquery, you’d have to repeat the whole query.

3. Recursion: A CTE can call itself, which lets you build things like a manager chain.

WITH RECURSIVE chain AS (
  SELECT employee_id, manager_id FROM employees WHERE employee_id = 1
  UNION ALL
  SELECT e.employee_id, e.manager_id
  FROM employees e
  JOIN chain c ON e.manager_id = c.employee_id
)
SELECT * FROM chain;

A normal subquery cannot do this.

Key differences table:

	CTE	Subquery
Named	Yes	No
Can reuse result	Yes	No (repeat it)
Supports recursion	Yes	No
Readability	Better	Can get nested

Key takeaway: Use a CTE when a query is complex, when you reuse the same result, or when you need recursion. For simple one-off lookups, a subquery is fine.

7. How do you write a Recursive CTE?

Answer: A recursive CTE has two parts: an anchor member (the starting point) and a recursive member (which references the CTE itself), joined by UNION ALL.

The structure:

WITH RECURSIVE name AS (
  -- anchor member: starting rows
  SELECT ...
  UNION ALL
  -- recursive member: refers to the CTE
  SELECT ... FROM name WHERE ...
)
SELECT * FROM name;

Simple example — counting from 1 to 5:

WITH RECURSIVE numbers (n) AS (
  SELECT 1                      -- anchor
  UNION ALL
  SELECT n + 1 FROM numbers
  WHERE n < 5                   -- termination condition
)
SELECT * FROM numbers;

Result:

n
1
2
3
4
5

How it runs step by step:

1. The anchor member returns 1.
2. The recursive member takes 1, adds 1, and returns 2.
3. This repeats until n < 5 is false.
4. All the rows are combined with UNION ALL.

Practical example — finding a manager chain: Employees table:

employee_id	name	manager_id
1	Ali	(none)
2	Bob	1
3	Cam	2

WITH RECURSIVE chain AS (
  SELECT employee_id, name, manager_id FROM employees WHERE employee_id = 3
  UNION ALL
  SELECT e.employee_id, e.name, e.manager_id
  FROM employees e
  JOIN chain c ON e.employee_id = c.manager_id
)
SELECT * FROM chain;

This walks from Cam up to Ali.

Key takeaway: Always include an anchor, a recursive member, and a termination condition. Without the termination condition, the query runs forever.

8. What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?

Answer: All three assign a number to each row based on an ordering. The difference is how they handle ties (equal values).

The sample data:

name	score
Ali	90
Bob	90
Cam	85

ROW_NUMBER() — gives every row a unique number, even ties.

name	score	row_number
Ali	90	1
Bob	90	2
Cam	85	3

RANK() — ties share a rank, and the next rank skips numbers.

name	score	rank
Ali	90	1
Bob	90	1
Cam	85	3

The next rank after the tie is 3, not 2.

DENSE_RANK() — ties share a rank, but the next rank does not skip.

name	score	dense_rank
Ali	90	1
Bob	90	1
Cam	85	2

The next rank is 2.

Example:

SELECT name, score,
       ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
       RANK() OVER (ORDER BY score DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk
FROM scores;

Key differences table:

	ROW_NUMBER()	RANK()	DENSE_RANK()
Unique number for every row	Yes	No	No
Gaps after ties	No	Yes	No
Ties share the same number	No	Yes	Yes

Key takeaway: Use ROW_NUMBER() when every row needs a unique number. Use RANK() when gaps are fine. Use DENSE_RANK() when ranks must be consecutive, like finding the Nth highest value.

9. Explain the use of LAG() and LEAD() window functions.

Answer: LAG() lets you access data from a previous row. LEAD() lets you access data from the next row. They let you compare a row with its neighbors without a self-join.

The sample data — daily sales:

date	sales
2026-01-01	100
2026-01-02	150
2026-01-03	120

LAG() — get the previous day’s sales:

SELECT date, sales,
       LAG(sales) OVER (ORDER BY date) AS prev_day_sales
FROM daily_sales;

Result:

date	sales	prev_day_sales
2026-01-01	100	(none)
2026-01-02	150	100
2026-01-03	120	150

LEAD() — get the next day’s sales:

SELECT date, sales,
       LEAD(sales) OVER (ORDER BY date) AS next_day_sales
FROM daily_sales;

Result:

date	sales	next_day_sales
2026-01-01	100	150
2026-01-02	150	120
2026-01-03	120	(none)

Practical use — day-over-day change:

SELECT date, sales,
       sales - LAG(sales) OVER (ORDER BY date) AS change_from_yesterday
FROM daily_sales;

Optional offset: LAG(sales, 2) would look back two rows instead of one.

Key takeaway: LAG() looks back, LEAD() looks forward. Both compare rows without the complexity of a self-join.

10. What is Denormalization, and when is it appropriate to use?

Answer: Denormalization is the intentional addition of duplicate data to a database. It trades storage and consistency for faster reads, by reducing the number of joins.

The problem with fully normalized data: A normalized database splits data into many small tables linked by keys.

Reading that data often requires joining several tables together.

On a huge, read-heavy system, those joins get slow.

How denormalization helps: Instead of joining, you store the already-combined data in one place.

Example — before and after:

Normalized: to show an order with the customer name, you join Orders with Customers.

Denormalized: you add the customer name directly into the Orders table.

order_id	customer_name	product
1	Ali	Pen
2	Ali	Book

Reading is now a single table scan, no join.

The costs:

• The customer name is duplicated across rows.
• If Ali changes her name, every row must be updated.
• You risk inconsistency if an update is missed.

When to use it:

• When reads vastly outnumber writes.
• When reporting queries join many tables.
• When the joins are the bottleneck.

Key takeaway: Normalize for data integrity. Denormalize for read speed. Use it deliberately on read-heavy reporting systems, not everywhere.