SELECT with expressions and calculations in SQL - Mini Project: Build & Apply

Calculate Total Sales with SELECT Expressions

📖 Scenario: You work for a small store that keeps track of products and their sales. You want to calculate the total sales amount for each product by multiplying the quantity sold by the price per unit.

🎯 Goal: Create a SQL query that selects the product name, quantity sold, price per unit, and calculates the total sales amount using an expression in the SELECT statement.

📋 What You'll Learn

Create a table called sales with columns product (text), quantity (integer), and price_per_unit (decimal).

Insert three rows into the sales table with exact values: ('Apple', 10, 0.5), ('Banana', 5, 0.3), ('Cherry', 20, 0.2).

Write a SELECT query that retrieves product, quantity, price_per_unit, and a calculated column total_sales which is quantity * price_per_unit.

Alias the calculated column as total_sales.

💡 Why This Matters

🌍 Real World

Stores and businesses often calculate total sales or revenue by multiplying quantity sold by price per unit to understand product performance.

💼 Career

Knowing how to write SELECT queries with calculations is essential for data analysts and database professionals to generate meaningful reports.

1

Create the sales table and insert data

Create a table called sales with columns product (text), quantity (integer), and price_per_unit (decimal). Then insert these exact rows: ('Apple', 10, 0.5), ('Banana', 5, 0.3), ('Cherry', 20, 0.2).

SQL

-- Create the sales table and insert data
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add the rows.

2

Set up the SELECT query base

Write a SELECT query that retrieves the columns product, quantity, and price_per_unit from the sales table.

SQL

CREATE TABLE sales (
  product TEXT,
  quantity INTEGER,
  price_per_unit DECIMAL
);

INSERT INTO sales (product, quantity, price_per_unit) VALUES
('Apple', 10, 0.5),
('Banana', 5, 0.3),
('Cherry', 20, 0.2);

-- Write a SELECT query to get product, quantity, and price_per_unit
-- Your code here

Hint

Use SELECT to choose columns and FROM sales to specify the table.

3

Add the calculation for total sales

Modify the SELECT query to add a calculated column called total_sales that multiplies quantity by price_per_unit.

SQL

CREATE TABLE sales (
  product TEXT,
  quantity INTEGER,
  price_per_unit DECIMAL
);

INSERT INTO sales (product, quantity, price_per_unit) VALUES
('Apple', 10, 0.5),
('Banana', 5, 0.3),
('Cherry', 20, 0.2);

SELECT product, quantity, price_per_unit,
-- Add total_sales calculation here
-- Your code here

Hint

Use quantity * price_per_unit AS total_sales to calculate and name the new column.

4

Complete the query with ordering

Add an ORDER BY clause to the SELECT query to sort the results by total_sales in descending order.

SQL

CREATE TABLE sales (
  product TEXT,
  quantity INTEGER,
  price_per_unit DECIMAL
);

INSERT INTO sales (product, quantity, price_per_unit) VALUES
('Apple', 10, 0.5),
('Banana', 5, 0.3),
('Cherry', 20, 0.2);

SELECT product, quantity, price_per_unit, quantity * price_per_unit AS total_sales
FROM sales
-- Add ORDER BY clause here
-- Your code here

Hint

Use ORDER BY total_sales DESC to sort from highest to lowest total sales.

Practice

1. What does the SQL expression SELECT price * quantity AS total_cost FROM sales; do?

easy

A. Calculates total cost by multiplying price and quantity for each row.

B. Adds price and quantity values together for each row.

C. Divides price by quantity for each row.

D. Filters rows where price equals quantity.

Solution

1. Step 1: Understand the multiplication in SELECT

   The expression multiplies the values in columns price and quantity for each row.

2. Step 2: Recognize the alias usage

   The result of multiplication is renamed as total_cost for clarity in output.

3. Final Answer:

   Calculates total cost by multiplying price and quantity for each row. -> Option A

4. Quick Check:

   price * quantity = total_cost [OK]

Hint: Look for * operator to identify multiplication [OK]

