DBMS & SQL

COMPLETE INTERVIEW MASTER GUIDE

From Basics to IBM Level | Theory + Queries + Cross Questions
 TABLE OF CONTENTS

CHAPTER 1: Database Fundamentals — DBMS, RDBMS, Architecture

CHAPTER 2: Keys in DBMS — Primary, Foreign, Candidate, Composite, Super

CHAPTER 3: Normalization — 1NF to BCNF with Examples

CHAPTER 4: ACID Properties & Transactions — Deep Dive

CHAPTER 5: Concurrency Control — Locks, Deadlock, Isolation Levels

CHAPTER 6: Indexing & Query Optimization

CHAPTER 7: SQL Commands — DDL, DML, DQL, TCL, DCL

CHAPTER 8: SQL Clauses — WHERE, GROUP BY, HAVING, ORDER BY

CHAPTER 9: SQL Joins — All Types with Diagrams & Examples

CHAPTER 10: Subqueries, Views, Stored Procedures & Triggers

CHAPTER 11: Window Functions — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD

CHAPTER 12: Top 40 SQL Interview Queries with Explanations

CHAPTER 13: Advanced Topics — Partitioning, Sharding, CAP Theorem

CHAPTER 14: IBM-Level Cross Questions & Model Answers

 CHAPTER 1
Database Fundamentals — DBMS, RDBMS, Architecture

1.1 What is a Database?

A Database is an organised collection of structured data stored electronically. It allows data to be easily
accessed, managed, updated, and retrieved. Unlike a plain file system, a database provides querying,
relationships, and data integrity.

1.2 What is DBMS?

A Database Management System (DBMS) is software that acts as an interface between
users/applications and the database. It handles storage, retrieval, security, and integrity of data.

Why DBMS over File System?

• Eliminates data redundancy and inconsistency
• Provides data independence (logical & physical)
• Supports concurrent access by multiple users
• Ensures data security through access controls
• Provides backup & recovery mechanisms
• Supports ACID-compliant transactions
• Enables complex queries using SQL

REAL-WORLD EXAMPLE

Example: A banking system uses DBMS to store customer details, account info, and
transactions. Multiple ATMs access the same database concurrently without data
corruption — possible only because of DBMS concurrency control.

Cross Questions (IBM may ask these):

Q: Is Excel a DBMS?

A: No. Excel lacks relationships, ACID properties, multi-user concurrency, and SQL
querying. It is a spreadsheet tool, not a DBMS.

Q: Difference between DBMS and File System?

A: File System stores raw files with no relationships, no query language, no concurrency
control, and no transaction support. DBMS provides all of these plus data integrity and
security.
 Q: Name some popular DBMS software.

A: Oracle, MySQL, PostgreSQL, Microsoft SQL Server, IBM Db2, SQLite, MongoDB
(NoSQL).

1.3 What is RDBMS?

A Relational DBMS (RDBMS) stores data in tables (relations) with rows and columns. Tables are linked
using keys. It is based on Codd's 12 rules and uses SQL as its query language.

Feature DBMS RDBMS

Data Storage Files / hierarchical Tables (relations)

Relationships No formal relationship Keys link tables

Normalisation Not enforced Enforced

ACID May not support Fully supported

Examples XML DB, IMS MySQL, Oracle, Db2

1.4 Three-Level Architecture (ANSI/SPARC)

DBMS uses a three-level architecture to achieve data independence:
External Level (View Level): What individual users see. Each user/app may see a different view of
the same data.
Conceptual Level (Logical Level): The complete logical structure of the entire database — tables,
columns, relationships, constraints.
Internal Level (Physical Level): How data is physically stored on disk — file organisation, indexes,
storage blocks.

KEY CONCEPT

Data Independence: Changes at the physical level (e.g. changing storage format) should
NOT affect the conceptual or external level. This is a key design goal of ANSI/SPARC
architecture.
 CHAPTER 2
Keys in DBMS

Can be
Key Type Definition Example
NULL?

Primary Key Uniquely identifies each row No emp_id

Foreign Key References PK of another table Yes dept_id in Employee

Candidate Key Any column(s) that could be PK No emp_id, email

Set of attributes that uniquely identify

Super Key No {emp_id, name}
a row

Composite Key PK made of 2+ columns No (order_id, product_id)

Alternate Key Candidate Key not chosen as PK No email (if emp_id is PK)

Surrogate Key System-generated artificial key No Auto-increment ID

Natural Key Real-world attribute used as key No Aadhar number

Key Rules to Remember:

• A table can have only ONE Primary Key but multiple Candidate Keys.
• A Foreign Key CAN be NULL (representing an optional relationship).
• A Foreign Key CAN have duplicate values (many-to-one relationship).
• A Composite Key is needed when no single column uniquely identifies a row.
• Every Primary Key is a Super Key, but not every Super Key is a Primary Key.

Cross Questions:
Q: Can a Foreign Key reference a non-Primary Key column?

A: Yes, it can reference any UNIQUE constraint column, not just the Primary Key.

Q: Can a table have no Primary Key?

A: Technically yes, but it is bad design. Without a PK, you can have duplicate rows and no
way to uniquely identify a record.

