GREATEST and LEAST functions in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to find the greatest value among three numbers.

PostgreSQL

SELECT GREATEST([1]) AS max_value;

Drag options to blanks, or click blank then click option'

A5, 10, 3

B5 10 3

C'5', '10', '3'

D(5, 10, 3)

Attempts:

3 left

💡 Hint

Common Mistakes

Passing numbers without commas

Using quotes around numbers making them strings

Passing a tuple instead of separate arguments

The GREATEST function takes multiple arguments separated by commas and returns the largest value. So, 5, 10, 3 is the correct way to pass the numbers.

2fill in blank

medium

Complete the code to find the least value among columns price1, price2, and price3 in the products table.

PostgreSQL

SELECT LEAST([1]) AS min_price FROM products;

Drag options to blanks, or click blank then click option'

Aprice1 price2 price3

Bprice1, price2, price3

C'price1', 'price2', 'price3'

D(price1, price2, price3)

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting commas between column names

Putting column names in quotes

Using parentheses around the list as a single argument

The LEAST function compares multiple columns or values separated by commas and returns the smallest one. So, the columns must be separated by commas without quotes.

3fill in blank

hard

Fix the error in the code to correctly find the greatest value between two columns colA and colB.

PostgreSQL

SELECT GREATEST([1]) AS max_val FROM data;

Drag options to blanks, or click blank then click option'

A(colA, colB)

BcolA colB

CcolA, colB

D'colA', 'colB'

Attempts:

3 left

💡 Hint

Common Mistakes

Missing commas between column names

Using quotes around column names

Passing columns as a tuple

The GREATEST function requires its arguments to be separated by commas without quotes or parentheses around them as a single argument. So, colA, colB is correct.

4fill in blank

hard

Fill both blanks to find the least value between two columns and rename the output as lowest.

PostgreSQL

SELECT LEAST([1]) AS [2] FROM sales;

Drag options to blanks, or click blank then click option'

Acost, price

Bmin_value

Clowest

Dcost price

Attempts:

3 left

💡 Hint

Common Mistakes

Not separating columns with commas

Using invalid alias names or quotes around alias

Putting spaces instead of commas between columns

The LEAST function needs the columns separated by commas, and the alias for the output can be any valid name like lowest.

5fill in blank

hard

Fill all three blanks to select the greatest value between col1 and col2, rename it as max_col, and filter rows where the greatest value is greater than 100.

PostgreSQL

SELECT GREATEST([1]) AS [2] FROM records WHERE GREATEST([3]) > 100;

Drag options to blanks, or click blank then click option'

Acol1, col2

Bmax_col

Dcol1 col2

Attempts:

3 left

💡 Hint

Common Mistakes

Using spaces instead of commas between columns

Using different column lists in SELECT and WHERE

Using quotes around alias or columns

The GREATEST function requires columns separated by commas. The alias max_col names the output. The WHERE clause uses the same columns separated by commas to filter rows.

Practice

1. What does the GREATEST function do in PostgreSQL?

easy

A. Counts the number of values in a list

B. Returns the smallest value from a list of values

C. Returns the largest value from a list of values

D. Calculates the average of values in a list

Solution

1. Step 1: Understand the function purpose

   The GREATEST function compares all given values and finds the largest one.

2. Step 2: Compare with other options

   LEAST returns the smallest, counting and averaging are different functions.

3. Final Answer:

   Returns the largest value from a list of values -> Option C

4. Quick Check:

   GREATEST = largest value [OK]

Hint: GREATEST picks the biggest value from all inputs [OK]

Common Mistakes:

• Confusing GREATEST with LEAST
• Thinking it counts or averages values
• Assuming it returns a boolean

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

easy

A. SELECT LEAST[5, 10, 3];

B. SELECT LEAST(5, 10, 3);

C. SELECT LEAST{5, 10, 3};

D. SELECT LEAST 5, 10, 3;

Solution

1. Step 1: Recall correct function call syntax

   PostgreSQL functions use parentheses with comma-separated values.

