BCSL-034 Solved Free Assignment 2024-25 Sem 3
Q1. Design and implement a simple database using MS-Access for an Online Retail Store, using fundamental concepts of database management systems such as creating tables, establishing relationships, performing CRUD (Create, Read, Update, Delete) operations, and writing basic SQL queries. Create a database schema for an online retail store and implement various operations on it. The database will manage information about customers, products, orders, and order details. Use SQL to create tables, establish relationships, and perform queries to manipulate and retrieve data.
I. Create Database Schema:
Customers Table: customer_id (Primary Key, INT, Auto Increment)
first_name (VARCHAR)
last_name (VARCHAR)
email (VARCHAR, Unique)
phone (VARCHAR)
address (VARCHAR)
Products Table: product_id (Primary Key, INT, Auto Increment)
product_name (VARCHAR)
description (TEXT) price (DECIMAL)
stock_quantity (INT)
Orders Table: order_id (Primary Key, INT, Auto Increment)
customer_id (Foreign Key, INT)
order_date (DATE) status (VARCHAR)
OrderDetails Table: order_detail_id (Primary Key, INT, Auto Increment)
order_id (Foreign Key, INT)
product_id (Foreign Key, INT) quantity (INT)
total_price (DECIMAL)
II. Relationships:
Each order is placed by one customer.
Each order can have multiple products.
Each product can be part of multiple orders.
Draw an ER-diagram for this application.
III. Operations:
CRUD (Create, Read, Update, Delete) Operations
Insert new records into each table.
Read/display records from each table.
Update existing records in each table.
Delete records from each table.
IV. Write and execute the following SQL Queries:
1. Retrieve all orders along with the customer details who placed the order.
2. Find all products that have been ordered by a specific customer.
3. Retrieve the total sales for each product.
4. Find all customers who have placed at least one order.
5. Retrieve the total quantity of products ordered by each customer.
6. Find all orders and their order details for a specific customer.
7. Retrieve all products along with the total quantity ordered.
8. Find the total revenue generated from orders placed within a specific date range.
9. Retrieve all customers who have ordered a specific product.
10. Find the most frequently ordered product.
11. Retrieve the average order value for each customer.
12. Find all products that have never been ordered.
13. Retrieve the total number of orders placed each month.
14. Retrieve the total number of products ordered in each order.
15. Find the top 5 customers based on total spending.
16. Retrieve all orders placed on a specific date.
17. Find the total number of unique products ordered by each customer.
18. Retrieve the order details for the order with the highest total price.
19. Find the top 3 products based on the total quantity ordered.
20. Retrieve the total sales for each month.
21. Find the customer who has placed the most orders.
22. Retrieve the total number of orders for each status.
23. Find the order with the highest quantity of a single product.
24. Retrieve the customer details for the order with the highest total price.
25. Find the average quantity of products ordered per order.
Note: You must perform the above said activities and also take prints of screenshots of the layouts, sample input and output along with the necessary documentation for this practical question. Assumptions can be made wherever necessary
Ans:- Here’s a comprehensive guide to designing and implementing a simple database for an Online Retail Store using **MS Access**, including creating tables, establishing relationships, performing CRUD operations, and executing SQL queries.
I. Create Database Schema
1. Create Tables
You will create four tables: **Customers**, **Products**, **Orders**, and **OrderDetails**.
**Customers Table**
- `customer_id`: Auto Increment, Primary Key (INT)
- `first_name`: VARCHAR
- `last_name`: VARCHAR
- `email`: VARCHAR, Unique
- `phone`: VARCHAR
- `address`: VARCHAR
**Products Table**
- `product_id`: Auto Increment, Primary Key (INT)
- `product_name`: VARCHAR
- `description`: TEXT
- `price`: DECIMAL
- `stock_quantity`: INT
**Orders Table**
- `order_id`: Auto Increment, Primary Key (INT)
- `customer_id`: Foreign Key referencing `Customers(customer_id)` (INT)
- `order_date`: DATE
- `status`: VARCHAR
**OrderDetails Table**
- `order_detail_id`: Auto Increment, Primary Key (INT)
- `order_id`: Foreign Key referencing `Orders(order_id)` (INT)
- `product_id`: Foreign Key referencing `Products(product_id)` (INT)
- `quantity`: INT
- `total_price`: DECIMAL
2. Establish Relationships
- **Customers to Orders**: One-to-Many
- **Orders to OrderDetails**: One-to-Many
- **Products to OrderDetails**: One-to-Many
### ER Diagram
Here's a textual representation of the ER diagram. You can use an ER diagram tool or draw it out:
```
[Customers]
|
| 1:N
|
[Orders]
|
| 1:N
|
[OrderDetails] ------ N:1 ------ [Products]
```
II. Operations
1. CRUD Operations
You can perform the following operations in **MS Access** using its GUI or SQL queries.
**A. Create (Insert New Records)**
```sql
-- Insert into Customers
INSERT INTO Customers (first_name, last_name, email, phone, address)
VALUES ('John', 'Doe', 'john.doe@example.com', '1234567890', '123 Main St');
-- Insert into Products
INSERT INTO Products (product_name, description, price, stock_quantity)
VALUES ('Laptop', 'Gaming Laptop', 1200.00, 50);
-- Insert into Orders
INSERT INTO Orders (customer_id, order_date, status)
VALUES (1, '2024-10-01', 'Pending');
-- Insert into OrderDetails
INSERT INTO OrderDetails (order_id, product_id, quantity, total_price)
VALUES (1, 1, 2, 2400.00);
```
**B. Read (Display Records)**
```sql
-- Retrieve all customers
SELECT * FROM Customers;
-- Retrieve all products
SELECT * FROM Products;
-- Retrieve all orders
SELECT * FROM Orders;
-- Retrieve all order details
SELECT * FROM OrderDetails;
```
**C. Update (Modify Existing Records)**
```sql
-- Update customer phone
UPDATE Customers
SET phone = '0987654321'
WHERE customer_id = 1;
-- Update product price
UPDATE Products
SET price = 1150.00
WHERE product_id = 1;
```
**D. Delete (Remove Records)**
```sql
-- Delete a customer
DELETE FROM Customers
WHERE customer_id = 1;
-- Delete a product
DELETE FROM Products
WHERE product_id = 1;
```
III. SQL Queries
Here are the SQL queries for your operations:
1. **Retrieve all orders along with the customer details who placed the order.**
```sql
SELECT Orders.order_id, Customers.first_name, Customers.last_name, Orders.order_date, Orders.status
FROM Orders
JOIN Customers ON Orders.customer_id = Customers.customer_id;
```
2. **Find all products that have been ordered by a specific customer.**
```sql
SELECT Products.product_name
FROM Products
JOIN OrderDetails ON Products.product_id = OrderDetails.product_id
JOIN Orders ON OrderDetails.order_id = Orders.order_id
WHERE Orders.customer_id = 1; -- Change to specific customer_id
```
3. **Retrieve the total sales for each product.**
```sql
SELECT Products.product_name, SUM(OrderDetails.total_price) AS total_sales
FROM OrderDetails
JOIN Products ON OrderDetails.product_id = Products.product_id
GROUP BY Products.product_name;
```
4. **Find all customers who have placed at least one order.**
```sql
SELECT DISTINCT Customers.first_name, Customers.last_name
FROM Customers
JOIN Orders ON Customers.customer_id = Orders.customer_id;
```
5. **Retrieve the total quantity of products ordered by each customer.**
```sql
SELECT Customers.first_name, Customers.last_name, SUM(OrderDetails.quantity) AS total_quantity
FROM Customers
JOIN Orders ON Customers.customer_id = Orders.customer_id
JOIN OrderDetails ON Orders.order_id = OrderDetails.order_id
GROUP BY Customers.first_name, Customers.last_name;
```
6. **Find all orders and their order details for a specific customer.**
```sql
SELECT Orders.order_id, OrderDetails.product_id, OrderDetails.quantity, OrderDetails.total_price
FROM Orders
JOIN OrderDetails ON Orders.order_id = OrderDetails.order_id
WHERE Orders.customer_id = 1; -- Change to specific customer_id
```
7. **Retrieve all products along with the total quantity ordered.**
```sql
SELECT Products.product_name, SUM(OrderDetails.quantity) AS total_quantity
FROM Products
JOIN OrderDetails ON Products.product_id = OrderDetails.product_id
GROUP BY Products.product_name;
```
8. **Find the total revenue generated from orders placed within a specific date range.**
```sql
SELECT SUM(OrderDetails.total_price) AS total_revenue
FROM Orders
JOIN OrderDetails ON Orders.order_id = OrderDetails.order_id
WHERE Orders.order_date BETWEEN '2024-10-01' AND '2024-10-31';
```
9. **Retrieve all customers who have ordered a specific product.**
```sql
SELECT DISTINCT Customers.first_name, Customers.last_name
FROM Customers
JOIN Orders ON Customers.customer_id = Orders.customer_id
JOIN OrderDetails ON Orders.order_id = OrderDetails.order_id
WHERE OrderDetails.product_id = 1; -- Change to specific product_id
```
10. **Find the most frequently ordered product.**
```sql
SELECT Products.product_name, SUM(OrderDetails.quantity) AS total_ordered
FROM Products
JOIN OrderDetails ON Products.product_id = OrderDetails.product_id
GROUP BY Products.product_name
ORDER BY total_ordered DESC
LIMIT 1;
```
11. **Retrieve the average order value for each customer.**
```sql
SELECT Customers.first_name, Customers.last_name, AVG(OrderDetails.total_price) AS avg_order_value
FROM Customers
JOIN Orders ON Customers.customer_id = Orders.customer_id
JOIN OrderDetails ON Orders.order_id = OrderDetails.order_id
GROUP BY Customers.first_name, Customers.last_name;
```
12. **Find all products that have never been ordered.**
```sql
SELECT Products.product_name
FROM Products
LEFT JOIN OrderDetails ON Products.product_id = OrderDetails.product_id
WHERE OrderDetails.product_id IS NULL;
```
13. **Retrieve the total number of orders placed each month.**
```sql
SELECT FORMAT(order_date, 'yyyy-mm') AS order_month, COUNT(order_id) AS total_orders
FROM Orders
GROUP BY FORMAT(order_date, 'yyyy-mm');
```
14. **Retrieve the total number of products ordered in each order.**
```sql
SELECT Orders.order_id, SUM(OrderDetails.quantity) AS total_products_ordered
FROM Orders
JOIN OrderDetails ON Orders.order_id = OrderDetails.order_id
GROUP BY Orders.order_id;
```
15. **Find the top 5 customers based on total spending.**
```sql
SELECT Customers.first_name, Customers.last_name, SUM(OrderDetails.total_price) AS total_spending
FROM Customers
JOIN Orders ON Customers.customer_id = Orders.customer_id
JOIN OrderDetails ON Orders.order_id = OrderDetails.order_id
GROUP BY Customers.first_name, Customers.last_name
ORDER BY total_spending DESC
LIMIT 5;
```
16. **Retrieve all orders placed on a specific date.**
```sql
SELECT * FROM Orders
WHERE order_date = '2024-10-01'; -- Change to specific date
```
17. **Find the total number of unique products ordered by each customer.**
```sql
SELECT Customers.first_name, Customers.last_name, COUNT(DISTINCT OrderDetails.product_id) AS unique_products_ordered
FROM Customers
JOIN Orders ON Customers.customer_id = Orders.customer_id
JOIN OrderDetails ON Orders.order_id = OrderDetails.order_id
GROUP BY Customers.first_name, Customers.last_name;
```
18. **Retrieve the order details for the order with the highest total price.**
```sql
SELECT * FROM OrderDetails
WHERE order_detail_id =
(SELECT TOP 1 order_detail_id
FROM OrderDetails
ORDER BY total_price DESC);
```
19. **Find the top 3 products based on the total quantity ordered.**
```sql
SELECT Products.product_name, SUM(OrderDetails.quantity) AS total_quantity_ordered
FROM Products
JOIN OrderDetails ON Products.product_id = OrderDetails.product_id
GROUP BY Products.product_name
ORDER BY total_quantity_ordered DESC
LIMIT 3;
```
20. **Retrieve the total sales for each month.**
```sql
SELECT FORMAT(order_date, 'yyyy-mm') AS order_month, SUM(OrderDetails.total_price) AS total_sales
FROM Orders
JOIN OrderDetails ON Orders.order_id = OrderDetails.order_id
GROUP BY FORMAT(order_date, 'yyyy-mm');
```
21. **Find the customer who has placed the most orders.**
```sql
SELECT Customers.first_name, Customers.last_name, COUNT(Orders.order_id) AS total_orders
FROM Customers
JOIN Orders ON Customers.customer_id = Orders.customer_id
GROUP BY Customers.first_name, Customers.last_name
ORDER BY total_orders DESC
LIMIT 1;
```
22. **Retrieve the total number of orders for each status.**
```sql
SELECT status, COUNT(order_id) AS total_orders
FROM Orders
GROUP BY status;
```
23. **Find the order with the highest quantity of a single product.**
```sql
SELECT OrderDetails.order_id, MAX(OrderDetails.quantity) AS max_quantity
FROM OrderDetails
GROUP BY OrderDetails.order_id
ORDER BY max_quantity DESC
LIMIT 1;
```
24. **Retrieve the customer details for the order with the highest total price.**
```sql
SELECT Customers.*
FROM Customers
JOIN Orders ON Customers.customer_id = Orders.customer_id
JOIN OrderDetails ON Orders.order_id = OrderDetails.order_id
WHERE OrderDetails.total_price =
(SELECT MAX(total_price)
FROM OrderDetails);
```
25. **Find the average quantity of products ordered per order.**
```sql
SELECT AVG(total_quantity) AS avg_quantity
FROM (SELECT SUM(OrderDetails.quantity) AS total_quantity
FROM Orders
JOIN OrderDetails ON Orders.order_id = OrderDetails.order_id
GROUP BY Orders.order_id) AS subquery;
```
Note
- Perform each of these operations in **MS Access** and take screenshots of layouts, sample input, and output as required.
- Ensure to make any necessary assumptions clear in your documentation.
If you have specific questions about any of the steps, feel free to ask!
No comments: