SQL / MySQL

MASTER REVISION NOTES

Beginner to Advanced · 24 Topics · Interview Ready
Theory + Syntax + Examples + 30 Interview Q&A; — Hinglish mein

Prepared for: Er. Shubham Jain

Is PDF ko kaise use karein (Subah ki 1-baar revision strategy)

Priority Matlab Kitna time do

TIER-1 (SABSE IMP) Interview inhi pe tikta hai — Joins, GROUP BY, Subqueries, Indexes, Dhyan se padho, queries
Window Functions, Keys haath se likho

TIER-2 Theory + difference tables yaad karo — likhwate kam, puchte zaroor Difference tables ratta
hain karo

LIGHT Ek baar padh lo, concept clear ho — deep jaane ki zaroorat nahi 5 min skim karo

YAAD RAKHO: Interview se pehle wali subah: (1) Saare TIER-1 topics + unke code examples padho, (2) Comparison tables
(DELETE/TRUNCATE/DROP, PK/Unique, WHERE/HAVING...) revise karo, (3) Aakhri section ke 30 Q&A; + query patterns ek
baar bol kar dohrao. Total ~90 minute.

Roadmap — 24 Topics ek nazar mein

# Topic Priority

1 Database Basics (SQL vs MySQL, DDL/DML/DQL/DCL/TCL) TIER-2

2 Data Types (INT, DECIMAL, CHAR/VARCHAR, DATE...) TIER-2

3 CREATE / ALTER / DROP / TRUNCATE TIER-1

4 INSERT / UPDATE / DELETE TIER-1

5 SELECT & WHERE (+ NULL handling) TIER-1

6 Operators: AND/OR, IN, BETWEEN, LIKE TIER-1

7 ORDER BY, LIMIT, DISTINCT TIER-1

8 Aggregate Functions (COUNT, SUM, AVG, MIN, MAX) TIER-1

9 GROUP BY & HAVING TIER-1

10 Constraints & Keys (PK, FK, UNIQUE, CHECK...) TIER-1

11 JOINS (INNER, LEFT, RIGHT, SELF, CROSS) TIER-1

12 Subqueries (+ Second Highest) TIER-1

13 String / Date / Numeric Functions TIER-2

14 CASE Statement TIER-2

Beginner to Advanced · 24 Topics · 30 Q&A Page 1

SQL / MySQL — Master Revision Notes Interview Preparation · Hinglish Edition

# Topic Priority

15 Views TIER-2

16 Indexes (Clustered vs Non-clustered) TIER-1

17 Stored Procedures TIER-2

18 Functions & Triggers TIER-2

19 Window Functions (ROW_NUMBER, RANK...) TIER-1

20 CTE (WITH clause) TIER-1

21 Transactions & ACID TIER-2

22 Normalization (1NF, 2NF, 3NF) TIER-2

23 Query Optimization TIER-1

24 30 Interview Q&A; + Query Patterns TIER-1

Beginner to Advanced · 24 Topics · 30 Q&A Page 2

SQL / MySQL — Master Revision Notes Interview Preparation · Hinglish Edition

TIER-2 · THEORY PAKKI

TOPIC 1 — Database Basics KARO

Database = data ka organized collection (tables mein rows-columns). SQL ek language hai; MySQL ek RDBMS
software hai jo SQL chalata hai. (Jaise Hindi bhasha hai, WhatsApp app hai jisme Hindi likhte ho.)

Category Commands Kaam

DDL (Definition) CREATE, ALTER, DROP, TRUNCATE Structure banana/badalna

DML (Manipulation) INSERT, UPDATE, DELETE Data ke saath kaam

DQL (Query) SELECT Data dekhna

DCL (Control) GRANT, REVOKE Permissions

TCL (Transaction) COMMIT, ROLLBACK, SAVEPOINT Transaction safety

BASIC COMMANDS

SHOW DATABASES; -- saare DB dekho

CREATE DATABASE shop_db; -- naya DB
USE shop_db; -- select karo
DROP DATABASE shop_db; -- delete (dhyan se!)

INTERVIEW POINT: TRUNCATE DDL hai, DML nahi — kyunki wo table ko internally drop-recreate karta hai (row-by-row delete
nahi). Isliye fast hai aur rollback nahi hota.

TIER-2 · THEORY PAKKI

TOPIC 2 — Data Types KARO

Type Kab use kare Example

INT / BIGINT IDs, quantity / bahut bade numbers id INT

DECIMAL(p,s) PAISA - hamesha! (exact value) amount DECIMAL(10,2)

FLOAT/DOUBLE Scientific approx values (paisa NAHI!) temp FLOAT

CHAR(n) Fixed length (PIN, state code) state CHAR(2)

VARCHAR(n) Variable text (naam, email) name VARCHAR(100)

TEXT Lamba content notes TEXT

DATE / DATETIME Sirf date / date+time dob DATE

TIMESTAMP Auto record time (1970-2038, timezone-aware) created_at TIMESTAMP

ENUM Fixed options mein se ek status ENUM('valid','expired')

BOOLEAN True/False (andar TINYINT(1)) is_active BOOLEAN

INTERVIEW POINT: Paise ke liye FLOAT kabhi nahi — approx hota hai (10.50 kabhi 10.499999 ban jata hai). DECIMAL exact
hai. | Mobile/Aadhaar number ko VARCHAR/CHAR mein rakho: math nahi karni, leading zero bachana hai, +91 store karna hai,
INT range chhoti padti hai.

YAAD RAKHO: CHAR(10) mein 'MP' daalo toh bhi 10 bytes (padding); VARCHAR jitna data utni jagah. CHAR = Constant,
VARCHAR = VARiable.

TOPIC 3 — CREATE / ALTER / DROP / TRUNCATE TIER-1 · SABSE IMP

SYNTAX

Beginner to Advanced · 24 Topics · 30 Q&A Page 3

SQL / MySQL — Master Revision Notes Interview Preparation · Hinglish Edition

CREATE TABLE table_name (

column1 datatype,
column2 datatype
);

EXAMPLE

CREATE TABLE clients (

id INT,
client_name VARCHAR(100),
amount DECIMAL(12,2)
);
DESC clients; -- structure dekho
CREATE TABLE IF NOT EXISTS clients (...); -- error-safe
CREATE TABLE backup_t AS SELECT * FROM clients; -- copy with data

ALTER — structure badalna

EXAMPLE

ALTER TABLE clients ADD COLUMN mobile VARCHAR(15);

ALTER TABLE clients DROP COLUMN mobile;
ALTER TABLE clients MODIFY COLUMN client_name VARCHAR(150); -- sirf type
ALTER TABLE clients CHANGE COLUMN city client_city VARCHAR(50); -- naam+type
ALTER TABLE clients RENAME TO new_name;

DELETE vs TRUNCATE vs DROP — SUPERHIT interview table

Point DELETE TRUNCATE DROP

Kya hatata Rows (filter se) Saari rows Puri table

Category DML DDL DDL

WHERE? Haan Nahi Nahi

Rollback? Haan (transaction) Nahi Nahi

Speed Slow (row-by-row) Fast Fast

Auto-increment reset Nahi Haan Table hi gayi

YAAD RAKHO: Yaad rakhne ki trick — DELETE = ghar se selected saaman nikalna, TRUNCATE = pura ghar khali (ghar khada
hai), DROP = ghar hi gira dena.

TOPIC 4 — INSERT / UPDATE / DELETE TIER-1 · SABSE IMP

SYNTAX

INSERT INTO table (col1, col2) VALUES (v1, v2);

UPDATE table SET col = value WHERE condition;
DELETE FROM table WHERE condition;

EXAMPLE

-- Multiple rows ek saath (fast):

