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

Using MIN and MAX Functions in SQL

📖 Scenario: You are managing a small bookstore database. You want to find the lowest and highest prices of books available to help set promotional discounts and premium pricing.

🎯 Goal: Build SQL queries using MIN and MAX functions to find the cheapest and most expensive book prices from the bookstore's inventory.

📋 What You'll Learn

Create a table called books with columns id, title, and price

Insert 5 specific book records with given prices

Write a query to find the minimum book price using MIN(price)

Write a query to find the maximum book price using MAX(price)

💡 Why This Matters

🌍 Real World

Finding minimum and maximum values is common in business to identify price ranges, sales extremes, or performance metrics.

💼 Career

Database professionals often use MIN and MAX functions to generate reports and insights from data.

1

Create the books table and insert data

Write SQL statements to create a table called books with columns id (integer), title (text), and price (decimal). Then insert these 5 books exactly: (1, 'Learn SQL', 25.50), (2, 'Python Basics', 30.00), (3, 'Data Science', 45.00), (4, 'Web Development', 20.00), (5, 'Machine Learning', 50.00).

MySQL

-- Create the books table and insert the 5 books
-- Your code here

Need a hint?

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

2

Write a query to find the minimum book price

Write a SQL query that selects the minimum price from the books table using the MIN(price) function. Name the result column min_price.

MySQL

CREATE TABLE books (id INT, title VARCHAR(100), price DECIMAL(5,2));
INSERT INTO books (id, title, price) VALUES
(1, 'Learn SQL', 25.50),
(2, 'Python Basics', 30.00),
(3, 'Data Science', 45.00),
(4, 'Web Development', 20.00),
(5, 'Machine Learning', 50.00);

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

Need a hint?

Use SELECT MIN(price) AS min_price FROM books; to get the lowest price.

3

Write a query to find the maximum book price

Write a SQL query that selects the maximum price from the books table using the MAX(price) function. Name the result column max_price.

MySQL

CREATE TABLE books (id INT, title VARCHAR(100), price DECIMAL(5,2));
INSERT INTO books (id, title, price) VALUES
(1, 'Learn SQL', 25.50),
(2, 'Python Basics', 30.00),
(3, 'Data Science', 45.00),
(4, 'Web Development', 20.00),
(5, 'Machine Learning', 50.00);

SELECT MIN(price) AS min_price FROM books;

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

Need a hint?

Use SELECT MAX(price) AS max_price FROM books; to get the highest price.

4

Combine MIN and MAX queries

Write a single SQL query that selects both the minimum and maximum prices from the books table. Use MIN(price) AS min_price and MAX(price) AS max_price in the same SELECT statement.

MySQL

CREATE TABLE books (id INT, title VARCHAR(100), price DECIMAL(5,2));
INSERT INTO books (id, title, price) VALUES
(1, 'Learn SQL', 25.50),
(2, 'Python Basics', 30.00),
(3, 'Data Science', 45.00),
(4, 'Web Development', 20.00),
(5, 'Machine Learning', 50.00);

SELECT MIN(price) AS min_price FROM books;

SELECT MAX(price) AS max_price FROM books;

-- Write a query to select both min and max prices together
-- Your code here

Need a hint?

Use SELECT MIN(price) AS min_price, MAX(price) AS max_price FROM books; to get both values in one query.