SQL

Complete Interview Guide

DDL • DML • Joins • Aggregations • Subqueries • Indexes • Normalization • Window Functions

28 Topics • Deep • Crisp • Interview-Ready

■ TABLE OF CONTENTS

# Topic

01 SQL Basics & Categories (DDL, DML, DCL, TCL)

02 Data Types

03 DDL — CREATE, ALTER, DROP

04 DML — INSERT, UPDATE, DELETE

05 SELECT — Core Query Structure

06 WHERE Clause & Operators

07 ORDER BY & LIMIT

08 Aggregate Functions

09 GROUP BY & HAVING

10 JOINs — All Types with Diagrams

11 Subqueries & Nested Queries

12 Set Operators — UNION, INTERSECT, EXCEPT

13 String Functions

14 Date & Time Functions

15 NULL Handling — IS NULL, COALESCE, NULLIF

16 CASE Expression

17 Indexes — Types & Usage

18 Views

19 Stored Procedures & Functions

SQL Complete Interview Guide Page 2

# Topic

20 Triggers

21 Transactions & ACID

22 Normalization — 1NF to BCNF

23 Keys — Primary, Foreign, Unique, Composite

24 Constraints

25 Window Functions — RANK, ROW_NUMBER, LAG/LEAD

26 CTEs — WITH Clause

27 Query Execution Order & Optimization

28 SQL Quick Reference & Interview Q&A;

DDL • DML • Joins • Aggregations • Subqueries • Indexes • Normalization • Window Functions

SQL Complete Interview Guide Page 3

01 SQL Basics & Categories

SQL (Structured Query Language) is the standard language for managing and manipulating relational
databases.

Category Full Form Commands Purpose

DDL Data Definition Language CREATE, ALTER, DROP, TRUNCATE, RENAME Define/modify schema structure

DML Data Manipulation Language SELECT, INSERT, UPDATE, DELETE Manipulate data

DCL Data Control Language GRANT, REVOKE Manage permissions

TCL Transaction Control Language COMMIT, ROLLBACK, SAVEPOINT Manage transactions

DDL vs DML DDL DML

Auto-commit Yes — cannot rollback No — part of transactions

Affects Schema/Structure Data

Rollback Not possible Possible

02 Data Types

Category Type Description Example

Numeric INT / INTEGER Whole numbers age INT

Numeric BIGINT Large whole numbers id BIGINT

Numeric DECIMAL(p,s) Exact decimal — use for money salary DECIMAL(10,2)

Numeric FLOAT / DOUBLE Approximate decimal price FLOAT

String VARCHAR(n) Variable-length string name VARCHAR(100)

String CHAR(n) Fixed-length string code CHAR(6)

String TEXT Large text data description TEXT

Date/Time DATE Date only (YYYY-MM-DD) dob DATE

Date/Time DATETIME Date and time created_at DATETIME

Date/Time TIMESTAMP Auto-tracks time updated_at TIMESTAMP

Boolean BOOLEAN / TINYINT(1) True/false is_active BOOLEAN

Binary BLOB Binary large object photo BLOB

Interview Tip:
Use DECIMAL for monetary values — never FLOAT (floating-point imprecision can cause rounding errors).

DDL • DML • Joins • Aggregations • Subqueries • Indexes • Normalization • Window Functions

SQL Complete Interview Guide Page 4

03 DDL — CREATE, ALTER, DROP

-- CREATE TABLE
CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE NOT NULL,
dept_id INT,
salary DECIMAL(10,2) DEFAULT 0.00,
hired_on DATE,
FOREIGN KEY (dept_id) REFERENCES departments(id)
);

-- ALTER TABLE
ALTER TABLE employees ADD COLUMN phone VARCHAR(15);
ALTER TABLE employees MODIFY COLUMN name VARCHAR(200);
ALTER TABLE employees DROP COLUMN phone;
ALTER TABLE employees RENAME COLUMN name TO full_name;

-- DROP vs TRUNCATE vs DELETE

DROP TABLE employees; -- Removes table + structure entirely
TRUNCATE TABLE employees; -- Removes all rows, keeps structure, faster
DELETE FROM employees; -- Removes rows (can rollback, fires triggers)

Command Removes Structure? Can Rollback? WHERE clause? Triggers fired?

DROP Yes No No No

TRUNCATE No No (DDL) No No

DELETE No Yes Yes Yes

04 DML — INSERT, UPDATE, DELETE

DDL • DML • Joins • Aggregations • Subqueries • Indexes • Normalization • Window Functions

SQL Complete Interview Guide Page 5

