Top 30 Intermediate SQL Interview Questions & Answers

JOINs, Subqueries, UNION, CASE WHEN, Views, Indexes, CTEs basics — yeh questions 1-2 years experience level ke liye hain. Har question mein Answer (English), Explanation (Hinglish) aur SQL Query ke saath complete guide. Data Insights par.

📑 Topics Covered in This Blog:

• JOINs — INNER, LEFT, RIGHT, FULL, SELF, CROSS (Q1-Q8)
• Subqueries — Single Row, Multi Row, Correlated (Q9-Q13)
• UNION, UNION ALL, INTERSECT, EXCEPT (Q14-Q15)
• CASE WHEN — Conditional Logic (Q16-Q17)
• Views (Q18-Q19)
• Indexes (Q20-Q21)
• String & Date Functions (Q22-Q24)
• Transactions — COMMIT, ROLLBACK (Q25-Q26)
• EXISTS vs IN (Q27)
• Scenario Based — Find Duplicates, Nth Salary (Q28-Q30)

📋 Level: Intermediate — 1-2 Years Experience. Yeh questions second round ya technical round mein puchhe jaate hain. JOINs aur Subqueries SQL ka core hain — inhe deeply samajhna zaroori hai. Prerequisite: Basic SQL Interview Questions (Data Insights par available).

Answer: A JOIN combines rows from two or more tables based on a related column (usually PK-FK relationship). Types: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, SELF JOIN, CROSS JOIN.

🎯 Explanation: JOIN do tables ko ek saath milata hai — jaise do Excel sheets ko ek common column se merge karna. Employees table mein dept_id hai, Departments table mein dept_id aur dept_name hai. JOIN se dono ko combine karo — employee ka naam aur uska department name ek result mein aayega. Bina JOIN ke tum ek time par sirf ek table se data nikal sakte ho.

JOIN Types — Visual Summary: ════════════════════════════════════════════════ INNER
JOIN : Only matching rows
from BOTH tables LEFT
JOIN : ALL rows
from LEFT + matching
from RIGHT RIGHT
JOIN : ALL rows
from RIGHT + matching
from LEFT FULL
JOIN : ALL rows
from BOTH tables SELF
JOIN : Table joins with ITSELF CROSS
JOIN : Every row of A x Every row of B (Cartesian)

Answer: INNER JOIN returns only the rows where the join condition is satisfied in BOTH tables. Rows that don't have a matching value in the other table are excluded from the result.

🎯 Explanation: INNER JOIN = intersection — dono tables mein jo common hai woh dikhao. Ek employee ka dept_id = 10 hai — agar departments table mein dept_id = 10 exist karta hai toh woh employee result mein aayega. Nahi karta toh nahi aayega. Orphan records (bina match ke) dono taraf se exclude ho jaate hain. Sabse commonly used JOIN hai — 80% queries mein INNER JOIN hota hai.

-- Get employee name with their department name
SELECT e.emp_id, e.name, d.dept_name
FROM employees e INNER
JOIN departments d
ON e.dept_id = d.dept_id;
-- Result: Only employees
WITH a valid department -- Employees with NULL dept_id = NOT included

HR

Answer: LEFT JOIN returns ALL rows from the left table and matching rows from the right table. If no match exists in the right table, NULL is returned for right table columns. RIGHT JOIN is the opposite — all rows from right table, matching from left.

🎯 Explanation: LEFT JOIN = "left table ka koi bhi row nahi chhodna." Agar left table mein koi employee bina department ke hai (dept_id NULL) — LEFT JOIN mein woh bhi aayega, sirf uska dept_name NULL hoga. Use case: "Saare employees dikhao, chahe unka department ho ya na ho." RIGHT JOIN = same lekin right table ke saare rows guarantee hain. Practice mein LEFT JOIN zyada use hota hai — RIGHT JOIN ke logic ko LEFT JOIN se achieve kar sakte ho (table order swap karke).

-- LEFT JOIN: ALL employees, even without department
SELECT e.name, d.dept_name
FROM employees e LEFT
JOIN departments d
ON e.dept_id = d.dept_id;
-- Find employees with NO department assigned
SELECT e.name
FROM employees e LEFT
JOIN departments d
ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;

JOIN Result:

