SQL Programming Resources on Telegram: “SELECT employee_name, salary, CASE WHEN salary >…”

SELECT employee_name, salary, CASE WHEN salary >= 1000000 THEN 'High' WHEN salary >= 600000 THEN 'Medium' ELSE 'Low' END AS salary_category FROM employees;

Answer 2

SELECT order_id, amount, CASE WHEN amount >= 10000 THEN 'Large' WHEN amount >= 5000 THEN 'Medium' ELSE 'Small' END AS order_size FROM orders;

Answer 3

SELECT SUM(CASE WHEN order_status = 'Completed' THEN 1 ELSE 0 END) AS completed_orders, SUM(CASE WHEN order_status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled_orders FROM orders;

Answer 4

SELECT SUM(CASE WHEN transaction_status = 'Success' THEN amount ELSE 0 END) AS successful_transaction_value FROM transactions;

Answer 5

SELECT customer_name, total_spend, CASE WHEN total_spend >= 100000 THEN 'VIP' ELSE 'Regular' END AS customer_category FROM customers;

Answer 6

SELECT employee_name, CASE WHEN manager_id IS NULL THEN 'No Manager' ELSE 'Has Manager' END AS manager_status FROM employees;

Answer 7

SELECT CASE WHEN salary >= 1000000 THEN 'High' WHEN salary >= 600000 THEN 'Medium' ELSE 'Low' END AS salary_band, COUNT(*) AS employee_count FROM employees GROUP BY CASE WHEN salary >= 1000000 THEN 'High' WHEN salary >= 600000 THEN 'Medium' ELSE 'Low' END;

Answer 8

SELECT SUM(CASE WHEN order_status = 'Completed' THEN amount ELSE 0 END) AS completed_revenue, SUM(CASE WHEN order_status = 'Cancelled' THEN amount ELSE 0 END) AS cancelled_revenue FROM orders;

🔥 Mini Challenge

Imagine an e-commerce company wants this dashboard:

• Total Orders

• Completed Orders

• Cancelled Orders

• Completed Revenue

• Cancelled Revenue

• Completion Rate

Write one SQL query to calculate all six metrics.

Think about:

• COUNT(*) → Total orders

• SUM(CASE...) → Conditional counts/revenue

• COUNT + SUM → Completion rate

Solution

SELECT
    COUNT(*) AS total_orders,
    SUM(CASE WHEN order_status = 'Completed' THEN 1 ELSE 0 END) AS completed_orders,
    SUM(CASE WHEN order_status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled_orders,
    SUM(CASE WHEN order_status = 'Completed' THEN amount ELSE 0 END) AS completed_revenue,
    SUM(CASE WHEN order_status = 'Cancelled' THEN amount ELSE 0 END) AS cancelled_revenue,
    ROUND(100.0 * SUM(CASE WHEN order_status = 'Completed' THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0), 2) AS completion_rate
FROM orders;

Once you become comfortable with CASE, you'll be able to build much more meaningful analytical queries instead of simply retrieving raw data.

Double Tap ❤️ For Part-8