SQL Programming Resources

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

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)

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:

🚀 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 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

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

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;

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.

🚀 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:

🔥 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

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;

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;

🚀 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 COUNT(*) AS total_employees
FROM employees;

Answer 2

SELECT AVG(salary) AS average_salary
FROM employees;

Answer 3

SELECT MAX(price) AS highest_price
FROM products;

Answer 4

SELECT MIN(price) AS lowest_price
FROM products;

Answer 5

SELECT
    SUM(amount) AS total_revenue
FROM orders
WHERE order_status = 'Completed';

Answer 6

SELECT
    COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;

Answer 7

SELECT
    MAX(amount) AS largest_order
FROM orders;

Answer 8

SELECT
    AVG(amount) AS average_order_value
FROM orders
WHERE order_status = 'Completed';

Answer 9

SELECT
    COUNT(*) AS completed_orders
FROM orders
WHERE order_status = 'Completed';

Answer 10

SELECT
    SUM(amount) AS total_revenue,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
WHERE order_status = 'Completed';

🔥 Mini Challenge

You have an orders table: order_id | customer_id | amount | status

Business requirement: Calculate: Total completed orders, Unique completed customers, Total completed revenue, Average completed order value, Largest completed order

Solution

SELECT
    COUNT(*) AS completed_orders,
    COUNT(DISTINCT customer_id) AS unique_customers,
    SUM(amount) AS total_revenue,
    AVG(amount) AS average_order_value,
    MAX(amount) AS largest_order
FROM orders
WHERE status = 'Completed';

Expected result: completed_orders = 4, unique_customers = 3, total_revenue = 15500, average_order_value = 3875, largest_order = 6000

Double Tap ❤️ For Part-6

SELECT
    100.0 *
    SUM(
        CASE
            WHEN status = 'Success'
            THEN 1
            ELSE 0
        END
    ) / COUNT(*) AS success_rate
FROM transactions;

This combines: COUNT + SUM + CASE + Arithmetic.

2️⃣1️⃣ Why GROUP BY Comes Next

At the moment:

SELECT
    SUM(amount)
FROM orders;

gives you one total.

But what if the business asks:

What is the revenue for each city?

Now you need:

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

For now, understand the difference: Aggregate only ↓ One summary, GROUP BY + Aggregate ↓ One summary per group

2️⃣2️⃣ COUNT DISTINCT in Business Analytics

Suppose orders table has 5 orders, customer 101 appears twice, 103 appears twice. Total orders = COUNT(*) = 5, Unique customers = COUNT(DISTINCT customer_id) = 3. This distinction is fundamental.

2️⃣3️⃣ Common Mistake: COUNT(*) vs COUNT(DISTINCT)

If a customer places multiple orders: Customer 101 ↓ Order 1, Order 2, Order 3

Then: COUNT(*) counts: 3 while: COUNT(DISTINCT customer_id) counts: 1

2️⃣4️⃣ Real-World Dashboard Query

Imagine your manager asks for a quick sales summary.

SELECT
    COUNT(*) AS total_orders,
    COUNT(DISTINCT customer_id) AS unique_customers,
    SUM(amount) AS total_revenue,
    AVG(amount) AS average_order_value,
    MIN(amount) AS minimum_order,
    MAX(amount) AS maximum_order
FROM orders
WHERE order_status = 'Completed';

This gives you six useful business metrics in one query.

🧠 Common Beginner Mistakes

❌ Mistake 1: Counting the wrong thing.

Don't automatically use: COUNT(*) when the requirement says:

Number of customers. Use: COUNT(DISTINCT customer_id) when appropriate.

❌ Mistake 2: Assuming NULL is zero.

NULL → Missing/unknown, 0 → Actual numeric zero

❌ Mistake 3: Using SUM on text.

SUM() is designed for numeric expressions. This is invalid or inappropriate: SUM(customer_name)

❌ Mistake 4: Forgetting the business definition.

"Revenue" might mean: Gross revenue, Net revenue, Completed-order revenue, Revenue after discounts, Revenue excluding refunds. Always understand the business definition before writing the SQL.

💼 SQL Interview Questions

