Complete SQL & DBMS Interview Guide 🗄️

SECTION 1: SQL KEYS & CONSTRAINTS

Primary Key vs Unique Key vs Candidate Key

─────────────────┬──────────────┬───────────┬─────────────────┐
│ Property │ Primary Key │ Unique Key│ Candidate Key │
├─────────────────┼──────────────┼───────────┼─────────────────┤
│ NULL allowed? │ NO │ ONE NULL │ NO │
│ Count per table │ ONE │ MULTIPLE │ MULTIPLE │
│ Uniqueness │ YES │ YES │ YES │
│ Auto Index │ YES │ YES │ YES │
└─────────────────┴──────────────┴───────────┴─────────────────┘

All Types of Keys — Simple Explanation

EMPLOYEE TABLE:
────┬──────────┬───────┬────────┬────────┐
│ ID │ Name │ Email │ Phone │ Dept_ID│
├────┼──────────┼───────┼────────┼────────┤
│ 1 │ Alice │ a@x │ 9999 │ D1 │
│ 2 │ Bob │ b@x │ 8888 │ D2 │
└────┴──────────┴───────┴────────┴────────┘

SUPER KEY → Any combo that uniquely identifies row

{ID}, {Email}, {Phone}, {ID,Name}, {ID,Email}...
(superset - can have extra columns)

CANDIDATE KEY → MINIMAL super key (no extra columns)

{ID}, {Email}, {Phone}
(these alone are enough to identify uniquely)
 PRIMARY KEY → ONE chosen candidate key
{ID} ← we picked this

ALTERNATE KEY → Remaining candidate keys after PK chosen

{Email}, {Phone}

UNIQUE KEY → Like candidate key but allows ONE NULL

Can have multiple unique keys per table

FOREIGN KEY → Links to Primary Key of another table

Dept_ID references Departments table

SECTION 2: NORMALIZATION (All Normal Forms)

1NF — First Normal Form

RULE: Each cell must have ATOMIC (single) values

No repeating groups

❌ VIOLATES 1NF:
────┬────────┬──────────────────┐
│ ID │ Name │ Phone │
├────┼────────┼──────────────────┤
│ 1 │ Alice │ 9999, 8888, 7777 │ ← Multiple values!
└────┴────────┴──────────────────┘

✅ SATISFIES 1NF:
────┬────────┬───────┐
│ ID │ Name │ Phone │
├────┼────────┼───────┤
│ 1 │ Alice │ 9999 │
│ 1 │ Alice │ 8888 │
│ 1 │ Alice │ 7777 │
└────┴────────┴───────┘

2NF — Second Normal Form

 RULE: Must be 1NF + No PARTIAL DEPENDENCY
Every non-key attribute must depend on WHOLE primary key
(Only matters when Primary Key is COMPOSITE)

❌ VIOLATES 2NF:
Table: (StudentID, CourseID, StudentName, CourseName, Grade)
PK = (StudentID + CourseID)

StudentName depends ONLY on StudentID ← Partial Dependency!

CourseName depends ONLY on CourseID ← Partial Dependency!
Grade depends on BOTH ← OK

✅ FIX — Split into:

Students(StudentID, StudentName)
Courses(CourseID, CourseName)
Enrollment(StudentID, CourseID, Grade)

3NF — Third Normal Form

RULE: Must be 2NF + No TRANSITIVE DEPENDENCY

Non-key attribute should NOT depend on another non-key attribute

❌ VIOLATES 3NF:
Table: (EmpID, EmpName, DeptID, DeptName)
PK = EmpID

EmpID → DeptID → DeptName

DeptName depends on DeptID (non-key) not directly on EmpID
This is TRANSITIVE DEPENDENCY!

✅ FIX — Split into:

Employee(EmpID, EmpName, DeptID)
Department(DeptID, DeptName)

BCNF — Boyce-Codd Normal Form

RULE: Must be 3NF + For every FD X→Y, X must be a SUPERKEY

Stricter than 3NF
 ❌ VIOLATES BCNF (but satisfies 3NF):
Table: (Student, Subject, Teacher)
FDs:
{Student, Subject} → Teacher ← composite key
Teacher → Subject ← Teacher is NOT superkey!

✅ FIX — Split into:

Teacher_Subject(Teacher, Subject)
Student_Teacher(Student, Teacher)

Quick Summary Table

