Why computed values add flexibility in MySQL

Introduction

Computed values let you create new information from existing data without storing extra details. This makes your data more flexible and easier to update.

When you want to show a total price by multiplying quantity and unit price.

When you need to calculate age from a birthdate stored in the database.

When you want to display a full name by combining first and last names.

When you want to find the discount amount based on a percentage and original price.

When you want to convert stored data, like changing temperature from Celsius to Fahrenheit on the fly.

Syntax

MySQL

SELECT column1, column2, (expression) AS computed_column FROM table_name;

Use parentheses around the expression to clearly define the computed value.

The AS keyword gives a name to the computed column for easy reference.

Examples

This calculates total cost by multiplying price and quantity for each row.

MySQL

SELECT price, quantity, (price * quantity) AS total_cost FROM sales;

This combines first and last names into one full name column.

MySQL

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

This computes age by subtracting birth year from the current year.

MySQL

SELECT birthdate, YEAR(CURDATE()) - YEAR(birthdate) AS age FROM people;

Sample Program

This example creates a products table, adds two items, and calculates the total value for each product by multiplying price and quantity.

MySQL

CREATE TABLE products (id INT, name VARCHAR(20), price DECIMAL(5,2), quantity INT);
INSERT INTO products VALUES (1, 'Pen', 1.50, 10), (2, 'Notebook', 3.00, 5);
SELECT name, price, quantity, (price * quantity) AS total_value FROM products;

Important Notes

Computed columns do not store data physically; they calculate values when you run the query.

Using computed values helps keep your database smaller and avoids data duplication.

Be careful with complex computations as they can slow down your queries.

Summary

Computed values create new data from existing columns without extra storage.

They help show useful information like totals, ages, or combined names easily.

Using them makes your data flexible and your queries more powerful.

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