File size: 2,118 Bytes
b3daa58 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 | -- 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;
|