Top 50 Placement Questions - Part 2

1. What is a Common Table Expression (CTE)?

Answer: A CTE is a temporary, named result set that exists only during a query. It makes complex queries easier to read and reuse.

The idea: You give a name to a sub-query using WITH.

Then the main query uses that name.

It’s like a temporary variable inside the query.

Example:

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

Step by step:

1. it_staff holds all department-5 employees.
2. The main query selects high earners from that set.

Why use a CTE:

• Better readability.
• Can reuse the same result multiple times.
• Supports recursion.

Key takeaway: A CTE is a named temporary result set. Use it to break a big query into readable, reusable pieces.

2. What is a Self-Join and when is it used?

Answer: A self-join joins a table with itself. It’s used for hierarchical data or comparing rows within the same table.

The idea: Sometimes the relationship lives inside one table.

The classic case: employees and their managers are both in the employees table.

Example:

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

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.employee_id;

Result:

employee	manager
Ali	(none)
Bob	Ali
Cam	Ali

The table appears twice — once as e, once as m.

Key takeaway: Use a self-join when rows in one table relate to other rows in the same table. Always use aliases so the two copies are clear.

3. What is the purpose of the COALESCE() function?

Answer: COALESCE() returns the first non-NULL value from a list of arguments.

The problem it solves: Columns often contain NULL, meaning missing data.

You want to show a fallback value instead.

Example:

SELECT name, COALESCE(phone, 'No phone') FROM customers;

Customers table:

name	phone
Ali	12345
Bob	(NULL)

Result:

name	phone
Ali	12345
Bob	No phone

Bob’s NULL became the fallback “No phone”.

Key takeaway: COALESCE() checks its arguments left to right and returns the first one that isn’t NULL. Perfect for defaulting missing values.

4. What is a Transaction in SQL?

Answer: A transaction is a sequence of operations treated as one single unit. Either all of them succeed, or none do.

The idea: Some operations must happen together or not at all.

Example — a money transfer: Debit 5000 from Account A.

Credit 5000 to Account B.

If the credit fails, the debit must not stay.

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
UPDATE accounts SET balance = balance + 5000 WHERE id = 2;
COMMIT;

If anything fails before COMMIT, a ROLLBACK undoes it all.

Why it matters: This is Atomicity — one of the ACID properties.

A partial transfer would make the accounts inconsistent.

Key takeaway: A transaction groups statements into one all-or-nothing unit. Use COMMIT to save, ROLLBACK to undo.

5. What is the difference between a Stored Procedure and a User-Defined Function?

Answer: A stored procedure can run complex logic and modify data. A function returns a single value or table and is used inside queries.

Stored procedure: A saved block of SQL.

Can run INSERT, UPDATE, DELETE.

Can return multiple values.

Called with CALL or EXEC.

Function: Designed to return a value.

Used inside SELECT, like built-in functions.

Usually can’t modify data.

Example procedure:

CREATE PROCEDURE update_salary(IN emp_id INT, IN new_salary DECIMAL)
BEGIN
  UPDATE employees SET salary = new_salary WHERE employee_id = emp_id;
END;

Example function:

CREATE FUNCTION full_name(first VARCHAR(50), last VARCHAR(50))
RETURNS VARCHAR(100)
RETURN CONCAT(first, ' ', last);

Used as: SELECT full_name('Ali', 'Khan');

Key differences table:

	Stored Procedure	Function
Main job	Run logic, modify data	Return a value
Can modify data	Yes	Usually no
Used inside SELECT	No	Yes
Returns	Multiple values/sets	Single value/table

Key takeaway: Procedures do the work, functions return values. Pick based on whether you’re modifying data or computing a value.

6. What is a Cross Join?

Answer: A cross join returns the Cartesian product — every row of one table paired with every row of the other.

How it works: No join condition is used.

Every possible combination is produced.

Example:

Colors: {Red, Green}

Sizes: {S, M}

SELECT * FROM colors CROSS JOIN sizes;

Result:

color	size
Red	S
Red	M
Green	S
Green	M

2 × 2 = 4 rows.

The danger: 3 rows × 4 rows = 12 rows.

1,000 rows × 1,000 rows = 1,000,000 rows.

It grows fast and is usually a mistake.

Key takeaway: A cross join creates every combination of rows. The result count is the product of both tables — rarely useful, often accidental.

7. What is Database Sharding?

Answer: Sharding splits a large database horizontally across multiple servers. Each part is called a shard.

Why shard: One server can only hold so much data.

When it maxes out, you split the data across machines.

How it works: Rows are divided among shards, usually by a key.

Example — users split across 3 servers:

• Shard 1: users 1–1,000,000
• Shard 2: users 1,000,001–2,000,000
• Shard 3: users 2,000,001–3,000,000

A common assignment rule: shard = user_id % number_of_shards

User 7 → shard 1 (7 % 3 = 1).

The costs:

• Joins across shards are hard.
• One shard failing takes part of the data offline.
• Rebalancing is complex.

Key takeaway: Sharding spreads a huge database over many servers for scale. It’s powerful, but cross-shard queries become tricky.

8. What is a Trigger?

Answer: A trigger is a database object that automatically runs code when an INSERT, UPDATE, or DELETE happens.

The idea: You attach code to a table event.

When the event fires, the code runs automatically.