Q: What is referential integrity?

A: It ensures that a Foreign Key value must either be NULL or match an existing Primary
Key value in the referenced table.

Q: Difference between Primary Key and Unique Key?

A: Primary Key does not allow NULL; Unique Key allows one NULL (in most databases). A
table has one PK but can have multiple Unique constraints.
 CHAPTER 3
Normalisation — 1NF to BCNF

Normalisation is the process of organising data to reduce redundancy and improve integrity. It
decomposes tables into smaller, well-structured tables.

Normal
Key Rule Description Example
Form

No repeating groups; each column holds Phone: '9876,1234' → split into

1NF Atomic Values
a single value separate rows

In (OrderID, ProductID) →
Must be in 1NF; non-key columns
2NF No Partial Dependency ProductName depends only on
depend on WHOLE PK
ProductID → move out

EmpID→DeptID→DeptName —
No Transitive Must be in 2NF; non-key columns don't
3NF DeptName depends on DeptID,
Dependency depend on other non-key columns
not EmpID → move out

Handles anomalies 3NF misses

Boyce-Codd Normal Stricter 3NF; every determinant must be
BCNF in tables with overlapping
Form a candidate key
candidate keys

Employee with multiple skills

No Multi-Valued A record should not have two or more
4NF AND multiple hobbies →
Dependency independent multi-valued facts
separate tables

Table cannot be decomposed further

5NF No Join Dependency Rare in practice
without losing information

IBM TIP

IBM Interview Tip: In practice, most databases are normalised to 3NF or BCNF.
Over-normalisation (4NF, 5NF) can hurt performance due to too many joins. Always
mention trade-offs: normalisation vs. denormalisation for performance.

Denormalisation
Denormalisation is the intentional introduction of redundancy to improve read performance. Used in
data warehouses, reporting systems, and OLAP databases where reads far outnumber writes.

Cross Questions:
Q: What is an Update Anomaly?
A: When the same data exists in multiple rows, updating one creates inconsistency.
Normalisation eliminates this.

Q: What is an Insertion Anomaly?

A: You cannot insert data without including unnecessary/unavailable data. E.g., can't add
a department without an employee.

Q: What is a Deletion Anomaly?

A: Deleting a row unintentionally removes other useful data. Normalisation separates

concerns into different tables.

Q: When would you denormalise?

A: In read-heavy OLAP/reporting systems, denormalisation reduces joins and improves

query speed at the cost of storage and write performance.
 CHAPTER 4
ACID Properties & Transactions

A — Atomicity
A transaction is ALL or NOTHING. If any part fails, the entire transaction is rolled back.

EXAMPLE

Example: Bank transfer: debit from A AND credit to B. If credit fails, debit must also be
undone.

C — Consistency
A transaction brings the database from one valid state to another. All rules, constraints, and cascades are
enforced.

EXAMPLE

Example: After a transfer, total money in the system must remain the same.

I — Isolation
Concurrent transactions execute as if they are sequential. Intermediate states are invisible to other
transactions.

EXAMPLE

Example: Two people booking the last seat — one must wait; they cannot both see it as
'available'.

D — Durability
Once a transaction is COMMITTED, it is permanently saved, even if the system crashes immediately after.

EXAMPLE

Example: After 'Payment Successful', the record persists even if the server reboots.

Transaction Commands
Command Purpose Example

Permanently saves all changes in the current

COMMIT COMMIT;
transaction

Undoes all changes since the last COMMIT or

ROLLBACK ROLLBACK;
SAVEPOINT
 Creates a checkpoint within a transaction for partial SAVEPOINT sp1; ... ROLLBACK TO
SAVEPOINT
rollback sp1;

SET TRANSACTION ISOLATION

SET TRANSACTION Configures isolation level for the transaction
LEVEL SERIALIZABLE;

Cross Questions:
Q: What happens if a COMMIT fails?

A: The database rolls back to its previous consistent state. The transaction is treated as if
it never occurred.

Q: Can we ROLLBACK after COMMIT?

A: No. Once committed, changes are permanent (Durability). You must issue a new
transaction to reverse the effect.

Q: What is an implicit transaction?

A: In auto-commit mode (default in many databases), every DML statement is

automatically committed. Auto-commit can be disabled with SET AUTOCOMMIT = 0.
 CHAPTER 5
Concurrency Control — Locks, Deadlock, Isolation Levels

5.1 Concurrency Problems

Dirty Read: T1 reads data written by T2 which is not yet committed. If T2 rolls back, T1 has wrong
data.
Non-Repeatable Read: T1 reads the same row twice but gets different values because T2 updated
and committed between reads.
Phantom Read: T1 reads a set of rows. T2 inserts/deletes rows. T1 re-reads and sees a different set.
Lost Update: Two transactions read and update the same data; one update overwrites the other.

5.2 Isolation Levels

Isolation Level Dirty Read Non-Repeatable Read Phantom Read

READ UNCOMMITTED Possible Possible Possible

READ COMMITTED Prevented Possible Possible

REPEATABLE READ Prevented Prevented Possible

SERIALIZABLE Prevented Prevented Prevented

