-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathcase_statement_examples.sql
65 lines (58 loc) · 1.73 KB
/
case_statement_examples.sql
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
-- Basic CASE statement example with employee salaries
SELECT
employee_name,
salary,
CASE
WHEN salary < 30000 THEN 'Entry Level'
WHEN salary BETWEEN 30000 AND 60000 THEN 'Mid Level'
WHEN salary > 60000 THEN 'Senior Level'
ELSE 'Unknown'
END AS salary_grade
FROM employees;
-- CASE statement with multiple conditions
SELECT
order_id,
order_amount,
CASE
WHEN order_amount >= 1000 AND payment_status = 'Paid' THEN 'Premium Order'
WHEN order_amount >= 500 AND payment_status = 'Paid' THEN 'Standard Order'
WHEN payment_status = 'Pending' THEN 'Pending Payment'
ELSE 'Basic Order'
END AS order_category
FROM orders;
-- CASE statement in UPDATE statement
UPDATE products
SET discount_price =
CASE
WHEN category = 'Electronics' THEN price * 0.9 -- 10% discount
WHEN category = 'Clothing' THEN price * 0.85 -- 15% discount
WHEN category = 'Books' AND price > 50 THEN price * 0.95 -- 5% discount
ELSE price -- no discount
END;
-- CASE with aggregate functions
SELECT
department,
COUNT(*) as total_employees,
SUM(CASE
WHEN performance_rating >= 4 THEN 1
ELSE 0
END) as high_performers,
AVG(CASE
WHEN gender = 'F' THEN salary
ELSE NULL
END) as avg_female_salary
FROM employees
GROUP BY department;
-- CASE in ORDER BY clause
SELECT
product_name,
stock_level,
price
FROM products
ORDER BY
CASE
WHEN stock_level = 0 THEN 1 -- Out of stock items first
WHEN stock_level < 10 THEN 2 -- Low stock items second
ELSE 3 -- Normal stock items last
END,
price DESC;