No one has to call it.

Example — log every delete:

CREATE TRIGGER log_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (employee_id, action) VALUES (OLD.employee_id, 'deleted');
END;

Now every delete also writes an audit entry.

Common uses:

• Enforce business rules.
• Audit changes automatically.
• Keep summary tables up to date.

Key takeaway: A trigger runs automatically on table events. It’s good for audit logs and rules — but use sparingly, since hidden code can surprise you.

9. What is a Materialized View?

Answer: A materialized view stores the result of a query physically. A standard view just re-runs the query every time.

Standard view: A saved query.

No stored data.

Fresh every time, but re-runs each call.

Materialized view: The result is saved on disk.

Queries read the saved result — fast.

But it can go stale until refreshed.

Example:

CREATE MATERIALIZED VIEW dept_salary_totals AS
SELECT department_id, SUM(salary) AS total
FROM employees
GROUP BY department_id;

The totals are stored. Later queries don’t re-aggregate.

Refresh it when new data arrives:

REFRESH MATERIALIZED VIEW dept_salary_totals;

Key differences table:

	Standard view	Materialized view
Stores data	No	Yes
Always fresh	Yes	No
Speed	Re-runs each time	Reads stored result
Storage	None	Used

Key takeaway: Use a materialized view for heavy analytical queries where speed beats instant freshness. Refresh it when the data changes.

10. What are Window Functions?

Answer: Window functions do calculations across a set of related rows while keeping every row in the result.

The difference from normal aggregates: SUM() with GROUP BY collapses rows into one summary.

A window function keeps all rows and adds the calculation as a new column.

Example — rank employees by salary:

SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees;

Result (keeps every row):

name	salary	rnk
Ali	90000	1
Bob	80000	2
Cam	70000	3

Common window functions:

• RANK(), DENSE_RANK(), ROW_NUMBER()
• LAG(), LEAD()
• SUM() OVER (...), AVG() OVER (...)

Key takeaway: Window functions calculate across rows without collapsing them. Great for rankings, running totals, and comparisons.

11. What is a Correlated Subquery?

Answer: A correlated subquery depends on the outer query. It references outer columns and runs once for every row.

The difference from a regular subquery: A regular subquery runs once, on its own.

A correlated subquery uses a value from each outer row, so it runs again and again.

Example — employees earning 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 each employee, the inner query recomputes that department’s average.

Why it’s slow: 1000 employees → the inner query runs up to 1000 times.

Key takeaway: Correlated subqueries are flexible but slow, since they re-run per row. Use a JOIN or window function when performance matters.

12. Why is ‘SELECT *’ discouraged in production?

Answer: SELECT * fetches every column, which wastes resources and can break when the schema changes.

The problems:

1. Unneeded data: If a table has 30 columns and you need 2, SELECT * pulls all 30.

More network traffic, more memory.

2. Breaks on schema change: Adding or renaming a column changes the result.

Code that assumed the old shape breaks.

3. Blocks covering indexes: An index with exactly your needed columns can answer a query alone. SELECT * forces a trip to the full table.

Good vs bad:

-- Bad
SELECT * FROM employees;

-- Good
SELECT name, salary FROM employees;

Key takeaway: List the columns you need. Faster, lighter, and safer against schema changes.

13. What is the difference between OLTP and OLAP?

Answer: OLTP handles quick, daily transactions. OLAP handles complex analysis over large historical data.

OLTP: Online Transaction Processing.

Banking, shopping, booking.

Small, fast, frequent writes.

Consistency is critical.

OLAP: Online Analytical Processing.

Sales reports, trends, forecasting.

Large, slow, read-heavy queries.

Analyzes accumulated history.

Key differences table:

	OLTP	OLAP
Purpose	Daily operations	Analysis
Queries	Many small writes	Few large reads
Data	Current	Historical
Example	Insert order	Yearly sales report

Key takeaway: OLTP runs the business; OLAP understands it. Different workloads, usually on separate databases.

14. What is a Surrogate Key?

Answer: A surrogate key is an artificial, system-generated identifier. It’s used when there’s no stable natural key.

The idea: Instead of relying on real data (like email), the database creates its own number.

It never changes and never repeats.

Example:

CREATE TABLE customers (
  customer_id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255)
);

customer_id is the surrogate key.

Why not use email as the key? Emails can change.

Users can delete and recreate accounts.

A surrogate number stays stable forever.

Key differences table:

	Natural key	Surrogate key
Source	Real data	Generated
Can change	Yes	No
Example	Email, phone	Auto-increment ID

Key takeaway: A surrogate key is a stable, system-made ID. Use it when the natural key might change over time.

15. What is a Deadlock and how to prevent it?

Answer: A deadlock happens when two transactions each hold a lock the other needs. Both wait forever.

The classic scenario: Transaction 1 locks Table A, wants Table B.

Transaction 2 locks Table B, wants Table A.

Each waits for the other to release. Neither can move.

How databases handle it: The database detects the deadlock and cancels one transaction (the victim).

It rolls back and the other transaction proceeds.

The app should retry the cancelled one.

Prevention tips:

• Access tables in the same order everywhere.
• Keep transactions short.
• Commit quickly.

Key takeaway: Deadlocks are lock-waiting cycles. Prevent them with consistent access order and short transactions, plus retry logic in the app.