5.3 Locking
Shared Lock (S): Multiple transactions can read simultaneously. No writes allowed.
Exclusive Lock (X): Only one transaction can read or write. Others must wait.
Deadlock: T1 holds lock on A, waits for B. T2 holds lock on B, waits for A. Neither can proceed.

Deadlock Prevention Strategies:

• Wait-Die: Older transaction waits; younger transaction dies (rolls back).
• Wound-Wait: Older transaction wounds (rolls back) the younger; younger waits.
• Timeout: If a transaction waits too long, it is automatically rolled back.
• Deadlock Detection: Database detects cycles in the wait-for graph and rolls back one transaction.
• Lock Ordering: Always acquire locks in the same global order to prevent circular waits.

Q: What is Two-Phase Locking (2PL)?

A: A concurrency protocol with two phases: Growing Phase (only acquire locks, no
releases) and Shrinking Phase (only release locks, no new acquisitions). Guarantees
serializability.
Q: What is MVCC?

A: Multi-Version Concurrency Control maintains multiple versions of data so readers don't

block writers. PostgreSQL and Oracle use MVCC. Readers see a consistent snapshot;
writers create new versions.
 CHAPTER 6
Indexing & Query Optimisation

An Index is a data structure (usually a B-Tree or Hash) that speeds up data retrieval by providing a fast
lookup path, similar to the index at the back of a book.

Index Type Description Best For

Physically sorts table rows. One per table. PK is

Clustered Index Range queries, ORDER BY
usually clustered.

Separate structure pointing to rows. Multiple

Non-Clustered Index Equality lookups, WHERE clauses
allowed.

Index on 2+ columns. Order matters — leftmost

Composite Index Multi-column WHERE conditions
prefix rule applies.

Unique Index Ensures all indexed values are distinct. Enforcing uniqueness

Enables fast text searching within large text

Full-Text Index LIKE '%word%' type queries
columns.

Includes all columns needed by a query — no

Covering Index High-performance read queries
table lookup needed.

Uses hash table. Only equality checks, not range

Hash Index Exact-match lookups
queries.

IMPORTANT TRADE-OFF

Trade-off: Indexes speed up SELECT but slow down INSERT, UPDATE, DELETE
because the index must also be maintained. Avoid indexing low-cardinality columns (e.g.
gender with only M/F).

Cross Questions:
Q: How does a B-Tree index work?

A: B-Tree (Balanced Tree) keeps data sorted and allows searches, sequential access,
insertions, and deletions in O(log n) time. Most databases (MySQL InnoDB, PostgreSQL)
use B+ Trees where all data is in leaf nodes linked for sequential scan.

Q: When should you NOT use an index?

A: Small tables (full scan is faster), low-cardinality columns, columns frequently updated,
columns rarely used in WHERE/JOIN/ORDER BY clauses.
Q: What is an execution plan?

A: A roadmap the database query optimiser generates showing how it will execute a query
— which indexes it will use, which join algorithm, etc. Use EXPLAIN or EXPLAIN
ANALYZE to view it.

Q: What is query optimisation?

A: The process of rewriting queries or creating indexes to reduce execution time and
resource consumption. The query optimiser automatically chooses the best execution plan
based on statistics.
 CHAPTER 7
SQL Commands — DDL, DML, DQL, TCL, DCL

Category Full Form Commands Purpose

CREATE, ALTER, DROP, TRUNCATE,

DDL Data Definition Language Define/modify database structure
RENAME

Data Manipulation
DML INSERT, UPDATE, DELETE, MERGE Manipulate data in tables
Language

DQL Data Query Language SELECT Retrieve data

Transaction Control
TCL COMMIT, ROLLBACK, SAVEPOINT Manage transactions
Language

DCL Data Control Language GRANT, REVOKE Manage permissions

DDL Commands in Detail

CREATE TABLE

CREATE TABLE Employee ( emp_id INT PRIMARY KEY, name VARCHAR(100)

NOT NULL, salary DECIMAL(10,2), dept_id INT, FOREIGN KEY (dept_id)
REFERENCES Department(dept_id) );

ALTER TABLE (Add Column)

ALTER TABLE Employee ADD COLUMN email VARCHAR(150);

ALTER TABLE (Modify Column)

ALTER TABLE Employee MODIFY COLUMN salary DECIMAL(12,2);

DROP TABLE

DROP TABLE Employee; -- Removes table structure AND all data

TRUNCATE TABLE

TRUNCATE TABLE Employee; -- Removes all rows, keeps structure,

faster than DELETE

RENAME TABLE

RENAME TABLE Employee TO Staff;

Q: Difference between DROP, DELETE, and TRUNCATE?

A: DELETE: DML, removes specific rows, can be rolled back, triggers fire, slow on large
tables. TRUNCATE: DDL, removes ALL rows, cannot be rolled back (in most DBs), no
triggers, very fast. DROP: DDL, removes the entire table including structure, cannot be
rolled back.
 CHAPTER 8
SQL Clauses — WHERE, GROUP BY, HAVING, ORDER BY

SQL Query Execution Order (Critical for Interviews!)

Step Clause What It Does

1 FROM Identify tables and apply JOINs

2 WHERE Filter individual rows

