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



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);


CREATE TABLE orienteerumistulemused(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  punktid INT,
  voistleja VARCHAR(30),
  FOREIGN KEY(voistleja) REFERENCES orienteerujad(email)
);


INSERT INTO orienteerumistulemused VALUES (DEFAULT, 10, 'katrin@eesti.ee');
INSERT INTO orienteerumistulemused VALUES (DEFAULT, 15, 'katrin@eesti.ee');
INSERT INTO orienteerumistulemused VALUES (DEFAULT, 8, 'katrin@eesti.ee');
INSERT INTO orienteerumistulemused VALUES (DEFAULT, 11, 'kati@eesti.ee');

INSERT INTO orienteerumistulemused VALUES (DEFAULT, 11, 'madis@eesti.ee');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`if17_jaagup_3`.`orienteerumistulemused`, CONSTRAINT `orienteerumistulemused_ibfk_1` FOREIGN KEY (`voistleja`) REFERENCES `orienteerujad` (`email`))


SELECT * FROM orienteerumistulemused;


+----+---------+-----------------+
| id | punktid | voistleja       |
+----+---------+-----------------+
|  1 |      10 | katrin@eesti.ee |
|  2 |      15 | katrin@eesti.ee |
|  3 |       8 | katrin@eesti.ee |
|  4 |      11 | kati@eesti.ee   |
+----+---------+-----------------+

SELECT * FROM orienteerujad
   INNER JOIN orienteerumistulemused ON orienteerumistulemused.voistleja=orienteerujad.email;


+-----------------+------+------------+----+---------+-----------------+
| email           | sugu | synniaasta | id | punktid | voistleja       |
+-----------------+------+------------+----+---------+-----------------+
| katrin@eesti.ee | N    |       2008 |  1 |      10 | katrin@eesti.ee |
| katrin@eesti.ee | N    |       2008 |  2 |      15 | katrin@eesti.ee |
| katrin@eesti.ee | N    |       2008 |  3 |       8 | katrin@eesti.ee |
| kati@eesti.ee   | N    |       2009 |  4 |      11 | kati@eesti.ee   |
+-----------------+------+------------+----+---------+-----------------+
   

SELECT email, punktid FROM orienteerujad
   INNER JOIN orienteerumistulemused ON orienteerumistulemused.voistleja=orienteerujad.email;
   
SELECT email, SUM(punktid) FROM orienteerujad
   INNER JOIN orienteerumistulemused ON orienteerumistulemused.voistleja=orienteerujad.email
   GROUP BY email;
   

+-----------------+--------------+
| email           | SUM(punktid) |
+-----------------+--------------+
| kati@eesti.ee   | 11           |
| katrin@eesti.ee | 33           |
+-----------------+--------------+

leidke punktidega kasutaja keskmised punktid voistluse kohta

SELECT email, AVG(punktid), SUM(punktid)/COUNT(punktid) FROM orienteerujad
   INNER JOIN orienteerumistulemused ON orienteerumistulemused.voistleja=orienteerujad.email
   GROUP BY email;