This document contains advanced SQL queries for managing and analyzing production systems, addressing machine performance, quality issues, inventory management, and operational efficiency.
Description: Retrieve production logs for machine ID M001, ordered by production date.
SELECT production_id, machine_id, production_date, units_produced
FROM production_logs
WHERE machine_id = 'M001'
ORDER BY production_date DESC;Description: Calculate the total units produced by each machine, sorted by production volume.
SELECT machine_id, SUM(units_produced) AS total_units
FROM production_logs
GROUP BY machine_id
ORDER BY total_units DESC;Description: Identify machines with total downtime exceeding 10 hours.
SELECT machine_id, SUM(downtime_minutes) AS total_downtime
FROM downtime_logs
GROUP BY machine_id
HAVING SUM(downtime_minutes) > 600; -- 10 hours = 600 minutesDescription: Summarize defective units for each product type.
SELECT product_type, SUM(defective_units) AS total_defective_units
FROM quality_checks
GROUP BY product_type;Description: List machines that have not been used in the past 30 days.
SELECT machine_id
FROM machines
WHERE last_used_date < DATEADD(DAY, -30, GETDATE());Description: Compute the average cycle time for each machine.
SELECT machine_id, AVG(cycle_time) AS avg_cycle_time
FROM production_logs
GROUP BY machine_id;Description: Identify the top 5 products with the highest sales revenue.
SELECT product_id, SUM(sales_amount) AS total_sales
FROM sales_data
GROUP BY product_id
ORDER BY total_sales DESC
LIMIT 5;Description: Find products with more than 10 defective batches and a defect rate above 5%.
SELECT product_id, COUNT(*) AS defective_batches
FROM quality_checks
WHERE defect_rate > 5
GROUP BY product_id
HAVING COUNT(*) > 10;Description: Compute the inventory turnover ratio for each product.
SELECT product_id, (SUM(sales_quantity) / AVG(stock_quantity)) AS inventory_turnover
FROM inventory
GROUP BY product_id;Description: List orders where the actual delivery date exceeded the scheduled delivery date.
SELECT order_id, scheduled_delivery_date, actual_delivery_date
FROM orders
WHERE actual_delivery_date > scheduled_delivery_date;Description: Identify machines with an average daily production below 100 units.
SELECT machine_id, AVG(units_produced) AS avg_daily_units
FROM production_logs
GROUP BY machine_id
HAVING AVG(units_produced) < 100;Description: Find the date with the highest total production output.
SELECT production_date, SUM(units_produced) AS total_output
FROM production_logs
GROUP BY production_date
ORDER BY total_output DESC
LIMIT 1;Description: Determine the scrap percentage for each product.
SELECT product_id,
(SUM(scrap_units) * 100.0) / SUM(total_units) AS scrap_percentage
FROM production_logs
GROUP BY product_id;Description: List the top 5 most frequently replaced parts.
SELECT part_id, COUNT(*) AS replacement_count
FROM maintenance_logs
GROUP BY part_id
ORDER BY replacement_count DESC
LIMIT 5;Description: Compute the average production time for each product.
SELECT product_id, AVG(production_time) AS avg_production_time
FROM production_logs
GROUP BY product_id;Description: Find products with stock levels below their reorder point.
SELECT product_id, stock_quantity, reorder_level
FROM inventory
WHERE stock_quantity < reorder_level;Description: Calculate total sales revenue for each region.
SELECT region, SUM(sales_amount) AS total_revenue
FROM sales_data
GROUP BY region
ORDER BY total_revenue DESC;Description: Identify instances of unscheduled machine downtime.
SELECT machine_id, downtime_minutes, scheduled_maintenance
FROM downtime_logs
WHERE scheduled_maintenance = 'No';Description: Analyze monthly production trends over time.
SELECT MONTH(production_date) AS production_month,
SUM(units_produced) AS total_production
FROM production_logs
GROUP BY MONTH(production_date)
ORDER BY production_month;Description: List products with no defects reported in quality checks.
SELECT product_id
FROM quality_checks
WHERE defect_rate = 0;