Scribd
Upload
32 views1 page

Grocery Store SQL Project

The document outlines a mini project for creating a Grocery Store Database using SQL. It includes steps for creating the database, defining tables for customers, products, orders, and order details, inserting sample data, and executing useful queries. Additionally, it suggests extensions such as adding supplier and payment tables, as well as discounts in the product table.

Uploaded by

swaminikale04
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
32 views1 page

Grocery Store SQL Project

The document outlines a mini project for creating a Grocery Store Database using SQL. It includes steps for creating the database, defining tables for customers, products, orders, and order details, inserting sample data, and executing useful queries. Additionally, it suggests extensions such as adding supplier and payment tables, as well as discounts in the product table.

Uploaded by

swaminikale04
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 1

Mini Project on SQL: Grocery Store Database

1. Create Database
CREATE DATABASE GroceryStore;

2. Create Tables
Customer Table: CREATE TABLE Customer ( customer_id INT PRIMARY KEY, name
VARCHAR(50), phone VARCHAR(15), city VARCHAR(30) ); Product Table: CREATE TABLE
Product ( 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, total_amount
DECIMAL(10,2), FOREIGN KEY (customer_id) REFERENCES Customer(customer_id) );
OrderDetails Table: CREATE TABLE OrderDetails ( orderdetail_id INT PRIMARY KEY,
order_id INT, product_id INT, quantity INT, subtotal DECIMAL(10,2), FOREIGN KEY
(order_id) REFERENCES Orders(order_id), FOREIGN KEY (product_id) REFERENCES
Product(product_id) );

3. Insert Sample Data


Customer: INSERT INTO Customer VALUES (1, 'Amit Sharma', '9876543210', 'Pune'), (2,
'Sneha Patil', '9123456780', 'Mumbai'), (3, 'Rahul Mehta', '9988776655', 'Nashik');
Product: INSERT INTO Product VALUES (101, 'Rice 5kg', 'Grains', 300, 50), (102,
'Milk 1L', 'Dairy', 50, 100), (103, 'Sugar 1kg', 'Grocery', 45, 80), (104, 'Bread',
'Bakery', 30, 40), (105, 'Oil 1L', 'Grocery', 150, 60); Orders: INSERT INTO Orders
VALUES (1001, 1, '2025-09-10', 395), (1002, 2, '2025-09-11', 180), (1003, 3,
'2025-09-12', 200); OrderDetails: INSERT INTO OrderDetails VALUES (1, 1001, 101, 1,
300), (2, 1001, 104, 1, 30), (3, 1001, 102, 1, 65), (4, 1002, 105, 1, 150), (5,
1002, 103, 1, 30), (6, 1003, 101, 1, 200);

4. Useful Queries
1) List all customers: SELECT * FROM Customer; 2) Display all products with price >
100: SELECT product_name, price FROM Product WHERE price > 100; 3) Show total sales
(sum of all orders): SELECT SUM(total_amount) AS Total_Sales FROM Orders; 4) Find
which customer ordered 'Rice 5kg': SELECT c.name, p.product_name FROM Customer c
JOIN Orders o ON c.customer_id = o.customer_id JOIN OrderDetails od ON o.order_id =
od.order_id JOIN Product p ON od.product_id = p.product_id WHERE p.product_name =
'Rice 5kg'; 5) Show all orders with their customer name and total amount: SELECT
o.order_id, c.name AS customer_name, o.order_date, o.total_amount FROM Orders o JOIN
Customer c ON o.customer_id = c.customer_id;

5. Extensions
- Add Supplier table (who supplies groceries). - Add Payment table (payment mode:
Cash/UPI/Card). - Add Discounts/Offers column in product table.

You might also like