CREATE TABLE seened (
   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   liik VARCHAR(30),
   korgus FLOAT,
   diameeter FLOAT
);

INSERT INTO seened VALUES (DEFAULT, 'kaseriisikas', 6, 8.4);
INSERT INTO seened VALUES (DEFAULT, 'kaseriisikas', 4, 4.4);
INSERT INTO seened VALUES (DEFAULT, 'männiriisikas', 3, 5);
INSERT INTO seened VALUES (DEFAULT, 'männiriisikas', 2, 2);
INSERT INTO seened VALUES (DEFAULT, 'männiriisikas', 2.5, 2.4);

SELECT * FROM seened;

mysql> SELECT * FROM seened;
+----+----------------+--------+-----------+
| id | liik           | korgus | diameeter |
+----+----------------+--------+-----------+
|  1 | kaseriisikas   |      6 |       8.4 |
|  2 | kaseriisikas   |      4 |       4.4 |
|  3 | männiriisikas  |      3 |         5 |
|  4 | männiriisikas  |      2 |         2 |
|  5 | männiriisikas  |    2.5 |       2.4 |
+----+----------------+--------+-----------+
5 rows in set (0.00 sec)

SELECT * FROM seened ORDER BY korgus;
SELECT * FROM seened ORDER BY korgus DESC;

SELECT * FROM seened WHERE liik='kaseriisikas';
SELECT * FROM seened WHERE liik='kaseriisikas' ORDER BY korgus;
SELECT korgus, diameeter FROM seened WHERE liik='kaseriisikas' ORDER BY korgus;

+--------+-----------+
| korgus | diameeter |
+--------+-----------+
|      4 |       4.4 |
|      6 |       8.4 |
+--------+-----------+

-- Kuvage vaid männiriisikad, mille diameeter on suurem kui 3 cm

SELECT * FROM seened WHERE liik='männiriisikas' AND diameeter>3;

-- Näita neljast sentimeetrist lühemate seente liiki ja diameetrit

SELECT liik, diameeter FROM seened WHERE korgus<4;

+----------------+-----------+
| liik           | diameeter |
+----------------+-----------+
| männiriisikas  |         5 |
| männiriisikas  |         2 |
| männiriisikas  |       2.4 |
+----------------+-----------+

SELECT liik, 3.14*diameeter*diameeter/4 AS pindala FROM seened;

+----------------+--------------------+
| liik           | pindala            |
+----------------+--------------------+
| kaseriisikas   |  55.38959496917736 |
| kaseriisikas   | 15.197600658798224 |
| männiriisikas  |             19.625 |
| männiriisikas  |               3.14 |
| männiriisikas  |   4.52160035934449 |
+----------------+--------------------+

SELECT liik, diameeter, 3.14*diameeter*diameeter/4 AS pindala FROM seened;
+----------------+-----------+--------------------+
| liik           | diameeter | pindala            |
+----------------+-----------+--------------------+
| kaseriisikas   |       8.4 |  55.38959496917736 |
| kaseriisikas   |       4.4 | 15.197600658798224 |
| männiriisikas  |         5 |             19.625 |
| männiriisikas  |         2 |               3.14 |
| männiriisikas  |       2.4 |   4.52160035934449 |
+----------------+-----------+--------------------+

SELECT liik, diameeter, round(3.14*diameeter*diameeter/4, 2) AS pindala FROM seened;
+----------------+-----------+---------+
| liik           | diameeter | pindala |
+----------------+-----------+---------+
| kaseriisikas   |       8.4 |   55.39 |
| kaseriisikas   |       4.4 |   15.20 |
| männiriisikas  |         5 |   19.62 |
| männiriisikas  |         2 |    3.14 |
| männiriisikas  |       2.4 |    4.52 |
+----------------+-----------+---------+

-- Leidke iga seene kohta kõrguse ja diameetri suhe (sihvakus)

SELECT *, korgus/diameeter AS sihvakus FROM seened;

+----+----------------+--------+-----------+--------------------+
| id | liik           | korgus | diameeter | sihvakus           |
+----+----------------+--------+-----------+--------------------+
|  1 | kaseriisikas   |      6 |       8.4 | 0.7142857467236177 |
|  2 | kaseriisikas   |      4 |       4.4 | 0.9090908893868948 |
|  3 | männiriisikas  |      3 |         5 |                0.6 |
|  4 | männiriisikas  |      2 |         2 |                  1 |
|  5 | männiriisikas  |    2.5 |       2.4 |  1.041666625274554 |
+----+----------------+--------+-----------+--------------------+


-- Järjestage seened sihvakuse järgi

SELECT *, round(korgus/diameeter, 2) AS sihvakus FROM seened ORDER BY sihvakus ;

+----+----------------+--------+-----------+----------+
| id | liik           | korgus | diameeter | sihvakus |
+----+----------------+--------+-----------+----------+
|  3 | männiriisikas  |      3 |         5 |     0.60 |
|  1 | kaseriisikas   |      6 |       8.4 |     0.71 |
|  2 | kaseriisikas   |      4 |       4.4 |     0.91 |
|  4 | männiriisikas  |      2 |         2 |     1.00 |
|  5 | männiriisikas  |    2.5 |       2.4 |     1.04 |
+----+----------------+--------+-----------+----------+

SELECT * FROM seened ORDER BY korgus DESC LIMIT 1;

+----+--------------+--------+-----------+
| id | liik         | korgus | diameeter |
+----+--------------+--------+-----------+
|  1 | kaseriisikas |      6 |       8.4 |
+----+--------------+--------+-----------+

mysql> SELECT * FROM seened ORDER BY korgus LIMIT 1;                            
+----+----------------+--------+-----------+
| id | liik           | korgus | diameeter |
+----+----------------+--------+-----------+
|  4 | männiriisikas  |      2 |         2 |
+----+----------------+--------+-----------+

SELECT MAX(korgus) FROM seened;

+-------------+
| MAX(korgus) |
+-------------+
|           6 |
+-------------+

-- Proovi ka MIN, COUNT, AVG