Bird
Raised Fist0
SQLquery~5 mins

MIN and MAX functions in SQL - Cheat Sheet & Quick Revision

Choose your learning style10 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime

Start learning this pattern below

Jump into concepts and practice - no test required

or
Recommended
Test this pattern10 questions across easy, medium, and hard to know if this pattern is strong
Recall & Review
beginner
What does the MIN() function do in SQL?
The MIN() function returns the smallest value from a column in a table.
Click to reveal answer
beginner
What does the MAX() function do in SQL?
The MAX() function returns the largest value from a column in a table.
Click to reveal answer
intermediate
Can MIN() and MAX() be used with text columns?
Yes, MIN() and MAX() can be used with text columns. They return the smallest or largest value based on alphabetical order.
Click to reveal answer
intermediate
How do MIN() and MAX() behave with NULL values?
MIN() and MAX() ignore NULL values when calculating the smallest or largest value.
Click to reveal answer
beginner
Write a SQL query to find the highest salary from an 'employees' table.
SELECT MAX(salary) AS highest_salary FROM employees;
Click to reveal answer
What will the query SELECT MIN(age) FROM users; return?
AThe total number of users
BThe oldest age in the users table
CThe youngest age in the users table
DThe average age of users
Which SQL function returns the largest value in a column?
AMAX()
BMIN()
CSUM()
DCOUNT()
If a column contains NULL values, how do MIN() and MAX() treat them?
AThey include NULLs in the result
BThey ignore NULLs
CThey return NULL
DThey cause an error
What does SELECT MAX(name) FROM customers; return if 'name' is a text column?
AThe alphabetically last name
BThe longest name
CThe alphabetically first name
DThe shortest name
Which of these queries finds the smallest price in a 'products' table?
ASELECT MAX(price) FROM products;
BSELECT COUNT(price) FROM products;
CSELECT SUM(price) FROM products;
DSELECT MIN(price) FROM products;
Explain how the MIN() and MAX() functions work in SQL and give an example of each.
Think about finding lowest and highest values in a list.
You got /4 concepts.
    Describe how NULL values affect the results of MIN() and MAX() functions in SQL.
    Consider what happens if some data is missing.
    You got /3 concepts.

      Practice

      (1/5)
      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