GREATEST and LEAST in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of GREATEST with NULL values

What is the output of the following MySQL query?

SELECT GREATEST(5, NULL, 3) AS result;

MySQL

SELECT GREATEST(5, NULL, 3) AS result;

A 3

B 5

C NULL

D 0

💡 Hint

Remember how GREATEST handles NULL values in MySQL.

In MySQL, if any argument to GREATEST is NULL, the result is NULL.

❓ query_result

intermediate

LEAST function with negative and positive numbers

What is the output of this query?

SELECT LEAST(-10, 0, 5, 3) AS result;

MySQL

SELECT LEAST(-10, 0, 5, 3) AS result;

A -10

B 0

C 3

D 5

💡 Hint

LEAST returns the smallest value among the arguments.

Among -10, 0, 5, and 3, the smallest number is -10.

📝 Syntax

advanced

Identify the syntax error in GREATEST usage

Which option contains a syntax error when using the GREATEST function in MySQL?

A SELECT GREATEST(1, 2, 3) AS max_value;

B SELECT GREATEST(4, 5, ) AS max_value;

C SELECT GREATEST(2, 3, NULL) AS max_value;

D SELECT GREATEST(5, '10', 7) AS max_value;

💡 Hint

Look for misplaced commas or missing arguments.

Option B has a trailing comma with no argument after it, causing a syntax error.

❓ optimization

advanced

Optimizing LEAST with columns and constants

Given a table scores with columns math, science, and english, which query efficiently returns the lowest score for each student?

A SELECT LEAST(math, science) + LEAST(english, 0) AS lowest_score FROM scores;

B SELECT CASE WHEN math < science AND math < english THEN math WHEN science < english THEN science ELSE english END AS lowest_score FROM scores;

C SELECT MIN(math, science, english) AS lowest_score FROM scores;

D SELECT LEAST(math, science, english) AS lowest_score FROM scores;

💡 Hint

Consider built-in functions that compare multiple values in one call.

LEAST directly compares all three columns and returns the smallest value efficiently.

🧠 Conceptual

expert

Behavior of GREATEST and LEAST with different data types

What is the result of this query?

SELECT GREATEST('10', 5, '7') AS greatest_val, LEAST('10', 5, '7') AS least_val;

Assume MySQL default type conversion rules.

MySQL

SELECT GREATEST('10', 5, '7') AS greatest_val, LEAST('10', 5, '7') AS least_val;

A greatest_val = 10, least_val = 5

B greatest_val = 10, least_val = 5 (both as strings)

C greatest_val = 10, least_val = 7

D greatest_val = '10', least_val = '7'

💡 Hint

MySQL converts strings to numbers when comparing numeric strings and numbers.

MySQL converts string numbers to numeric values for comparison. So GREATEST compares 10, 5, and 7 numerically, returning 10; LEAST returns 5.