-- INSERT
INSERT INTO employees (name, email, dept_id, salary)
VALUES ('Bharath', 'b@[Link]', 2, 75000.00);

-- INSERT multiple rows

INSERT INTO employees (name, email) VALUES
('Alice', 'alice@[Link]'),
('Bob', 'bob@[Link]');

-- UPDATE
UPDATE employees
SET salary = salary * 1.10 -- 10% raise
WHERE dept_id = 2;

-- DELETE
DELETE FROM employees
WHERE hired_on < '2020-01-01';

-- UPSERT (MySQL: INSERT ... ON DUPLICATE KEY UPDATE)

INSERT INTO employees (id, name, salary)
VALUES (1, 'Bharath', 80000)
ON DUPLICATE KEY UPDATE salary = 80000;

■ Always use WHERE in UPDATE and DELETE — without it, ALL rows are affected!

DDL • DML • Joins • Aggregations • Subqueries • Indexes • Normalization • Window Functions

SQL Complete Interview Guide Page 6

05 SELECT — Core Query Structure

SELECT column1, column2, aggregate_fn(col) -- 5. What to return

FROM table_name -- 1. Which table
JOIN other_table ON condition -- 2. Join tables
WHERE filter_condition -- 3. Filter rows
GROUP BY column1 -- 4. Group rows
HAVING group_filter -- 6. Filter groups
ORDER BY column1 ASC, column2 DESC -- 7. Sort result
LIMIT 10 OFFSET 20; -- 8. Paginate

Execution Order (critical interview topic!):

FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

-- Aliases
SELECT [Link] AS employee_name, [Link] AS department
FROM employees e
JOIN departments d ON e.dept_id = [Link];

-- DISTINCT — remove duplicates

SELECT DISTINCT dept_id FROM employees;

-- SELECT with expression

SELECT name, salary * 12 AS annual_salary FROM employees;

06 WHERE Clause & Operators

Operator Meaning Example

= Equal WHERE dept_id = 2

!= or <> Not equal WHERE status != 'inactive'

> >= < <= Comparison WHERE salary >= 50000

BETWEEN ... AND Range (inclusive) WHERE salary BETWEEN 40000 AND 80000

IN (...) Match any in list WHERE dept_id IN (1, 2, 3)

NOT IN Not in list WHERE status NOT IN ('deleted','banned')

LIKE Pattern match WHERE name LIKE 'A%'

IS NULL Check for NULL WHERE manager_id IS NULL

IS NOT NULL Check not NULL WHERE email IS NOT NULL

EXISTS Subquery returns rows WHERE EXISTS (SELECT ...)

AND / OR / NOT Logical operators WHERE age > 18 AND active = 1

LIKE Pattern Matching

DDL • DML • Joins • Aggregations • Subqueries • Indexes • Normalization • Window Functions

SQL Complete Interview Guide Page 7

WHERE name LIKE 'A%' -- Starts with A

WHERE name LIKE '%son' -- Ends with 'son'
WHERE name LIKE '%ar%' -- Contains 'ar'
WHERE code LIKE 'A_C' -- A + any one char + C (e.g. ABC, AXC)

07 ORDER BY & LIMIT

-- ORDER BY
SELECT name, salary FROM employees
ORDER BY salary DESC, name ASC; -- Sort by salary desc, then name asc

-- LIMIT + OFFSET (pagination)

SELECT * FROM employees
ORDER BY id
LIMIT 10 OFFSET 20; -- Page 3 (records 21-30)

-- TOP N per group — interview classic!

-- Get top 3 earners per department (using window function)
SELECT * FROM (
SELECT *, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
FROM employees
) ranked
WHERE rnk <= 3;

DDL • DML • Joins • Aggregations • Subqueries • Indexes • Normalization • Window Functions

SQL Complete Interview Guide Page 8

08 Aggregate Functions

Function Description NULL Handling Example

COUNT(*) Count all rows Includes NULLs COUNT(*) → 100

COUNT(col) Count non-null values in col Ignores NULLs COUNT(email) → 95

SUM(col) Sum of values Ignores NULLs SUM(salary)

AVG(col) Average of values Ignores NULLs AVG(salary)

MAX(col) Maximum value Ignores NULLs MAX(salary)

MIN(col) Minimum value Ignores NULLs MIN(hired_on)

GROUP_CONCAT Concatenate values (MySQL) Ignores NULLs GROUP_CONCAT(name)

SELECT
COUNT(*) AS total_employees,
COUNT(DISTINCT dept_id) AS dept_count,
SUM(salary) AS total_payroll,
AVG(salary) AS avg_salary,
MAX(salary) AS highest_salary,
MIN(salary) AS lowest_salary
FROM employees;