Answer: FULL OUTER JOIN returns ALL rows from BOTH tables. Where there is no match, NULL is filled in for the missing side. It combines LEFT JOIN and RIGHT JOIN results. MySQL does not support FULL OUTER JOIN directly — it is simulated using UNION of LEFT and RIGHT JOIN.

🎯 Explanation: FULL JOIN = LEFT JOIN + RIGHT JOIN combined — koi row nahi chhodna dono tables se. Agar employee ka department nahi hai — woh bhi aayega (NULL dept). Agar department mein koi employee nahi hai — woh department bhi aayega (NULL emp). Complete picture milti hai — kaunse employees unassigned hain aur kaunse departments empty hain. MySQL mein FULL JOIN directly nahi hota — LEFT JOIN UNION RIGHT JOIN se simulate karte hain.

-- FULL OUTER JOIN in MySQL (using UNION)
SELECT e.name, d.dept_name
FROM employees e LEFT
JOIN departments d
ON e.dept_id = d.dept_id
UNION SELECT e.name, d.dept_name
FROM employees e RIGHT
JOIN departments d
ON e.dept_id = d.dept_id;

Answer: SELF JOIN is when a table is joined with itself. It requires table aliases to distinguish between the two instances of the same table. It is used for hierarchical data (employee-manager relationships) or comparing rows within the same table.

🎯 Explanation: SELF JOIN = table khud se milti hai apni — jaise ek register mein employee ka naam aur usi register mein uske manager ka naam bhi hai (manager bhi employee hai). Employee table mein manager_id column hai jo same table ke emp_id ko point karta hai. SELF JOIN se tum employee ka naam aur uske manager ka naam ek query mein nikal sakte ho. Aliases mandatory hain — warna kaise pata chalega kaunsi copy employee hai aur kaunsi manager.

-- Employee-Manager relationship using SELF JOIN
SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e INNER
JOIN employees m
ON e.manager_id = m.emp_id;
-- e = employee instance, m = manager instance -- Same table, two different aliases

Answer: CROSS JOIN returns the Cartesian product of two tables — every row from Table A is combined with every row from Table B. If Table A has 5 rows and Table B has 4 rows, CROSS JOIN returns 5×4 = 20 rows. No ON condition is needed.

🎯 Explanation: CROSS JOIN = har combination. Socho 3 sizes (S, M, L) aur 4 colors (Red, Blue, Green, Black) — har size ke saath har color ka combination = 3×4 = 12 rows. CROSS JOIN yehi karta hai. Real use case: product variants (size × color), test data generate karna, schedule combinations. Dhyan rakho — bade tables par CROSS JOIN bahut zyada rows return karta hai (10K × 10K = 10 crore rows!).

-- All combinations of products and colors
SELECT p.product_name, c.color_name
FROM products p CROSS
JOIN colors c;
-- If products has 3 rows and colors has 4 rows -- Result = 3 × 4 = 12 rows (all combinations)

Answer: INNER JOIN returns only matching rows from both tables — rows without a match are excluded. LEFT JOIN returns all rows from the left table regardless of match, and NULLs for unmatched right table columns.

🎯 Explanation: "Sirf un employees ki list dikhao jinka department assigned hai" — INNER JOIN. "Saare employees dikhao, jinka department nahi hai unhe bhi" — LEFT JOIN. INNER JOIN strict hai — matching chahiye. LEFT JOIN lenient hai — left table ka koi row bahar nahi jaata. Interview mein yeh comparison bahut common hai!

Answer: Yes, you can JOIN multiple tables in a single query by chaining JOIN clauses. Each JOIN adds another table to the result. The order of JOINs matters for readability but SQL optimizer handles execution order.

🎯 Explanation: Real-world queries mein 3-4 tables join karna common hai. Employees + Departments + Locations + Projects — sab ek query mein. Har JOIN ek nayi table add karta hai. Pehle employees ko departments se join karo, phir us result ko locations se join karo. Chain karte jaao jitni tables chahiye. Aliases use karo clearly — nahi toh columns identify karna mushkil ho jaata hai.

-- JOIN 3 tables: employees + departments + locations
SELECT e.name AS employee, d.dept_name AS department, l.city AS location
FROM employees e INNER
JOIN departments d
ON e.dept_id = d.dept_id INNER
JOIN locations l
ON d.location_id = l.location_id
ORDER BY e.name;

