Case Study: Bookstore Management System

A bookstore maintains a database to track its publishers, books, customers,

purchases, and sales.

 The store buys books from publishers and maintains stock.

 Customers buy books, and the transactions are recorded as sales.

 Management wants to analyze purchase and sales data for decision-making.

Schema
Publisher(pub_id PK, pub_name, city, phone)
Book(book_id PK, title, author, price, pub_id FK)
Customer(cust_id PK, cust_name, city, phone)
Purchase(purchase_id PK, book_id FK, pub_id FK, quantity, purchase_date)
Sale(sale_id PK, book_id FK, cust_id FK, quantity, sale_date)
Questions –
1. Insert 3 rows in every table.
2. Display all books whose price is greater than 500.
3. # Display customer names with the books they purchased and the sales date.
4. Show books with price higher than the average price of all books.
5. Identify books that have never been sold.
6. Increase the price of the book "Data Science Handbook" by 10%.
7. Customers who have never purchased any book.
8. Books that are not supplied by any publisher (if possible).
9. Publishers who have not supplied any books yet.
10. # List books that are running low in stock (total purchased – total sold < 10).
11. # Identify the top 5 selling books in the last month.
12. # Find publishers who supplied books with total purchases > 100 units.
13. Calculate total revenue generated per book (price × quantity sold).
14. #Find the city with the highest total sales (based on customer city).
15. List books priced above ₹1500 and published by ‘Pearson Education’.
16. Display publisher-wise average book price and number of books supplied.
17. Show the total number of customers who purchased each book.
18. Identify publishers who supplied more than 1000 books in total.
19. Find the book with the maximum total sales quantity.
20. Display the minimum, maximum, and average book price per publisher.
21. Show customers who purchased more than one book title.
22. #List customers whose names start with ‘A’ and who purchased books costing more than
₹2000.
3.

Select Customer.cust_name, [Link], Sale.sale_date

from Customer

Join Sale on Sale.cust_id=Customer.cust_id

Join Book on Sale.book_id=Book.book_id;

4.

SELECT book_id, title, author, price

FROM Book

WHERE price > (SELECT AVG(price) FROM Book);

Using join

SELECT book_id, title, author, price

FROM Book

WHERE price > (SELECT AVG(price) FROM Book);

5.

SELECT book_id, title, author

FROM Book

WHERE book_id NOT IN (SELECT book_id FROM Sale);

Using join

SELECT Book.book_id, [Link], [Link]

FROM Book

LEFT JOIN Sale ON Book.book_id = Sale.book_id

WHERE Sale.book_id IS NULL;

6.

UPDATE Book

SET price = price * 1.10

WHERE title = 'Data Science Handbook';

7.

SELECT cust_id, cust_name, city, phone

FROM Customer

WHERE cust_id NOT IN (SELECT cust_id FROM Sale);

Using join

SELECT Customer.cust_id, Customer.cust_name, [Link], [Link]

FROM Customer

LEFT JOIN Sale ON Customer.cust_id = Sale.cust_id

WHERE Sale.cust_id IS NULL;

9.

SELECT pub_id, pub_name, city, phone

FROM Publisher

WHERE pub_id NOT IN (SELECT pub_id FROM Purchase);

Using Join

SELECT Publisher.pub_id, Publisher.pub_name, [Link], [Link]

FROM Publisher

LEFT JOIN Purchase ON Publisher.pub_id = Purchase.pub_id

WHERE Purchase.pub_id IS NULL;

10.

Select Book.book_id, [Link], sum([Link]) as Stock

from Purchase

Join Book on Book.book_id=Purchase.book_id

Join Sale on Sale.book_id=Book.book_id

group by Book.book_id, [Link]

having Stock<100;

Alternative way:

Select Book.book_id, [Link], sum([Link]) as Stock

from Purchase, Book, Sale where Sale.book_id=Book.book_id AND

Book.book_id=Purchase.book_id

group by Book.book_id, [Link];