Q1. What is an aggregate function?

An aggregate function performs a calculation over multiple rows and returns a summarized value.

Q2. Name five common aggregate functions. COUNT(), SUM(), AVG(), MIN(), MAX()

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

COUNT(*) counts rows, while COUNT(column) counts non-NULL values in that column.

Q4. What does COUNT(DISTINCT customer_id) do?

It counts the number of unique non-NULL customer IDs.

Q5. Does AVG ignore NULL values?

Yes, AVG() normally ignores NULL values.

Q6. How do you calculate total revenue?

SELECT SUM(amount) FROM orders;

Q7. How do you find the highest salary?

SELECT MAX(salary) FROM employees;

Q8. Can multiple aggregate functions be used together?

Yes.

🎯 Practice Questions

Q1. Find the total number of employees.

Q2. Find the average employee salary.

Q3. Find the highest product price.

Q4. Find the lowest product price.

Q5. Calculate total revenue from completed orders.

Q6. Count the number of unique customers who placed an order.

Q7. Find the largest order amount.

Q8. Calculate the average order value for completed orders.

Q9. Count the number of completed orders.

Q10. Calculate total revenue and total unique customers from completed orders.

✅ Answers

Answer 1

SELECT
    MIN(price) AS lowest_price
FROM products;

1️⃣1️⃣ MAX()

MAX() finds the largest value.

SELECT
    MAX(salary) AS highest_salary
FROM employees;

SELECT
    MAX(amount) AS largest_order
FROM orders;

1️⃣2️⃣ Using Multiple Aggregate Functions

You can use several aggregate functions in the same query.

SELECT
    COUNT(*) AS total_orders,
    SUM(amount) AS total_revenue,
    AVG(amount) AS average_order_value,
    MIN(amount) AS smallest_order,
    MAX(amount) AS largest_order
FROM orders;

This single query gives you a basic sales summary.

1️⃣3️⃣ Aggregate Functions With WHERE

Example:

Analyze completed orders only.

SELECT
    COUNT(*) AS completed_orders,
    SUM(amount) AS revenue,
    AVG(amount) AS average_order_value,
    MIN(amount) AS smallest_order,
    MAX(amount) AS largest_order
FROM orders
WHERE order_status = 'Completed';

This is a powerful analytical pattern.

1️⃣4️⃣ NULL and SUM()

SUM() generally ignores NULL values.

Suppose:

amount 1000, 2000, NULL, 3000

Then: SUM(amount) = 6000

However, if all values are NULL, the result can be NULL rather than 0.

You can handle that later using COALESCE().

Example:

SELECT
    COALESCE(SUM(amount), 0) AS total_revenue
FROM orders
WHERE order_status = 'Completed';

1️⃣5️⃣ Aggregate Functions Are the Foundation of KPIs

Most business dashboards are built using aggregate functions.

For example:

• Revenue = SUM(amount)

• Number of Orders = COUNT(*)

• Customers = COUNT(DISTINCT customer_id)

• Average Order Value = AVG(amount)

• Largest Order = MAX(amount)

This is why mastering aggregates is critical.

1️⃣6️⃣ Calculating Average Order Value

A common e-commerce KPI is AOV — Average Order Value.

A simple version:

SELECT
    AVG(amount) AS average_order_value
FROM orders
WHERE order_status = 'Completed';

Another formulation is:

SELECT
    SUM(amount) / COUNT(*) AS average_order_value
FROM orders
WHERE order_status = 'Completed';

The AVG() version is usually clearer when each row represents one order.

1️⃣7️⃣ Calculating Revenue Per Customer

Suppose the business asks:

What is the average revenue generated per unique customer?

You need to be careful not to divide revenue by the number of orders.

SELECT
    SUM(amount) /
    COUNT(DISTINCT customer_id) AS revenue_per_customer
FROM orders
WHERE order_status = 'Completed';

This is a good example of translating a business metric into SQL.

1️⃣8️⃣ Aggregate Functions + Expressions

You can aggregate calculations.

Example:

SELECT
    SUM(quantity * unit_price) AS total_sales
FROM order_items;