■ COUNT(*) vs COUNT(col): COUNT(*) counts all rows. COUNT(col) skips NULLs. Critical difference!

09 GROUP BY & HAVING

GROUP BY groups rows sharing common values. HAVING filters groups — it is to GROUP BY what WHERE is to
rows.

-- Count employees per department

SELECT dept_id, COUNT(*) AS emp_count, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept_id
HAVING COUNT(*) > 5 -- Only departments with > 5 employees
ORDER BY avg_sal DESC;

-- GROUP BY multiple columns

SELECT dept_id, job_title, COUNT(*) AS count
FROM employees
GROUP BY dept_id, job_title;

WHERE vs HAVING WHERE HAVING

Filters Individual rows Groups (after GROUP BY)

Used with Any SELECT Only with GROUP BY

Can use aggregates? No Yes

Execution order Before GROUP BY After GROUP BY

DDL • DML • Joins • Aggregations • Subqueries • Indexes • Normalization • Window Functions

SQL Complete Interview Guide Page 9

10 JOINs — All Types with Diagrams

A B A B A B A B

INNER LEFT RIGHT FULL OUTER

JOIN JOIN JOIN JOIN

JOIN Type Returns NULL rows? Use When

INNER JOIN Matching rows in BOTH tables No Only want records with match in both

LEFT JOIN All rows from left + matches from right Right side NULLs if no matchKeep all left rows, optionally join right

RIGHT JOIN All rows from right + matches from left Left side NULLs if no match Keep all right rows, optionally join left

FULL OUTER JOIN All rows from both tables NULLs on either side All records, whether matched or not

