🚀 SQL Roadmap 2026 — Part 6

GROUP BY & HAVING — Analyzing Data by Categories 📊

In Part 5, you learned how aggregate functions answer questions like:

What is the total revenue?

How many customers do we have?

But real-world business questions are usually more specific:

What is the revenue by city?

How many employees are there in each department?

Which products generated the most revenue?

That's where GROUP BY comes in.

1️⃣ What is GROUP BY?

GROUP BY combines rows with the same value into groups so that aggregate functions can calculate a metric for each group.

Basic Syntax

SELECT
    column_name,
    aggregate_function(column)
FROM table_name
GROUP BY column_name;

Example:

SELECT
    department,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department;

Instead of getting one total employee count, you get a count for each department.

2️⃣ Why Do We Need GROUP BY?

Without GROUP BY:

SELECT COUNT(*) AS total_employees
FROM employees;

Result: 1000

This answers: How many employees are there?

But:

SELECT
    department,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department;

Result:

• IT | 350

• Finance | 200

• HR | 120

• Sales | 330

Now you can answer: How many employees are in each department?

3️⃣ GROUP BY With COUNT()

This is probably the most common GROUP BY pattern.

SELECT
    city,
    COUNT(*) AS customer_count
FROM customers
GROUP BY city;

4️⃣ GROUP BY With SUM()

Suppose you want revenue by city.

SELECT
    city,
    SUM(amount) AS total_revenue
FROM orders
GROUP BY city;

This is a common business KPI.

5️⃣ GROUP BY With AVG()

Calculate average salary by department:

SELECT
    department,
    AVG(salary) AS average_salary
FROM employees
GROUP BY department;

6️⃣ GROUP BY With MIN() and MAX()

You can use multiple aggregate functions.

SELECT
    department,
    MIN(salary) AS minimum_salary,
    MAX(salary) AS maximum_salary,
    AVG(salary) AS average_salary
FROM employees
GROUP BY department;

7️⃣ Multiple Aggregations

You aren't limited to one metric.

SELECT
    department,
    COUNT(*) AS employees,
    SUM(salary) AS total_salary,
    AVG(salary) AS average_salary,
    MIN(salary) AS minimum_salary,
    MAX(salary) AS maximum_salary
FROM employees
GROUP BY department;

This is the foundation of many analytical reports.

8️⃣ GROUP BY Multiple Columns

You can group by more than one column.

Example: Count customers by city and customer segment.

SELECT
    city,
    customer_segment,
    COUNT(*) AS customer_count
FROM customers
GROUP BY
    city,
    customer_segment;

SQL creates a group for each unique combination.

9️⃣ Understanding Multiple GROUP BY Columns

Grouping by GROUP BY city, segment creates groups like:

• Mumbai + Premium

• Mumbai + Standard

• Delhi + Premium

The combination matters.

🔟 GROUP BY With WHERE

WHERE filters rows before grouping.

Example: Calculate revenue by city for completed orders only.

SELECT
    city,
    SUM(amount) AS revenue
FROM orders
WHERE order_status = 'Completed'
GROUP BY city;

Conceptually: All Orders → WHERE Completed → GROUP BY City → SUM Revenue

1️⃣1️⃣ WHERE vs GROUP BY

WHERE Answers: Which rows should be included?

GROUP BY Answers: How should those rows be divided into groups?

1️⃣2️⃣ What is HAVING?

HAVING filters groups after aggregation.

Example: Find departments with more than 100 employees.

SELECT
    department,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 100;

1️⃣3️⃣ WHERE vs HAVING

This is one of the most frequently asked SQL interview questions.

WHERE filters individual rows. WHERE salary > 500000

HAVING filters groups. HAVING AVG(salary) > 800000

Remember:

WHERE → Filter rows

GROUP BY → Create groups

HAVING → Filter groups

1️⃣4️⃣ Example: WHERE + GROUP BY + HAVING

Requirement: Find departments whose average salary is greater than ₹8 lakh, considering only employees earning more than ₹5 lakh.

SELECT
    department,
    AVG(salary) AS average_salary
FROM employees
WHERE salary > 500000
GROUP BY department
HAVING AVG(salary) > 800000;

1️⃣5️⃣ GROUP BY With COUNT(DISTINCT)

Very useful for customer analytics.

SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

1️⃣6️⃣ GROUP BY With CASE

You can create business categories and then group them.

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;

1️⃣7️⃣ GROUP BY Dates

This is extremely important for Data Analysts.

Example: Calculate monthly revenue.

SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

1️⃣8️⃣ Daily Sales

SELECT
    order_date,
    SUM(amount) AS daily_revenue
FROM orders
GROUP BY order_date
ORDER BY order_date;

1️⃣9️⃣ Monthly Order Count

SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS order_count
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

2️⃣0️⃣ Monthly Customer Count

SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(DISTINCT customer_id) AS active_customers
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

