AVG function in SQL - Mini Project: Build & Apply

Calculate Average Sales Using AVG Function

📖 Scenario: You work at a small store that keeps track of daily sales in a database table. You want to find out the average sales amount to understand how well the store is doing on average each day.

🎯 Goal: Build a SQL query that calculates the average sales amount from the sales data using the AVG function.

📋 What You'll Learn

Create a table called sales with columns day and amount.

Insert the exact sales data for 5 days into the sales table.

Write a SQL query that uses the AVG function to find the average sales amount.

Complete the query to return the average sales with the column name average_sales.

💡 Why This Matters

🌍 Real World

Calculating averages is common in business to understand typical performance, such as average sales, average customer ratings, or average expenses.

💼 Career

Knowing how to use the AVG function and filter data with WHERE is essential for data analysts, database administrators, and anyone working with data to generate meaningful insights.

1

Create the sales table and insert data

Create a table called sales with two columns: day as INTEGER and amount as INTEGER. Then insert these exact rows into the sales table: (1, 100), (2, 150), (3, 200), (4, 130), (5, 170).

SQL

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

Need a hint?

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

2

Set up the query to calculate average

Write a SQL query starting with SELECT that will calculate the average of the amount column from the sales table. Use the AVG function and alias the result as average_sales.

SQL

CREATE TABLE sales (day INTEGER, amount INTEGER);
INSERT INTO sales (day, amount) VALUES
(1, 100),
(2, 150),
(3, 200),
(4, 130),
(5, 170);

-- Write the SELECT query using AVG function
-- Your code here

Need a hint?

Use SELECT AVG(amount) AS average_sales FROM sales; to get the average sales.

3

Add a WHERE clause to filter days

Modify the existing query to calculate the average sales amount only for days greater than 2. Add a WHERE clause with the condition day > 2.

SQL

CREATE TABLE sales (day INTEGER, amount INTEGER);
INSERT INTO sales (day, amount) VALUES
(1, 100),
(2, 150),
(3, 200),
(4, 130),
(5, 170);

-- Add WHERE clause to filter days > 2
-- Your code here

Need a hint?

Use WHERE day > 2 to filter the rows before averaging.

4

Complete the query with ORDER BY and LIMIT

Add ORDER BY average_sales DESC and LIMIT 1 to the query to show the highest average sales first and limit the output to one row.

SQL

CREATE TABLE sales (day INTEGER, amount INTEGER);
INSERT INTO sales (day, amount) VALUES
(1, 100),
(2, 150),
(3, 200),
(4, 130),
(5, 170);

-- Add ORDER BY and LIMIT to complete the query
-- Your code here

Need a hint?

Use ORDER BY average_sales DESC LIMIT 1 to sort and limit the results.