MIN and MAX functions in SQL - Mini Project: Build & Apply

Finding Minimum and Maximum Values with SQL

📖 Scenario: You are working with a small store's sales database. The store wants to find out the lowest and highest prices of products they sell to help with pricing decisions.

🎯 Goal: Build SQL queries that use the MIN and MAX functions to find the lowest and highest product prices from the products table.

📋 What You'll Learn

Create a products table with columns product_id, product_name, and price.

Insert exactly three products with specified prices.

Write a query to find the minimum price using the MIN function.

Write a query to find the maximum price using the MAX function.

💡 Why This Matters

🌍 Real World

Stores and businesses often need to find the lowest and highest prices of products to set discounts or price ranges.

💼 Career

Knowing how to use MIN and MAX functions is essential for data analysis and reporting roles that work with databases.

1

Create the products table and insert data

Write SQL statements to create a table called products with columns product_id (integer), product_name (text), and price (integer). Then insert these three rows exactly: (1, 'Pen', 10), (2, 'Notebook', 25), (3, 'Eraser', 5).

SQL

-- Create the products table and insert the three products
-- Your code here

Need a hint?

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

2

Set up a query to find the minimum price

Write a SQL query that selects the minimum price from the products table using the MIN function. Name the result column lowest_price.

SQL

CREATE TABLE products (
    product_id INTEGER,
    product_name TEXT,
    price INTEGER
);

INSERT INTO products (product_id, product_name, price) VALUES
(1, 'Pen', 10),
(2, 'Notebook', 25),
(3, 'Eraser', 5);

-- Write a query to find the minimum price
-- Your code here

Need a hint?

Use SELECT MIN(price) AS lowest_price FROM products; to get the lowest price.

3

Write a query to find the maximum price

Write a SQL query that selects the maximum price from the products table using the MAX function. Name the result column highest_price.

SQL

CREATE TABLE products (
    product_id INTEGER,
    product_name TEXT,
    price INTEGER
);

INSERT INTO products (product_id, product_name, price) VALUES
(1, 'Pen', 10),
(2, 'Notebook', 25),
(3, 'Eraser', 5);

SELECT MIN(price) AS lowest_price FROM products;

-- Write a query to find the maximum price
-- Your code here

Need a hint?

Use SELECT MAX(price) AS highest_price FROM products; to get the highest price.

4

Combine minimum and maximum price queries

Write a single SQL query that selects both the minimum and maximum prices from the products table. Name the columns lowest_price and highest_price respectively.

SQL

CREATE TABLE products (
    product_id INTEGER,
    product_name TEXT,
    price INTEGER
);

INSERT INTO products (product_id, product_name, price) VALUES
(1, 'Pen', 10),
(2, 'Notebook', 25),
(3, 'Eraser', 5);

SELECT MIN(price) AS lowest_price FROM products;

SELECT MAX(price) AS highest_price FROM products;

-- Write a query to select both minimum and maximum prices together
-- Your code here

Need a hint?

Use both MIN(price) and MAX(price) in the same SELECT statement.