🚀 SQL Roadmap 2026 — Part 2

SQL SELECT Statement & Retrieving Data

Now that you understand databases, tables, rows, columns, primary keys, and foreign keys, it's time to learn the most fundamental SQL command: SELECT.

Almost every SQL analysis starts with retrieving data.

1️⃣ What is SELECT?

SELECT is used to retrieve data from one or more columns in a table.

Basic syntax:

SELECT column_name
FROM table_name;

Example:

SELECT customer_name
FROM customers;

2️⃣ Select Multiple Columns

You can retrieve multiple columns by separating them with commas.

SELECT
    customer_id,
    customer_name,
    city
FROM customers;

Result:

customer_id | customer_name | city

1 | Rahul | Mumbai

2 | Priya | Delhi

3 | Amit | Pune

3️⃣ Select All Columns Using *

If you want every column:

SELECT *
FROM customers;

* means all columns.

⚠️ Interview Tip: Although SELECT * is convenient while exploring data, avoid relying on it in production queries. Prefer selecting only needed columns.

4️⃣ Column Aliases

Use AS to give a column a different name in the result.

SELECT
    customer_name AS name,
    city AS location
FROM customers;

The original table is not changed.

5️⃣ Aliases Without AS

SELECT
    customer_name name,
    city location
FROM customers;

However, using AS is generally clearer for beginners.

6️⃣ Calculations Inside SELECT

SELECT
    product_name,
    price,
    price * 0.90 AS discounted_price
FROM products;

7️⃣ Arithmetic Operators

+ Addition, - Subtraction, * Multiplication, / Division

SELECT
    product_name,
    selling_price,
    cost_price,
    selling_price - cost_price AS profit
FROM products;

8️⃣ Using Expressions

SELECT
    product_name,
    quantity,
    unit_price,
    quantity * unit_price AS total_value
FROM order_items;

9️⃣ DISTINCT

Removes duplicate values.

SELECT DISTINCT city
FROM customers;

🔟 DISTINCT Across Multiple Columns

SELECT DISTINCT
    city,
    customer_segment
FROM customers;

1️⃣1️⃣ Using SELECT With Text

SELECT
    customer_name,
    'Active Customer' AS status
FROM customers;

1️⃣2️⃣ Combining Columns

SELECT
    first_name,
    last_name,
    CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

1️⃣3️⃣ SELECT With a Condition

SELECT
    customer_name,
    city
FROM customers
WHERE city = 'Mumbai';

1️⃣5️⃣ SQL Query Structure

At this stage, learn this basic pattern:

SELECT column1, column2
FROM table_name;

SELECT column1, column2
FROM table_name
WHERE condition;

1️⃣6️⃣ A Real-World Example

Manager asks: "Show me product name, selling price, cost price, and profit"

SELECT
    product_name,
    selling_price,
    cost_price,
    selling_price - cost_price AS profit
FROM products;

🧠 Common Beginner Mistakes

❌ Mistake 1: Forgetting FROM

Wrong: SELECT customer_name;

Correct: SELECT customer_name FROM customers;

❌ Mistake 2: Using commas incorrectly

Wrong: SELECT customer_id customer_name city

Correct: Use commas

❌ Mistake 3: Using quotes around column names unnecessarily

SELECT 'customer_name' treats it as text, not column.

❌ Mistake 4: Confusing *

SELECT * means return all columns, not all rows.

🎯 Practice Questions

Q1. Display all columns from employees

Q2. Display employee_name, salary, department_id

Q3. Display unique cities from customers

Q4. Display product_name, price and 15% discounted price

Q5. Display product_name, selling_price, cost_price and profit

Q6. Display customer names with alias Customer

Q7. Display employee name and salary increased by 10%

✅ Answers

-- A1
SELECT * FROM employees;

-- A2
SELECT employee_name, salary, department_id FROM employees;

-- A3
SELECT DISTINCT city FROM customers;

-- A4
SELECT product_name, price, price * 0.85 AS discounted_price FROM products;

-- A5
SELECT product_name, selling_price, cost_price, selling_price - cost_price AS profit FROM products;

-- A6
SELECT customer_name AS Customer FROM customers;

-- A7
SELECT employee_name, salary, salary * 1.10 AS increased_salary FROM employees;

💼 Interview Questions

1. What does SELECT do? → Retrieves data from columns.

2. What does SELECT * mean? → Retrieves all columns.

3. What is DISTINCT? → Removes duplicate combinations.

4. What is an alias? → Temporary name for clarity.

5. Can SQL perform calculations? → Yes, arithmetic expressions directly in queries.

Double Tap ❤️ For Part-3

🚀 SQL Roadmap 2026 — Part 4

Sorting, Limiting & Selecting the Right Records

In the previous part, you learned how to filter data using WHERE.

Now we'll learn how to control which records appear first, last, or how many records are returned.

These concepts are simple, but they are extremely important for SQL interviews and real-world analytics.

1️⃣ ORDER BY

ORDER BY is used to sort query results.

Syntax

SELECT column1, column2
FROM table_name
ORDER BY column_name;

By default, SQL sorts in ascending order (ASC).

