Create a Book Sales System with MySQL: A Revision Lesson on Foundational SQL

If you've been following my SQL lesson series, you’ve already mastered the fundamentals and explored how SQL is used in data analytics through hands-on practice. Today, we’re shifting gears for a quick revision session—a refresher on everything we've covered so far. Instead of simply rereading previous lessons, let's put active recall into action with a mini-project. By applying what we've learned in a real-world scenario, you'll reinforce key concepts and gain deeper insights. Ready to dive in? Let’s get started!

Today we will be building a Bookstore Management System, where we track book sales, customers, and payments. For this project we will be using DrawSQL app to design our schema and MySQL to create the system.

❇️ Step 1: Database Schema Design using DrawSQL

First step is to brainstorm and list down the tables that are required for the system, along with the attributes/ columns needed. For our book sales system we will be creating the following tables,

• Books

• Customers

• Sales

• Payments

For each table, lets identify the columns required,

• Books - book_id (PRIMARY KEY), title, author, genre, year, price, stock

• Customers - customer_id (PRIMARY KEY), name, email, phone, address

• Sales - sales_id (PRIMARY KEY), sales_date, total, quantity

• Payments - payment_id (PRIMARY KEY), amount, payment_date

Make sure to give proper names to your columns not to get confused among different columns in different tables. Now that we have identified the primary keys for each table, next step is to identify the relationships between tables. Before that I’m going to create my tables up to this point in DrawSQL.

Now lets look at the relationships and add them to our diagram.

• Books and Customers have a many to many relationships as one customer can purchase multiple books and a book can be purchased by multiple customers.

• Books and Sales table also have a many to many relationship.

• Customers and sales tables have a 1 to many relationship as one customer can have multiple different sales but a particular sale can only belong to one customer.

• Customers and payments tables have 1 to many relationship

• Sales and payments tables have one to one relationship as any given payment should be for one sale and a particular sale can have one payment (which is an assumption I’m making)

After identifying table relationships, the next step is to create foreign keys. One important thing to remember is to always be mindful when creating relationships, creating unnecessary relationships can complicate your database. For example, in this scenario, I’m not creating a foreign key to link books and customers table as it will be a redundancy since we are already linking the books table to the sales table and sales table is already linked to the customers table. To simplify this, lets take a look at our updated schema.

Once your diagram is completed, you can export it as a sql file. Now lets move on to MySQL and set up our schema there.

❇️ Step 2: Create the Schema in MySQL

First thing is to create a schema for your project. I have created a schema called books_sales_system, once it is created make sure to double click on it to activate the schema. You can go to File > Open SQL Script to open the file saved from DrawSQL. Make sure everything is in order before your execute the script.

With such easy and simple steps, we have now created our tables in MySQL. Next step is to insert some data into our tables.

❇️ Step 3: Insert Sample Data

Inserting data into SQL is very straightforward and one of the most basic queries we have learned.

Our database and tables are ready. Lets move on to some exercises.

❇️ Step 4: Write SQL Queries

Now we are going to use our tables to practice writing SQL queries. In this section I will try to demonstrate as many different SQL commands as possible.

• Find total sales per book

SELECT b.title, SUM(s.total) as Total
FROM books b
JOIN sales s ON b.book_id=s.book_id
GROUP BY b.title
ORDER BY Total ASC;

• Find top 3 best selling genres

SELECT b.genre, SUM(s.total) as Total
FROM books b
JOIN sales s ON b.book_id=s.book_id
GROUP BY genre
ORDER BY Total Desc
LIMIT 3;

• Get the top spending customer

SELECT c.name, SUM(s.total) as Total_Spent
FROM customers c
JOIN sales s ON c.customer_id=s.customer_id
GROUP BY c.name
ORDER BY Total_Spent DESC
LIMIT 1;

• Find customers who have bought more than 2 books

SELECT c.name, COUNT(s.sales_id) as num_of_purchases
FROM customers c
JOIN sales s ON c.customer_id=s.customer_id
GROUP BY c.name
HAVING num_of_purchases>2;

• Check if stock is low (less than 5 books left)

SELECT title, stock
FROM books
WHERE stock<5;

• Create a stored procedure to check all purchases of a customer.

Delimiter $$

create procedure getcustomerpurchasees(IN customer_id int)
begin
    SELECT c.name, b.title, s.date, s.quantity, s.total
    FROM sales s
    JOIN customers c ON s.customer_id = c.customer_id
    JOIN books b ON s.book_id = b.book_id
    WHERE c.customer_id = customer_id;
end $$
DELIMITER ;

call getcustomerpurchasees(1);

• Rank the books based on sales

Select title, Total,
RANK() OVER(order by Total DESC) as rankk
FROM ( SELECT 
        b.title, 
        SUM(s.total) AS Total
    FROM books b
    JOIN sales s ON b.book_id = s.book_id
    GROUP BY b.title)subquery;

That is all for this revision lesson. Hope you were able to refresh your memory on what we’ve learned so far in SQL.