DbSchema Tutorial | SQL MIN() MAX() FUNCTIONS
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
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.
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.
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
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.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.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().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
Write a SQL query to find the maximum and minimum salaries from the
Employeestable.Write a SQL query to find the department with the highest average salary from the
Employeestable.Write a SQL query to find the employees with salaries higher than the average salary of their department.
Write a SQL query to find the first and last employee names in alphabetical order from the
Employeestable.
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!