Notice: COUNT(*) counts orders, while COUNT(DISTINCT customer_id) counts unique customers.

2️⃣1️⃣ GROUP BY With ORDER BY

Example: Find departments with the highest average salary.

SELECT
    department,
    AVG(salary) AS average_salary
FROM employees
GROUP BY department
ORDER BY average_salary DESC;

2️⃣2️⃣ GROUP BY + HAVING + ORDER BY

A powerful analytical pattern:

SELECT
    customer_id,
    SUM(amount) AS total_spend
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 50000
ORDER BY total_spend DESC;

2️⃣3️⃣ Real-World Example: Top Revenue Categories

Requirement: Find categories generating more than ₹10 lakh in revenue.

SELECT
    p.category,
    SUM(oi.quantity * oi.selling_price) AS revenue
FROM products p
JOIN order_items oi
    ON p.product_id = oi.product_id
GROUP BY p.category
HAVING SUM(oi.quantity * oi.selling_price) > 1000000
ORDER BY revenue DESC;

2️⃣4️⃣ Common GROUP BY Error

SELECT
    department,
    employee_name,
    AVG(salary)
FROM employees
GROUP BY department;

This is generally invalid because employee_name is neither grouped, nor aggregated.

2️⃣5️⃣ The Golden Rule of GROUP BY

When using GROUP BY, every selected expression generally needs to be either:

1. Included in GROUP BY

2. Or aggregated

Think: Group columns describe the group; aggregate functions summarize the group.

2️⃣6️⃣ SQL Query Pattern to Memorize

SELECT
    grouping_column,
    AGGREGATE_FUNCTION(value_column) AS metric
FROM table_name
WHERE row_condition
GROUP BY grouping_column
HAVING group_condition
ORDER BY metric DESC;

Example:

SELECT
    city,
    SUM(amount) AS revenue
FROM orders
WHERE order_status = 'Completed'
GROUP BY city
HAVING SUM(amount) > 100000
ORDER BY revenue DESC;

🧠 Logical Processing Order

A useful simplified model is:

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

This helps explain why WHERE SUM(amount) > 100000 is not valid. Use HAVING instead.

💼 SQL Interview Questions

Q1. What is GROUP BY?

Groups rows with the same values so aggregate functions can calculate metrics for each group.

Q2. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping, while HAVING filters groups after aggregation.

Q3. Can GROUP BY contain multiple columns?

Yes. GROUP BY city, category;

Q4. Can GROUP BY be used without an aggregate function?

Yes, although SELECT DISTINCT is often clearer when the goal is simply to return unique combinations.

Q5. Can you use aggregate functions in WHERE?

Generally no. Use HAVING.

Q6. Why do we use COUNT(DISTINCT customer_id)?

To count unique customers rather than counting every transaction.

🎯 Practice Questions

Q1. Count employees in each department.

Q2. Calculate total revenue by product category.

Q3. Calculate average salary by department.

Q4. Find the highest salary in each department.

Q5. Count customers by city.

Q6. Find cities with more than 500 customers.

Q7. Calculate monthly revenue.

Q8. Calculate monthly unique customers.

Q9. Find customers whose total spending is greater than ₹50,000.

Q10. Find product categories generating more than ₹1 lakh revenue, sorted from highest to lowest.

✅ Answers

Answer 1

SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department;

Answer 2

SELECT category, SUM(amount) AS revenue FROM sales GROUP BY category;

Answer 3

SELECT department, AVG(salary) AS average_salary FROM employees GROUP BY department;

Answer 4

SELECT department, MAX(salary) AS highest_salary FROM employees GROUP BY department;

Answer 5

SELECT city, COUNT(*) AS customer_count FROM customers GROUP BY city;

Answer 6

SELECT city, COUNT(*) AS customer_count 
FROM customers GROUP BY city HAVING COUNT(*) > 500;

Answer 7

SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue FROM orders GROUP BY DATE_TRUNC('month', order_date) ORDER BY month;

Answer 8

SELECT DATE_TRUNC('month', order_date) AS month, COUNT(DISTINCT customer_id) AS unique_customers FROM orders GROUP BY DATE_TRUNC('month', order_date) ORDER BY month;

Answer 9

SELECT customer_id, SUM(amount) AS total_spend FROM orders GROUP BY customer_id HAVING SUM(amount) > 50000 ORDER BY total_spend DESC;

Answer 10

SELECT category, SUM(amount) AS revenue FROM sales GROUP BY category HAVING SUM(amount) > 100000 ORDER BY revenue DESC;

🔥 Mini Challenge

You have orders table with columns: order_id, customer_id, city, amount, status

Find each city's: Completed order count, Unique customers, Total revenue, Average order value. Only include cities where completed revenue is greater than ₹10,000. Sort by revenue from highest to lowest.

Solution:

SELECT
    city,
    COUNT(*) AS completed_orders,
    COUNT(DISTINCT customer_id) AS unique_customers,
    SUM(amount) AS revenue,
    AVG(amount) AS average_order_value
