04_data_exploration.sql

USE business_performance;
SELECT 'Stores' AS table_name, COUNT(*) AS record_count
FROM stores

UNION ALL

SELECT 'Employees', COUNT(*)
FROM employees

UNION ALL

SELECT 'Products', COUNT(*)
FROM products

UNION ALL

SELECT 'Customers', COUNT(*)
FROM customers

UNION ALL

SELECT 'Targets', COUNT(*)
FROM targets

UNION ALL

SELECT 'Sales', COUNT(*)
FROM sales;

SELECT
    SUM(sale_id IS NULL) AS missing_sale_id,
    SUM(sale_date IS NULL) AS missing_sale_date,
    SUM(store_id IS NULL) AS missing_store_id,
    SUM(employee_id IS NULL) AS missing_employee_id,
    SUM(customer_id IS NULL) AS missing_customer_id,
    SUM(product_id IS NULL) AS missing_product_id,
    SUM(quantity IS NULL) AS missing_quantity,
    SUM(discount IS NULL) AS missing_discount
FROM sales;
SELECT
    sale_id,
    COUNT(*) AS duplicate_count
FROM sales
GROUP BY sale_id
HAVING COUNT(*) > 1;
SELECT
    MIN(sale_date) AS first_sale,
    MAX(sale_date) AS last_sale
FROM sales;
SELECT *
FROM sales
WHERE quantity <= 0;
SELECT
    MIN(discount) AS minimum_discount,
    MAX(discount) AS maximum_discount
FROM sales;
SELECT e.employee_id
FROM employees e
LEFT JOIN stores s
    ON e.store_id = s.store_id
WHERE s.store_id IS NULL;
SELECT s.sale_id
FROM sales s
LEFT JOIN products p
    ON s.product_id = p.product_id
WHERE p.product_id IS NULL;
SELECT s.sale_id
FROM sales s
LEFT JOIN customers c
    ON s.customer_id = c.customer_id
WHERE c.customer_id IS NULL;
SELECT s.sale_id
FROM sales s
LEFT JOIN employees e
    ON s.employee_id = e.employee_id
WHERE e.employee_id IS NULL;