MySQL + SQL · Lesson 119

Online Shopping Database Project

About this Project

An Online Shopping database holds customers, products and orders — a popular e-commerce project.

Database Schema

CREATE TABLE customers (cust_id INT PRIMARY KEY, name VARCHAR(50), city VARCHAR(30));
CREATE TABLE products (prod_id INT PRIMARY KEY, name VARCHAR(50), price DECIMAL(8,2), stock INT);
CREATE TABLE orders (order_id INT PRIMARY KEY, cust_id INT, prod_id INT, qty INT, order_date DATE,
  FOREIGN KEY(cust_id) REFERENCES customers(cust_id),
  FOREIGN KEY(prod_id) REFERENCES products(prod_id));

Useful Queries

-- total spent by each customer
SELECT c.name, SUM(p.price * o.qty) AS total_spent
FROM orders o JOIN customers c ON o.cust_id=c.cust_id
              JOIN products p ON o.prod_id=p.prod_id
GROUP BY c.name;

-- best-selling products
SELECT p.name, SUM(o.qty) AS units_sold
FROM orders o JOIN products p ON o.prod_id=p.prod_id
GROUP BY p.name ORDER BY units_sold DESC;

Summary

This project shows a real, exam-ready database design with working queries.
Create the tables, insert sample data, and run each query to learn by doing.