MIN and MAX functions in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Find the minimum salary in the Employees table

Given the Employees table with columns id, name, and salary, what is the result of this query?

SELECT MIN(salary) FROM Employees;

MySQL

CREATE TABLE Employees (id INT, name VARCHAR(50), salary INT);
INSERT INTO Employees VALUES (1, 'Alice', 5000), (2, 'Bob', 7000), (3, 'Charlie', 6000);

A [{"MIN(salary)": null}]

B [{"MIN(salary)": 6000}]

C [{"MIN(salary)": 7000}]

D [{"MIN(salary)": 5000}]

💡 Hint

MIN returns the smallest value in the column.

The smallest salary among 5000, 7000, and 6000 is 5000.

❓ query_result

intermediate

Find the maximum age in the Users table

Given the Users table with columns user_id, username, and age, what is the output of this query?

SELECT MAX(age) FROM Users;

MySQL

CREATE TABLE Users (user_id INT, username VARCHAR(50), age INT);
INSERT INTO Users VALUES (1, 'john', 25), (2, 'jane', 30), (3, 'doe', 28);

A [{"MAX(age)": 30}]

B [{"MAX(age)": null}]

C [{"MAX(age)": 28}]

D [{"MAX(age)": 25}]

💡 Hint

MAX returns the largest value in the column.

The largest age among 25, 30, and 28 is 30.

📝 Syntax

advanced

Identify the syntax error in using MIN function

Which option contains a syntax error when trying to find the minimum price from the Products table?

MySQL

CREATE TABLE Products (product_id INT, product_name VARCHAR(50), price DECIMAL(10,2));
INSERT INTO Products VALUES (1, 'Pen', 1.20), (2, 'Notebook', 2.50), (3, 'Eraser', 0.80);

A SELECT MIN price FROM Products;

B SELECT MIN(price) FROM Products;

C SELECT MIN(price) AS min_price FROM Products;

D SELECT MIN(price) FROM Products WHERE price > 0;

💡 Hint

MIN function requires parentheses around the column name.

Option A misses parentheses around price, causing a syntax error.

❓ query_result

advanced

Find the maximum and minimum scores in one query

Given the Scores table with columns student_id and score, what is the output of this query?

SELECT MAX(score) AS max_score, MIN(score) AS min_score FROM Scores;

MySQL

CREATE TABLE Scores (student_id INT, score INT);
INSERT INTO Scores VALUES (1, 88), (2, 92), (3, 75), (4, 92), (5, 60);

A [{"max_score": 88, "min_score": 60}]

B [{"max_score": 92, "min_score": 75}]

C [{"max_score": 92, "min_score": 60}]

D [{"max_score": 75, "min_score": 60}]

💡 Hint

MAX and MIN can be selected together in one query.

The highest score is 92 and the lowest is 60.

🧠 Conceptual

expert

Understanding MIN and MAX with NULL values

Consider the table Measurements with a column value that contains some NULLs. What will the query

SELECT MIN(value), MAX(value) FROM Measurements;

return?

MySQL

CREATE TABLE Measurements (value INT);
INSERT INTO Measurements VALUES (10), (NULL), (5), (NULL), (20);

A [{"MIN(value)": null, "MAX(value)": null}]

B [{"MIN(value)": 5, "MAX(value)": 20}]

C [{"MIN(value)": 0, "MAX(value)": 20}]

D [{"MIN(value)": 5, "MAX(value)": null}]

💡 Hint

MIN and MAX ignore NULL values in aggregation.

MIN and MAX ignore NULLs and return the smallest and largest non-null values.

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