INSERT INTO clients (id, client_name, amount) VALUES
(1, 'Amit Traders', 45000),
(2, 'Priya Stores', 12500);

UPDATE clients SET amount = 50000, city = 'Indore' WHERE id = 1;

DELETE FROM clients WHERE amount < 10000 AND city = 'Ujjain';

GALTI SE BACHO: Bina WHERE ke UPDATE/DELETE = SAARI rows affect! Bachne ke liye: pehle same condition se SELECT
chala kar dekho, Workbench ka Safe Update Mode on rakho, ya transaction mein chalao.

Beginner to Advanced · 24 Topics · 30 Q&A Page 4

SQL / MySQL — Master Revision Notes Interview Preparation · Hinglish Edition

INTERVIEW POINT: INSERT mein column names likhna best practice — order yaad nahi rakhna padta, naya column add hone pe
bhi query nahi tooteti. 'Upsert' pucha jaye toh: INSERT ... ON DUPLICATE KEY UPDATE.

TOPIC 5 — SELECT & WHERE + NULL TIER-1 · SABSE IMP

SYNTAX

SELECT col1, col2 FROM table WHERE condition;

SELECT col AS 'Naya Naam' FROM table; -- alias

EXAMPLE

SELECT name, salary FROM employees WHERE salary > 30000;

SELECT name, salary * 1.18 AS with_gst FROM employees;
SELECT * FROM employees WHERE join_date < '2026-01-01';

NULL — Interview favourite

RULE: NULL ke saath = nahi, IS lagta hai

SELECT * FROM emp WHERE bonus = NULL; -- GALAT! kuch nahi milega
SELECT * FROM emp WHERE bonus IS NULL; -- SAHI
SELECT * FROM emp WHERE bonus IS NOT NULL; -- SAHI

INTERVIEW POINT: NULL = value hai hi nahi (0 aur '' toh values hain). NULL = NULL bhi true nahi hota! | SELECT * production
mein avoid karo — sirf zaroori columns mango (performance).

YAAD RAKHO: Execution order: likha jata hai SELECT-FROM-WHERE, chalta hai FROM-WHERE-SELECT. Ye concept GROUP
BY/HAVING mein kaam aata hai.

TOPIC 6 — Operators: AND/OR, IN, BETWEEN, LIKE TIER-1 · SABSE IMP

SYNTAX + EXAMPLES

WHERE city = 'Indore' AND salary > 30000 -- dono true

WHERE city = 'Indore' OR city = 'Dewas' -- koi ek
WHERE city IN ('Indore','Dewas','Ujjain') -- list shortcut
WHERE salary BETWEEN 20000 AND 50000 -- inclusive range!
WHERE name LIKE 'A%' -- A se shuru
WHERE name LIKE '%kumar%' -- kahin bhi kumar
WHERE city LIKE '_n%' -- 2nd letter n

Wildcard Matlab

% Kuch bhi, kitne bhi characters (zero bhi)

_ Exactly EK character

GALTI SE BACHO: AND ki priority OR se zyada — mix karo toh HAMESHA brackets lagao: (city='A' OR city='B') AND salary >
30000.

INTERVIEW POINT: LIKE 'A%' fast (index chalta hai), LIKE '%A%' slow (full scan — index bekaar). | NOT IN ki list mein NULL aa
jaye toh result KHALI aata hai — subquery mein IS NOT NULL lagao.

TOPIC 7 — ORDER BY, LIMIT, DISTINCT TIER-1 · SABSE IMP

SYNTAX

Beginner to Advanced · 24 Topics · 30 Q&A Page 5

SQL / MySQL — Master Revision Notes Interview Preparation · Hinglish Edition

SELECT ... ORDER BY col ASC|DESC; -- ASC default

SELECT ... ORDER BY col1 ASC, col2 DESC; -- multi-level sort
SELECT ... LIMIT 5; -- top 5
SELECT ... LIMIT offset, count; -- skip karke lo
SELECT DISTINCT col FROM table; -- duplicates hatao

EXAMPLE

-- Top 3 highest paid:

SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 3;

-- 2nd highest (LIMIT tarika): 1 skip, 1 lo

SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 1, 1;

-- Pagination formula: LIMIT (page-1)*size, size

-- Page 3, 10 per page => LIMIT 20, 10

INTERVIEW POINT: DISTINCT multiple columns pe COMBINATION dekhta hai — DISTINCT city, id mein id unique hai toh saari
rows aa jayengi. | Nth highest: LIMIT (N-1), 1.

TOPIC 8 — Aggregate Functions TIER-1 · SABSE IMP

SYNTAX + EXAMPLE

SELECT COUNT(*), SUM(salary), AVG(salary),

MIN(salary), MAX(salary)
FROM employees;
SELECT COUNT(DISTINCT city) FROM employees; -- unique ginti

COUNT(*) vs COUNT(column) — mechanism samjho

COUNT(*) COUNT(column)

Poori ROW ginta hai — row hai toh count hai, values se matlab Us column ki sirf NON-NULL values ginta hai — NULL skip!
nahi

'Building mein kitne flats?' — andar jhaanke bina 'Kitne flats mein extinguisher laga hai?' — andar dekhna
padega

NULL TRAP EXAMPLE

-- amounts: 100, 300, NULL, NULL

SELECT COUNT(*) FROM t; -- 4 (saari rows)
SELECT COUNT(amount) FROM t; -- 2 (NULL skip)
SELECT AVG(amount) FROM t; -- 200 = (100+300)/2, NULL divide mein bhi nahi!

GALTI SE BACHO: Aggregate ke saath normal column bina GROUP BY ke nahi likh sakte: SELECT name, MAX(salary) —
GALAT! Sahi: subquery ya ORDER BY+LIMIT. | COUNT(*) vs COUNT(1): practically same, same speed — 'COUNT(1) fast hai'
myth hai.

TOPIC 9 — GROUP BY & HAVING TIER-1 · SABSE IMP

SYNTAX + ORDER

SELECT col, AGG(...)

FROM table
WHERE row_filter -- grouping SE PEHLE
GROUP BY col
HAVING group_filter -- grouping KE BAAD (aggregate allowed!)
ORDER BY ... LIMIT ...;

EXAMPLE

Beginner to Advanced · 24 Topics · 30 Q&A Page 6

SQL / MySQL — Master Revision Notes Interview Preparation · Hinglish Edition

-- Dept-wise count aur total, sirf bade groups:

SELECT dept, COUNT(*) AS cnt, SUM(salary) AS total
FROM employees
WHERE status = 'active'
GROUP BY dept
HAVING SUM(salary) > 100000
ORDER BY total DESC;

Mechanism (dher wala funda): GROUP BY har unique value ka alag DHER banata hai; aggregate HAR DHER ke
andar alag chalta hai; har dher collapse hokar output ki 1 row banta hai.

WHERE HAVING

ROWS filter karta hai GROUPS filter karta hai

Grouping se PEHLE chalta hai Grouping ke BAAD chalta hai

Aggregate (COUNT/SUM) nahi laga sakte Aggregate laga sakte hain

INTERVIEW POINT: GOLDEN RULE: SELECT mein sirf wahi columns jo GROUP BY mein hain YA aggregate ke andar. |
Execution order (ratta): FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY > LIMIT.

TOPIC 10 — Constraints & Keys TIER-1 · SABSE IMP

SYNTAX + EXAMPLE (sab ek saath)

CREATE TABLE employees (

emp_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
mobile VARCHAR(15) UNIQUE,
salary DECIMAL(10,2) CHECK (salary > 0),
city VARCHAR(50) DEFAULT 'Indore',
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
ON DELETE CASCADE
);

Constraint Kaam Note

NOT NULL Khali nahi chhod sakte Mandatory fields