Example:

SELECT
    employee_name,
    salary
FROM employees
ORDER BY salary;

This displays employees from the lowest salary to the highest.

2️⃣ ASC — Ascending Order

You can explicitly specify ASC.

SELECT
    employee_name,
    salary
FROM employees
ORDER BY salary ASC;

For numbers:

100, 250, 500, 1000

For text:

Amit, Neha, Priya, Rahul

3️⃣ DESC — Descending Order

Use DESC when you want the highest values first.

SELECT
    employee_name,
    salary
FROM employees
ORDER BY salary DESC;

Result:

Amit: 1200000, Priya: 950000, Rahul: 850000, Neha: 650000

This is one of the most commonly used SQL patterns.

4️⃣ Real-World Example: Top Salaries

Business requirement:

Find the highest-paid employees.

SELECT
    employee_name,
    salary
FROM employees
ORDER BY salary DESC;

But this might return thousands of employees.

That's where LIMIT becomes useful.

5️⃣ LIMIT

LIMIT restricts the number of rows returned.

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

This returns only the top 5 employees by salary.

Think of it as:

ORDER BY DESC → Highest first → LIMIT 5 → Keep first 5

6️⃣ Top 10 Products by Price

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

Very common in analytics.

7️⃣ LIMIT Without ORDER BY

You technically can write:

SELECT *
FROM customers
LIMIT 10;

But this means:

Give me 10 rows.

It does not mean:

Give me the first 10 rows according to some meaningful business order.

Without ORDER BY, the returned order should generally not be relied upon.

If you want the top 10 customers by revenue:

SELECT
    customer_id,
    revenue
FROM customer_revenue
ORDER BY revenue DESC
LIMIT 10;

8️⃣ OFFSET

OFFSET allows you to skip a number of rows.

Example:

SELECT
    employee_name,
    salary
FROM employees
ORDER BY salary DESC
LIMIT 5 OFFSET 5;

This skips the first 5 rows and returns the next 5.

Conceptually:

Rows 1–5 → Skip, Rows 6–10 → Return

9️⃣ Pagination

LIMIT and OFFSET are often used for pagination.

For example:

Page 1

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

Page 2

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

Page 3

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

The general pattern is:

Page 1 → OFFSET 0, Page 2 → OFFSET 10, Page 3 → OFFSET 20

🔟 Sorting by Multiple Columns

You can sort using more than one column.

Example:

SELECT
    employee_name,
    department,
    salary
FROM employees
ORDER BY department ASC, 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;

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;

🎯 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

𝗧𝗼𝗽 𝗜𝗻-𝗗𝗲𝗺𝗮𝗻𝗱 𝗦𝗸𝗶𝗹𝗹𝘀 𝘁𝗼 𝗙𝘂𝘁𝘂𝗿𝗲-𝗣𝗿𝗼𝗼𝗳 𝗬𝗼𝘂𝗿 𝗖𝗮𝗿𝗲𝗲𝗿 😍

🔥 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.

🚀 𝗙𝗥𝗘𝗘 𝗚𝗲𝗻𝗔𝗜 + 𝗖𝗹𝗮𝘂𝗱𝗲 𝗢𝗻𝗹𝗶𝗻𝗲 𝗠𝗮𝘀𝘁𝗲𝗿𝗰𝗹𝗮𝘀𝘀 😍

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!

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

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.

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

📊 𝗠𝗮𝘀𝘁𝗲𝗿 𝗘𝘅𝗰𝗲𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘 | 𝟱 𝗣𝗼𝘄𝗲𝗿𝗳𝘂𝗹 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 🚀

🔥 Top 5 FREE Excel Courses:

1️⃣ Goldman Sachs – Excel Skills for Business
2️⃣ PwC – Problem Solving with Excel
3️⃣ Corporate Finance Institute – Excel Fundamentals
4️⃣ Great Learning – Excel for Beginners
5️⃣ Simplilearn – Introduction to MS Excel

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

https://pdlink.in/3UQ8S09

🚀 Learn Excel for FREE and upgrade your career skills!

🚀 𝗟𝗲𝘃𝗲𝗹 𝗨𝗽 𝗬𝗼𝘂𝗿 𝗖𝗮𝗿𝗲𝗲𝗿 𝘄𝗶𝘁𝗵 𝗙𝗥𝗘𝗘 𝗠𝗶𝗰𝗿𝗼𝘀𝗼𝗳𝘁 𝗟𝗲𝗮𝗿𝗻𝗶𝗻𝗴! 💻

Microsoft-focused learning paths can help you strengthen your resume and prepare for in-demand tech and data roles.

🔥 Top 5 Courses / Certification Paths:
✅ Beginner-friendly options
✅ Build practical, job-ready skills
✅ Learn Azure, Power BI, Excel & SQL
✅ Strengthen your resume & career profile

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

https://pdlink.in/3UNpPs7

💫Perfect for students, freshers, data analysts and professionals looking to upgrade their skills.