Answer: A Subquery (nested query / inner query) is a query written inside another query. The inner query executes first and passes its result to the outer query. Subqueries can be used in SELECT, FROM, WHERE, and HAVING clauses.

🎯 Explanation: Subquery ek query ke andar doosri query hai — jaise ek box ke andar doosra box. Pehle andar wali query chalti hai, uska result bahar wali query use karti hai. "Woh employees dikhao jinki salary average salary se zyada hai" — pehle AVG(salary) nikalo (inner query), phir us value se bade wale employees filter karo (outer query). Subqueries IN, EXISTS, =, >, < operators ke saath use hote hain.

-- Find employees with salary above average SELECT name, salary FROM employees WHERE salary > (
SELECT AVG(salary)
FROM employees );
-- Inner query runs first: AVG(salary) = 58000 -- Outer query: WHERE salary > 58000

Answer: Three types based on result: Single-row subquery returns one row, used with =, !=, >, <. Multi-row subquery returns multiple rows, used with IN, ANY, ALL. Correlated subquery references the outer query's column — executes once per outer row.

🎯 Explanation: Single-row: "Woh employee dikhao jiska salary maximum hai" — MAX() ek value deta hai. Multi-row: "Woh employees dikhao jo IT ya HR department mein hain" — subquery multiple dept_ids deta hai, IN se match karo. Correlated: Outer query ki har row ke liye inner query chalti hai — jaise har employee ke liye puchho "kya is employee ki salary uske department ki average se zyada hai?" — bahut powerful lekin slow bhi ho sakta hai.

-- Single-row subquery (returns one value)
SELECT *
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
-- Multi-row subquery (returns multiple rows) SELECT name FROM employees WHERE dept_id IN (
SELECT dept_id
FROM departments
WHERE location = 'Mumbai' );
-- Correlated subquery (references outer query) SELECT name, salary, department FROM employees e1 WHERE salary > (
SELECT AVG(salary)
FROM employees e2
WHERE e2.department = e1.department );
-- Finds employees earning above their dept average

Answer: A subquery in the FROM clause is called a Derived Table or Inline View. It acts as a temporary table that exists only during query execution. It must be given an alias. It is useful when you need to filter or join on aggregated data.

🎯 Explanation: FROM clause mein subquery ek virtual table ban jaata hai. Pehle andar wali query ek temporary result table banati hai — phir bahar wali query us temporary table par work karti hai. HAVING se jo kaam nahi ho sakta (jaise HAVING ke result par phir filter karna) — derived table se ho sakta hai. Alias mandatory hai — warna SQL samajh nahi paata is "table" ko reference kaise karein.

-- Derived Table: Dept avg salary, then filter > 55000 SELECT dept_summary.department, dept_summary.avg_sal FROM (
SELECT department, AVG(salary) AS avg_sal
FROM employees
GROUP BY department ) AS dept_summary
WHERE dept_summary.avg_sal > 55000;

Answer: Both retrieve data from multiple tables but differently. JOIN combines tables horizontally (adds columns). Subquery filters data using another query's result. JOINs are generally faster for large datasets. Subqueries are more readable for complex filtering logic.

🎯 Explanation: "Employee ka naam aur uska department naam chahiye" — JOIN use karo (dono tables ke columns chahiye). "Woh employees chahiye jinka department Mumbai mein hai" — Subquery use kar sakte ho ya JOIN bhi — depends on what else you need in SELECT. Generally: JOIN faster hota hai kyunki optimizer better handle karta hai. Subquery sometimes zyada readable hoti hai complex logic mein.

Answer: ANY returns TRUE if the condition is satisfied by AT LEAST ONE value returned by the subquery. ALL returns TRUE only if the condition is satisfied by ALL values returned by the subquery.

🎯 Explanation: ANY = "kisi ek se bhi zyada/kam ho toh chalega." ALL = "sabse bade/chhotey se bhi zyada/kam hona chahiye." > ANY (list) = list ki minimum se bada hona chahiye. > ALL (list) = list ke maximum se bada hona chahiye (sabse strict condition). > ANY = IN jaisa behavior deta hai. > ALL = MAX se bada.

-- ANY: Salary greater than ANY salary in IT dept SELECT name, salary FROM employees WHERE salary > ANY (
SELECT salary
FROM employees
WHERE department = 'IT' );
-- Salary > minimum IT salary (at least one match) -- ALL: Salary greater than ALL salaries in IT dept
SELECT name, salary
FROM employees
WHERE salary > ALL ( SELECT salary FROM employees WHERE department = 'IT' );
-- Salary > maximum IT salary (all must match)

