TCS / Infosys Technical Round Me SQL Kya Poochte Hain

Bhai, service-based companies ke technical round ka ek pattern hai jo saalon se nahi badla — SQL almost hamesha aata hai. Chahe tumne Java bola ho, Python bola ho ya MERN, ek na ek sawaal database se ghumega. Aur dikkat yeh hai ki internet pe har "TCS NQT syllabus" article sirf topic ki list de deta hai — JOIN, normalization, ACID — par ye kabhi nahi dikhaata ki query chalane pe output aata kya hai.

Isi liye yeh blog thoda alag hai. Neeche har important question ke saath ek chhoti sample table hai, query hai, aur "output kya aayega" — actual result rows. Kyunki interview me tumse yehi poocha jaata hai: "is query ka output batao." Poora question bank aur practice ke liye SQL chapter pe jaana, wahan har topic ke code, MCQ aur flashcards hain.

Pehle ek sample table fix kar lete hain. Neeche saari queries isi table pe chalengi:

employees

id	name	dept	salary	city	manager_id
1	Amit	Sales	50000	Delhi	NULL
2	Neha	Tech	90000	Mumbai	1
3	Raj	Tech	70000	Pune	2
4	Priya	Sales	60000	Delhi	1
5	Karan	Tech	90000	Mumbai	2
6	Zoya	HR	45000	NULL	1

SELECT aur WHERE — warm-up jo log yahin girte hain

Pehla sawaal hamesha aasan hota hai, par strict comparison pe log fisal jaate hain.

SELECT name, salary FROM employees
WHERE dept = 'Tech' AND salary > 70000;

Output:

name  | salary
------|-------
Neha  | 90000
Karan | 90000

Raj kyun nahi aaya? Uski salary exactly 70000 hai, aur condition > 70000 hai — strictly zyada. >= likhte to Raj bhi aata. Interviewer yeh boundary jaan-boojh ke set karta hai.

Ab NULL waala gotcha, jo mera favourite hai:

SELECT name, city FROM employees WHERE city <> 'Delhi';

Output:

name  | city
------|--------
Neha  | Mumbai
Raj   | Pune
Karan | Mumbai

Zoya gayab hai. Kyun? Uski city NULL hai, aur NULL <> 'Delhi' ka result TRUE nahi hota — wo UNKNOWN hota hai, aur WHERE sirf TRUE rows rakhta hai. NULL kisi bhi comparison me participate nahi karta. Sahi tareeka:

SELECT name, city FROM employees
WHERE city <> 'Delhi' OR city IS NULL;

Ab Zoya bhi aa jaayegi — 4 rows. Yaad rakho: NULL ke liye hamesha IS NULL / IS NOT NULL, kabhi = NULL nahi.

GROUP BY vs WHERE vs HAVING — THE most-asked concept

Ye combo har service-based interview me aata hai. Pehle simple grouping:

SELECT dept, COUNT(*) AS cnt, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept;

Output:

dept  | cnt | avg_sal
------|-----|---------
Sales | 2   | 55000
Tech  | 3   | 83333.33
HR    | 1   | 45000

Sales = (50000 + 60000) / 2 = 55000. Tech = (90000 + 70000 + 90000) / 3 = 250000/3 = 83333.33. Row ka order guarantee nahi hota — chahiye to ORDER BY dept lagao.

Ab asli sawaal — WHERE aur HAVING dono ek saath:

SELECT dept, COUNT(*) AS cnt
FROM employees
WHERE salary > 50000
GROUP BY dept
HAVING COUNT(*) > 1;

Output:

dept | cnt
-----|----
Tech | 3

Step by step trace karo, isi tarah interview me bolna hai:

1. WHERE pehle chalta hai — Amit (50000, strictly zyada nahi) aur Zoya (45000) rows hi hat gayi.
2. Bache: Neha, Raj, Karan (Tech), Priya (Sales).
3. GROUP BY — Tech = 3 rows, Sales = 1 row.
4. HAVING — groups pe filter. Sales ka count 1 hai, fail. Sirf Tech bacha.

Engine ka order yaad rakho: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Isi liye WHERE me COUNT(*) > 1 nahi likh sakte — us stage pe group bana hi nahi hota.