FROM orders
WHERE status = 'Completed'
GROUP BY city
HAVING SUM(amount) > 10000
ORDER BY revenue DESC;

Double Tap ❤️ For Part-7

🚀 𝗗𝗿𝗲𝗮𝗺𝗶𝗻𝗴 𝗼𝗳 𝗪𝗼𝗿𝗸𝗶𝗻𝗴 𝗮𝘁 𝗧𝗼𝗽 𝗧𝗲𝗰𝗵 𝗖𝗼𝗺𝗽𝗮𝗻𝗶𝗲𝘀? 💻🔥

Here’s a collection of company-specific resources to help you understand their interview and hiring processes.

🎯 Interview Preparation Guides For:

🟠 Amazon – Interviewing Guide
🔵 Google – Interview Tips
🪟 Microsoft – Hiring & Interview Tips
🟢 NVIDIA – Hiring Process
🔷 Meta – Software Engineering Interview Prep

𝐋𝐢𝐧𝐤 👇:-

https://pdlink.in/4i6HkgN

📢 Save & share this with your friends — start learning for FREE!

🔥 𝗠𝗮𝘀𝘁𝗲𝗿 𝗦𝗤𝗟 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 — 𝗙𝗿𝗼𝗺 𝗕𝗲𝗴𝗶𝗻𝗻𝗲𝗿 𝘁𝗼 𝗔𝗱𝘃𝗮𝗻𝗰𝗲𝗱! 💻📊

These free learning resources cover everything from database fundamentals to advanced SQL queries, with opportunities to practice real-world problems.

🎯 Top FREE SQL Resources:
1️⃣ Introduction to Databases & SQL — Udemy
2️⃣ Advanced Database & SQL — Udemy
3️⃣ Learn SQL — Codecademy
4️⃣ SQL Tutorial — SQLZoo

🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 👇:-

https://pdlink.in/4gNYHk7

🚀 Start from the basics and work your way toward advanced SQL skills!

🚀 SQL Roadmap 2026 — Part 7

CASE Statements — Adding Business Logic to SQL 🧠

In the previous parts, you learned how to:

• Retrieve data with SELECT

• Filter data with WHERE

• Sort results with ORDER BY

• Summarize data with aggregate functions

• Group data with GROUP BY

Now it's time to learn how to make SQL think in business categories.

For example:

• Is this customer High Value, Medium Value, or Low Value?

• Is this employee's salary High, Medium, or Low?

• Is this order Small, Medium, or Large?

That's what the CASE expression helps you do.

1️⃣ What is CASE?

CASE allows you to create conditional logic inside SQL.

It's similar to:

• IF condition

• THEN result

• ELSE result

Basic Syntax

SELECT
    column_name,
    CASE
        WHEN condition THEN result
        WHEN condition THEN result
        ELSE result
    END AS new_column
FROM table_name;

2️⃣ Simple CASE Example

Suppose we have employee salaries.

We want to classify employees based on salary.

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

Result:

employee_name | salary  | salary_category
Rahul         | 1200000 | High
Priya         | 850000  | Medium
Amit          | 500000  | Low

3️⃣ How CASE Works

SQL checks conditions from top to bottom.

For:

CASE
    WHEN salary >= 1000000 THEN 'High'
    WHEN salary >= 600000 THEN 'Medium'
    ELSE 'Low'
END

SQL effectively asks:

• Is salary >= 1,000,000? YES → High

• NO → Is salary >= 600,000? YES → Medium

• NO → Low

Once a matching WHEN condition is found, SQL returns that result.

4️⃣ Order of WHEN Conditions Matters

Consider:

CASE
    WHEN salary >= 600000 THEN 'Medium'
    WHEN salary >= 1000000 THEN 'High'
    ELSE 'Low'
END

This is problematic.

Why?

Someone earning ₹12 lakh satisfies:

• salary >= 600000 first.

So SQL labels them:

• Medium instead of High

Better:

CASE
    WHEN salary >= 1000000 THEN 'High'
    WHEN salary >= 600000 THEN 'Medium'
    ELSE 'Low'
END

Rule:

• Put more specific or higher-priority conditions before broader conditions.

5️⃣ CASE With Text Conditions

You can also classify based on text.

Example:

SELECT
    employee_name,
    department,
    CASE
        WHEN department = 'IT' THEN 'Technology'
        WHEN department = 'Finance' THEN 'Corporate'
        WHEN department = 'HR' THEN 'Corporate'
        ELSE 'Other'
    END AS department_group
FROM employees;

6️⃣ CASE With Multiple Conditions

You can use AND and OR inside WHEN.

Example:

SELECT
    customer_name,
    city,
    customer_segment,
    CASE
        WHEN customer_segment = 'Premium'
             AND city = 'Mumbai'
        THEN 'Premium Mumbai'

        WHEN customer_segment = 'Premium'
        THEN 'Other Premium'

        ELSE 'Standard'
    END AS customer_group
