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.