Ek aur chhota par common sawaal — COUNT(*) vs COUNT(column):

SELECT COUNT(*) AS total, COUNT(city) AS with_city,
       COUNT(DISTINCT dept) AS depts
FROM employees;

Output:

total | with_city | depts
------|-----------|-------
6     | 5         | 3

COUNT(*) saari rows ginta hai (6). COUNT(city) NULL skip karta hai, isliye 5. COUNT(DISTINCT dept) unique dept ginta hai — Sales, Tech, HR = 3.

JOINs — yahi sabse zyada poocha jaata hai

JOIN ke liye do chhoti tables lete hain, taaki output haath se count ho sake:

students

id	name	course_id
1	Amit	101
2	Neha	102
3	Raj	NULL

courses

id	title
101	Java
102	Python
103	SQL

INNER JOIN — sirf dono taraf match:

SELECT s.name, c.title
FROM students s
INNER JOIN courses c ON s.course_id = c.id;

name | title
-----|-------
Amit | Java
Neha | Python

2 rows. Raj ka course_id NULL hai — koi match nahi, drop. Course "SQL" (103) kisi student ne nahi liya — wo bhi drop. Plain JOIN likhna bilkul INNER JOIN hi hota hai.

LEFT JOIN — left table ki har row bachegi:

SELECT s.name, c.title
FROM students s
LEFT JOIN courses c ON s.course_id = c.id;

name | title
-----|-------
Amit | Java
Neha | Python
Raj  | NULL

3 rows. Raj bacha, uska title NULL bhar diya gaya.

RIGHT JOIN — right table ki har row bachegi:

name | title
-----|-------
Amit | Java
Neha | Python
NULL | SQL

3 rows — ab course SQL bacha, student NULL.

FULL OUTER JOIN — dono taraf ka sab kuch: 4 rows (Amit|Java, Neha|Python, Raj|NULL, NULL|SQL). Note: MySQL me FULL OUTER JOIN keyword hi nahi hai — wahan LEFT JOIN ... UNION ... RIGHT JOIN se simulate karte hain. Yeh line bolne se interviewer ko lagta hai tumne sach me use kiya hai.

CROSS JOIN — koi ON nahi, har row har row ke saath: 3 × 3 = 9 rows. Ye Cartesian product hai.

SELF JOIN — ek table khud se, manager nikaalne ke liye:

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

employee | manager
---------|--------
Amit     | NULL
Neha     | Amit
Raj      | Neha
Priya    | Amit
Karan    | Neha
Zoya     | Amit

Amit top pe hai (manager_id NULL) isliye uska manager NULL. LEFT JOIN ki wajah se wo drop nahi hua — INNER JOIN hota to Amit gayab ho jaata. Yeh exact follow-up interviewer poochta hai.

Second highest salary — classic, aur log yahin galat karte hain

Sabse common galat jawab:

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

Hamari table pe sorted order hai: 90000, 90000, 70000, 60000, 50000, 45000. OFFSET 1 doosri row uthaata hai — 90000. Galat, kyunki Neha aur Karan tie kar rahe hain.

Safe version:

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

second_highest
--------------
70000

Inner query true max nikaalti hai (90000), outer usse strictly neeche ka max nikaalti hai — 70000. Ties se nahi tootta.

Window function waala version bhi ek baar bol dena, senior lagoge:

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

DENSE_RANK: 90000→1, 90000→1, 70000→2, ... isliye output 70000. Aur rnk = 3 likh do to "third highest" mil jaayega — yahi iska faayda hai.

DELETE vs TRUNCATE vs DROP

Pure theory, par almost guaranteed:

	DELETE	TRUNCATE	DROP
Type	DML	DDL	DDL
WHERE laga sakte ho?	Haan	Nahi	Nahi
Kya jaata hai	Chuni hui rows	Saari rows	Rows + table structure
Speed	Slow (row-by-row log)	Fast	Fast
Rollback	Haan	Engine pe depend	Engine pe depend

Rollback wali line pe detail: PostgreSQL me transaction ke andar TRUNCATE rollback ho sakta hai, aur wo by default identity/SERIAL sequence reset nahi karta (uske liye TRUNCATE t RESTART IDENTITY;). MySQL me TRUNCATE implicit commit karta hai isliye rollback nahi hota, par AUTO_INCREMENT 1 pe reset ho jaata hai. Interview me "depends on the database" bolna kamzori nahi — clarity hai.

