SQL MIN() and MAX() Functions

 In SQL, the MIN() and MAX() functions are aggregate functions used to retrieve the smallest and largest values, respectively, from a set of values in a database column. These functions are often used with the SELECT statement to perform queries on numeric, date, or string data types. Below is an explanation of each function:

MIN() Function

• Purpose: The MIN() function returns the smallest (minimum) value in a column.
• Syntax:

  SELECT MIN(column_name)
  FROM table_name;
  

• Example: Suppose you have a table Employees with a column salary:

  SELECT MIN(salary) AS MinimumSalary
  FROM Employees;
  

  This query will return the smallest salary value from the salary column in the Employees table.

MAX() Function

• Purpose: The MAX() function returns the largest (maximum) value in a column.
• Syntax:

  SELECT MAX(column_name)
  FROM table_name;
  

• Example: Suppose you have a table Employees with a column salary:

  SELECT MAX(salary) AS MaximumSalary
  FROM Employees;
  

  This query will return the largest salary value from the salary column in the Employees table.

Usage with GROUP BY

Both MIN() and MAX() can be used with the GROUP BY clause to find the smallest or largest value within groups of data.

• Example with GROUP BY: Suppose you have a Sales table with columns salesperson_id and sales_amount, and you want to find the minimum and maximum sales for each salesperson:

  SELECT salesperson_id, MIN(sales_amount) AS MinSales, MAX(sales_amount) AS MaxSales
  FROM Sales
  GROUP BY salesperson_id;
  

  This query will return the minimum and maximum sales amount for each salesperson_id from the Sales table.

Additional Points

• NULL values: Both MIN() and MAX() ignore NULL values when calculating the result. For example, if a column contains a mix of values and NULLs, the function will only consider the non-NULL values.
• Data Types: These functions can be used with any data type that supports comparison operations, such as INT, DECIMAL, DATE, VARCHAR, etc.

Example Queries

1. Get the minimum and maximum order dates from an Orders table:

   SELECT MIN(order_date) AS FirstOrderDate, MAX(order_date) AS LastOrderDate
   FROM Orders;
   

2. Get the smallest and largest price in a Products table:

   SELECT MIN(price) AS CheapestProduct, MAX(price) AS MostExpensiveProduct
   FROM Products;
   

3. Find the youngest and oldest employee in a Employees table by birthdate:

   SELECT MIN(birth_date) AS YoungestEmployee, MAX(birth_date) AS OldestEmployee
   FROM Employees;
   

By using MIN() and MAX(), you can efficiently retrieve boundary values from your dataset, helping with data analysis and reporting.