FROM customers;

7️⃣ CASE With IN

You can combine CASE with IN.

SELECT
    customer_name,
    city,
    CASE
        WHEN city IN ('Mumbai', 'Pune', 'Nashik')
            THEN 'Maharashtra'
        WHEN city IN ('Delhi', 'Noida', 'Gurgaon')
            THEN 'NCR'
        ELSE 'Other'
    END AS region
FROM customers;

This is useful for creating business regions.

8️⃣ CASE With BETWEEN

Example:

SELECT
    employee_name,
    salary,
    CASE
        WHEN salary BETWEEN 0 AND 500000
            THEN 'Entry Level'

        WHEN salary BETWEEN 500001 AND 1000000
            THEN 'Mid Level'

        ELSE 'Senior Level'
    END AS salary_band
FROM employees;

However, for numeric ranges, inequality conditions are often easier to maintain:

CASE
    WHEN salary < 500000 THEN 'Entry Level'
    WHEN salary < 1000000 THEN 'Mid Level'
    ELSE 'Senior Level'
END

Because the conditions are evaluated from top to bottom.

9️⃣ CASE for Customer Segmentation

Customer segmentation is a common analytics use case.

Suppose:

• total_spend represents total customer spending.

SELECT
    customer_id,
    customer_name,
    total_spend,
    CASE
        WHEN total_spend >= 100000 THEN 'VIP'
        WHEN total_spend >= 50000 THEN 'High Value'
        WHEN total_spend >= 10000 THEN 'Medium Value'
        ELSE 'Low Value'
    END AS customer_segment
FROM customers;

This transforms raw spending into a business classification.

🔟 CASE for Order Size

Suppose you want to classify orders.

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

Result:

order_id | amount | order_size
101      | 12000  | Large
102      | 7000   | Medium
103      | 2500   | Small 

1️⃣1️⃣ CASE for Order Status

You can simplify several statuses into broader business categories.

SELECT
    order_id,
    order_status,
    CASE
        WHEN order_status = 'Completed'
            THEN 'Successful'

        WHEN order_status = 'Cancelled'
            THEN 'Unsuccessful'

        ELSE 'Pending'
    END AS business_status
FROM orders;

1️⃣2️⃣ CASE With Dates

You can classify orders based on when they were placed.

SELECT
    order_id,
    order_date,
    CASE
        WHEN order_date < '2026-01-01'
            THEN 'Previous Year'
        ELSE 'Current Year'
    END AS order_period
FROM orders;

1️⃣3️⃣ CASE for Profitability

Suppose you have:

• selling_price

• cost_price

You can classify products based on profit.

SELECT
    product_name,
    selling_price,
    cost_price,
    selling_price - cost_price AS profit,

    CASE
        WHEN selling_price - cost_price >= 10000
            THEN 'Highly Profitable'

        WHEN selling_price - cost_price > 0
            THEN 'Profitable'

        ELSE 'Loss'
    END AS profitability
FROM products;

1️⃣4️⃣ CASE With Aggregate Functions

This is where CASE becomes extremely powerful.

Suppose you want to count completed orders.

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

Why does this work?

Each row becomes:

• Completed → 1

• Other → 0

Then SUM() adds them.

1️⃣5️⃣ Conditional Counting

You can calculate several metrics at once.

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
FROM orders;

This is called conditional aggregation.

It is one of the most useful SQL techniques for dashboard development.

1️⃣6️⃣ Calculate Success Rate

You can combine CASE, SUM, and COUNT.

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

The logic:

• Completed orders ÷ Total orders × 100

1️⃣7️⃣ Conditional Revenue

Suppose you want only revenue from completed orders.

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

This is especially useful when you need several conditional metrics in one query.

1️⃣8️⃣ Multiple Conditional Metrics

You can build an entire KPI summary:

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_value
FROM orders;

This is very close to the kind of SQL used behind BI dashboards.

1️⃣9️⃣ CASE With GROUP BY

You can create categories and then aggregate them.

Example:

SELECT
    CASE
        WHEN amount >= 10000 THEN 'Large'
        WHEN amount >= 5000 THEN 'Medium'
        ELSE 'Small'
    END AS order_size,
    COUNT(*) AS order_count
FROM orders
GROUP BY
    CASE
        WHEN amount >= 10000 THEN 'Large'
        WHEN amount >= 5000 THEN 'Medium'
        ELSE 'Small'
    END;

Result:

order_size | order_count
Large      | 120
Medium     | 450
Small      | 980

2️⃣0️⃣ CASE + GROUP BY + SUM

You can also calculate revenue by order category.

SELECT
    CASE
        WHEN amount >= 10000 THEN 'Large'
        WHEN amount >= 5000 THEN 'Medium'
        ELSE 'Small'
    END AS order_size,
    SUM(amount) AS revenue
