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

Need a 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

Need a 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

Need a 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

Need a hint?

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