DbSchema Tutorial | SQL MIN() MAX() FUNCTIONS

Publish on DbSchema Blog >>>

SQL, or Structured Query Language, is the standard language for managing relational databases. It allows users to store, manipulate, and retrieve data stored in a relational database. One essential aspect of manipulating data involves performing calculations on the data. SQL provides several functions that can be used to perform calculations on the data in a database. Two of these functions are MIN() and MAX().

SQL MIN() Function

The MIN() function in SQL is used to return the smallest value of the selected column. It is an aggregate function, which means it operates on each row of the data to result in a single value.

Syntax

The syntax for the MIN() function is straightforward:

SELECT MIN(column_name)
FROM table_name;

Example

Consider the following Employees table:

EmployeeID	FirstName	LastName	Salary
1	John	Doe	50000
2	Jane	Smith	60000
3	Sam	Brown	55000

To find the minimum salary from the Employees table, the SQL query would be:

SELECT MIN(Salary)
FROM Employees;

Result

The result would be:

MIN(Salary)
50000

Explanation

This result indicates that the lowest salary among the employees is 50,000.

SQL MAX() Function

The MAX() function in SQL is used to return the largest value of the selected column.

Syntax

The syntax for the MAX() function is:

SELECT MAX(column_name)
FROM table_name;

Example

Using the same Employees table, to find the maximum salary, the SQL query would be:

SELECT MAX(Salary)
FROM Employees;

Result

The result would be:

MAX(Salary)
60000

Explanation

This result indicates that the highest salary among the employees is 60,000.

Using MIN() and MAX() with Other Columns

It is possible to use the MIN() and MAX() functions with other columns in the same query. However, it is important to note that when used with other columns, the values of these columns are not necessarily related to the minimum or maximum value found.

Example

Consider the following query:

SELECT MIN(Salary), MAX(Salary), FirstName, LastName
FROM Employees;

Result

The result would be:

MIN(Salary)	MAX(Salary)	FirstName	LastName
50000	60000	John	Doe

Explanation

In this result, the FirstName and LastName columns are just showing the values from the first row of the Employees table, and they are not related to the minimum or maximum salaries.

Using MIN() and MAX() in the Same Query

It is possible to use both the MIN() and MAX() functions in the same query.

Example

Using the Employees table, the query would be:

SELECT MIN(Salary) as MinSalary, MAX(Salary) as MaxSalary
FROM Employees;

Result

The result would be:

MinSalary	MaxSalary
50000	60000

Explanation

In this result, we have both the minimum and maximum salaries displayed in the same row.

Use SQL MIN function with GROUP BY statement

The GROUP BY statement is often used with aggregate functions like MIN() to return the minimum value for each group of data.

Example

Consider the following Employees table with an additional Department column:

EmployeeID	FirstName	LastName	Salary	Department
1	John	Doe	50000	IT
2	Jane	Smith	60000	HR
3	Sam	Brown	55000	IT

The query to find the minimum salary in each department would be:

SELECT Department, MIN(Salary)
FROM Employees
GROUP BY Department;

Result

The result would be:

Department	MIN(Salary)
HR	60000
IT	50000

Explanation

This result shows the minimum salary in each department.

Use SQL MAX function with GROUP BY statement

Similarly, the MAX() function can also be used with the GROUP BY statement.

Example

Using the same Employees table, the query to find the maximum salary in each department would be:

SELECT Department, MAX(Salary)
FROM Employees
GROUP BY Department;

Result

The result would be:

Department	MAX(Salary)
HR	60000
IT	55000

Explanation

This result shows the maximum salary in each department.

USE SQL MIN function with ORDER BY statement

The ORDER BY statement is used to sort the result set in ascending or descending order. It can be used with the MIN() function to order the result.

Example

Consider the following query:

SELECT Department, MIN(Salary) as MinSalary
FROM Employees
GROUP BY Department
ORDER BY MinSalary;

Result

The result would be:

Department	MinSalary
IT	50000
HR	60000

Explanation

The result set is ordered by the minimum salary in ascending order.

USE SQL MAX function with ORDER BY statement

The ORDER BY statement can also be used with the MAX() function.

Example

Consider the following query:

SELECT Department, MAX(Salary) as MaxSalary
FROM Employees
GROUP BY Department
ORDER BY MaxSalary DESC;

Result

The result would be:

Department	MaxSalary
HR	60000
IT	55000

Explanation

The result set is ordered by the maximum salary in descending order.

USE SQL MIN function with other aggregate functions like (MAX, AVG, SUM)

The MIN() function can be used in the same query with other aggregate functions like MAX(), AVG(), and SUM().

Example

Consider the following query:

SELECT Department, 
       MIN(Salary) as MinSalary, 
       MAX(Salary) as MaxSalary, 
       AVG(Salary) as AvgSalary, 
       SUM(Salary) as TotalSalary
FROM Employees
GROUP BY Department;

Result

The result would be:

Department	MinSalary	MaxSalary	AvgSalary	TotalSalary
HR	60000	60000	60000	60000
IT	50000	55000	52500	105000

Explanation

This result shows the minimum, maximum, average, and total salaries in each department.

USE SQL MAX function with other aggregate functions like (MAX, AVG, SUM)

Similarly, the MAX() function can be used with other aggregate functions.