UNIQUE Duplicate nahi NULL allowed (multiple bhi!)

PRIMARY KEY Row ki unique pehchaan = NOT NULL + UNIQUE; sirf 1/table

FOREIGN KEY Doosri table ki PK ko refer Referential integrity

DEFAULT Value na do toh auto DEFAULT CURRENT_TIMESTAMP

CHECK Custom rule MySQL 8.0.16+ se enforce

AUTO_INCREMENT Auto number Delete pe gap rehta hai; TRUNCATE pe reset

Primary vs Unique — SUPERHIT

PRIMARY KEY UNIQUE KEY

NULL nahi ho sakta NULL ho sakta hai

Table mein sirf EK Multiple ho sakte hain

Clustered index banta hai Non-clustered index

Aadhaar card jaisa PAN/passport jaisa

INTERVIEW POINT: ON DELETE CASCADE = parent delete, children auto-delete. SET NULL = children bachte hain, FK NULL.
RESTRICT (default) = children hain toh parent delete hi nahi hoga. | Composite PK (a,b) = COMBINATION unique, individual
columns repeat ho sakte hain.

Beginner to Advanced · 24 Topics · 30 Q&A Page 7

SQL / MySQL — Master Revision Notes Interview Preparation · Hinglish Edition

TOPIC 11 — JOINS — Interview ka 40% TIER-1 · SABSE IMP

Zaroorat: Data alag tables mein hai (clients + orders), report mein saath chahiye — JOIN unhe ON condition ke basis
pe jodta hai.

SYNTAX (alias c, o + ON condition)

SELECT [Link], [Link], [Link]

FROM customers c
INNER JOIN orders o ON c.cust_id = o.cust_id;

JOIN Kaun aayega output mein Yaad rakhne ka tarika

INNER Sirf MATCHING rows dono taraf ki Sirf jodi wale couples

LEFT LEFT ki SAARI rows + right ka match (warna NULL) Left ke sab, single bhi (NULL
partner)

RIGHT RIGHT ki saari + left ka match LEFT ka mirror (kam use hota hai)

FULL OUTER Dono taraf ke sab (MySQL mein direct NAHI - LEFT UNION RIGHT se) Puri baraat

CROSS Har row x har row (m x n) — no ON Har ladke ki har ladki se jodi

SELF Table khud se judti hai (2 alias) Aaine ke saamne khud se milna

SUPERHIT pattern — 'Jinka koi record NAHI' (customers who never ordered)
EXAMPLE

SELECT [Link]
FROM customers c
LEFT JOIN orders o ON c.cust_id = o.cust_id
WHERE o.order_id IS NULL;
-- Logic: LEFT JOIN mein bina match wale NULL ke saath aaye,
-- IS NULL se wahi pakde! (2 aur tarike: NOT IN + IS NOT NULL, NOT EXISTS)

SELF JOIN — employee-manager (likhwate hain!)

EXAMPLE

-- Table: emp(emp_id, name, manager_id) - manager isi table mein!

SELECT [Link] AS employee, [Link] AS manager
FROM emp e
LEFT JOIN emp m ON e.manager_id = m.emp_id;
-- Mechanism: 2 'photocopies' - e (employee roop), m (manager roop).
-- e ka manager_id = m ka emp_id => number se naam mil gaya.
-- LEFT isliye: boss (manager_id NULL) bhi list mein aaye.

GALTI SE BACHO: LEFT JOIN mein condition ON mein vs WHERE mein rakhne se result badalta hai — WHERE join ke baad
chalta hai aur NULL rows kaat kar LEFT ko INNER bana deta hai. Right table ki filter ON mein rakho agar left ki saari rows
chahiye.

INTERVIEW POINT: JOIN + GROUP BY combo mein COUNT(*) nahi, COUNT(right_table.column) use karo — warna 0-record
wale ko bhi 1 gin lega (NULL row bhi row hai!).

TOPIC 12 — Subqueries + Second Highest TIER-1 · SABSE IMP

Subquery = query ke andar query. Pehle ANDAR wali chalti hai, uska result BAHAR wali use karti hai. Do-step
sawaalon ke liye: 'average se zyada kaun?'

3 JAGAH SUBQUERY

Beginner to Advanced · 24 Topics · 30 Q&A Page 8

SQL / MySQL — Master Revision Notes Interview Preparation · Hinglish Edition

-- 1) Single value (= ke saath):

SELECT name FROM emp
WHERE salary > (SELECT AVG(salary) FROM emp);

-- 2) List (IN ke saath):

SELECT name FROM customers
WHERE cust_id IN (SELECT cust_id FROM orders);

-- 3) FROM mein (derived table - ALIAS COMPULSORY!):

SELECT AVG(dept_total) FROM
(SELECT dept, SUM(salary) AS dept_total
FROM emp GROUP BY dept) AS t;

SECOND HIGHEST SALARY — Interview ka Baap (3 tarike bolo!)

EXAMPLE

-- Tarika 1: LIMIT (duplicate pe dhokha de sakta hai)

SELECT DISTINCT salary FROM emp ORDER BY salary DESC LIMIT 1, 1;

-- Tarika 2: MAX + subquery (safe)

SELECT MAX(salary) FROM emp
WHERE salary < (SELECT MAX(salary) FROM emp);
-- Logic: sabse bade ko hatao, bache mein jo sabse bada = 2nd!

-- Tarika 3: DENSE_RANK (modern - Topic 19 mein)

Correlated Subquery (advanced)

EXAMPLE

-- Har emp jo APNE dept ke avg se zyada kamata hai:

SELECT name, dept, salary FROM emp e1
WHERE salary > (SELECT AVG(salary) FROM emp e2
WHERE [Link] = [Link]); -- bahar ki row use ho rahi!
-- Har row ke liye alag chalti hai => slow. EXISTS bhi correlated hota hai.

INTERVIEW POINT: Normal subquery EK baar chalti hai; correlated HAR ROW ke liye. | Subquery multiple values de aur = lagaya
toh error 'returns more than 1 row' — IN lagao. | NOT EXISTS NULL-safe hai, NOT IN nahi.

UNION vs UNION ALL (do queries ke results jodna) — common question!

SYNTAX + EXAMPLE

SELECT city FROM customers

UNION -- duplicates HATATA hai (extra sort = slow)
SELECT city FROM suppliers;

SELECT city FROM customers

UNION ALL -- duplicates RAKHTA hai (no sort = FAST)
SELECT city FROM suppliers;

UNION UNION ALL JOIN (farak samjho)

Duplicates hatata hai Duplicates rakhta hai Rows ko jodta nahi, COLUMNS
side-by-side lata hai

Slow (dedupe sorting) Fast ON condition chahiye

Rows NEECHE jodti hain Rows NEECHE jodti hain Tables SIDE mein judti hain

INTERVIEW POINT: UNION ke rules: dono queries mein columns ki GINTI same + data types compatible hone chahiye. Column
names pehli query se aate hain. Duplicates ki tension nahi ho toh UNION ALL lagao — performance better.

EXISTS vs IN — kab kaunsa?

Beginner to Advanced · 24 Topics · 30 Q&A Page 9

SQL / MySQL — Master Revision Notes Interview Preparation · Hinglish Edition

IN EXISTS

Subquery ki poori LIST bana kar match karta hai Bas check karta hai — match MILA ya NAHI (ruk jata hai)

Chhoti list pe theek Bade data / correlated pe aksar fast

NOT IN + NULL = khatarnak (khali result) NOT EXISTS NULL-safe hai — prefer karo

TIER-2 · THEORY PAKKI

TOPIC 13 — String / Date / Numeric Functions KARO

STRING

CONCAT('Er. ','Shubham') -- 'Er. Shubham'

