SQL Practice Projects: E-Commerce, Banking and Social Media Databases with Queries

The fastest way to make SQL stick is to build a small database and answer real questions on it. These three SQL practice projects each give you a schema and the queries a business would actually ask, from best-selling products to account balances and follower counts. Create them in MySQL or PostgreSQL and treat each query as an interview question.

Why Build SQL Projects?

Project 1: E-Commerce Database

-- Schema Design
CREATE TABLE customers (
    customer_id  INT AUTO_INCREMENT PRIMARY KEY,
    name         VARCHAR(100) NOT NULL,
    email        VARCHAR(200) UNIQUE NOT NULL,
    phone        VARCHAR(15),
    city         VARCHAR(50),
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    product_id   INT AUTO_INCREMENT PRIMARY KEY,
    name         VARCHAR(200) NOT NULL,
    category     VARCHAR(50),
    price        DECIMAL(10,2) NOT NULL CHECK (price > 0),
    stock        INT DEFAULT 0,
    rating       DECIMAL(3,2)
);

CREATE TABLE orders (
    order_id     INT AUTO_INCREMENT PRIMARY KEY,
    customer_id  INT NOT NULL REFERENCES customers(customer_id),
    order_date   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status       ENUM('pending','shipped','delivered','cancelled') DEFAULT 'pending',
    total_amount DECIMAL(12,2)
);

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

Real Interview Queries on This Schema:

-- Q1: Top 5 best-selling products by revenue (last 3 months)
SELECT p.name, SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_date >= DATE_SUB(NOW(), INTERVAL 3 MONTH)
  AND o.status = 'delivered'
GROUP BY p.product_id, p.name
ORDER BY revenue DESC LIMIT 5;

-- Q2: Customers with more than 3 orders but never cancelled
SELECT c.name, COUNT(o.order_id) AS order_count
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
HAVING COUNT(o.order_id) > 3
  AND SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END) = 0
ORDER BY order_count DESC;

-- Q3: Products never ordered
SELECT p.name FROM products p
WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.product_id);

-- Q4: Average order value per city
SELECT c.city, COUNT(o.order_id) AS orders,
       ROUND(AVG(o.total_amount), 2) AS avg_order_value
FROM customers c JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status != 'cancelled'
GROUP BY c.city ORDER BY avg_order_value DESC;

Project 2: Banking System

CREATE TABLE bank_accounts (
    account_id   BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_id  INT NOT NULL,
    account_type ENUM('savings','current','fd') NOT NULL,
    balance      DECIMAL(15,2) DEFAULT 0 CHECK (balance >= 0),
    status       ENUM('active','frozen','closed') DEFAULT 'active',
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE transactions (
    txn_id       BIGINT AUTO_INCREMENT PRIMARY KEY,
    account_id   BIGINT NOT NULL REFERENCES bank_accounts(account_id),
    txn_type     ENUM('credit','debit','transfer') NOT NULL,
    amount       DECIMAL(15,2) NOT NULL CHECK (amount > 0),
    description  VARCHAR(200),
    txn_date     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Safe transfer stored procedure with transaction
DELIMITER $$
CREATE PROCEDURE transfer_funds(
    IN from_acc BIGINT, IN to_acc BIGINT, IN amount DECIMAL(15,2))
BEGIN
    DECLARE current_balance DECIMAL(15,2);
    START TRANSACTION;
    SELECT balance INTO current_balance FROM bank_accounts
    WHERE account_id = from_acc FOR UPDATE;  -- row-level lock
    IF current_balance < amount THEN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
    ELSE
        UPDATE bank_accounts SET balance = balance - amount WHERE account_id = from_acc;
        UPDATE bank_accounts SET balance = balance + amount WHERE account_id = to_acc;
        INSERT INTO transactions (account_id, txn_type, amount, description)
        VALUES (from_acc, 'debit', amount, CONCAT('Transfer to ', to_acc));
        INSERT INTO transactions (account_id, txn_type, amount, description)
        VALUES (to_acc, 'credit', amount, CONCAT('Transfer from ', from_acc));
        COMMIT;
    END IF;
END $$
DELIMITER ;

Project 3: Social Media Database

CREATE TABLE users (
    user_id      INT AUTO_INCREMENT PRIMARY KEY,
    username     VARCHAR(50) UNIQUE NOT NULL,
    email        VARCHAR(200) UNIQUE NOT NULL,
    bio          TEXT,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
    post_id      INT AUTO_INCREMENT PRIMARY KEY,
    user_id      INT REFERENCES users(user_id),
    content      TEXT NOT NULL,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE follows (
    follower_id  INT REFERENCES users(user_id),
    following_id INT REFERENCES users(user_id),
    followed_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (follower_id, following_id),
    CHECK (follower_id != following_id)  -- can't follow yourself
);

CREATE TABLE likes (
    user_id      INT REFERENCES users(user_id),
    post_id      INT REFERENCES posts(post_id),
    liked_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, post_id)
);

-- Instagram-style: posts from people I follow (feed)
SELECT p.post_id, u.username, p.content, p.created_at,
       COUNT(l.user_id) AS like_count
FROM follows f
JOIN posts p ON f.following_id = p.user_id
JOIN users u ON p.user_id = u.user_id
LEFT JOIN likes l ON p.post_id = l.post_id
WHERE f.follower_id = 42  -- current user
GROUP BY p.post_id, u.username, p.content, p.created_at
ORDER BY p.created_at DESC LIMIT 20;

-- Mutual follows (friends)
SELECT u.username AS mutual_friend
FROM follows f1
JOIN follows f2 ON f1.following_id = f2.follower_id
               AND f1.follower_id  = f2.following_id
JOIN users u ON f1.following_id = u.user_id
WHERE f1.follower_id = 42;

FAQs

What is a good SQL project for beginners?

An e-commerce database with customers, products, orders and order items: it covers joins, aggregation, dates and window functions with questions everyone understands.

Where can I practise SQL for free?

Install MySQL or PostgreSQL locally, use an online SQL editor, or practise problem sets on sites such as LeetCode, HackerRank and StrataScratch.

Should I put SQL projects on my resume?

Yes, especially for QA and data roles: a GitHub repository with the schema, sample data and documented queries shows practical skill.