FROM orders
GROUP BY
    CASE
        WHEN amount >= 10000 THEN 'Large'
        WHEN amount >= 5000 THEN 'Medium'
        ELSE 'Small'
    END;

2️⃣1️⃣ Simple CASE vs Searched CASE

There are two common forms.

Searched CASE

This is what we've mainly used:

CASE
    WHEN salary >= 1000000 THEN 'High'
    WHEN salary >= 600000 THEN 'Medium'
    ELSE 'Low'
END

It evaluates conditions.

Simple CASE

Useful when comparing one expression against specific values:

CASE department
    WHEN 'IT' THEN 'Technology'
    WHEN 'HR' THEN 'People'
    WHEN 'Finance' THEN 'Corporate'
    ELSE 'Other'
END

Think:

• Simple CASE → Compare one value

• Searched CASE → Evaluate different conditions

2️⃣2️⃣ CASE and NULL

You can explicitly handle NULL.

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

This is much better than comparing NULL using =.

2️⃣3️⃣ CASE and COALESCE

Sometimes you want to replace NULL with a default value.

SELECT
    employee_name,
    COALESCE(bonus, 0) AS bonus
FROM employees;

You can combine this with CASE:

SELECT
    employee_name,
    CASE
        WHEN COALESCE(bonus, 0) > 10000
            THEN 'High Bonus'
        ELSE 'Standard Bonus'
    END AS bonus_category
FROM employees;

2️⃣4️⃣ CASE in Data Cleaning

CASE can also standardize inconsistent values.

Suppose a dataset contains:

• M

• Male

• male

• MALE

You can standardize them:

SELECT
    employee_name,
    CASE
        WHEN LOWER(gender) = 'm'
          OR LOWER(gender) = 'male'
        THEN 'Male'

        WHEN LOWER(gender) = 'f'
          OR LOWER(gender) = 'female'
        THEN 'Female'

        ELSE 'Unknown'
    END AS standardized_gender
FROM employees;

This is a practical data-cleaning technique.

2️⃣5️⃣ CASE for Business Rules

Imagine a company wants to classify customers:

• Spend ≥ ₹100,000 → VIP

• Spend ≥ ₹50,000 → High Value

• Spend ≥ ₹10,000 → Regular

• Otherwise → Low Value

SQL:

SELECT
    customer_name,
    total_spend,
    CASE
        WHEN total_spend >= 100000 THEN 'VIP'
        WHEN total_spend >= 50000 THEN 'High Value'
        WHEN total_spend >= 10000 THEN 'Regular'
        ELSE 'Low Value'
    END AS customer_segment
FROM customers;

This is an important Data Analyst mindset:

• Convert business rules into SQL logic.

🧠 Common CASE Mistakes

• ❌ Mistake 1: Forgetting END

• Wrong: CASE WHEN salary > 500000 THEN 'High'

•

Correct: CASE WHEN salary > 500000 THEN 'High' ELSE 'Low' END

•

❌ Mistake 2: Incorrect condition order

• Wrong: CASE WHEN salary > 500000 THEN 'Medium' WHEN salary > 1000000 THEN 'High' END

• The second condition won't be reached for salaries above ₹1 million because they already satisfy the first condition.

•

Better: CASE WHEN salary > 1000000 THEN 'High' WHEN salary > 500000 THEN 'Medium' ELSE 'Low' END

•

❌ Mistake 3: Forgetting ELSE

• You can omit ELSE, but if no WHEN condition matches, SQL generally returns NULL.

•

Better when appropriate: CASE WHEN status = 'Completed' THEN 'Success' WHEN status = 'Cancelled' THEN 'Failure' ELSE 'Other' END

•

❌ Mistake 4: Confusing CASE with filtering

• CASE creates or transforms a value.

• WHERE filters rows.

• For example: CASE WHEN salary > 800000 THEN 'High' ELSE 'Low' END doesn't remove rows. It categorizes them.

💼 SQL Interview Questions

•

Q1. What is CASE in SQL? CASE is an expression used to implement conditional logic and return different values based on specified conditions

.

•

Q2. Can CASE be used with aggregate functions? Yes. SUM(CASE WHEN status = 'Completed' THEN 1 ELSE 0 END)

• Q3. What happens if no WHEN condition matches? If there is an ELSE, its value is returned. Otherwise, the result is generally NULL.

• Q4. Does CASE stop after the first matching condition? For a searched CASE, SQL returns the result associated with the first matching WHEN condition.

• Q5. Can CASE be used with GROUP BY? Yes. You can group by a CASE expression or, depending on the SQL dialect, an alias representing that expression.

• Q6. What is conditional aggregation? Using expressions such as CASE inside aggregate functions to calculate metrics for selected conditions.

🎯 Practice Questions

Try solving these yourself first.

• Q1. Classify employees as: High → salary >= 1,000,000, Medium → salary >= 600,000, Low → everything else