11.

Select Book.book_id, [Link], sum([Link]) as Total_Sales

from Book, Sale where Sale.book_id=Book.book_id

group by Book.book_id, [Link]

order by Total_Sales desc limit 5;

12.

Select Publisher.pub_id, Publisher.pub_name, [Link]

from Purchase

Join Book on Book.pub_id=Purchase.pub_id

JOIn Publisher on Publisher.pub_id=Purchase.pub_id

group by Publisher.pub_id, Publisher.pub_name, [Link]

having sum([Link])>150;
13.

Select Book.book_id,

sum([Link]*[Link]) as Revenue

from Book, Sale

where Book.book_id=Sale.book_id

group by Book.book_id;

14.

SELECT [Link], SUM([Link] * [Link]) AS total_sales

FROM Sale

JOIN Customer ON Sale.cust_id = Customer.cust_id

JOIN Book ON Sale.book_id = Book.book_id

GROUP BY [Link]

ORDER BY total_sales DESC

LIMIT 1;

15.

SELECT book_id, title, author, price, pub_id

FROM Book

WHERE price > 1500

AND pub_id = (SELECT pub_id FROM Publisher WHERE pub_name = 'Pearson Education');

Alternate

SELECT Book.book_id, [Link], [Link], [Link], Publisher.pub_name

FROM Book

JOIN Publisher ON Book.pub_id = Publisher.pub_id

WHERE [Link] > 1500

AND Publisher.pub_name = 'Pearson Education';

16.

Select Publisher.pub_id, Publisher.pub_name, avg([Link]), count(Book.book_id)

from Book, Publisher

where Book.pub_id=Publisher.pub_id

group by Publisher.pub_id;

Alternate:

Select Publisher.pub_id, Publisher.pub_name,count(Book.book_id), avg([Link])

from Book,Publisher

where Publisher.pub_id=Book.pub_id

group by Book.pub_id;

17.

Select book_id, count(cust_id) from Sale group by book_id;

18

Select Publisher.pub_id, Publisher.pub_name, sum([Link]) as Supplied from

Purchase, Publisher

where Publisher.pub_id= Purchase.pub_id

group by Publisher.pub_id, Publisher.pub_name

having Supplied>150;

19.

Select Book.book_id, sum([Link]) from Book, Sale where Book.book_id= Sale.book_id

group by Book.book_id

order by sum([Link]) desc limit 1;

20.

Select Publisher.pub_id, max([Link]), min([Link]), avg([Link]) from Book,

Publisher where Publisher.pub_id=Book.pub_id group by Publisher.pub_id;

21

Select Customer.cust_id, Customer.cust_name, count(Sale.book_id) from Sale, Customer where

Sale.cust_id=Customer.cust_id

group by Customer.cust_id having count(Sale.book_id)>1;

22

Select Customer.cust_name, sum([Link]) from

Customer

Join Sale on Sale.cust_id=Customer.cust_id

Join Book on Book.book_id=Sale.book_id

where Customer.cust_name like 'A%'

group by Customer.cust_name

having sum([Link])>1100;

-- =========================

-- 1. CREATE TABLES

-- =========================

CREATE TABLE Publisher (

pub_id INT PRIMARY KEY,

pub_name VARCHAR(100),

city VARCHAR(50),

phone VARCHAR(15)
);

CREATE TABLE Book (

book_id INT PRIMARY KEY,

title VARCHAR(150),

author VARCHAR(100),

price DECIMAL(10,2),

pub_id INT,

FOREIGN KEY (pub_id) REFERENCES Publisher(pub_id)

);

CREATE TABLE Customer (

cust_id INT PRIMARY KEY,

cust_name VARCHAR(100),

city VARCHAR(50),

phone VARCHAR(15)

);

CREATE TABLE Purchase (

purchase_id INT PRIMARY KEY,

book_id INT,

pub_id INT,

quantity INT,
 purchase_date DATE,

FOREIGN KEY (book_id) REFERENCES Book(book_id),

FOREIGN KEY (pub_id) REFERENCES Publisher(pub_id)

);