SQL first evaluates: quantity * unit_price for each row, then sums those values.

1️⃣9️⃣ Aggregate Functions + CASE

You can create conditional metrics.

Example:

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

This technique becomes extremely important when building dashboards.

2️⃣0️⃣ Example: Success Rate

Suppose you have payment transactions. You want:

Percentage of successful transactions.

🚀 SQL Roadmap 2026 — Part 5

Aggregate Functions: COUNT, SUM, AVG, MIN & MAX 📊

So far, you've learned how to retrieve, filter, and sort individual rows.

Now we're moving to one of the most important skills for a Data Analyst:

Turning thousands of rows into meaningful business metrics.

For example:

How many customers do we have?

What is our total revenue?

What is the average order value?

What is the highest salary?

What is the lowest product price?

That's exactly what aggregate functions are designed for.

1️⃣ What Are Aggregate Functions?

Aggregate functions perform a calculation across multiple rows and return a summarized result.

The five essential functions are:

• COUNT() Counts rows/values

• SUM() Calculates total

• AVG() Calculates average

• MIN() Finds minimum

• MAX() Finds maximum

2️⃣ COUNT()

COUNT() is used to count records or non-NULL values.

Count all rows:

SELECT COUNT(*) AS total_customers
FROM customers;

If there are 5,000 customers: total_customers = 5000

3️⃣ COUNT(*) vs COUNT(column)

This distinction is extremely important.

COUNT(*) Counts rows.

SELECT COUNT(*)
FROM employees;

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

SELECT COUNT(manager_id)
FROM employees;

Suppose: employee A | 101, B | 102, C | NULL, D | 103

Then: COUNT(*) = 4, COUNT(manager_id) = 3

Because one manager_id is NULL.

Interview Tip:

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

4️⃣ COUNT(DISTINCT)

Use COUNT(DISTINCT ...) when you want to count unique values.

Example:

SELECT
    COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;

Suppose: customer_id 101, 101, 102, 103, 103, 103

Then: COUNT(*) = 6, COUNT(DISTINCT customer_id) = 3

This is extremely common in analytics.

5️⃣ Real-World Example: Active Customers

Suppose your orders table contains thousands of orders.

The business asks:

How many unique customers placed an order?

SELECT
    COUNT(DISTINCT customer_id) AS active_customers
FROM orders;

Notice that we're counting customers, not orders. One customer may have placed 20 orders, but should still count as one unique customer.

6️⃣ SUM()

SUM() calculates the total of a numeric column.

Example:

SELECT
    SUM(amount) AS total_revenue
FROM orders;

If the amounts are: 1000, 2000, 1500, 3000 then: SUM = 7500

7️⃣ SUM With a Condition

You can combine SUM() with WHERE.

Example:

Calculate revenue from completed orders only.

SELECT
    SUM(amount) AS completed_revenue
FROM orders
WHERE order_status = 'Completed';

This is a very common business query.

8️⃣ AVG()

AVG() calculates the average of non-NULL numeric values.

Example:

SELECT
    AVG(salary) AS average_salary
FROM employees;

If salaries are: 50000, 60000, 70000 then: Average = 60000

9️⃣ AVG and NULL Values

AVG() generally ignores NULL values.

Suppose: salary 50000, 60000, NULL, 70000

The average is: (50000 + 60000 + 70000) / 3 = 60000

It doesn't divide by 4. This is important when working with incomplete real-world data.

🔟 MIN()

MIN() finds the smallest value.

Example:

SELECT
    MIN(salary) AS lowest_salary
FROM employees;

For products:

🎯 Practice Questions

Try these yourself first.

Q1. Display all employees sorted by salary from highest to lowest.

Q2. Find the top 5 highest-priced products.

Q3. Display customers alphabetically by name.

Q4. Find the 10 customers with the highest spending.

Q5. Display employees by department alphabetically and salary from highest to lowest within each department.

Q6. Find the 3 cheapest products.

Q7. Display unique customer cities alphabetically.

Q8. Return the second page of 10 customers ordered by customer_id.

Q9. Find the 5 most profitable products.

Q10. Explain why ORDER BY revenue DESC LIMIT 3 cannot directly find the top 3 products in each category.