3 GROUP BY Group filtered rows

4 HAVING Filter groups

5 SELECT Choose columns, apply expressions

6 DISTINCT Remove duplicate rows

7 ORDER BY Sort final result

8 LIMIT/OFFSET Restrict number of rows returned

COMMON MISTAKE

Critical Interview Point: WHERE cannot use aggregate functions (SUM, COUNT, AVG)
because it runs BEFORE GROUP BY. Use HAVING for aggregate conditions.

WHERE vs HAVING — Side by Side

Feature WHERE HAVING

Execution Before GROUP BY After GROUP BY

Used with Individual rows Groups / Aggregates

Aggregate functions NOT allowed Allowed

Performance Faster (filters early) Slower (filters late)

Example WHERE salary > 50000 HAVING COUNT(*) > 5

LIKE Operator Patterns

Pattern Meaning Example Match

'A%' Starts with A Alice, Andrew

'%A' Ends with A India, Java

'%SQL%' Contains SQL MySQL, PostgreSQL

'_ohn' 4 chars, ends with ohn John

'A___' 4 chars starting with A Alex, Anna

 CHAPTER 9
SQL Joins — All Types with Examples

Join Type Returns Use Case

Find employees WITH a

INNER JOIN Only matching rows from both tables
department

All rows from left + matching from right (NULL if no Find ALL employees, including
LEFT JOIN (LEFT OUTER)
match) those with no department

RIGHT JOIN (RIGHT All rows from right + matching from left (NULL if no Find ALL departments, even those
OUTER) match) with no employees

Complete list of employees and

FULL OUTER JOIN All rows from both tables (NULL where no match)
departments

Cartesian product — every row in A paired with

CROSS JOIN Generate test data, combinations
every row in B

SELF JOIN A table joined with itself Find employee-manager hierarchy

INNER JOIN

SELECT [Link], d.dept_name FROM Employee e INNER JOIN Department d

ON e.dept_id = d.dept_id;

LEFT JOIN

SELECT [Link], d.dept_name FROM Employee e LEFT JOIN Department d ON

e.dept_id = d.dept_id;

FULL OUTER JOIN

SELECT [Link], d.dept_name FROM Employee e FULL OUTER JOIN

Department d ON e.dept_id = d.dept_id;

SELF JOIN (Manager)

SELECT [Link] AS Employee, [Link] AS Manager FROM Employee e JOIN

Employee m ON e.manager_id = m.emp_id;

CROSS JOIN

SELECT [Link], d.dept_name FROM Employee e CROSS JOIN Department d;

Cross Questions:
Q: What is the difference between INNER JOIN and WHERE clause join?

A: Both produce the same result for equi-joins, but INNER JOIN is explicit and more
readable. Modern SQL prefers explicit JOIN syntax. WHERE clause joins are
implicit/old-style.

Q: When does a LEFT JOIN return NULL?

A: When there is no matching row in the right table for a row in the left table, all right-table
columns are returned as NULL.

Q: Can you JOIN on multiple conditions?

A: Yes: ON e.dept_id = d.dept_id AND [Link] = [Link]

 CHAPTER 10
Subqueries, Views, Stored Procedures & Triggers

10.1 Subqueries
A subquery is a query nested inside another query. It can appear in SELECT, FROM, WHERE, or
HAVING clauses.

Scalar Subquery (returns one value)

SELECT name FROM Employee WHERE salary = (SELECT MAX(salary) FROM

Employee);

Correlated Subquery (references outer query)

SELECT [Link] FROM Employee e WHERE salary > (SELECT AVG(salary)

FROM Employee WHERE dept_id = e.dept_id);

EXISTS Subquery

SELECT name FROM Employee e WHERE EXISTS ( SELECT 1 FROM Department d

WHERE d.dept_id = e.dept_id );

IN Subquery

SELECT name FROM Employee WHERE dept_id IN (SELECT dept_id FROM

Department WHERE location = 'Mumbai');

Q: Difference between IN and EXISTS?

A: IN evaluates the subquery first and compares all values — good for small subquery
results. EXISTS stops as soon as it finds the first match — more efficient for large
subquery results. EXISTS is generally faster when the subquery returns many rows.

10.2 Views
A View is a virtual table based on a SQL query. It does not store data itself; it stores the query definition
and executes it when accessed.

CREATE VIEW HighSalaryEmp AS SELECT name, salary, dept_id FROM

Employee WHERE salary > 50000; SELECT * FROM HighSalaryEmp; -- Use
like a regular table DROP VIEW HighSalaryEmp; -- Remove the view
 Feature View Materialised View

Data Storage No — query runs each time Yes — result stored physically

Performance Slower (re-executes query) Faster (pre-computed)

Refresh Always current Manual or scheduled refresh

Use Case Security, simplify complex queries Reporting, large aggregations

10.3 Stored Procedures

CREATE PROCEDURE GetEmpByDept(IN dept INT) BEGIN SELECT * FROM
Employee WHERE dept_id = dept; END;

Q: Stored Procedure vs Function?

A: Procedure: can have IN, OUT, INOUT parameters; can call COMMIT/ROLLBACK; does
not need to return a value; called with CALL. Function: must return a value; cannot use
COMMIT/ROLLBACK; can be used in SELECT/WHERE; called like an expression.