Answer: UNION combines results of two or more SELECT statements vertically (stacks rows). UNION removes duplicates. UNION ALL keeps all rows including duplicates (faster than UNION). Both require same number of columns and compatible data types.

🎯 Explanation: UNION = SQL ka UNION set operation — do SELECT ke results ko ek saath stack karo (rows add karo). JOIN horizontal hota hai (columns add), UNION vertical hota hai (rows add). Ek company ke India employees + USA employees — dono alag tables mein — UNION se ek list mein laao. UNION duplicate remove karta hai (slow). UNION ALL duplicates keep karta hai (fast). Columns ki count aur types dono queries mein same honi chahiye.

-- UNION: Combine two queries, remove duplicates SELECT name, city FROM employees_india UNION
SELECT name, city
FROM employees_usa;
-- UNION ALL: Keep ALL rows including duplicates (faster) SELECT name, salary FROM employees_2024 UNION ALL
SELECT name, salary
FROM employees_2025;
-- Rules: Same column count + compatible types in both queries

Answer: JOIN combines tables horizontally — adds more columns to each row. UNION combines queries vertically — adds more rows (stacks results). JOIN relates tables by a key column. UNION stacks same-structure results from different queries.

🎯 Explanation: JOIN Excel mein "VLOOKUP/Merge" jaisa hai — do sheets ko common column se milana. UNION Excel mein "Copy-Paste rows neeche" jaisa hai — ek sheet ke baad doosri sheet paste karna. Join = more columns. Union = more rows. Dono completely alag concepts hain.

Answer: CASE WHEN is SQL's conditional expression — like IF-ELSE in programming. It evaluates conditions and returns corresponding values. It can be used in SELECT, WHERE, ORDER BY, GROUP BY clauses.

🎯 Explanation: CASE WHEN = SQL ka if-else. "Agar salary 70000 se zyada hai toh 'High', agar 40000 se zyada hai toh 'Medium', warna 'Low' — yeh category column banao." Yeh data transformation ke liye bahut use hota hai — raw data ko meaningful categories mein convert karna. Excel ke IF function ki tarah — lekin zyada powerful aur multiple conditions handle karta hai.

-- Categorize employees by salary
SELECT name, salary, CASE
WHEN salary > 70000
THEN 'High'
WHEN salary > 40000
THEN 'Medium'
ELSE 'Low'
END AS salary_category
FROM employees;
-- CASE WHEN in ORDER BY (custom sort)
SELECT *
FROM employees
ORDER BY CASE department
WHEN 'Sales'
THEN 1
WHEN 'IT'
THEN 2
ELSE 3
END;

Answer: CASE WHEN combined with SUM/COUNT inside GROUP BY creates a pivot-style output — rows become columns. This is called conditional aggregation and is very commonly used in reporting queries.

🎯 Explanation: Conditional aggregation = CASE WHEN + SUM/COUNT. "Har department ka employee count alag column mein dikhao — Sales column, IT column, HR column" — yeh pivot table SQL mein banate hain CASE WHEN se. SUM(CASE WHEN department = 'Sales' THEN 1 ELSE 0 END) = Sales mein kitne employees hain. Yeh data reporting mein bahut commonly use hota hai!

-- Pivot: Department count as columns
SELECT SUM(CASE WHEN department = 'Sales' THEN 1 ELSE 0 END) AS sales_count,
SUM(CASE WHEN department = 'IT' THEN 1 ELSE 0 END) AS it_count,
SUM(CASE WHEN department = 'HR' THEN 1 ELSE 0 END) AS hr_count
FROM employees;

Answer: A VIEW is a virtual table based on the result of a SELECT query. It does not store data physically — it stores the query definition. When you query a view, SQL executes the underlying SELECT. Views provide security, abstraction, and simplify complex queries.

🎯 Explanation: View = ek saved query jisko tum table ki tarah use kar sakte ho. Bahut complex JOIN query hai jo team mein sabko use karni hai — har baar poori query likhne ki jagah VIEW banao, naam do "employee_department_view", aur phir sab log uss naam se query karein. Data physically store nahi hota — sirf query definition store hoti hai. View se security bhi milti hai — kuch columns sensitive hain toh view mein woh exclude karo, user sirf view dekhe.

