# SQL MIN() and 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

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!