DROP TABLE orienteerujad;
CREATE TABLE orienteerujad(
  email VARCHAR(30) PRIMARY KEY,
  sugu ENUM('M', 'N'),
  synniaasta INT
);

SHOW TABLES;


INSERT INTO orienteerujad VALUES ('juku@eesti.ee', 'M', 2009);
INSERT INTO orienteerujad VALUES ('katrin@eesti.ee', 'N', 2008);
INSERT INTO orienteerujad VALUES ('juhan@eesti.ee', 'M', 2009);
INSERT INTO orienteerujad VALUES ('kati@eesti.ee', 'N', 2009);
INSERT INTO orienteerujad VALUES ('juulius@eesti.ee', 'M', 2009);
INSERT INTO orienteerujad VALUES ('katariina@eesti.ee', 'N', 2009);
INSERT INTO orienteerujad VALUES ('mati@eesti.ee', 'M', 2010);


MariaDB [if17_jaagup_3]> SELECT * FROM orienteerujad;
+--------------------+------+------------+
| email              | sugu | synniaasta |
+--------------------+------+------------+
| juhan@eesti.ee     | M    |       2009 |
| juku@eesti.ee      | M    |       2009 |
| juulius@eesti.ee   | M    |       2009 |
| katariina@eesti.ee | N    |       2009 |
| kati@eesti.ee      | N    |       2009 |
| katrin@eesti.ee    | N    |       2008 |
| mati@eesti.ee      | M    |       2010 |
+--------------------+------+------------+


SELECT COUNT(*) FROM orienteerujad;

+----------+
| COUNT(*) |
+----------+
|        7 |
+----------+

SELECT MAX(synniaasta) FROM orienteerujad;

+-----------------+
| MAX(synniaasta) |
+-----------------+
|            2010 |
+-----------------+

SELECT * FROM orienteerujad WHERE synniaasta=2010;

+---------------+------+------------+
| email         | sugu | synniaasta |
+---------------+------+------------+
| mati@eesti.ee | M    |       2010 |
+---------------+------+------------+

-- leidke vanimad orienteerujad
SELECT MIN(synniaasta) FROM orienteerujad;
SELECT * FROM orienteerujad 
   WHERE synniaasta=(SELECT MIN(synniaasta) FROM orienteerujad);
   
-- leidke keskmisest vanemad orienteerujad

SELECT * FROM orienteerujad 
   WHERE synniaasta<(SELECT AVG(synniaasta) FROM orienteerujad);

SELECT * FROM orienteerujad ORDER BY synniaasta LIMIT 5;

SELECT COUNT(*), sugu FROM orienteerujad GROUP BY sugu;

-- kuvage vanima mehe ja vanima naise sünniaasta


SELECT COUNT(*), MIN(synniaasta), MAX(synniaasta), sugu 
  FROM orienteerujad 
  GROUP BY sugu;

+----------+-----------------+-----------------+------+
| COUNT(*) | MIN(synniaasta) | MAX(synniaasta) | sugu |
+----------+-----------------+-----------------+------+
|        4 |            2009 |            2010 | M    |
|        3 |            2008 |            2009 | N    |
+----------+-----------------+-----------------+------+

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

-- naiste keskmisest vanemad naised ning meeste keskmisest vanemad mehed
-- kuva orienteerujad, kelle sünniaasta on väiksem kui 
   vastavast soost isikute keskmine sünniaasta

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

-- Kuvage iga erineva sünniaasta kohta, mitu isikut seal on
SELECT COUNT(*), synniaasta FROM orienteerujad GROUP BY synniaasta;
-- Kuvage iga erineva sünniaasta kohta sealsete isikute elektronpostid
SELECT GROUP_CONCAT(email), synniaasta FROM orienteerujad GROUP BY synniaasta;
  * Päring loetelu asemel
* Leia sünniaastad, mil on sündinud naisi
* Leia mehed sünniaastatel, mil on sündinud naisi

* Leia mehed sünniaastatel, mil pole sündinud naisi

SELECT synniaasta FROM orienteerujad WHERE sugu="N";
SELECT DISTINCT synniaasta FROM orienteerujad WHERE sugu="N";
+------------+
| synniaasta |
+------------+
|       2009 |
|       2008 |
+------------+
SELECT * FROM orienteerujad 
  WHERE sugu="M" AND synniaasta IN (2008, 2009);

+------------------+------+------------+
| email            | sugu | synniaasta |
+------------------+------+------------+
| juhan@eesti.ee   | M    |       2009 |
| juku@eesti.ee    | M    |       2009 |
| juulius@eesti.ee | M    |       2009 |
+------------------+------+------------+

SELECT * FROM orienteerujad 
  WHERE sugu="M" AND synniaasta IN 
    (SELECT DISTINCT synniaasta FROM orienteerujad WHERE sugu="N");

+------------------+------+------------+
| email            | sugu | synniaasta |
+------------------+------+------------+
| juhan@eesti.ee   | M    |       2009 |
| juku@eesti.ee    | M    |       2009 |
| juulius@eesti.ee | M    |       2009 |
+------------------+------+------------+

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

+---------------+------+------------+
| email         | sugu | synniaasta |
+---------------+------+------------+
| mati@eesti.ee | M    |       2010 |
+---------------+------+------------+
	

SELECT * FROM orienteerujad ORDER BY synniaasta;

SELECT email, YEAR(NOW())-synniaasta AS vanus FROM
(SELECT * FROM orienteerujad ORDER BY synniaasta) AS tabel1;


Näita, millisest soost isikuid on rohkem
SELECT COUNT(*), sugu FROM orienteerujad GROUP BY sugu;

SELECT COUNT(*), sugu FROM orienteerujad GROUP BY sugu ORDER BY COUNT(*);

SELECT sugu FROM orienteerujad 
   GROUP BY sugu 
   ORDER BY COUNT(*) DESC 
   LIMIT 1;


Näita sellest soost isikud keda on rohkem
SELECT * FROM orienteerujad WHERE sugu=(
 SELECT sugu FROM orienteerujad 
   GROUP BY sugu 
   ORDER BY COUNT(*) DESC 
   LIMIT 1
 );

Näita, mitu protsenti isikuid on kummastki soost

SELECT COUNT(*), sugu FROM orienteerujad GROUP BY sugu
UNION 
SELECT COUNT(*), 'kokku' FROM orienteerujad;


+----------+-------+
| COUNT(*) | sugu  |
+----------+-------+
|        4 | M     |
|        3 | N     |
|        7 | kokku |
+----------+-------+

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

+----------+------+-------+
| sookogus | sugu | kokku |
+----------+------+-------+
|        4 | M    |     7 |
|        3 | N    |     7 |
+----------+------+-------+

SELECT sugu, sookogus/kokku AS suhe FROM
(SELECT COUNT(*) AS sookogus, sugu, 
  (SELECT COUNT(*) FROM orienteerujad) AS kokku 
  FROM orienteerujad GROUP BY sugu) AS tabel1;

+------+--------+
| sugu | suhe   |
+------+--------+
| M    | 0.5714 |
| N    | 0.4286 |
+------+--------+

SELECT sugu, ROUND(suhe*100) AS protsent FROM  
(SELECT sugu, sookogus/kokku AS suhe FROM
 (SELECT COUNT(*) AS sookogus, sugu, 
  (SELECT COUNT(*) FROM orienteerujad) AS kokku 
  FROM orienteerujad GROUP BY sugu) AS tabel1)
  AS tabel2;
+------+----------+
| sugu | protsent |
+------+----------+
| M    | 57       |
| N    | 43       |
+------+----------+