-- Create a VIEW CREATE VIEW emp_dept_view AS
SELECT e.emp_id, e.name, e.salary, d.dept_name, d.location
FROM employees e
JOIN departments d
ON e.dept_id = d.dept_id;
-- Use the VIEW like a regular table
SELECT *
FROM emp_dept_view
WHERE dept_name = 'IT';
-- Drop a VIEW
DROP VIEW emp_dept_view;

Answer: Yes, but with restrictions. A view is updatable only if it is based on a single table, does not use DISTINCT, GROUP BY, HAVING, aggregate functions, subqueries, or UNION. If any of these are used, the view becomes read-only.

🎯 Explanation: Simple views (ek table, koi aggregation nahi) pe UPDATE, INSERT, DELETE kar sakte hain — changes directly base table mein reflect hote hain. Complex views (JOINs, GROUP BY, DISTINCT waale) read-only hain — UPDATE karne ki koshish karo toh error aayega. Kyunki SQL nahi samajh paata ki complex view ke through update kaise karein — ambiguity hoti hai.

-- Simple updatable view (single table) CREATE VIEW active_employees AS
SELECT emp_id, name, salary, department
FROM employees
WHERE is_active = 1;
-- This UPDATE works! (Updates base table)

UPDATE active_employees
SET salary = 65000
WHERE emp_id = 101;
-- Complex view (JOIN) — READ ONLY, UPDATE will fail! CREATE VIEW emp_dept_view AS
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d
ON e.dept_id = d.dept_id;
-- UPDATE emp_dept_view ... → ERROR!

Answer: An Index is a database object that speeds up data retrieval operations on a table. It works like a book's index — instead of scanning every page (row), the database jumps directly to the location. Index stores a sorted copy of specified column values with pointers to actual rows.

🎯 Explanation: Index = table of contents (kitaab ki soochi). Bina index ke SQL har row scan karta hai (full table scan — slow). Index ke saath — seedha relevant rows pe jump karta hai (fast). Ek company ka 10 lakh employees table hai, tum name = 'Amit Kumar' dhundh rahe ho — bina index 10 lakh rows scan hongi, index ke saath direct jump. Trade-off: INSERT/UPDATE/DELETE thoda slow ho jaata hai (index bhi update karna padta hai) aur storage zyada lagg hai.

-- Create single column index CREATE INDEX idx_emp_name ON employees(name); -- Create composite index (multiple columns) CREATE INDEX idx_dept_salary ON employees(department, salary); -- Create unique index CREATE UNIQUE INDEX idx_email ON employees(email); -- Drop an index DROP INDEX idx_emp_name ON employees;

Answer: Indexes speed up SELECT queries but have trade-offs: (1) Slower INSERT, UPDATE, DELETE — index must be updated with data changes. (2) Extra storage space needed. (3) Index maintenance overhead. (4) Too many indexes can actually slow down overall database performance.

🎯 Explanation: Index ek double-edged sword hai. Padhna fast karta hai, likhna slow karta hai. Jab tum INSERT karte ho — data table mein jaata hai + index bhi update hota hai (ek kaam do baar). Bahut saare indexes matlab bahut saara overhead on every write. Kab use karein: WHERE, JOIN, ORDER BY mein frequently use hone wale columns par. Kab avoid karein: chhoti tables par (full scan faster), columns jinhein bahut zyada updates hote hain.

Answer: Common string functions in MySQL: UPPER, LOWER, LENGTH, TRIM, SUBSTRING, CONCAT, REPLACE, LEFT, RIGHT, INSTR.

🎯 Explanation: String functions text data manipulate karte hain — data cleaning aur transformation mein bahut use hote hain. "Saare names uppercase mein convert karo", "email se domain nikalo", "leading/trailing spaces hatao", "first name aur last name combine karo" — yeh sab string functions se hota hai. Data analysts ke liye yeh bahut important hain.

SELECT UPPER(name) AS upper_name, -- AMIT KUMAR LOWER(name) AS lower_name,
-- amit kumar LENGTH(name) AS name_length, -- 10 TRIM(name) AS trimmed_name, -- Remove leading/trailing spaces LEFT(name, 4) AS first_4, -- 'Amit' RIGHT(name, 5) AS last_5, -- 'Kumar' SUBSTRING(name, 1, 4) AS substr, -- 'Amit' CONCAT(name, ' - ', department), -- 'Amit Kumar - Sales' REPLACE(email, '@old.com', '@new.com') FROM employees;

