MySQL MAX, MIN Functions

In MySQL, the MAX and MIN functions are used to return the maximum and minimum values, respectively, from a column or expression in a table. Here's how these functions work:

MAX function: The MAX function returns the maximum value from a column or expression. 

SELECT MAX(price) AS max_price FROM Stores;

Query will return a single row with a single column "max_price" that contains the highest value found in the "price" column of the "Stores" table.

MIN function: The MIN function returns the minimum value from a column or expression. 

SELECT MIN(price) AS min_price FROM Stores;

This query will return a single row with a single column "min_price" that contains the lowest value found in the "price" column of the "Stores" table. 

What is in our table:

mysql> SELECT * FROM Stores;
+------+--------+------------+-----------------+-----------+-------+
| s_id | city   | store_name | product         | available | price |
+------+--------+------------+-----------------+-----------+-------+
|    1 | London | London_1   | Gold PSU        |       153 |   100 |
|    2 | London | London_2   | Gold PSU        |        75 |   100 |
|    3 | Berlin | Berlin_1   | Green PSU       |        50 |   120 |
|    4 | Berlin | Berlin_2   | XYZ Motherboard |         5 |    75 |
|    5 | Moscow | Moscow_1   | Extension Cable |        50 |    25 |
|    6 | Moscow | Moscow_2   | LPT Cables      |       500 |    10 |
|    7 | Miami  | Miami_1    | COM Cables      |      1450 |     5 |
|    8 | Paris  | Paris_1    | NIC             |       350 |    15 |
+------+--------+------------+-----------------+-----------+-------+
8 rows in set (0.00 sec)

mysql>

MAX function example:

mysql> SELECT MAX(price) AS MostExpensive FROM Stores;
+---------------+
| MostExpensive |
+---------------+
|           120 |
+---------------+
1 row in set (0.00 sec)

mysql>

Our query selects the maximum value of the "price" column in the "Stores" table and assigns it an alias "MostExpensive". The MAX function is used to calculate the maximum value of the "price" column.

The result is a single row with a single column "MostExpensive" which contains the highest value found in the "price" column of the "Stores" table. The value returned in this case is 120, which means that the highest value found in the "price" column of the "Stores" table is 120. 

MIN function example:

mysql> SELECT MIN(price) AS CheapestOne FROM Stores;
+-------------+
| CheapestOne |
+-------------+
|           5 |
+-------------+
1 row in set (0.00 sec)

mysql>

Here we selects the minimum value of the "price" column in the "Stores" table and assigns it an alias "CheapestOne". The MIN function is used to calculate the minimum value of the "price" column.

MAX and WHERE combination:

mysql> SELECT MAX(price) AS ExpensivePSU FROM Stores WHERE product='Gold PSU';
+--------------+
| ExpensivePSU |
+--------------+
|          100 |
+--------------+
1 row in set (0.00 sec)

mysql>

The result is a single row with a single column "ExpensivePSU" which contains the highest value found in the "price" column of the "Stores" table where the "product" column is "Gold PSU". 

The value returned in this case is 100, which means that the highest value found in the "price" column of the "Stores" table where the "product" column is "Gold PSU" is 100. 

Also, we can search for a substring:

mysql> SELECT MAX(price) AS MaxPriceCables FROM Stores WHERE product LIKE "%Cable%";
+----------------+
| MaxPriceCables |
+----------------+
|             25 |
+----------------+
1 row in set (0.00 sec)

mysql>

This MySQL query selects the maximum value of the "price" column in the "Stores" table where the "product" column contains the word "Cable".