SELECT
    title,
    price,
    CASE
        WHEN price < 14 THEN 'Budget'
        WHEN price < 17 THEN 'Standard'
        ELSE 'Premium'
    END AS price_tier
FROM books;

SELECT
    order_id,
    status,
    CASE status
        WHEN 'pending' THEN 'Awaiting Processing'
        WHEN 'shipped' THEN 'On Its Way'
        WHEN 'completed' THEN 'Delivered'
        WHEN 'cancelled' THEN 'Cancelled'
        ELSE 'Unknown'
    END AS status_description
FROM orders;

SELECT
    title,
    CASE
        WHEN price > 17 THEN 'Expensive'
        WHEN price > 14 THEN 'Moderate'
        -- No ELSE: books under $14 get NULL
    END AS label
FROM books;

SELECT
    title,
    stock_quantity,
    CASE
        WHEN stock_quantity = 0 THEN 'Out of Stock'
        WHEN stock_quantity < 10 THEN 'Critical'
        WHEN stock_quantity < 25 THEN 'Low'
        WHEN stock_quantity < 40 THEN 'Normal'
        ELSE 'Well Stocked'
    END AS stock_status
FROM books;

-- Custom sort order
SELECT title, status FROM orders
ORDER BY CASE status
    WHEN 'pending' THEN 1
    WHEN 'shipped' THEN 2
    WHEN 'completed' THEN 3
    WHEN 'cancelled' THEN 4
    ELSE 5
END;

-- Dynamic filtering based on a parameter
SET @include_expensive = TRUE;
 
SELECT * FROM books
WHERE
    CASE
        WHEN @include_expensive THEN TRUE
        ELSE price <= 15
    END;

SELECT
    CASE
        WHEN price < 15 THEN 'Under $15'
        WHEN price < 18 THEN '$15-$18'
        ELSE 'Over $18'
    END AS price_range,
    COUNT(*) AS book_count,
    AVG(price) AS avg_price
FROM books
GROUP BY CASE
    WHEN price < 15 THEN 'Under $15'
    WHEN price < 18 THEN '$15-$18'
    ELSE 'Over $18'
END;

-- Bulk price adjustment by category
UPDATE books
SET price = CASE
    WHEN stock_quantity > 40 THEN price * 0.9   -- Discount overstocked
    WHEN stock_quantity < 10 THEN price * 1.1   -- Premium for low stock
    ELSE price
END;

SELECT
    title,
    price,
    stock_quantity,
    CASE
        WHEN stock_quantity = 0 THEN 'Unavailable'
        ELSE CASE
            WHEN price > 17 THEN 'Premium - In Stock'
            WHEN price > 14 THEN 'Standard - In Stock'
            ELSE 'Budget - In Stock'
        END
    END AS availability
FROM books;

SELECT
    first_name,
    COALESCE(phone, email, 'No contact') AS primary_contact
FROM customers;

-- Default value for NULL
SELECT COALESCE(discount_percent, 0) AS discount FROM products;
 
-- Fallback chain
SELECT COALESCE(nickname, first_name, email) AS display_name FROM users;
 
-- Division with NULL protection
SELECT total / COALESCE(NULLIF(count, 0), 1) AS average FROM stats;

SELECT NULLIF(value, 0);  -- Returns NULL if value is 0

-- Without NULLIF: error if quantity is 0
SELECT total / quantity AS unit_price FROM order_items;
 
-- With NULLIF: returns NULL instead of error
SELECT total / NULLIF(quantity, 0) AS unit_price FROM order_items;

SELECT
    GREATEST(10, 20, 15),  -- 20
    LEAST(10, 20, 15);     -- 10
 
-- Practical: ensure minimum/maximum values
SELECT
    product_name,
    GREATEST(price, minimum_price) AS actual_price,
    LEAST(discount, max_discount) AS applied_discount
FROM products;

-- SQL Server IIF
SELECT IIF(price > 15, 'Expensive', 'Affordable') AS label FROM books;
 
