Top 25 Placement Questions - Part 2

1. What is the difference between OLTP and OLAP systems?

Answer: OLTP handles daily, real-time transactions. OLAP handles complex analysis over large amounts of historical data.

OLTP — Online Transaction Processing: This is what a bank or online store uses every second.

Each operation is small and fast, like inserting an order or updating a balance.

The workload is write-heavy.

A single mistake matters, so consistency is critical.

OLAP — Online Analytical Processing: This is what a business analyst uses to answer questions like “what were sales by region last year?”

Each query is big and reads millions of rows.

The workload is read-heavy.

Speed of a single transaction doesn’t matter; the analysis result does.

Key differences table:

	OLTP	OLAP
Full form	Online Transaction Processing	Online Analytical Processing
Purpose	Day-to-day operations	Analysis and reporting
Workload	Many small writes	Large reads
Data	Current, up to date	Historical, accumulated
Example	Banking, shopping carts	Sales reports, trends

Key takeaway: OLTP keeps the business running. OLAP helps understand the business. The same company usually runs both on different databases.

2. Explain the difference between Star Schema and Snowflake Schema.

Answer: Both are ways to organize tables in a data warehouse. A star schema uses denormalized dimension tables. A snowflake schema uses normalized dimension tables.

The core idea: A data warehouse has a central fact table (the numbers, like sales) and surrounding dimension tables (the descriptions, like product and date).

Star Schema: The dimension tables are denormalized. All the details are in one table.

It looks like a star: a center with points around it.

Fewer joins, faster queries.

Snowflake Schema: The dimension tables are normalized. They are split into multiple related tables.

It looks like a snowflake: branches off branches.

Less duplication, but more joins.

Example — the Product dimension:

Star schema — one table:

product_id	product_name	brand	category	supplier
1	Pen	Bic	Stationery	Co A

Snowflake schema — split into three tables:

Products:

product_id	product_name	brand_id	category_id
1	Pen	1	1

Brands:

brand_id	brand_name	supplier
1	Bic	Co A

Categories:

category_id	category_name
1	Stationery

Key differences table:

	Star Schema	Snowflake Schema
Dimension tables	Denormalized	Normalized
Duplication	More	Less
Joins needed	Fewer	More
Query speed	Faster	Slower
Storage	More	Less

Key takeaway: Use a star schema for fast, simple queries. Use a snowflake schema when you want to reduce redundancy and storage.

3. What is a Surrogate Key, and why is it used?

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

Natural key vs surrogate key: A natural key is a real-world value, like email or phone number.

The problem: natural keys can change. A person changes their email, or two people share a phone number.

A surrogate key is just a number the database creates, like 1, 2, 3.

It never changes and never repeats.

Example:

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

Here customer_id is the surrogate key.

email could be a natural key, but it can change, so it’s not safe as the primary key.

Why surrogate keys are safer:

• They never change over time.
• They’re simple numbers, fast to index.
• The real data (like email) can change without breaking references.

Key differences table:

	Natural key	Surrogate key
Based on	Real-world data	System-generated
Can change	Yes	No
Example	Email, SSN	Auto-increment number
Safe as a primary key	Not always	Yes

Key takeaway: Use a surrogate key when the natural key is unstable, like an email that users can change. It keeps all references stable.

4. Explain the ACID properties of a database transaction.

Answer: ACID stands for Atomicity, Consistency, Isolation, and Durability. Together they make database transactions reliable.

Atomicity — all or nothing: A transaction is a group of steps.

Either every step succeeds, or none do.

If the system crashes mid-way, everything rolls back.

Example: transferring money from A to B. Both the debit from A and the credit to B must happen together. If the credit fails, the debit is undone.

Consistency — always valid: The database must move from one valid state to another.

Rules like constraints and keys are always respected.

A transaction can’t leave the data half-broken.

Isolation — no interference: Transactions running at the same time don’t mess with each other.

One transaction’s unfinished changes aren’t visible to others.

Durability — permanent: Once a transaction is committed, the data is saved forever.

Even a power failure won’t lose it.

Key takeaway: ACID is why banks can trust their databases. Atomicity and Isolation handle crashes and concurrency; Consistency keeps data valid; Durability ensures nothing is lost.

5. What is a Deadlock, and how can it be prevented?

Answer: A deadlock happens when two transactions wait on each other. Each holds a lock the other needs, so neither can finish.

The classic example: Transaction 1 locks Table A and wants Table B.

Transaction 2 locks Table B and wants Table A.

Each waits for the other to release. Neither can continue. Stuck forever.

How the database handles it: The database detects the deadlock and picks one transaction to cancel (the victim).

