Why computed values add flexibility in MySQL - See It in Action

Why Computed Values Add Flexibility

📖 Scenario: You are managing a small online store database. You want to calculate the total price for each order item by multiplying the quantity by the unit price. Instead of storing the total price in the database, you will compute it on the fly using SQL queries. This approach keeps your data flexible and always accurate if prices or quantities change.

🎯 Goal: Build a simple SQL query that computes the total price for each order item by multiplying quantity and unit_price columns. This shows how computed values add flexibility by calculating results dynamically.

📋 What You'll Learn

Create a table called order_items with columns item_id (integer), quantity (integer), and unit_price (decimal).

Insert three rows with exact values: (1, 2, 10.00), (2, 5, 7.50), (3, 1, 20.00).

Write a SELECT query that returns item_id, quantity, unit_price, and a computed column total_price which is quantity * unit_price.

Use an alias total_price for the computed column in the SELECT statement.

💡 Why This Matters

🌍 Real World

Computing values on the fly in queries helps keep data consistent and reduces storage needs. For example, online stores calculate totals dynamically to reflect current prices and quantities.

💼 Career

Database developers and analysts often write queries with computed columns to generate reports and insights without changing the stored data.

1

Create the order_items table and insert data

Create a table called order_items with columns item_id as INTEGER, quantity as INTEGER, and unit_price as DECIMAL(5,2). Then insert these exact rows: (1, 2, 10.00), (2, 5, 7.50), and (3, 1, 20.00).

MySQL

-- Create the order_items table and insert the rows
-- Your code here

Need a hint?

Use CREATE TABLE to define columns and INSERT INTO to add rows with the exact values.

2

Add a SELECT query to retrieve all columns

Write a SELECT query to get item_id, quantity, and unit_price from the order_items table.

MySQL

CREATE TABLE order_items (
  item_id INT,
  quantity INT,
  unit_price DECIMAL(5,2)
);

INSERT INTO order_items (item_id, quantity, unit_price) VALUES
(1, 2, 10.00),
(2, 5, 7.50),
(3, 1, 20.00);

-- Write a SELECT query to get item_id, quantity, and unit_price
-- Your code here

Need a hint?

Use SELECT with the exact column names and FROM order_items.

3

Add a computed column for total price

Modify the SELECT query to include a computed column called total_price that multiplies quantity by unit_price. Use the expression quantity * unit_price AS total_price.

MySQL

CREATE TABLE order_items (
  item_id INT,
  quantity INT,
  unit_price DECIMAL(5,2)
);

INSERT INTO order_items (item_id, quantity, unit_price) VALUES
(1, 2, 10.00),
(2, 5, 7.50),
(3, 1, 20.00);

-- Modify the SELECT query to add total_price as quantity * unit_price
-- Your code here

Need a hint?

Add quantity * unit_price AS total_price to the SELECT list.

4

Complete the query to show computed total price

Ensure the final query selects item_id, quantity, unit_price, and the computed total_price column from order_items. This completes the example of using computed values for flexibility.

MySQL

CREATE TABLE order_items (
  item_id INT,
  quantity INT,
  unit_price DECIMAL(5,2)
);

INSERT INTO order_items (item_id, quantity, unit_price) VALUES
(1, 2, 10.00),
(2, 5, 7.50),
(3, 1, 20.00);

-- Finalize the query to select all columns including total_price
-- Your code here

Need a hint?

Check that the SELECT statement includes the computed column with the alias total_price.