SQL MIN() and MAX() Functions

Welcome to The Coding College, your trusted resource for learning coding and programming! In this tutorial, we’ll explore the SQL MIN() and SQL MAX() functions, which are essential for finding the smallest and largest values in your data.

What Are SQL MIN() and MAX() Functions?

• MIN(): Returns the smallest value in a column.
• MAX(): Returns the largest value in a column.

These functions are incredibly useful for identifying minimum or maximum values in datasets, such as the lowest price of a product or the highest score in an exam.

Syntax

SELECT MIN(column_name) AS alias_name,  
       MAX(column_name) AS alias_name  
FROM table_name  
WHERE condition;  

Example Table: products

Examples of MIN() and MAX()

1. Finding the Lowest and Highest Prices

SELECT MIN(price) AS lowest_price,  
       MAX(price) AS highest_price  
FROM products;  

Result:

2. Finding the Minimum and Maximum Stock

SELECT MIN(stock) AS minimum_stock,  
       MAX(stock) AS maximum_stock  
FROM products;  

Result:

Using MIN() and MAX() with GROUP BY

Example: Find the Lowest and Highest Price by Category

SELECT category,  
       MIN(price) AS lowest_price,  
       MAX(price) AS highest_price  
FROM products  
GROUP BY category;  

Result:

Using MIN() and MAX() with WHERE

Example: Find the Minimum Price of Products in the “Electronics” Category

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

Result:

Combining MIN() and MAX() with Other Functions

Example: Retrieve Product Names with Minimum and Maximum Prices

SELECT product_name, price  
FROM products  
WHERE price = (SELECT MIN(price) FROM products)  
   OR price = (SELECT MAX(price) FROM products);  

Result:

Handling NULL Values

• MIN() and MAX() ignore NULL values.
• To account for NULL, use the COALESCE() function to replace them with a default value.

Example: Replace NULL in Price Column

SELECT MIN(COALESCE(price, 0)) AS lowest_price,  
       MAX(COALESCE(price, 0)) AS highest_price  
FROM products;  

Real-World Applications

• E-Commerce Analytics:
  • Identify the cheapest and most expensive products for marketing campaigns.

SELECT MIN(price) AS cheapest_item, MAX(price) AS premium_item FROM products;  

• Stock Management:
  • Determine products with the least and most stock to manage inventory.

SELECT product_name FROM products WHERE stock = (SELECT MIN(stock) FROM products);  

• Employee Records:
  • Find the youngest and oldest employees in a company.

SELECT MIN(birth_date) AS youngest, MAX(birth_date) AS oldest FROM employees;  

• Sales Reports:
  • Calculate the minimum and maximum sales revenue for different regions.

SELECT region, MIN(sales) AS min_sales, MAX(sales) AS max_sales FROM sales_data GROUP BY region;  

Best Practices

1. Combine with GROUP BY:
   Use GROUP BY to apply MIN() and MAX() across categories or groups.
2. Handle NULL Values:
   Replace NULL values with meaningful defaults using COALESCE().
3. Index Critical Columns:
   Indexing columns used in MIN() and MAX() queries improves performance on large datasets.
4. Preview Data:
   Test queries with SELECT * first to understand the dataset structure.

Conclusion

The SQL MIN() and SQL MAX() functions are indispensable for data analysis. They help you quickly identify the smallest and largest values in your datasets, making them perfect for summarizing, reporting, and decision-making.

For more SQL insights and tutorials, visit The Coding College and elevate your SQL skills to new heights!