📘 Base Tables (Used for ALL examples)

`orders`

order_id	total_amount	discounted_amount
101	1200	1000
102	700	700
103	300	250

`employees`

emp_id	first_name	middle_name	age
1	Amit	NULL	28
2	Neha	Kumari	55
3	Ravi	NULL	42

🧾 Summary Table (Conditional Questions)

No.	Expression	Question
1	CASE	Categorize orders by value
2	IF()	Classify employees by age
3	COALESCE()	Show preferred employee name
4	NULLIF()	Compare two amounts

1️⃣ CASE Statement

Question: Categorize orders as High, Medium, or Low value.

SELECT
order_id,
total_amount,
CASE
  WHEN total_amount > 1000 THEN 'High Value Order'
  WHEN total_amount > 500 THEN 'Medium Value Order'
  ELSE 'Low Value Order'
END AS order_status
FROM orders;

✅ Output

order_id	total_amount	order_status
101	1200	High Value Order
102	700	Medium Value Order
103	300	Low Value Order

✔ Multiple conditions supported
✔ Works like IF–ELSE ladder

2️⃣ IF() Function

Question: Classify employees as Senior or Junior based on age.

SELECT
first_name,
age,
IF(age > 50, 'Senior', 'Junior') AS employee_category
FROM employees;

✅ Output

first_name	age	employee_category
Amit	28	Junior
Neha	55	Senior
Ravi	42	Junior

✔ Only one condition
✔ Mostly used in MySQL

3️⃣ COALESCE() Function

Question: Show preferred name (first non-NULL value).

SELECT
COALESCE(first_name, middle_name) AS preferred_name
FROM employees;

✅ Output

preferred_name
Amit
Neha
Ravi

✔ Returns first non-NULL value
✔ Very useful for handling NULLs

4️⃣ NULLIF() Function

Question: Return NULL if total_amount equals discounted_amount.

SELECT
order_id,
NULLIF(total_amount, discounted_amount) AS diff_amount
FROM orders;

✅ Output

order_id	diff_amount
101	1200
102	NULL
103	300

✔ Returns NULL when values are equal
✔ Prevents divide-by-zero & comparison issues

🔥 CASE vs IF (Exam Favorite)

Feature	CASE	IF()
Conditions	Multiple	Single
SQL Standard	✅ Yes	❌ MySQL-specific
Readability	High	Medium

🧠 One-Line Memory Trick

Expression	Meaning
CASE	IF–ELSE ladder
IF()	Single condition
COALESCE	First non-NULL
NULLIF	NULL if equal

⚠️ Exam Traps

CASE is portable across DBs
IF() may not work in Oracle / SQL Server
COALESCE can take multiple values
NULLIF(a,b) → returns NULL if a = b

If you want next:

CASE + GROUP BY examples
Conditional expressions MCQs
Hindi explanation for exams
All SQL functions cheat-sheet

Just say 👍