SkillsetMaster - AI, Web Development & Data Analytics Courses

What You Will Learn

Sample Students Table

id	name	age	grade
1	Alice	18	B
2	John	20	A
3	Peter	22	A
4	Zoe	24	B

4 rows

In this lesson, you will understand:

How to find minimum values with MIN
How to find maximum values with MAX
Using MIN and MAX with different data types
Combining these functions with WHERE
Real-world applications

What are MIN and MAX?

Sample Students Table

id	name	age	grade
1	Alice	18	B
2	John	20	A
3	Peter	22	A
4	Zoe	24	B

4 rows

MIN finds the smallest value in a column. MAX finds the largest value in a column.

Think of it like:

Finding the youngest or oldest person
Finding the cheapest or most expensive product
Finding the earliest or latest date

MIN - Find Minimum Value

Sample Students Table

id	name	age	grade
1	Alice	18	B
2	John	20	A
3	Peter	22	A
4	Zoe	24	B

4 rows

MIN finds the smallest value.

Example 1: Find Youngest Student

Result: MIN(age)

MIN(age)
18

1 row

SELECT MIN(age) FROM students;

If ages are 18, 20, 22, 24 then MIN(age) = 18 (youngest)

Example 2: MIN with Different Data Types

Result: MIN with Different Types

Function	Result	Meaning
MIN(price)	9.99	Cheapest product
MIN(hire_date)	2020-01-15	Earliest hire
MIN(name)	Alice	First alphabetically

3 rows

Numbers:

SELECT MIN(price) FROM products;  -- Cheapest product

Dates:

SELECT MIN(hire_date) FROM employees;  -- Earliest hire

Text:

SELECT MIN(name) FROM students;  -- First alphabetically

If names are John, Alice, Zoe then MIN(name) = Alice

Example 3: MIN with WHERE

Result: Youngest A-Grade Student

youngest_a_student
20

1 row

SELECT MIN(age) AS youngest_a_student
FROM students
WHERE grade = 'A';

Finds youngest student with grade A.

MAX - Find Maximum Value

Sample Students Table

id	name	age	grade
1	Alice	18	B
2	John	20	A
3	Peter	22	A
4	Zoe	24	B

4 rows

MAX finds the largest value.

Example 4: Find Oldest Student

Result: MAX(age)

MAX(age)
24

1 row

SELECT MAX(age) FROM students;

If ages are 18, 20, 22, 24 then MAX(age) = 24 (oldest)

MAX with Different Types

Numbers:

SELECT MAX(price) FROM products;   -- Most expensive
SELECT MAX(salary) FROM employees; -- Highest salary
SELECT MAX(score) FROM games;      -- High score

Dates:

SELECT MAX(birth_date) FROM students;    -- Youngest person
SELECT MAX(order_date) FROM orders;      -- Most recent order
SELECT MAX(last_login) FROM users;       -- Latest login

Text:

SELECT MAX(name) FROM students;  -- Last name alphabetically

Result:

If names are: John, Alice, Zoe
MAX(name) = Zoe (Z comes last alphabetically)

MAX with WHERE

SELECT MAX(salary) FROM employees WHERE department = 'Engineering';

Finds highest paid engineer.

More examples:

-- Oldest student with grade A
SELECT MAX(age) FROM students WHERE grade = 'A';

-- Most recent completed order
SELECT MAX(order_date) FROM orders WHERE status = 'completed';

-- Highest price product in stock
SELECT MAX(price) FROM products WHERE stock > 0;

Combining Multiple Functions

Sample Students Table

id	name	age	grade
1	Alice	18	B
2	John	20	A
3	Peter	22	A
4	Zoe	24	B

4 rows

Example 5: Combining MIN and MAX

Result: MIN and MAX Combined

youngest	oldest	age_range
18	24	6

1 row

You can use multiple aggregate functions together:

SELECT
  MIN(age) AS youngest,
  MAX(age) AS oldest,
  MAX(age) - MIN(age) AS age_range
FROM students;

This shows the youngest age, oldest age, and the difference between them.

Real-World Examples

Sample Students Table

id	name	age	grade
1	Alice	18	B
2	John	20	A
3	Peter	22	A
4	Zoe	24	B

4 rows

Example 1: Product Price Analysis

SELECT
  category,
  COUNT(*) AS products,
  MIN(price) AS min_price,
  MAX(price) AS max_price,
  ROUND(AVG(price), 2) AS avg_price
FROM products
WHERE stock > 0
GROUP BY category;

Note: GROUP BY will be covered in next lesson.

Example 2: Employee Salary Report

SELECT
  department,
  COUNT(*) AS employee_count,
  MIN(salary) AS lowest_salary,
  MAX(salary) AS highest_salary,
  ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department;

Example 3: Student Performance

SELECT
  MIN(score) AS lowest_score,
  MAX(score) AS highest_score,
  ROUND(AVG(score), 2) AS average_score
FROM exam_results
WHERE exam_date = '2024-11-15';

Example 4: Order Statistics

SELECT
  COUNT(*) AS total_orders,
  MIN(total) AS smallest_order,
  MAX(total) AS largest_order,
  ROUND(AVG(total), 2) AS avg_order_value
FROM orders
WHERE order_date >= '2024-01-01';

Example 5: Temperature Data

SELECT
  MIN(temperature) AS min_temp,
  MAX(temperature) AS max_temp,
  ROUND(AVG(temperature), 1) AS avg_temp