Answer: Common date functions in MySQL: NOW(), CURDATE(), YEAR(), MONTH(), DAY(), DATEDIFF(), DATE_ADD(), DATE_FORMAT(), TIMESTAMPDIFF().

🎯 Explanation: Date functions date aur time data manipulate karte hain. "Employee kitne saal se company mein hai?", "Last 30 days ke orders dikhao", "Date ko different format mein display karo" — yeh sab date functions se hota hai. Data analytics mein date calculations bahut common hain — time-based reporting har jagah hoti hai.

SELECT NOW() AS current_datetime, -- 2026-01-15 10:30:00 CURDATE() AS today,
-- 2026-01-15 YEAR(join_date) AS join_year, -- 2023 MONTH(join_date) AS join_month, -- 6 DATEDIFF(CURDATE(), join_date) AS days_worked, TIMESTAMPDIFF(YEAR, join_date, CURDATE()) AS years_worked, DATE_FORMAT(join_date, '%d-%b-%Y') AS formatted_date FROM employees;
-- Employees who joined in last 30 days
SELECT *
FROM employees
WHERE join_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

Answer: Common numeric functions: ROUND, CEIL/CEILING, FLOOR, ABS, MOD, POWER, SQRT, FORMAT.

🎯 Explanation: Numeric functions calculations aur number formatting ke liye hain. ROUND percentage results ko 2 decimal places par round karta hai. CEIL hamesha upar round karta hai (3.1 → 4). FLOOR hamesha neeche (3.9 → 3). ABS negative value ko positive karta hai. Reporting mein numbers ko properly format karna zaroori hai — ROUND sabse common function hai.

SELECT ROUND(125.567, 2) AS rounded, -- 125.57 CEIL(125.1) AS ceiling,
-- 126 FLOOR(125.9) AS floor_val, -- 125 ABS(-500) AS absolute, -- 500 MOD(17, 5) AS remainder, -- 2 POWER(2, 10) AS power_of_2, -- 1024 ROUND(salary/1000, 1) AS salary_k -- 55.0 (55K format) FROM employees;

Answer: A Transaction is a group of SQL operations that are treated as a single unit. Either ALL operations succeed (COMMIT) or ALL are rolled back (ROLLBACK). Transactions follow ACID properties: Atomicity, Consistency, Isolation, Durability.

🎯 Explanation: Transaction = "ya sab hoga ya kuch nahi hoga." Example: Bank transfer — Account A se ₹5000 deduct karo, Account B mein ₹5000 add karo. Agar step 1 ho gaya lekin step 2 mein error aa gaya — ₹5000 gaayab! Transaction mein dono steps ek unit hain — agar step 2 fail hota hai toh step 1 bhi rollback ho jaayega. Data consistency ensure hoti hai. ACID properties yeh guarantee karte hain.

-- Bank Transfer Transaction START TRANSACTION;
-- Step 1: Deduct from Account A

UPDATE accounts
SET balance = balance - 5000
WHERE account_id = 101;
-- Step 2: Add to Account B

UPDATE accounts
SET balance = balance + 5000
WHERE account_id = 102;
-- If both successful: COMMIT;
-- Save changes permanently -- If any error occurs: -- ROLLBACK;
-- Undo all changes in this transaction

Answer: ACID stands for four properties that guarantee database transactions are processed reliably:

🎯 Explanation: ACID = database ki reliability guarantee. Atomicity = "ya poora transaction, ya bilkul nahi." Consistency = "har transaction ke baad data valid state mein." Isolation = "ek transaction doosre ko interfere nahi karta — jaise parallel transactions ek doosre ka kaam nahi bigadte." Durability = "COMMIT ke baad committed changes permanent hain — system crash bhi data nahi kho sakta." Yeh banking, e-commerce, medical jaise critical systems mein bahut important hai.

Answer: IN checks if a value matches any value in a list/subquery result. EXISTS checks if the subquery returns ANY rows at all (TRUE/FALSE). EXISTS is often faster than IN for large datasets, especially correlated subqueries.