──────┬──────────────────────────────────────────────┐
│ NF │ Eliminates │
├──────┼──────────────────────────────────────────────┤
│ 1NF │ Multi-valued / non-atomic attributes │
│ 2NF │ Partial dependencies on composite PK │
│ 3NF │ Transitive dependencies │
│ BCNF │ All anomalies (determinant must be superkey) │
└──────┴──────────────────────────────────────────────┘

SECTION 3: ACID PROPERTIES

A — ATOMICITY
Transaction = ALL or NOTHING

Example: Transfer ₹500 from A to B

Step 1: Debit A by 500
Step 2: Credit B by 500

If Step 2 fails → Step 1 is ROLLED BACK

Both happen or neither happens ✓

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

C — CONSISTENCY
 DB must be in valid state BEFORE and AFTER transaction

Example: Total money in bank = ₹10,000

After any transfer → total must still be ₹10,000
Rules/constraints must never be violated ✓

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

I — ISOLATION
Concurrent transactions appear as if SEQUENTIAL

Example: 2 people book last seat on flight

T1 and T2 run simultaneously
Result = same as if they ran one after another
No intermediate state visible to other transactions ✓

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

D — DURABILITY
Committed transactions are PERMANENT

Example: Payment done, confirmation received

Even if server crashes immediately after
Data is safely stored and will persist ✓

SECTION 4: SQL QUERIES — COMPLETE PRACTICE

Setup — Tables We’ll Use

-- Create Employee Table

CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
salary DECIMAL(10,2),
dept_id INT,
manager_id INT,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
 -- Create Department Table
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);

-- Sample Data
INSERT INTO employees VALUES
(1, 'Alice', 90000, 1, NULL),
(2, 'Bob', 75000, 1, 1),
(3, 'Charlie',85000, 2, 1),
(4, 'Diana', 75000, 2, 3),
(5, 'Eve', 60000, 3, 3),
(6, 'Frank', 60000, 3, 1);

✅ Q1: Find MAX Salary Employee Name

-- Method 1: Simple subquery

SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

-- Output:
-- name | salary
-- Alice | 90000

✅ Q2: Find 2nd Highest Salary

-- Method 1: Using subquery (Classic way)

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Method 2: Using DENSE_RANK (Best way)

SELECT salary FROM (
SELECT salary,
 DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) ranked
WHERE rnk = 2;

-- Method 3: Using LIMIT OFFSET (MySQL)

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

-- All three give same result:

-- salary
-- 85000

✅ Q3: Find Nth Highest Salary (General Formula)

-- Replace N with any number

-- For 5th highest salary → rnk = 5

SELECT salary FROM (

SELECT salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) ranked
WHERE rnk = 5;

-- Using OFFSET for Nth

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET N-1;
-- For 5th: LIMIT 1 OFFSET 4

✅ Q4: LIKE Operator — All Patterns

 -- % means: zero or more characters
-- _ means: exactly one character

-- Names STARTING with 'A'

SELECT * FROM employees WHERE name LIKE 'A%';
-- Alice ✓

-- Names ENDING with 'e'

SELECT * FROM employees WHERE name LIKE '%e';
-- Alice ✓, Charlie ✓

-- Names CONTAINING 'li'

SELECT * FROM employees WHERE name LIKE '%li%';
-- Alice ✓, Charlie ✓

-- Names with exactly 3 characters

SELECT * FROM employees WHERE name LIKE '___';
-- Bob ✓, Eve ✓

-- Names starting with 'A' and ending with 'e'

SELECT * FROM employees WHERE name LIKE 'A%e';
-- Alice ✓

-- Names where second letter is 'o'

SELECT * FROM employees WHERE name LIKE '_o%';
-- Bob ✓

-- NOT LIKE — names NOT starting with 'A'

SELECT * FROM employees WHERE name NOT LIKE 'A%';
-- Bob, Charlie, Diana, Eve, Frank ✓

✅ Q5: GROUP BY + Aggregation (Directly Asked)

-- Department wise salary statistics

SELECT
dept_id,
COUNT(*) AS total_employees,
SUM(salary) AS total_salary,
 AVG(salary) AS avg_salary,
MAX(salary) AS max_salary,
MIN(salary) AS min_salary
FROM employees
GROUP BY dept_id;

-- Output:
-- dept_id | total_emp | total_sal | avg_sal | max_sal | min_sal
-- 1 | 2 | 165000 | 82500 | 90000 | 75000
-- 2 | 2 | 160000 | 80000 | 85000 | 75000
-- 3 | 2 | 120000 | 60000 | 60000 | 60000

