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

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

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

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

Hint

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

Practice

1. What does the MIN function do in MySQL?

easy

A. Finds the smallest value in a column

B. Finds the largest value in a column

C. Counts the number of rows

D. Calculates the average value

Solution

1. Step 1: Understand the purpose of MIN function

   The MIN function is designed to find the smallest value in a specified column of a table.

2. Step 2: Compare with other aggregate functions

   Unlike MAX which finds the largest value, MIN specifically returns the minimum value.

3. Final Answer:

   Finds the smallest value in a column -> Option A

4. Quick Check:

   MIN = smallest value [OK]

Hint: MIN always returns the smallest value in a column [OK]

Common Mistakes:

• Confusing MIN with MAX
• Thinking MIN counts rows
• Assuming MIN calculates average

2. Which of the following is the correct syntax to find the maximum value in the price column of a table named products?

easy

A. SELECT MAX(price) IN products;

B. SELECT MAX price FROM products;

C. SELECT MAX(price) FROM products WHERE;

D. SELECT MAX(price) FROM products;

Solution

1. Step 1: Recall correct SQL aggregate function syntax

   The correct syntax for using MAX is: SELECT MAX(column_name) FROM table_name;

2. Step 2: Check each option for syntax errors

   SELECT MAX(price) FROM products; uses correct parentheses and FROM clause. Options A, B, and D have syntax errors or incomplete clauses.

3. Final Answer:

   SELECT MAX(price) FROM products; -> Option D

4. Quick Check:

   MAX(price) syntax = SELECT MAX(price) FROM table [OK]

Hint: Use parentheses around column name with MAX [OK]

Common Mistakes:

• Omitting parentheses in MAX function
• Using IN instead of FROM
• Leaving incomplete WHERE clause

3. Given the table employees with a column salary containing values (3000, 4500, 2500, 5000), what will this query return?
SELECT MIN(salary), MAX(salary) FROM employees;

medium

A. (4500, 3000)

B. (2500, 5000)

C. (5000, 2500)

D. (3000, 4500)

Solution

1. Step 1: Identify the smallest and largest salary values

   The salaries are 3000, 4500, 2500, and 5000. The smallest is 2500 and the largest is 5000.

2. Step 2: Understand the query output format

   The query returns a single row with two columns: MIN(salary) and MAX(salary), so the output is (2500, 5000).

3. Final Answer:

   (2500, 5000) -> Option B

4. Quick Check:

   MIN=2500, MAX=5000 [OK]

Hint: MIN returns smallest, MAX returns largest value [OK]

Common Mistakes:

• Mixing up MIN and MAX values
• Assuming output is multiple rows
• Confusing order of returned values

4. What is wrong with this query to find the minimum age from the users table?
SELECT MIN age FROM users;

medium

A. Missing parentheses around the column name

B. Incorrect table name

C. MIN cannot be used with numeric columns

D. Missing WHERE clause

Solution

1. Step 1: Check the syntax of the MIN function usage

   The MIN function requires parentheses around the column name, like MIN(age).

2. Step 2: Identify the error in the query

   The query is missing parentheses, so it will cause a syntax error.

3. Final Answer:

   Missing parentheses around the column name -> Option A

4. Quick Check:

   MIN requires parentheses [OK]

Hint: Always use parentheses with MIN and MAX [OK]

Common Mistakes:

• Omitting parentheses in function calls
• Assuming WHERE clause is mandatory
• Thinking MIN can't be used on numbers

5. You have a table orders with columns order_id, customer_id, and amount. How would you write a query to find the highest order amount for each customer?

hard

A. SELECT customer_id, amount FROM orders WHERE amount = MAX(amount);

B. SELECT MAX(amount) FROM orders GROUP BY customer_id;

C. SELECT customer_id, MAX(amount) FROM orders GROUP BY customer_id;

D. SELECT customer_id, MAX(amount) FROM orders;

Solution

1. Step 1: Understand the requirement to group by customer

   We want the highest order amount per customer, so we must group results by customer_id.

2. Step 2: Use MAX with GROUP BY

   Using MAX(amount) with GROUP BY customer_id returns the maximum amount for each customer.

3. Step 3: Check each option

   SELECT customer_id, MAX(amount) FROM orders GROUP BY customer_id; correctly groups by customer_id and selects MAX(amount). SELECT MAX(amount) FROM orders GROUP BY customer_id; misses customer_id in SELECT. SELECT customer_id, amount FROM orders WHERE amount = MAX(amount); uses invalid WHERE with aggregate. SELECT customer_id, MAX(amount) FROM orders; lacks GROUP BY.

4. Final Answer:

   SELECT customer_id, MAX(amount) FROM orders GROUP BY customer_id; -> Option C

5. Quick Check:

   MAX with GROUP BY returns max per group [OK]

Hint: Use GROUP BY with MAX to get max per group [OK]

Common Mistakes:

• Not using GROUP BY when aggregating per group
• Using WHERE with aggregate functions incorrectly
• Selecting columns not in GROUP BY without aggregation