🎓 𝗧𝗼𝗽 𝗖𝗼𝗺𝗽𝗮𝗻𝗶𝗲𝘀 𝗢𝗳𝗳𝗲𝗿𝗶𝗻𝗴 𝗙𝗥𝗘𝗘 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 🚀

Learn in-demand skills • Add valuable credentials to your resume

🏢 TATA :- https://pdlink.in/3QiwLvx

💻 Infosys :- https://pdlink.in/4eBH3Aa

⚡ IBM :- https://pdlink.in/45KgqDR

💫 Amazon :- https://pdlink.in/47XuBGz

🌐 Cisco :- https://pdlink.in/4gaeVVV

🪟 Microsoft :- https://pdlink.in/4zhGTX6

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

🚀 SQL Roadmap 2026 — Part 6

GROUP BY & HAVING — Analyzing Data by Categories 📊

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

What is the total revenue?

How many customers do we have?

But real-world business questions are usually more specific:

What is the revenue by city?

How many employees are there in each department?

Which products generated the most revenue?

That's where GROUP BY comes in.

1️⃣ What is GROUP BY?

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

Basic Syntax

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

Example:

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

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

2️⃣ Why Do We Need GROUP BY?

Without GROUP BY:

SELECT COUNT(*) AS total_employees
FROM employees;

Result: 1000

This answers: How many employees are there?

But:

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

Result:

• IT | 350

• Finance | 200

• HR | 120

• Sales | 330

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

3️⃣ GROUP BY With COUNT()

This is probably the most common GROUP BY pattern.

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

4️⃣ GROUP BY With SUM()

Suppose you want revenue by city.

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

This is a common business KPI.

5️⃣ GROUP BY With AVG()

Calculate average salary by department:

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

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

You can use multiple aggregate functions.

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

7️⃣ Multiple Aggregations

You aren't limited to one metric.

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

This is the foundation of many analytical reports.

8️⃣ GROUP BY Multiple Columns

You can group by more than one column.

Example: Count customers by city and customer segment.

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

SQL creates a group for each unique combination.

9️⃣ Understanding Multiple GROUP BY Columns

Grouping by GROUP BY city, segment creates groups like:

• Mumbai + Premium

• Mumbai + Standard

• Delhi + Premium

The combination matters.

🔟 GROUP BY With WHERE

WHERE filters rows before grouping.

Example: Calculate revenue by city for completed orders only.

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

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

1️⃣1️⃣ WHERE vs GROUP BY

WHERE Answers: Which rows should be included?

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

1️⃣2️⃣ What is HAVING?

HAVING filters groups after aggregation.

Example: Find departments with more than 100 employees.

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

1️⃣3️⃣ WHERE vs HAVING

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

WHERE filters individual rows. WHERE salary > 500000

HAVING filters groups. HAVING AVG(salary) > 800000

Remember:

WHERE → Filter rows

GROUP BY → Create groups

HAVING → Filter groups

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

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

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

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

Very useful for customer analytics.

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

1️⃣6️⃣ GROUP BY With CASE

You can create business categories and then group them.

SELECT
    CASE
        WHEN salary >= 1000000 THEN 'High'
        WHEN salary >= 600000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_band,
    COUNT(*) AS employee_count
FROM employees
GROUP BY
    CASE
        WHEN salary >= 1000000 THEN 'High'
        WHEN salary >= 600000 THEN 'Medium'
        ELSE 'Low'
    END;

1️⃣7️⃣ GROUP BY Dates

This is extremely important for Data Analysts.

Example: Calculate monthly revenue.

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

1️⃣8️⃣ Daily Sales

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

1️⃣9️⃣ Monthly Order Count

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

2️⃣0️⃣ Monthly Customer Count

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

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

2️⃣1️⃣ GROUP BY With ORDER BY

Example: Find departments with the highest average salary.

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

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

A powerful analytical pattern:

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

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

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

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

2️⃣4️⃣ Common GROUP BY Error

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

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

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

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

1. Included in GROUP BY

2. Or aggregated

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

2️⃣6️⃣ SQL Query Pattern to Memorize

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

Example:

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

🧠 Logical Processing Order

A useful simplified model is:

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

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

💼 SQL Interview Questions

Q1. What is GROUP BY?

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

Q2. What is the difference between WHERE and HAVING?

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

Q3. Can GROUP BY contain multiple columns?

Yes. GROUP BY city, category;

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

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

Q5. Can you use aggregate functions in WHERE?

Generally no. Use HAVING.

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

To count unique customers rather than counting every transaction.

🎯 Practice Questions

Q1. Count employees in each department.

Q2. Calculate total revenue by product category.

Q3. Calculate average salary by department.

Q4. Find the highest salary in each department.

Q5. Count customers by city.

Q6. Find cities with more than 500 customers.

Q7. Calculate monthly revenue.

Q8. Calculate monthly unique customers.

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

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

✅ Answers

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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

🔥 Mini Challenge

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

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

Solution:

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

Double Tap ❤️ For Part-7

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

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

🎯 Interview Preparation Guides For:

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

𝐋𝐢𝐧𝐤 👇:-

https://pdlink.in/4i6HkgN

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

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

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

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

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

https://pdlink.in/4gNYHk7

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