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

Challenge - 5 Problems

❓ query_result

intermediate

Find the minimum salary from employees

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

SELECT MIN(salary) FROM Employees;

SQL

SELECT MIN(salary) FROM Employees;

A Returns the smallest salary value from the Employees table

B Returns the largest salary value from the Employees table

C Returns the average salary value from the Employees table

D Returns the total number of employees

💡 Hint

MIN function finds the smallest value in a column.

The MIN function returns the smallest value in the specified column. Here, it returns the smallest salary among all employees.

❓ query_result

intermediate

Find the maximum order amount

Given the table Orders with columns order_id, customer_id, and amount, what does this query return?

SELECT MAX(amount) FROM Orders;

SQL

SELECT MAX(amount) FROM Orders;

A Returns the largest order amount

B Returns the smallest order amount

C Returns the total sum of all order amounts

D Returns the number of orders

💡 Hint

MAX function finds the largest value in a column.

The MAX function returns the highest value in the specified column. Here, it returns the largest order amount.

🧠 Conceptual

advanced

Understanding MIN and MAX with NULL values

Consider a table Scores with a column score that contains some NULL values. What will the query SELECT MIN(score) FROM Scores; return?

A NULL because there are NULL values in the column

B An error because MIN cannot handle NULL values

C The smallest value including NULLs

D The smallest non-NULL score value

💡 Hint

Aggregate functions ignore NULL values by default.

MIN and MAX ignore NULL values and return the smallest or largest non-NULL value respectively.

📝 Syntax

advanced

Identify the syntax error in MIN usage

Which of the following SQL queries will cause a syntax error?

A SELECT MAX(amount) FROM Orders;

B SELECT MIN(salary) FROM Employees;

C SELECT MIN salary FROM Employees;

D SELECT MIN(age) FROM Persons;

💡 Hint

Aggregate functions require parentheses around the column name.

Option C is missing parentheses around the column name, causing a syntax error.

❓ optimization

expert

Optimizing MIN and MAX queries on large tables

You have a very large table Sales with millions of rows and a column sale_date. You want to find the earliest and latest sale dates efficiently. Which approach is best?

A Use SELECT MIN(sale_date), MAX(sale_date) FROM Sales; without any indexes

B Create an index on sale_date and then run SELECT MIN(sale_date), MAX(sale_date) FROM Sales;

C Run two separate queries: SELECT MIN(sale_date) FROM Sales; and SELECT MAX(sale_date) FROM Sales; without indexes

D Use SELECT sale_date FROM Sales ORDER BY sale_date LIMIT 1; and SELECT sale_date FROM Sales ORDER BY sale_date DESC LIMIT 1;

💡 Hint

Indexes help speed up queries that search for minimum or maximum values.

Creating an index on the column allows the database to quickly find the minimum and maximum values without scanning the entire table.

Practice

1. What does the SQL function MIN() do when applied to a column of numbers?

easy

A. Calculates the average of the numbers

B. Finds the largest number in the column

C. Finds the smallest number in the column

D. Counts the total numbers in the column

Solution

1. Step 1: Understand the purpose of MIN()

   The MIN() function is designed to find the smallest value in a column of data.

2. Step 2: Compare with other functions

   Unlike MAX() which finds the largest, or AVG() which calculates average, MIN() specifically returns the minimum value.

3. Final Answer:

   Finds the smallest number in the column -> Option C

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() calculates average
• Using MIN() to count rows

2. Which of the following is the correct syntax to find the maximum salary from a table named Employees?

easy

A. SELECT MAX salary FROM Employees;

B. SELECT MAX{salary} FROM Employees;

C. SELECT MAX[salary] FROM Employees;

D. SELECT MAX(salary) FROM Employees;

Solution

1. Step 1: Recall correct function syntax

   The correct syntax for SQL aggregate functions like MAX() requires parentheses around the column name.

2. Step 2: Check syntax

   SELECT MAX(salary) FROM Employees; uses MAX(salary) which is correct. Forms without parentheses or using [] {} are invalid.

3. Final Answer:

   SELECT MAX(salary) FROM Employees; -> Option D

4. Quick Check:

   MAX() uses parentheses around column [OK]

Hint: Use parentheses around column name in MAX() [OK]

Common Mistakes:

• Omitting parentheses in function calls
• Using square or curly brackets instead of parentheses
• Misspelling function names

3. Given the table Products with a column Price containing values (100, 250, 50, 400), what will the query SELECT MIN(Price), MAX(Price) FROM Products; return?

medium

A. (400, 50)

B. (50, 400)

C. (100, 250)

D. (0, 400)

Solution

1. Step 1: Identify minimum price

   From the values (100, 250, 50, 400), the smallest price is 50.

2. Step 2: Identify maximum price

   The largest price in the list is 400.

3. Final Answer:

   (50, 400) -> Option B

4. Quick Check:

   MIN = 50, MAX = 400 [OK]

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

Common Mistakes:

• Mixing up MIN and MAX values
• Assuming default zero minimum
• Confusing order of returned values

4. What is wrong with this query to find the maximum age from the Users table?
SELECT MAX age FROM Users;

medium

A. Missing parentheses around the column name

B. MAX function cannot be used on numeric columns

C. Table name should be in quotes

D. MAX should be lowercase

Solution

1. Step 1: Check function syntax

   The MAX() function requires parentheses around the column name, so MAX age is invalid syntax.

2. Step 2: Verify other options

   MAX works on numeric columns, table names don't need quotes, and function names are case-insensitive in SQL.

3. Final Answer:

   Missing parentheses around the column name -> Option A

4. Quick Check:

   MAX() needs parentheses [OK]

Hint: Always use parentheses with MAX() [OK]

Common Mistakes:

• Omitting parentheses in function calls
• Thinking MAX() only works on strings
• Adding unnecessary quotes around table names

5. You have a table Orders with columns OrderID, CustomerID, and TotalAmount. How would you write a query to find the highest order amount for each customer?

hard

A. SELECT CustomerID, MAX(TotalAmount) FROM Orders GROUP BY CustomerID;

B. SELECT MAX(TotalAmount) FROM Orders WHERE CustomerID;

C. SELECT CustomerID, MAX(TotalAmount) FROM Orders;

D. SELECT CustomerID, TotalAmount FROM Orders WHERE MAX(TotalAmount);

Solution

1. Step 1: Understand grouping requirement

   To find the highest order amount per customer, we must group data by CustomerID.

2. Step 2: Use MAX() with GROUP BY

   Using MAX(TotalAmount) with GROUP BY CustomerID returns the maximum order amount for each customer.

3. Step 3: Why others fail

   Using MAX() in a WHERE clause is invalid, WHERE CustomerID; is meaningless, and selecting CustomerID with MAX() without GROUP BY causes an error because CustomerID is not aggregated.

4. Final Answer:

   SELECT CustomerID, MAX(TotalAmount) FROM Orders GROUP BY CustomerID; -> Option A

5. Quick Check:

   Use GROUP BY with MAX() for per-group max [OK]

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

Common Mistakes:

• Forgetting GROUP BY when using MAX per group
• Using MAX() in WHERE clause incorrectly
• Selecting columns without grouping