That frees the locks, and the other transaction finishes.

The cancelled transaction is rolled back, and the app should retry it.

Prevention tips:

1. Access tables in the same order everywhere: Always lock A then B. Then two transactions can never hold opposite locks.

2. Keep transactions short: The less time a transaction holds locks, the smaller the window for a deadlock.

3. Commit quickly: Don’t pause for user input inside a transaction.

Key takeaway: Deadlocks are a lock-waiting cycle. Prevent them with consistent access order and short transactions, and write app retry logic for the rare cases that slip through.

6. What is the difference between Optimistic and Pessimistic locking?

Answer: Pessimistic locking assumes conflicts will happen and locks resources in advance. Optimistic locking assumes no conflict and checks only at the end.

Pessimistic locking: Before reading or writing, the transaction locks the row.

Others are blocked until the lock is released.

This guarantees no conflict, but reduces concurrency.

Used when conflicts are frequent or expensive.

Optimistic locking: Nobody locks anything. Everyone reads and works freely.

At the end, before committing, the transaction checks whether the data changed since it was read.

If it changed, the update is rejected and the app retries.

Used when conflicts are rare.

Example — optimistic version check:

UPDATE flights
SET seats = seats - 1, version = version + 1
WHERE flight_id = 101 AND version = 5;

If another user already changed the version, the update affects zero rows and the app knows to retry.

Key differences table:

	Pessimistic	Optimistic
Locks before working	Yes	No
Blocks other users	Yes	No
Checks at the end	No	Yes
Best when	Conflicts frequent	Conflicts rare
Concurrency	Lower	Higher

Key takeaway: Use pessimistic locking for critical systems with frequent clashes. Use optimistic locking for web apps where conflicts are rare, so users aren’t blocked.

7. What is a Self-Join, and when would you use it?

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

Why join a table to itself? Sometimes the relationship you need is inside one table.

The classic example is employees and their managers — both live in the same employees table.

Example — employees table:

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

To show each employee’s manager, join the table to itself:

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

We used the table twice: once as e (employees) and once as m (managers). That’s the self-join.

Key takeaway: Use a self-join whenever the comparison is between rows of the same table — hierarchies, rankings, or finding pairs. Always use aliases so the two copies have clear names.

8. What is a Cross-Join?

Answer: A cross join matches every row in the first table with every row in the second. The result is called a Cartesian product.

How it works: If table A has 3 rows and table B has 4 rows, the result has 3 × 4 = 12 rows.

Every possible pair appears.

Example:

Colors table:

color
Red
Green

Sizes table:

size
S
M

SELECT * FROM colors CROSS JOIN sizes;

Result:

color	size
Red	S
Red	M
Green	S
Green	M

2 × 2 = 4 rows.

When is it used? Sometimes on purpose, like generating all combinations for testing.

Most of the time it’s a mistake — a join without a condition that accidentally creates millions of rows.

Key takeaway: A cross join produces every combination of rows. The row count is the product of both tables, so be careful — it grows fast.

9. What are the Set Operators (INTERSECT, EXCEPT/MINUS)?

Answer: Set operators combine the results of two queries. The main ones are UNION, UNION ALL, INTERSECT, and EXCEPT (also called MINUS).

The sample data:

Set A: {Ali, Bob}

Set B: {Bob, Cam}

UNION — everything in either set, no duplicates: Result: {Ali, Bob, Cam}

INTERSECT — only what’s in both sets:

SELECT name FROM employees
INTERSECT
SELECT name FROM managers;

Result: {Bob}

EXCEPT (MINUS) — in the first set but not the second:

SELECT name FROM employees
EXCEPT
SELECT name FROM managers;

Result: {Ali}

Example of all three together:

Operator	A = {Ali, Bob}, B = {Bob, Cam}	Result
UNION	{Ali, Bob, Cam}	Both, no dupes
INTERSECT	{Bob}	Only common
EXCEPT	{Ali}	Only in A

Key takeaway: Use INTERSECT for common rows and EXCEPT for rows that exist in only one query. Both require the queries to have matching column counts and types.

10. What is the difference between a standard View and a Materialized View?

Answer: A standard view is just a saved query. A materialized view stores the query’s result physically.

Standard view: It’s like a saved SELECT statement.

It stores no data.

Every time you query it, the database runs the query again against the base tables.

The data is always fresh.

Materialized view: It runs the query once and stores the result on disk.

Later queries read the stored result, no re-running.

This is much faster for complex queries.

But the stored result can go stale. It only updates when refreshed.

-- Some databases
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT department_id, SUM(salary) AS total
FROM employees
GROUP BY department_id;