-- HAVING — filter AFTER grouping

-- Departments where average salary > 70000
SELECT dept_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > 70000
ORDER BY avg_sal DESC;

-- KEY DIFFERENCE:
-- WHERE → filters BEFORE grouping (filters individual rows)
-- HAVING → filters AFTER grouping (filters groups)

-- Example combining both:

SELECT dept_id, AVG(salary)
FROM employees
WHERE salary > 60000 -- first filter rows
GROUP BY dept_id -- then group
HAVING AVG(salary) > 70000 -- then filter groups
ORDER BY AVG(salary) DESC; -- then sort

✅ Q6: All JOIN Types (With Visual)

employees table: departments table:

────┬────────┬─────────┐ ─────────┬───────────┐
│ ID │ Name │ dept_id │ │ dept_id │ dept_name │
├────┼────────┼─────────┤ ├─────────┼───────────┤
│ 1 │ Alice │ 1 │ │ 1 │ IT │
│ 2 │ Bob │ 1 │ │ 2 │ HR │
│ 3 │ Charlie│ 2 │ │ 4 │ Finance │ ← No employee!
│ 4 │ Diana │ NULL │ └─────────┴───────────┘
└────┴────────┴─────────┘
Diana has no dept! dept 4 has no employee!

-- INNER JOIN: Only MATCHING rows from both tables

SELECT [Link], d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

-- Result: Alice-IT, Bob-IT, Charlie-HR

-- (Diana excluded, Finance excluded)

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

-- LEFT JOIN: ALL from LEFT table + matching from right

SELECT [Link], d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

-- Result: Alice-IT, Bob-IT, Charlie-HR, Diana-NULL

-- (All employees shown, Diana gets NULL for dept)

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

-- RIGHT JOIN: ALL from RIGHT table + matching from left

SELECT [Link], d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

-- Result: Alice-IT, Bob-IT, Charlie-HR, NULL-Finance

-- (All departments shown, Finance gets NULL for employee)

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

-- FULL OUTER JOIN: ALL from BOTH tables

SELECT [Link], d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id;
 -- Result: Alice-IT, Bob-IT, Charlie-HR, Diana-NULL, NULL-Finance
-- (Everything from both sides)

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

-- SELF JOIN: Table joins with ITSELF

-- Find each employee and their manager name
SELECT
[Link] AS employee,
[Link] AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

-- Result:
-- Alice | NULL (Alice is top manager)
-- Bob | Alice
-- Charlie| Alice
-- Diana | Charlie

✅ Q7: Window Functions (Directly Asked)

-- RANK() — gives GAPS in ranking for ties

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

-- Result:
-- Alice 90000 1
-- Charlie 85000 2
-- Bob 75000 3
-- Diana 75000 3 ← same rank
-- Eve 60000 5 ← GAP! (no rank 4)
-- Frank 60000 5

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

-- DENSE_RANK() — NO gaps in ranking

SELECT name, salary,
 DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;

-- Result:
-- Alice 90000 1
-- Charlie 85000 2
-- Bob 75000 3
-- Diana 75000 3 ← same rank
-- Eve 60000 4 ← NO GAP
-- Frank 60000 4

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

-- ROW_NUMBER() — always unique, no ties

SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

-- Result:
-- Alice 90000 1
-- Charlie 85000 2
-- Bob 75000 3
-- Diana 75000 4 ← different even though same salary!
-- Eve 60000 5
-- Frank 60000 6

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

-- PARTITION BY — rank WITHIN each department

SELECT name, dept_id, salary,
RANK() OVER (
PARTITION BY dept_id
ORDER BY salary DESC
) AS dept_rank
FROM employees;

-- Result:
-- Alice 1 90000 1 ← Rank 1 in dept 1
-- Bob 1 75000 2 ← Rank 2 in dept 1
-- Charlie 2 85000 1 ← Rank 1 in dept 2 (restarts!)
 -- Diana 2 75000 2
-- Eve 3 60000 1 ← Rank 1 in dept 3
-- Frank 3 60000 1

✅ Q8: Subqueries

-- Employees earning above average salary

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

-- Employees in departments having more than 1 employee

SELECT name
FROM employees
WHERE dept_id IN (
SELECT dept_id
FROM employees
GROUP BY dept_id
HAVING COUNT(*) > 1
);

-- Employees with same salary as Bob

SELECT name
FROM employees
WHERE salary = (
SELECT salary FROM employees WHERE name = 'Bob'
) AND name != 'Bob';