🎯 Explanation: IN = "meri value is list mein hai?" — poori list scan karta hai. EXISTS = "kya koi row hai?" — pehla matching row milte hi ruk jaata hai. EXISTS short-circuit evaluation karta hai — isliye large datasets mein faster hota hai. EXISTS correlated subquery ke saath best kaam karta hai. IN simple list ya non-correlated subquery ke saath better hai.

-- IN: Employees in departments located in Mumbai SELECT name FROM employees WHERE dept_id IN (
SELECT dept_id
FROM departments
WHERE location = 'Mumbai' );
-- EXISTS: Same result, different approach SELECT e.name FROM employees e WHERE EXISTS (
SELECT 1
FROM departments d
WHERE d.dept_id = e.dept_id AND d.location = 'Mumbai' );
-- EXISTS just checks IF any row exists —
SELECT 1 is convention

Answer: Use GROUP BY on the column(s) you want to check for duplicates, then filter with HAVING COUNT(*) > 1.

🎯 Explanation: Duplicates dhundhne ke liye — GROUP BY se same values group karo, COUNT se batao kitni baar repeat hua, HAVING COUNT(*) > 1 se sirf duplicate groups rakho. Duplicate rows ke actual data chahiye — subquery ya JOIN use karo. Yeh data cleaning mein bahut common task hai — interview mein scenario-based question aata hai.

-- Find duplicate emails in employees table
SELECT email, COUNT(*) AS count
FROM employees
GROUP BY email
HAVING COUNT(*) > 1;
-- Get full rows of duplicate records SELECT * FROM employees WHERE email IN (
SELECT email
FROM employees
GROUP BY email
HAVING COUNT(*) > 1 )
ORDER BY email;

3

Answer: Multiple approaches: Using subquery (MAX of values less than MAX), using LIMIT with OFFSET, or using DISTINCT with ORDER BY.

🎯 Explanation: Yeh sabse classic SQL interview question hai — "2nd highest salary kaise nikaaloge?" Teen approaches hain: (1) Subquery approach — MAX ki jagah SECOND MAX nikaalo by excluding the overall MAX. (2) LIMIT + OFFSET — ORDER BY DESC karke 2nd row lo. (3) Nth salary ke liye general approach — Advanced mein DENSE_RANK window function se aasaan ho jaata hai. Yeh approaches yaad rakho — interview mein zaroor aata hai!

-- Method 1: Subquery approach
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
-- Method 2: LIMIT + OFFSET
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1
OFFSET 1;
-- OFFSET 0 = 1st, OFFSET 1 = 2nd, OFFSET 2 = 3rd... -- Method 3: Nth highest (generic) — change N here
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1
OFFSET (N - 1);
-- 3rd highest: OFFSET 2, 4th highest: OFFSET 3

Answer: Keep the row with the minimum (or maximum) id for each duplicate group, and delete all others. Use a self-join or subquery with MIN/MAX on the primary key.

🎯 Explanation: Duplicate rows remove karne hain lekin ek rakhnaa hai — practical data cleaning scenario hai. Strategy: Har duplicate group mein sabse purani row (minimum ID) rakho, baaki delete karo. Self-join ya subquery se — "woh rows delete karo jinki ID, same email waali minimum ID se zyada hai." Yeh MySQL mein thoda tricky hai — direct subquery same table par DELETE + SELECT allowed nahi hota — workaround use karta hai.

-- Method 1: Delete duplicates using self-join DELETE e1 FROM employees e1 INNER JOIN employees e2 ON e1.email = e2.email AND e1.emp_id > e2.emp_id;
-- Keeps row with minimum emp_id for each email -- Deletes all rows with higher emp_id (duplicates) -- Method 2: Using subquery in MySQL (workaround) DELETE FROM employees WHERE emp_id NOT IN ( SELECT min_id FROM ( SELECT MIN(emp_id) AS min_id FROM employees GROUP BY email ) AS temp );

Next: SQL Interview Questions — Advanced (30 Questions)

Agle blog mein cover karenge: Window Functions (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE), CTEs (WITH clause), Recursive CTEs, Stored Procedures, Triggers, Query Optimization, EXPLAIN plan, Scenario-based advanced questions (Running Total, YoY, Gaps in sequences) — 2+ years experience level ke liye. Basic aur Intermediate SQL Interview Questions Data Insights par available hain.

Happy Learning & Keep Querying! 🚀