✅ Answers

Answer 1

SELECT employee_name, salary FROM employees ORDER BY salary DESC;

Answer 2

SELECT product_name, price FROM products ORDER BY price DESC LIMIT 5;

Answer 3

SELECT customer_name FROM customers ORDER BY customer_name ASC;

Answer 4

SELECT customer_name, total_spend FROM customers ORDER BY total_spend DESC LIMIT 10;

Answer 5

SELECT employee_name, department, salary FROM employees ORDER BY department ASC, salary DESC;

Answer 6

SELECT product_name, price FROM products ORDER BY price ASC LIMIT 3;

Answer 7

SELECT DISTINCT city FROM customers ORDER BY city ASC;

Answer 8

SELECT * FROM customers ORDER BY customer_id LIMIT 10 OFFSET 10;

Answer 9

SELECT product_name, profit FROM products ORDER BY profit DESC LIMIT 5;

Answer 10

Because LIMIT 3 applies to the entire result, not separately to each category. To get the top 3 within every category, you need a window function such as ROW_NUMBER() or DENSE_RANK().

🔥 Mini Challenge

You have products:

1 Laptop Electronics 90000

2 Phone Electronics 70000

3 Monitor Electronics 50000

4 Chair Furniture 80000

5 Desk Furniture 60000

Business Requirement:

Find the 3 products generating the highest revenue overall.

Steps:

1. Retrieve products, 2. Sort revenue highest → lowest, 3. Keep 3 rows

The solution is:

SELECT product_name, category, revenue FROM products ORDER BY revenue DESC LIMIT 3;

Double Tap ❤️ For Part-5

Result:

Bangalore, Delhi, Hyderabad, Mumbai, Pune

2️⃣0️⃣ ORDER BY Multiple Columns With Different Directions

You can specify different directions.

SELECT
    department,
    employee_name,
    salary
FROM employees
ORDER BY
    department ASC,
    salary DESC;

Meaning:

Department → A to Z, Salary → Highest to Lowest within department

2️⃣1️⃣ Real-World Business Example

Requirement:

Show the 5 most expensive products that are currently active.

SELECT
    product_name,
    category,
    price
FROM products
WHERE product_status = 'Active'
ORDER BY price DESC
LIMIT 5;

Notice the combination:

WHERE → Filter active products, ORDER BY → Highest price first, LIMIT → Keep only 5

2️⃣2️⃣ Another Example

Requirement:

Find the 10 customers with the highest total spending.

SELECT
    customer_id,
    customer_name,
    total_spend
FROM customers
ORDER BY total_spend DESC
LIMIT 10;

This is a classic Data Analyst query.

🧠 Common Beginner Mistakes

❌ Mistake 1: Forgetting DESC

If you want the highest values first:

ORDER BY salary DESC;

Not:

ORDER BY salary;

because the default is typically ascending.

❌ Mistake 2: Using LIMIT without ORDER BY

This:

SELECT *
FROM products
LIMIT 5;

doesn't reliably identify the "top 5" by any business metric.

Instead:

SELECT *
FROM products
ORDER BY revenue DESC
LIMIT 5;

❌ Mistake 3: Confusing LIMIT with filtering

LIMIT doesn't filter rows based on a condition.

LIMIT 10 means:

Return at most 10 rows.

Whereas:

WHERE salary > 800000 means:

Return rows satisfying a condition.

❌ Mistake 4: Using LIMIT for Top N per Group

ORDER BY revenue DESC LIMIT 3; returns 3 rows overall.

It does not return 3 rows from every category.

💼 SQL Interview Questions

Q1. What is ORDER BY?

Answer: ORDER BY sorts the result set according to one or more columns or expressions.

Q2. What is the default sorting direction?

Answer: Ascending (ASC) is the default in standard SQL usage.

Q3. How do you find the highest-paid employee?

SELECT employee_name, salary FROM employees ORDER BY salary DESC LIMIT 1;

Q4. How do you find the top 5 products by revenue?

SELECT product_name, revenue FROM products ORDER BY revenue DESC LIMIT 5;

