Capstone Project 1

Project Overview

Design and query a database for an online bookstore.

1. Schema Design

Tables:

• Users (ID, Name, Email, Address)
• Books (ID, Title, Author, Price, Stock)
• Orders (ID, UserID, Date, TotalAmount)
• OrderDetails (OrderID, BookID, Quantity, Subtotal)

CREATE TABLE Users (
    ID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100) UNIQUE
);

CREATE TABLE Books (
    ID INT PRIMARY KEY,
    Title VARCHAR(200),
    Price DECIMAL(10,2),
    Stock INT CHECK (Stock >= 0)
);

CREATE TABLE Orders (
    ID INT PRIMARY KEY,
    UserID INT,
    OrderDate DATE,
    FOREIGN KEY (UserID) REFERENCES Users(ID)
);

2. Key Queries

A. Top Selling Books

SELECT b.Title, SUM(od.Quantity) as TotalSold
FROM Books b
JOIN OrderDetails od ON b.ID = od.BookID
GROUP BY b.Title
ORDER BY TotalSold DESC
LIMIT 5;

B. Monthly Revenue

SELECT YEAR(OrderDate), MONTH(OrderDate), SUM(TotalAmount)
FROM Orders
GROUP BY YEAR(OrderDate), MONTH(OrderDate);