SQL PROJECT REPORT
Retail Sales Database Analysis using SQL
1. Project Objective
The purpose of this project is to design and analyze a retail sales database using SQL.
The project focuses on creating a structured relational database and performing queries
to understand customer purchasing behavior and sales patterns.
Main goals of the project include:
• Designing a relational database for a retail sales system.
• Organizing data into multiple related tables.
• Performing SQL queries for business analysis.
• Studying customer buying patterns.
• Evaluating product and monthly sales performance.
2. Database Design
The retail sales database is organized into four main tables:
1. Customers
2. Products
3. Orders
4. Order_Items
Each table stores specific information and they are connected through
primary keys and foreign keys. This structure allows efficient data storage,
easy querying, and better data management.
3. Table Structure
Customers Table
Stores information about customers who purchase products.
Columns:
customer_id – Unique identification number for each customer
customer_name – Name of the customer
city – City where the customer lives
state – State of the customer
SQL Query – Customers Table
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100),
city VARCHAR(50),
state VARCHAR(50)
);
Products Table
The products table contains information about items available for sale.
Columns:
product_id – Unique identifier for each product
product_name – Name of the product
category – Product category
price – Price of the product
SQL Query – Products Table
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
category VARCHAR(50),
price DECIMAL(10,2)
);
Orders Table
The orders table records purchase orders placed by customers.
Columns:
order_id – Unique order identifier
customer_id – ID of the customer who placed the order
order_date – Date when the order was placed
SQL Query – Orders Table
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
Order_Items Table
This table records the products included in each order.
Columns:
order_item_id – Unique identifier for order items
order_id – Order reference
product_id – Product reference
quantity – Number of products purchased
sales_amount – Total sales value
SQL Query – Order_Items Table
CREATE TABLE order_items (
order_item_id INT PRIMARY KEY,
order_id INT,
product_id INT,
quantity INT,
sales_amount DECIMAL(10,2),
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
4. Data Insertion
Sample data was inserted into the tables using SQL INSERT statements.
These records represent customers, products, and order transactions.
Example:
INSERT INTO customers VALUES
(1,'Retail Customer','Chennai','Tamil Nadu');
5. Analytical Queries
Several SQL queries were used to analyze sales performance and customer activity.
These queries help businesses understand revenue sources and customer preferences.
Total Sales per Product
SELECT p.product_name,
SUM(oi.sales_amount) AS total_sales
FROM order_items oi
JOIN products p
ON oi.product_id = p.product_id
GROUP BY p.product_name;
This query calculates the total revenue generated by each product.
Total Sales by Category
SELECT [Link],
SUM(oi.sales_amount) AS total_sales
FROM order_items oi
JOIN products p
ON oi.product_id = p.product_id
GROUP BY [Link];
This analysis helps determine which product categories perform best.
Top 5 Customers
SELECT c.customer_name,
SUM(oi.sales_amount) AS total_purchase
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_name
ORDER BY total_purchase DESC
LIMIT 5;
This query identifies the highest spending customers.
Monthly Sales Analysis
SELECT MONTH(order_date) AS month,
SUM(oi.sales_amount) AS monthly_sales
FROM orders o
JOIN order_items oi
ON o.order_id = oi.order_id
GROUP BY MONTH(order_date)
ORDER BY month;
This query shows how sales vary from month to month.
Average Order Value
SELECT AVG(order_total) AS average_order_value
FROM (
SELECT order_id,
SUM(sales_amount) AS order_total
FROM order_items
GROUP BY order_id
) AS order_summary;
This query calculates the average value of each order.
LEFT JOIN Example
SELECT c.customer_name,
o.order_id,
oi.sales_amount
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
LEFT JOIN order_items oi
ON o.order_id = oi.order_id;
LEFT JOIN ensures that all customers are displayed even if they have not placed any orders.
Products with High Sales
SELECT p.product_name,
SUM(oi.sales_amount) AS total_sales
FROM products p
JOIN order_items oi
ON p.product_id = oi.product_id
GROUP BY p.product_name
HAVING total_sales > 5000;
The HAVING clause filters products whose total sales exceed 5000.
6. Normalization
The database follows normalization principles to reduce redundancy and
improve data consistency.
Customer information is stored in the customers table.
Product details are stored in the products table.
Order information and order details are stored separately.
This approach improves database efficiency and maintains data accuracy.
7. Primary Key and Foreign Key
Primary Key:
A primary key uniquely identifies each record in a table.
Examples: customer_id, product_id, order_id.
Foreign Key:
A foreign key connects one table with another table.
Examples:
customer_id in orders references customers table.
product_id in order_items references products table.
These relationships maintain referential integrity.
8. Tools Used
MySQL Workbench
SQL
Microsoft Word
Retail Sales Dataset
9. Conclusion
This project illustrates how SQL can be used to design a relational database
and analyze business data effectively.
By organizing data into structured tables and executing analytical queries,
important insights such as customer behavior, product performance, and monthly
sales trends can be identified.
The project also strengthened understanding of SQL concepts including joins,
aggregation functions, subqueries, and database relationships.