Why computed values add flexibility in MySQL - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

Output of a computed column in a SELECT query

Given a table Orders with columns quantity and unit_price, what is the output of the following query?

SELECT quantity, unit_price, quantity * unit_price AS total_price FROM Orders WHERE order_id = 101;

MySQL

SELECT quantity, unit_price, quantity * unit_price AS total_price FROM Orders WHERE order_id = 101;

A [{"quantity": 3, "unit_price": 20, "total_price": 23}]

B [{"quantity": 3, "unit_price": 20}]

C [{"quantity": 3, "unit_price": 20, "total_price": 60}]

D SyntaxError

💡 Hint

Think about how multiplication works in SQL expressions.

The computed column total_price multiplies quantity by unit_price. For quantity 3 and unit_price 20, total_price is 60.

🧠 Conceptual

intermediate

Why use computed columns in a database?

Which of the following is the best reason to use computed columns in a database table?

A To store redundant data that never changes

B To prevent any changes to the table structure

C To make queries slower by adding extra calculations

D To automatically calculate values based on other columns, reducing manual errors

💡 Hint

Think about how computed columns help keep data consistent.

Computed columns calculate values automatically from other columns, reducing manual errors and improving data consistency.

📝 Syntax

advanced

Identify the correct syntax for creating a computed column

Which option correctly creates a computed column named full_name that concatenates first_name and last_name in MySQL?

MySQL

CREATE TABLE Employees (
  id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  full_name VARCHAR(101) AS (CONCAT(first_name, ' ', last_name))
);

A CREATE TABLE Employees (id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), full_name VARCHAR(101) AS (CONCAT(first_name, ' ', last_name)));

B CREATE TABLE Employees (id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), full_name VARCHAR(101) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) VIRTUAL);

C CREATE TABLE Employees (id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), full_name AS VARCHAR(101) (CONCAT(first_name, ' ', last_name)));

D CREATE TABLE Employees (id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), full_name VARCHAR(101) COMPUTED AS CONCAT(first_name, ' ', last_name));

💡 Hint

MySQL uses GENERATED ALWAYS AS syntax for computed columns.

In MySQL, computed columns are created using GENERATED ALWAYS AS with either VIRTUAL or STORED keyword. Option B uses correct syntax.

❓ optimization

advanced

Performance impact of computed columns

Which statement about computed columns and query performance is true?

A Stored computed columns can improve query performance by pre-calculating values

B Computed columns cannot be indexed, so they never improve performance

C Computed columns always slow down queries because they calculate values on the fly

D Using computed columns requires disabling indexes on the table

💡 Hint

Think about how storing computed values affects query speed.

Stored computed columns save calculated values physically, allowing indexes on them and improving query speed.

🔧 Debug

expert

Debugging a computed column error

A developer tries to create a computed column discounted_price as price - discount but gets an error. Which option explains the cause?

MySQL

CREATE TABLE Products (
  id INT PRIMARY KEY,
  price DECIMAL(10,2),
  discount DECIMAL(10,2),
  discounted_price DECIMAL(10,2) GENERATED ALWAYS AS (price - discount)
);

A The computed column must specify VIRTUAL or STORED keyword in MySQL

B You cannot use arithmetic operations in computed columns

C The discount column must be an integer type

D Computed columns cannot reference other columns in the same table

💡 Hint

Check the syntax requirements for computed columns in MySQL.

MySQL requires specifying VIRTUAL or STORED for computed columns. Omitting this causes a syntax error.

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