CREATE TABLE jooksjad(
  epost VARCHAR(30) PRIMARY KEY,
  sugu ENUM('M', 'N'),
  synniaasta INT
);


DELETE FROM jooksjad WHERE epost='madis@hot2.ee';

INSERT INTO jooksjad VALUES ('juku@eesti.ee', 'M', 2007);
INSERT INTO jooksjad VALUES ('kati@eesti.ee', 'N', 2007);
INSERT INTO jooksjad VALUES ('mari@eesti.ee', 'N', 2007);
INSERT INTO jooksjad VALUES ('katrin@eesti.ee', 'N', 2008);
INSERT INTO jooksjad VALUES ('mati@eesti.ee', 'M', 2008);
INSERT INTO jooksjad VALUES ('madis@hot.ee', 'M', 2003);
INSERT INTO jooksjad VALUES ('mihkel@hot.ee', 'M', 2002);
INSERT INTO jooksjad VALUES ('margus@hot.ee', 'M', 2003);


MariaDB [if17_jaagup_4]> SELECT * FROM jooksjad;
+-----------------+------+------------+
| epost           | sugu | synniaasta |
+-----------------+------+------------+
| juku@eesti.ee   | M    |       2007 |
| kati@eesti.ee   | N    |       2007 |
| katrin@eesti.ee | N    |       2008 |
| madis@hot.ee    | M    |       2003 |
| margus@hot.ee   | M    |       2003 |
| mari@eesti.ee   | N    |       2007 |
| mati@eesti.ee   | M    |       2008 |
| mihkel@hot.ee   | M    |       2002 |
+-----------------+------+------------+

-- Kõige noorema naisterahva sünniaasta
SELECT MAX(synniaasta) FROM jooksjad WHERE sugu='N'; 


MariaDB [if17_jaagup_4]> SELECT MAX(synniaasta) FROM jooksjad WHERE sugu='N';
+-----------------+
| MAX(synniaasta) |
+-----------------+
|            2008 |
+-----------------+



MariaDB [if17_jaagup_4]> SELECT MIN(synniaasta) FROM jooksjad WHERE sugu='N';   +-----------------+
| MIN(synniaasta) |
+-----------------+
|            2007 |
+-----------------+


MariaDB [if17_jaagup_4]> SET @a=3;
Query OK, 0 rows affected (0.00 sec)

MariaDB [if17_jaagup_4]> SELECT @a;
+------+
| @a   |
+------+
|    3 |
+------+



MariaDB [if17_jaagup_4]>  SELECT @vns:=MIN(synniaasta) FROM jooksjad WHERE sugu='N';
+-----------------------+
| @vns:=MIN(synniaasta) |
+-----------------------+
|                  2007 |
+-----------------------+
1 row in set (0.00 sec)

MariaDB [if17_jaagup_4]> SELECT @vns;
+------+
| @vns |
+------+
| 2007 |
+------+

SET @vns=(SELECT MIN(synniaasta) FROM jooksjad WHERE sugu='N');


MariaDB [if17_jaagup_4]> SET @vns=(SELECT MIN(synniaasta) FROM jooksjad WHERE sugu='N');
Query OK, 0 rows affected (0.02 sec)

MariaDB [if17_jaagup_4]> SELECT @vns;
+------+
| @vns |
+------+
| 2007 |
+------+

SELECT * FROM jooksjad WHERE sugu='N' AND synniaasta=@vns;

+---------------+------+------------+
| epost         | sugu | synniaasta |
+---------------+------+------------+
| kati@eesti.ee | N    |       2007 |
| mari@eesti.ee | N    |       2007 |
+---------------+------+------------+

SET @nr=0;
SELECT @nr:=@nr+1, epost, sugu, synniaasta FROM jooksjad ORDER BY synniaasta;

+------------+-----------------+------+------------+
| @nr:=@nr+1 | epost           | sugu | synniaasta |
+------------+-----------------+------+------------+
|          1 | mihkel@hot.ee   | M    |       2002 |
|          2 | madis@hot.ee    | M    |       2003 |
|          3 | margus@hot.ee   | M    |       2003 |
|          4 | juku@eesti.ee   | M    |       2007 |
|          5 | kati@eesti.ee   | N    |       2007 |
|          6 | mari@eesti.ee   | N    |       2007 |
|          7 | katrin@eesti.ee | N    |       2008 |
|          8 | mati@eesti.ee   | M    |       2008 |
+------------+-----------------+------+------------+