• Q2. Classify orders as: Large → amount >= 10,000, Medium → amount >= 5,000, Small → everything else

• Q3. Count completed and cancelled orders using conditional aggregation.

• Q4. Calculate successful transaction value.

• Q5. Classify customers as VIP if spending is greater than ₹100,000.

• Q6. Create a column that says Has Manager or No Manager based on manager_id.

• Q7. Create salary bands and count employees in each band.

• Q8. Calculate completed revenue and cancelled revenue in the same query.

✅ Answers

Answer 1

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

𝗣𝗮𝘆 𝗔𝗳𝘁𝗲𝗿 𝗣𝗹𝗮𝗰𝗲𝗺𝗲𝗻𝘁 — 𝗚𝗲𝘁 𝗣𝗹𝗮𝗰𝗲𝗱 𝗜𝗻 𝗧𝗼𝗽 𝗧𝗲𝗰𝗵 𝗖𝗼𝗺𝗽𝗮𝗻𝗶𝗲𝘀😍

Learn JAVA/MERN Full Stack Development With GenAI.

🏆 Placement Highlights:-

💰 ₹41 LPA highest salary
📈 ₹7.4 LPA average salary
🎓 2,000+ students placed
🏢 500+ partner companies

🔗 𝗔𝗽𝗽𝗹𝘆 𝗡𝗼𝘄 👇:-

https://pdlink.in/3SuUeuD

⚡ Take the first step toward your dream tech career today!

🚀 𝗙𝗥𝗘𝗘 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲 𝗢𝗻 𝗔𝘇𝘂𝗿𝗲 𝗠𝗮𝗰𝗵𝗶𝗻𝗲 𝗟𝗲𝗮𝗿𝗻𝗶𝗻𝗴 ☁️

✨ Build practical skills in Cloud AI • Machine Learning • Data Preparation • ML Workflows • Azure Data Services.

🔥 Learn → Practice → Build Projects → Strengthen Your Tech Career

🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 👇:-

https://pdlink.in/3UyljxK

🎓 Perfect for Students • Freshers • Data Science Aspirants • AI/ML Learners • Working Professionals

🚀 SQL Roadmap 2026 — Part 8

NULL Handling, COALESCE & NULLIF — Managing Missing Data in SQL

In real-world databases, missing data is extremely common.

Customers may not have a phone number.

Orders may not have a discount.

Employees may not have a resignation date.

Transactions may have missing reference values.

SQL uses NULL to represent an unknown or missing value.

Understanding NULL properly is essential for accurate SQL queries and data analysis.

🧠 1. What is NULL?

"NULL" means:

«The value is missing, unknown, or not available.»

Example:

customer_id | customer_name | phone
101         | Alice         | 9876543210
102         | Bob           | NULL
103         | Charlie       | 9123456780

Bob's phone number is not stored.

It does not necessarily mean:

• "0"

• empty string "''"

• "Unknown"

• "N/A"

These are different values.

⚠️ 2. NULL Is Not Equal to 0

SELECT *
FROM customers
WHERE credit_limit = 0;

This finds customers whose credit limit is actually zero.

It will not find customers whose credit limit is missing.

To find missing values:

SELECT *
FROM customers
WHERE credit_limit IS NULL;

⚠️ 3. Never Use = NULL

This is incorrect:

SELECT *
FROM customers
WHERE phone = NULL;

It won't correctly identify NULL values.

Use:

SELECT *
FROM customers
WHERE phone IS NULL;

And for non-NULL values:

SELECT *
FROM customers
WHERE phone IS NOT NULL;

Remember:

= NULL       ❌
<> NULL      ❌
IS NULL      ✅
IS NOT NULL  ✅

🔢 4. NULL in Calculations

Suppose:

order_id | price | discount
1        | 1000  | 100
2        | 800   | NULL

Now:

SELECT
    price,
    discount,
    price - discount AS final_price
FROM orders;

For order 2, the result may be:

NULL

because:

800 - NULL = NULL

SQL generally cannot determine the result when one operand is unknown.

🛠️ 5. COALESCE()

COALESCE() is one of the most important functions for handling NULL values.

It returns the first non-NULL value.

Syntax:

COALESCE(value1, value2, value3, ...)

Example:

SELECT
    customer_name,
    COALESCE(phone, 'Not Available') AS phone
FROM customers;

If "phone" is NULL:

NULL → Not Available

🎯 6. COALESCE with Multiple Values

You can provide several fallback values.

SELECT
    customer_name,
    COALESCE(phone, email, 'No Contact Information') AS contact
FROM customers;

SQL checks in order:

phone
  ↓
email
  ↓
No Contact Information

The first non-NULL value is returned.

💰 7. COALESCE for Financial Calculations

Suppose discounts can be NULL.

Instead of:

SELECT
    price - discount AS final_price
FROM orders;

Use:

SELECT
    price - COALESCE(discount, 0) AS final_price
FROM orders;

Now a missing discount is treated as zero.