FROM weather_data
WHERE date BETWEEN '2024-11-01' AND '2024-11-30';

Finding the Record with MIN or MAX

Sample Students Table

id	name	age	grade
1	Alice	18	B
2	John	20	A
3	Peter	22	A
4	Zoe	24	B

4 rows

These functions only return the value, not the entire row.

If you want the full row:

Wrong approach:

SELECT * FROM products WHERE price = AVG(price);
-- Error: Cannot use aggregate in WHERE

Correct approach:

-- Find most expensive product
SELECT * FROM products
WHERE price = (SELECT MAX(price) FROM products);

-- Find youngest student
SELECT * FROM students
WHERE age = (SELECT MIN(age) FROM students);

Alternative using ORDER BY + LIMIT:

-- Most expensive product
SELECT * FROM products ORDER BY price DESC LIMIT 1;

-- Youngest student
SELECT * FROM students ORDER BY age LIMIT 1;

NULL Handling

Sample Students Table

id	name	age	grade
1	Alice	18	B
2	John	20	A
3	Peter	22	A
4	Zoe	24	B

4 rows

All three functions ignore NULL values:

Values: 10, NULL, 20, 30, NULL

AVG = (10+20+30) / 3 = 20  (NULLs ignored)
MIN = 10                    (NULLs ignored)
MAX = 30                    (NULLs ignored)

If all values are NULL:

SELECT AVG(bonus) FROM employees WHERE bonus IS NULL;
-- Returns: NULL (not 0)

Common Patterns

Sample Students Table

id	name	age	grade
1	Alice	18	B
2	John	20	A
3	Peter	22	A
4	Zoe	24	B

4 rows

Pattern 1: Price Range

SELECT
  MIN(price) AS min,
  MAX(price) AS max,
  MAX(price) - MIN(price) AS price_range
FROM products;

Pattern 2: Age Distribution

SELECT
  MIN(age) AS youngest,
  MAX(age) AS oldest,
  MAX(age) - MIN(age) AS age_span,
  ROUND(AVG(age), 1) AS avg_age
FROM users;

Pattern 3: Performance Metrics

SELECT
  MIN(response_time) AS fastest,
  MAX(response_time) AS slowest,
  ROUND(AVG(response_time), 2) AS avg_time
FROM api_logs
WHERE date = CURRENT_DATE;

Common Mistakes

Sample Students Table

id	name	age	grade
1	Alice	18	B
2	John	20	A
3	Peter	22	A
4	Zoe	24	B

4 rows

Mistake 1: Using Aggregate in WHERE

Wrong:

SELECT * FROM products WHERE price > AVG(price);

Correct:

SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

Mistake 2: Mixing Aggregates with Regular Columns

Wrong:

SELECT name, AVG(age) FROM students;
-- Error: name is not aggregated

Correct (use GROUP BY):

SELECT class, AVG(age) FROM students GROUP BY class;

Or just the aggregate:

SELECT AVG(age) FROM students;

Mistake 3: Not Rounding Averages

Hard to read:

SELECT AVG(price) FROM products;
-- Result: 125.4567891234

Better:

SELECT ROUND(AVG(price), 2) FROM products;
-- Result: 125.46

Mistake 4: Comparing Text MIN/MAX Wrong

Be aware:

SELECT MIN(name) FROM students;
-- Returns: 'Alice' (alphabetically first)
-- NOT the shortest name!

For shortest name length:

SELECT MIN(LENGTH(name)) FROM students;

Summary

Sample Students Table

id	name	age	grade
1	Alice	18	B
2	John	20	A
3	Peter	22	A
4	Zoe	24	B

4 rows

Key points to remember:

AVG calculates average of numeric values
MIN finds smallest value (numbers, dates, or text)
MAX finds largest value (numbers, dates, or text)
All three ignore NULL values
Use ROUND with AVG for cleaner results
Can combine multiple aggregate functions
Use WHERE to filter before aggregating
Cannot use aggregates directly in WHERE
For dates: MIN = earliest, MAX = latest
For text: MIN = alphabetically first, MAX = last

What Comes Next

Sample Students Table

id	name	age	grade
1	Alice	18	B
2	John	20	A
3	Peter	22	A
4	Zoe	24	B

4 rows

Now you know MIN and MAX. Next, you will learn about GROUP BY to calculate aggregates for different groups in your data.

SQL Editor

Find youngest and oldest ages

Output

Click "Run Query" to see results

SQL Editor

Calculate age range

Output

Click "Run Query" to see results

MIN & MAX

What You Will Learn

What are MIN and MAX?

MIN - Find Minimum Value

Example 1: Find Youngest Student

Example 2: MIN with Different Data Types

Example 3: MIN with WHERE

MAX - Find Maximum Value

Example 4: Find Oldest Student

MAX with Different Types

MAX with WHERE

Combining Multiple Functions

Example 5: Combining MIN and MAX

Real-World Examples

Example 1: Product Price Analysis

Example 2: Employee Salary Report

Example 3: Student Performance

Example 4: Order Statistics

Example 5: Temperature Data

Finding the Record with MIN or MAX

NULL Handling

Common Patterns

Pattern 1: Price Range

Pattern 2: Age Distribution

Pattern 3: Performance Metrics

Common Mistakes

Mistake 1: Using Aggregate in WHERE

Mistake 2: Mixing Aggregates with Regular Columns

Mistake 3: Not Rounding Averages

Mistake 4: Comparing Text MIN/MAX Wrong

Summary

What Comes Next

Finished this topic?