Sql_Project_Online_Retail_Company_DataAnalytics

Data Analytics and Business use cases.

Project Overview: Fictional Online Retail Company

1. Database Design -- Database Name: OnlineRetailDB

2. Tables: -- Customers: Stores customer details. -- Products: Stores product details. -- Orders: Stores order details. -- OrderItems: Stores details of each item in an order. -- Categories: Stores product categories.

3. Insert Sample Data: -- Populate each table with sample data.

4. Write Queries: -- Retrieve data (e.g., customer orders, popular products). -- Perform aggregations (e.g., total sales, average order value). -- Join tables for comprehensive reports. -- Use subqueries and common table expressions (CTEs).

5. Complex Problem solved by Window function (Row_Num,Rank,Dense_Rank).

6. Tool - Sql server.

7. Langauge- TSQL.

8. Query 1: Retrieve all orders for a specific customer. Query 2: Find the total sales for each product. Query 3: Calculate the average order value. Query 4: List the top 5 customers by total spending. Query 5: Retrieve the most popular product category. Query 6: Insert a new product with zero stock. Query 7: List all products that are out of stock. Query 8: Find customers who placed orders in the last 30 days. Query 9: Calculate the total number of orders placed each month. Query 10: Retrieve the details of the most recent order. Query 11: Find the average price of products in each category. Query 12: List customers who have never placed an order. Query 13: Retrieve the total quantity sold for each product. Query 14: Calculate the total revenue generated from each product category. Query 15: Find the highest-priced product in each category. Query 16: Retrieve orders with a total amount greater than a specific value . Query 17: List products along with the number of orders they appear in. Query 18: Find the top 3 most frequently ordered products. Query 19: Calculate the total number of customers from each country. Query 20: Retrieve the list of customers along with their total spending. Query 21: List orders with more than a specified number of items .