DROP TABLE jooksjad;

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


INSERT INTO jooksjad VALUES ('kati@eesti.ee', 'N', 2007);
INSERT INTO jooksjad VALUES ('mati@eesti.ee', 'M', 2008);
INSERT INTO jooksjad VALUES ('madis@hot.ee', 'M', 2003);
INSERT INTO jooksjad VALUES ('mari@hot.ee', 'N', 2008);
INSERT INTO jooksjad VALUES ('martin@hot.ee', 'M', 1998);
INSERT INTO jooksjad VALUES ('anu@hot.ee', 'N', 2006);
INSERT INTO jooksjad VALUES ('ain@hot.ee', 'M', 2003);
INSERT INTO jooksjad VALUES ('sille@hot.ee', 'N', 2007);
INSERT INTO jooksjad VALUES ('mart@hot.ee', 'M', 2007);





MariaDB [if17_jaagup_1]> Select * FROM jooksjad;
+---------------+------+------------+
| epost         | sugu | synniaasta |
+---------------+------+------------+
| ain@hot.ee    | M    |       2003 |
| anu@hot.ee    | N    |       2006 |
| kati@eesti.ee | N    |       2007 |
| madis@hot.ee  | M    |       2003 |
| mari@hot.ee   | N    |       2008 |
| mart@hot.ee   | M    |       2007 |
| martin@hot.ee | M    |       1998 |
| mati@eesti.ee | M    |       2008 |
| sille@hot.ee  | N    |       2007 |
+---------------+------+------------+

SELECT synniaasta FROM jooksjad ORDER BY synniaasta;

SELECT synniaasta FROM jooksjad ORDER BY synniaasta LIMIT 1;

SELECT MIN(synniaasta) FROM jooksjad;

+-----------------+
| MIN(synniaasta) |
+-----------------+
|            1998 |
+-----------------+

SELECT * FROM jooksjad WHERE synniaasta=1998;


SELECT * FROM jooksjad WHERE synniaasta=(SELECT MIN(synniaasta) FROM jooksjad);

-- lisa üks vanem jooksja ja veendu, et päring näitab teda
-- Näita loetelu noorimatest jooksjatest
SELECT * FROM jooksjad WHERE synniaasta=(SELECT MAX(synniaasta) FROM jooksjad);
+---------------+------+------------+
| epost         | sugu | synniaasta |
+---------------+------+------------+
| mari@hot.ee   | N    |       2008 |
| mati@eesti.ee | M    |       2008 |
+---------------+------+------------+

SELECT AVG(synniaasta) FROM jooksjad;

-- Leidke keskmisest nooremad jooksjad
SELECT * FROM jooksjad WHERE synniaasta>(SELECT AVG(synniaasta) FROM jooksjad);
-- Leidke keskmisest vanemad jooksjad
SELECT * FROM jooksjad WHERE synniaasta<(SELECT AVG(synniaasta) FROM jooksjad);

+---------------+------+------------+
| epost         | sugu | synniaasta |
+---------------+------+------------+
| ain@hot.ee    | M    |       2003 |
| madis@hot.ee  | M    |       2003 |
| martin@hot.ee | M    |       1998 |
+---------------+------+------------+

-- Mitu jooksjat on kokku (COUNT(*))
SELECT COUNT(*) FROM jooksjad;

-- Mitu naisjooksjat on kokku
SELECT COUNT(*) FROM jooksjad WHERE sugu='N';

-- Mitu naisjooksjat on kõikide jooksjate keskmisest vanemad
SELECT COUNT(*) FROM jooksjad WHERE sugu='N' AND 
  synniaasta<(SELECT AVG(synniaasta) FROM jooksjad)
-- Mitu naisjooksjat on kõikide jooksjate keskmisest nooremad

SELECT COUNT(*) FROM jooksjad WHERE sugu='N' AND 
  synniaasta>(SELECT AVG(synniaasta) FROM jooksjad)

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

+-----------------+-----------------+------+
| AVG(synniaasta) | STD(synniaasta) | sugu |
+-----------------+-----------------+------+
| 2003.8000       |          3.5440 | M    |
| 2007.0000       |          0.7071 | N    |
+-----------------+-----------------+------+

-- Kuva jooksjad, kelle sünniaasta on suurem kui
-- temaga samast soost jooksjate keskmine sünniaasta
-- rekursiivne alampäring, alampäring käivitatakse iga kuvatava rea puhul uuesti

SELECT * FROM jooksjad WHERE synniaasta>
   (SELECT AVG(synniaasta) FROM jooksjad AS koopia
     WHERE jooksjad.sugu=koopia.sugu);
	 
+---------------+------+------------+
| epost         | sugu | synniaasta |
+---------------+------+------------+
| mari@hot.ee   | N    |       2008 |
| mart@hot.ee   | M    |       2007 |
| mati@eesti.ee | M    |       2008 |
+---------------+------+------------+
	 
-- Kuva mehed sünniaastatel kus on ka naisi

-- Naiste sünniaastad

SELECT synniaasta FROM jooksjad WHERE sugu='N';

+------------+
| synniaasta |
+------------+
|       2006 |
|       2007 |
|       2008 |
|       2007 |
+------------+

SELECT DISTINCT synniaasta FROM jooksjad WHERE sugu='N';

+------------+
| synniaasta |
+------------+
|       2006 |
|       2007 |
|       2008 |
+------------+

SELECT * FROM jooksjad WHERE sugu='M' AND synniaasta IN (2006, 2007, 2008);

+---------------+------+------------+
| epost         | sugu | synniaasta |
+---------------+------+------------+
| mart@hot.ee   | M    |       2007 |
| mati@eesti.ee | M    |       2008 |
+---------------+------+------------+

SELECT * FROM jooksjad WHERE sugu='M' AND synniaasta IN 
   (SELECT DISTINCT synniaasta FROM jooksjad WHERE sugu='N');

+---------------+------+------------+
| epost         | sugu | synniaasta |
+---------------+------+------------+
| mart@hot.ee   | M    |       2007 |
| mati@eesti.ee | M    |       2008 |
+---------------+------+------------+


-- Mehed naisteta aastatel

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

+---------------+------+------------+
| epost         | sugu | synniaasta |
+---------------+------+------------+
| ain@hot.ee    | M    |       2003 |
| madis@hot.ee  | M    |       2003 |
| martin@hot.ee | M    |       1998 |
+---------------+------+------------+

-- Aastad, kus on nii naisi kui mehi
-- ei tööta MySQLis
SELECT synniaasta FROM jooksjad WHERE sugu='N'
  INTERSECT
SELECT synniaasta FROM jooksjad WHERE sugu='M';

-- töötab MySQLis

SELECT synniaasta FROM jooksjad WHERE sugu='N' AND synniaasta IN
 (SELECT synniaasta FROM jooksjad WHERE sugu='M');

+------------+
| synniaasta |
+------------+
|       2007 |
|       2008 |
|       2007 |
+------------+


SELECT COUNT(*) AS sookogus, sugu, (SELECT COUNT(*) FROM jooksjad) AS kokku 
  FROM jooksjad GROUP BY sugu;

+----------+------+-------+
| sookogus | sugu | kokku |
+----------+------+-------+
|        5 | M    |     9 |
|        4 | N    |     9 |
+----------+------+-------+

SELECT * FROM
 (SELECT COUNT(*) AS sookogus, sugu, (SELECT COUNT(*) FROM jooksjad) AS kokku 
  FROM jooksjad GROUP BY sugu) AS tabel1;
+----------+------+-------+
| sookogus | sugu | kokku |
+----------+------+-------+
|        5 | M    |     9 |
|        4 | N    |     9 |
+----------+------+-------+

SELECT sugu, sookogus/kokku AS suhe FROM
 (SELECT COUNT(*) AS sookogus, sugu, (SELECT COUNT(*) FROM jooksjad) AS kokku 
  FROM jooksjad GROUP BY sugu) AS tabel1;
+------+--------+
| sugu | suhe   |
+------+--------+
| M    | 0.5556 |
| N    | 0.4444 |
+------+--------+

SELECT sugu, ROUND(suhe*100) AS protsent FROM 
(SELECT sugu, sookogus/kokku AS suhe FROM
 (SELECT COUNT(*) AS sookogus, sugu, (SELECT COUNT(*) FROM jooksjad) AS kokku 
  FROM jooksjad GROUP BY sugu) AS tabel1) AS tabel2;

+------+----------+
| sugu | protsent |
+------+----------+
| M    | 56       |
| N    | 44       |
+------+----------+

-- sama trepituna

SELECT sugu, ROUND(suhe*100) AS protsent 
  FROM 
    (SELECT sugu, sookogus/kokku AS suhe 
      FROM
        (SELECT COUNT(*) AS sookogus, sugu, 
           (SELECT COUNT(*) FROM jooksjad) AS kokku 
             FROM jooksjad 
         GROUP BY sugu) 
     AS tabel1) 
  AS tabel2;
  

+------+----------+
| sugu | protsent |
+------+----------+
| M    | 56       |
| N    | 44       |
+------+----------+