SQLinterview questions & answers

Think of a spreadsheet — rows and columns, where every row is a record and every column is a fixed field (name, age, city). Now imagine many such spreadsheets living inside one workbook, and each spreadsheet can look up rows in another spreadsheet using a shared column (like an employee ID). That's roughly a relational database.

In SQL (Structured Query Language), that workbook is a database, each spreadsheet is a table, each row is a record, and each column is a field. SQL is the standard declarative language you use to talk to a relational database (RDBMS) — you say WHAT data you want, not HOW to fetch it row by row; the database engine figures out the HOW. Popular RDBMS: PostgreSQL, MySQL, SQL Server, Oracle, SQLite.

SQL itself splits into sub-languages: DDL (Data Definition Language — CREATE, ALTER, DROP, shapes the tables), DML (Data Manipulation Language — SELECT, INSERT, UPDATE, DELETE, works with the data), DCL (Data Control Language — GRANT, REVOKE, controls access), and TCL (Transaction Control Language — COMMIT, ROLLBACK, controls transactions).

🌍 Real-world example: A small 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'; returns Rahul and Meena's names + salaries — you just said WHAT you wanted (Tech dept, name + salary), not how to scan the table.

💡 RDBMS = Relational Database Management System — software that stores data in related tables (Postgres, MySQL, etc.).

💡 declarative = you describe the desired result; the engine decides the execution plan (unlike imperative code where you write the loop yourself).

💡 table / row / column = a table is a named grid of data; a row is one record; a column is one field shared by every row.

SQL vs NoSQL: SQL/relational databases enforce a fixed schema (every row in a table has the same columns) and are strong at relationships across tables (JOINs) and strict consistency (ACID transactions) — great for orders, payments, banking. NoSQL databases (like MongoDB) store flexible, schema-less documents and shine when data nests naturally or its shape changes often. Neither is "better" — pick based on how structured and relational your data is.

Standard definition: 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. Define the table (DDL)
CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50),
  dept VARCHAR(50),
  salary INT
);

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

-- 3. Query the data (DML) — say WHAT you want
SELECT name, salary FROM employees WHERE dept = 'Tech';

Think of an Excel spreadsheet you're about to design for HR. Before anyone types a single row, you first decide the column headers — Name, Age, Salary, Joining Date — and for each column you decide what KIND of value goes there (text? number? date?). Only after that structure exists can rows of actual data be added. A database table works exactly the same way: you design the columns first, then insert rows.

In SQL, CREATE TABLE is how you define that structure. Each column gets a name and a data type — common ones are INT/BIGINT (whole numbers), DECIMAL/NUMERIC (exact decimals — use these for money, never floats), VARCHAR(n) (text with a max length n) or TEXT (unlimited text), BOOLEAN (true/false), and DATE/TIMESTAMP (calendar date, or date+time). Every table should have a PRIMARY KEY — a column (usually id) that uniquely identifies each row; the database won't let two rows share the same primary key value, and it can never be NULL. Once a table exists you can still reshape it: ALTER TABLE adds/removes columns without losing existing data, and DROP TABLE deletes the whole table structure AND all its rows permanently.

🌍 Real-world example: An HR system needs an employees table.

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

Later HR wants to track email too: ALTER TABLE employees ADD COLUMN email VARCHAR(100); — existing rows just get NULL in the new column. If the whole HR module is scrapped: DROP TABLE employees; wipes the table and every row in it — there's no undo.

💡 CREATE TABLE = the DDL statement that defines a new table's columns and their types.

💡 PRIMARY KEY = a column (or columns) that uniquely identifies each row; unique + never NULL.

💡 VARCHAR(n) vs TEXT = VARCHAR has a max length limit n; TEXT has no practical limit — both store characters.

💡 auto-increment id = a column that fills itself with 1, 2, 3… automatically — you never supply it yourself.

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
);

-- Later: add a column
ALTER TABLE employees ADD COLUMN email VARCHAR(100);

-- Remove the table entirely (structure + all rows, permanent)
DROP TABLE employees;

