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.