Microsoft SQL Server

SQL Assignment:

As part of this SQL assignment, you are required to design SQL queries that will extract the necessary data from a relational database to generate insights for a Store Management System Dashboard. The dashboard will focus on key areas such as products, customers, orders, inventory, and employee performance.

1. Product Overview

• Task: Write SQL queries to generate the following metrics:
  1. Total Products in Stock: Write a query to count the total number of products currently available across all stores.
  2. Products by Category: Write a query to count the number of products for each category (for a bar or pie chart).
  3. Low Stock Products: Write a query to list products that have a stock quantity below a certain threshold (e.g., less than 10 units).
  4. Recently Added Products: Write a query to list products added in the last 30 days.

2. Customer Overview

• Task: Write SQL queries to pull customer-related insights:
  1. Total Number of Customers: Write a query to count the total number of customers in the system.
  2. Active Customers: Write a query to retrieve customers who have placed orders in the last 6 months.
  3. Customer Purchase History: Write a query that retrieves all orders made by each customer, showing total amounts and order dates (for a table).
  4. Customer Registration: Write a query to list all customers who registered in the last month.

3. Order Overview

• Task: Use SQL to pull order-related insights:
  1. Total Orders by Month: Write a query that retrieves the number of orders placed each month.
  2. High-Value Orders: Write a query that lists all orders above a certain total amount (e.g., greater than 500 USD).
  3. Orders by Product: Write a query that retrieves the total quantity ordered for each product (for a bar chart).
  4. Pending Orders: Write a query that lists all orders that have not been fulfilled yet.

4. Employee Overview

• Task: Write SQL queries to manage employee-related data:
  1. Number of Employees per Store: Write a query to retrieve the number of employees working at each store.
  2. Employee Salary Report: Write a query to retrieve the salary details for all employees.

5. Inventory Overview

• Task: Write SQL queries to pull inventory data across stores:
  1. Inventory by Store: Write a query that retrieves the total quantity of products available at each store.
  2. Out-of-Stock Products: Write a query to list products that are out of stock at any store.
  3. Inventory by Supplier: Write a query that lists the quantity of products supplied by each supplier to all stores.

Key Visuals and Corresponding SQL Queries:

Note: All dashboard queries must work against current month or current year filters

1. Pie Chart - Products by Category

   • Query: Write an SQL query to retrieve the number of products for each category from the `PRODUCT` table.
   • Sections: Product categories with the number of products as values.

2. Bar Chart - Orders by Month

   • Query: Write a query that retrieves the number of orders placed in each month from the `ORDER` table, grouped by order date (by month).
   • X-Axis: Order date (by month).
   • Y-Axis: Number of orders.

3. Multi Line Graph - Sales vs Collection Over Month

   • Query: Write a query to compare total sales amounts (from the `ORDER` table) and collections (from the `PAYMENT` table) for each month.
   • X-Axis: Month (grouped by order_date and payment_date).
   • Y-Axis: Total sales and collection amounts. Two lines in the graph, one for sales and other for collections.
   • Details: Sales data comes from the `total_amount` field in the `ORDER` table, while collection data comes from the `amount_paid` field in the `PAYMENT` table.

4. Table - Customer Purchase History

   • Query: Write an SQL query to retrieve order details from the `ORDER` and `CUSTOMER` tables, showing customer names, order dates, and total amounts.
   • Columns: Customer Name, Order Date, Total Amount.

5. Gauge - Total Store Inventory

   • Query: Write an SQL query to calculate the total quantity of products in stock across all stores from the `INVENTORY` table.
   • Display: Total quantity of products available.

GOOD LUCK WITH YOUR ASSIGNMENT!!!

Don't forget to contact us if you need any further assistance with your assignments, and most importantly, for a manual review and approval of your work.

Sample ERD and Data Model for Dashboard Reporting Logic in a Store Management System