MySQL provides inbuilt functions to find the minimum and maximum values.
SQL provides 5 aggregate functions. They are:
MIN(): Minimum value
MAX(): Maximum value
SUM(): The sum of values
AVG(): The average values
COUNT(): Counts the number of entries
In this session of the online MySQL course, we'll look at finding the minimum and maximum values in a column.
MySQL MIN() – Minimum value
select MIN(salary) from employee_data;
+————-+
| MIN(salary) |
+————-+
| 70000 |
+————-+
1 row in set (0.00 sec)
MySQL MAX() – Maximum value
select MAX(salary) from employee_data;
+————-+
| MAX(salary) |
+————-+
| 200000 |
+————-+
1 row in set (0.00 sec)
Assignment:
1.List the minimum perks package.
2.List the maximum salary given to a "Programmer".
3.Display the age of the oldest "Marketing Executive".
4.(Tricky!) Find the first and last names of the oldest employee
Possible Answers:
1.mysql> select MIN(perks) from employee_data;
+————+
| MIN(perks) |
+————+
| 9000 |
+————+
1 row in set (0.00 sec)
2.mysql> select MAX(salary) from employee_data
-> where title = "Programmer";
+————-+
| MAX(salary) |
+————-+
| 80000 |
+————-+
1 row in set (0.00 sec)
3.mysql> select MAX(age) from employee_data
-> where title = 'Marketing Executive';
+———-+
| MAX(age) |
+———-+
| 30 |
+———-+
1 row in set (0.00 sec)
4.Here is one way to do this without using aggregate functions.
mysql> select f_name, l_name, age
-> from employee_data
-> order by age DESC limit 1;
+——–+——–+——+
| f_name | l_name | age |
+——–+——–+——+
| Paul | Simon | 43 |
+——–+——–+——+
1 row in set (0.00 sec)