-- MySQL IF
SELECT IF(price > 15, 'Expensive', 'Affordable') AS label FROM books;
 
-- Standard equivalent
SELECT CASE WHEN price > 15 THEN 'Expensive' ELSE 'Affordable' END FROM books;

-- Count orders by status in columns
SELECT
    customer_id,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
    SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending,
    SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders
GROUP BY customer_id;

-- Revenue breakdown by price tier
SELECT
    SUM(CASE WHEN price < 15 THEN quantity * unit_price ELSE 0 END) AS budget_revenue,
    SUM(CASE WHEN price BETWEEN 15 AND 17 THEN quantity * unit_price ELSE 0 END) AS standard_revenue,
    SUM(CASE WHEN price > 17 THEN quantity * unit_price ELSE 0 END) AS premium_revenue
FROM order_items oi
JOIN books b ON oi.book_id = b.book_id;

-- Monthly sales pivot
SELECT
    author_id,
    SUM(CASE WHEN MONTH(o.order_date) = 1 THEN oi.quantity ELSE 0 END) AS jan,
    SUM(CASE WHEN MONTH(o.order_date) = 2 THEN oi.quantity ELSE 0 END) AS feb,
    SUM(CASE WHEN MONTH(o.order_date) = 3 THEN oi.quantity ELSE 0 END) AS mar
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
JOIN books b ON oi.book_id = b.book_id
GROUP BY author_id;

SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    COALESCE(SUM(o.total_amount), 0) AS total_spent,
    COUNT(o.order_id) AS order_count,
    CASE
        WHEN COALESCE(SUM(o.total_amount), 0) >= 100 THEN 'VIP'
        WHEN COALESCE(SUM(o.total_amount), 0) >= 50 THEN 'Regular'
        WHEN COUNT(o.order_id) > 0 THEN 'New'
        ELSE 'Prospect'
    END AS customer_tier
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name;

SELECT
    title,
    price AS original_price,
    CASE
        WHEN stock_quantity > 40 THEN price * 0.80  -- 20% off
        WHEN stock_quantity > 30 THEN price * 0.90  -- 10% off
        WHEN publication_year < 1950 THEN price * 0.85  -- Classic discount
        ELSE price
    END AS sale_price,
    CASE
        WHEN stock_quantity > 40 THEN '20% OFF - Clearance'
        WHEN stock_quantity > 30 THEN '10% OFF'
        WHEN publication_year < 1950 THEN '15% OFF - Classic'
        ELSE NULL
    END AS promotion
FROM books;

SELECT
    'customers' AS table_name,
    COUNT(*) AS total_rows,
    SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) AS missing_email,
    SUM(CASE WHEN phone IS NULL THEN 1 ELSE 0 END) AS missing_phone,
    ROUND(100.0 * SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) / COUNT(*), 2) AS pct_missing_email
FROM customers
 
UNION ALL
 
SELECT
    'authors' AS table_name,
    COUNT(*) AS total_rows,
    SUM(CASE WHEN birth_date IS NULL THEN 1 ELSE 0 END) AS missing_birthdate,
    SUM(CASE WHEN nationality IS NULL THEN 1 ELSE 0 END) AS missing_nationality,
    ROUND(100.0 * SUM(CASE WHEN birth_date IS NULL THEN 1 ELSE 0 END) / COUNT(*), 2) AS pct_missing
FROM authors;

SELECT
    o.order_id,
    o.order_date,
    o.status,
    CASE
        WHEN o.status = 'cancelled' THEN 'N/A'
        WHEN o.status = 'completed' THEN 'Delivered'
        WHEN o.status = 'shipped' AND DATEDIFF(CURRENT_DATE, o.order_date) > 7
            THEN 'Delayed'
        WHEN o.status = 'shipped' THEN 'In Transit'
        WHEN o.status = 'pending' AND DATEDIFF(CURRENT_DATE, o.order_date) > 2
            THEN 'Processing Delayed'
        ELSE 'Processing'
    END AS shipping_status
FROM orders o;