Example

Using the same Employees table and query from the previous example, the result would be the same as shown above.

USE MIN function within WHERE Clause

The MIN() function cannot directly be used within a WHERE clause. However, you can use a subquery with the MIN() function in the WHERE clause.

Example

Consider the following query to find the employees with the minimum salary:

SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Salary = (SELECT MIN(Salary) FROM Employees);

Result

The result would be:

EmployeeID	FirstName	LastName	Salary
1	John	Doe	50000

Explanation

This result shows the details of the employee(s) with the minimum salary.

USE MAX function within WHERE Clause

Similarly, the MAX() function can be used in a subquery within the WHERE clause.

Example

Consider the following query to find the employees with the maximum salary:

SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Salary = (SELECT MAX(Salary) FROM Employees);

Result

The result would be:

EmployeeID	FirstName	LastName	Salary
2	Jane	Smith	60000

Explanation

This result shows the details of the employee(s) with the maximum salary.

Aliases with MAX() and MIN()

Aliases can be used with the MIN() and MAX() functions to give the result column a more meaningful name.

Example

Consider the following query:

SELECT MIN(Salary) as MinimumSalary, MAX(Salary) as MaximumSalary
FROM Employees;

Result

The result would be:

MinimumSalary	MaximumSalary
50000	60000

Explanation

In this result, the columns are labeled MinimumSalary and MaximumSalary instead of MIN(Salary) and MAX(Salary).

MAX() and MIN() with strings

The MAX() and MIN() functions can also be used with columns containing string values. The MIN() function will return the lowest value in the column, and the MAX() function will return the highest value in the column.

Example

Consider the following Employees table:

EmployeeID	FirstName	LastName
1	John	Doe
2	Jane	Smith
3	Sam	Brown

The query to find the first and last names in alphabetical order would be:

SELECT MIN(FirstName) as FirstAlphabeticalName, MAX(FirstName) as LastAlphabeticalName
FROM Employees;

Result

The result would be:

FirstAlphabeticalName	LastAlphabeticalName
Jane	Sam

Explanation

In this result, Jane is the first name in alphabetical order, and Sam is the last name in alphabetical order.

Selecting Full Row With MIN Or MAX Value

It is a common requirement to select the entire row with the minimum or maximum value in a particular column. This can be done using a subquery and the WHERE clause.

Example

Consider the following query to find the details of the employee with the minimum salary:

SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Salary = (SELECT MIN(Salary) FROM Employees);

Result

The result would be:

EmployeeID	FirstName	LastName	Salary
1	John	Doe	50000

Explanation

This result shows the details of the employee with the minimum salary.

Using MIN() and MAX() in the HAVING CLAUSE

The HAVING clause is used with the GROUP BY statement to filter the groups based on a condition. The MIN() and MAX() functions can be used in the HAVING clause to filter the groups.

Example

Consider the following query to find the departments with a maximum salary greater than 55,000:

SELECT Department, MAX(Salary) as MaxSalary
FROM Employees
GROUP BY Department
HAVING MaxSalary > 55000;

Result

The result would be:

Department	MaxSalary
HR	60000

Explanation

This result shows the department(s) with a maximum salary greater than 55,000.

Common Mistakes

1. Using MIN() or MAX() in the WHERE clause: The MIN() and MAX() functions cannot be directly used in the WHERE clause. Instead, they should be used in a subquery.

2. Ignoring NULL values: The MIN() and MAX() functions ignore NULL values. If a column contains only NULL values, the result for that column will be NULL.

3. Using MIN() or MAX() with non-numeric strings: When used with non-numeric strings, the MIN() function returns the value that is the lowest in the alphabetical order, and the MAX() function returns the value that is the highest in the alphabetical order.

FAQs

1. Can the MIN() and MAX() functions be used with columns containing NULL values?
   Yes, the MIN() and MAX() functions can be used with columns containing NULL values. However, these functions ignore NULL values.

2. Can the MIN() and MAX() functions be used with columns containing string values?
   Yes, the MIN() and MAX() functions can be used with columns containing string values. The MIN() function will return the value that is the lowest in the alphabetical order, and the MAX() function will return the value that is the highest in the alphabetical order.

3. Can the MIN() and MAX() functions be used with other aggregate functions?
   Yes, the MIN() and MAX() functions can be used in the same query with other aggregate functions like AVG() and SUM().

4. Can the MIN() and MAX() functions be used in the same query?
   Yes, the MIN() and MAX() functions can be used in the same query.

Practice Questions

1. Write a SQL query to find the maximum and minimum salaries from the Employees table.

2. Write a SQL query to find the department with the highest average salary from the Employees table.

3. Write a SQL query to find the employees with salaries higher than the average salary of their department.

4. Write a SQL query to find the first and last employee names in alphabetical order from the Employees table.

Conclusion

The MIN() and MAX() functions are powerful tools in SQL that allow you to find the minimum and maximum values in a column. These functions can be used with other columns, in the same query with other aggregate functions, and with the GROUP BY, ORDER BY, and HAVING clauses. Understanding how to use these functions effectively will help you manipulate and analyze your data more efficiently.

Remember, practice is key to becoming proficient in SQL. Be sure to try out the practice questions and experiment with different queries to get a better understanding of the MIN() and MAX() functions.

Happy querying!