Project Overview

This project analyses warehousing operations and order management using MySQL. By leveraging techniques such as JOINs, functions, logical operators, and subqueries, we uncover insights about warehouse performance, order trends, and employee statistics.

Objectives

Dataset Description

Warehousewarehouse Table
Column Name Description
warehouse_id Unique identifier for each warehouse.
warehouse_alias Alias or name of the warehouse.
maximum_capacity Maximum storage capacity of the warehouse.
employee_total Total number of employees.
state Location of the warehouse.
Warehouseorders Table
Column Name Description
order_id Unique identifier for each order.
customer_id Unique identifier for the customer.
warehouse_id ID linking the order to its warehouse.
order_date Date the order was placed.
shipper_date Date the order was shipped.

Starting with MySQL Workbench

To get started, download MySQL DBMS from the official MySQL website: Download MySQL.

Follow the on-screen instructions, choosing the appropriate custom settings for your SQL Server and Workbench. Remember to set a secure password during the setup.

Starting MySQL Workbench

  1. Open MySQL Workbench: Launch MySQL Workbench and log in using the password you set during installation.

  2. Connect to the Server: Ensure you’re connected to the server using the Navigator. For more information on using the Navigator, refer to the MySQL Workbench Navigator Documentation.

  3. Import Datasets: Import the warehousewarehouse and warehouseorders datasets into MySQL Workbench. You can find the detailed steps in the Import and Export Management Guide.

  4. Load the Datasets: Execute the following SQL queries to load and view your datasets:

SELECT *
FROM warehousewarehouse;

SHOW COLUMNS 
FROM warehousewarehouse;
SHOW COLUMNS 
FROM warehouseorders;

SELECT *
FROM warehouseorders;

Key SQL Queries and Their Context

1. Total Orders Per Warehouse

SELECT
    WW.warehouse_id,
    WW.warehouse_alias,
    COUNT(WO.order_id) `Total Orders`
FROM warehousewarehouse WW
LEFT JOIN warehouseorders WO
ON WW.warehouse_id = WO.warehouse_id
GROUP BY 
    WW.warehouse_id,
    WW.warehouse_alias;
  • Objective: Count the number of orders handled by each warehouse.
  • Logic: A LEFT JOIN ensures that all warehouses, including those without orders, are included in the results. The COUNT() function aggregates the order totals.

2. Employee and Order Summary for Each Warehouse

SELECT 
    WW.warehouse_id,
    WW.warehouse_alias,
    WW.employee_total  `Employees`,
    COUNT(WO.order_id) `Orders`
FROM warehousewarehouse WW
LEFT JOIN warehouseorders WO
ON WW.warehouse_id = WO.warehouse_id
GROUP BY  
    WW.warehouse_id,
    WW.warehouse_alias,
    WW.employee_total;
  • Objective: Provide an overview of employee counts and order totals for each warehouse.
  • Logic: The query combines data using a LEFT JOIN and groups results by warehouse.

3. Recent Order Date Per Warehouse

SELECT 
    WW.warehouse_id, 
    WW.warehouse_alias, 
    MAX(WO.order_date) `Recent Order Date`
FROM warehousewarehouse WW 
LEFT JOIN warehouseorders WO 
ON WW.warehouse_id = WO.warehouse_id
GROUP BY 
    WW.warehouse_id, 
    WW.warehouse_alias;
  • Objective: Identify the most recent order date for each warehouse.
  • Logic: The MAX() function finds the latest order_date, and the LEFT JOIN ensures all warehouses are included.

4. Warehouse with Maximum Capacity

SELECT 
    warehouse_alias,
    maximum_capacity
FROM warehousewarehouse
WHERE maximum_capacity = (
    SELECT MAX(maximum_capacity) 
    FROM warehousewarehouse);
  • Objective: Retrieve the warehouse with the largest storage capacity.
  • Logic: A subquery identifies the maximum capacity, and the main query matches this value.

5. Orders Shipped Same Day as Ordered

SELECT 
    order_id,
    customer_id,
    warehouse_id,
    order_date,
    shipper_date
FROM warehouseorders 
WHERE order_date = shipper_date;
  • Objective: Identify orders processed and shipped on the same day.
  • Logic: The query uses the = operator to match order_date with shipper_date.

6. Top 3 Warehouses by Order Count

SELECT
    WW.warehouse_id, 
    WW.warehouse_alias, 
    COUNT(WO.order_id) `Order Count`
FROM warehousewarehouse WW
JOIN warehouseorders WO
ON WW.warehouse_id = WO.warehouse_id
GROUP BY
    WW.warehouse_id, 
    WW.warehouse_alias
ORDER BY `Order Count` DESC
LIMIT 3;
  • Objective: Identify the top three performing warehouses.
  • Logic: A JOIN combines data, COUNT() tallies the orders, and LIMIT restricts results to three.

How to Use

1. Set Up Database:

  • Import the dataset into MySQL.
  • Create the necessary tables (Warehousewarehouse and Warehouseorders).
  • Populate them with the sample data.

2. Run Queries:

  • Use the provided SQL queries to explore the dataset and generate insights.

3. Customise Queries:

  • Modify filters (e.g., date ranges or thresholds) to fit different scenarios.

Deliverables

Contact If you have questions or suggestions, feel free to reach out to [ or ].