Why computed values add flexibility in MySQL - Visual Breakdown

Concept Flow - Why computed values add flexibility

Start with base table

↓

Define computed column

↓

Query uses computed column

↓

Results show dynamic values

↓

Change base data or formula

↓

Output updates automatically

This flow shows how computed values are created from base data and used in queries, allowing results to update automatically when data or formulas change.

Execution Sample

MySQL

CREATE TABLE sales (
  id INT,
  price DECIMAL(10,2),
  quantity INT
);

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

This code creates a sales table and selects price, quantity, and a computed total (price times quantity) for each row.

Execution Table

Step	Action	price	quantity	Computed total	Output row
1	Read first row	10.00	2	10.00 * 2 = 20.00	(10.00, 2, 20.00)
2	Read second row	5.50	4	5.50 * 4 = 22.00	(5.50, 4, 22.00)
3	Read third row	7.25	3	7.25 * 3 = 21.75	(7.25, 3, 21.75)
4	No more rows				Query ends

💡 All rows processed, query returns computed totals dynamically

Variable Tracker

Variable	Start	After 1	After 2	After 3	Final
price	N/A	10.00	5.50	7.25	7.25
quantity	N/A	2	4	3	3
total	N/A	20.00	22.00	21.75	21.75

Key Moments - 2 Insights

Why does the total column change automatically when price or quantity changes?

Because total is computed on the fly using price * quantity in the query, as shown in execution_table rows 1-3, so any change in price or quantity affects total immediately.

Is the total stored in the table or calculated each time?

It is calculated each time the query runs, not stored, which adds flexibility to reflect current data instantly, as seen in the computed total column in execution_table.

Visual Quiz - 3 Questions

Look at the execution_table at step 2, what is the computed total?

A 21.75

B 20.00

C 22.00

D 5.50

💡 Hint

Check the 'Computed total' column in execution_table row with Step 2

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 with 'No more rows' in the Action column in execution_table

If quantity in the first row changed from 2 to 3, what would be the new computed total at step 1?

A 30.00

B 20.00

C 10.00

D 13.00

💡 Hint

Multiply price 10.00 by new quantity 3 as in variable_tracker and execution_table logic

Concept Snapshot

Computed values are calculated dynamically in queries using expressions.
They reflect current data without storing extra columns.
Syntax example: SELECT price, quantity, price * quantity AS total FROM table;
This adds flexibility by updating results automatically when data changes.

Full Transcript

This lesson shows how computed values in SQL queries add flexibility by calculating results dynamically from base data. We start with a sales table having price and quantity columns. The query selects these columns plus a computed total column calculated as price times quantity. Step-by-step, each row's price and quantity are read, the total is computed, and output is generated. The total is not stored but calculated on the fly, so if price or quantity changes, the total updates automatically. This dynamic calculation allows flexible, up-to-date results without extra storage. Key moments include understanding why totals change automatically and that totals are not stored but computed each query run. The visual quiz tests understanding of computed totals at specific steps and how changes affect output.

Practice

1. What is the main benefit of using computed values in a MySQL query?

easy

A. They create new data from existing columns without extra storage

B. They permanently change the original data in the table

C. They slow down the query execution significantly

D. They require additional tables to store results

Solution

1. Step 1: Understand computed values

   Computed values are created on the fly using existing columns without storing extra data.

2. Step 2: Compare options

   Only They create new data from existing columns without extra storage correctly states this benefit; others describe incorrect effects.

3. Final Answer:

   They create new data from existing columns without extra storage -> Option A

4. Quick Check:

   Computed values = new data without storage [OK]

Hint: Computed values don't store data, they calculate on demand [OK]

Common Mistakes:

• Thinking computed values change stored data
• Assuming computed values need extra tables
• Believing computed values always slow queries

2. Which of the following is the correct syntax to create a computed column showing total price as quantity * price in a SELECT statement?

easy

A. SELECT quantity + price AS total_price FROM sales;

B. SELECT quantity * price AS total_price FROM sales;

C. SELECT quantity price AS total_price FROM sales;

D. SELECT total_price = quantity * price FROM sales;

Solution

1. Step 1: Identify correct arithmetic operator

   Multiplication uses the * operator, so quantity * price is correct.

2. Step 2: Check SQL syntax for aliasing

   Using AS total_price correctly names the computed column.

3. Final Answer:

   SELECT quantity * price AS total_price FROM sales; -> Option B

4. Quick Check:

   Use * for multiplication and AS for alias [OK]

Hint: Use * for multiplication and AS to name computed columns [OK]

Common Mistakes:

• Using + instead of * for multiplication
• Missing AS keyword for alias
• Writing invalid syntax like quantity price

3. Given the table employees with columns first_name, last_name, and birth_year, what will this query return?

SELECT first_name, last_name, YEAR(CURDATE()) - birth_year AS age FROM employees;

medium

A. An error because YEAR() cannot be used here

B. A list of employees with their birth years only

C. A list of employees with their full names and current ages

D. A list of employees with their ages but no names

Solution

1. Step 1: Understand the computed column

   The expression YEAR(CURDATE()) - birth_year calculates current age by subtracting birth year from current year.

2. Step 2: Analyze selected columns

   The query selects first_name, last_name, and the computed age, so it returns names and ages.

3. Final Answer:

   A list of employees with their full names and current ages -> Option C

4. Quick Check:

   Computed age = current year - birth year [OK]

Hint: Subtract birth year from current year to get age [OK]

Common Mistakes:

• Thinking YEAR() function causes error
• Ignoring the computed age column
• Assuming only birth years are returned

4. Identify the error in this query that tries to compute full name:

SELECT first_name + ' ' + last_name AS full_name FROM users;

medium

A. Using + to concatenate strings causes an error in MySQL

B. Missing AS keyword for alias

C. Using single quotes around space is invalid

D. The table name users is incorrect

Solution

1. Step 1: Check string concatenation syntax in MySQL

   MySQL uses CONCAT() function, not + operator, to join strings.

2. Step 2: Verify other parts of query

   AS keyword and single quotes are correct; table name assumed valid.

3. Final Answer:

   Using + to concatenate strings causes an error in MySQL -> Option A

4. Quick Check:

   Use CONCAT() for strings, not + [OK]

Hint: Use CONCAT() to join strings in MySQL [OK]

Common Mistakes:

• Using + for string concatenation
• Confusing single quotes with double quotes
• Assuming AS is optional

5. You want to create a query that shows each product's name and its discounted price, which is 10% less than the original price stored in price. Which query correctly uses a computed value to achieve this?

hard

A. SELECT product_name, price / 0.10 AS discounted_price FROM products;

B. SELECT product_name, price * 10 AS discounted_price FROM products;

C. SELECT product_name, price + 0.10 AS discounted_price FROM products;

D. SELECT product_name, price - (price * 0.10) AS discounted_price FROM products;

Solution

1. Step 1: Calculate 10% discount correctly

   10% discount means subtracting 10% of price: price - (price * 0.10).

2. Step 2: Check other options for correctness

   SELECT product_name, price * 10 AS discounted_price FROM products; multiplies price by 10 (wrong), C adds 0.10 (wrong), D divides by 0.10 (wrong).

3. Final Answer:

   SELECT product_name, price - (price * 0.10) AS discounted_price FROM products; -> Option D

4. Quick Check:

   Discounted price = price minus 10% [OK]

Hint: Subtract 10% by price - (price * 0.10) [OK]

Common Mistakes:

• Multiplying price by 10 instead of 0.10
• Adding 0.10 instead of subtracting percentage
• Dividing price by 0.10 incorrectly