10.4 Triggers
A Trigger is a stored program that automatically executes when a specific DML event (INSERT,
UPDATE, DELETE) occurs on a table.

CREATE TRIGGER before_salary_update BEFORE UPDATE ON Employee FOR

EACH ROW BEGIN IF [Link] < 0 THEN SIGNAL SQLSTATE '45000' SET
MESSAGE_TEXT = 'Salary cannot be negative'; END IF; END;

Trigger Timing Event Use Case

BEFORE INSERT/UPDATE/DELETE Validate data before it changes

AFTER INSERT/UPDATE/DELETE Audit logging after change

INSERT/UPDATE/DELETE on
INSTEAD OF Custom logic for view modifications
Views
 CHAPTER 11
Window Functions — ROW_NUMBER, RANK, LAG, LEAD

Window functions perform calculations across a set of rows related to the current row, WITHOUT
collapsing rows like GROUP BY does. They use the OVER() clause.

Function Description Handles Ties?

No tie handling — arbitrary

ROW_NUMBER() Unique sequential number regardless of ties
order for ties

Ties get same rank; next rank

RANK() Rank with gaps after ties
skips

Ties get same rank; next rank is

DENSE_RANK() Rank without gaps after ties
consecutive

NTILE(n) Divides rows into n equal buckets N/A

LAG(col, n) Value from n rows BEFORE current row N/A

LEAD(col, n) Value from n rows AFTER current row N/A

FIRST_VALUE(col) First value in the window partition N/A

LAST_VALUE(col) Last value in the window partition N/A

SUM/AVG/COUNT OVER() Running totals/averages N/A

ROW_NUMBER — Find Nth Highest Salary

SELECT * FROM ( SELECT name, salary, ROW_NUMBER() OVER (ORDER BY

salary DESC) AS rn FROM Employee ) ranked WHERE rn = 3; -- 3rd
highest salary

RANK vs DENSE_RANK

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank_val,

DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_val FROM
Employee;

LAG — Salary Comparison with Previous Row

SELECT name, salary, LAG(salary, 1) OVER (ORDER BY hire_date) AS

prev_salary FROM Employee;

Running Total
 SELECT name, salary, SUM(salary) OVER (ORDER BY emp_id ROWS BETWEEN
UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM Employee;

Partition by Department

SELECT name, dept_id, salary, RANK() OVER (PARTITION BY dept_id

ORDER BY salary DESC) AS dept_rank FROM Employee;

Q: Difference between RANK and DENSE_RANK?

A: If two employees both earn 50000 (rank 2), RANK gives both rank 2 and the next gets
rank 4 (gap). DENSE_RANK gives both rank 2 and the next gets rank 3 (no gap). Use
DENSE_RANK when you don't want to skip ranks.
 CHAPTER 12
Top 40 SQL Interview Queries

TABLE STRUCTURE

All queries use the Employee table: emp_id, name, salary, dept_id, manager_id,
hire_date and Department table: dept_id, dept_name, location

Q1: Get all employees

SELECT * FROM Employee;

TIP

Use column names instead of * in production to avoid fetching unnecessary data.

Q2: Employees with salary > 50,000

SELECT * FROM Employee WHERE salary > 50000;

TIP

Index on salary column will make this query faster.

Q3: Sort employees by salary descending

SELECT * FROM Employee ORDER BY salary DESC;

TIP

Always specify ASC or DESC explicitly for clarity.

Q4: Unique/distinct salaries

SELECT DISTINCT salary FROM Employee;

TIP

DISTINCT works after SELECT — it applies to the entire row, not just one column.

Q5: Count total employees

SELECT COUNT(*) AS total_employees FROM Employee;

TIP
 COUNT(*) counts all rows including NULLs; COUNT(column) skips NULLs.

Q6: Maximum salary

SELECT MAX(salary) AS max_salary FROM Employee;

TIP

Can also use ORDER BY salary DESC LIMIT 1, but MAX() is more efficient.

Q7: Minimum salary

SELECT MIN(salary) AS min_salary FROM Employee;

Q8: Average salary

SELECT AVG(salary) AS avg_salary FROM Employee;

TIP

AVG ignores NULL values automatically.

Q9: Second highest salary

SELECT MAX(salary) FROM Employee WHERE salary < (SELECT MAX(salary)

FROM Employee);

TIP

Alternative: SELECT DISTINCT salary FROM Employee ORDER BY salary DESC LIMIT
1 OFFSET 1;

Q10: Nth highest salary (generalised)

SELECT salary FROM ( SELECT salary, DENSE_RANK() OVER (ORDER BY

salary DESC) AS dr FROM Employee ) t WHERE dr = N; -- Replace N with
desired rank

TIP

Use DENSE_RANK to handle duplicate salaries correctly.

Q11: Employees earning more than average

SELECT * FROM Employee WHERE salary > (SELECT AVG(salary) FROM

Employee);

TIP
 Correlated comparison — subquery runs once and result is reused.

Q12: Count of employees per department

SELECT dept_id, COUNT(*) AS emp_count FROM Employee GROUP BY

dept_id;

Q13: Departments with more than 5 employees

SELECT dept_id, COUNT(*) AS emp_count FROM Employee GROUP BY dept_id

HAVING COUNT(*) > 5;

TIP

HAVING filters AFTER grouping; WHERE cannot be used here.

Q14: Average salary per department with dept name

SELECT d.dept_name, AVG([Link]) AS avg_sal FROM Employee e JOIN

Department d ON e.dept_id = d.dept_id GROUP BY d.dept_name ORDER BY
avg_sal DESC;

Q15: INNER JOIN — employees with their department

SELECT [Link], d.dept_name FROM Employee e INNER JOIN Department d

ON e.dept_id = d.dept_id;

TIP

Only employees WITH a department are returned.

Q16: LEFT JOIN — all employees including those without department

SELECT [Link], d.dept_name FROM Employee e LEFT JOIN Department d ON

e.dept_id = d.dept_id;

TIP

dept_name will be NULL for employees not assigned to any department.

Q17: Find employees without a department

SELECT [Link] FROM Employee e LEFT JOIN Department d ON e.dept_id =

d.dept_id WHERE d.dept_id IS NULL;

TIP

The IS NULL on the right table column identifies unmatched rows.

Q18: Duplicate salaries

SELECT salary, COUNT(*) AS occurrences FROM Employee GROUP BY salary

HAVING COUNT(*) > 1;

Q19: Names starting with letter A

SELECT * FROM Employee WHERE name LIKE 'A%';

TIP

% = any characters. LIKE is case-insensitive in MySQL by default.

Q20: Names containing 'an'

SELECT * FROM Employee WHERE name LIKE '%an%';

Q21: Top 3 highest paid employees

SELECT * FROM Employee ORDER BY salary DESC LIMIT 3;

TIP

Use FETCH FIRST 3 ROWS ONLY in Oracle/Db2.

Q22: Top 3 per department

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY dept_id

ORDER BY salary DESC) AS rn FROM Employee ) t WHERE rn <= 3;

