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.