SET @keskmine=(SELECT AVG(synniaasta) FROM jooksjad);
SELECT @keskmine;
SELECT * FROM jooksjad WHERE synniaasta<@keskmine;

+---------------+------+------------+
| epost         | sugu | synniaasta |
+---------------+------+------------+
| madis@hot.ee  | M    |       2003 |
| margus@hot.ee | M    |       2003 |
| mihkel@hot.ee | M    |       2002 |
+---------------+------+------------+

SELECT * FROM jooksjad WHERE synniaasta<
  (SELECT AVG(synniaasta) FROM jooksjad);
  
+---------------+------+------------+
| epost         | sugu | synniaasta |
+---------------+------+------------+
| madis@hot.ee  | M    |       2003 |
| margus@hot.ee | M    |       2003 |
| mihkel@hot.ee | M    |       2002 |
+---------------+------+------------+

SET @kogus=(SELECT COUNT(*) FROM jooksjad);
SELECT @kogus;
SET @poolkogus=ROUND(@kogus/2);
SELECT @poolkogus;

SET @nr=0;
SELECT * FROM 
(SELECT @nr:=@nr+1 AS loendur, epost, sugu, synniaasta 
  FROM jooksjad ORDER BY synniaasta) AS tabel1
    WHERE loendur=@poolkogus;

+---------+---------------+------+------------+
| loendur | epost         | sugu | synniaasta |
+---------+---------------+------+------------+
|       4 | juku@eesti.ee | M    |       2007 |
+---------+---------------+------+------------+

SELECT * FROM jooksjad WHERE synniaasta IN (2006, 2007);

SELECT synniaasta FROM jooksjad WHERE sugu='N';
+------------+
| synniaasta |
+------------+
|       2007 |
|       2008 |
|       2007 |
+------------+

SELECT synniaasta FROM jooksjad WHERE sugu='M';

-- Leia isikud aastatel, mil on sündinud ka naisi

SELECT * FROM jooksjad WHERE synniaasta IN 
  (SELECT synniaasta FROM jooksjad WHERE sugu='N');
  
+-----------------+------+------------+
| epost           | sugu | synniaasta |
+-----------------+------+------------+
| juku@eesti.ee   | M    |       2007 |
| kati@eesti.ee   | N    |       2007 |
| katrin@eesti.ee | N    |       2008 |
| mari@eesti.ee   | N    |       2007 |
| mati@eesti.ee   | M    |       2008 |
+-----------------+------+------------+

-- Leia isikud aastatel, mil pole sündinud naisi


SELECT * FROM jooksjad WHERE synniaasta NOT IN 
  (SELECT synniaasta FROM jooksjad WHERE sugu='N');

+---------------+------+------------+
| epost         | sugu | synniaasta |
+---------------+------+------------+
| madis@hot.ee  | M    |       2003 |
| margus@hot.ee | M    |       2003 |
| mihkel@hot.ee | M    |       2002 |
+---------------+------+------------+


SELECT sugu, AVG(synniaasta) FROM jooksjad GROUP BY sugu;

+------+-----------------+
| sugu | AVG(synniaasta) |
+------+-----------------+
| M    | 2004.6000       |
| N    | 2007.3333       |
+------+-----------------+

-- Meeste keskmisest vanemad mehed ning naiste keskmisest vanemad naised

SELECT * FROM jooksjad WHERE synniaasta<
 (SELECT AVG(synniaasta) FROM jooksjad AS koopia
   WHERE jooksjad.sugu=koopia.sugu);
   

SELECT synniaasta, COUNT(*) FROM jooksjad GROUP BY synniaasta;

SELECT * FROM
 (SELECT synniaasta, COUNT(*) AS jooksjaidsynniaastas FROM jooksjad GROUP BY synniaasta) AS abitabel;


+------------+----------------------+
| synniaasta | jooksjaidsynniaastas |
+------------+----------------------+
|       2002 |                    1 |
|       2003 |                    2 |
|       2007 |                    3 |
|       2008 |                    2 |
+------------+----------------------+

SELECT MAX(jooksjaidsynniaastas) FROM
 (SELECT synniaasta, COUNT(*) AS jooksjaidsynniaastas FROM jooksjad GROUP BY synniaasta) AS abitabel;