Q5. What does OFFSET do?

Answer: OFFSET skips a specified number of rows before returning the remaining rows subject to LIMIT or the database's equivalent pagination mechanism.

Q6. Can you sort by multiple columns?

Answer: Yes. ORDER BY department, salary DESC;

Q7. Can you use an alias in ORDER BY?

Answer: In most common SQL systems, yes.

SELECT salary * 12 AS annual_salary FROM employees ORDER BY annual_salary DESC;

SQL first sorts by:

department

Then within each department:

salary DESC

Example:

Finance: 950000, Finance: 750000, IT: 1200000, IT: 850000, IT: 700000

1️⃣1️⃣ Why Multiple Sorting Columns Matter

Suppose several products have the same price.

Laptop: 50000, Phone: 50000, Tablet: 50000

You can add a second sorting condition:

SELECT
    product_name,
    price
FROM products
ORDER BY
    price DESC,
    product_name ASC;

Now SQL uses the product name to break ties.

1️⃣2️⃣ Sorting by Calculated Values

You can sort using an expression.

Example:

SELECT
    product_name,
    selling_price,
    cost_price,
    selling_price - cost_price AS profit
FROM products
ORDER BY profit DESC;

This displays the products with the highest calculated profit first.

1️⃣3️⃣ Sorting by an Alias

You can usually sort using a column alias defined in the SELECT list.

SELECT
    product_name,
    selling_price - cost_price AS profit
FROM products
ORDER BY profit DESC;

This is convenient and makes the query easier to read.

1️⃣4️⃣ Sorting by Column Position

Some SQL dialects allow:

SELECT
    product_name,
    price
FROM products
ORDER BY 2 DESC;

Here:

1 → product_name, 2 → price

So SQL sorts by the second selected column.

⚠️ Best Practice

Although positional ordering may be supported, prefer:

ORDER BY price DESC;

because it is easier to understand and less fragile if the SELECT list changes.

1️⃣5️⃣ NULL Values and ORDER BY

NULL values require special attention.

For example:

Rahul: 5000, Priya: NULL, Amit: 8000

The position of NULL values when sorting can vary by database system and sort direction.

Some systems allow explicit control:

ORDER BY bonus DESC NULLS LAST;

or:

ORDER BY bonus ASC NULLS FIRST;

Interview Tip

Don't assume NULL sorting behavior is identical across MySQL, PostgreSQL, SQL Server, and Oracle.

1️⃣6️⃣ ORDER BY With WHERE

You can combine filtering and sorting.

Example:

Find Mumbai customers and display the highest spenders first.

SELECT
    customer_name,
    city,
    total_spend
FROM customers
WHERE city = 'Mumbai'
ORDER BY total_spend DESC;

Execution conceptually works as:

FROM → WHERE → SELECT → ORDER BY

The detailed logical processing order has a few nuances, but this is a useful beginner mental model.

1️⃣7️⃣ ORDER BY With LIMIT

This combination is extremely important.

Requirement:

Find the top 3 customers by spending.

SELECT
    customer_name,
    total_spend
FROM customers
ORDER BY total_spend DESC
LIMIT 3;

This pattern appears constantly in SQL interviews.

1️⃣8️⃣ Top N Per Category

Here's an important distinction.

Suppose you need:

Top 3 products overall.

You can use:

ORDER BY revenue DESC LIMIT 3;

But if the requirement is:

Top 3 products in every category

LIMIT 3 alone isn't enough.

You'll eventually need window functions such as ROW_NUMBER() or DENSE_RANK().

Example:

WITH ranked_products AS (
    SELECT
        product_name,
        category,
        revenue,
        ROW_NUMBER() OVER (
            PARTITION BY category
            ORDER BY revenue DESC
        ) AS rn
    FROM product_sales
)
SELECT
    product_name,
    category,
    revenue
FROM ranked_products
WHERE rn <= 3;

Don't worry if this looks advanced.

You'll learn window functions later.

1️⃣9️⃣ DISTINCT & ORDER BY

You can combine DISTINCT and ORDER BY.

Example:

SELECT DISTINCT city
FROM customers
ORDER BY city ASC;