AVG function in MySQL - Mini Project: Build & Apply

Calculate Average Product Price Using AVG Function

📖 Scenario: You work in a small store that sells different products. You want to find out the average price of all products to understand the general pricing.

🎯 Goal: Build a simple database table of products with their prices and write a query to calculate the average price using the AVG function.

📋 What You'll Learn

Create a table called products with columns id, name, and price

Insert exactly three products with given prices

Write a query to calculate the average price of all products using the AVG function

Use an alias average_price for the average value in the query result

💡 Why This Matters

🌍 Real World

Stores and businesses often need to analyze average prices to set competitive pricing or understand sales trends.

💼 Career

Knowing how to use aggregate functions like AVG is essential for data analysts, database administrators, and anyone working with data to summarize information quickly.

1

Create the products table

Write a SQL statement to create a table called products with three columns: id as an integer primary key, name as a variable character string of length 50, and price as a decimal number with two decimal places.

MySQL

-- Create the products table with id, name, and price columns
-- Your code here

Need a hint?

Use CREATE TABLE with column definitions. Remember to set id as the primary key.

2

Insert product data into products

Insert three products into the products table with these exact values: (1, 'Pen', 1.20), (2, 'Notebook', 2.50), and (3, 'Eraser', 0.80).

MySQL

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(10, 2)
);
-- Insert the three products below
-- Your code here

Need a hint?

Use a single INSERT INTO statement with multiple rows.

3

Write a query to calculate the average price

Write a SQL query that selects the average of the price column from the products table using the AVG function. Use the alias average_price for the result.

MySQL

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(10, 2)
);

INSERT INTO products (id, name, price) VALUES
(1, 'Pen', 1.20),
(2, 'Notebook', 2.50),
(3, 'Eraser', 0.80);

-- Write the query to calculate average price below
-- Your code here

Need a hint?

Use SELECT AVG(price) AS average_price FROM products; to get the average price.

4

Complete the project with a comment explaining the result

Add a SQL comment below the query explaining that the result shows the average price of all products in the table.

MySQL

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(10, 2)
);

INSERT INTO products (id, name, price) VALUES
(1, 'Pen', 1.20),
(2, 'Notebook', 2.50),
(3, 'Eraser', 0.80);

SELECT AVG(price) AS average_price FROM products;
-- Your comment here

Need a hint?

Add a comment starting with -- explaining the query result.