TIP

PARTITION BY resets the row number for each department.

Q23: Self JOIN — employee and manager names

SELECT [Link] AS Employee, [Link] AS Manager FROM Employee e LEFT

JOIN Employee m ON e.manager_id = m.emp_id;

TIP

Self JOIN is key for hierarchical data like org charts.

Q24: Employees hired in the last 6 months

SELECT * FROM Employee WHERE hire_date >= DATE_SUB(CURDATE(),

INTERVAL 6 MONTH);
 TIP

Use DATEADD in SQL Server; ADD_MONTHS in Oracle.

Q25: Delete duplicate rows (keep one)

DELETE FROM Employee WHERE emp_id NOT IN ( SELECT MIN(emp_id) FROM

Employee GROUP BY name, salary, dept_id );

TIP

Always test with a SELECT first before running DELETE.

Q26: Update salary by 10% for a department

UPDATE Employee SET salary = salary * 1.10 WHERE dept_id = 3;

Q27: Running total of salary

SELECT name, salary, SUM(salary) OVER (ORDER BY emp_id) AS

running_total FROM Employee;

Q28: Salary percentile rank

SELECT name, salary, PERCENT_RANK() OVER (ORDER BY salary) AS

percentile FROM Employee;

TIP

PERCENT_RANK returns 0 for lowest, 1 for highest.

Q29: Employees whose salary is above their dept avg

SELECT [Link], [Link], e.dept_id FROM Employee e WHERE [Link] >

( SELECT AVG(salary) FROM Employee WHERE dept_id = e.dept_id );

TIP

This is a correlated subquery — runs once per outer row.

Q30: Department with highest average salary

SELECT dept_id, AVG(salary) AS avg_sal FROM Employee GROUP BY

dept_id ORDER BY avg_sal DESC LIMIT 1;
Q31: Count NULLs in a column

SELECT COUNT(*) - COUNT(salary) AS null_count FROM Employee;

TIP

COUNT(*) counts all rows; COUNT(column) skips NULLs. Their difference = NULL
count.

Q32: Replace NULL salary with 0

SELECT name, COALESCE(salary, 0) AS salary FROM Employee;

TIP

COALESCE returns the first non-NULL value in the list.

Q33: Pivot: Count employees per dept in one row (MySQL)

SELECT SUM(CASE WHEN dept_id=1 THEN 1 ELSE 0 END) AS Dept1, SUM(CASE

WHEN dept_id=2 THEN 1 ELSE 0 END) AS Dept2 FROM Employee;

TIP

CASE WHEN is the SQL pivot technique.

Q34: Find employees with the same salary as another employee

SELECT DISTINCT [Link], [Link] FROM Employee e1 JOIN Employee e2

ON [Link] = [Link] AND e1.emp_id != e2.emp_id;

Q35: Cumulative distribution

SELECT name, salary, CUME_DIST() OVER (ORDER BY salary) AS cume_dist

FROM Employee;

TIP

CUME_DIST returns fraction of rows with value <= current row's value.

Q36: EXCEPT / MINUS — employees not in another table

SELECT emp_id FROM Employee EXCEPT SELECT emp_id FROM

