This project demonstrates how SQL can be used to analyze business data from an online bookstore. Using PostgreSQL, I designed a relational database, imported transactional data, and answered real business questions related to sales, customers, inventory, and revenue.
The goal of this project was not only to practice SQL syntax but also to understand how SQL helps businesses make informed decisions from their data.
An online bookstore stores information about books, customers, and orders every day. As the amount of data grows, it becomes difficult to answer important business questions manually, such as:
- Which books generate the highest revenue?
- Which customers spend the most?
- Which genres are performing well?
- How are sales changing over time?
- Which books need inventory attention?
This project demonstrates how SQL can answer these questions efficiently using structured queries.
The project consists of three related tables.
Stores information about each book.
- Book ID
- Title
- Author
- Genre
- Published Year
- Price
- Stock
Stores customer details.
- Customer ID
- Customer Name
- Phone Number
- City
- Country
Stores every purchase made by customers.
- Order ID
- Customer ID
- Book ID
- Order Date
- Quantity
- Total Amount
- PostgreSQL
- pgAdmin
- CSV Data Import
The project was completed using the following workflow:
- Designed the relational database.
- Created tables with Primary Keys and Foreign Keys.
- Imported data from CSV files.
- Validated the imported data.
- Wrote SQL queries to solve business problems.
- Analyzed query results to generate business insights.
Throughout this project, I applied practical SQL concepts including:
- SELECT
- WHERE
- ORDER BY
- GROUP BY
- HAVING
- Aggregate Functions
- INNER JOIN
- LEFT JOIN
- CASE Statements
- Subqueries
- Common Table Expressions (CTEs)
- Window Functions
- Ranking Functions
- Date Functions
This project answers several real-world business questions, including:
- Retrieve books by genre.
- Find books published after a specific year.
- Identify customers from different countries.
- Calculate total sales revenue.
- Find the best-selling books.
- Analyze sales by genre.
- Identify high-value customers.
- Calculate monthly revenue.
- Find customers who have never placed an order.
- Rank books based on revenue generated.
- Calculate cumulative revenue over time.
- Measure the revenue contribution of each genre.
The SQL analysis helped uncover several useful business insights:
- Sales performance varies across different book genres.
- A small group of customers contributes significantly to total revenue.
- Some books consistently outperform others in sales.
- Revenue trends can be monitored over time to identify seasonal patterns.
- SQL window functions make it easier to analyze rankings and cumulative sales without writing complex procedural code.
Based on the analysis:
- Maintain sufficient stock for high-selling books.
- Promote underperforming categories through marketing campaigns.
- Reward loyal customers with targeted offers.
- Monitor monthly revenue trends for better inventory planning.
- Focus future purchasing decisions on genres with strong sales performance.
This project demonstrates my ability to:
- Design relational databases
- Write analytical SQL queries
- Work with multiple related tables
- Analyze business data
- Solve real-world business problems using SQL
- Generate meaningful business insights
- Apply advanced SQL concepts for reporting and analysis
Online-Bookstore-SQL-Analysis/
│
├── README.md
├── Books.csv
├── Customers.csv
├── Orders.csv
└── project.sql
- Install PostgreSQL and pgAdmin.
- Create a new database.
- Execute
project.sqlto create the database schema. - Import the CSV files into their respective tables.
- Execute the business query script.
- Review the query results for analysis.
This project was built to strengthen my SQL skills through hands-on practice with relational databases and business-oriented data analysis. It reflects how SQL is used to solve real analytical problems and support business decision-making.
Mohan Thurpati
Aspiring Data Analyst
Skills: Excel • SQL • Power BI • Python
LinkedIn: https://www.linkedin.com/in/mohanthurpati
Portfolio: https://mohanthurpati.framer.website