-- Simple Supply Chain SQL Queries
USE supply_chain_db;

-- 1. View Sample Data from Tables
-- Retrieve the first 5 records from the customers table
SELECT * FROM customers LIMIT 5;

-- Retrieve the first 5 records from the products table
SELECT * FROM products LIMIT 5;

-- Retrieve the first 5 records from the orders table
SELECT * FROM orders LIMIT 5;


-- 2. Count Records in Each Table
-- Check the total number of customer accounts
SELECT COUNT(*) AS total_customers FROM customers;

-- Check the total number of products
SELECT COUNT(*) AS total_products FROM products;

-- Check the total number of orders placed
SELECT COUNT(*) AS total_orders FROM orders;


-- 3. Simple Filtering (WHERE Clause)
-- Find 10 orders that were delayed (late_delivery_risk = 1)
SELECT order_id, order_date, shipping_mode, late_delivery_risk 
FROM orders 
WHERE late_delivery_risk = 1 
LIMIT 10;

-- Find products that cost more than $100
SELECT product_card_id, product_name, product_price 
FROM products 
WHERE product_price > 100.00
ORDER BY product_price ASC;


-- 4. Simple Aggregations (SUM, AVG, MIN, MAX)
-- Calculate the total gross sales and net profit from all order items
SELECT 
    SUM(sales) AS total_gross_sales, 
    SUM(benefit_per_order) AS total_profit 
FROM order_items;

-- Find the average price of all products
SELECT AVG(product_price) AS average_product_price FROM products;

-- Find the minimum and maximum shipping times scheduled
SELECT 
    MIN(days_for_shipping_scheduled) AS min_scheduled_days, 
    MAX(days_for_shipping_scheduled) AS max_scheduled_days 
FROM orders;


-- 5. Simple Grouping (GROUP BY)
-- Count the number of customers in each customer segment (Consumer, Corporate, etc.)
SELECT customer_segment, COUNT(*) AS customer_count
FROM customers
GROUP BY customer_segment;

-- Count orders by shipping mode (Standard, Second Class, etc.)
SELECT shipping_mode, COUNT(*) AS order_count
FROM orders
GROUP BY shipping_mode;


-- 6. Simple Ordering (ORDER BY)
-- List the top 5 most expensive products
SELECT product_name, product_price 
FROM products 
ORDER BY product_price DESC 
LIMIT 5;