+---------------------------+
| MAX(jooksjaidsynniaastas) |
+---------------------------+
|                         3 |
+---------------------------+

SELECT jooksjaidsynniaastas, COUNT(*) AS mituvastavataastat FROM
 (SELECT synniaasta, COUNT(*) AS jooksjaidsynniaastas FROM jooksjad GROUP BY synniaasta) AS abitabel
 GROUP BY jooksjaidsynniaastas;

+----------------------+--------------------+
| jooksjaidsynniaastas | mituvastavataastat |
+----------------------+--------------------+
|                    1 |                  1 |
|                    2 |                  2 |
|                    3 |                  1 |
+----------------------+--------------------+


SELECT synniaasta, jooksjaidsynniaastas, (SELECT COUNT(*) FROM jooksjad) AS jooksjaidkokku FROM
 (SELECT synniaasta, COUNT(*) AS jooksjaidsynniaastas FROM jooksjad GROUP BY synniaasta) AS abitabel;

+------------+----------------------+----------------+
| synniaasta | jooksjaidsynniaastas | jooksjaidkokku |
+------------+----------------------+----------------+
|       2002 |                    1 |              8 |
|       2003 |                    2 |              8 |
|       2007 |                    3 |              8 |
|       2008 |                    2 |              8 |
+------------+----------------------+----------------+

 
-- Muutke päringut nõnda, et iga aasta kohta näidataks, mitu protsenti jooksjatest
-- on sündinud vastaval aastal

SELECT synniaasta, jooksjaidsynniaastas / (SELECT COUNT(*) FROM jooksjad) AS suhtarv FROM
 (SELECT synniaasta, COUNT(*) AS jooksjaidsynniaastas FROM jooksjad GROUP BY synniaasta) AS abitabel;
 

+------------+---------+
| synniaasta | suhtarv |
+------------+---------+
|       2002 | 0.1250  |
|       2003 | 0.2500  |
|       2007 | 0.3750  |
|       2008 | 0.2500  |
+------------+---------+

SELECT synniaasta, 
       ROUND(100* jooksjaidsynniaastas / (SELECT COUNT(*) FROM jooksjad)) AS protsent 
   FROM
 (SELECT synniaasta, COUNT(*) AS jooksjaidsynniaastas FROM jooksjad GROUP BY synniaasta) AS abitabel;
+------------+----------+
| synniaasta | protsent |
+------------+----------+
|       2002 | 13       |
|       2003 | 25       |
|       2007 | 38       |
|       2008 | 25       |
+------------+----------+


-- Kuvage, mitu protsenti on kummastki soost jooksjaid

alustuseks eraldi päringuga
edasi juba grupeerimise ja alampäringuga

SELECT COUNT(*) FROM jooksjad WHERE sugu='M';

+----------+
| COUNT(*) |
+----------+
|        5 |
+----------+

SELECT (SELECT COUNT(*) FROM jooksjad WHERE sugu='M') / (SELECT COUNT(*) FROM jooksjad)
  AS meeste_osakaal;

+----------------+
| meeste_osakaal |
+----------------+
| 0.6250         |
+----------------+
 
SELECT sugu, COUNT(*) FROM jooksjad GROUP BY sugu;

+------+----------+
| sugu | COUNT(*) |
+------+----------+
| M    |        5 |
| N    |        3 |
+------+----------+

SELECT sugu, COUNT(*)/(SELECT COUNT(*) FROM jooksjad)  FROM jooksjad GROUP BY sugu;

+------+------------------------------------------+
| sugu | COUNT(*)/(SELECT COUNT(*) FROM jooksjad) |
+------+------------------------------------------+
| M    | 0.6250                                   |
| N    | 0.3750                                   |
+------+------------------------------------------+

SELECT sugu, ROUND(100*kogus/(SELECT COUNT(*) FROM jooksjad)) FROM
(SELECT sugu, COUNT(*) AS kogus FROM jooksjad GROUP BY sugu) AS abitabel;

;
+------+--------------------------------------------------+
| sugu | ROUND(100*kogus/(SELECT COUNT(*) FROM jooksjad)) |
+------+--------------------------------------------------+
| M    | 63                                               |
| N    | 38                                               |
+------+--------------------------------------------------+