What we're building: A real e-commerce database schema from scratch — four tables (users, products, orders, order_items) wired together with PRIMARY KEY / FOREIGN KEY constraints, seeded with sample rows, and proven with a JOIN query that pulls an order together with its items and product names. This is the capstone: it wires together entity design + relationships + constraints + seed data + multi-table JOINs — the exact skeleton every backend/database interview expects you to draw on a whiteboard.

By the end you'll be able to trace a schema all the way through: real-world nouns (user, product, order) → tables → PK/FK wiring → seed rows → a JOIN that proves the wiring works. Master this and 'design me a database for X' interview questions stop being scary.

🌍 Real-world example: This is exactly what Amazon/Flipkart's order system looks like underneath. A user places an order containing multiple products; each product can appear in many different orders. That many-to-many relationship is why we need a fourth table — order_items — sitting between orders and products.

💡 Schema = the blueprint of a database — which tables exist, which columns each has, and how tables relate to each other via keys.

Standard definition: 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 — The entities and relationships (design before code)

Before writing a single CREATE TABLE, list the nouns and how they connect:

• users — people who shop. One user can place many orders.
• products — items for sale. One product can appear in many orders.
• orders — a purchase event made by one user, at one point in time.
• order_items — the line items inside an order (which products, how many, at what price).

The relationships:

1. users 1-to-many orders — one user has many orders, but each order belongs to exactly one user. This is a direct relationship: put a user_id FOREIGN KEY column on orders pointing back to users.id.
2. orders many-to-many products, via order_items — one order can contain many products (a shopping cart with 3 items), AND one product can appear in many different orders (100 customers all bought the same mouse). You cannot model many-to-many with a single FK column on either side — so we introduce a junction table (order_items) that sits in the middle and holds two FKs: one to orders, one to products.

💡 Junction table (also called a bridge/join table) = a table whose only job is to connect two other tables in a many-to-many relationship. It has (at minimum) two FOREIGN KEY columns, one pointing to each side.

This is the single most-asked schema-design interview question: "How do you model many-to-many in SQL?" Answer: a third table in between, with a FOREIGN KEY to each side.

---

Step 1 — CREATE TABLE users (the 1 side of 1-to-many)

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()
);

Why each constraint:

• id SERIAL PRIMARY KEY — SERIAL auto-increments (1, 2, 3, ...) so we never assign ids by hand. PRIMARY KEY means unique + NOT NULL automatically, and every other table will reference this column.
• name VARCHAR(100) NOT NULL — every user must have a name; an empty/missing name is invalid data, so NOT NULL blocks it at the database level (not just in app code).
• email VARCHAR(150) NOT NULL UNIQUE — UNIQUE stops two users from registering with the same email (a login/signup requirement), NOT NULL because email is how they log in.
• password_hash VARCHAR(255) NOT NULL — we store a hash, never the plain password; NOT NULL because an account without credentials is broken.
• created_at TIMESTAMP DEFAULT NOW() — DEFAULT auto-fills the current time on INSERT so the app doesn't have to pass it every time.

---

Step 2 — CREATE TABLE products (the other side of the many-to-many)

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()
);

Why each constraint:

• price DECIMAL(10,2) NOT NULL CHECK (price > 0) — money must use DECIMAL, never FLOAT (floats round money wrong — classic interview gotcha). CHECK (price > 0) rejects a free or negative-priced product at the database level, before it ever reaches the app.
• stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0) — DEFAULT 0 means a newly added product starts with zero stock unless told otherwise; CHECK (stock >= 0) is the guard rail that stops a bug from ever writing negative stock.

💡 CHECK = a constraint that validates a condition on every INSERT/UPDATE; the database rejects the row if the condition is false. It's validation that lives in the schema, not just in application code — so it holds even if a rogue script writes to the table directly.

---

Step 3 — CREATE TABLE orders (the FK that makes 1-to-many real)

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()
);

Why each constraint:

• user_id INT NOT NULL REFERENCES users(id) — this is the FOREIGN KEY. REFERENCES users(id) tells Postgres/MySQL: "every value in this column must already exist as an id in users." NOT NULL because an order without a buyer makes no sense. This single line is the 1-to-many relationship: many orders rows can share the same user_id, but each row points to exactly one user.
• status ... CHECK (status IN (...)) — an enum-style CHECK: only these five string values are valid, so you can never accidentally save status = 'shpped' (typo) or status = 'done' (not a real state). DEFAULT 'pending' means a brand-new order starts life as pending without the app having to say so.
• total_amount DECIMAL(10,2) NOT NULL DEFAULT 0 — a cached total (computed from order_items at insert/update time) so we don't have to re-SUM every time we list orders.

