Practice Questions — Conditional Logic - CASE WHEN, IF, IFNULL

CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(50), marks INT);
INSERT INTO students VALUES
  (1,'Aarav Sharma',92),(2,'Priya Nair',78),(3,'Rohan Mehta',64),
  (4,'Ananya Iyer',45),(5,'Vikram Singh',32),(6,'Meera Krishnan',88);

SELECT name, marks,
  CASE
    WHEN marks >= 90 THEN 'A'
    WHEN marks >= 75 THEN 'B'
    WHEN marks >= 60 THEN 'C'
    WHEN marks >= 40 THEN 'D'
    ELSE 'F'
  END AS grade
FROM students
ORDER BY marks DESC;

CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(50), salary DECIMAL(10,2));
INSERT INTO employees VALUES
  (1,'Aarav Sharma',72000),(2,'Priya Nair',45000),
  (3,'Rohan Mehta',51000),(4,'Ananya Iyer',38000),
  (5,'Vikram Singh',95000);

SELECT name, salary,
  IF(salary >= 50000, 'High', 'Low') AS band
FROM employees;

CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT, amount DECIMAL(10,2), status VARCHAR(20));
INSERT INTO orders VALUES
  (1,101,1200,'paid'),(2,101, 800,'paid'),(3,101, 500,'pending'),
  (4,102,2500,'paid'),(5,102,1000,'failed'),
  (6,103, 700,'pending'),(7,103, 300,'pending'),(8,103,1500,'paid');

SELECT customer_id,
  SUM(CASE WHEN status='paid'    THEN amount ELSE 0 END) AS paid_total,
  SUM(CASE WHEN status='pending' THEN amount ELSE 0 END) AS pending_total,
  SUM(CASE WHEN status='failed'  THEN amount ELSE 0 END) AS failed_total
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

CREATE TABLE tickets (id INT PRIMARY KEY, subject VARCHAR(60), priority VARCHAR(10));
INSERT INTO tickets VALUES
  (1,'Server down','urgent'),
  (2,'Typo on page','low'),
  (3,'Deploy pipeline','high'),
  (4,'Slow login','normal'),
  (5,'Billing bug','urgent'),
  (6,'Missing tooltip','low');

SELECT *
FROM tickets
ORDER BY
  CASE priority
    WHEN 'urgent' THEN 1
    WHEN 'high'   THEN 2
    WHEN 'normal' THEN 3
    WHEN 'low'    THEN 4
    ELSE 5
  END,
  id;

CREATE TABLE campaigns (id INT PRIMARY KEY, name VARCHAR(40), spend DECIMAL(10,2), revenue DECIMAL(10,2));
INSERT INTO campaigns VALUES
  (1,'Google Search',5000,25000),
  (2,'Meta Ads',3200,8000),
  (3,'LinkedIn',1500,0),
  (4,'YouTube',0,4200);

SELECT name, spend, revenue,
  ROUND(revenue / NULLIF(spend, 0), 2)                                         AS roas,
  ROUND((revenue - spend) / NULLIF(revenue, 0), 4)                             AS margin
FROM campaigns;

CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50), age INT);
INSERT INTO users VALUES
  (1,'Ishaan Gupta',14),(2,'Kavya Reddy',17),(3,'Arjun Pillai',24),
  (4,'Neha Choudhary',28),(5,'Diya Bhatt',35),(6,'Dev Kapoor',41),
  (7,'Riya Joshi',52),(8,'Kabir Shah',67);

SELECT
  CASE
    WHEN age < 18 THEN 'Under 18'
    WHEN age < 30 THEN '18-29'
    WHEN age < 50 THEN '30-49'
    ELSE            '50+'
  END AS age_group,
  COUNT(*) AS users
FROM users
GROUP BY age_group
ORDER BY MIN(age);

CREATE TABLE feedback (id INT PRIMARY KEY, product_id INT, rating INT);
INSERT INTO feedback VALUES
  (1,1,5),(2,1,4),(3,1,5),(4,1,3),
  (5,2,2),(6,2,1),(7,2,3),
  (8,3,5),(9,3,5),(10,3,4);

SELECT product_id,
  COUNT(CASE WHEN rating=1 THEN 1 END) AS stars_1,
  COUNT(CASE WHEN rating=2 THEN 1 END) AS stars_2,
  COUNT(CASE WHEN rating=3 THEN 1 END) AS stars_3,
  COUNT(CASE WHEN rating=4 THEN 1 END) AS stars_4,
  COUNT(CASE WHEN rating=5 THEN 1 END) AS stars_5,
  COUNT(*)                              AS total_ratings,
  ROUND(AVG(rating), 2)                 AS avg_rating
FROM feedback
GROUP BY product_id
ORDER BY product_id;

CREATE TABLE results (student VARCHAR(50), maths INT, english INT, science INT);
INSERT INTO results VALUES
  ('Aarav Sharma',92,88,95),
  ('Priya Nair',75,82,71),
  ('Rohan Mehta',60,33,68),
  ('Ananya Iyer',35,42,39),
  ('Vikram Singh',NULL,80,85);

SELECT student, maths, english, science,
  (IFNULL(maths,0)+IFNULL(english,0)+IFNULL(science,0)) AS total,
  ROUND((IFNULL(maths,0)+IFNULL(english,0)+IFNULL(science,0))/3.0, 1) AS avg_marks,
  CASE
    WHEN maths IS NULL OR english IS NULL OR science IS NULL  THEN 'Incomplete'
    WHEN maths < 40 OR english < 40 OR science < 40           THEN 'Fail'
    WHEN (maths+english+science)/3.0 >= 90                     THEN 'Distinction'
    WHEN (maths+english+science)/3.0 >= 75                     THEN 'First Class'
    WHEN (maths+english+science)/3.0 >= 60                     THEN 'Second Class'
    ELSE                                                           'Pass'
  END AS verdict
FROM results;