CONCAT_WS('-','SC','188') -- 'SC-188'
UPPER('abc') / LOWER('ABC') -- 'ABC' / 'abc'
LENGTH('Indore') -- 6
TRIM(' hi ') -- 'hi'
SUBSTRING('SC/FSA/188', 4, 3) -- 'FSA' (pos 4 se, 3 chars)
LEFT('Indore',3)/RIGHT(s,3) -- 'Ind' / last 3
REPLACE('2026','26','27') -- '2027'
LPAD('7', 3, '0') -- '007' (invoice numbers!)
-- GROUP_CONCAT (MySQL-special! GROUP BY ke saath values jodta hai):
SELECT dept, GROUP_CONCAT(name SEPARATOR ', ') FROM emp GROUP BY dept;
-- Output: IT | Amit, Priya, Rahul (ek row mein saare naam!)

DATE (DATEDIFF sabse imp!)

NOW() / CURDATE() -- abhi ka datetime / date

YEAR(d), MONTH(d), DAY(d), MONTHNAME(d)
DATEDIFF('2027-03-15', CURDATE()) -- dono mein kitne DIN
DATE_ADD(CURDATE(), INTERVAL 1 YEAR) -- 1 saal aage
DATE_SUB(CURDATE(), INTERVAL 30 DAY) -- 30 din pehle
DATE_FORMAT(d, '%d-%m-%Y') -- Indian format

NUMERIC + NULL HANDLERS

ROUND(412.567, 2) -- 412.57 CEIL(4.1) -- 5

FLOOR(4.9) -- 4 ABS(-5) -- 5
MOD(10,3) -- 1 (even/odd: MOD(n,2))
IFNULL(col, 0) -- NULL ho toh 0
COALESCE(a, b, 'def') -- pehli non-NULL value

TIER-2 · THEORY PAKKI

TOPIC 14 — CASE Statement (SQL ka if-else) KARO

SYNTAX

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE default_result
END

EXAMPLE

Beginner to Advanced · 24 Topics · 30 Q&A Page 10

SQL / MySQL — Master Revision Notes Interview Preparation · Hinglish Edition

SELECT name, salary,

CASE WHEN salary >= 50000 THEN 'High'
WHEN salary >= 25000 THEN 'Mid'
ELSE 'Low' END AS grade
FROM employees;

-- PIVOT trick (ek row mein status-wise counts):

SELECT
COUNT(CASE WHEN status='active' THEN 1 END) AS active_cnt,
COUNT(CASE WHEN status='inactive' THEN 1 END) AS inactive_cnt
FROM employees;
-- Match => 1, warna NULL => COUNT NULL skip karta hai!

GALTI SE BACHO: Upar se neeche check hota hai — PEHLI true condition pe ruk jata hai (badi condition pehle likho!). ELSE nahi
+ koi match nahi = NULL. END likhna mat bhoolo.

TIER-2 · THEORY PAKKI

TOPIC 15 — Views (Virtual Table) KARO

View = saved query jisko table jaisa naam mil gaya. Data store NAHI hota — har baar fresh query asli table pe
chalti hai (khidki hai, godown nahi). Isliye data hamesha up-to-date.

SYNTAX

CREATE VIEW naam AS (SELECT query);

CREATE OR REPLACE VIEW naam AS (...); -- update
DROP VIEW naam; -- table ka data SAFE!

EXAMPLE

-- Security: junior ko salary mat dikhao

CREATE VIEW staff_public AS
SELECT emp_id, name, city FROM employees; -- salary chhupa di!
SELECT * FROM staff_public WHERE city='Indore'; -- table jaisa use

INTERVIEW POINT: 2 fayde bolo: (1) Complex query ko simple naam (2) Security — sensitive columns chhupana. | GROUP
BY/JOIN wale complex views READ-ONLY hote hain.

TOPIC 16 — Indexes — 'Slow query fast kaise?' TIER-1 · SABSE IMP

Index = kitab ka index page. Bina index: MySQL har row check karta hai (Full Table Scan — slow). Index (B-Tree)
ke saath: seedha jump — millisecond!

SYNTAX

CREATE INDEX idx_city ON employees(city);

CREATE UNIQUE INDEX idx_mob ON employees(mobile);
CREATE INDEX idx_cs ON employees(city, status); -- composite
SHOW INDEXES FROM employees;
DROP INDEX idx_city ON employees;
-- PRIMARY KEY & UNIQUE pe index AUTO banta hai!

Clustered Index Non-Clustered Index

Data KHUD us order mein physically sorted Alag structure + asli data ka pointer

Table mein sirf 1 (MySQL/InnoDB = PRIMARY KEY) Multiple ho sakte hain

Dictionary jaisa (words khud sorted) Kitab ke peeche ka index (topic->page no.)

GALTI SE BACHO: Index FREE nahi: READ fast hota hai, lekin har INSERT/UPDATE/DELETE pe index bhi update hota hai =
WRITE slow + extra storage. 'Har column pe index laga dein?' = NAHI!

Beginner to Advanced · 24 Topics · 30 Q&A Page 11

SQL / MySQL — Master Revision Notes Interview Preparation · Hinglish Edition

INTERVIEW POINT: Kahan lagao: WHERE / JOIN (foreign keys!) / ORDER BY wale columns. Kahan nahi: chhoti tables,
kam-variety columns (gender), heavy-write tables. | Composite index (a,b) ka LEFTMOST RULE: WHERE b=5 akela => index
NAHI chalega, 'a' query mein hona chahiye.

TIER-2 · THEORY PAKKI

TOPIC 17 — Stored Procedures KARO

Saved SQL bundle jo database mein rehta hai — CALL se chalao. Fayde: reusability, pre-compiled (fast), security
(table access nahi dena padta), kam network traffic, SQL-injection se bachav.

SYNTAX + EXAMPLE

DELIMITER //
CREATE PROCEDURE GetByCity(IN p_city VARCHAR(50),
OUT p_total DECIMAL(12,2))
BEGIN
SELECT SUM(salary) INTO p_total
FROM employees WHERE city = p_city;
END //
DELIMITER ;

CALL GetByCity('Indore', @t); SELECT @t;

IN OUT INOUT

Value ANDAR jati hai (default) Jawab BAHAR aata hai Dono — andar bhi, modified bahar bhi

INTERVIEW POINT: DELIMITER kyun badalte: procedure ke andar ; hota hai — MySQL wahin ruk na jaye, isliye temporary //
banate hain, end mein wapas ;.

TIER-2 · THEORY PAKKI

TOPIC 18 — Functions & Triggers KARO

Procedure vs Function — SUPERHIT table (ratta!)

Stored Procedure Function

CALL se chalta hai SELECT ke ANDAR use hota hai

Return optional (0/1/multiple OUT) Exactly 1 value return COMPULSORY

INSERT/UPDATE/DELETE kar sakta Generally sirf calculation

Kaam karne wala (action) Jawab dene wala (calculation)

FUNCTION EXAMPLE

DELIMITER //
CREATE FUNCTION WithGST(amt DECIMAL(12,2)) RETURNS DECIMAL(12,2)
DETERMINISTIC
BEGIN
RETURN amt * 1.18;
END //
DELIMITER ;
SELECT name, WithGST(amount) FROM orders; -- query mein use!

Trigger — event pe AUTOMATIC chalta hai (koi CALL nahi)

TRIGGER EXAMPLE (audit trail)

Beginner to Advanced · 24 Topics · 30 Q&A Page 12

SQL / MySQL — Master Revision Notes Interview Preparation · Hinglish Edition