💡 FOREIGN KEY = a column that must match a value that already exists in another table's PRIMARY KEY. It enforces referential integrity — the database physically refuses to save an order for a user_id that doesn't exist.

---

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

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)
);

Trace the wiring, column by column:

1. order_id INT NOT NULL REFERENCES orders(id) ON DELETE CASCADE — FK #1, points at orders.id. This is half of the many-to-many bridge: one order can have many order_items rows (many products in the cart). ON DELETE CASCADE means if the parent order is deleted, its line items are automatically deleted too — no orphan rows left behind.
2. product_id INT NOT NULL REFERENCES products(id) — FK #2, points at products.id. This is the other half: one product can appear in many order_items rows across many different orders. No ON DELETE CASCADE here on purpose — you don't want deleting a product to silently rewrite order history.
3. Put the two together: order_items has two foreign keys, one to each side of the many-to-many. Every row in order_items = "this specific product, in this specific order, this quantity, at this price." That's the many-to-many-via-junction pattern: orders ←(1-to-many)— order_items —(many-to-1)→ products. Two 1-to-many relationships chained together simulate one many-to-many.
4. quantity INT NOT NULL CHECK (quantity > 0) — you can't order zero or negative units of something.
5. unit_price DECIMAL(10,2) NOT NULL — we snapshot the price at order time (not a live lookup to products.price), because if the product's price changes next month, this historical order must still show what the customer actually paid.
6. UNIQUE (order_id, product_id) — a composite unique constraint: the same product can't appear as two separate rows in the same order (if a customer adds 2 more mice, you UPDATE the existing row's quantity, you don't INSERT a duplicate row).

---

Step 5 — Seed a few rows (prove the FKs accept real data)

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 it: users gets ids 1 (Rahul) and 2 (Priya) via SERIAL. products gets ids 1 (Mouse), 2 (Keyboard), 3 (Cable). The orders row references user_id = 1 — this only succeeds because id = 1 already exists in users; try user_id = 99 and the FOREIGN KEY constraint rejects the insert (referential integrity in action). Finally order_items links order_id = 1 to product_id = 1 and product_id = 2 — one order, two products, proving the many-to-many bridge works: order 1 now "contains" both the Mouse and the Keyboard.

---

Step 6 — The proof JOIN (order + its items + product names, across all 4 tables)

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

Trace the JOIN chain: we start at orders, JOIN to users on the 1-to-many FK (o.user_id = u.id) to get the customer's name, then JOIN to order_items on oi.order_id = o.id to fan out into one row per line item, then JOIN to products on oi.product_id = p.id to turn the raw product_id into a readable name. Four tables, three JOINs, one readable receipt — this single query is the payoff of the whole schema: every constraint we wrote in Steps 1–4 is what makes this JOIN safe and correct.

---

✅ What you just learned

• Entity + relationship design before code — nouns become tables, relationships become FK columns (1-to-many) or a junction table (many-to-many).
• PRIMARY KEY (SERIAL PRIMARY KEY) uniquely identifies every row and is what every FK points back to.
• FOREIGN KEY (REFERENCES table(col)) enforces referential integrity — you cannot insert a row pointing at a parent that doesn't exist.
• NOT NULL, UNIQUE, CHECK, DEFAULT — each constraint is a rule the database enforces at write time, not just app-level validation.
• The many-to-many-via-junction pattern: orders —(1:M)→ order_items ←(M:1)— products. This is THE most-asked schema-design interview point.
• ON DELETE CASCADE on the parent-owned FK (order_id) vs no cascade on the reference-only FK (product_id) — a deliberate design choice, not a default.
• A composite UNIQUE constraint (order_id, product_id) prevents duplicate line items.
• Snapshotting price (unit_price stored on order_items, not looked up live) protects historical order data from future price changes.
• The proof JOIN — chaining JOINs across 4 tables to turn raw FK ids into a human-readable order receipt.

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;