-- Correlated Subquery: Employees earning more than dept average

SELECT name, salary, dept_id
FROM employees e1
WHERE salary > (
SELECT AVG(salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id -- references outer query!
);
 SECTION 5: DBMS THEORY — ALL CONCEPTS

Thread vs Process

PROCESS:
─────────────────────────────────────┐
│ PROCESS (Chrome) │
│ ─────────────────────────────┐ │
│ │ Own Memory Space │ │
│ │ Own Resources │ │
│ │ Independent execution │ │
│ └─────────────────────────────┘ │
└─────────────────────────────────────┘

THREAD (within same process):

─────────────────────────────────────┐
│ PROCESS (Chrome) │
│ ───────┐ ───────┐ ───────┐ │
│ │Thread1│ │Thread2│ │Thread3│ │
│ │ Tab 1 │ │ Tab 2 │ │Download │
│ └───────┘ └───────┘ └───────┘ │
│ Shared Memory Space │
└─────────────────────────────────────┘

KEY DIFFERENCES:
────────────────┬──────────────┬───────────────┐
│ Aspect │ Process │ Thread │
├────────────────┼──────────────┼───────────────┤
│ Memory │ Own space │ Shared space │
│ Creation cost │ Heavy/Slow │ Light/Fast │
│ Communication │ IPC needed │ Direct (fast) │
│ Crash effect │ Independent │ Crashes all │
│ Example │ Chrome app │ Chrome tab │
└────────────────┴──────────────┴───────────────┘

MULTITHREADING = Multiple threads running simultaneously

Benefits: Better CPU utilization, faster execution
Problems: Race conditions, deadlocks, synchronization issues
Internal vs External Fragmentation

INTERNAL FRAGMENTATION:
━━━━━━━━━━━━━━━━━━━━━━
Memory allocated is LARGER than what's needed
Wasted space INSIDE allocated block

Example:
Process needs 18KB → System gives 20KB block
──────────────────────┐
│ Process Data (18KB) │
├──────────────────────┤
│ Wasted (2KB) ← INTERNAL FRAGMENTATION │
└──────────────────────┘

EXTERNAL FRAGMENTATION:
━━━━━━━━━━━━━━━━━━━━━━
Total free memory is enough BUT not CONTIGUOUS
Free spaces scattered, can't use them together

Example:
────┬──────┬────┬──────┬────┐
│Used│Free │Used│Free │Used│
│ │(4KB) │ │(4KB) │ │
└────┴──────┴────┴──────┴────┘
Total free = 8KB, but process needs 8KB CONTIGUOUS
→ FAILS! Even though total space exists

SOLUTION: Compaction (move processes to make contiguous space)

QUICK REMEMBER:
Internal = wasted space INSIDE block (given too much)
External = wasted space OUTSIDE blocks (fragmented holes)

Indexes — How They Work

WITHOUT INDEX:
Table with 1 million rows
 Query: SELECT * WHERE name = 'Alice'
→ Scans ALL 1 million rows one by one ← SLOW!

WITH INDEX (B-Tree):

[M]
/ \
[D-H] [N-Z]
/ \ / \
[A-C][I-L][N-P][Q-Z]

→ Jumps directly to 'Alice' ← FAST! (like a book index)

Types of Indexes:
- Primary Index: On primary key (auto-created)
- Secondary Index: On other columns
- Composite Index: On multiple columns
- Unique Index: No duplicate values

Trade-off:
✅ SELECT queries become FASTER
❌ INSERT/UPDATE/DELETE become SLOWER
(index must be updated too)

Views

-- VIEW = Virtual table based on a query

-- No data stored, just stores the query definition

-- Create a view
CREATE VIEW high_salary_emp AS
SELECT name, salary, dept_id
FROM employees
WHERE salary > 70000;

-- Use it like a regular table

SELECT * FROM high_salary_emp;

-- Benefits:
-- ✅ Security: hide sensitive columns
 -- ✅ Simplicity: complex query → simple view
-- ✅ Reusability: use view multiple times

-- Drop view
DROP VIEW high_salary_emp;

Stored Procedures vs Triggers

-- STORED PROCEDURE: Pre-compiled SQL, called manually

CREATE PROCEDURE GetHighEarners(IN min_salary DECIMAL)
BEGIN
SELECT name, salary
FROM employees
WHERE salary > min_salary;
END;

-- Call it
CALL GetHighEarners(70000);

-- Benefits: Faster (pre-compiled), Reusable, Secure

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

-- TRIGGER: Auto-executes on table event (INSERT/UPDATE/DELETE)

CREATE TRIGGER after_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO salary_log(emp_id, old_sal, new_sal, changed_at)
VALUES (OLD.emp_id, [Link], [Link], NOW());
END;

-- Now every time salary is updated → log is auto-created!

-- No need to call manually

Transaction Control
 BEGIN TRANSACTION;

UPDATE employees SET salary = salary - 500 WHERE emp_id = 1;

UPDATE employees SET salary = salary + 500 WHERE emp_id = 2;

-- If everything OK:
COMMIT; -- Make changes permanent

-- If something wrong:
ROLLBACK; -- Undo all changes in this transaction

-- SAVEPOINT: Partial rollback

BEGIN TRANSACTION;
UPDATE employees SET salary = 90000 WHERE emp_id = 1;
SAVEPOINT sp1; -- Save checkpoint here

UPDATE employees SET salary = 85000 WHERE emp_id = 2;

ROLLBACK TO sp1; -- Undo only changes after sp1

-- emp_id=1 change kept, emp_id=2 change undone

COMMIT;

SQL vs NoSQL

──────────────────┬──────────────────┬─────────────────────┐
│ Aspect │ SQL │ NoSQL │
├──────────────────┼──────────────────┼─────────────────────┤
│ Structure │ Tables, Rows │ Documents, Key-Value│
│ Schema │ Fixed (rigid) │ Flexible (dynamic) │
│ Scaling │ Vertical │ Horizontal │
│ ACID │ Yes │ Often No │
│ Examples │ MySQL, Postgres │ MongoDB, Redis │
│ Best for │ Banking, ERP │ Social media, IoT │
└──────────────────┴──────────────────┴─────────────────────┘

Use SQL when:

→ Data is structured, relationships important
 → Need ACID guarantees (banking, finance)

Use NoSQL when:

→ Data is unstructured, schema changes often
→ Need to scale horizontally (millions of users)
→ Speed > consistency (social media feeds)

SECTION 6: QUICK INTERVIEW ANSWERS

Most Common Questions & Crisp Answers

Q: Primary Key vs Unique Key?

A: Primary Key = Unique + NOT NULL, one per table
Unique Key = Unique but allows ONE NULL, multiple per table

Q: Candidate Key vs Super Key?

A: Candidate Key = minimal set to identify row uniquely
Super Key = candidate key + extra unnecessary attributes
All candidate keys are super keys, not vice versa

Q: RANK vs DENSE_RANK vs ROW_NUMBER?

A: RANK → gaps in ranking for ties (1,2,3,3,5)
DENSE_RANK → no gaps for ties (1,2,3,3,4)
ROW_NUMBER → always unique (1,2,3,4,5)

Q: WHERE vs HAVING?
A: WHERE → filters BEFORE grouping (works on rows)
HAVING → filters AFTER grouping (works on groups)

Q: What is a Foreign Key?

A: Column referencing Primary Key of another table
Maintains Referential Integrity
Prevents orphan records

Q: What is a View?
A: Virtual table based on query, no physical storage
Used for security, simplicity, reusability
Q: What is Multithreading?
A: Multiple threads executing simultaneously within one process
Threads share memory, faster than multi-processing
Risk: race conditions, need synchronization

SECTION 7: MOST ASKED — CHEAT SHEET

──────────────────────────────────────────────────────────────┐
│ TOP 10 THINGS TO MEMORIZE │
├──────────────────────────────────────────────────────────────┤
│ 1. Nth Highest → DENSE_RANK() OVER (ORDER BY sal DESC) │
│ 2. JOIN types → INNER/LEFT/RIGHT/FULL/SELF │
│ 3. LIKE patterns → %, _, NOT LIKE │
│ 4. GROUP BY + HAVING vs WHERE │
│ 5. RANK vs DENSE_RANK vs ROW_NUMBER │
│ 6. 1NF→2NF→3NF→BCNF (what each eliminates) │
│ 7. ACID (Atomicity, Consistency, Isolation, Durability) │
│ 8. Primary vs Unique vs Candidate key differences │
│ 9. Thread vs Process (memory, cost, crash behavior) │
│ 10. Internal vs External Fragmentation │
└──────────────────────────────────────────────────────────────┘

Interview Tip: Always think out loud when writing SQL. Say “I’m using DENSE_RANK
because it handles ties without gaps” — interviewers love when you explain your
reasoning, not just write the query!