This allows us to connect orders to customers.
8️⃣ Understanding Relationships
The relationship is:
Customers
Customer_ID
↓
Orders
One customer can have multiple orders.
For example:
John
↓
Order 5001
Order 5003
Order 5010
This is a:
One-to-Many relationship
It's one of the most important database concepts for Data Analysts.
9️⃣ What Is a Relational Database?
A relational database stores data in related tables.
For example:
Customers
↓
Orders
↓
Order Details
↓
Products
Instead of storing the customer's name repeatedly in every order, the database can store:
Customer_ID
and retrieve the customer information through relationships.
This helps reduce unnecessary duplication.
🔟 What Is SQL Syntax?
SQL queries generally consist of keywords and expressions.
For example:
SELECT *
FROM Customers;
This asks:
Let me break it down.
SELECT: Specifies what you want to retrieve.
FROM: Specifies the table.
Customers: The table you're querying.
1️⃣1️⃣ SELECT
SELECT is one of the first SQL commands you need to learn.
Suppose you have:
Employees
Employee_ID Name Department Salary
101 John IT 75,000
102 Sarah HR 60,000
103 Mike Finance 82,000
To retrieve all columns:
SELECT *
FROM Employees;
1️⃣2️⃣ Selecting Specific Columns
You don't always need every column.
Suppose you only want:
Name and Department
Use:
SELECT Name, Department
FROM Employees;
Result:
Name Department
John IT
Sarah HR
Mike Finance
This is generally better than using SELECT * when you only need specific fields.
1️⃣3️⃣ Why Avoid SELECT * in Production Queries?
You may see beginners writing:
SELECT *
FROM Employees;
all the time.
It's useful while learning and exploring data.
But in production queries, explicitly selecting the required columns is often better because:
• It makes the query clearer
• It avoids retrieving unnecessary data
• It can reduce data transfer
• It makes downstream dependencies more predictable
For example:
SELECT Employee_ID, Name, Salary
FROM Employees;
is more intentional.
1️⃣4️⃣ WHERE
WHERE filters records.
Suppose you want employees from IT.
SELECT *
FROM Employees
WHERE Department = 'IT';
Result:
Employee_ID Name Department Salary
101 John IT 75,000
The database only returns records satisfying the condition.
1️⃣5️⃣ Filtering Numeric Values
Suppose you want employees earning more than ₹70,000.
SELECT *
FROM Employees
WHERE Salary > 70000;
Result:
Employee_ID Name Department Salary
101 John IT 75,000
103 Mike Finance 82,000
1️⃣6️⃣ Comparison Operators
You should know these operators:
Operator Meaning
= Equal to
<> Not equal to
Examples:
WHERE Salary >= 80000
WHERE Department <> 'HR'
1️⃣7️⃣ AND
AND requires all conditions to be true.
Suppose you want:
IT employees earning more than ₹70,000.
SELECT *
FROM Employees
WHERE Department = 'IT'
AND Salary > 70000;
The record must satisfy both conditions.
Think:
IT
AND
Salary > 70,000
1️⃣8️⃣ OR
OR requires at least one condition to be true.
Suppose you want:
IT or Finance employees.
8️⃣ Understanding Relationships
The relationship is:
Customers
Customer_ID
↓
Orders
One customer can have multiple orders.
For example:
John
↓
Order 5001
Order 5003
Order 5010
This is a:
One-to-Many relationship
It's one of the most important database concepts for Data Analysts.
9️⃣ What Is a Relational Database?
A relational database stores data in related tables.
For example:
Customers
↓
Orders
↓
Order Details
↓
Products
Instead of storing the customer's name repeatedly in every order, the database can store:
Customer_ID
and retrieve the customer information through relationships.
This helps reduce unnecessary duplication.
🔟 What Is SQL Syntax?
SQL queries generally consist of keywords and expressions.
For example:
SELECT *
FROM Customers;
This asks:
Return all columns from the Customers table.
Let me break it down.
SELECT: Specifies what you want to retrieve.
FROM: Specifies the table.
Customers: The table you're querying.
1️⃣1️⃣ SELECT
SELECT is one of the first SQL commands you need to learn.
Suppose you have:
Employees
Employee_ID Name Department Salary
101 John IT 75,000
102 Sarah HR 60,000
103 Mike Finance 82,000
To retrieve all columns:
SELECT *
FROM Employees;
1️⃣2️⃣ Selecting Specific Columns
You don't always need every column.
Suppose you only want:
Name and Department
Use:
SELECT Name, Department
FROM Employees;
Result:
Name Department
John IT
Sarah HR
Mike Finance
This is generally better than using SELECT * when you only need specific fields.
1️⃣3️⃣ Why Avoid SELECT * in Production Queries?
You may see beginners writing:
SELECT *
FROM Employees;
all the time.
It's useful while learning and exploring data.
But in production queries, explicitly selecting the required columns is often better because:
• It makes the query clearer
• It avoids retrieving unnecessary data
• It can reduce data transfer
• It makes downstream dependencies more predictable
For example:
SELECT Employee_ID, Name, Salary
FROM Employees;
is more intentional.
1️⃣4️⃣ WHERE
WHERE filters records.
Suppose you want employees from IT.
SELECT *
FROM Employees
WHERE Department = 'IT';
Result:
Employee_ID Name Department Salary
101 John IT 75,000
The database only returns records satisfying the condition.
1️⃣5️⃣ Filtering Numeric Values
Suppose you want employees earning more than ₹70,000.
SELECT *
FROM Employees
WHERE Salary > 70000;
Result:
Employee_ID Name Department Salary
101 John IT 75,000
103 Mike Finance 82,000
1️⃣6️⃣ Comparison Operators
You should know these operators:
Operator Meaning
= Equal to
<> Not equal to
Greater than
< Less than
= Greater than or equal
<= Less than or equal
Examples:
WHERE Salary >= 80000
WHERE Department <> 'HR'
1️⃣7️⃣ AND
AND requires all conditions to be true.
Suppose you want:
IT employees earning more than ₹70,000.
SELECT *
FROM Employees
WHERE Department = 'IT'
AND Salary > 70000;
The record must satisfy both conditions.
Think:
IT
AND
Salary > 70,000
1️⃣8️⃣ OR
OR requires at least one condition to be true.
Suppose you want:
IT or Finance employees.
SELECT *
FROM Employees
WHERE Department = 'IT'
OR Department = 'Finance';
Both departments will be included.
1️⃣9️⃣ IN
When checking multiple values, IN makes your query cleaner.
Instead of:
WHERE Department = 'IT'
OR Department = 'Finance'
OR Department = 'HR'
you can write:
WHERE Department IN ('IT', 'Finance', 'HR');
This is easier to read and maintain.
2️⃣0️⃣ NOT IN
You can exclude multiple values.
SELECT *
FROM Employees
WHERE Department NOT IN ('HR', 'Finance');
This returns employees who aren't in those departments.
2️⃣1️⃣ BETWEEN
BETWEEN checks whether a value falls within a range.
For example:
SELECT *
FROM Employees
WHERE Salary BETWEEN 50000 AND 80000;
This returns salaries within the specified range.
For numeric data, this is often useful for:
• Salary ranges
• Sales ranges
• Age ranges
• Scores
• Transaction values
2️⃣2️⃣ LIKE
LIKE is used for pattern matching.
Suppose you want employees whose names start with J.
SELECT *
FROM Employees
WHERE Name LIKE 'J%';
% means:
So this could match:
• John
• James
• Jennifer
2️⃣3️⃣ LIKE with Wildcards
•
Starts with J
LIKE 'J%'
•
Ends with n
LIKE '%n'
•
Contains "oh"
LIKE '%oh%'
Wildcards are extremely useful when searching text data.
2️⃣4️⃣ DISTINCT
DISTINCT removes duplicate values from the result.
Suppose your employee table contains:
• IT
• HR
• IT
• Finance
• HR
• IT
Use:
SELECT DISTINCT Department
FROM Employees;
Result:
IT
HR
Finance
This is useful for discovering categories in a dataset.
2️⃣5️⃣ ORDER BY
ORDER BY sorts your results.
Suppose you want employees with the highest salary first.
SELECT *
FROM Employees
ORDER BY Salary DESC;
DESC means:
Descending
Highest → Lowest
2️⃣6️⃣ ASC
ASC means ascending.
SELECT *
FROM Employees
ORDER BY Salary ASC;
Lowest → Highest
Ascending is generally the default sort direction.
2️⃣7️⃣ LIMIT / TOP
The syntax depends on the database system.
In systems such as PostgreSQL and MySQL:
SELECT *
FROM Employees
ORDER BY Salary DESC
LIMIT 5;
This returns the top 5 employees by salary.
In SQL Server, you would commonly use:
SELECT TOP 5 *
FROM Employees
ORDER BY Salary DESC;
This is an important point:
2️⃣8️⃣ Aliases
Aliases give columns or tables temporary names within a query.
For example:
SELECT
Name AS Employee_Name,
Salary AS Annual_Salary
FROM Employees;
The result displays:
Employee_Name Annual_Salary
John 75,000
Sarah 60,000
Aliases make results easier to understand.
2️⃣9️⃣ SQL Comments
You can add comments to explain your queries.
For example:
-- Get employees earning more than 70,000
SELECT Name, Salary
FROM Employees
WHERE Salary > 70000;
Comments don't affect the query result.
They're useful when queries become complex.
🧪 Practical Interview Challenge
Suppose you have:
Employees
ID Name Department Salary
101 John IT 75,000
102 Sarah HR 60,000
103 Mike Finance 82,000
104 David IT 90,000
105 Alice HR 65,000
Q1. Retrieve all employees.
SELECT *
FROM Employees;
Q2. Retrieve only names and salaries.
SELECT Name, Salary
FROM Employees;
Q3. Find employees earning more than ₹70,000.
SELECT *
FROM Employees
WHERE Salary > 70000;
Q4. Find IT employees.
SELECT *
FROM Employees
WHERE Department = 'IT';
Q5. Find IT or Finance employees.
SELECT *
FROM Employees
WHERE Department IN ('IT', 'Finance');
Q6. Sort employees by salary from highest to lowest.
SELECT *
FROM Employees
ORDER BY Salary DESC;
Q7. Find the top 3 highest-paid employees.
PostgreSQL/MySQL:
SELECT *
FROM Employees
ORDER BY Salary DESC
LIMIT 3;
SQL Server:
SELECT TOP 3 *
FROM Employees
ORDER BY Salary DESC;
Q8. List unique departments.
SELECT DISTINCT Department
FROM Employees;
🏆 Double Tap ❤️ For More
FROM Employees
WHERE Department = 'IT'
OR Department = 'Finance';
Both departments will be included.
1️⃣9️⃣ IN
When checking multiple values, IN makes your query cleaner.
Instead of:
WHERE Department = 'IT'
OR Department = 'Finance'
OR Department = 'HR'
you can write:
WHERE Department IN ('IT', 'Finance', 'HR');
This is easier to read and maintain.
2️⃣0️⃣ NOT IN
You can exclude multiple values.
SELECT *
FROM Employees
WHERE Department NOT IN ('HR', 'Finance');
This returns employees who aren't in those departments.
2️⃣1️⃣ BETWEEN
BETWEEN checks whether a value falls within a range.
For example:
SELECT *
FROM Employees
WHERE Salary BETWEEN 50000 AND 80000;
This returns salaries within the specified range.
For numeric data, this is often useful for:
• Salary ranges
• Sales ranges
• Age ranges
• Scores
• Transaction values
2️⃣2️⃣ LIKE
LIKE is used for pattern matching.
Suppose you want employees whose names start with J.
SELECT *
FROM Employees
WHERE Name LIKE 'J%';
% means:
Any number of characters.
So this could match:
• John
• James
• Jennifer
2️⃣3️⃣ LIKE with Wildcards
•
Starts with J
LIKE 'J%'
•
Ends with n
LIKE '%n'
•
Contains "oh"
LIKE '%oh%'
Wildcards are extremely useful when searching text data.
2️⃣4️⃣ DISTINCT
DISTINCT removes duplicate values from the result.
Suppose your employee table contains:
• IT
• HR
• IT
• Finance
• HR
• IT
Use:
SELECT DISTINCT Department
FROM Employees;
Result:
IT
HR
Finance
This is useful for discovering categories in a dataset.
2️⃣5️⃣ ORDER BY
ORDER BY sorts your results.
Suppose you want employees with the highest salary first.
SELECT *
FROM Employees
ORDER BY Salary DESC;
DESC means:
Descending
Highest → Lowest
2️⃣6️⃣ ASC
ASC means ascending.
SELECT *
FROM Employees
ORDER BY Salary ASC;
Lowest → Highest
Ascending is generally the default sort direction.
2️⃣7️⃣ LIMIT / TOP
The syntax depends on the database system.
In systems such as PostgreSQL and MySQL:
SELECT *
FROM Employees
ORDER BY Salary DESC
LIMIT 5;
This returns the top 5 employees by salary.
In SQL Server, you would commonly use:
SELECT TOP 5 *
FROM Employees
ORDER BY Salary DESC;
This is an important point:
SQL is a language, but different database systems have slightly different syntax.
2️⃣8️⃣ Aliases
Aliases give columns or tables temporary names within a query.
For example:
SELECT
Name AS Employee_Name,
Salary AS Annual_Salary
FROM Employees;
The result displays:
Employee_Name Annual_Salary
John 75,000
Sarah 60,000
Aliases make results easier to understand.
2️⃣9️⃣ SQL Comments
You can add comments to explain your queries.
For example:
-- Get employees earning more than 70,000
SELECT Name, Salary
FROM Employees
WHERE Salary > 70000;
Comments don't affect the query result.
They're useful when queries become complex.
🧪 Practical Interview Challenge
Suppose you have:
Employees
ID Name Department Salary
101 John IT 75,000
102 Sarah HR 60,000
103 Mike Finance 82,000
104 David IT 90,000
105 Alice HR 65,000
Q1. Retrieve all employees.
SELECT *
FROM Employees;
Q2. Retrieve only names and salaries.
SELECT Name, Salary
FROM Employees;
Q3. Find employees earning more than ₹70,000.
SELECT *
FROM Employees
WHERE Salary > 70000;
Q4. Find IT employees.
SELECT *
FROM Employees
WHERE Department = 'IT';
Q5. Find IT or Finance employees.
SELECT *
FROM Employees
WHERE Department IN ('IT', 'Finance');
Q6. Sort employees by salary from highest to lowest.
SELECT *
FROM Employees
ORDER BY Salary DESC;
Q7. Find the top 3 highest-paid employees.
PostgreSQL/MySQL:
SELECT *
FROM Employees
ORDER BY Salary DESC
LIMIT 3;
SQL Server:
SELECT TOP 3 *
FROM Employees
ORDER BY Salary DESC;
Q8. List unique departments.
SELECT DISTINCT Department
FROM Employees;
🏆 Double Tap ❤️ For More
❤20
🚀 Data Analyst Roadmap — Part 12
🗄️ SQL — Level 2: Aggregate Functions, GROUP BY & HAVING
Now that you've learned SQL fundamentals, it's time to move from retrieving individual records to summarizing data.
This is one of the most important SQL skills for Data Analysts.
In real interviews and jobs, you'll frequently be asked questions like:
To answer these questions, you need:
Aggregate Functions + GROUP BY + HAVING
1️⃣ What Are Aggregate Functions?
Aggregate functions perform calculations across multiple rows and return a summarized result.
The most important ones are:
SUM()
COUNT()
AVG()
MIN()
MAX()
Think of them as the SQL equivalent of the basic Excel functions you learned earlier.
2️⃣ SUM()
SUM() calculates the total of a numeric column.
Suppose you have:
Order_ID: 1001, Sales: 50,000
Order_ID: 1002, Sales: 70,000
Order_ID: 1003, Sales: 30,000
Query:
Result:
Total_Sales = 150,000
Business question
Answer → SUM()
3️⃣ COUNT()
COUNT() counts records.
If there are 10,000 orders:
Total_Orders = 10,000
Why COUNT(*)?
COUNT(*) counts rows.
This is often useful when you want the total number of records.
4️⃣ COUNT(Column)
You can also count values in a specific column.
One important distinction:
COUNT(column) generally doesn't count NULL values.
Whereas:
COUNT(*)
counts rows regardless of whether individual columns contain NULLs.
5️⃣ COUNT(DISTINCT)
Suppose your Orders table contains:
Order 1001 → Customer 101
Order 1002 → Customer 102
Order 1003 → Customer 101
Order 1004 → Customer 103
There are:
4 orders
but only:
3 unique customers
Use:
Result:
3
This is extremely important in analytics.
6️⃣ AVG()
AVG() calculates the average.
Suppose salaries are:
50,000, 60,000, 70,000
Query:
Result:
60,000
Business questions
Answer → AVG()
7️⃣ MIN()
MIN() returns the smallest value.
Example result:
35,000
Useful for:
Minimum salary
Lowest sales
Earliest date
Lowest transaction value
8️⃣ MAX()
MAX() returns the largest value.
Result:
150,000
Useful for:
Highest salary
Highest sales
Largest transaction
Latest date
9️⃣ Using Multiple Aggregate Functions
You can use several aggregate functions in one query.
🗄️ SQL — Level 2: Aggregate Functions, GROUP BY & HAVING
Now that you've learned SQL fundamentals, it's time to move from retrieving individual records to summarizing data.
This is one of the most important SQL skills for Data Analysts.
In real interviews and jobs, you'll frequently be asked questions like:
What is the total sales by region?
What is the average salary by department?
How many customers are in each city?
Which products generated more than ₹10 lakh in sales?
To answer these questions, you need:
Aggregate Functions + GROUP BY + HAVING
1️⃣ What Are Aggregate Functions?
Aggregate functions perform calculations across multiple rows and return a summarized result.
The most important ones are:
SUM()
COUNT()
AVG()
MIN()
MAX()
Think of them as the SQL equivalent of the basic Excel functions you learned earlier.
2️⃣ SUM()
SUM() calculates the total of a numeric column.
Suppose you have:
Order_ID: 1001, Sales: 50,000
Order_ID: 1002, Sales: 70,000
Order_ID: 1003, Sales: 30,000
Query:
SELECT SUM(Sales) AS Total_Sales
FROM Orders;
Result:
Total_Sales = 150,000
Business question
What is our total revenue?
Answer → SUM()
3️⃣ COUNT()
COUNT() counts records.
SELECT COUNT(*) AS Total_Orders
FROM Orders;
If there are 10,000 orders:
Total_Orders = 10,000
Why COUNT(*)?
COUNT(*) counts rows.
This is often useful when you want the total number of records.
4️⃣ COUNT(Column)
You can also count values in a specific column.
SELECT COUNT(Customer_ID) AS Customer_Count
FROM Orders;
One important distinction:
COUNT(column) generally doesn't count NULL values.
Whereas:
COUNT(*)
counts rows regardless of whether individual columns contain NULLs.
5️⃣ COUNT(DISTINCT)
Suppose your Orders table contains:
Order 1001 → Customer 101
Order 1002 → Customer 102
Order 1003 → Customer 101
Order 1004 → Customer 103
There are:
4 orders
but only:
3 unique customers
Use:
SELECT COUNT(DISTINCT Customer_ID) AS Unique_Customers
FROM Orders;
Result:
3
This is extremely important in analytics.
6️⃣ AVG()
AVG() calculates the average.
Suppose salaries are:
50,000, 60,000, 70,000
Query:
SELECT AVG(Salary) AS Average_Salary
FROM Employees;
Result:
60,000
Business questions
What is the average order value?
What is the average employee salary?
What is the average product price?
Answer → AVG()
7️⃣ MIN()
MIN() returns the smallest value.
SELECT MIN(Salary) AS Minimum_Salary
FROM Employees;
Example result:
35,000
Useful for:
Minimum salary
Lowest sales
Earliest date
Lowest transaction value
8️⃣ MAX()
MAX() returns the largest value.
SELECT MAX(Salary) AS Maximum_Salary
FROM Employees;
Result:
150,000
Useful for:
Highest salary
Highest sales
Largest transaction
Latest date
9️⃣ Using Multiple Aggregate Functions
You can use several aggregate functions in one query.
SELECT
SUM(Sales) AS Total_Sales,
AVG(Sales) AS Average_Sales,
MIN(Sales) AS Minimum_Sales,
MAX(Sales) AS Maximum_Sales,
COUNT(*) AS Total_Orders
FROM Orders;
❤3
The process is:
WHERE → Filter rows
GROUP BY → Create groups
SUM → Calculate totals
HAVING → Filter groups
This sequence is fundamental to SQL analysis.
1️⃣9️⃣ ORDER BY with GROUP BY
You can sort aggregated results.
Suppose you want regions with the highest sales first:
Result:
North: 500,000, South: 350,000, West: 200,000, East: 150,000
2️⃣0️⃣ Top 3 Regions
You can combine:
GROUP BY + ORDER BY + LIMIT
For example, in PostgreSQL/MySQL:
This answers:
2️⃣1️⃣ GROUP BY Dates
Suppose you have:
Order_Date and Sales
You might want:
The exact date function varies by database system.
For example, in PostgreSQL:
Result:
2024: 8,500,000, 2025: 10,200,000, 2026: 12,400,000
2️⃣2️⃣ Grouping by Month
In PostgreSQL, you can use:
This creates monthly sales totals.
Different SQL platforms have different date functions, so always check the database you're working with.
2️⃣3️⃣ Calculate Average Order Value
A common business KPI is:
Average Order Value (AOV)
A simple version is:
If each row represents exactly one order.
If the table can contain multiple rows per order, however, you need to calculate the denominator based on distinct orders:
This distinction is extremely important.
2️⃣4️⃣ COUNT(DISTINCT) in Real Analytics
Suppose a customer places multiple orders:
Customer 101 → Orders 5001, 5002
Customer 102 → Order 5003
Customer 103 → Orders 5004, 5005
Total orders: 5
Unique customers: 3
Query:
Result: 3
This is commonly used for metrics such as:
Active customers
Unique users
Unique accounts
Distinct orders
Distinct products
2️⃣5️⃣ Conditional Aggregation
One powerful technique is combining CASE WHEN with aggregate functions.
For example:
This allows you to create customized metrics.
You'll use this technique much more in advanced SQL.
2️⃣6️⃣ Common SQL Analytical Pattern
A very common query structure is:
For example:
WHERE → Filter rows
GROUP BY → Create groups
SUM → Calculate totals
HAVING → Filter groups
This sequence is fundamental to SQL analysis.
1️⃣9️⃣ ORDER BY with GROUP BY
You can sort aggregated results.
Suppose you want regions with the highest sales first:
SELECT
Region,
SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Region
ORDER BY Total_Sales DESC;
Result:
North: 500,000, South: 350,000, West: 200,000, East: 150,000
2️⃣0️⃣ Top 3 Regions
You can combine:
GROUP BY + ORDER BY + LIMIT
For example, in PostgreSQL/MySQL:
SELECT
Region,
SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Region
ORDER BY Total_Sales DESC
LIMIT 3;
This answers:
"Which three regions generated the most sales?"
2️⃣1️⃣ GROUP BY Dates
Suppose you have:
Order_Date and Sales
You might want:
Total sales by year.
The exact date function varies by database system.
For example, in PostgreSQL:
SELECT
EXTRACT(YEAR FROM Order_Date) AS Sales_Year,
SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY EXTRACT(YEAR FROM Order_Date)
ORDER BY Sales_Year;
Result:
2024: 8,500,000, 2025: 10,200,000, 2026: 12,400,000
2️⃣2️⃣ Grouping by Month
In PostgreSQL, you can use:
SELECT
DATE_TRUNC('month', Order_Date) AS Sales_Month,
SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY DATE_TRUNC('month', Order_Date)
ORDER BY Sales_Month;
This creates monthly sales totals.
Different SQL platforms have different date functions, so always check the database you're working with.
2️⃣3️⃣ Calculate Average Order Value
A common business KPI is:
Average Order Value (AOV)
A simple version is:
SELECT
SUM(Sales) / COUNT(*) AS Average_Order_Value
FROM Orders;
If each row represents exactly one order.
If the table can contain multiple rows per order, however, you need to calculate the denominator based on distinct orders:
SELECT
SUM(Sales) / COUNT(DISTINCT Order_ID) AS Average_Order_Value
FROM Orders;
This distinction is extremely important.
2️⃣4️⃣ COUNT(DISTINCT) in Real Analytics
Suppose a customer places multiple orders:
Customer 101 → Orders 5001, 5002
Customer 102 → Order 5003
Customer 103 → Orders 5004, 5005
Total orders: 5
Unique customers: 3
Query:
SELECT COUNT(DISTINCT Customer_ID) AS Unique_Customers
FROM Orders;
Result: 3
This is commonly used for metrics such as:
Active customers
Unique users
Unique accounts
Distinct orders
Distinct products
2️⃣5️⃣ Conditional Aggregation
One powerful technique is combining CASE WHEN with aggregate functions.
For example:
Count how many orders were above ₹50,000.
SELECT
SUM(
CASE
WHEN Sales > 50000 THEN 1
ELSE 0
END
) AS High_Value_Orders
FROM Orders;
This allows you to create customized metrics.
You'll use this technique much more in advanced SQL.
2️⃣6️⃣ Common SQL Analytical Pattern
A very common query structure is:
SELECT
Dimension,
AGGREGATE_FUNCTION(Metric) AS KPI
FROM Table
WHERE Condition
GROUP BY Dimension
HAVING Aggregate_Condition
ORDER BY KPI DESC;
For example:
SELECT
Region,
SUM(Sales) AS Total_Sales
FROM Orders
WHERE Order_Date >= '2026-01-01'
GROUP BY Region
HAVING SUM(Sales) > 100000
ORDER BY Total_Sales DESC;
This produces a compact business summary.
For example:
Total_Sales: 15,000,000, Average_Sales: 75,000, Minimum_Sales: 1,000, Maximum_Sales: 500,000, Total_Orders: 200
🔟 Why Do We Need GROUP BY?
Aggregate functions give you an overall summary.
But what if the business asks:
You need to divide the data into groups.
That's what GROUP BY does.
1️⃣1️⃣ Basic GROUP BY
Suppose:
North: 50,000 and 70,000 → Total 120,000
South: 40,000 and 60,000 → Total 100,000
West: 80,000 → Total 80,000
Query:
Result:
North = 120,000, South = 100,000, West = 80,000
Now you've answered:
1️⃣2️⃣ GROUP BY Department
Suppose you have:
John - IT - 75,000
Sarah - HR - 60,000
Mike - IT - 82,000
David - Finance - 90,000
Alice - HR - 65,000
Query:
Result:
Finance: 90,000, HR: 62,500, IT: 78,500
1️⃣3️⃣ GROUP BY with COUNT()
Question:
Result:
IT: 2, HR: 2, Finance: 1
1️⃣4️⃣ GROUP BY with Multiple Columns
You can group by more than one column.
Suppose your sales data contains:
North Electronics: 80,000
North Furniture: 40,000
South Electronics: 70,000
South Furniture: 50,000
Query:
Result:
North Electronics = 80,000, North Furniture = 40,000, South Electronics = 70,000, South Furniture = 50,000
This lets you analyze combinations of dimensions.
1️⃣5️⃣ GROUP BY vs PivotTable
This is an important connection.
In Excel:
Region → Rows
Sales → Values
In SQL:
The analytical concept is very similar.
You're grouping records and calculating an aggregate.
1️⃣6️⃣ HAVING
Now suppose you want:
You can't simply use WHERE on the aggregate result.
You use: HAVING
Result:
North = 120,000
1️⃣7️⃣ WHERE vs HAVING
This is a very common SQL interview question.
WHERE
Filters individual rows before grouping.
Example:
HAVING
Filters groups after aggregation.
Example:
Remember:
1️⃣8️⃣ WHERE + GROUP BY + HAVING
You can use all three.
Question:
Conceptually:
For example:
Total_Sales: 15,000,000, Average_Sales: 75,000, Minimum_Sales: 1,000, Maximum_Sales: 500,000, Total_Orders: 200
🔟 Why Do We Need GROUP BY?
Aggregate functions give you an overall summary.
But what if the business asks:
"What are total sales for each region?"
You need to divide the data into groups.
That's what GROUP BY does.
1️⃣1️⃣ Basic GROUP BY
Suppose:
North: 50,000 and 70,000 → Total 120,000
South: 40,000 and 60,000 → Total 100,000
West: 80,000 → Total 80,000
Query:
SELECT
Region,
SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Region;
Result:
North = 120,000, South = 100,000, West = 80,000
Now you've answered:
"How much did each region sell?"
1️⃣2️⃣ GROUP BY Department
Suppose you have:
John - IT - 75,000
Sarah - HR - 60,000
Mike - IT - 82,000
David - Finance - 90,000
Alice - HR - 65,000
Query:
SELECT
Department,
AVG(Salary) AS Average_Salary
FROM Employees
GROUP BY Department;
Result:
Finance: 90,000, HR: 62,500, IT: 78,500
1️⃣3️⃣ GROUP BY with COUNT()
Question:
How many employees are in each department?
SELECT
Department,
COUNT(*) AS Employee_Count
FROM Employees
GROUP BY Department;
Result:
IT: 2, HR: 2, Finance: 1
1️⃣4️⃣ GROUP BY with Multiple Columns
You can group by more than one column.
Suppose your sales data contains:
North Electronics: 80,000
North Furniture: 40,000
South Electronics: 70,000
South Furniture: 50,000
Query:
SELECT
Region,
Category,
SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Region, Category;
Result:
North Electronics = 80,000, North Furniture = 40,000, South Electronics = 70,000, South Furniture = 50,000
This lets you analyze combinations of dimensions.
1️⃣5️⃣ GROUP BY vs PivotTable
This is an important connection.
In Excel:
Region → Rows
Sales → Values
In SQL:
SELECT
Region,
SUM(Sales)
FROM Orders
GROUP BY Region;
The analytical concept is very similar.
You're grouping records and calculating an aggregate.
1️⃣6️⃣ HAVING
Now suppose you want:
"Show only regions where total sales are greater than ₹100,000."
You can't simply use WHERE on the aggregate result.
You use: HAVING
SELECT
Region,
SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Region
HAVING SUM(Sales) > 100000;
Result:
North = 120,000
1️⃣7️⃣ WHERE vs HAVING
This is a very common SQL interview question.
WHERE
Filters individual rows before grouping.
Example:
SELECT *
FROM Orders
WHERE Region = 'North';
HAVING
Filters groups after aggregation.
Example:
SELECT
Region,
SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Region
HAVING SUM(Sales) > 100000;
Remember:
WHERE → Filter rows
HAVING → Filter groups
1️⃣8️⃣ WHERE + GROUP BY + HAVING
You can use all three.
Question:
Find regions where 2026 sales exceed ₹100,000.
Conceptually:
SELECT
Region,
SUM(Sales) AS Total_Sales
FROM Orders
WHERE Order_Date >= '2026-01-01'
AND Order_Date < '2027-01-01'
GROUP BY Region
HAVING SUM(Sales) > 100000;
❤4
Learning this structure will make many analytical SQL problems much easier.
🧪 Practical Interview Challenge
Suppose you have Orders with:
1001 North Electronics 80,000
1002 North Furniture 40,000
1003 South Electronics 70,000
1004 South Furniture 50,000
1005 North Electronics 60,000
Q1. Find total sales.
Q2. Find average sales.
Q3. Find sales by region.
Q4. Count orders by region.
Q5. Find average sales by category.
Q6. Show only regions with sales greater than ₹100,000.
Q7. Sort regions by highest sales.
🏆 Double Tap ❤️ For More
🧪 Practical Interview Challenge
Suppose you have Orders with:
1001 North Electronics 80,000
1002 North Furniture 40,000
1003 South Electronics 70,000
1004 South Furniture 50,000
1005 North Electronics 60,000
Q1. Find total sales.
SELECT SUM(Sales) AS Total_Sales FROM Orders;
Q2. Find average sales.
SELECT AVG(Sales) AS Average_Sales FROM Orders;
Q3. Find sales by region.
SELECT Region, SUM(Sales) AS Total_Sales FROM Orders GROUP BY Region;
Q4. Count orders by region.
SELECT Region, COUNT(*) AS Order_Count FROM Orders GROUP BY Region;
Q5. Find average sales by category.
SELECT Category, AVG(Sales) AS Average_Sales FROM Orders GROUP BY Category;
Q6. Show only regions with sales greater than ₹100,000.
SELECT Region, SUM(Sales) AS Total_Sales FROM Orders GROUP BY Region HAVING SUM(Sales) > 100000;
Q7. Sort regions by highest sales.
SELECT Region, SUM(Sales) AS Total_Sales FROM Orders GROUP BY Region ORDER BY Total_Sales DESC;
🏆 Double Tap ❤️ For More
❤2👍1
𝗧𝗼𝗽 𝗜𝗻-𝗗𝗲𝗺𝗮𝗻𝗱 𝗦𝗸𝗶𝗹𝗹𝘀 𝘁𝗼 𝗙𝘂𝘁𝘂𝗿𝗲-𝗣𝗿𝗼𝗼𝗳 𝗬𝗼𝘂𝗿 𝗖𝗮𝗿𝗲𝗲𝗿 😍
🔥 Skills Worth Learning:
⛓️ Blockchain
☁️ Cloud Computing
♾️ DevOps Engineering
🤖 Artificial Intelligence & Machine Learning
📊 Data Science & Analytics
🔐 Cybersecurity
🎯 Leadership & Communication
𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:-
https://pdlinks.in/i89
Don’t just collect certificates — build projects, gain practical experience and showcase your skills on your resume & LinkedIn.
🔥 Skills Worth Learning:
⛓️ Blockchain
☁️ Cloud Computing
♾️ DevOps Engineering
🤖 Artificial Intelligence & Machine Learning
📊 Data Science & Analytics
🔐 Cybersecurity
🎯 Leadership & Communication
𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:-
https://pdlinks.in/i89
Don’t just collect certificates — build projects, gain practical experience and showcase your skills on your resume & LinkedIn.
🚀 Data Analyst Roadmap — Part 13
🗄️ SQL — Level 3: CASE WHEN, NULL Handling & Conditional Logic
In the previous part, you learned how to summarize data using GROUP BY and aggregate functions.
Now we're going to make SQL more powerful by learning how to create categories, handle missing data, and apply business rules.
These skills are extremely important because real-world datasets are rarely perfect.
You may need to answer questions like:
This is where CASE WHEN and NULL-handling functions become essential.
1️⃣ What Is CASE WHEN?
CASE WHEN allows SQL to make decisions.
Think of it as the SQL equivalent of Excel's:
IF()
For example:
SQL evaluates the conditions and returns the appropriate category.
2️⃣ Basic CASE WHEN
Suppose you have:
Order_ID | Sales
1001 | 120,000
1002 | 75,000
1003 | 30,000
You want to classify orders.
Result:
Order_ID | Sales | Sales_Category
1001 | 120,000 | High
1002 | 75,000 | Medium
1003 | 30,000 | Low
3️⃣ Understand the Evaluation Order
SQL evaluates the WHEN conditions from top to bottom.
For example:
If Sales = 120000:
Is it ≥ 100000? ✅
Return High
Stop evaluating the remaining conditions.
That's why the order of conditions matters.
4️⃣ CASE WHEN with Categories
Suppose employees have salaries.
You want:
₹100,000+ → Senior
₹60,000–99,999 → Mid-Level
Below ₹60,000 → Junior
This is a common data transformation technique.
5️⃣ CASE WHEN with Text Conditions
You can also evaluate text.
Suppose:
Department
IT
HR
Finance
Sales
You want to categorize IT and Finance as:
Business-Critical
and everything else as:
Other
6️⃣ CASE WHEN with AND
You can combine multiple conditions.
Suppose an employee qualifies for a bonus if:
Department = IT
Salary > ₹80,000
Both conditions must be true.
7️⃣ CASE WHEN with OR
Suppose employees from IT or Finance should receive a particular classification.
🗄️ SQL — Level 3: CASE WHEN, NULL Handling & Conditional Logic
In the previous part, you learned how to summarize data using GROUP BY and aggregate functions.
Now we're going to make SQL more powerful by learning how to create categories, handle missing data, and apply business rules.
These skills are extremely important because real-world datasets are rarely perfect.
You may need to answer questions like:
Which orders are High, Medium, or Low value?
How many customers have missing information?
What should we display when a value is NULL?
How many employees are above their target?
This is where CASE WHEN and NULL-handling functions become essential.
1️⃣ What Is CASE WHEN?
CASE WHEN allows SQL to make decisions.
Think of it as the SQL equivalent of Excel's:
IF()
For example:
CASE
WHEN Sales >= 100000 THEN 'High'
WHEN Sales >= 50000 THEN 'Medium'
ELSE 'Low'
END
SQL evaluates the conditions and returns the appropriate category.
2️⃣ Basic CASE WHEN
Suppose you have:
Order_ID | Sales
1001 | 120,000
1002 | 75,000
1003 | 30,000
You want to classify orders.
SELECT
Order_ID,
Sales,
CASE
WHEN Sales >= 100000 THEN 'High'
WHEN Sales >= 50000 THEN 'Medium'
ELSE 'Low'
END AS Sales_Category
FROM Orders;
Result:
Order_ID | Sales | Sales_Category
1001 | 120,000 | High
1002 | 75,000 | Medium
1003 | 30,000 | Low
3️⃣ Understand the Evaluation Order
SQL evaluates the WHEN conditions from top to bottom.
For example:
CASE
WHEN Sales >= 100000 THEN 'High'
WHEN Sales >= 50000 THEN 'Medium'
ELSE 'Low'
END
If Sales = 120000:
Is it ≥ 100000? ✅
Return High
Stop evaluating the remaining conditions.
That's why the order of conditions matters.
4️⃣ CASE WHEN with Categories
Suppose employees have salaries.
You want:
₹100,000+ → Senior
₹60,000–99,999 → Mid-Level
Below ₹60,000 → Junior
SELECT
Name,
Salary,
CASE
WHEN Salary >= 100000 THEN 'Senior'
WHEN Salary >= 60000 THEN 'Mid-Level'
ELSE 'Junior'
END AS Salary_Level
FROM Employees;
This is a common data transformation technique.
5️⃣ CASE WHEN with Text Conditions
You can also evaluate text.
Suppose:
Department
IT
HR
Finance
Sales
You want to categorize IT and Finance as:
Business-Critical
and everything else as:
Other
SELECT
Name,
Department,
CASE
WHEN Department IN ('IT', 'Finance')
THEN 'Business-Critical'
ELSE 'Other'
END AS Department_Type
FROM Employees;
6️⃣ CASE WHEN with AND
You can combine multiple conditions.
Suppose an employee qualifies for a bonus if:
Department = IT
Salary > ₹80,000
SELECT
Name,
Department,
Salary,
CASE
WHEN Department = 'IT'
AND Salary > 80000
THEN 'Bonus Eligible'
ELSE 'Not Eligible'
END AS Bonus_Status
FROM Employees;
Both conditions must be true.
7️⃣ CASE WHEN with OR
Suppose employees from IT or Finance should receive a particular classification.
SELECT
Name,
Department,
CASE
WHEN Department = 'IT'
OR Department = 'Finance'
THEN 'Priority'
ELSE 'Standard'
END AS Employee_Type
FROM Employees;
At least one condition must be true.
8️⃣ CASE WHEN with Aggregation
Here's where CASE WHEN becomes extremely powerful.
Suppose you want to count high-value orders.
You can write:
This counts only orders where Sales is at least ₹100,000.
9️⃣ Conditional SUM
Suppose you want:
Use:
This calculates sales only for qualifying orders.
This technique is called conditional aggregation.
🔟 Conditional Aggregation by Region
Suppose you want to compare:
North sales
South sales
in the same result.
Result:
North_Sales | South_Sales
500,000 | 350,000
This is extremely useful when building analytical reports.
1️⃣1️⃣ CASE WHEN with GROUP BY
You can create categories and then aggregate them.
For example:
This tells you how many orders belong to each sales category.
1️⃣2️⃣ What Is NULL?
NULL represents missing or unknown information.
It is important to understand:
For example:
Salary = 0
means the salary value is explicitly zero.
But:
Salary = NULL
means the value is missing or unknown.
Similarly:
Discount = NULL
doesn't necessarily mean:
Discount = 0
It means:
No value is available.
1️⃣3️⃣ NULL Is Not an Empty String
These are different:
NULL
''
' '
0
NULL
Missing/unknown value.
Empty string
A text value containing no characters.
Space
A string containing a space.
Zero
A numeric value equal to zero.
This distinction is extremely important when cleaning data.
1️⃣4️⃣ Don't Use = NULL
A common beginner mistake is:
This is incorrect for testing NULL.
Instead, use:
To find non-NULL values:
1️⃣5️⃣ Find Missing Values
Suppose you want customers whose phone numbers are missing:
This is useful for data-quality analysis.
1️⃣6️⃣ Count Missing Values
You can use conditional aggregation:
Now you can see:
Total_Customers | Missing_Phone
10,000 | 350
So:
350 customers have missing phone numbers.
1️⃣7️⃣ COALESCE()
COALESCE() returns the first non-NULL value.
For example:
8️⃣ CASE WHEN with Aggregation
Here's where CASE WHEN becomes extremely powerful.
Suppose you want to count high-value orders.
You can write:
SELECT
COUNT(
CASE
WHEN Sales >= 100000 THEN 1
END
) AS High_Value_Orders
FROM Orders;
This counts only orders where Sales is at least ₹100,000.
9️⃣ Conditional SUM
Suppose you want:
Total sales from high-value orders.
Use:
SELECT
SUM(
CASE
WHEN Sales >= 100000 THEN Sales
ELSE 0
END
) AS High_Value_Sales
FROM Orders;
This calculates sales only for qualifying orders.
This technique is called conditional aggregation.
🔟 Conditional Aggregation by Region
Suppose you want to compare:
North sales
South sales
in the same result.
SELECT
SUM(
CASE
WHEN Region = 'North' THEN Sales
ELSE 0
END
) AS North_Sales,
SUM(
CASE
WHEN Region = 'South' THEN Sales
ELSE 0
END
) AS South_Sales
FROM Orders;
Result:
North_Sales | South_Sales
500,000 | 350,000
This is extremely useful when building analytical reports.
1️⃣1️⃣ CASE WHEN with GROUP BY
You can create categories and then aggregate them.
For example:
SELECT
CASE
WHEN Sales >= 100000 THEN 'High'
WHEN Sales >= 50000 THEN 'Medium'
ELSE 'Low'
END AS Sales_Category,
COUNT(*) AS Order_Count
FROM Orders
GROUP BY
CASE
WHEN Sales >= 100000 THEN 'High'
WHEN Sales >= 50000 THEN 'Medium'
ELSE 'Low'
END;
This tells you how many orders belong to each sales category.
1️⃣2️⃣ What Is NULL?
NULL represents missing or unknown information.
It is important to understand:
NULL is not the same as zero.
For example:
Salary = 0
means the salary value is explicitly zero.
But:
Salary = NULL
means the value is missing or unknown.
Similarly:
Discount = NULL
doesn't necessarily mean:
Discount = 0
It means:
No value is available.
1️⃣3️⃣ NULL Is Not an Empty String
These are different:
NULL
''
' '
0
NULL
Missing/unknown value.
Empty string
A text value containing no characters.
Space
A string containing a space.
Zero
A numeric value equal to zero.
This distinction is extremely important when cleaning data.
1️⃣4️⃣ Don't Use = NULL
A common beginner mistake is:
WHERE Email = NULLThis is incorrect for testing NULL.
Instead, use:
WHERE Email IS NULLTo find non-NULL values:
WHERE Email IS NOT NULL1️⃣5️⃣ Find Missing Values
Suppose you want customers whose phone numbers are missing:
SELECT *
FROM Customers
WHERE Phone IS NULL;
This is useful for data-quality analysis.
1️⃣6️⃣ Count Missing Values
You can use conditional aggregation:
SELECT
COUNT(*) AS Total_Customers,
COUNT(
CASE
WHEN Phone IS NULL THEN 1
END
) AS Missing_Phone
FROM Customers;
Now you can see:
Total_Customers | Missing_Phone
10,000 | 350
So:
350 customers have missing phone numbers.
1️⃣7️⃣ COALESCE()
COALESCE() returns the first non-NULL value.
For example:
SELECT
Customer_Name,
COALESCE(Phone, 'Not Available') AS Phone
FROM Customers;
If Phone is NULL, SQL returns:
Not Available
Otherwise, it returns the actual phone number.
1️⃣8️⃣ COALESCE() with Multiple Options
You can provide multiple alternatives.
SQL checks:
1. Work Email
2. Personal Email
3. "No Email"
It returns the first non-NULL value.
This is extremely useful when combining multiple possible sources of information.
1️⃣9️⃣ NULLIF()
NULLIF() returns NULL if two expressions are equal.
For example:
If Sales is:
0
the result becomes:
NULL
Otherwise, the original Sales value is returned.
2️⃣0️⃣ Why NULLIF() Is Useful
Suppose you're calculating:
Profit Margin = Profit / Sales
If Sales is zero:
Profit / Sales
could cause a division-by-zero error.
You can use:
If Sales = 0:
So the division doesn't attempt to divide by zero.
This is an important practical technique.
2️⃣1️⃣ CASE WHEN + NULL
You can also explicitly handle missing values.
Result:
Customer_Name | Phone_Status
John | Available
Sarah | Missing
Mike | Available
This is useful for data-quality reports.
2️⃣2️⃣ Categorize Customers
Suppose you want to classify customers based on total spending:
₹1,00,000+ → VIP
₹50,000+ → Premium
₹20,000+ → Standard
Below ₹20,000 → Basic
After calculating customer-level sales, you could use:
This type of segmentation is widely used in business analytics.
2️⃣3️⃣ CASE WHEN for KPI Status
Suppose the target is:
₹10,00,000
and actual sales are stored in Total_Sales.
You could create:
This turns a raw number into a business interpretation.
2️⃣4️⃣ CASE WHEN for Profitability
Suppose:
Profit > 0 → Profitable
Profit = 0 → Break-even
Profit < 0 → Loss
Use:
This is a simple but powerful analytical transformation.
2️⃣5️⃣ CASE WHEN for Data Cleaning
Suppose your dataset contains:
India
INDIA
india
IN
You can standardize values with a CASE expression:
For a small number of known inconsistencies, this can be useful.
For larger or recurring transformations, you may want to handle standardization upstream in your data pipeline.
2️⃣6️⃣ A Powerful Interview Pattern
You will frequently encounter queries like:
Not Available
Otherwise, it returns the actual phone number.
1️⃣8️⃣ COALESCE() with Multiple Options
You can provide multiple alternatives.
SELECT
COALESCE(Work_Email, Personal_Email, 'No Email')
AS Contact_Email
FROM Customers;
SQL checks:
1. Work Email
2. Personal Email
3. "No Email"
It returns the first non-NULL value.
This is extremely useful when combining multiple possible sources of information.
1️⃣9️⃣ NULLIF()
NULLIF() returns NULL if two expressions are equal.
For example:
NULLIF(Sales, 0)If Sales is:
0
the result becomes:
NULL
Otherwise, the original Sales value is returned.
2️⃣0️⃣ Why NULLIF() Is Useful
Suppose you're calculating:
Profit Margin = Profit / Sales
If Sales is zero:
Profit / Sales
could cause a division-by-zero error.
You can use:
SELECT
Profit / NULLIF(Sales, 0) AS Profit_Margin
FROM Orders;
If Sales = 0:
NULLIF(0,0) → NULLSo the division doesn't attempt to divide by zero.
This is an important practical technique.
2️⃣1️⃣ CASE WHEN + NULL
You can also explicitly handle missing values.
SELECT
Customer_Name,
CASE
WHEN Phone IS NULL THEN 'Missing'
ELSE 'Available'
END AS Phone_Status
FROM Customers;
Result:
Customer_Name | Phone_Status
John | Available
Sarah | Missing
Mike | Available
This is useful for data-quality reports.
2️⃣2️⃣ Categorize Customers
Suppose you want to classify customers based on total spending:
₹1,00,000+ → VIP
₹50,000+ → Premium
₹20,000+ → Standard
Below ₹20,000 → Basic
After calculating customer-level sales, you could use:
CASE
WHEN Total_Sales >= 100000 THEN 'VIP'
WHEN Total_Sales >= 50000 THEN 'Premium'
WHEN Total_Sales >= 20000 THEN 'Standard'
ELSE 'Basic'
END
This type of segmentation is widely used in business analytics.
2️⃣3️⃣ CASE WHEN for KPI Status
Suppose the target is:
₹10,00,000
and actual sales are stored in Total_Sales.
You could create:
CASE
WHEN Total_Sales >= 1000000 THEN 'Target Achieved'
ELSE 'Below Target'
END
This turns a raw number into a business interpretation.
2️⃣4️⃣ CASE WHEN for Profitability
Suppose:
Profit > 0 → Profitable
Profit = 0 → Break-even
Profit < 0 → Loss
Use:
CASE
WHEN Profit > 0 THEN 'Profitable'
WHEN Profit = 0 THEN 'Break-even'
ELSE 'Loss'
END AS Profit_Status
This is a simple but powerful analytical transformation.
2️⃣5️⃣ CASE WHEN for Data Cleaning
Suppose your dataset contains:
India
INDIA
india
IN
You can standardize values with a CASE expression:
CASE
WHEN Country IN ('India', 'INDIA', 'india', 'IN')
THEN 'India'
ELSE Country
END AS Standardized_Country
For a small number of known inconsistencies, this can be useful.
For larger or recurring transformations, you may want to handle standardization upstream in your data pipeline.
2️⃣6️⃣ A Powerful Interview Pattern
You will frequently encounter queries like:
SELECT
Region,
SUM(Sales) AS Total_Sales,
CASE
WHEN SUM(Sales) >= 1000000
THEN 'Target Achieved'
ELSE 'Below Target'
END AS Target_Status
FROM Orders
GROUP BY Region;
This combines:
GROUP BY
SUM()
CASE WHEN
to create a business-ready result.
2️⃣7️⃣ Important SQL Execution Concept
A simplified logical order of SQL processing is:
FROM
↓
WHERE
↓
GROUP BY
↓
HAVING
↓
SELECT
↓
ORDER BY
This helps explain why SQL behaves differently from how the query appears visually.
For example:
Think:
Get data → filter rows → group → calculate → filter groups → sort
Understanding SQL's logical processing order will become increasingly important as queries get more complex.
🧪 Practical Interview Challenge
Suppose you have:
Orders
Order_ID | Region | Sales | Profit
1001 | North | 120,000 | 20,000
1002 | South | 75,000 | 10,000
1003 | North | 30,000 | -5,000
1004 | West | 150,000 | 30,000
1005 | South | NULL | 8,000
Q1. Categorize orders by sales.
Q2. Find orders with missing sales.
Q3. Replace missing sales with zero for display.
Remember: this changes the display/calculation result, not necessarily the underlying data.
Q4. Categorize profitability.
Q5. Count high-value orders.
Q6. Calculate profit margin safely.
🏆 Double Tap ❤️ For More
GROUP BY
SUM()
CASE WHEN
to create a business-ready result.
2️⃣7️⃣ Important SQL Execution Concept
A simplified logical order of SQL processing is:
FROM
↓
WHERE
↓
GROUP BY
↓
HAVING
↓
SELECT
↓
ORDER BY
This helps explain why SQL behaves differently from how the query appears visually.
For example:
SELECT
Region,
SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Region
HAVING SUM(Sales) > 100000
ORDER BY Total_Sales DESC;
Think:
Get data → filter rows → group → calculate → filter groups → sort
Understanding SQL's logical processing order will become increasingly important as queries get more complex.
🧪 Practical Interview Challenge
Suppose you have:
Orders
Order_ID | Region | Sales | Profit
1001 | North | 120,000 | 20,000
1002 | South | 75,000 | 10,000
1003 | North | 30,000 | -5,000
1004 | West | 150,000 | 30,000
1005 | South | NULL | 8,000
Q1. Categorize orders by sales.
SELECT
Order_ID,
Sales,
CASE
WHEN Sales >= 100000 THEN 'High'
WHEN Sales >= 50000 THEN 'Medium'
ELSE 'Low'
END AS Sales_Category
FROM Orders;
Q2. Find orders with missing sales.
SELECT *
FROM Orders
WHERE Sales IS NULL;
Q3. Replace missing sales with zero for display.
SELECT
Order_ID,
COALESCE(Sales, 0) AS Sales
FROM Orders;
Remember: this changes the display/calculation result, not necessarily the underlying data.
Q4. Categorize profitability.
SELECT
Order_ID,
CASE
WHEN Profit > 0 THEN 'Profitable'
WHEN Profit = 0 THEN 'Break-even'
ELSE 'Loss'
END AS Profit_Status
FROM Orders;
Q5. Count high-value orders.
SELECT
COUNT(
CASE
WHEN Sales >= 100000 THEN 1
END
) AS High_Value_Orders
FROM Orders;
Q6. Calculate profit margin safely.
SELECT
Order_ID,
Profit / NULLIF(Sales, 0) AS Profit_Margin
FROM Orders;
🏆 Double Tap ❤️ For More
❤9
🚀 𝗙𝗥𝗘𝗘 𝗚𝗲𝗻𝗔𝗜 + 𝗖𝗹𝗮𝘂𝗱𝗲 𝗢𝗻𝗹𝗶𝗻𝗲 𝗠𝗮𝘀𝘁𝗲𝗿𝗰𝗹𝗮𝘀𝘀 😍
Want to work faster, create better content and save hours every week using AI?
Join this beginner-friendly masterclass and discover how to use 𝟮𝟱+ powerful AI tools to:
✅ Automate repetitive tasks
✅ Create professional content in minutes
✅ Improve productivity and efficiency
✅ Save valuable time every week
✅ Use GenAI and Claude effectively
💡 No technical knowledge or previous AI experience required!
🔗 𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 👇
https://pdlink.in/46wurp9
⚡ Limited slots available—register now and start working smarter with AI!
Want to work faster, create better content and save hours every week using AI?
Join this beginner-friendly masterclass and discover how to use 𝟮𝟱+ powerful AI tools to:
✅ Automate repetitive tasks
✅ Create professional content in minutes
✅ Improve productivity and efficiency
✅ Save valuable time every week
✅ Use GenAI and Claude effectively
💡 No technical knowledge or previous AI experience required!
🔗 𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 👇
https://pdlink.in/46wurp9
⚡ Limited slots available—register now and start working smarter with AI!