CREATE TABLE pirnid(
   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   pirnisort VARCHAR(30),
   r1 FLOAT,
   p2 FLOAT,
   r2 FLOAT,
   p3 FLOAT,
   r3 FLOAT
);

INSERT INTO pirnid VALUES (DEFAULT, 'Eesti pirn', 1, 3, 2, 6, 4);
INSERT INTO pirnid VALUES (DEFAULT, 'Eesti pirn', 0.8, 2, 1.5, 3, 2.7);
INSERT INTO pirnid VALUES (DEFAULT, 'Eesti pirn', 1.8, 2, 1.8, 4, 5);

INSERT INTO pirnid VALUES (DEFAULT, 'Lutsu võipirn', 1, 2, 2, 3, 3);
INSERT INTO pirnid VALUES (DEFAULT, 'Lutsu võipirn', 1, 3, 3, 5, 5);

SELECT * FROM pirnid;
+----+----------------+------+------+------+------+------+
| id | pirnisort      | r1   | p2   | r2   | p3   | r3   |
+----+----------------+------+------+------+------+------+
|  1 | Eesti pirn     |    1 |    3 |    2 |    6 |    4 |
|  2 | Eesti pirn     |  0.8 |    2 |  1.5 |    3 |  2.7 |
|  3 | Eesti pirn     |  1.8 |    2 |  1.8 |    4 |    5 |
|  4 | Lutsu võipirn  |    1 |    2 |    2 |    3 |    3 |
|  5 | Lutsu võipirn  |    1 |    3 |    3 |    5 |    5 |
+----+----------------+------+------+------+------+------+

SELECT * FROM pirnid ORDER BY p3;

+----+----------------+------+------+------+------+------+
| id | pirnisort      | r1   | p2   | r2   | p3   | r3   |
+----+----------------+------+------+------+------+------+
|  2 | Eesti pirn     |  0.8 |    2 |  1.5 |    3 |  2.7 |
|  4 | Lutsu võipirn  |    1 |    2 |    2 |    3 |    3 |
|  3 | Eesti pirn     |  1.8 |    2 |  1.8 |    4 |    5 |
|  5 | Lutsu võipirn  |    1 |    3 |    3 |    5 |    5 |
|  1 | Eesti pirn     |    1 |    3 |    2 |    6 |    4 |
+----+----------------+------+------+------+------+------+

DELETE FROM pirnid WHERE id=4;

+----+----------------+------+------+------+------+------+
| id | pirnisort      | r1   | p2   | r2   | p3   | r3   |
+----+----------------+------+------+------+------+------+
|  1 | Eesti pirn     |    1 |    3 |    2 |    6 |    4 |
|  2 | Eesti pirn     |  0.8 |    2 |  1.5 |    3 |  2.7 |
|  3 | Eesti pirn     |  1.8 |    2 |  1.8 |    4 |    5 |
|  5 | Lutsu võipirn  |    1 |    3 |    3 |    5 |    5 |
+----+----------------+------+------+------+------+------+
DELETE FROM pirnid WHERE id IN (2, 5);


+----+------------+------+------+------+------+------+
| id | pirnisort  | r1   | p2   | r2   | p3   | r3   |
+----+------------+------+------+------+------+------+
|  1 | Eesti pirn |    1 |    3 |    2 |    6 |    4 |
|  3 | Eesti pirn |  1.8 |    2 |  1.8 |    4 |    5 |
+----+------------+------+------+------+------+------+


SELECT pirnisort, p3 FROM pirnid;

+----------------+------+
| pirnisort      | p3   |
+----------------+------+
| Eesti pirn     |    6 |
| Eesti pirn     |    4 |
| Lutsu võipirn  |    3 |
| Lutsu võipirn  |    5 |
+----------------+------+

SELECT pirnisort, 3.14*r3*r3 AS ristloikepindala FROM pirnid;

+----------------+--------------------+
| pirnisort      | ristloikepindala   |
+----------------+--------------------+
| Eesti pirn     |              50.24 |
| Eesti pirn     |               78.5 |
| Lutsu võipirn  | 28.259999999999998 |
| Lutsu võipirn  |               78.5 |
+----------------+--------------------+

SELECT pirnisort, ROUND(3.14*r3*r3, 1) AS ristloikepindala FROM pirnid;

-- Leia pirni kolmanda ning teise mõõtekoha raadiuse vahe

SELECT *, r3-r2 FROM pirnid;

SELECT *, (r3-r2)/(p3-p2)-(r2-r1)/p2 FROM pirnid;

INSERT INTO pirnid VALUES (DEFAULT, 'Lutsu võipirn', 1, 2, 4, 3, 4);

SELECT *, (r2-r1)/p2-(r3-r2)/(p3-p2) AS kumerus FROM pirnid;

SELECT *, ((r2-r1)/p2)/((r3-r2)/(p3-p2)) AS kumerus FROM pirnid;


SELECT *, (r2-r1)/p2-(r3-r2)/(p3-p2) AS kumerus FROM pirnid ORDER BY kumerus;

+----+----------------+------+------+------+------+------+----------------------+
| id | pirnisort      | r1   | p2   | r2   | p3   | r3   | kumerus              |
+----+----------------+------+------+------+------+------+----------------------+
|  3 | Eesti pirn     |  1.8 |    2 |  1.8 |    4 |    5 |   -1.600000023841858 |
|  6 | Lutsu võipirn  |    1 |    2 |    2 |    3 |    3 |                 -0.5 |
|  7 | Lutsu võipirn  |    1 |    3 |    3 |    5 |    5 | -0.33333333333333337 |
|  1 | Eesti pirn     |    1 |    3 |    2 |    6 |    4 |  -0.3333333333333333 |
|  8 | Lutsu võipirn  |    1 |    2 |    4 |    3 |    4 |                  1.5 |
+----+----------------+------+------+------+------+------+----------------------+