Sql Min Max – Guided Study Workspace

Introduction

In data analysis and business intelligence, identifying the extreme boundaries of a dataset is a daily necessity. MIN and MAX answer two fundamental questions:

MIN: What is the smallest, lowest, or earliest value? (e.g. lowest product price, earliest order date, minimum test score).
MAX: What is the largest, highest, or latest value? (e.g. highest employee salary, latest customer sign-up, peak server load).

What Are MIN and MAX?

MIN() and MAX() are aggregate functions built into all mainstream SQL engines. They scan a set of values and return the single extreme scalar result:

SELECT MIN(price) FROM products;
SELECT MAX(price) FROM products;

Basic Syntax

MIN(expression)
MAX(expression)

The expression is usually a column name (such as price or created_at), but it can also be an arithmetic calculation.

Your First MIN Query

Given product prices: [100, 250, 80, 400]:

SELECT MIN(price) AS lowest_price
FROM products;

Result: 80.

Diagram 1: MIN() Extreme Flow

Values: 100, 250, 80, 400

➔ MIN() ➔

Smallest = 80

Your First MAX Query

Using the same dataset [100, 250, 80, 400]:

SELECT MAX(price) AS highest_price
FROM products;

Result: 400.

Diagram 2: MAX() Extreme Flow

Values: 100, 250, 80, 400

➔ MAX() ➔

Largest = 400

MIN / MAX With WHERE

When paired with a WHERE clause, the database filters the candidate rows first before locating the extreme value:

SELECT MAX(salary) AS top_sales_salary
FROM employees
WHERE department = 'Sales';

SELECT MIN(price) AS cheapest_electronics
FROM products
WHERE category = 'Electronics';

🧠

Mental Model: Table ➔ WHERE filters rows ➔ Matching values ➔ MIN / MAX ➔ Single Result.

MIN / MAX and NULL Rules

Like all standard aggregate functions, MIN and MAX completely ignore NULL values.

Given prices [100, NULL, 250, 80, NULL]:

NULLs are discarded: only [100, 250, 80] are evaluated.
MIN() is 80 (NULL is NOT treated as 0).
MAX() is 250.

⚠️

Edge Case: If all rows contain NULL or if 0 rows match the WHERE filter, MIN() and MAX() return NULL.

Diagram 3: NULL Ignored in Boundary Finding

Values: 100, NULL, 250, 80
Ignore NULL ➔ [100, 250, 80]

➔ Evaluate ➔

MIN = 80 & MAX = 250

MIN / MAX With Duplicate Values

Duplicate values have zero impact on the final minimum or maximum.

For example, with values [100, 100, 250, 80, 80]:

MIN() is still 80.
MAX() is still 250.

MIN / MAX With Dates & Timestamps

MIN and MAX work seamlessly on chronological date and timestamp columns:

SELECT MIN(order_date) AS first_order_date,
MAX(order_date) AS latest_order_date
FROM orders;

MIN(order_date): Returns the earliest / oldest date in the records.
MAX(order_date): Returns the latest / most recent date in the records.

MIN / MAX With Text Columns

SQL systems (such as PostgreSQL and MySQL) also support MIN and MAX on text strings using alphabetical collation ordering:

SELECT MIN(name), MAX(name) FROM customers;

MIN(name) returns the name that comes first alphabetically (e.g. 'Aarav'), while MAX(name)returns the name that comes last (e.g. 'Zara').

MIN / MAX vs ORDER BY + LIMIT

Beginners often wonder how MIN(price) differs from sorting and taking the top row:

Aggregate Function: MIN(price)

SELECT MIN(price)
FROM products;

Returns a single scalar aggregate value. If table is empty, returns 1 row with NULL.

Row Stream: ORDER BY + LIMIT

SELECT price
FROM products
ORDER BY price ASC
LIMIT 1;

Sorts candidate rows and limits the output rows. If table is empty, returns 0 rows.

⚡ Live Interactive MIN / MAX Lab

📦 Products Catalog & PricesVisual Extreme Finder

