Practice Projects

Consolidate SQL knowledge through practical projects. This chapter provides complete practical cases.

Project 1: Blog System

Requirements

• Users can publish articles
• Articles can have multiple tags
• Users can comment on articles

Database Design

-- Users table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Posts table
CREATE TABLE posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    view_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Tags table
CREATE TABLE tags (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) UNIQUE NOT NULL
);

-- Post-Tag relationship
CREATE TABLE post_tags (
    post_id INT,
    tag_id INT,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

-- Comments table
CREATE TABLE comments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    post_id INT NOT NULL,
    user_id INT NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Common Queries

-- 1. Get post list with author and tags
SELECT 
    p.id,
    p.title,
    u.username AS author,
    p.view_count,
    GROUP_CONCAT(t.name) AS tags
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN post_tags pt ON p.id = pt.post_id
LEFT JOIN tags t ON pt.tag_id = t.id
GROUP BY p.id
ORDER BY p.created_at DESC
LIMIT 10;

-- 2. Popular tags
SELECT 
    t.name,
    COUNT(pt.post_id) AS post_count
FROM tags t
JOIN post_tags pt ON t.id = pt.tag_id
GROUP BY t.id
ORDER BY post_count DESC
LIMIT 10;

Project 2: E-commerce

Core Features

• Product management
• Shopping cart
• Order processing
• Inventory management

Key Queries

-- 1. Add to cart
INSERT INTO cart_items (user_id, product_id, quantity)
VALUES (1, 101, 2)
ON DUPLICATE KEY UPDATE quantity = quantity + 2;

-- 2. Create order
START TRANSACTION;

INSERT INTO orders (user_id, total_amount, status)
VALUES (1, 299.99, 'pending');

SET @order_id = LAST_INSERT_ID();

INSERT INTO order_items (order_id, product_id, quantity, price)
SELECT @order_id, product_id, quantity, price
FROM cart_items
WHERE user_id = 1;

COMMIT;

-- 3. Sales report
SELECT 
    DATE(created_at) AS date,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_sales
FROM orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(created_at);

Practice Exercises

Beginner

1. Query all users with their order count
2. Query top 10 most expensive products
3. Count products per category

Intermediate

4. Query daily order count for last 7 days
5. Find users who bought product A but not B
6. Rank users by total spending

Advanced

7. Product recommendations
8. Calculate user retention rate
9. Inventory alert system

Summary

• Learn SQL through practical projects
• Understand business requirements
• Design proper database structure
• Write efficient queries

Next Step: Learn RESOURCES