DELIMITER //
CREATE TRIGGER trg_log AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
IF [Link] != [Link] THEN
INSERT INTO salary_log (emp_id, old_sal, new_sal)
VALUES (OLD.emp_id, [Link], [Link]);
END IF;
END //
DELIMITER ;
-- 6 combos: BEFORE/AFTER x INSERT/UPDATE/DELETE

INTERVIEW POINT: OLD = purani value, NEW = nayi. INSERT mein sirf NEW, DELETE mein sirf OLD, UPDATE mein dono.
BEFORE = data validate/modify entry se pehle; AFTER = logging jaise side-effects. Nuksaan: chhupa hua hai, debugging mushkil.

TOPIC 19 — Window Functions — Modern TIER-1 · SABSE IMP

Interviews ka Hot Topic
GROUP BY rows ko nichod deta hai (5 rows -> 2); Window Function rows JINDA rakh kar bagal mein
calculation chipka deta hai (5 rows -> 5). OVER dikha = window function hai.

SYNTAX

AGG/RANKING() OVER (PARTITION BY col ORDER BY col)

-- PARTITION BY = groups banao BINA collapse (har group mein ginti restart)
-- ORDER BY (OVER ke andar) = kis order se rank/ginti

EXAMPLE

-- Har row ke saamne uske dept ka avg:

SELECT name, dept, salary,
AVG(salary) OVER (PARTITION BY dept) AS dept_avg
FROM employees; -- saari rows salamat + avg chipka hua!

ROW_NUMBER vs RANK vs DENSE_RANK — tie (80,80) pe farak

Function Output (90,80,80,70 pe) Yaad rakho

ROW_NUMBER() 1, 2, 3, 4 Cinema seat — sabki ALAG

RANK() 1, 2, 2, 4 Olympic — tie pe agla number SKIP

DENSE_RANK() 1, 2, 2, 3 Class rank — koi skip NAHI

TOP-PER-GROUP + NTH HIGHEST

-- BAAP QUESTION: Har department ka top earner

SELECT name, dept, salary FROM (
SELECT name, dept, salary,
ROW_NUMBER() OVER (PARTITION BY dept
ORDER BY salary DESC) AS rn
FROM employees
) AS ranked
WHERE rn = 1; -- Top 2 chahiye? WHERE rn <= 2

-- 2nd highest (modern, duplicate-safe):

SELECT DISTINCT salary FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rk
FROM employees) t
WHERE rk = 2;

GALTI SE BACHO: WHERE mein window function DIRECT nahi laga sakte (WHERE pehle chalta hai) — subquery ya CTE mein
lapeto. | LAG() = pichhli row ki value, LEAD() = agli row (month-on-month growth ke liye).

Beginner to Advanced · 24 Topics · 30 Q&A Page 13

SQL / MySQL — Master Revision Notes Interview Preparation · Hinglish Edition

TOPIC 20 — CTE — WITH Clause TIER-1 · SABSE IMP

CTE = query ke result ko temporary NAAM do, phir table ki tarah use karo — sirf usi query tak zinda. Nested
subquery ka saaf-suthra version: andar-se-bahar ki jagah upar-se-neeche padho (recipe style).

SYNTAX

WITH naam AS (
SELECT ...
)
SELECT ... FROM naam;

-- Multiple CTEs (comma se, WITH ek hi baar):

WITH step1 AS (...),
step2 AS (SELECT ... FROM step1) -- upar wala use kar sakta!
SELECT * FROM step2;

EXAMPLE (CTE + Window = modern jodi)

-- Top earner per dept — CTE style (interview mein YAHI likho):
WITH ranked AS (
SELECT name, dept, salary,
ROW_NUMBER() OVER (PARTITION BY dept
ORDER BY salary DESC) AS rn
FROM employees
)
SELECT name, dept, salary FROM ranked WHERE rn = 1;

Subquery CTE View

Usi query mein, andar-se-bahar padhna Usi query mein, seedha padhna, naam DATABASE mein SAVED (permanent)
se reuse

Chhota inline kaam Multi-step complex query Roz repeat hone wali query

INTERVIEW POINT: Recursive CTE (WITH RECURSIVE) = hierarchy/series ke liye — org chart (CEO se neeche tak), 1-100
series. Concept bolna kaafi hai, fresher se syntax nahi likhwate.

TIER-2 · THEORY PAKKI

TOPIC 21 — Transactions & ACID KARO

Transaction = statements ka atoot bundle: ya sab honge ya koi nahi. Bank transfer — paise katna + milna dono,
ya dono cancel. Beech mein crash? ROLLBACK!

SYNTAX + EXAMPLE

START TRANSACTION;
UPDATE accounts SET bal = bal - 1000 WHERE name='Amit';
UPDATE accounts SET bal = bal + 1000 WHERE name='Priya';
COMMIT; -- pakka (permanent)
-- ya ROLLBACK; -- sab wapas, jaise kuch hua hi nahi
-- SAVEPOINT p1; ... ROLLBACK TO p1; -- checkpoint (game jaisa)

ACID Matlab Bank example

Atomicity Sab ya kuch nahi (atoot) Katna+milna dono, ya dono nahi

Consistency Rules kabhi nahi tootenge Transfer ke pehle-baad total paisa same

Isolation Transactions mein taak-jhaank nahi Adhura transfer doosre ko nahi dikhta

Durability COMMIT = pathhar ki lakeer Commit ke baad bijli jaye, data safe

INTERVIEW POINT: MySQL default autocommit ON — har query auto-COMMIT. Default isolation level = REPEATABLE READ.
TRUNCATE/DROP (DDL) auto-commit ho jate hain isliye rollback nahi. Levels: READ UNCOMMITTED < READ COMMITTED <
REPEATABLE READ < SERIALIZABLE.

Beginner to Advanced · 24 Topics · 30 Q&A Page 14

SQL / MySQL — Master Revision Notes Interview Preparation · Hinglish Edition

TIER-2 · THEORY PAKKI

TOPIC 22 — Normalization (1NF, 2NF, 3NF) KARO

Normalization = data ko tables mein todna taaki har cheez sirf EK jagah ho — redundancy khatam, 3 anomalies
(Update/Insert/Delete) se bachav.

Form Rule Example violation

1NF Har cell mein EK (atomic) value products = 'Laptop, Mouse' (comma list) — GALAT

2NF 1NF + PURI composite key pe depend (no PK=(order_id, product_id) but product_name sirf product_id pe
partial dependency) depend — alag table mein bhejo

3NF 2NF + SIRF key pe depend (no transitive dept_name -> dept_id -> emp_id chain — departments alag table
dependency)

INTERVIEW POINT: Ratta line: 'The key (1NF), the whole key (2NF), and nothing but the key (3NF)!' Hindi: chaabi pe, puri chaabi
pe, sirf chaabi pe. | Denormalization = jaan-boojh kar duplicate rakhna reporting speed ke liye (kam JOINs) — senior-level point!

TOPIC 23 — Query Optimization — 95% Guaranteed TIER-1 · SABSE IMP

Question
PEHLA STEP: EXPLAIN

EXPLAIN SELECT * FROM emp WHERE city = 'Indore';

-- Dekho: type=ALL (full scan = SLOW!) ya ref/range (index = OK)
-- rows: kitni scan hongi (10 lakh me se 10 lakh? Problem!)

# Hathiyar Kya karo

1 Index lagao (sabse bada) WHERE / JOIN (FKs!) / ORDER BY columns pe

2 SELECT * band Sirf zaroori columns (covering index bonus)

3 Index-killers hatao Column pe function/math NAHI: YEAR(d)=2026 => d BETWEEN '2026-01-01' AND
'2026-12-31'; LIKE '%x%' avoid

4 LIMIT / pagination Zaroorat bhar rows hi mango

5 JOIN sahi JOIN columns indexed, unnecessary tables mat jodo