#	Product	Category	Price	Extreme Status
1	Wireless Mouse	Electronics	₹250	Valid non-NULL value
2	Cotton T-Shirt	Clothing	₹100	Valid non-NULL value
3	Mechanical Keyboard	Electronics	₹800	Valid non-NULL value
4	Clearance USB Cable	Electronics	₹80	⭐ Minimum Value
5	Denim Jeans	Clothing	₹250	Valid non-NULL value
6	4K Ultra Gaming Monitor	Electronics	₹2,500	Valid non-NULL value
7	Custom Prototype Hub	Electronics	NULL	NULL (Ignored)
8	Basic Crew Socks	Clothing	₹80	⭐ Minimum Value
9	Specialty Winter Jacket	Clothing	NULL	NULL (Ignored)

✍️ SQL Extreme Query● Scalar Output

SELECT MIN(price) AS lowest_price
FROM products;

₹80

Output column: lowest_price

Evaluation: MIN of [₹250, ₹100, ₹800, ₹80, ₹250, ₹2500, ₹80] ➔ ₹80

Common MIN / MAX Mistakes

1. Assuming MIN/MAX Returns the Whole Row

SELECT MAX(salary) FROM employees; returns only the scalar number (e.g. ₹90,000), not the employee name who earns it.

2. Treating NULL as Zero

MIN() does NOT treat NULL as zero. With values [100, NULL, 50], MIN returns 50, not 0.

3. Confusing MIN/MAX with COUNT

COUNT counts the rows; MIN and MAX find the lowest and highest values in those rows.

🧠 Interactive Challenge: Predict The Result (1 of 3)

SELECT MIN(price) FROM products;

Given prices [100, NULL, 250, 80, 250], what will MIN(price) return?

⭐

MIN vs MAX Scenario Challenge

Test your intuition: choose whether you would use MIN or MAX for each real-world analytics scenario:

Finding the lowest product price in your catalog

Column context: Price column (e.g. ₹80 to ₹2500)

Identifying the highest salary in the Sales department

Column context: Salary column (e.g. ₹45k to ₹120k)

Discovering the earliest customer registration date

Column context: Created_at date column (e.g. 2021-01-10 to 2026-08-20)

Finding the latest shipment update timestamp

Column context: Updated_at timestamp column

Practical Step-by-Step Exercises

Task Goal	Target Table	Required SQL Solution	Function Pattern
1. Lowest Product Price	`products`	`SELECT MIN(price) FROM products;`	Table-level MIN
2. Highest Product Price	`products`	`SELECT MAX(price) FROM products;`	Table-level MAX
3. Earliest Order Date	`orders`	`SELECT MIN(order_date) FROM orders;`	Date MIN
4. Latest Order Date	`orders`	`SELECT MAX(order_date) FROM orders;`	Date MAX
5. Top Sales Salary	`employees`	`SELECT MAX(salary) FROM employees WHERE department = 'Sales';`	WHERE + MAX

MIN / MAX Best Practices

Always Alias Your Extremes: Write SELECT MIN(price) AS lowest_price for intuitive reporting headers.
Filter Before Aggregating: Narrow your dataset using WHERE clauses for specific categories or date spans.
Remember That NULL is Excluded: Never assume NULL is treated as 0 or the lowest value.
Do Not Expect Row IDs: If you need the full row with the minimum or maximum value, use subqueries or ordering techniques.

What You Should Know Now

✓MIN(col): Finds smallest number, earliest date, or first text
✓MAX(col): Finds largest number, latest date, or last text
✓NULL Handling: NULL values are ignored during evaluation
✓Empty Sets: Returns NULL when 0 valid rows match
✓Duplicates: Duplicates do not alter the minimum or maximum
✓Scalar Output: Returns a single boundary value, not the whole row

🎯 Knowledge Check Quiz: SQL MIN / MAX

Test your understanding of boundary aggregations, NULL rules, date handling, duplicates, and row isolation.

1. What is the primary role of the SQL MIN() aggregate function?

2. What is the primary role of the SQL MAX() aggregate function?

3. How do MIN() and MAX() handle NULL values in a column?

4. Given the list of values [100, 100, 250, 80, 80], how do the duplicate values affect the MIN and MAX?

5. When applied to DATE or TIMESTAMP columns, what do MIN(date) and MAX(date) represent?

6. Does the query `SELECT MAX(salary) FROM employees;` return the name of the employee who earns that salary?

7. What is a key difference between `SELECT MIN(price) FROM products;` and `SELECT price FROM products ORDER BY price ASC LIMIT 1;`?

8. If a table has 5 rows and all 5 rows have NULL in the target column, what will `SELECT MAX(column) FROM table;` return?