Common Mistakes:

• Confusing multiplication with addition or division
• Ignoring the AS alias
• Thinking it filters rows

2. Which of the following is the correct syntax to calculate the average price and rename it as avg_price?

easy

A. SELECT AVG(price) AS avg_price FROM products;

B. SELECT AVG(price) avg_price FROM products;

C. SELECT AVG(price) TO avg_price FROM products;

D. SELECT AVG(price) RENAME avg_price FROM products;

Solution

1. Step 1: Identify correct alias syntax

   In SQL, the keyword AS is used to rename columns or expressions in SELECT.

2. Step 2: Check each option's syntax

   SELECT AVG(price) AS avg_price FROM products; uses AVG(price) AS avg_price which is the correct syntax; others use invalid keywords or omit AS.

3. Final Answer:

   SELECT AVG(price) AS avg_price FROM products; -> Option A

4. Quick Check:

   Use AS to rename columns [OK]

Hint: Use AS keyword to rename calculated columns [OK]

Common Mistakes:

• Omitting AS keyword
• Using TO or RENAME instead of AS
• Misplacing parentheses

3. Given a table orders with columns price and quantity, what is the output of this query?

SELECT price, quantity, price * quantity AS total FROM orders WHERE price = 10;

medium

A. All rows with total calculated, ignoring price filter.

B. Syntax error due to missing GROUP BY.

C. Rows where price is 10, showing price, quantity, and total (price*quantity).

D. Rows where quantity equals 10.

Solution

1. Step 1: Understand the WHERE clause effect

   The query filters rows to only those where price equals 10.

2. Step 2: Calculate total for filtered rows

   For each filtered row, it shows price, quantity, and total which is price multiplied by quantity.

3. Final Answer:

   Rows where price is 10, showing price, quantity, and total (price*quantity). -> Option C

4. Quick Check:

   WHERE price=10 filters rows; total = price*quantity [OK]

Hint: Check WHERE filters before calculations [OK]

Common Mistakes:

• Ignoring WHERE clause filtering
• Expecting all rows instead of filtered
• Thinking GROUP BY is needed here

4. Identify the error in this SQL query:

SELECT price + quantity AS total FROM sales WHERE total > 100;

medium

A. Missing FROM clause in the query.

B. You cannot use alias 'total' in WHERE clause directly.

C. The + operator is invalid for numeric addition.

D. AS keyword cannot be used for aliasing.

Solution

1. Step 1: Understand alias scope in SQL

   Aliases defined in SELECT cannot be used in WHERE clause because WHERE is evaluated before SELECT.

2. Step 2: Identify correct fix

   Use the full expression (price + quantity) in WHERE or use a subquery/CTE to filter by total.

3. Final Answer:

   You cannot use alias 'total' in WHERE clause directly. -> Option B

4. Quick Check:

   Alias not usable in WHERE [OK]

Hint: Aliases appear after WHERE; can't use them there [OK]

Common Mistakes:

• Using alias in WHERE clause
• Thinking + is invalid operator
• Ignoring query evaluation order

5. You want to calculate the discounted price for products with a 10% discount and show it as discounted_price. Which query correctly applies the discount and rounds the result to 2 decimals?

hard

A. SELECT price - (price * 0.10) AS discounted_price FROM products;

B. SELECT price * 0.90 AS discounted_price FROM products;

C. SELECT ROUND(price * 0.90) AS discounted_price FROM products;

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

Solution

1. Step 1: Calculate 10% discount correctly

   Discounted price is price minus 10% of price, which is price - (price * 0.10) or price * 0.90.

2. Step 2: Round the result to 2 decimals

   Use ROUND(expression, 2) to round to two decimal places for currency formatting.

3. Final Answer:

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

4. Quick Check:

   Use ROUND(..., 2) for 2 decimals [OK]

Hint: Use ROUND(expression, 2) to round decimals [OK]

Common Mistakes:

• Not rounding the result
• Rounding without specifying decimals
• Using incorrect discount calculation