Welcome to our SQL tutorial on the MIN and MAX functions! In this lesson, we'll explore how to find the minimum and maximum values in a database table. Let's dive right in!
In SQL, the MIN and MAX functions are used to retrieve the smallest (MIN) and largest (MAX) value from a specified column within a table. They are incredibly useful when working with data, and you'll find them invaluable in many real-world projects.
The MIN function returns the smallest value in a specified column. Here's an example using the following table:
CREATE TABLE Numbers (
id INT,
number INT
);
INSERT INTO Numbers (id, number) VALUES
(1, 5),
(2, 3),
(3, 7),
(4, 2);To find the smallest number in the "number" column, use the following query:
SELECT MIN(number) FROM Numbers;The result will be:
2
Always use the MIN function when you want to find the smallest value in a column.
The MAX function returns the largest value in a specified column. Using the same Numbers table, let's find the largest number:
SELECT MAX(number) FROM Numbers;The result will be:
7
Always use the MAX function when you want to find the largest value in a column.
Let's say you have a table of employee salaries. You can find the highest and lowest salaries with the MIN and MAX functions.
CREATE TABLE Employees (
id INT,
name VARCHAR(255),
salary DECIMAL(10, 2)
);
INSERT INTO Employees (id, name, salary) VALUES
(1, 'John Doe', 50000.00),
(2, 'Jane Smith', 60000.00),
(3, 'Bob Johnson', 55000.00);To find the lowest salary:
SELECT MIN(salary) FROM Employees;The result will be:
50000.00
To find the highest salary:
SELECT MAX(salary) FROM Employees;The result will be:
60000.00
MIN and MAX functions can be very useful for analyzing data, such as finding the highest or lowest sale, the minimum or maximum stock, and more.
You can use MIN and MAX functions in complex queries as well. For instance, let's find the employee with the highest salary and the minimum salary:
SELECT name, salary
FROM Employees
ORDER BY salary DESC
LIMIT 1;
SELECT name, salary
FROM Employees
ORDER BY salary ASC
LIMIT 1;The first query returns the employee with the highest salary:
Jane Smith 60000.00
The second query returns the employee with the lowest salary:
Bob Johnson 50000.00
You can use MIN and MAX functions in combination with other SQL functions to create powerful queries.
Which SQL function returns the smallest value in a specified column?
Which SQL function returns the largest value in a specified column?