SQL interview questions & answers

Ek spreadsheet socho — rows aur columns, jisme har row ek record hai aur har column ek fixed field (name, age, city). Ab socho aise kai spreadsheets ek workbook ke andar rehte hain, aur ek spreadsheet doosre spreadsheet me rows lookup kar sakta hai kisi shared column se (jaise employee ID). Ye lagbhag ek relational database hai.

SQL me (Structured Query Language), wo workbook ek database hai, har spreadsheet ek table hai, har row ek record hai, aur har column ek field hai. SQL wo standard declarative language hai jise tum ek relational database (RDBMS) se baat karne ke liye use karte ho — tum bolte ho ki tumhe KYA data chahiye, YE nahi ki row-by-row kaise fetch kare; database engine khud figure karta hai KAISE. Popular RDBMS: PostgreSQL, MySQL, SQL Server, Oracle, SQLite.

SQL khud sub-languages me bata hai: DDL (Data Definition Language — CREATE, ALTER, DROP, tables ka shape define karta hai), DML (Data Manipulation Language — SELECT, INSERT, UPDATE, DELETE, data ke saath kaam karta hai), DCL (Data Control Language — GRANT, REVOKE, access control karta hai), aur TCL (Transaction Control Language — COMMIT, ROLLBACK, transactions control karta hai).

🌍 Real-world example: Ek chhota employees table:

id	name	dept	salary
1	Aisha	Sales	50000
2	Rahul	Tech	65000
3	Meena	Tech	58000

SELECT name, salary FROM employees WHERE dept = 'Tech'; Rahul aur Meena ke names + salaries return karta hai — tumne bas ye bola ki tumhe KYA chahiye (Tech dept, name + salary), ye nahi ki table kaise scan kare.

💡 RDBMS = Relational Database Management System — software jo data ko related tables me store karta hai (Postgres, MySQL, waghera).

💡 declarative = tum desired result describe karte ho; engine execution plan decide karta hai (imperative code ke ulat, jahan tum khud loop likhte ho).

💡 table / row / column = table data ka ek named grid hai; row ek record hai; column ek field hai jo har row me common hoti hai.

SQL vs NoSQL: SQL/relational databases ek fixed schema enforce karte hain (table ki har row me same columns hote hain) aur tables ke beech relationships (JOINs) aur strict consistency (ACID transactions) me strong hote hain — orders, payments, banking ke liye perfect. NoSQL databases (jaise MongoDB) flexible, schema-less documents store karte hain aur tab shine karte hain jab data naturally nest kare ya uski shape aksar badle. Koi bhi 'better' nahi hai — depend karta hai ki tumhara data kitna structured aur relational hai.

Standard definition (interview me bolo): SQL (Structured Query Language) is a declarative language used to define and query data in a relational database (RDBMS) where data is organized into tables of rows and columns, with sub-languages DDL (define structure), DML (manipulate data), DCL (control access), and TCL (control transactions).

-- 1. Table define karo (DDL)
CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50),
  dept VARCHAR(50),
  salary INT
);

-- 2. Data add karo (DML)
INSERT INTO employees (name, dept, salary) VALUES
  ('Aisha', 'Sales', 50000),
  ('Rahul', 'Tech', 65000),
  ('Meena', 'Tech', 58000);

-- 3. Data query karo (DML) — bolo tumhe KYA chahiye
SELECT name, salary FROM employees WHERE dept = 'Tech';

Ek Excel spreadsheet socho jo tum HR ke liye design karne waale ho. Koi bhi ek row type kare, uske pehle tum column headers decide karte ho — Name, Age, Salary, Joining Date — aur har column ke liye ye decide karte ho ki wahan kis KIND ki value jaayegi (text? number? date?). Ye structure ban jaane ke baad hi actual data ki rows add ho sakti hain. Database table bilkul isi tarah kaam karta hai: pehle tum columns design karte ho, phir rows insert karte ho.

SQL me CREATE TABLE isi structure ko define karne ka tarika hai. Har column ko ek naam aur ek data type milta hai — common types hain INT/BIGINT (whole numbers), DECIMAL/NUMERIC (exact decimals — money ke liye inhi ko use karo, float kabhi nahi), VARCHAR(n) (text jiski max length n hai) ya TEXT (unlimited text), BOOLEAN (true/false), aur DATE/TIMESTAMP (calendar date, ya date+time). Har table ki ek PRIMARY KEY honi chahiye — ek column (usually id) jo har row ko uniquely identify karta hai; database do rows ko same primary key value ke saath allow nahi karega, aur ye kabhi NULL nahi ho sakti. Table ban jaane ke baad bhi tum use reshape kar sakte ho: ALTER TABLE existing data khoye bina columns add/remove karta hai, aur DROP TABLE poori table structure AUR uski saari rows permanently delete kar deta hai.