6 Correlated subquery -> JOIN Har-row wali subquery ko JOIN/EXISTS se badlo

7 UNION ALL > UNION Duplicates ki tension nahi toh ALL (no extra sort)

8 Sahi data types INT compare VARCHAR se fast; chhota type = chhota index

YAAD RAKHO: MODEL ANSWER (30-sec, ratta): 'Pehle EXPLAIN se dekhunga full scan ho raha hai ya index lag raha. Scan ho
raha toh WHERE/JOIN columns pe index. Phir SELECT *, column pe function, leading % wala LIKE check karunga. Correlated
subqueries JOIN mein, zyada rows pe LIMIT, aakhir mein data types aur normalization.'

Beginner to Advanced · 24 Topics · 30 Q&A Page 15

SQL / MySQL — Master Revision Notes Interview Preparation · Hinglish Edition

TOPIC R — GOLDEN RULES — Ye chalta hai / Ye TIER-1 · SABSE IMP

NAHI chalta
GALAT (nahi chalta / galat result) SAHI

WHERE col = NULL WHERE col IS NULL

SELECT name, MAX(salary) FROM emp; (bina GROUP BY) Subquery ya ORDER BY salary DESC LIMIT 1

WHERE COUNT(*) > 5 HAVING COUNT(*) > 5 (aggregate = HAVING)

SELECT city, name ... GROUP BY city (name kahin nahi) name ko GROUP BY mein daalo ya aggregate lagao

FROM-subquery bina alias (...) AS t — alias COMPULSORY

WHERE rn = 1 (window direct WHERE mein) Subquery/CTE mein lapet kar WHERE rn=1

Subquery 3 values de + '=' lagaya IN lagao (ya subquery ko 1 value tak limit karo)

NOT IN (subquery jisme NULL aa sakta) Subquery mein IS NOT NULL ya NOT EXISTS

WHERE YEAR(join_date) = 2026 (index mara) join_date BETWEEN '2026-01-01' AND '2026-12-31'

UPDATE/DELETE bina WHERE (sab uda!) Pehle SELECT se check, phir WHERE ke saath

ORDER BY, WHERE ka galat order SELECT>FROM>WHERE>GROUP BY>HAVING>ORDER

BY>LIMIT

Paisa FLOAT mein DECIMAL(p,2) — exact

YAAD RAKHO: Query likhne ka order (likhawat): SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY - LIMIT.
Chalne ka order (execution): FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY - LIMIT. Dono alag hain — ye
interview mein poocha jata hai!

Beginner to Advanced · 24 Topics · 30 Q&A Page 16

SQL / MySQL — Master Revision Notes Interview Preparation · Hinglish Edition

TOPIC 24 — 30 INTERVIEW QUESTIONS + TIER-1 · SABSE IMP

ANSWERS
Ye section interview se pehli raat + subah zaroor padho. Answers chhote aur bolne-layak rakhe hain — inhe
zor se bol kar practice karo!

A. Basics & Commands (Q1-Q8)

Q1. SQL aur MySQL mein kya difference hai?
Ans: SQL ek language hai (queries likhne ka standard); MySQL ek RDBMS software hai jo SQL queries ko chalata
hai. Jaise Hindi bhasha hai aur WhatsApp app.
Q2. DDL aur DML mein kya farak hai?
Ans: DDL structure ke liye — CREATE, ALTER, DROP, TRUNCATE. DML data ke liye — INSERT, UPDATE,
DELETE. SELECT ko DQL kehte hain.
Q3. DELETE, TRUNCATE aur DROP mein difference?
Ans: DELETE: DML, WHERE laga sakte, rollback possible, slow (row-by-row), auto-increment reset nahi.
TRUNCATE: DDL, saari rows ek saath, fast, rollback nahi, auto-increment reset. DROP: table hi structure samet
khatam.
Q4. TRUNCATE ko DDL kyun maante hain jabki wo data delete karta hai?
Ans: Kyunki wo rows individually delete nahi karta — table ko internally drop karke recreate kar deta hai.
Structure-level operation hai, isliye fast hai aur rollback nahi hota.
Q5. CHAR aur VARCHAR mein kya difference?
Ans: CHAR fixed-length (CHAR(10) mein 'MP' bhi 10 bytes lega — padding); VARCHAR variable (jitna data utni
jagah +1 byte). Fixed codes ke liye CHAR, naam/email ke liye VARCHAR.
Q6. Paise/salary ke liye kaunsa data type aur kyun?
Ans: DECIMAL(p,s) — exact value store karta hai. FLOAT/DOUBLE approximate hote hain — 10.50 kabhi
10.499999 ban sakta hai, hisaab mein galti aa jati hai.
Q7. NULL, 0 aur empty string ('') mein kya farak?
Ans: 0 aur '' values hain; NULL matlab value hai hi nahi (unknown/missing). NULL se comparison = se nahi hota —
IS NULL / IS NOT NULL lagta hai. NULL = NULL bhi true nahi hota.
Q8. Bina WHERE ke UPDATE chalane pe kya hoga? Kaise bachein?
Ans: Saari rows update ho jayengi! Bachav: pehle same condition se SELECT chala kar dekho, Workbench ka Safe
Update Mode, ya START TRANSACTION mein chalao taaki ROLLBACK ho sake.

B. Keys, Joins & Grouping (Q9-Q16)

Q9. Primary Key aur Unique Key mein difference?
Ans: PK: NULL nahi ho sakta, table mein sirf ek, clustered index banta hai. Unique: NULL allowed (multiple bhi), kai
ho sakte hain, non-clustered index. PK = NOT NULL + UNIQUE.
Q10. Foreign Key aur referential integrity kya hai?
Ans: FK ek column hai jo doosri table ki PK ko refer karta hai — do tables ka rishta. Referential integrity = rishta kabhi
nahi tootega: bina parent ke child record nahi ban sakta, aur children hote hue parent delete nahi hota (jab tak
CASCADE/SET NULL na ho).
Q11. ON DELETE CASCADE vs SET NULL?
Ans: CASCADE: parent delete hote hi children bhi auto-delete. SET NULL: children bach jate hain lekin unka FK
column NULL ho jata hai. RESTRICT (default): delete hone hi nahi deta.
Q12. WHERE aur HAVING mein difference?
Ans: WHERE rows filter karta hai grouping se PEHLE (aggregate nahi laga sakte); HAVING groups filter karta hai
grouping ke BAAD (COUNT/SUM laga sakte hain). Execution: FROM > WHERE > GROUP BY > HAVING > SELECT
> ORDER BY > LIMIT.
Q13. COUNT(*) aur COUNT(column) mein difference?

Beginner to Advanced · 24 Topics · 30 Q&A Page 17

SQL / MySQL — Master Revision Notes Interview Preparation · Hinglish Edition

Ans: COUNT(*) poori rows ginta hai — values se matlab nahi. COUNT(column) sirf us column ki non-NULL values
ginta hai — NULL skip. COUNT(1) practically COUNT(*) jaisa hi hai.
Q14. INNER JOIN aur LEFT JOIN mein difference?
Ans: INNER: sirf dono taraf matching rows. LEFT: left table ki SAARI rows + right ka match (match nahi toh right
columns NULL). 'Missing records' dhoondhne ke liye LEFT hi lagta hai.
Q15. Aise customers kaise nikaloge jinhone kabhi order nahi kiya? (3 tarike)
Ans: (1) LEFT JOIN + WHERE o.order_id IS NULL, (2) NOT IN subquery (subquery mein IS NOT NULL zaroor!), (3)
NOT EXISTS — ye NULL-safe hai. Interview mein teeno ka naam lo, ek likh do.
Q16. SELF JOIN kya hai? Kab use hota hai?
Ans: Table ko khud se jodna — do alias (e, m) ke saath, jab ek row ka jawab usi table ki doosri row mein ho. Classic:
employee-manager (manager_id usi table ke emp_id ko refer karta hai). Koi naya join type nahi — wahi INNER/LEFT,
dono taraf same table.