SELF JOIN Table joined with itself Depends Hierarchical data (employees + manag

CROSS JOIN Cartesian product (every combo) No Generate all combinations

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

-- LEFT JOIN — show employees even if no dept assigned

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

-- SELF JOIN — employee and their manager

SELECT [Link] AS employee, [Link] AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = [Link];

-- CROSS JOIN — all combinations

SELECT [Link], [Link] FROM colors c CROSS JOIN sizes s;

DDL • DML • Joins • Aggregations • Subqueries • Indexes • Normalization • Window Functions

SQL Complete Interview Guide Page 10

11 Subqueries & Nested Queries

A subquery is a query inside another query. It can be in SELECT, FROM, WHERE, or HAVING clauses.

Type Description Keyword

Scalar Subquery Returns single value (1 row, 1 col) =, !=, >, <

Row Subquery Returns single row (1 row, multiple cols) =, IN

Column Subquery Returns single column (multiple rows) IN, NOT IN, ANY, ALL

Table Subquery Returns multiple rows & cols FROM clause (derived table)

Correlated Subquery References outer query — runs once per outer row EXISTS, NOT EXISTS

-- Scalar subquery in WHERE

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

-- Column subquery with IN

SELECT name FROM employees
WHERE dept_id IN (SELECT id FROM departments WHERE location = 'Mumbai');

-- Correlated subquery with EXISTS

SELECT d.dept_name FROM departments d
WHERE EXISTS (
SELECT 1 FROM employees e WHERE e.dept_id = [Link] -- references outer 'd'
);

-- Derived table in FROM

SELECT dept_id, avg_sal FROM (
SELECT dept_id, AVG(salary) AS avg_sal
FROM employees GROUP BY dept_id
) dept_avgs
WHERE avg_sal > 60000;

12 Set Operators — UNION, INTERSECT, EXCEPT

Operator Returns Duplicates Columns Required

UNION Rows from query1 OR query2 Removed (distinct) Same count & compatible types

UNION ALL Rows from query1 OR query2 Kept (faster) Same count & compatible types

INTERSECT Rows in BOTH query1 AND query2 Removed Same count & compatible types

EXCEPT / MINUS Rows in query1 NOT in query2 Removed Same count & compatible types

DDL • DML • Joins • Aggregations • Subqueries • Indexes • Normalization • Window Functions

SQL Complete Interview Guide Page 11

-- UNION — combine results from two tables

SELECT name, email FROM customers
UNION
SELECT name, email FROM suppliers;

-- INTERSECT — emails in both tables

SELECT email FROM customers
INTERSECT
SELECT email FROM newsletter_subscribers;

-- EXCEPT — customers NOT in blacklist

SELECT id FROM customers
EXCEPT
SELECT customer_id FROM blacklist;

DDL • DML • Joins • Aggregations • Subqueries • Indexes • Normalization • Window Functions

SQL Complete Interview Guide Page 12

13 String Functions

Function Description Example Result

UPPER(str) Convert to uppercase UPPER('hello') 'HELLO'

LOWER(str) Convert to lowercase LOWER('SQL') 'sql'

LENGTH(str) String length LENGTH('hello') 5

SUBSTRING(str,pos,len) Extract substring SUBSTRING('Hello',1,3) 'Hel'

LEFT(str,n) First n chars LEFT('Bharath',3) 'Bha'

RIGHT(str,n) Last n chars RIGHT('Bharath',3) 'ath'

TRIM(str) Remove whitespace TRIM(' hi ') 'hi'

REPLACE(str,old,new) Replace text REPLACE('Hi Bob','Bob','Alice') 'Hi Alice'

CONCAT(s1,s2) Concatenate CONCAT('Hi',' ','SQL') 'Hi SQL'

INSTR(str,substr) Position of substr INSTR('Hello','ell') 2

LPAD(str,len,pad) Left pad to length LPAD('5',3,'0') '005'

FORMAT(num,d) Format number FORMAT(12345.6,2) '12,345.60'

14 Date & Time Functions

Function Description Example

NOW() Current date and time 2026-03-13 10:30:00

CURDATE() Current date only 2026-03-13

CURTIME() Current time only 10:30:00

DATE(datetime) Extract date part DATE(NOW())

YEAR(date) Extract year YEAR('2026-03-13') → 2026

MONTH(date) Extract month MONTH('2026-03-13') → 3

DAY(date) Extract day DAY('2026-03-13') → 13

DATEDIFF(d1,d2) Days between dates DATEDIFF('2026-12-31','2026-01-01') → 364

DATE_ADD(d,INTERVAL) Add interval DATE_ADD('2026-01-01', INTERVAL 30 DAY)

DATE_FORMAT(d,fmt) Format date DATE_FORMAT(NOW(),'%d-%m-%Y')

TIMESTAMPDIFF(unit,d1,d2) Difference in units TIMESTAMPDIFF(YEAR,dob,NOW())

-- Employees hired in the last 30 days

SELECT name FROM employees WHERE hired_on >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

-- Age calculation
SELECT name, TIMESTAMPDIFF(YEAR, dob, CURDATE()) AS age FROM employees;

DDL • DML • Joins • Aggregations • Subqueries • Indexes • Normalization • Window Functions

SQL Complete Interview Guide Page 13

15 NULL Handling — IS NULL, COALESCE, NULLIF

NULL = absence of value. NULL is not zero, not empty string, not false. Any comparison with NULL returns NULL
(not true/false).

Function/Operator Purpose Example Result

IS NULL Check if NULL WHERE mgr IS NULL Rows with no manager

IS NOT NULL Check not NULL WHERE email IS NOT NULL Rows with email

COALESCE(a,b,c) Return first non-NULL COALESCE(phone,email,'N/A') First non-null value

NULLIF(a,b) Return NULL if a=b NULLIF(score, 0) NULL if score=0

IFNULL(a,b) Return b if a is NULL (MySQL) IFNULL(phone,'unknown') 'unknown' if NULL

NVL(a,b) Oracle equivalent of IFNULL NVL(phone,'unknown') Oracle only

-- NULL traps — common interview gotcha

SELECT * FROM employees WHERE manager_id = NULL; -- WRONG! Returns 0 rows
SELECT * FROM employees WHERE manager_id IS NULL; -- CORRECT ✓

-- NULL in arithmetic
SELECT salary + bonus FROM employees; -- If bonus is NULL, result is NULL!
SELECT salary + COALESCE(bonus, 0) FROM employees; -- Safe ✓

-- COALESCE for fallback display

SELECT name, COALESCE(phone, email, 'No contact') AS contact FROM employees;

■ NULL = NULL is UNKNOWN (not TRUE). Use IS NULL, never = NULL.

16 CASE Expression

CASE is SQL's conditional expression — like an if-else inside a query.

DDL • DML • Joins • Aggregations • Subqueries • Indexes • Normalization • Window Functions

SQL Complete Interview Guide Page 14

-- Simple CASE (equality check)

SELECT name,
CASE dept_id
WHEN 1 THEN 'Engineering'
WHEN 2 THEN 'Marketing'
WHEN 3 THEN 'Finance'
ELSE 'Other'
END AS department_name
FROM employees;

-- Searched CASE (range/condition check)

SELECT name, salary,
CASE
WHEN salary >= 100000 THEN 'Senior'
WHEN salary >= 60000 THEN 'Mid-level'
WHEN salary >= 30000 THEN 'Junior'
ELSE 'Trainee'
END AS grade
FROM employees;

-- CASE in aggregate (conditional counting)

SELECT
COUNT(CASE WHEN gender = 'M' THEN 1 END) AS male_count,
COUNT(CASE WHEN gender = 'F' THEN 1 END) AS female_count
FROM employees;

DDL • DML • Joins • Aggregations • Subqueries • Indexes • Normalization • Window Functions

SQL Complete Interview Guide Page 15

17 Indexes — Types & Usage

Index = Data structure (usually B-Tree) that speeds up data retrieval at the cost of extra storage and slower writes.

Index Type Description When to Use

Primary Index On primary key — unique + not null Auto-created on PK

Unique Index Enforces uniqueness on column(s) Email, username, SSN

Composite Index On multiple columns Queries filtering by multiple cols

Full-Text Index For text search optimization LIKE '%word%' on large text

Clustered Index Data physically sorted by index One per table (usually PK)

Non-Clustered Separate structure pointing to data Secondary lookups

-- Create index
CREATE INDEX idx_emp_name ON employees(name);
CREATE UNIQUE INDEX idx_emp_email ON employees(email);
CREATE INDEX idx_emp_dept_salary ON employees(dept_id, salary); -- Composite

-- Drop index
DROP INDEX idx_emp_name ON employees;

-- Check query using EXPLAIN

EXPLAIN SELECT * FROM employees WHERE name = 'Bharath';
-- Look for 'Using index' in Extra column = index is being used ✓

Index Best Practices

✔ Index columns used in WHERE, JOIN ON, and ORDER BY
✔ Composite index — order matters: put most selective column first
✔ Too many indexes slow down INSERT/UPDATE/DELETE
✔ Avoid indexing low-cardinality columns (e.g. boolean, gender)
✔ Use EXPLAIN to verify index usage

18 Views

View = A virtual table defined by a stored SELECT query. No data stored — query runs on access.

DDL • DML • Joins • Aggregations • Subqueries • Indexes • Normalization • Window Functions

SQL Complete Interview Guide Page 16

-- Create view
CREATE VIEW high_earners AS
SELECT [Link], [Link], d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = [Link]
WHERE [Link] > 80000;

-- Use view like a table

SELECT * FROM high_earners ORDER BY salary DESC;

-- Update view
CREATE OR REPLACE VIEW high_earners AS
SELECT [Link], [Link], d.dept_name, [Link]
FROM employees e JOIN departments d ON e.dept_id = [Link]
WHERE [Link] > 80000;

-- Drop view
DROP VIEW high_earners;

Views: Pros & Cons Pros Cons

Data Simplifies complex queries No data stored — query re-runs each time

Security Hide sensitive columns Cannot always UPDATE/INSERT

Maintenance One place to update logic Can be slower than materialized views

DDL • DML • Joins • Aggregations • Subqueries • Indexes • Normalization • Window Functions

SQL Complete Interview Guide Page 17

19 Stored Procedures & Functions

Aspect Stored Procedure Function

Returns Zero or multiple values via OUT params Single value (mandatory)

Called with CALL proc_name() SELECT fn_name() or in expression

DML allowed? Yes (INSERT/UPDATE/DELETE) Usually only SELECT

Transaction control Yes (COMMIT/ROLLBACK) No

Use case Business logic, batch operations Calculation, reusable expression

-- Stored Procedure
DELIMITER //
CREATE PROCEDURE give_raise(IN dept INT, IN pct DECIMAL(5,2))
BEGIN
UPDATE employees
SET salary = salary * (1 + pct/100)
WHERE dept_id = dept;
END //
DELIMITER ;

CALL give_raise(2, 10.0); -- Give dept 2 a 10% raise

-- Function
DELIMITER //
CREATE FUNCTION annual_salary(monthly DECIMAL(10,2))
RETURNS DECIMAL(10,2) DETERMINISTIC
BEGIN
RETURN monthly * 12;
END //
DELIMITER ;

SELECT name, annual_salary(salary) AS annual FROM employees;

20 Triggers

Trigger = Automatically executes SQL code in response to INSERT, UPDATE, or DELETE on a table.

Trigger Timing Event Use Case

BEFORE INSERT Before row inserted Validate/transform data before save

AFTER INSERT After row inserted Log new records, update summary

BEFORE UPDATE Before row updated Prevent unauthorized changes

AFTER UPDATE After row updated Audit trail, sync tables

BEFORE DELETE Before row deleted Prevent delete of critical records

AFTER DELETE After row deleted Cascade cleanup, logging

DDL • DML • Joins • Aggregations • Subqueries • Indexes • Normalization • Window Functions

SQL Complete Interview Guide Page 18

-- Audit trigger: log salary changes

CREATE TRIGGER trg_salary_audit
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
IF [Link] != [Link] THEN
INSERT INTO salary_audit(emp_id, old_sal, new_sal, changed_at)
VALUES ([Link], [Link], [Link], NOW());
END IF;
END;

-- OLD = row before change NEW = row after change

-- BEFORE triggers: can modify NEW values
-- AFTER triggers: can only read OLD/NEW, not modify

DDL • DML • Joins • Aggregations • Subqueries • Indexes • Normalization • Window Functions

SQL Complete Interview Guide Page 19

21 Transactions & ACID

A transaction is a unit of work that is either fully completed or fully rolled back.

ACID Property Meaning Example

Atomicity All or nothing — either all ops succeed or all fail Transfer: debit + credit both succeed or both fail

Consistency DB moves from one valid state to another Balance can't go below 0 after transfer

Isolation Concurrent transactions don't interfere T1 & T2 see consistent data even running simultaneously

Durability Committed changes persist even after crash After COMMIT, data survives power failure

-- Transaction example: bank transfer

START TRANSACTION;

UPDATE accounts SET balance = balance - 1000 WHERE id = 1; -- Debit

UPDATE accounts SET balance = balance + 1000 WHERE id = 2; -- Credit

-- Check if both succeeded

COMMIT; -- Save changes permanently
-- or
ROLLBACK; -- Undo all changes if something went wrong

-- SAVEPOINT — partial rollback

SAVEPOINT sp1;
UPDATE accounts SET balance = 0 WHERE id = 3;
ROLLBACK TO SAVEPOINT sp1; -- Only undo this, not whole transaction

Transaction Isolation Levels

Level Dirty Read Non-Repeatable Read Phantom Read Performance

READ UNCOMMITTED Yes Yes Yes Fastest

READ COMMITTED No Yes Yes Fast

REPEATABLE READ No No Yes Moderate (MySQL default)

SERIALIZABLE No No No Slowest — most safe

DDL • DML • Joins • Aggregations • Subqueries • Indexes • Normalization • Window Functions

SQL Complete Interview Guide Page 20

22 Normalization — 1NF to BCNF

Normalization = Organizing a database to reduce redundancy and improve data integrity.

Normal Form Rule Violation Example Fix

1NF Atomic values only. No repeating groups or arrays.

phone = '9999,8888' (multi-value) Separate rows per phone number

2NF 1NF + Every non-key column fully depends onOrder

the WHOLE
table: (order_id,
primary key.
product_id) → dependency
(No partial product_name
Move product_name
— depends
applies toonly
composite
to Products
on product_id
PKs)
table

3NF 2NF + No transitive dependency (non-key col depends

Employee: another→
onemp_id non-key → dept_name
dept_idcol). Move
(dept_name
dept_name
depends
to Departments
on dept_idtable
not em

BCNF 3NF + Every determinant must be a candidate Rare

key. Stricter
— appliesthan
when
[Link] overlapping Further
candidate
decompose
keys existthe table

Denormalization:
Intentionally adding redundancy for performance (e.g. caching frequently joined data). Used in reporting/OLAP systems.

23 Keys — Primary, Foreign, Unique, Composite

Key Type Description Rules Example

Primary Key Uniquely identifies each row NOT NULL + UNIQUE. One per table. id INT PRIMARY KEY

Foreign Key Links to PK of another table Must match a valid PK or be NULL dept_id REFERENCES departmen

Unique Key Column(s) with unique values Can have one NULL (unlike PK) email VARCHAR UNIQUE

Composite Key PK spanning multiple columns Combination is unique PRIMARY KEY(order_id, product_i

Candidate Key Any column that could be PK Unique + not null email, id both qualify

Super Key Any set that uniquely identifies May have extra columns (id, email) together

Surrogate Key Artificial PK (no business meaning) Usually AUTO_INCREMENT id INT AUTO_INCREMENT

-- Foreign key with cascading actions

FOREIGN KEY (dept_id) REFERENCES departments(id)
ON DELETE CASCADE -- Delete employees when dept is deleted
ON UPDATE CASCADE; -- Update dept_id when [Link] changes

-- Other FK actions: SET NULL, SET DEFAULT, RESTRICT, NO ACTION

DDL • DML • Joins • Aggregations • Subqueries • Indexes • Normalization • Window Functions

SQL Complete Interview Guide Page 21

24 Constraints

Constraint Purpose Example

NOT NULL Column cannot be empty name VARCHAR(100) NOT NULL

UNIQUE All values in column must differ email VARCHAR UNIQUE

PRIMARY KEY Unique row identifier (NOT NULL + UNIQUE) id INT PRIMARY KEY

FOREIGN KEY Referential integrity between tables FOREIGN KEY(dept_id) REFERENCES depts(id)

CHECK Enforce custom validation rule CHECK (salary > 0)

DEFAULT Set default value if none provided status VARCHAR DEFAULT 'active'

AUTO_INCREMENT Auto-generate sequential integers id INT AUTO_INCREMENT

-- Adding constraint after table creation

ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary >= 0);
ALTER TABLE employees ADD CONSTRAINT fk_dept
FOREIGN KEY (dept_id) REFERENCES departments(id);

-- Drop constraint
ALTER TABLE employees DROP CONSTRAINT chk_salary;

25 Window Functions — RANK, ROW_NUMBER, LAG/LEAD

Window functions perform calculations across a set of rows related to the current row, without collapsing rows
like GROUP BY does.

Function Description Tie Handling

ROW_NUMBER() Unique sequential number 1,2,3... No ties — arbitrary for equals

RANK() Rank with gaps on ties Tied rows get same rank, next rank skips

DENSE_RANK() Rank without gaps on ties Tied rows get same rank, no gap

NTILE(n) Divide rows into n equal buckets Approximate equal groups

LAG(col,n) Value from n rows BEFORE current NULL for first n rows

LEAD(col,n) Value from n rows AFTER current NULL for last n rows

FIRST_VALUE(col) First value in window frame Based on ORDER BY

LAST_VALUE(col) Last value in window frame Needs frame specification

SUM/AVG/COUNT OVER Running totals / moving averages Works as aggregate over window

DDL • DML • Joins • Aggregations • Subqueries • Indexes • Normalization • Window Functions

SQL Complete Interview Guide Page 22

-- Syntax: fn() OVER (PARTITION BY col ORDER BY col ROWS/RANGE frame)

SELECT name, dept_id, salary,

ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS row_num,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS d_rank,
LAG(salary,1) OVER (PARTITION BY dept_id ORDER BY salary DESC) AS prev_sal,
LEAD(salary,1)OVER (PARTITION BY dept_id ORDER BY salary DESC) AS next_sal
FROM employees;

-- Running total
SELECT name, salary,
SUM(salary) OVER (ORDER BY hired_on ROWS UNBOUNDED PRECEDING) AS running_total
FROM employees;

-- Difference from: ROW_NUMBER vs RANK vs DENSE_RANK

-- Salaries: 100, 100, 90, 80
-- ROW_NUMBER: 1, 2, 3, 4
-- RANK: 1, 1, 3, 4 (gap after tie)
-- DENSE_RANK: 1, 1, 2, 3 (no gap)

DDL • DML • Joins • Aggregations • Subqueries • Indexes • Normalization • Window Functions

SQL Complete Interview Guide Page 23

26 CTEs — Common Table Expressions (WITH Clause)

A CTE is a named temporary result set defined within a query. Cleaner than subqueries. Lives only for the duration
of the query.

-- Basic CTE
WITH dept_avg AS (
SELECT dept_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept_id
)
SELECT [Link], [Link], d.avg_sal
FROM employees e
JOIN dept_avg d ON e.dept_id = d.dept_id
WHERE [Link] > d.avg_sal; -- Employees earning above dept average

-- Multiple CTEs chained

WITH
top_depts AS (
SELECT dept_id FROM employees GROUP BY dept_id HAVING COUNT(*) > 10
),
senior_staff AS (
SELECT * FROM employees WHERE YEAR(hired_on) < 2020
)
SELECT [Link] FROM senior_staff s
WHERE s.dept_id IN (SELECT dept_id FROM top_depts);

-- Recursive CTE — traverse hierarchies

WITH RECURSIVE emp_tree AS (
SELECT id, name, manager_id, 0 AS level
FROM employees WHERE manager_id IS NULL -- root
UNION ALL
SELECT [Link], [Link], e.manager_id, [Link]+1
FROM employees e
JOIN emp_tree t ON e.manager_id = [Link] -- recursive join
)
SELECT * FROM emp_tree ORDER BY level, name;

CTE vs Subquery CTE Subquery

Readability High — named and reusable in same query Nested — harder to read

Reuse Can reference same CTE multiple times Must repeat

Recursion Supports recursive queries No

Performance Similar (optimizer usually treats same) Similar

27 Query Execution Order & Optimization

SQL Execution Order — Critical for Interviews

DDL • DML • Joins • Aggregations • Subqueries • Indexes • Normalization • Window Functions

SQL Complete Interview Guide Page 24

Step Clause What Happens

1 FROM + JOIN Tables loaded and joined

2 WHERE Row-level filtering applied

3 GROUP BY Rows grouped

4 HAVING Group-level filtering applied

5 SELECT Columns evaluated, expressions computed

6 DISTINCT Duplicate rows removed

7 ORDER BY Result sorted

8 LIMIT / OFFSET Final rows selected

■ Column aliases defined in SELECT cannot be used in WHERE or HAVING — they don't exist yet at that execution
step!

Query Optimization Tips

✔ Use indexes on columns in WHERE, JOIN ON, ORDER BY
✔ Use EXPLAIN / EXPLAIN ANALYZE to understand query plan
✔ Avoid SELECT * — fetch only needed columns
✔ Filter early with WHERE before joining
✔ Avoid functions on indexed columns in WHERE: WHERE YEAR(dob)=1990 prevents index use
✔ Use EXISTS instead of IN for large subqueries (faster short-circuits)
✔ Avoid OR on different columns — use UNION instead for index efficiency
✔ LIMIT results when only a few rows are needed
✔ Avoid correlated subqueries in SELECT — can run once per row (N+1 problem)

DDL • DML • Joins • Aggregations • Subqueries • Indexes • Normalization • Window Functions

SQL Complete Interview Guide Page 25

28 SQL Quick Reference & Interview Q&A

Most-Asked Interview Questions

Question Answer

Difference between WHERE and HAVING? WHERE filters rows; HAVING filters groups. WHERE runs before GROUP BY; HAVING after

UNION vs UNION ALL? UNION removes duplicates; UNION ALL keeps them. UNION ALL is faster.

DELETE vs TRUNCATE vs DROP? DELETE = DML, can rollback, fires triggers. TRUNCATE = DDL, faster, no rollback. DROP re

What is a correlated subquery? A subquery that references the outer query — executes once per outer row. Can be slow.

Primary key vs Unique key? PK: NOT NULL + unique, one per table. Unique key: allows one NULL, can have multiple per

What is a clustered index? Index where data is physically sorted by the index key. One per table (usually the PK).

What is ACID? Atomicity, Consistency, Isolation, Durability — properties ensuring reliable transactions.

Can we use aggregate in WHERE? No — use HAVING or a subquery. WHERE runs before aggregation.

RANK vs DENSE_RANK? RANK skips numbers after ties (1,1,3). DENSE_RANK doesn't skip (1,1,2).

What is normalization? Organizing DB to reduce redundancy. 1NF→2NF→3NF→BCNF by removing dependencies.

What is a VIEW? Virtual table based on a query. No data stored. Simplifies complex queries.

Self JOIN use case? Comparing rows in same table — e.g. employee vs their manager.

Classic Interview Query Patterns

-- 2nd highest salary (no window function)
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

-- Nth highest salary (window function way)

SELECT salary FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees
) t WHERE rnk = 3; -- Change 3 for Nth

-- Duplicate rows
SELECT email, COUNT(*) FROM employees GROUP BY email HAVING COUNT(*) > 1;

-- Employees without a department

SELECT [Link] FROM employees e LEFT JOIN departments d ON e.dept_id = [Link]
WHERE [Link] IS NULL;

-- Running total
SELECT name, salary, SUM(salary) OVER (ORDER BY id) AS running_total
FROM employees;

-- Top N per group

SELECT * FROM (
SELECT *, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) r
FROM employees) t WHERE r <= 2;

Quick Reference Cheatsheet

DDL • DML • Joins • Aggregations • Subqueries • Indexes • Normalization • Window Functions

SQL Complete Interview Guide Page 26

EXECUTION ORDER: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER → LIMIT

JOINS: INNER = matching rows only | LEFT = all left | RIGHT = all right | FULL = all
NULLS: NULL = NULL is UNKNOWN | Use IS NULL, not = NULL
AGG: COUNT(*) counts NULLs | COUNT(col) skips NULLs
WINDOW: ROW_NUMBER=unique | RANK=gaps | DENSE_RANK=no gaps | LAG=prev | LEAD=next
INDEXES: Speed up reads, slow down writes | Use EXPLAIN to verify
NORMAL: 1NF=atomic | 2NF=no partial dep | 3NF=no transitive | BCNF=determinants→CK
ACID: Atomic=all-or-nothing | Consistent=valid state | Isolated=no interference | Durable=persists

SQL Complete Interview Guide • 28 Topics • Deep • Crisp • Interview-Ready

DDL • DML • Joins • Aggregations • Subqueries • Indexes • Normalization • Window Functions