This project simulates a simple retail sales management system using SQL. It focuses on database design, data analysis, and report generation through practical SQL queries involving customers, products, and orders.
This project demonstrates the use of SQL to model and analyze business transactions in a sales environment. It includes a normalized schema with foreign key relationships and a variety of real-world query cases, suitable for interviews and SQL practice.
| Column Name | Data Type | Description |
|---|---|---|
| CustomerID | INT (PK) | Unique identifier for a customer |
| Name | VARCHAR | Customer's name |
| City | VARCHAR | City where the customer lives |
| JoinDate | DATE | When the customer joined |
| Column Name | Data Type | Description |
|---|---|---|
| ProductID | INT (PK) | Unique product ID |
| Name | VARCHAR | Product name |
| Category | VARCHAR | Product category |
| Price | DECIMAL | Price per unit |
| Column Name | Data Type | Description |
|---|---|---|
| OrderID | INT (PK) | Unique order ID |
| CustomerID | INT (FK) | Linked customer ID |
| ProductID | INT (FK) | Linked product ID |
| OrderDate | DATE | Date of the order |
| Quantity | INT | Quantity of product ordered |
The following queries are included:
- List all customers from Mumbai.
- Find total number of orders placed.
- Calculate total quantity ordered per product.
- Calculate total revenue generated per product.
- Find top 3 customers by total spending.
- Show orders placed in the last 7 days.
- Calculate average order value.
- Revenue generated by each product category.
- Find the city with the most customers.
- Total revenue from each customer.
- List customers who placed more than one order.
- Retrieve the highest-priced product.
- List customers and their first order date.
- Identify customers who haven’t placed any orders.
- Monthly revenue report.
- Most popular product by quantity sold.
- Total order count and quantity per customer.
- Analyze customer purchasing behavior
- Generate sales reports for specific timeframes
- Identify high-value customers
- Track product popularity and category performance
- Oracle SQL / MySQL / PostgreSQL
- SQL Developer or any SQL IDE
- Clone or download the project.
- Import and execute
Sales Management System.sqlin your SQL environment. - Run and customize the included SQL queries based on your needs.
- Add payment status or delivery tracking
- Build a reporting dashboard using Power BI or Tableau
- Integrate with frontend or ERP systems
This project is open-source and available for educational and portfolio use. Contributions are welcome!