C. Subqueries, Windows, Views (Q17-Q23)

Q17. Second highest salary kaise nikaloge? (3 tarike)
Ans: (1) SELECT DISTINCT salary ... ORDER BY salary DESC LIMIT 1,1. (2) SELECT MAX(salary) WHERE salary
< (SELECT MAX(salary)...) — sabse bade ko hatao, bache ka max. (3) DENSE_RANK() OVER (ORDER BY salary
DESC) = 2 — duplicate-safe, modern. Bonus bolo: LIMIT wala duplicates pe dhokha de sakta hai.
Q18. Correlated subquery kya hai?
Ans: Wo subquery jo bahar wali query ki current row ka data use karti hai — isliye HAR ROW ke liye alag chalti hai
(normal subquery ek baar chalti hai). Powerful but slow — aksar JOIN se replace karke optimize karte hain.
Q19. ROW_NUMBER, RANK, DENSE_RANK mein difference?
Ans: Tie (same values) pe farak: ROW_NUMBER sabko alag number (1,2,3,4); RANK tie ko same number, agla skip
(1,2,2,4 — Olympic style); DENSE_RANK same number, koi skip nahi (1,2,2,3 — class rank).
Q20. GROUP BY aur Window Function mein kya farak?
Ans: GROUP BY rows ko collapse karke summary deta hai (5 rows se 2); Window function (OVER) saari rows jinda
rakh kar har row ke saath calculation chipka deta hai (5 rows se 5). PARTITION BY = bina collapse wale groups.
Q21. View kya hai? Data store karta hai?
Ans: View = saved query jo virtual table ki tarah dikhti hai. Data store NAHI hota — har baar fresh query asli table pe
chalti hai, isliye data hamesha updated. Fayde: complex query simple + security (sensitive columns chhupana).
Q22. CTE kya hai? View se kaise alag?
Ans: CTE (WITH clause) = query ke result ko temporary naam dena, sirf usi ek query tak zinda. View database mein
permanently save hota hai. CTE nested subqueries ko readable banata hai — upar se neeche padho. Recursive CTE
hierarchy (org chart) ke liye.
Q23. Stored Procedure aur Function mein difference?
Ans: Procedure CALL se chalta hai, return optional, INSERT/UPDATE/DELETE kar sakta hai. Function SELECT ke
andar use hota hai, exactly 1 value return karna compulsory, generally sirf calculation. Procedure = action, Function =
jawab.

D. Triggers, ACID, Design, Performance (Q24-Q30)

Q24. Trigger kya hai? OLD aur NEW kya hote hain?
Ans: Trigger = code jo INSERT/UPDATE/DELETE pe AUTOMATIC chal jata hai (BEFORE ya AFTER). OLD = purani
value, NEW = nayi value. INSERT mein sirf NEW, DELETE mein sirf OLD, UPDATE mein dono. Use: audit trail,
auto-validation.
Q25. ACID properties samjhao.
Ans: Atomicity: sab ya kuch nahi (bank transfer — katna+milna dono ya dono nahi). Consistency: rules kabhi nahi
tootenge (total paisa same rahega). Isolation: transactions ek doosre ka adhura kaam nahi dekhte. Durability:
COMMIT ke baad crash bhi ho, data safe.
Q26. COMMIT aur ROLLBACK kya hain?
Ans: START TRANSACTION ke baad changes temporary hote hain. COMMIT = permanent kar do; ROLLBACK =
sab undo, transaction se pehle wali halat. MySQL default autocommit ON hota hai; default isolation level
REPEATABLE READ.

Beginner to Advanced · 24 Topics · 30 Q&A Page 18

SQL / MySQL — Master Revision Notes Interview Preparation · Hinglish Edition

Q27. Normalization kya hai? 1NF, 2NF, 3NF?

Ans: Data ko todna taaki redundancy aur anomalies (update/insert/delete) na hon. 1NF: har cell mein atomic value.
2NF: puri composite key pe depend (no partial dependency). 3NF: sirf key pe depend (no transitive dependency).
'The key, the whole key, nothing but the key!'
Q28. Index kya hai? Nuksaan kya hai?
Ans: Kitab ke index jaisa data structure (B-Tree) jo SELECT fast karta hai — full table scan ki jagah seedha jump.
Nuksaan: har INSERT/UPDATE/DELETE pe index bhi update hota hai = write slow + extra storage. Isliye har column
pe nahi lagate.
Q29. Clustered aur Non-clustered index mein difference?
Ans: Clustered: data khud physically us order mein sorted, 1 per table (MySQL mein = PK) — dictionary jaisa.
Non-clustered: alag structure + data ka pointer, multiple ho sakte — kitab ke peeche ke index jaisa.
Q30. Slow query ko fast kaise karoge? (Model answer)
Ans: Pehle EXPLAIN se execution plan — full scan (type=ALL) ho raha hai ya index lag raha. Scan ho raha toh
WHERE/JOIN columns pe index. Phir: SELECT * hatao, column pe function hatao (YEAR(d)=2026 ki jagah
BETWEEN), leading % wala LIKE avoid, correlated subquery ko JOIN mein badlo, LIMIT/pagination lagao, sahi data
types aur normalization check karo.

Bonus Rapid-Fire (Q31-Q33)

Q31. UNION aur UNION ALL mein difference?
Ans: Dono do queries ke results neeche-neeche jodte hain. UNION duplicates hata deta hai (extra sorting = slow);
UNION ALL duplicates rakhta hai (fast). Rule: dono queries mein columns ki ginti same aur types compatible hone
chahiye. JOIN se farak: JOIN columns side-by-side jodta hai, UNION rows neeche jodta hai.
Q32. EXISTS aur IN mein kya farak? Kaunsa kab?
Ans: IN subquery ki poori list bana kar match karta hai — chhoti list pe theek. EXISTS bas check karta hai match mila
ya nahi (pehle match pe ruk jata hai) — bade/correlated data pe aksar fast. NOT EXISTS NULL-safe hai, NOT IN nahi
— isliye 'never ordered' type queries mein NOT EXISTS safer hai.
Q33. DISTINCT aur GROUP BY dono duplicates hatate hain — farak kya?
Ans: DISTINCT sirf unique rows dikhata hai — koi calculation nahi. GROUP BY groups bana kar har group pe
AGGREGATE (COUNT/SUM) chalane ke liye hai. Sirf unique values chahiye toh DISTINCT; group-wise summary
chahiye toh GROUP BY.

Beginner to Advanced · 24 Topics · 30 Q&A Page 19

SQL / MySQL — Master Revision Notes Interview Preparation · Hinglish Edition

TOPIC Q — 25 QUERY PATTERNS — Haath se likhne TIER-1 · SABSE IMP

ki practice karo!
Ye 25 queries interview mein LIKHWAI jati hain (LeetCode/HackerRank favourites bhi included). Roz 5-5 karke
kagaz pe likho — syntax haath mein baith jayega.

A. Salary / Ranking wale (1-8)

PATTERN 1

-- 1) SECOND HIGHEST SALARY (subquery tarika)

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

PATTERN 2

-- 2) NTH HIGHEST SALARY (N ki jagah number - duplicate-safe)

SELECT DISTINCT salary FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rk
FROM employees) t
WHERE rk = N;
-- Simple tarika: ORDER BY salary DESC LIMIT N-1, 1

PATTERN 3

