SELECT with WHERE, ORDER BY, GROUP BY in DBMS Theory - Step-by-Step Execution

Concept Flow - SELECT with WHERE, ORDER BY, GROUP BY

Start Query

↓

FROM: Choose Table

↓

WHERE: Filter Rows

↓

GROUP BY: Group Rows

↓

SELECT: Choose Columns/Aggregates

↓

ORDER BY: Sort Result

↓

Return Result Set

The query starts by selecting the table, filters rows with WHERE, groups them with GROUP BY, selects columns or aggregates, then sorts the result with ORDER BY before returning.

Execution Sample

DBMS Theory

SELECT department, COUNT(*) AS emp_count
FROM employees
WHERE salary > 3000
GROUP BY department
ORDER BY emp_count DESC;

This query finds departments with employees earning more than 3000, counts employees per department, and sorts departments by employee count descending.

Analysis Table

Step	Action	Input Rows	Output Rows	Details
1	FROM employees	All employees table	All employees	Start with full employees table
2	WHERE salary > 3000	All employees	Filtered employees	Keep only employees with salary > 3000
3	GROUP BY department	Filtered employees	Grouped rows	Group employees by department
4	SELECT department, COUNT(*) AS emp_count	Grouped rows	Aggregated rows	Count employees per department
5	ORDER BY emp_count DESC	Aggregated rows	Sorted rows	Sort departments by employee count descending
6	Return Result Set	Sorted rows	Final result	Output the sorted grouped data

💡 Query completes after sorting and returning the final grouped and filtered result set.

State Tracker

Variable	Start	After WHERE	After GROUP BY	After SELECT	After ORDER BY
rows	All employees	Employees with salary > 3000	Employees grouped by department	Departments with emp_count	Departments sorted by emp_count desc

Key Insights - 3 Insights

Why do we apply WHERE before GROUP BY?

WHERE filters rows before grouping, so only matching rows are grouped. See execution_table step 2 filters rows before step 3 groups them.

What does ORDER BY do after GROUP BY?

ORDER BY sorts the grouped result. It does not affect grouping but changes the order of final rows. See execution_table step 5.

Can SELECT include columns not in GROUP BY?

Only aggregated columns or those in GROUP BY can be selected. Here, department is grouped and emp_count is aggregate. See execution_table step 4.

Visual Quiz - 3 Questions

Look at the execution_table, what happens at step 2?

A Rows are grouped by department

B Rows are sorted by employee count

C Rows are filtered where salary > 3000

D Rows are selected with department and count

💡 Hint

Check the 'Action' and 'Details' columns at step 2 in execution_table.

At which step does the query count employees per department?

A Step 4

B Step 2

C Step 3

D Step 5

💡 Hint

Look for 'COUNT(*)' in the 'Action' column in execution_table.

If we remove WHERE clause, how does the 'After WHERE' variable change?

A It becomes empty

B It stays as all employees

C It groups employees immediately

D It sorts employees by salary

💡 Hint

Refer to variable_tracker 'After WHERE' column and think what happens without filtering.

Concept Snapshot

SELECT with WHERE, GROUP BY, ORDER BY:
- FROM chooses table
- WHERE filters rows
- GROUP BY groups rows
- SELECT picks columns/aggregates
- ORDER BY sorts final result
Use WHERE before GROUP BY to filter data
Use ORDER BY last to sort output

Full Transcript

This visual execution shows how a SQL query with SELECT, WHERE, GROUP BY, and ORDER BY runs step-by-step. First, the query starts with the full table. Then WHERE filters rows by condition. Next, GROUP BY groups rows by a column. SELECT chooses columns and aggregates like COUNT. ORDER BY sorts the grouped results. Variables track the rows at each step. Key moments explain why WHERE comes before GROUP BY and how ORDER BY sorts after grouping. The quiz tests understanding of each step's role.

Practice

1. What does the WHERE clause do in a SQL SELECT statement?

easy

A. Filters rows based on a condition

B. Groups rows with the same values

C. Sorts the result set

D. Joins two tables together

Solution

1. Step 1: Understand the role of WHERE clause

   The WHERE clause filters rows that meet a specific condition before any grouping or sorting.

2. Step 2: Differentiate from GROUP BY and ORDER BY

   GROUP BY groups rows, ORDER BY sorts rows, but WHERE filters rows.

3. Final Answer:

   Filters rows based on a condition -> Option A

4. Quick Check:

   WHERE = Filters rows [OK]

Hint: WHERE filters rows before grouping or sorting [OK]

Common Mistakes:

• Confusing WHERE with GROUP BY
• Thinking WHERE sorts data
• Assuming WHERE joins tables

2. Which of the following is the correct syntax to select all columns from a table named Employees where the Department is 'Sales'?

easy

A. SELECT * FROM Employees WHERE Department = 'Sales';

B. SELECT * Employees WHERE Department == 'Sales';

C. SELECT * FROM Employees WHERE Department := 'Sales';

D. SELECT * FROM Employees WHERE Department = Sales;

Solution

1. Step 1: Check correct SELECT syntax

   The correct syntax uses SELECT * FROM table WHERE condition with single equals and quotes around string values.