Example:

Price | Discount | Final Price
1000  | 100      | 900
800   | NULL     | 800

This is extremely common in analytics.

📊 8. COALESCE with Aggregations

Suppose there are no matching transactions for a customer.

You may want to display:

0

instead of NULL.

SELECT
    customer_id,
    COALESCE(SUM(amount), 0) AS total_spending
FROM transactions
GROUP BY customer_id;

This makes reports easier to interpret.

🧮 9. NULL and COUNT()

These two queries behave differently:

SELECT COUNT(*)
FROM customers;

Counts all rows.

While:

SELECT COUNT(phone)
FROM customers;

Counts only rows where "phone" is not NULL.

Example:

customer | phone
A        | 12345
B        | NULL
C        | 67890

COUNT(*)     → 3
COUNT(phone) → 2

This difference is frequently tested in interviews.

📈 10. NULL and SUM(), AVG(), MIN(), MAX()

Most aggregate functions ignore NULL values.

Example:

Salary
50000
60000
NULL
70000

Then:

SELECT AVG(salary)
FROM employees;

The NULL salary is generally ignored.

So the average is calculated using:

50000, 60000, 70000

not four values.

Important:

• COUNT(*) counts rows.

• COUNT(column) ignores NULL.

• SUM(), AVG(), MIN(), and MAX() generally ignore NULL values.

🔄 11. NULL with CASE

NULL can be handled using CASE.

SELECT
    customer_name,
    CASE
        WHEN phone IS NULL THEN 'Missing'
        ELSE 'Available'
    END AS phone_status
FROM customers;

Result:

customer | phone_status
Alice    | Available
Bob      | Missing
Charlie  | Available

🧹 12. Handling NULL in Data Cleaning

Suppose customer cities contain missing values.

SELECT
    customer_name,
    COALESCE(city, 'Unknown') AS city
FROM customers;

This can make reports more readable.

But be careful:

Replacing NULL does not mean the original data wasn't missing.

For analysis, it may still be important to track missingness.

🧨 13. NULLIF()

NULLIF() returns NULL when two expressions are equal.

Syntax:

NULLIF(value1, value2)

Example:

SELECT NULLIF(10, 10);

Result:

NULL

But:

SELECT NULLIF(10, 5);

Result:

10

🚨 14. NULLIF() for Division by Zero

This is one of the most useful real-world applications.

Suppose:

SELECT
    revenue / orders AS revenue_per_order
FROM sales;

If "orders = 0", some database systems will raise a division-by-zero error.

Use:

SELECT
    revenue / NULLIF(orders, 0) AS revenue_per_order
FROM sales;

If:

orders = 0

then:

NULLIF(orders, 0)

returns:

NULL

So the calculation becomes:

revenue / NULL

and returns NULL instead of attempting division by zero.

You can then provide a fallback:

SELECT
    COALESCE(
        revenue / NULLIF(orders, 0),
        0
    ) AS revenue_per_order
FROM sales;

This combines:

• NULLIF → prevent invalid division

• COALESCE → provide fallback value

🔗 15. NULL in JOINs

NULL becomes especially important with joins.

Suppose:

customers

contains all customers, while:

orders

contains only customers who placed orders.

Using:

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id;

Customers without orders may have:

order_id = NULL

You can identify them with:

WHERE o.order_id IS NULL;

This is a common technique for finding:

«Customers who have never placed an order.»

📦 16. NULL in GROUP BY

NULL values can also appear as a group.

Example:

SELECT
    department,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department;

If some employees have no department, the result can contain a group where:

department = NULL

You can make it more readable:

SELECT
    COALESCE(department, 'Unassigned') AS department,
    COUNT(*) AS employee_count
FROM employees
GROUP BY COALESCE(department, 'Unassigned');

↕️ 17. NULL and ORDER BY

NULL sorting behavior can differ between database systems.

For example:

SELECT *
FROM employees
ORDER BY salary DESC;

Depending on the database, NULL values may appear at the beginning or end.

Some systems support:

ORDER BY salary DESC NULLS LAST;

Always check the SQL dialect you're using when NULL ordering matters.

🧠 18. NULL vs Empty String

These are not necessarily the same:

NULL
''

"NULL" means:

«No known value.»

An empty string means:

«A string exists but contains no characters.»

For example:

phone = ''

is different from:

phone IS NULL

This distinction matters during data cleaning.

🏢 19. Real-World Analytics Example

Imagine an e-commerce dataset:

order_id | revenue | discount | shipping_cost
1        | 2000    | 200      | 100
2        | 1500    | NULL     | 80
3        | 3000    | 300      | NULL

Calculate profit safely:

SELECT
    order_id,
    revenue,
    COALESCE(discount, 0) AS discount,
    COALESCE(shipping_cost, 0) AS shipping_cost,
    revenue
        - COALESCE(discount, 0)
        - COALESCE(shipping_cost, 0) AS net_revenue