🌍 Real-world example: Ek HR system ko employees table chahiye.

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  salary DECIMAL(10,2),
  is_active BOOLEAN DEFAULT TRUE,
  joined_on DATE
);

Baad me HR email bhi track karna chahta hai: ALTER TABLE employees ADD COLUMN email VARCHAR(100); — existing rows me naye column me bas NULL aa jaata hai. Agar poora HR module hi hata dena ho: DROP TABLE employees; table aur uski har row wipe kar deta hai — koi undo nahi hai.

💡 CREATE TABLE = wo DDL statement jo naye table ke columns aur unke types define karta hai.

💡 PRIMARY KEY = ek column (ya columns) jo har row ko uniquely identify karta hai; unique + kabhi NULL nahi.

💡 VARCHAR(n) vs TEXT = VARCHAR ki max length limit n hoti hai; TEXT ki koi practical limit nahi — dono characters store karte hain.

💡 auto-increment id = wo column jo khud hi 1, 2, 3… bharta hai automatically — tum khud kabhi supply nahi karte.

Standard definition: CREATE TABLE defines a table's columns and data types (INT/BIGINT, DECIMAL/NUMERIC, VARCHAR(n)/TEXT, BOOLEAN, DATE/TIMESTAMP); a PRIMARY KEY column uniquely identifies each row and is never NULL; ALTER TABLE ... ADD COLUMN changes an existing table's structure without deleting data; DROP TABLE permanently deletes the table and all its rows. Auto-incrementing ids are dialect-specific: SERIAL (PostgreSQL), AUTO_INCREMENT (MySQL), GENERATED ALWAYS AS IDENTITY (standard SQL / SQL Server / newer Postgres).

-- PostgreSQL / MySQL

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,           -- MySQL: id INT AUTO_INCREMENT PRIMARY KEY
  name VARCHAR(100) NOT NULL,
  salary DECIMAL(10,2),
  is_active BOOLEAN DEFAULT TRUE,
  joined_on DATE
);

-- Baad me: ek column add karo
ALTER TABLE employees ADD COLUMN email VARCHAR(100);

-- Table ko poori tarah hata do (structure + saari rows, permanent)
DROP TABLE employees;

Kya bana rahe hain: Ek asli e-commerce database schema shuru se — chaar tables (users, products, orders, order_items) jo PRIMARY KEY / FOREIGN KEY constraints se aapas mein jude hain, sample rows se seed kiye gaye hain, aur ek JOIN query se prove kiya gaya hai jo ek order ko uske items aur product names ke saath nikalti hai. Ye capstone hai: ye entity design + relationships + constraints + seed data + multi-table JOINs ko ek saath jodta hai — wahi skeleton jo har backend/database interview tumse whiteboard pe banwane ki ummeed karti hai.

Ant tak tum ek schema ko poora trace kar paoge: real-world nouns (user, product, order) → tables → PK/FK wiring → seed rows → ek JOIN jo prove karta hai ki wiring kaam karti hai. Ye pakka aa gaya, to 'mere liye X ka database design karo' jaise interview questions darawne nahi lagenge.

🌍 Real-world example: Ye bilkul wahi hai jo Amazon/Flipkart ke order system ke andar hota hai. Ek user ek order place karta hai jismein multiple products hote hain; har product bahut saare alag-alag orders mein aa sakta hai. Wahi many-to-many relationship hai jiske liye humein chautha table chahiye — order_items — jo orders aur products ke beech mein baithta hai.

💡 Schema = database ka blueprint — kaunse tables exist karte hain, har table mein kaunse columns hain, aur tables ek doosre se keys ke through kaise related hain.

Standard definition (interview me bolo): A relational schema models real-world entities as tables, uses a PRIMARY KEY to uniquely identify each row, and uses FOREIGN KEYs to enforce relationships between tables — one-to-many directly, many-to-many via a junction table.

---

Step 0 — Entities aur relationships (code se pehle design)

Ek bhi CREATE TABLE likhne se pehle, nouns list karo aur ye connect kaise hote hain:

• users — log jo shopping karte hain. Ek user bahut saare orders place kar sakta hai.
• products — bikne wale items. Ek product bahut saare orders mein aa sakta hai.
• orders — ek purchase event jo ek user ne, ek time pe, kiya.
• order_items — order ke andar ke line items (kaunse products, kitne, kis price pe).

Relationships:

1. users 1-to-many orders — ek user ke bahut saare orders hote hain, par har order sirf ek hi user ka hota hai. Ye ek direct relationship hai: orders pe ek user_id FOREIGN KEY column daalo jo wapas users.id ki taraf point kare.
2. orders many-to-many products, order_items ke through — ek order mein bahut saare products ho sakte hain (3 items wali shopping cart), AUR ek product bahut saare alag-alag orders mein aa sakta hai (100 customers ne wahi mouse khareeda). Many-to-many ko tum kisi bhi side pe ek single FK column se model nahi kar sakte — isliye hum ek junction table (order_items) introduce karte hain jo beech mein baithta hai aur do FKs rakhta hai: ek orders ki taraf, ek products ki taraf.

💡 Junction table (bridge/join table bhi kehte hain) = ek table jiska kaam sirf do doosre tables ko many-to-many relationship mein connect karna hai. Ismein (kam se kam) do FOREIGN KEY columns hote hain, ek har side ki taraf point karta hai.

Ye sabse zyada poocha jaane wala schema-design interview question hai: "SQL mein many-to-many kaise model karte ho?" Jawab: beech mein ek teesra table, jiske dono side FOREIGN KEY hoti hai.

---

Step 1 — CREATE TABLE users (1-to-many ka 1 wala side)

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

Har constraint kyun:

• id SERIAL PRIMARY KEY — SERIAL auto-increment karta hai (1, 2, 3, ...) taaki humein ids haath se assign na karni padein. PRIMARY KEY ka matlab automatically unique + NOT NULL, aur har doosra table isi column ko reference karega.
• name VARCHAR(100) NOT NULL — har user ka naam hona chahiye; khali/missing naam invalid data hai, isliye NOT NULL ise database level pe hi rok deta hai (sirf app code mein nahi).
• email VARCHAR(150) NOT NULL UNIQUE — UNIQUE do users ko same email se register hone se rokta hai (login/signup requirement), NOT NULL kyunki email se hi wo login karte hain.
• password_hash VARCHAR(255) NOT NULL — hum ek hash store karte hain, kabhi plain password nahi; NOT NULL kyunki bina credentials wala account bekar hai.
• created_at TIMESTAMP DEFAULT NOW() — DEFAULT INSERT pe current time apne aap bhar deta hai taaki app ko har baar ye pass na karna pade.

---

Step 2 — CREATE TABLE products (many-to-many ka doosra side)

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  price DECIMAL(10,2) NOT NULL CHECK (price > 0),
  stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
  created_at TIMESTAMP DEFAULT NOW()
);

Har constraint kyun:

• price DECIMAL(10,2) NOT NULL CHECK (price > 0) — paise ke liye hamesha DECIMAL use karo, kabhi FLOAT nahi (floats paise ko galat round karte hain — classic interview gotcha). CHECK (price > 0) free ya negative-priced product ko database level pe hi reject kar deta hai, app tak pahunchne se pehle.
• stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0) — DEFAULT 0 ka matlab naya add hua product bina kuch bataye zero stock se shuru hota hai; CHECK (stock >= 0) wo guard rail hai jo kisi bug ko negative stock likhne se rokta hai.

💡 CHECK = ek constraint jo har INSERT/UPDATE pe ek condition validate karta hai; agar condition false hai to database row reject kar deta hai. Ye validation schema mein rehti hai, sirf application code mein nahi — isliye ye tab bhi hold karti hai jab koi rogue script table mein seedha likhe.

---

Step 3 — CREATE TABLE orders (wo FK jo 1-to-many ko asli banata hai)

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL REFERENCES users(id),
  status VARCHAR(20) NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending','paid','shipped','delivered','cancelled')),
  total_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT NOW()
);

Har constraint kyun:

• user_id INT NOT NULL REFERENCES users(id) — ye hai FOREIGN KEY. REFERENCES users(id) Postgres/MySQL ko batata hai: "is column ki har value users mein id ki tarah pehle se exist karni chahiye." NOT NULL kyunki bina buyer wala order koi maayne nahi rakhta. Ye ek line hi 1-to-many relationship hai: bahut saari orders rows same user_id share kar sakti hain, par har row sirf ek hi user ko point karti hai.
• status ... CHECK (status IN (...)) — ek enum-jaisa CHECK: sirf ye paanch string values valid hain, isliye tum kabhi galti se status = 'shpped' (typo) ya status = 'done' (real state nahi) save nahi kar sakte. DEFAULT 'pending' ka matlab bilkul naya order bina app kuch bataye pending state se shuru hota hai.
• total_amount DECIMAL(10,2) NOT NULL DEFAULT 0 — ek cached total (order_items se insert/update time pe compute kiya hua) taaki jab bhi orders list karein har baar re-SUM na karna pade.

💡 FOREIGN KEY = ek column jiski value doosre table ki PRIMARY KEY mein pehle se exist karni chahiye. Ye referential integrity enforce karta hai — database physically ek aise user_id ke liye order save karne se mana kar deta hai jo exist hi nahi karta.

---

Step 4 — CREATE TABLE order_items (junction table — wiring TRACE karo)

CREATE TABLE order_items (
  id SERIAL PRIMARY KEY,
  order_id INT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id INT NOT NULL REFERENCES products(id),
  quantity INT NOT NULL CHECK (quantity > 0),
  unit_price DECIMAL(10,2) NOT NULL,
  UNIQUE (order_id, product_id)
);

Wiring ko column-by-column trace karo:

1. order_id INT NOT NULL REFERENCES orders(id) ON DELETE CASCADE — FK #1, orders.id ki taraf point karta hai. Ye many-to-many bridge ka aadha hissa hai: ek order ki bahut saari order_items rows ho sakti hain (cart mein bahut saare products). ON DELETE CASCADE ka matlab agar parent order delete hota hai, to uske line items bhi apne aap delete ho jaate hain — koi orphan row nahi bachti.
2. product_id INT NOT NULL REFERENCES products(id) — FK #2, products.id ki taraf point karta hai. Ye doosra aadha hissa hai: ek product bahut saari order_items rows mein, bahut saare alag orders ke across, aa sakta hai. Yahan jaan-boojhkar ON DELETE CASCADE nahi hai — tum nahi chahte ki ek product delete karne se order history chupke se rewrite ho jaaye.
3. Dono ko saath rakho: order_items mein do foreign keys hain, many-to-many ke har side ke liye ek. order_items ki har row ka matlab hai "ye specific product, is specific order mein, itni quantity, is price pe." Yahi hai many-to-many-via-junction pattern: orders ←(1-to-many)— order_items —(many-to-1)→ products. Do 1-to-many relationships ko zanjeer mein jodkar ek many-to-many simulate hota hai.
4. quantity INT NOT NULL CHECK (quantity > 0) — tum kisi cheez ki zero ya negative units order nahi kar sakte.
5. unit_price DECIMAL(10,2) NOT NULL — hum order time pe price ka snapshot lete hain (products.price ka live lookup nahi), kyunki agar product ka price agle mahine badal jaaye, to ye historical order phir bhi ye dikhana chahiye ki customer ne asal mein kya diya tha.
6. UNIQUE (order_id, product_id) — ek composite unique constraint: same order mein wahi product do alag rows ki tarah nahi aa sakta (agar customer 2 aur mouse add karta hai, to tum existing row ki quantity UPDATE karte ho, duplicate row INSERT nahi karte).

---

Step 5 — Kuch rows seed karo (prove karo ki FKs asli data accept karte hain)

INSERT INTO users (name, email, password_hash) VALUES
  ('Rahul Sharma', 'rahul@example.com', 'hash_abc123'),
  ('Priya Singh', 'priya@example.com', 'hash_def456');

INSERT INTO products (name, price, stock) VALUES
  ('Wireless Mouse', 499.00, 100),
  ('Mechanical Keyboard', 2999.00, 50),
  ('USB-C Cable', 199.00, 200);

INSERT INTO orders (user_id, status, total_amount) VALUES
  (1, 'paid', 3498.00);

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
  (1, 1, 1, 499.00),
  (1, 2, 1, 2999.00);

Trace karo: users ko SERIAL se ids 1 (Rahul) aur 2 (Priya) milti hain. products ko ids 1 (Mouse), 2 (Keyboard), 3 (Cable) milti hain. orders wali row user_id = 1 reference karti hai — ye tabhi succeed hoti hai jab users mein id = 1 pehle se exist karti hai; user_id = 99 try karo aur FOREIGN KEY constraint insert reject kar dega (referential integrity action mein). Aakhir mein order_items order_id = 1 ko product_id = 1 aur product_id = 2 se link karta hai — ek order, do products, jo many-to-many bridge kaam karne ka proof hai: order 1 ab Mouse aur Keyboard dono "contain" karta hai.