ResignedEmployees;

TIP

MINUS in Oracle; EXCEPT in SQL Server/PostgreSQL.

Q37: INSERT multiple rows

INSERT INTO Employee (emp_id, name, salary, dept_id) VALUES

(101,'Alice',60000,1), (102,'Bob',45000,2), (103,'Carol',70000,1);

Q38: Common Table Expression (CTE)

WITH HighEarners AS ( SELECT * FROM Employee WHERE salary > 60000 )

SELECT [Link], d.dept_name FROM HighEarners h JOIN Department d ON
h.dept_id = d.dept_id;

TIP

CTEs improve readability and can be recursive for hierarchies.

Q39: Recursive CTE — org hierarchy

WITH RECURSIVE OrgChart AS ( SELECT emp_id, name, manager_id, 1 AS

level FROM Employee WHERE manager_id IS NULL UNION ALL SELECT
e.emp_id, [Link], e.manager_id, [Link]+1 FROM Employee e JOIN
OrgChart oc ON e.manager_id = oc.emp_id ) SELECT * FROM OrgChart;

TIP

Recursive CTEs are essential for tree structures.

Q40: MERGE (UPSERT) statement

MERGE INTO Employee AS target USING NewData AS source ON

target.emp_id = source.emp_id WHEN MATCHED THEN UPDATE SET
[Link] = [Link] WHEN NOT MATCHED THEN INSERT (emp_id,
name, salary) VALUES (source.emp_id, [Link], [Link]);

TIP

MERGE combines INSERT and UPDATE in one atomic statement.

 CHAPTER 13
Advanced Topics — Partitioning, Sharding, NoSQL, CAP

13.1 Database Partitioning

Type Description Example

Users A-M on server1, N-Z on

Horizontal (Sharding) Rows split across multiple tables/servers
server2

Columns split — frequently accessed columns Basic info table + rarely accessed
Vertical
separated details table

Range Partitioning Rows partitioned by value range Orders by year: 2022, 2023, 2024

Hash Partitioning Hash of a column determines partition emp_id % 4 → 4 partitions

List Partitioning Explicit list of values per partition Region: North, South, East, West

13.2 CAP Theorem

In a distributed system, you can guarantee only 2 of these 3 properties at the same time:

Property Meaning Trade-off

Consistency (C) Every read gets the most recent write Sacrifice availability

Availability (A) Every request gets a response (may not be latest) Sacrifice consistency

System works despite network failures between

Partition Tolerance (P) Must choose C or A
nodes

EXAMPLES

CP systems (e.g. HBase, Zookeeper): Consistent but may be unavailable during

partition. AP systems (e.g. Cassandra, DynamoDB): Always available but may return
stale data. CA systems: Only possible in non-distributed setups.

13.3 SQL vs NoSQL

Feature SQL (Relational) NoSQL

Schema Fixed, predefined schema Flexible/dynamic schema

Data Model Tables with rows & columns Document, Key-Value, Graph, Column

ACID Fully supported Eventual consistency (mostly)

Scaling Vertical (scale up) Horizontal (scale out)

Query Language SQL — standardised Database-specific APIs

Use Case Banking, ERP, structured data Social media, IoT, real-time analytics

Examples MySQL, PostgreSQL, Oracle, Db2 MongoDB, Cassandra, Redis, DynamoDB

13.4 OLTP vs OLAP

Feature OLTP OLAP

Purpose Day-to-day transactions Business intelligence / reporting

Data Volume Small per transaction Very large aggregations

Query Type Simple INSERT/UPDATE/SELECT Complex aggregations & joins

Normalisation Highly normalised (3NF) Denormalised (Star/Snowflake schema)

Response Time Milliseconds Seconds to minutes

Examples Banking system, e-commerce Data warehouse, BI tools

CHAPTER 14
IBM-Level Cross Questions & Model Answers

Q: What is the difference between a clustered and non-clustered index? Which is

faster?

A: A clustered index physically reorders the table data to match the index — there is only
one per table and it is the fastest for range queries. A non-clustered index is a separate
structure pointing back to the data rows — multiple allowed. Clustered index is faster for
range queries; non-clustered is flexible for multiple access patterns.

Q: Explain the difference between TRUNCATE and DELETE. Can both be rolled
back?

A: DELETE is a DML operation that removes rows one-by-one, fires triggers, can be
filtered with WHERE, and can be rolled back. TRUNCATE is DDL, removes all rows in bulk
by deallocating data pages, does not fire row-level triggers, and in most databases (except
PostgreSQL) CANNOT be rolled back.

Q: What is a covering index and why is it important?

A: A covering index includes all columns needed by a query, so the database can answer
the query entirely from the index without touching the main table (no 'key lookup'). This
dramatically reduces I/O and improves performance for read-heavy queries.

Q: Explain correlated vs non-correlated subqueries.

A: A non-correlated subquery is independent — it runs once and the result is used by the
outer query. A correlated subquery references a column from the outer query — it runs
once for every row in the outer query, making it potentially slow on large tables.

Q: What is the difference between UNION and UNION ALL?