2. Step 2: Identify syntax errors in other options

   SELECT * Employees WHERE Department == 'Sales'; uses '==' which is invalid in SQL; SELECT * FROM Employees WHERE Department := 'Sales'; uses ':=' which is not standard; SELECT * FROM Employees WHERE Department = Sales; misses quotes around 'Sales'.

3. Final Answer:

   SELECT * FROM Employees WHERE Department = 'Sales'; -> Option A

4. Quick Check:

   Correct WHERE syntax uses = and quotes for strings [OK]

Hint: Use single = and quotes for string in WHERE [OK]

Common Mistakes:

• Using == instead of =
• Missing quotes around string values
• Wrong assignment operator :=

3. Given the table Sales with columns Region and Amount, what will this query return?

SELECT Region, SUM(Amount) FROM Sales WHERE Amount > 100 GROUP BY Region ORDER BY SUM(Amount) DESC;

medium

A. Sum of all sales amounts regardless of region

B. All sales records with amount over 100, sorted by region

C. Total sales amount per region for sales over 100, sorted highest to lowest

D. Syntax error due to ORDER BY on aggregate

Solution

1. Step 1: Analyze WHERE and GROUP BY

   WHERE filters rows with Amount > 100, GROUP BY groups rows by Region, SUM(Amount) calculates total per group.

2. Step 2: Understand ORDER BY with aggregate

   ORDER BY SUM(Amount) DESC sorts groups by total sales descending.

3. Final Answer:

   Total sales amount per region for sales over 100, sorted highest to lowest -> Option C

4. Quick Check:

   WHERE filters, GROUP BY groups, ORDER BY sorts aggregates [OK]

Hint: ORDER BY can sort aggregates after GROUP BY [OK]

Common Mistakes:

• Thinking ORDER BY on aggregate causes error
• Ignoring WHERE filter before grouping
• Confusing sorting by region instead of sum

4. Identify the error in this SQL query:

SELECT Department, COUNT(*) FROM Employees ORDER BY Department WHERE COUNT(*) > 5 GROUP BY Department;

medium

A. Missing semicolon at the end

B. ORDER BY appears before WHERE and GROUP BY, causing syntax error

C. WHERE cannot filter on aggregate functions like COUNT(*)

D. COUNT(*) cannot be used with GROUP BY

Solution

1. Step 1: Check SQL clause order

   The correct order is SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. ORDER BY before WHERE is invalid.

2. Step 2: Confirm other clauses

   COUNT(*) with GROUP BY is valid; filtering on aggregates requires HAVING, not WHERE; but main syntax error is clause order.

3. Final Answer:

   ORDER BY appears before WHERE and GROUP BY, causing syntax error -> Option B

4. Quick Check:

   Clause order matters: WHERE before GROUP BY, ORDER BY last [OK]

Hint: Remember SQL clause order: WHERE, GROUP BY, HAVING, ORDER BY [OK]

Common Mistakes:

• Placing ORDER BY before WHERE
• Using WHERE to filter aggregates instead of HAVING
• Ignoring clause order rules

5. You have a table Orders with columns CustomerID, OrderDate, and Total. Write a query to find customers who placed more than 3 orders in 2023, showing their CustomerID and total number of orders, sorted by number of orders descending.

hard

A. SELECT CustomerID, COUNT(*) FROM Orders WHERE YEAR(OrderDate) == 2023 GROUP BY CustomerID HAVING COUNT(*) > 3 ORDER BY COUNT(*) DESC;

B. SELECT CustomerID, COUNT(*) FROM Orders GROUP BY CustomerID WHERE YEAR(OrderDate) = 2023 HAVING COUNT(*) > 3 ORDER BY COUNT(*) DESC;

C. SELECT CustomerID, COUNT(*) FROM Orders WHERE OrderDate >= '2023-01-01' AND OrderDate <= '2023-12-31' GROUP BY CustomerID HAVING COUNT(*) >= 3 ORDER BY COUNT(*) DESC;

D. SELECT CustomerID, COUNT(*) FROM Orders WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31' GROUP BY CustomerID HAVING COUNT(*) > 3 ORDER BY COUNT(*) DESC;

Solution

1. Step 1: Filter orders in 2023

   Use WHERE with OrderDate between '2023-01-01' and '2023-12-31' to select orders in 2023.

2. Step 2: Group by CustomerID and filter groups

   GROUP BY CustomerID groups orders per customer; HAVING COUNT(*) > 3 filters customers with more than 3 orders.

3. Step 3: Sort results

   ORDER BY COUNT(*) DESC sorts customers by number of orders from highest to lowest.

4. Final Answer:

   SELECT CustomerID, COUNT(*) FROM Orders WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31' GROUP BY CustomerID HAVING COUNT(*) > 3 ORDER BY COUNT(*) DESC; -> Option D

5. Quick Check:

   Filter dates, group, HAVING filter, then ORDER BY count desc [OK]

Hint: Use HAVING to filter groups after GROUP BY [OK]

Common Mistakes:

• Using WHERE to filter aggregates instead of HAVING
• Incorrect date filtering syntax
• Using >= 3 instead of > 3 for count condition