-- 3) HAR DEPARTMENT KA TOP EARNER (CTE + window)

WITH ranked AS (
SELECT name, dept, salary,
ROW_NUMBER() OVER (PARTITION BY dept
ORDER BY salary DESC) AS rn
FROM employees
)
SELECT name, dept, salary FROM ranked WHERE rn = 1;

PATTERN 4

-- 4) HAR DEPT KE TOP 3 EARNERS (wahi pattern, bas rn <= 3)

-- Upar wali query mein: WHERE rn <= 3
-- Ties bhi chahiye toh ROW_NUMBER ki jagah DENSE_RANK lagao

PATTERN 5

-- 5) SECOND HIGHEST PER DEPARTMENT

WITH r AS (
SELECT name, dept, salary,
DENSE_RANK() OVER (PARTITION BY dept
ORDER BY salary DESC) AS rk
FROM employees
)
SELECT name, dept, salary FROM r WHERE rk = 2;

PATTERN 6

-- 6) APNE MANAGER SE ZYADA KAMANE WALE (LeetCode classic!)

SELECT [Link]
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
WHERE [Link] > [Link];

PATTERN 7

-- 7) APNE DEPT KE AVERAGE SE ZYADA KAMANE WALE (correlated)

SELECT name, dept, salary FROM employees e1
WHERE salary > (SELECT AVG(salary) FROM employees e2
WHERE [Link] = [Link]);

Beginner to Advanced · 24 Topics · 30 Q&A Page 20

SQL / MySQL — Master Revision Notes Interview Preparation · Hinglish Edition

PATTERN 8

-- 8) MAX SALARY BINA MAX/LIMIT FUNCTION KE (trick question!)

SELECT salary FROM employees
WHERE salary >= ALL (SELECT salary FROM employees);
-- Ya: SELECT DISTINCT salary FROM emp e1 WHERE
-- (SELECT COUNT(DISTINCT salary) FROM emp e2
-- WHERE [Link] > [Link]) = 0;

B. Duplicates & Data Cleanup (9-12)

PATTERN 9

-- 9) DUPLICATE RECORDS DHOONDHNA

SELECT name, COUNT(*) AS cnt FROM employees
GROUP BY name HAVING COUNT(*) > 1;

PATTERN 10

-- 10) DUPLICATES DELETE (pehla rakho, baaki udao - self join)

DELETE e1 FROM employees e1
JOIN employees e2 ON [Link] = [Link] AND [Link] > [Link];

PATTERN 11

-- 11) VALUES SWAP KARNA EK QUERY MEIN (LeetCode: m<->f swap)

UPDATE employees
SET gender = CASE WHEN gender = 'm' THEN 'f' ELSE 'm' END;

PATTERN 12

-- 12) EMAIL VALID/INVALID ALAG KARNA (pattern check)

SELECT * FROM users WHERE email LIKE '%_@_%._%';
-- Advanced: WHERE email REGEXP '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+$'

C. Joins & Missing Data (13-17)

PATTERN 13

-- 13) JINHONE KABHI ORDER NAHI KIYA (LEFT JOIN + IS NULL)

SELECT [Link] FROM customers c
LEFT JOIN orders o ON c.cust_id = o.cust_id
WHERE o.order_id IS NULL;

PATTERN 14

-- 14) EMPLOYEE-MANAGER NAAM SAATH MEIN (SELF JOIN)

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

PATTERN 15

-- 15) DO TABLES KE COMMON RECORDS (INTERSECT ka MySQL jugaad)

SELECT [Link] FROM table_a a
INNER JOIN table_b b ON [Link] = [Link];
-- Ya: WHERE name IN (SELECT name FROM table_b)

PATTERN 16

-- 16) TABLE A MEIN HAI, B MEIN NAHI (MINUS ka jugaad)

SELECT [Link] FROM table_a a
LEFT JOIN table_b b ON [Link] = [Link]
WHERE [Link] IS NULL;

PATTERN 17

Beginner to Advanced · 24 Topics · 30 Q&A Page 21

SQL / MySQL — Master Revision Notes Interview Preparation · Hinglish Edition

-- 17) SAME SALARY WALE EMPLOYEE PAIRS (self join, pair repeat na ho)
SELECT [Link], [Link], [Link]
FROM employees e1
JOIN employees e2 ON [Link] = [Link]
AND e1.emp_id < e2.emp_id;
-- < lagane se (A,B) aayega, (B,A) aur (A,A) nahi!

D. Grouping & Reports (18-21)

PATTERN 18

-- 18) DEPT-WISE COUNT (2+ log), ZYADA WALE PEHLE

SELECT dept, COUNT(*) AS cnt FROM employees
GROUP BY dept HAVING COUNT(*) >= 2
ORDER BY cnt DESC;

PATTERN 19

-- 19) SAAL-WISE KITNI HIRING HUI

SELECT YEAR(join_date) AS yr, COUNT(*) AS hired
FROM employees GROUP BY YEAR(join_date) ORDER BY yr;

PATTERN 20

-- 20) STATUS-WISE COUNT EK HI ROW MEIN (CASE pivot)

SELECT
COUNT(CASE WHEN status='active' THEN 1 END) AS active_cnt,
COUNT(CASE WHEN status='inactive' THEN 1 END) AS inactive_cnt
FROM employees;

PATTERN 21

-- 21) DEPT-WISE SAARE NAAM EK CELL MEIN (GROUP_CONCAT)

SELECT dept, GROUP_CONCAT(name ORDER BY name SEPARATOR ', ')
FROM employees GROUP BY dept;

E. Advanced / Window wale (22-25)

PATTERN 22

-- 22) RUNNING TOTAL (har row tak ka cumulative sum)

SELECT month, amount,
SUM(amount) OVER (ORDER BY month_no) AS running_total
FROM sales;

PATTERN 23

-- 23) PICHHLE MONTH SE COMPARE (LAG - growth nikalna)

SELECT month, amount,
amount - LAG(amount) OVER (ORDER BY month_no) AS growth
FROM sales;

PATTERN 24

-- 24) EVEN / ODD ROWS NIKALNA

SELECT * FROM (
SELECT e.*, ROW_NUMBER() OVER (ORDER BY emp_id) AS rn
FROM employees e) t
WHERE MOD(rn, 2) = 0; -- even rows (odd ke liye = 1)

PATTERN 25

-- 25) PEHLA AUR AAKHRI RECORD EK SAATH

(SELECT * FROM employees ORDER BY emp_id ASC LIMIT 1)
UNION ALL
(SELECT * FROM employees ORDER BY emp_id DESC LIMIT 1);

Interview Day — Aakhri Tips

Beginner to Advanced · 24 Topics · 30 Q&A Page 22

SQL / MySQL — Master Revision Notes Interview Preparation · Hinglish Edition

# Tip

1 Query likhte waqt SOCHTE HUE BOLO: 'pehle groups banaunga, phir HAVING se filter...' — silent mat raho, approach
dikhao.

2 Syntax bhool jao toh logic samjha do — interviewer approach dekhta hai, semicolon nahi.

3 Jo nahi aata, honestly bolo: 'ye use nahi kiya, but seekh lunga' — bluff pakda jata hai.

4 Difference questions pe TABLE format mein jawab do (2-3 points) — structured lagta hai.

5 Optimization pucha jaye toh EXPLAIN se shuru karo — practical banda lagte ho.

6 Second highest pe teeno tarike + duplicates wala point bolo — full marks pakka.

YAAD RAKHO: All the best Shubham! Ye PDF + haath se query practice + bol kar revision = interview crack. Tier-1 topics pehle,
phir 33 Q&A;, phir 25 patterns — bas yahi formula hai.

Beginner to Advanced · 24 Topics · 30 Q&A Page 23