Aur THE production galti: DELETE FROM employees; bina WHERE ke poori table khaali kar deta hai, chupchaap, bina warning. Golden rule — koi bhi UPDATE/DELETE chalane se pehle wahi WHERE ek SELECT ke saath chala ke dekho.

Primary key vs Foreign key

	PRIMARY KEY	FOREIGN KEY
Kaam	Har row ko uniquely identify karna	Doosri table ki PK ko reference karna
NULL allowed?	Nahi	Haan (jaise Raj ka course_id)
Kitne per table	Ek (composite ho sakta hai)	Kai
Duplicate	Nahi	Haan
Index	Auto-indexed hota hai	Postgres me auto nahi, MySQL/InnoDB me haan

Follow-up jo aksar aata hai: UNIQUE aur PRIMARY KEY me farak? UNIQUE me NULL allowed hai aur table me kai UNIQUE columns ho sakte hain; PRIMARY KEY ek hi hota hai aur NULL kabhi nahi. Aur ek chhoti si baat jo impress karti hai — UNIQUE column me multiple NULLs ho sakte hain, kyunki NULL kabhi doosre NULL ke barabar nahi maana jaata.

Normalization — 1NF, 2NF, 3NF one-liner me

Ek fat table socho jisme order + customer + product sab ek jagah hai, aur products cell me 'Pen:2, Book:1' likha hai.

• 1NF — har cell atomic ho, repeating group na ho. Us comma-list ko todo, ek row per item.
• 2NF — 1NF + composite key ke ek part pe partial dependency na ho. customer_name sirf order_id pe depend karta hai aur product_name sirf product_id pe — dono alag tables me nikaalo.
• 3NF — 2NF + transitive dependency na ho. order_id → customer_id → customer_name ek chain hai, isliye customers table alag banao.

Ek line me bol do: "Normalization redundancy aur update anomalies hataane ke liye hai — aur denormalization jaan-boojh ke redundancy wapas laana hai taaki read-heavy queries me JOIN bach jaayein." Ye aakhri line hi tumhe baaki candidates se alag karti hai.

Index aur ACID — do jaldi wale

Index ek sorted side-structure hai (usually B-tree) jo engine ko poori table scan karne ke bajaye seedha matching rows tak jump karne deta hai. Cost yeh hai ki har INSERT/UPDATE/DELETE ko index bhi update karna padta hai — isliye har column pe index nahi lagate, sirf WHERE / JOIN ON / ORDER BY wale columns pe. Composite index (user_id, created_at) me leftmost-prefix rule lagta hai: akele user_id pe filter karne wali query ko fayda milega, akele created_at pe nahi.

ACID — Atomicity (sab ya kuch nahi), Consistency (valid state se valid state), Isolation (concurrent transactions ek doosre ka aadha kaam nahi dekhte), Durability (COMMIT ke baad crash me bhi data safe). Example hamesha bank transfer ka do — debit aur credit ek hi unit, beech me fail hua to ROLLBACK.

UNION vs UNION ALL bhi ek line me: UNION duplicates hataata hai, UNION ALL nahi hataata aur isi liye fast hai. Dono me column count, order aur compatible types match hone chahiye. Aur order kabhi guarantee mat maano — chahiye to aakhir me ek ORDER BY lagao.

Ab yeh karo

SQL ka game yaad karne ka nahi, output predict karne ka hai. Ek chhoti table banao (upar wali employees chalegi), har query khud chalao, aur output dekhne se pehle guess karo. Jis din tumhara guess consistently sahi aane lage, us din technical round tumhare liye aasaan ho jaayega.

• Poore SQL questions, MCQs, "query likho" practice aur flashcards: SQL interview prep chapter
• Bolne ki practice ke liye voice-based AI mock interview — theory question sun ke jawab dena alag skill hai
• Technical round ke baad HR round kaisa hota hai, wo yahan padho: TCS aur Infosys interview questions

Concept aata hai par bol nahi paate — yehi asli problem hai. Aaj hi ek query bol ke explain karke dekho, kisi ko ya khud ko. Farak turant dikhega.