FROM orders;

This prevents missing values from turning the entire calculation into NULL.

🎯 20. Business KPI Example — Conversion Rate

Suppose:

conversions = 50
visitors = 0

A safe calculation is:

SELECT
    COALESCE(
        conversions * 100.0 / NULLIF(visitors, 0),
        0
    ) AS conversion_rate
FROM marketing;

The logic is:

NULLIF(visitors, 0)
        ↓
Prevents division by zero
        ↓
Returns NULL if visitors = 0
        ↓
COALESCE(..., 0)
        ↓
Displays 0 instead of NULL

This pattern is highly useful for KPI dashboards.

⚠️ Common NULL Mistakes

Mistake 1:

WHERE salary = NULL;

❌ Incorrect

Use:

WHERE salary IS NULL;

Mistake 2:

Assuming NULL means zero.

NULL ≠ 0

Mistake 3:

Ignoring NULL during calculations.

price - discount

may produce NULL when discount is NULL.

Consider:

price - COALESCE(discount, 0)

when treating missing discount as zero is appropriate.

Mistake 4:

Using COALESCE blindly.

Replacing every NULL with "0" can distort analysis.

For example:

Missing salary → 0

does not mean the employee earns zero.

The correct replacement depends on the business meaning of the missing value.

🎤 SQL Interview Questions

Q1. What is NULL?

NULL represents a missing, unknown, or unavailable value.

Q2. How do you check for NULL?

WHERE column_name IS NULL;

Q3. How do you check for non-NULL values?

WHERE column_name IS NOT NULL;

Q4. Why doesn't "= NULL" work?

Because NULL represents an unknown value and comparisons with NULL do not evaluate to TRUE in the normal way. SQL provides IS NULL and IS NOT NULL specifically for this purpose.

Q5. What does COALESCE() do?

It returns the first non-NULL expression.

COALESCE(phone, email, 'No Contact')

Q6. What does NULLIF() do?

It returns NULL when two expressions are equal.

NULLIF(value1, value2)

Q7. Difference between COUNT(*) and COUNT(column)?

COUNT(*) counts rows.

COUNT(column) counts non-NULL values in that column.

Q8. How can you prevent division by zero?

revenue / NULLIF(orders, 0)

Q9. Does AVG() normally include NULL values?

No. NULL values are generally ignored when calculating the average.

Q10. What is the difference between NULL and 0?

"0" is an actual numeric value.

"NULL" represents an unknown or missing value.

📝 Practice Questions

Practice 1

Find customers whose email is missing.

SELECT *
FROM customers
WHERE email IS NULL;

Practice 2

Display "Unknown" when a customer's city is NULL.

SELECT
    customer_name,
    COALESCE(city, 'Unknown') AS city
FROM customers;

Practice 3

Calculate final price assuming a missing discount means zero.

SELECT
    price - COALESCE(discount, 0) AS final_price
FROM orders;

Practice 4

Calculate revenue per order without dividing by zero.

SELECT
    revenue / NULLIF(order_count, 0) AS revenue_per_order
FROM sales;

Practice 5

Count how many customers have a phone number.

SELECT COUNT(phone) AS customers_with_phone
FROM customers;

🧪 Mini SQL Challenge

You have a table:

sales

sale_id
revenue
discount
orders

Write a query that returns:

• sale_id

• revenue

• discount, treating NULL as 0

• revenue after discount

• revenue per order

• safely handle "orders = 0"

Solution:

SELECT
    sale_id,
    revenue,
    COALESCE(discount, 0) AS discount,

    revenue - COALESCE(discount, 0)
        AS revenue_after_discount,

    COALESCE(
        revenue / NULLIF(orders, 0),
        0
    ) AS revenue_per_order

FROM sales;

Double Tap ❤️ For Part-9

🚀 𝗠𝗮𝘀𝘁𝗲𝗿 𝗜𝗻-𝗗𝗲𝗺𝗮𝗻𝗱 𝗧𝗲𝗰𝗵 𝗦𝗸𝗶𝗹𝗹𝘀 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 𝗶𝗻 𝟮𝟬𝟮𝟲 🔥

Want to upgrade your tech skills without spending money?

Here are some excellent FREE YouTube resources to learn high-demand technologies through tutorials and hands-on practice.

🔥 Learn → Practice → Build Projects → Upgrade Your Resume

🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 👇:-

https://pdlink.in/4x3B9hb

🎯 Perfect for Students • Freshers • Job Seekers • Working Professionals

Data analytics is not about the the tools you master but about the people you influence.

I see many debates around the best tools such as:

- Excel vs SQL
- Python vs R
- Tableau vs PowerBI
- ChatGPT vs no ChatGPT

The truth is that business doesn't care about how you come up with your insights.

All business cares about is:

- the story line
- how well they can understand it
- your communication style
- the overall feeling after a presentation

These make the difference in being perceived as a great data analyst...

not the tools you may or may not master 😅