---

Step 6 — Proof JOIN (order + uske items + product names, saare 4 tables ke across)

SELECT
  o.id           AS order_id,
  u.name         AS customer,
  p.name         AS product,
  oi.quantity,
  oi.unit_price,
  (oi.quantity * oi.unit_price) AS line_total
FROM orders o
JOIN users u        ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON oi.product_id = p.id
ORDER BY o.id, p.name;

Result:

order_id | customer      | product              | quantity | unit_price | line_total
---------|---------------|----------------------|----------|------------|------------
1        | Rahul Sharma  | Mechanical Keyboard  | 1        | 2999.00    | 2999.00
1        | Rahul Sharma  | Wireless Mouse       | 1        | 499.00     | 499.00

JOIN chain trace karo: hum orders se shuru karte hain, users se 1-to-many FK pe JOIN karte hain (o.user_id = u.id) customer ka naam paane ke liye, phir order_items se oi.order_id = o.id pe JOIN karte hain taaki har line item ke liye ek row fan-out ho, phir products se oi.product_id = p.id pe JOIN karte hain taaki raw product_id ko readable naam mein badla ja sake. Chaar tables, teen JOINs, ek readable receipt — yahi ek query poore schema ka payoff hai: Step 1–4 mein jo bhi constraint humne likhe, wahi is JOIN ko safe aur correct banate hain.

---

✅ Jo tumne abhi seekha

• Code se pehle entity + relationship design — nouns tables ban jaate hain, relationships FK columns (1-to-many) ya junction table (many-to-many) ban jaate hain.
• PRIMARY KEY (SERIAL PRIMARY KEY) har row ko uniquely identify karti hai aur har FK isi ki taraf point karta hai.
• FOREIGN KEY (REFERENCES table(col)) referential integrity enforce karta hai — tum aisi row insert nahi kar sakte jo kisi non-existent parent ko point kare.
• NOT NULL, UNIQUE, CHECK, DEFAULT — har constraint ek rule hai jo database write time pe enforce karta hai, sirf app-level validation nahi.
• Many-to-many-via-junction pattern: orders —(1:M)→ order_items ←(M:1)— products. Ye SCHEMA-DESIGN ka sabse zyada poocha jaane wala interview point hai.
• ON DELETE CASCADE parent-owned FK (order_id) pe, vs koi cascade nahi reference-only FK (product_id) pe — ye ek jaan-boojhkar liya gaya design decision hai, default nahi.
• Composite UNIQUE constraint (order_id, product_id) duplicate line items ko rokta hai.
• Price ka snapshot lena (unit_price order_items pe stored, live lookup nahi) historical order data ko future price changes se bachata hai.
• Proof JOIN — 4 tables ke across JOINs chain karke raw FK ids ko ek human-readable order receipt mein badalna.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  price DECIMAL(10,2) NOT NULL CHECK (price > 0),
  stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL REFERENCES users(id),
  status VARCHAR(20) NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending','paid','shipped','delivered','cancelled')),
  total_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE order_items (
  id SERIAL PRIMARY KEY,
  order_id INT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id INT NOT NULL REFERENCES products(id),
  quantity INT NOT NULL CHECK (quantity > 0),
  unit_price DECIMAL(10,2) NOT NULL,
  UNIQUE (order_id, product_id)
);

INSERT INTO users (name, email, password_hash) VALUES
  ('Rahul Sharma', 'rahul@example.com', 'hash_abc123'),
  ('Priya Singh', 'priya@example.com', 'hash_def456');

INSERT INTO products (name, price, stock) VALUES
  ('Wireless Mouse', 499.00, 100),
  ('Mechanical Keyboard', 2999.00, 50),
  ('USB-C Cable', 199.00, 200);

INSERT INTO orders (user_id, status, total_amount) VALUES
  (1, 'paid', 3498.00);

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
  (1, 1, 1, 499.00),
  (1, 2, 1, 2999.00);

SELECT
  o.id AS order_id,
  u.name AS customer,
  p.name AS product,
  oi.quantity,
  oi.unit_price,
  (oi.quantity * oi.unit_price) AS line_total
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id
ORDER BY o.id, p.name;