GREATEST and LEAST in MySQL - Mini Project: Build & Apply

Using GREATEST and LEAST Functions in MySQL

📖 Scenario: You work at a small online store that tracks product prices from different suppliers. You want to find the highest and lowest prices for each product to make better buying decisions.

🎯 Goal: Build a simple MySQL table with product prices from three suppliers. Then write queries using GREATEST and LEAST functions to find the highest and lowest prices for each product.

📋 What You'll Learn

Create a table named product_prices with columns product_name (VARCHAR) and three price columns: price_supplier1, price_supplier2, price_supplier3 (all DECIMAL).

Insert exactly three products with their prices from each supplier.

Write a SELECT query that uses GREATEST to find the highest price among the three suppliers for each product.

Write a SELECT query that uses LEAST to find the lowest price among the three suppliers for each product.

💡 Why This Matters

🌍 Real World

Stores and businesses often compare prices from multiple suppliers to get the best deals. Using GREATEST and LEAST helps quickly find the best and worst prices.

💼 Career

Knowing how to use these functions is useful for data analysts and database developers who work with pricing data or any scenario needing comparisons across columns.

1

Create the product_prices table

Write a MySQL statement to create a table called product_prices with these columns: product_name as VARCHAR(50), and price_supplier1, price_supplier2, price_supplier3 as DECIMAL(6,2).

MySQL

-- Your code here to create the product_prices table

Hint

Use CREATE TABLE with the exact column names and types.

2

Insert product price data

Insert these three products with prices into product_prices: ('Laptop', 900.00, 850.00, 870.00), ('Smartphone', 500.00, 520.00, 510.00), and ('Tablet', 300.00, 310.00, 305.00).

MySQL

CREATE TABLE product_prices (
  product_name VARCHAR(50),
  price_supplier1 DECIMAL(6,2),
  price_supplier2 DECIMAL(6,2),
  price_supplier3 DECIMAL(6,2)
);
-- Your code here to insert the three products

Hint

Use a single INSERT INTO statement with multiple rows.

3

Query highest price using GREATEST

Write a SELECT query to show product_name and the highest price among price_supplier1, price_supplier2, and price_supplier3 using the GREATEST function. Name the highest price column highest_price.

MySQL

CREATE TABLE product_prices (
  product_name VARCHAR(50),
  price_supplier1 DECIMAL(6,2),
  price_supplier2 DECIMAL(6,2),
  price_supplier3 DECIMAL(6,2)
);

INSERT INTO product_prices (product_name, price_supplier1, price_supplier2, price_supplier3) VALUES
('Laptop', 900.00, 850.00, 870.00),
('Smartphone', 500.00, 520.00, 510.00),
('Tablet', 300.00, 310.00, 305.00);

-- Your code here to select product_name and highest_price using GREATEST

Hint

Use GREATEST with the three price columns inside the SELECT statement.

4

Query lowest price using LEAST

Write a SELECT query to show product_name and the lowest price among price_supplier1, price_supplier2, and price_supplier3 using the LEAST function. Name the lowest price column lowest_price.

MySQL

CREATE TABLE product_prices (
  product_name VARCHAR(50),
  price_supplier1 DECIMAL(6,2),
  price_supplier2 DECIMAL(6,2),
  price_supplier3 DECIMAL(6,2)
);

INSERT INTO product_prices (product_name, price_supplier1, price_supplier2, price_supplier3) VALUES
('Laptop', 900.00, 850.00, 870.00),
('Smartphone', 500.00, 520.00, 510.00),
('Tablet', 300.00, 310.00, 305.00);

SELECT product_name, GREATEST(price_supplier1, price_supplier2, price_supplier3) AS highest_price
FROM product_prices;

-- Your code here to select product_name and lowest_price using LEAST

Hint

Use LEAST with the three price columns inside the SELECT statement.

Practice

1. What does the MySQL function GREATEST(3, 7, 5) return?

easy

A. NULL

B. 3

C. 5

D. 7

Solution

1. Step 1: Understand the GREATEST function

   The GREATEST function returns the largest value among the given arguments.

2. Step 2: Compare the values 3, 7, and 5

   Among 3, 7, and 5, the largest number is 7.

3. Final Answer:

   7 -> Option D

4. Quick Check:

   GREATEST(3, 7, 5) = 7 [OK]

Hint: GREATEST picks the highest number from the list [OK]

Common Mistakes:

• Confusing GREATEST with LEAST
• Thinking it returns the first value
• Assuming it returns a sum

