# 📚 Online Bookstore SQL Data Analysis (PostgreSQL)
## 🧾 Project Overview
This project demonstrates how to design and analyze a relational database for an **Online Bookstore** using **PostgreSQL**. It includes SQL scripts for creating tables, importing CSV data, and performing analysis queries.
## 🧱 Database Structure
The project consists of three main tables:
1. **Books** – book details (title, author, genre, price, stock)
2. **Customers** – customer info (name, email, city, country)
3. **Orders** – order records (customer_id, book_id, order_date, quantity, total_amount)
## 🎯 Objectives
- Create and manage relational tables in PostgreSQL.
- Import CSV data and establish foreign key relationships.
- Perform SQL-based analysis to extract business insights (sales, inventory, customers).
## 📊 Analysis Performed
Examples of analysis in this project:
- Retrieve books by genre and publication year.
- Identify top-selling and most expensive books.
- Calculate total revenue and available stock.
- Find customers with multiple orders and top spenders.
- Analyze sales by city, author, and genre.
## 🧠 Type of Analysis
- Descriptive Analysis (totals, averages)
- Exploratory Analysis (top/lowest performers)
- Relational / Join Analysis (Books ↔ Orders ↔ Customers)
- Aggregate Analysis (SUM, AVG, COUNT, GROUP BY)
## ⚙️ Tools Used
- PostgreSQL
- pgAdmin or psql (SQL Shell)
- CSV files (data/books.csv, data/customers.csv, data/orders.csv)
- Git & GitHub for (version control) and push project.
## 📂 Folder Structure
OnlineBookstore-SQL-Analysis-PostgreSQL/
│
├── data/
│ ├── books.csv
│ ├── customers.csv
│ └── orders.csv
│
├── onlinebookstore_analysis.sql
└── README.md
## 🚀 How to Run
1. Open pgAdmin or SQL Shell (psql).
2. Create the database:
```sql
CREATE DATABASE OnlineBookstore;
3.Connect to the new database and run the main SQL script:
\i 'onlinebookstore_analysis.sql'
4.If you need to import CSV files (from data/), use:
COPY Books(title, author, genre, published_year, price, stock)
FROM 'data/books.csv'
DELIMITER ','
CSV HEADER;
Note: Use relative paths (data/books.csv) if you run the script from the project folder.
## 🧑💻 Author
Sharik Ansari
Email: sharikkha8900@gmail.com
## 🏷️ Repository Info
**Repository Name:** OnlineBookstore-SQL-Analysis-PostgreSQL
**Language:** SQL
**Category:** Data Analysis / Database Project
## 🧪 Example Query
Top 3 most expensive books in Fantasy:
SELECT title, genre, price
FROM books
WHERE genre='Fantasy'
ORDER BY price DESC
LIMIT 3;