Key differences table:

	Standard view	Materialized view
Stores data	No	Yes
Runs query each time	Yes	No
Always fresh	Yes	No, needs refresh
Speed for complex queries	Slower	Faster
Uses storage	No	Yes

Key takeaway: Use a standard view for a simple, always-fresh saved query. Use a materialized view for heavy analytical queries where speed matters more than instant freshness.

11. How do you use the EXPLAIN command?

Answer: EXPLAIN shows the query execution plan — how the database intends to run your query.

What it tells you:

• Whether the database scans the whole table.
• Which indexes it uses.
• Where the expensive operations are.

Example:

EXPLAIN SELECT * FROM employees WHERE department_id = 5;

The output might say “Seq Scan on employees” (a full table scan) or “Index Scan using idx_emp_dept” (an index is used).

Why it matters: If you see a full table scan on a large table, you know you need an index.

After adding the index, run EXPLAIN again to confirm the plan changed.

EXPLAIN ANALYZE actually runs the query and shows real timings.

Key takeaway: EXPLAIN is the first tool for optimizing slow queries. It shows the plan; you fix the bottleneck; re-check and repeat.

12. Why is SELECT * generally discouraged?

Answer: SELECT * fetches every column from the table. That causes unnecessary work and can break your app.

The problems:

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

This increases network traffic and memory use.

2. Breaks on schema change: If someone adds or renames a column, the result set changes.

Code that assumed a fixed column order can break.

3. Can’t use covering indexes efficiently: An index that contains exactly the columns you need can answer a query without touching the table. SELECT * forces a trip to the full table.

Good vs bad:

-- Bad: pulls everything
SELECT * FROM employees;

-- Good: only what's needed
SELECT name, salary FROM employees;

Key takeaway: Always list the columns you actually need. It’s faster, uses less memory, and protects against schema changes.

13. 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 and handle so many queries.

When a single database can’t keep up, you split it.

How it works: Rows of the same table are distributed across shards.

Each shard stores a subset of the rows.

Example: A users table with 10 million rows split across 5 servers:

• Shard 1: users 1–2,000,000
• Shard 2: users 2,000,001–4,000,000
• and so on

How rows are assigned: Usually by a key, like user ID.

A simple rule: shard = user_id % 5.

User 7 goes to shard 2, user 12 goes to shard 2 as well (7%5=2, 12%5=2).

The trade-offs:

• Queries that need data across shards (like joins or global searches) become hard.
• If one shard fails, part of the data is unavailable.

Key differences table (vs vertical scaling):

	Sharding (horizontal)	Bigger server (vertical)
Adds	More servers	More CPU/RAM on one
Scale limit	Very high	Hardware limit
Cost	Complex to manage	Simple but expensive

Key takeaway: Sharding is a powerful scaling technique for very large data. It spreads load across machines, but makes cross-shard queries harder.

14. How do you identify and remove duplicate records from a table?

Answer: First find duplicates with GROUP BY and HAVING. Then delete all but one copy using ROW_NUMBER() with a CTE.

Step 1 — find the duplicates: Users table:

id	email
1	ali@mail.com
2	bob@mail.com
3	ali@mail.com

SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Result: ali@mail.com appears 2 times.

Step 2 — remove the duplicates:

WITH ranked AS (
  SELECT id,
         ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
  FROM users
)
DELETE FROM users
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

ROW_NUMBER() numbers each row inside its email group. The first row (rn = 1) is kept; the rest (rn > 1) are deleted.

Step 3 — prevent them:

ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

Key takeaway: Find with GROUP BY/HAVING, delete with ROW_NUMBER() in a CTE, and lock the door with a UNIQUE constraint afterward.

15. What is a Stored Procedure, and how does it differ from a User-Defined Function?

Answer: A stored procedure is a saved block of SQL that can run complex logic and modify data. A user-defined function is designed to return a single value or table and is used inside queries.

Stored procedure: A named group of SQL statements saved in the database.

It can run INSERT, UPDATE, and DELETE.

It can return multiple values and result sets.

It’s called with EXEC or CALL.

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;

User-defined function: Designed to compute and return a value.

It’s used inside a SELECT, like a built-in function.

It usually can’t modify data.

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 purpose	Run logic, modify data	Return a value
Can use INSERT/UPDATE/DELETE	Yes	Usually no
Return type	Multiple values/result sets	Single value/table
Used inside SELECT	No	Yes
Called with	CALL / EXEC	As part of an expression

Key takeaway: Procedures do the work; functions return values. If you need to modify data or run steps, use a procedure. If you need a reusable value inside a query, use a function.