This project focuses on building and analyzing a relational database for a bookstore. The primary objective is to simulate a real-world retail environment and perform data analysis using structured query language (SQL). It demonstrates how data-driven insights can be extracted from raw transactional data to support business decision-making.The goal was to simulate a real-world retail environment where books are sold to customers through orders.
The project begins by creating a normalized database with three interconnected tables:
1)Books: Stores detailed information about each book, such as its title, author, genre, price, publication year, and available stock.
2)Customers: Holds customer profiles including name, location, and contact information.
3)Orders: Records transaction data, linking customers and books, along with order dates, quantities, and total amounts spent.
Insights Extracted
- Genre Popularity: Identified popular genres by examining the number of books sold per genre.
- Revenue Tracking: Calculated total revenue generated by the bookstore using order data.
- Customer Behavior:
-> Listed customers who placed multiple orders.
-> Found customers who spent the most.
-> Identified cities and countries with the highest customer engagement.
- Inventory Management:
-> Found the most and least stocked books.
-> Calculated remaining stock after fulfilling all customer orders.
- Top Performers:
-> Highlighted the most expensive books by genre.
-> Identified the most frequently ordered books.