Reading the Brief Before You Type
The project is a small online store. It sells products, customers place orders, and customers leave reviews. That is three sentences, and turning three sentences into a schema is the actual skill this course has been building towards — so resist opening a SQL client for the next ten minutes and do this on paper first.
Start with the nouns, because each one is a candidate table. Customer. Product. Category. Order. Review. Then write down the relationships as plain sentences, because each sentence decides where a foreign key goes. One customer places many orders, so orders carries a customer_id. One category holds many products, so products carries a category_id. One order contains many products and one product appears in many orders, which is many-to-many and therefore needs a junction table — order_items, with one row per product on one order. A category can sit inside another category, which is a relationship from a table to itself, handled with a parent_id pointing back at the same table.
Then write down the questions the database will have to answer, because those determine your indexes and will expose gaps in the design while it is still cheap to fix them. What has this customer ordered? What is this product's average rating? What did we sell last month? Which products have never been ordered? Notice that the last two are already telling you something: revenue must be computable from the order lines, and the query for products never ordered needs the products side to be reachable without an order.
One decision deserves thinking about now rather than later, because it is the one people get wrong. When a customer buys a product for ₹1,499 and the price rises to ₹1,699 next month, what should their old order say? It must still say ₹1,499, because that is what they paid and an invoice is a historical record. So the price has to be copied onto the order line at the moment of purchase. Miss this and every past order silently rewrites itself whenever somebody edits a price.
-- The six tables, and the relationship each one carries
--
-- customers 1 ---- N orders
-- categories 1 ---- N products (and 1 ---- N itself)
-- orders 1 ---- N order_items N ---- 1 products
-- customers 1 ---- N reviews N ---- 1 products
--
-- Questions it has to answer:
-- what has this customer ordered?
-- what is this product's average rating?
-- how much did we sell last month?
-- which products have never been ordered?- Sketching the tables and drawing lines between them takes a few minutes and saves hours. If you cannot draw a line between two tables, you are missing a foreign key or a junction table; if you find yourself wanting to draw a line to a comma-separated list inside a column, that column should be a table.
The Schema, and the Reason for Every Line
Now the schema. Read the code below as a set of decisions rather than as boilerplate, because in an interview the decisions are what you will be asked about.
DECIMAL(10,2) for every money column, never FLOAT: binary floating point cannot represent 0.10 exactly, and a table of small errors eventually fails to add up. utf8mb4 as the character set, so Indian-language text and emoji in a review body are stored rather than mangled. NOT NULL on everything genuinely required, because a nullable column is a promise that the value may be missing and you will have to handle that everywhere forever. UNIQUE on email, which is a real business rule that belongs in the database rather than in whichever screen happens to remember to check.
The foreign key actions are chosen individually, not copied. order_items cascades from orders, because an order line genuinely cannot exist without its order. It does not cascade from products — deleting a product must be refused while it appears on a past order, since removing it would rewrite financial history. products uses ON DELETE SET NULL for its category, because a product without a category is untidy but a product that disappears with its category is a bug. Three tables, three different answers, each following from what the data means.
Two dialect points. ENUM for the order status is MySQL-only and has a real cost: adding a status later requires an ALTER TABLE on the whole table. PostgreSQL uses CREATE TYPE ... AS ENUM, SQL Server has no equivalent and uses a CHECK constraint, and a portable design would use a small order_statuses lookup table instead. And the CHECK on the review rating is enforced by MySQL only from version 8.0.16 onward — older versions parse the clause and ignore it silently, so verify it works before trusting it.
CREATE DATABASE IF NOT EXISTS shop
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE shop;
CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
full_name VARCHAR(100) NOT NULL,
email VARCHAR(120) NOT NULL UNIQUE,
phone VARCHAR(15),
password_hash VARCHAR(255) NOT NULL, -- a hash, never a password
city VARCHAR(60),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
-- A category can live inside another category: a link to itself
CREATE TABLE categories (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(80) NOT NULL UNIQUE,
parent_id INT NULL,
FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(200) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL, -- rupees, never FLOAT
stock INT NOT NULL DEFAULT 0,
is_active BOOLEAN NOT NULL DEFAULT TRUE,
category_id INT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
CONSTRAINT chk_price_positive CHECK (price > 0),
CONSTRAINT chk_stock_not_negative CHECK (stock >= 0)
) ENGINE=InnoDB;
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
status ENUM('pending','paid','shipped','delivered','cancelled')
NOT NULL DEFAULT 'pending',
total_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
shipping_address TEXT,
ordered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT
) ENGINE=InnoDB;
-- The junction table. unit_price records what was actually paid.
CREATE TABLE order_items (
id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL DEFAULT 1,
unit_price DECIMAL(10,2) NOT NULL,
UNIQUE KEY uq_order_product (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT,
CONSTRAINT chk_quantity_positive CHECK (quantity > 0)
) ENGINE=InnoDB;
CREATE TABLE reviews (
id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
product_id INT NOT NULL,
rating TINYINT NOT NULL,
comment TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uq_one_review_per_product (customer_id, product_id),
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
CONSTRAINT chk_rating_range CHECK (rating BETWEEN 1 AND 5)
) ENGINE=InnoDB;
-- PostgreSQL would write the status column as:
-- CREATE TYPE order_status AS ENUM ('pending','paid','shipped',
-- 'delivered','cancelled');
-- SQL Server has no ENUM; use a CHECK constraint or a lookup table.- The two
UNIQUEkeys are doing quiet but important work.uq_order_productstops the same product being added twice as two separate lines on one order, which would produce an order that displays a duplicate row and totals incorrectly — the application should increasequantityinstead.uq_one_review_per_productstops one customer flooding a product with reviews. Both are business rules, and both are far safer enforced here than in application code that somebody will one day bypass.
Indexes, and Sample Data to Test Against
The primary keys and unique constraints already gave you several indexes for free. What is missing are the foreign key columns and the columns your listed questions filter and sort by. On MySQL's InnoDB the foreign keys are indexed automatically, but adding them explicitly costs nothing, documents the intent, and is required if this schema is ever moved to PostgreSQL. The composite index on (customer_id, ordered_at) serves 'this customer's orders, newest first' in one contiguous read, with the equality column first and the sort column second, exactly as the indexing lesson described.
Then load some data. Sample data is not decoration — it is how you find out whether the constraints you wrote actually fire, and the most useful thing you can do with a fresh schema is deliberately try to break it. Insert an order line with a quantity of zero. Insert two reviews from the same customer for the same product. Try to delete a product that appears on an order. If any of those succeed, a rule you thought you had is not there, and it is much better to learn that now than after the demo.
Note two things about the data below. The password_hash column holds placeholder text here, and in a real application it would hold the output of a password hashing function such as bcrypt or Argon2 — never the password itself, and never a plain MD5 or SHA-256 hash, which are designed to be fast and therefore quick to attack. And the unit_price values on the order lines match the current product prices only because nothing has changed yet; the whole point of that column is that they are free to diverge.
Insert in dependency order — categories before products, customers before orders, orders before order items — because the foreign keys will refuse anything else. If an insert is rejected with a foreign key error, the parent row does not exist yet, and that error is the constraint doing precisely the job you gave it.
-- Foreign keys and the columns the questions actually filter on
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_orders_customer_date ON orders(customer_id, ordered_at);
CREATE INDEX idx_orders_ordered_at ON orders(ordered_at);
CREATE INDEX idx_order_items_product ON order_items(product_id);
CREATE INDEX idx_reviews_product ON reviews(product_id);
-- order_items(order_id) is already the left column of uq_order_product
-- Amounts are in rupees. Insert parents before children.
INSERT INTO categories (name, parent_id) VALUES
('Electronics', NULL),
('Books', NULL),
('Laptops', 1),
('Smartphones', 1);
INSERT INTO customers (full_name, email, phone, password_hash, city) VALUES
('Ananya Sharma', 'ananya@example.com', '9876543210', 'bcrypt-hash-1', 'Pune'),
('Ravi Kumar', 'ravi@example.com', '9876500011', 'bcrypt-hash-2', 'Chennai'),
('Meera Nair', 'meera@example.com', '9876500022', 'bcrypt-hash-3', 'Kochi'),
('Arjun Patel', 'arjun@example.com', '9876500033', 'bcrypt-hash-4', 'Ahmedabad');
INSERT INTO products (name, description, price, stock, category_id) VALUES
('Aspire 5 Laptop', '15-inch, 16GB RAM, 512GB SSD', 52999.00, 12, 3),
('Redmi Note 14', '6.7-inch display, 5000mAh', 18499.00, 40, 4),
('Wireless Mouse', 'Silent click, 2.4GHz', 899.00, 150, 1),
('SQL for Beginners', 'Paperback, 320 pages', 649.00, 80, 2),
('USB-C Hub', '7-in-1, 100W passthrough', 2499.00, 0, 1);
INSERT INTO orders (customer_id, status, total_amount, shipping_address, ordered_at) VALUES
(1, 'delivered', 53898.00, 'Kothrud, Pune', '2026-06-14 10:22:00'),
(2, 'shipped', 18499.00, 'T Nagar, Chennai', '2026-07-02 18:40:00'),
(1, 'paid', 3148.00, 'Kothrud, Pune', '2026-07-19 09:05:00'),
(3, 'pending', 649.00, 'Panampilly, Kochi', '2026-07-28 20:11:00');
-- unit_price is copied from the product price AT THIS MOMENT
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 52999.00),
(1, 3, 1, 899.00),
(2, 2, 1, 18499.00),
(3, 5, 1, 2499.00),
(3, 4, 1, 649.00),
(4, 4, 1, 649.00);
INSERT INTO reviews (customer_id, product_id, rating, comment) VALUES
(1, 1, 5, 'Fast, and the battery easily lasts a full day of classes.'),
(2, 2, 4, 'Good phone for the price. Camera is average indoors.'),
(1, 3, 3, 'Works fine, but the scroll wheel is noisy.'),
(3, 4, 5, 'Explains joins better than my textbook.'),
(4, 4, 4, 'Clear and short. Wanted more on indexing.');
-- Now try to break it. Every one of these should be rejected.
-- INSERT INTO order_items (order_id, product_id, quantity, unit_price)
-- VALUES (1, 2, 0, 18499.00); -- quantity must be > 0
-- INSERT INTO reviews (customer_id, product_id, rating)
-- VALUES (1, 1, 5); -- duplicate review
-- INSERT INTO reviews (customer_id, product_id, rating)
-- VALUES (1, 2, 9); -- rating out of range
-- DELETE FROM products WHERE id = 1; -- appears on order 1- If those four deliberate failures all succeed on your server, check two things before blaming the schema. MySQL enforces
CHECKconstraints only from version 8.0.16 onward, and foreign keys only on InnoDB tables —SHOW CREATE TABLE order_items;will show you both the engine and whether the constraints were kept. A rule you believe is enforced and is not is worse than no rule at all, because you stop checking for it in code.
The Queries the Application Actually Needs
Here are the questions from the brief, answered. Each one is worth reading for the choice it makes, because these are the same choices that come up in interviews.
The product listing uses a LEFT JOIN to categories, not an INNER JOIN. Since category_id allows NULL, an inner join would silently drop every uncategorised product from the shop's own product list — one of the most common bugs in this exact query. The average rating uses a LEFT JOIN for the same reason: a product with no reviews yet must still appear, with a rating of NULL rather than being absent.
The revenue report computes SUM(oi.quantity * oi.unit_price) from the order lines rather than reading orders.total_amount. Both should agree; computing it from the lines is the version that stays right if a line is ever edited, and comparing the two is a useful data quality check in itself. It also filters on a half-open date range instead of wrapping ordered_at in a function, so the index on that column is usable.
The last two show the two ways to ask a negative question. 'Which products have never been ordered' is written with NOT EXISTS, which stops as soon as it finds one matching row and, importantly, behaves correctly when the inner query could produce a NULL — NOT IN against a list containing a NULL returns no rows at all, which is the single nastiest silent failure in SQL. And 'top-rated products' filters on an aggregate, which is what HAVING is for; the same condition in WHERE would be rejected, because WHERE runs before the rows have been grouped.
-- 1. Product listing. LEFT JOIN keeps uncategorised products visible.
SELECT p.name AS product, p.price, c.name AS category
FROM products p
LEFT JOIN categories c ON c.id = p.category_id
WHERE p.is_active = TRUE
ORDER BY p.price DESC;
-- 2. One customer's order history, newest first
SELECT o.id, o.ordered_at, o.status, o.total_amount
FROM orders o
WHERE o.customer_id = 1
ORDER BY o.ordered_at DESC;
-- 3. One order, fully itemised, with its line totals
SELECT
p.name,
oi.quantity,
oi.unit_price,
oi.quantity * oi.unit_price AS line_total
FROM order_items oi
JOIN products p ON p.id = oi.product_id
WHERE oi.order_id = 1;
-- 4. Average rating per product. LEFT JOIN keeps unreviewed products.
SELECT
p.name,
ROUND(AVG(r.rating), 1) AS avg_rating,
COUNT(r.id) AS review_count
FROM products p
LEFT JOIN reviews r ON r.product_id = p.id
GROUP BY p.id, p.name
ORDER BY avg_rating DESC;
-- 5. Revenue by month, computed from the lines, index-friendly dates
SELECT
DATE_FORMAT(o.ordered_at, '%Y-%m') AS month,
COUNT(DISTINCT o.id) AS orders,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'
AND o.ordered_at >= '2026-01-01'
AND o.ordered_at < '2027-01-01'
GROUP BY DATE_FORMAT(o.ordered_at, '%Y-%m')
ORDER BY month;
-- 6. Products nobody has ever ordered
SELECT p.id, p.name, p.price
FROM products p
WHERE NOT EXISTS (
SELECT 1 FROM order_items oi WHERE oi.product_id = p.id
);
-- 7. Well-reviewed products: a filter on an aggregate, so HAVING
SELECT
p.name,
ROUND(AVG(r.rating), 1) AS avg_rating,
COUNT(r.id) AS reviews
FROM products p
JOIN reviews r ON r.product_id = p.id
GROUP BY p.id, p.name
HAVING AVG(r.rating) >= 4 AND COUNT(r.id) >= 2
ORDER BY avg_rating DESC;
-- 8. Stock that needs attention
SELECT id, name, stock FROM products
WHERE is_active = TRUE AND stock <= 5
ORDER BY stock;- Query 5 uses
COUNT(DISTINCT o.id)rather thanCOUNT(*), and the reason is the join. Joiningorderstoorder_itemsmultiplies each order into one row per line, so a plainCOUNT(*)would count order lines and report an inflated number of orders. Any time you aggregate across a one-to-many join, check whether your counts have been multiplied — the revenue total is correct here precisely because it is meant to be summed per line.
Placing an Order Safely, and Finishing the Project
One piece is left, and it is the piece that separates a schema exercise from something that behaves like a real system. Placing an order is not one statement. It creates the order, adds a line for each product, reduces the stock, and computes the total — and if any of those fails halfway, the database must not be left holding an order with no lines or stock that was deducted for a sale that never happened. That is a transaction, exactly as the previous lesson described.
The stock update inside it needs care. Do not read the stock into your application, subtract, and write the new number back: two customers checking out at the same moment both read 1 and both write 0, and you have sold an item you do not have. Do the arithmetic in SQL with a condition attached — SET stock = stock - 1 WHERE id = 5 AND stock >= 1 — and then check how many rows were affected. Zero rows means it was out of stock, and the application rolls the whole transaction back. The check and the change happen in one statement, so there is no window between them for anybody to slip through.
Finish by adding a view for the summary that several screens will want, so the join logic lives in one place rather than being retyped. Then go back over the project with the checklist this course has built: does every table have a primary key; is every money column DECIMAL; is every foreign key deliberate about what happens on delete; does every query you run often have an index behind it; is every query in your application code parameterised; and can you restore the database from a dump into an empty schema without editing the file.
That is the whole thing, and it is a genuinely reasonable project to talk about in an interview. When you do, talk about the decisions rather than the syntax — why unit_price is copied onto the order line, why the product foreign key restricts instead of cascading, why the stock update is written as one conditional statement. Anybody can write a CREATE TABLE; being able to explain why the table looks like that is the part that is worth something.
-- Placing an order: all of it, or none of it
START TRANSACTION;
INSERT INTO orders (customer_id, status, shipping_address)
VALUES (2, 'pending', 'T Nagar, Chennai');
SET @order_id = LAST_INSERT_ID();
-- Take the price from the product as it stands right now
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT @order_id, id, 2, price FROM products WHERE id = 3;
-- Reduce stock and check availability in ONE statement.
-- If this affects 0 rows, there was not enough stock: ROLLBACK.
UPDATE products
SET stock = stock - 2
WHERE id = 3 AND stock >= 2;
-- Keep the stored total in step with the lines
UPDATE orders o
SET total_amount = (
SELECT COALESCE(SUM(quantity * unit_price), 0)
FROM order_items WHERE order_id = o.id
)
WHERE o.id = @order_id;
COMMIT;
-- ROLLBACK; -- if the stock update affected 0 rows
-- The summary several screens will want, defined once
CREATE VIEW customer_summary AS
SELECT
c.id AS customer_id,
c.full_name,
c.city,
COUNT(DISTINCT o.id) AS order_count,
COALESCE(SUM(o.total_amount), 0) AS lifetime_value,
MAX(o.ordered_at) AS last_order_at
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id
AND o.status <> 'cancelled'
GROUP BY c.id, c.full_name, c.city;
SELECT * FROM customer_summary ORDER BY lifetime_value DESC;
-- A consistency check worth running now and then
SELECT o.id, o.total_amount, SUM(oi.quantity * oi.unit_price) AS from_lines
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, o.total_amount
HAVING o.total_amount <> SUM(oi.quantity * oi.unit_price);
-- And before you call it done
-- mysqldump -u root -p --single-transaction shop > shop_backup.sql- Notice where the cancelled-order condition sits in
customer_summary: in theONclause, not in aWHERE. On aLEFT JOINthat distinction decides the answer. InON, cancelled orders are simply not matched and a customer whose only order was cancelled still appears with a count of zero. Moved toWHERE, the same test is applied after the join and discards that customer'sNULLrow entirely, so they vanish from the report — turning the left join back into an inner one without changing the wordLEFT.