The SQL MIN() and MAX() Functions
The MIN() function returns the smallest value of the selected column.
The MAX() function returns the largest value of the selected column.
MIN() Syntax
SELECT MIN(column_name)
FROM table_name
WHERE condition;
Setting environment for using XAMPP for Windows.
Mounika@MOUNIKA-PC c:\xampp
# ram.cmd
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.4.18-MariaDB mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use classicmodels;
Database changed
MariaDB [classicmodels]> select * from orders limit 2;
+-------------+------------+--------------+-------------+---------+------------------------+----------------+
| orderNumber | orderDate | requiredDate | shippedDate | status | comments | customerNumber |
+-------------+------------+--------------+-------------+---------+------------------------+----------------+
| 10100 | 2003-01-06 | 2003-01-13 | 2003-01-10 | Shipped | NULL | 363 |
| 10101 | 2003-01-09 | 2003-01-18 | 2003-01-11 | Shipped | Check on availability. | 128 |
+-------------+------------+--------------+-------------+---------+------------------------+----------------+
2 rows in set (0.288 sec)
MariaDB [classicmodels]> show tables;
+-------------------------+
| Tables_in_classicmodels |
+-------------------------+
| customers |
| employees |
| offices |
| orderdetails |
| orders |
| payments |
| productlines |
| products |
+-------------------------+
8 rows in set (0.096 sec)
MariaDB [classicmodels]> select * from ordersdetails limit 2;
ERROR 1146 (42S02): Table 'classicmodels.ordersdetails' doesn't exist
MariaDB [classicmodels]> select * from orderdetails limit 2;
+-------------+-------------+-----------------+-----------+-----------------+
| orderNumber | productCode | quantityOrdered | priceEach | orderLineNumber |
+-------------+-------------+-----------------+-----------+-----------------+
| 10100 | S18_1749 | 30 | 136.00 | 3 |
| 10100 | S18_2248 | 50 | 55.09 | 2 |
+-------------+-------------+-----------------+-----------+-----------------+
2 rows in set (0.159 sec)
MariaDB [classicmodels]> select * from payments limit 2;
+----------------+-------------+-------------+----------+
| customerNumber | checkNumber | paymentDate | amount |
+----------------+-------------+-------------+----------+
| 103 | HQ336336 | 2004-10-19 | 6066.78 |
| 103 | JM555205 | 2003-06-05 | 14571.44 |
+----------------+-------------+-------------+----------+
2 rows in set (0.033 sec)
MariaDB [classicmodels]> select max(amount) from payments;
+-------------+
| max(amount) |
+-------------+
| 120166.58 |
+-------------+
1 row in set (0.075 sec)
MariaDB [classicmodels]> select max(amount),customernumber from payments;
+-------------+----------------+
| max(amount) | customernumber |
+-------------+----------------+
| 120166.58 | 103 |
+-------------+----------------+
1 row in set (0.002 sec)
MariaDB [classicmodels]> select * from payments where amount=120166.58;
+----------------+-------------+-------------+-----------+
| customerNumber | checkNumber | paymentDate | amount |
+----------------+-------------+-------------+-----------+
| 141 | JE105477 | 2005-03-18 | 120166.58 |
+----------------+-------------+-------------+-----------+
1 row in set (0.011 sec)
MariaDB [classicmodels]> select max(amount),min(amount) from payments;
+-------------+-------------+
| max(amount) | min(amount) |
+-------------+-------------+
| 120166.58 | 615.45 |
+-------------+-------------+
1 row in set (0.001 sec)
MariaDB [classicmodels]> select amount from payments where customernumber=103;
+----------+
| amount |
+----------+
| 6066.78 |
| 14571.44 |
| 1676.14 |
+----------+
3 rows in set (0.060 sec)
MariaDB [classicmodels]> select max(amount) from payments where customernumber=103;
+-------------+
| max(amount) |
+-------------+
| 14571.44 |
+-------------+
1 row in set (0.002 sec)
MariaDB [classicmodels]> select max(amount) from payments where customernumber=103;
+-------------+
| max(amount) |
+-------------+
| 14571.44 |
+-------------+
1 row in set (0.001 sec)
MariaDB [classicmodels]> select min(amount) from payments where customernumber=103;
+-------------+
| min(amount) |
+-------------+
| 1676.14 |
+-------------+
1 row in set (0.001 sec)
MariaDB [classicmodels]>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.