CREATE TABLE Sale (

sale_id INT PRIMARY KEY,

book_id INT,

cust_id INT,

quantity INT,

sale_date DATE,

FOREIGN KEY (book_id) REFERENCES Book(book_id),

FOREIGN KEY (cust_id) REFERENCES Customer(cust_id)

);

-- =========================

-- 2. INSERT VALUES

-- =========================

-- Publishers

INSERT INTO Publisher VALUES

(1, 'Pearson Education', 'Delhi', '9876543210'),

(2, 'McGraw Hill', 'Mumbai', '9123456780'),

(3, 'Oxford Press', 'Bangalore', '9765432109'),

(4, 'Wiley India', 'Hyderabad', '9988776655'),

(5, 'Penguin Books', 'Kolkata', '9090909090'),

(6, 'HarperCollins', 'Pune', '9823456712'),

(7, 'Springer', 'Chennai', '9123987654'),

(8, 'Cengage', 'Delhi', '9345678912'),

(9, 'Elsevier', 'Mumbai', '9871203456'),

(10,'Routledge', 'Jaipur', '9001122334');

-- Books

INSERT INTO Book VALUES

(101, 'Data Science Handbook', 'Jake Brown', 1200, 1),

(102, 'Machine Learning Basics', 'Andrew Ng', 1500, 2),

(103, 'AI Revolution', 'Nick Bostrom', 1800, 3),

(104, 'Database Systems', 'Elmasri', 950, 4),

(105, 'Operating Systems', 'Galvin', 1100, 5),

(106, 'Computer Networks', 'Tanenbaum', 1300, 6),

(107, 'Deep Learning', 'Ian Goodfellow', 2000, 7),

(108, 'Python Programming', 'Mark Lutz', 750, 8),

(109, 'Big Data Analytics', 'Rajkumar Buyya', 1600, 9),

(110, 'Cloud Computing', 'Thomas Erl', 1400, 10);

-- Customers
INSERT INTO Customer VALUES

(201, 'Ravi Kumar', 'Delhi', '9811111111'),

(202, 'Anita Sharma', 'Mumbai', '9822222222'),

(203, 'Vikas Singh', 'Kolkata', '9833333333'),

(204, 'Neha Gupta', 'Chennai', '9844444444'),

(205, 'Amit Patel', 'Pune', '9855555555'),

(206, 'Priya Reddy', 'Hyderabad', '9866666666'),

(207, 'Suresh Iyer', 'Bangalore', '9877777777'),

(208, 'Meena Joshi', 'Jaipur', '9888888888'),

(209, 'Karan Mehta', 'Delhi', '9899999999'),

(210, 'Simran Kaur', 'Mumbai', '9900000000');

-- Purchases (from publishers)

INSERT INTO Purchase VALUES

(301, 101, 1, 200, '2025-01-15'),

(302, 102, 2, 150, '2025-01-20'),

(303, 103, 3, 180, '2025-01-25'),

(304, 104, 4, 100, '2025-02-01'),

(305, 105, 5, 120, '2025-02-05'),

(306, 106, 6, 140, '2025-02-10'),

(307, 107, 7, 160, '2025-02-15'),

(308, 108, 8, 130, '2025-02-20'),

(309, 109, 9, 170, '2025-02-25'),

(310, 110, 10, 150, '2025-03-01');

-- Sales (to customers)

INSERT INTO Sale VALUES

(401, 101, 201, 2, '2025-03-10'),

(402, 102, 202, 1, '2025-03-12'),

(403, 103, 203, 3, '2025-03-15'),

(404, 104, 204, 2, '2025-03-18'),

(405, 105, 205, 1, '2025-03-20'),

(406, 106, 206, 2, '2025-03-22'),

(407, 107, 207, 1, '2025-03-25'),

(408, 108, 208, 4, '2025-03-28');