2. Which of the following is the correct syntax to find the smallest value among 10, 20, and 5 using MySQL?

easy

A. MIN(10, 20, 5)

B. LEAST(10, 20, 5)

C. SMALLEST(10, 20, 5)

D. LOWEST(10, 20, 5)

Solution

1. Step 1: Recall the function to find smallest value

   In MySQL, the LEAST function returns the smallest value from a list of arguments.

2. Step 2: Check the syntax options

   LEAST(10, 20, 5) is the correct syntax. MIN is an aggregate function and requires a column, not a list of values. SMALLEST and LOWEST are not valid MySQL functions.

3. Final Answer:

   LEAST(10, 20, 5) -> Option B

4. Quick Check:

   LEAST(10, 20, 5) is correct syntax [OK]

Hint: Use LEAST() for smallest, GREATEST() for largest values [OK]

Common Mistakes:

• Using MIN() with values instead of columns
• Using non-existent functions like SMALLEST()
• Confusing function names

3. Given the table Scores with columns math, science, and english, what will the query SELECT GREATEST(math, science, english) AS top_score FROM Scores WHERE id = 1; return if the row with id = 1 has values (85, 90, 88)?

medium

A. 90

B. 85

C. 88

D. NULL

Solution

1. Step 1: Identify the values for id = 1

   The row has math=85, science=90, english=88.

2. Step 2: Apply GREATEST to these values

   GREATEST(85, 90, 88) returns the largest value, which is 90.

3. Final Answer:

   90 -> Option A

4. Quick Check:

   GREATEST(85, 90, 88) = 90 [OK]

Hint: GREATEST returns the highest among given column values [OK]

Common Mistakes:

• Selecting the first column value instead of greatest
• Confusing GREATEST with LEAST
• Assuming NULL if any value is less

4. Consider this query: SELECT LEAST(score1, score2, score3) FROM Results; It returns NULL for some rows even though all scores are numbers. What is the likely cause?

medium

A. Syntax error in the query

B. LEAST function only works with positive numbers

C. One or more scores are NULL in those rows

D. LEAST returns the largest value, so NULL is unexpected

Solution

1. Step 1: Understand LEAST behavior with NULLs

   LEAST returns NULL if any argument is NULL because NULL means unknown value.

2. Step 2: Check the cause of NULL results

   If some rows have NULL in score1, score2, or score3, LEAST will return NULL for those rows.

3. Final Answer:

   One or more scores are NULL in those rows -> Option C

4. Quick Check:

   LEAST returns NULL if any input is NULL [OK]

Hint: Check for NULLs in inputs; LEAST returns NULL if any argument is NULL [OK]

Common Mistakes:

• Assuming LEAST ignores NULLs
• Thinking LEAST only works with positive numbers
• Misreading LEAST as GREATEST

5. You want to find the highest and lowest scores among three exams for each student in a table Students with columns exam1, exam2, and exam3. Which query correctly returns the student ID, highest score as max_score, and lowest score as min_score?

hard

A. SELECT student_id, GREATEST(exam1, exam2, exam3) AS max_score, LEAST(exam1, exam2, exam3) AS min_score FROM Students;

B. SELECT student_id, MAX(exam1, exam2, exam3) AS max_score, MIN(exam1, exam2, exam3) AS min_score FROM Students;

C. SELECT student_id, GREATEST(exam1, exam2, exam3) AS min_score, LEAST(exam1, exam2, exam3) AS max_score FROM Students;

D. SELECT student_id, MAX(exam1, exam2, exam3) AS min_score, MIN(exam1, exam2, exam3) AS max_score FROM Students;

Solution

1. Step 1: Understand GREATEST and LEAST usage

   GREATEST returns the highest value among columns, LEAST returns the lowest.

2. Step 2: Check the query syntax

   SELECT student_id, GREATEST(exam1, exam2, exam3) AS max_score, LEAST(exam1, exam2, exam3) AS min_score FROM Students; correctly uses GREATEST and LEAST with column names and aliases them properly. MAX and MIN are aggregate functions and cannot take multiple columns as arguments directly.

3. Final Answer:

   Option A query -> Option A

4. Quick Check:

   Use GREATEST and LEAST for column-wise max/min [OK]

Hint: Use GREATEST/LEAST for row-wise max/min, not MAX/MIN [OK]

Common Mistakes:

• Using MAX/MIN with multiple columns incorrectly
• Swapping GREATEST and LEAST aliases
• Syntax errors with aggregate functions