SQL Mini Project Report 2 – Grocery Store

Management System
A grocery store handles a wide variety of products and serves multiple customers daily. To maintain
proper records of stock, customers, and sales, an efficient database system is required. Using SQL,
this project builds a simple database for managing grocery store operations. It helps in recording
customer details, product inventory, sales transactions, and generating useful reports such as total
revenue and best-selling products.

Objectives
- To design a database that stores customer, product, and order details.
- To simplify the billing process using SQL queries.
- To maintain stock levels and prevent shortages.
- To analyze sales and identify the most popular products.
- To reduce manual errors and improve efficiency in managing a grocery store.

Entities Used
- Customers → Stores customer information.
- Products → Contains details of available grocery items.
- Orders → Records order transactions placed by customers.
- OrderDetails → Links products with orders and their quantities.

SQL Queries (Sample)

Customers Table
CREATE TABLE Customers ( customer_id INT PRIMARY KEY, name VARCHAR(50), phone
VARCHAR(15), address VARCHAR(100) );

Products Table
CREATE TABLE Products ( product_id INT PRIMARY KEY, product_name VARCHAR(50),
category VARCHAR(30), price DECIMAL(8,2), stock INT );

Orders Table
CREATE TABLE Orders ( order_id INT PRIMARY KEY, customer_id INT, order_date DATE,
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) );

OrderDetails Table
CREATE TABLE OrderDetails ( orderdetail_id INT PRIMARY KEY, order_id INT, product_id
INT, quantity INT, FOREIGN KEY (order_id) REFERENCES Orders(order_id), FOREIGN KEY
(product_id) REFERENCES Products(product_id) );

Sample Queries (One-Liners)

SELECT * FROM Products; -- List all products

SELECT product_name FROM Products WHERE stock < 5; -- Low stock check

SELECT p.product_name, SUM(od.quantity) AS sold FROM OrderDetails od JOIN Products p

ON od.product_id = p.product_id GROUP BY p.product_name ORDER BY sold DESC LIMIT 1;
-- Best-selling product

Uses of the Project

- Helps store owners track inventory easily.
- Generates customer bills faster.
- Prevents product shortages by monitoring stock.
- Identifies profitable and fast-moving products.
- Provides sales insights for decision-making.

Conclusion
This project demonstrates how SQL can be effectively used to manage a grocery store database.
By organizing customer, product, and sales data into structured tables, the store can run more
efficiently. The queries simplify reporting, billing, and stock management, making the system
reliable and useful for daily operations. It can further be enhanced into a complete management
application with a user-friendly interface.