2. Step 2: Check each option

   Only SELECT LEAST(5, 10, 3); uses parentheses correctly; others use brackets, braces, or no parentheses.

3. Final Answer:

   SELECT LEAST(5, 10, 3); -> Option B

4. Quick Check:

   Function calls need parentheses [OK]

Hint: Use parentheses () for function arguments in SQL [OK]

Common Mistakes:

• Using square brackets or braces instead of parentheses
• Omitting parentheses around arguments
• Separating arguments incorrectly

3. What will be the result of this query?

SELECT GREATEST(7, 2, 9, 4) AS max_val, LEAST(7, 2, 9, 4) AS min_val;

medium

A. max_val = 9, min_val = 2

B. max_val = 7, min_val = 4

C. max_val = 4, min_val = 2

D. max_val = 9, min_val = 4

Solution

1. Step 1: Evaluate GREATEST function

   Among 7, 2, 9, 4, the largest value is 9.

2. Step 2: Evaluate LEAST function

   Among the same values, the smallest is 2.

3. Final Answer:

   max_val = 9, min_val = 2 -> Option A

4. Quick Check:

   GREATEST=9, LEAST=2 [OK]

Hint: GREATEST picks max, LEAST picks min from values [OK]

Common Mistakes:

• Mixing up max and min values
• Choosing middle values instead of extremes
• Ignoring all values except first or last

4. Identify the error in this query:

SELECT GREATEST(4, 8, 'ten') AS max_value;

medium

A. Missing closing parenthesis

B. No error, query runs fine

C. GREATEST function requires at least four arguments

D. Mixing integer and string types causes an error

Solution

1. Step 1: Check argument types

   GREATEST compares values of the same type; mixing integers and strings causes a type error.

2. Step 2: Verify syntax and argument count

   Parentheses are correct and function accepts any number of arguments greater than zero.

3. Final Answer:

   Mixing integer and string types causes an error -> Option D

4. Quick Check:

   Type mismatch causes error [OK]

Hint: All arguments must be same type for GREATEST/LEAST [OK]

Common Mistakes:

• Assuming mixed types work without error
• Thinking function needs fixed number of arguments
• Ignoring type errors in SQL

5. You have a table scores with columns math, science, and english. Which query correctly finds the highest and lowest score for each student?

hard

A. SELECT student_id, GREATEST(math, science, english) AS highest, LEAST(math, science, english) AS lowest FROM scores;

B. SELECT student_id, MAX(math, science, english) AS highest, MIN(math, science, english) AS lowest FROM scores;

C. SELECT student_id, GREATEST(math, science, english) AS lowest, LEAST(math, science, english) AS highest FROM scores;

D. SELECT student_id, MAX(math) AS highest, MIN(english) AS lowest FROM scores;

Solution

1. Step 1: Understand GREATEST and LEAST usage

   They compare multiple columns per row to find max and min values.

2. Step 2: Analyze each option

   SELECT student_id, GREATEST(math, science, english) AS highest, LEAST(math, science, english) AS lowest FROM scores; correctly uses GREATEST and LEAST on columns per student. SELECT student_id, MAX(math, science, english) AS highest, MIN(math, science, english) AS lowest FROM scores; uses MAX/MIN incorrectly with multiple columns. SELECT student_id, GREATEST(math, science, english) AS lowest, LEAST(math, science, english) AS highest FROM scores; swaps highest and lowest. SELECT student_id, MAX(math) AS highest, MIN(english) AS lowest FROM scores; only compares single columns, not all three.

3. Final Answer:

   SELECT student_id, GREATEST(math, science, english) AS highest, LEAST(math, science, english) AS lowest FROM scores; -> Option A

4. Quick Check:

   GREATEST/LEAST compare columns per row [OK]

Hint: Use GREATEST/LEAST to compare multiple columns in one row [OK]

Common Mistakes:

• Using MAX/MIN with multiple columns incorrectly
• Swapping highest and lowest functions
• Comparing only one column instead of all