Aggregate functions (COUNT, SUM, AVG, MAX, MIN) in DBMS Theory - Mini Project: Build & Apply

Aggregate functions (COUNT, SUM, AVG, MAX, MIN)

📖 Scenario: You are working with a simple sales database that records sales transactions. Each transaction has a product name, quantity sold, and price per unit.You want to learn how to use SQL aggregate functions to get useful summaries from this data.

🎯 Goal: Build SQL queries step-by-step to calculate total sales, average quantity sold, maximum price, minimum price, and count of transactions.

📋 What You'll Learn

Create a table called sales with columns product, quantity, and price

Insert the exact rows specified into the sales table

Write SQL queries using aggregate functions COUNT, SUM, AVG, MAX, and MIN

Use the exact column names and table name as specified

💡 Why This Matters

🌍 Real World

Businesses use aggregate functions to summarize sales data, track performance, and make decisions based on totals, averages, and extremes.

💼 Career

Knowing SQL aggregate functions is essential for data analysts, database administrators, and developers working with data reporting and business intelligence.

1

DATA SETUP: Create the sales table and insert data

Write SQL statements to create a table called sales with columns product (text), quantity (integer), and price (decimal). Then insert these exact rows: ('Apple', 10, 0.5), ('Banana', 5, 0.3), ('Orange', 8, 0.7), ('Apple', 3, 0.5), ('Banana', 7, 0.3).

DBMS Theory

-- Create the sales table and insert the rows below
-- Your code here

Need a hint?

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

2

CONFIGURATION: Write a query to count total transactions

Write a SQL query that uses the COUNT aggregate function to count the total number of rows (transactions) in the sales table. Name the result column total_transactions.

DBMS Theory

CREATE TABLE sales (
  product TEXT,
  quantity INTEGER,
  price DECIMAL(5,2)
);

INSERT INTO sales (product, quantity, price) VALUES
('Apple', 10, 0.5),
('Banana', 5, 0.3),
('Orange', 8, 0.7),
('Apple', 3, 0.5),
('Banana', 7, 0.3);

-- Write a query to count total transactions
-- Your code here

Need a hint?

Use COUNT(*) to count all rows and AS to name the output column.

3

CORE LOGIC: Calculate total quantity sold and average price

Write a SQL query that uses SUM(quantity) to find the total quantity sold and AVG(price) to find the average price from the sales table. Name the columns total_quantity and average_price respectively.

DBMS Theory

CREATE TABLE sales (
  product TEXT,
  quantity INTEGER,
  price DECIMAL(5,2)
);

INSERT INTO sales (product, quantity, price) VALUES
('Apple', 10, 0.5),
('Banana', 5, 0.3),
('Orange', 8, 0.7),
('Apple', 3, 0.5),
('Banana', 7, 0.3);

SELECT COUNT(*) AS total_transactions FROM sales;

-- Write a query to get total quantity and average price
-- Your code here

Need a hint?

Use SUM() and AVG() together in the SELECT clause.

4

COMPLETION: Find maximum and minimum price in sales

Write a SQL query that uses MAX(price) to find the highest price and MIN(price) to find the lowest price in the sales table. Name the columns max_price and min_price respectively.

DBMS Theory

CREATE TABLE sales (
  product TEXT,
  quantity INTEGER,
  price DECIMAL(5,2)
);

INSERT INTO sales (product, quantity, price) VALUES
('Apple', 10, 0.5),
('Banana', 5, 0.3),
('Orange', 8, 0.7),
('Apple', 3, 0.5),
('Banana', 7, 0.3);

SELECT COUNT(*) AS total_transactions FROM sales;

SELECT SUM(quantity) AS total_quantity, AVG(price) AS average_price FROM sales;

-- Write a query to get max and min price
-- Your code here

Need a hint?

Use MAX() and MIN() in the SELECT clause to find highest and lowest values.