SELECT with expressions and calculations in SQL - Step-by-Step Execution

Concept Flow - SELECT with expressions and calculations

Start: SELECT statement

↓

Identify columns and expressions

↓

Calculate expressions for each row

↓

Build result set with calculated values

↓

Return result set to user

The SELECT statement processes each row, calculates expressions, and returns the results.

Execution Sample

SQL

SELECT price, quantity, price * quantity AS total
FROM sales;

This query selects price and quantity columns and calculates total as price times quantity for each row.

Execution Table

Step	Row Data (price, quantity)	Expression Calculated	Output Row
1	(10, 2)	10 * 2 = 20	(10, 2, 20)
2	(5, 5)	5 * 5 = 25	(5, 5, 25)
3	(8, 3)	8 * 3 = 24	(8, 3, 24)
4	No more rows	No calculation	End of result set

💡 All rows processed, query returns result set with calculated totals.

Variable Tracker

Variable	Start	After Row 1	After Row 2	After Row 3	Final
price	N/A	10	5	8	N/A
quantity	N/A	2	5	3	N/A
total	N/A	20	25	24	N/A

Key Moments - 2 Insights

Why does the expression 'price * quantity' get calculated for each row separately?

Because the SELECT processes each row individually, calculating expressions using that row's column values, as shown in execution_table rows 1 to 3.

What happens if there are no rows in the table?

The query returns an empty result set immediately, as shown in execution_table row 4 where no more rows exist.

Visual Quiz - 3 Questions

Look at the execution table, what is the 'total' value at step 2?

A 10

B 20

C 25

D 5

💡 Hint

Check the 'Expression Calculated' column at step 2 in the execution_table.

At which step does the query finish processing all rows?

A Step 3

B Step 4

C Step 2

D Step 1

💡 Hint

Look for the row indicating 'No more rows' in the execution_table.

If the quantity in the second row changed from 5 to 4, what would be the new total at step 2?

A 20

B 25

C 24

D 21

💡 Hint

Multiply price and quantity values from variable_tracker for row 2.

Concept Snapshot

SELECT with expressions lets you calculate new values from columns.
Syntax: SELECT col1, col2, expression AS alias FROM table;
Each row's expression is calculated separately.
Results include original columns plus calculated values.
Useful for totals, differences, or any math on data.

Full Transcript

This visual execution shows how a SELECT query with expressions works. The query selects price and quantity columns from a sales table and calculates a new column total by multiplying price and quantity for each row. The execution table traces each row: it shows the input values, the calculation done, and the output row with the new total. Variables price, quantity, and total change as each row is processed. The query stops when no more rows remain. Key points include that expressions are calculated per row and that an empty table returns no results. The quiz tests understanding of these steps and calculations.