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!