Perl Data Analytics Tutorial

Let's find the maximum, minimum, and average values ​​with SQL

Let's find the maximum, minimum, and average values ​​in SQL. I create a table, insert data, and find the maximum, minimum, and average values ​​in SQL.

Create database

If you don't have a database, create a database with the create database statement.

MariaDB [none]> create database kimotosystem;

MariaDB [kimotosystem]> use kimotosystem;

MariaDB [kimotosystem]> show tables;
+-----------------+
| Tables_in_kimotosystem |
+-----------------+
| book            |
+-----------------+
1 row in set (0.01 sec)

Create table

Create a table using the create table statement. The table name is book. ID, title and price data.

MariaDB [kimotosystem]> 
MariaDB [kimotosystem]> 
MariaDB [kimotosystem]> create table kimotosystem.book(id int AUTO_INCREMENT PRIMARY KEY,name varchar(10), price int);
Query OK, 0 rows affected (0.01 sec)

MariaDB [itodb]> desc book;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
| price | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Insert data

Insert book data using the SQL insert statement.

MariaDB [kimotosystem]> insert into kimotosystem.book (name,price) values ('book A',800),('book B', 1000),('book C', 1500),('book D', 2500),('book E',2800),('book F',3500),('book G',4000);
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

MariaDB [kimotosystem]> select * from kimotosystem.book;
+----+--------+-------+
| id | name   | price |
+----+--------+-------+
|  1 | book A |   800 |
|  2 | book B |  1000 |
|  3 | book C |  1500 |
|  4 | book D |  2500 |
|  5 | book E |  2800 |
|  6 | book F |  3500 |
|  7 | book G |  4000 |
+----+--------+-------+
7 rows in set (0.01 sec)

Find the maximum, minimum, and average values with SQL

Use the select statement and set operation to find the maximum, minimum, and average values.

MariaDB [kimotosystem]> select max(price), min(price),avg(price) from kimotosystem.book;
+------------+------------+------------+
| max(price) | min(price) | avg(price) |
+------------+------------+------------+
|       4000 |        800 |  2300.0000 |
+------------+------------+------------+
1 row in set (0.00 sec)