A: UNION combines results of two SELECT statements and removes duplicate rows
(requires sorting). UNION ALL combines results and keeps ALL rows including duplicates
— it is faster because no deduplication step is needed. Use UNION ALL when you know
there are no duplicates or performance is critical.

Q: How would you optimise a slow SQL query?

A: 1) Use EXPLAIN to analyse the execution plan. 2) Add indexes on columns used in
WHERE, JOIN, ORDER BY. 3) Avoid SELECT * — select only needed columns. 4)
Rewrite subqueries as JOINs where possible. 5) Use covering indexes. 6) Partition large
tables. 7) Avoid functions on indexed columns in WHERE clause. 8) Check for missing
foreign key indexes. 9) Consider query caching or materialised views.
Q: What is phantom read? Which isolation level prevents it?

A: A phantom read occurs when transaction T1 reads a set of rows, T2 inserts new rows
matching T1's WHERE condition and commits, and T1 re-reads the same range and sees
'phantom' rows that weren't there before. Only SERIALIZABLE isolation level prevents
phantom reads by using range locking.

Q: Explain the difference between DELETE and DROP.

A: DELETE removes specific rows from a table (data only), keeping the table structure
intact. Can be rolled back. DROP removes the entire table including its structure,
constraints, indexes, and all data. Cannot be rolled back.

Q: What is a deadlock and how does a DBMS handle it?

A: Deadlock: Two transactions each hold a lock the other needs — creating a circular wait.
DBMS handles it via: deadlock detection (waits-for graph cycle detection) then kills one
victim transaction; or prevention strategies like wait-die / wound-wait / lock ordering.

Q: What is the difference between a VIEW and a MATERIALISED VIEW?

A: A VIEW is a virtual table — the underlying query runs every time the view is accessed.
A MATERIALISED VIEW stores the query result physically — much faster for complex
aggregations, but data may be stale until refreshed. Materialised Views are used in data
warehouses and reporting.

Q: Can a foreign key be NULL? What does it mean?

A: Yes. A NULL foreign key means the relationship is optional — the row does not belong
to any parent record. For example, an employee with a NULL dept_id has not been
assigned to a department yet.

Q: What is normalisation and when would you denormalise?

A: Normalisation organises tables to eliminate redundancy and ensure data integrity.

Denormalisation intentionally adds redundancy to improve read performance — used in
OLAP/reporting/data warehouse environments where reads far outnumber writes and
query speed is more critical than storage efficiency.

Q: Explain RANK(), DENSE_RANK(), and ROW_NUMBER() with an example.

A: Given salaries: 90K, 80K, 80K, 70K — ROW_NUMBER: 1,2,3,4 (unique, arbitrary for
ties). RANK: 1,2,2,4 (ties share rank, next rank skips). DENSE_RANK: 1,2,2,3 (ties share
rank, next rank is consecutive). Use DENSE_RANK for Nth highest salary problems.
Q: What is a CTE and how is it different from a subquery?

A: A CTE (Common Table Expression) is a named temporary result set defined with the
WITH clause. Unlike a subquery (inline, hard to reuse), a CTE: is named and readable,
can be referenced multiple times in the same query, can be recursive (for hierarchies), and
generally has the same performance as a subquery in most databases.

Q: How does IBM Db2 differ from MySQL?

A: IBM Db2 is an enterprise-grade RDBMS designed for high-volume OLTP and OLAP
workloads on mainframes and Linux/Windows. It supports advanced features like
pureScale (clustering), BLU Acceleration (in-memory columnar), row and column
organisation, and deep IBM ecosystem integration. MySQL is open-source, lightweight,
and commonly used for web applications. Db2 has superior concurrency control,
partitioning, and workload management features.
 QUICK REFERENCE
Cheat Sheet — Key Formulas & Patterns

Must-Know SQL Patterns

Nth highest salary:

SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY

salary DESC) dr FROM Employee) t WHERE dr = N;

Delete duplicates:

DELETE FROM t WHERE id NOT IN (SELECT MIN(id) FROM t GROUP BY col1,

col2);

Running total:

SUM(col) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND

CURRENT ROW)

Top N per group:

ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY rank_col DESC)

Pivot with CASE:

SUM(CASE WHEN col='val' THEN 1 ELSE 0 END) AS val_count

Employees > dept avg:

WHERE salary > (SELECT AVG(salary) FROM Employee WHERE dept_id =

e.dept_id)

NULL count:

COUNT(*) - COUNT(column) AS null_count

Recursive hierarchy:

WITH RECURSIVE cte AS (anchor UNION ALL recursive_step) SELECT *

FROM cte;
FINAL IBM INTERVIEW TIPS

Final IBM Interview Tips: 1. Always explain your reasoning before writing the query. 2.
Mention performance considerations and trade-offs. 3. Know the difference between
RANK, DENSE_RANK, ROW_NUMBER — this is asked in every IBM interview. 4.
Understand execution order: FROM → WHERE → GROUP BY → HAVING → SELECT
→ ORDER BY. 5. Always mention indexes when asked about optimisation. 6. Know
ACID properties deeply — not just the acronym, but real-world examples. 7. Prepare for
correlated subquery vs CTE trade-offs. 8. Be ready to write recursive CTEs for org
hierarchy problems.