CREATE TABLE omanikud(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  eesnimi VARCHAR(30),
  maakond VARCHAR(30)
);

INSERT INTO omanikud VALUES(1, 'Juku', 'Harju');
INSERT INTO omanikud VALUES(2, 'Kati', 'Harju');
INSERT INTO omanikud VALUES(3, 'Mati', 'Rapla');
INSERT INTO omanikud VALUES(4, 'Madis', 'Rapla');



CREATE TABLE lemmikloomad(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  liik VARCHAR(50),
  synniaasta INT, 
  omaniku_id INT,
  FOREIGN KEY(omaniku_id) REFERENCES omanikud(id)
);

INSERT INTO lemmikloomad VALUES(NULL, 'kass', 2009, 2);
INSERT INTO lemmikloomad VALUES(NULL, 'kass', 2010, 2);
INSERT INTO lemmikloomad VALUES(NULL, 'koer', 2009, 1);
INSERT INTO lemmikloomad VALUES(NULL, 'koer', 2013, 3);


mysql> SELECT * FROM omanikud;
+----+---------+---------+
| id | eesnimi | maakond |
+----+---------+---------+
|  1 | Juku    | Harju   |
|  2 | Kati    | Harju   |
|  3 | Mati    | Rapla   |
|  4 | Madis   | Rapla   |
+----+---------+---------+



mysql> SELECT * FROM lemmikloomad;
+----+------+------------+------------+
| id | liik | synniaasta | omaniku_id |
+----+------+------------+------------+
|  1 | kass |       2009 |          2 |
|  2 | kass |       2010 |          2 |
|  3 | koer |       2009 |          1 |
|  4 | koer |       2013 |          3 |
+----+------+------------+------------+

SELECT * FROM lemmikloomad
  JOIN omanikud ON lemmikloomad.omaniku_id=omanikud.id;

mysql> SELECT * FROM lemmikloomad
    ->   JOIN omanikud ON lemmikloomad.omaniku_id=omanikud.id;
+----+------+------------+------------+----+---------+---------+
| id | liik | synniaasta | omaniku_id | id | eesnimi | maakond |
+----+------+------------+------------+----+---------+---------+
|  3 | koer |       2009 |          1 |  1 | Juku    | Harju   |
|  1 | kass |       2009 |          2 |  2 | Kati    | Harju   |
|  2 | kass |       2010 |          2 |  2 | Kati    | Harju   |
|  4 | koer |       2013 |          3 |  3 | Mati    | Rapla   |
+----+------+------------+------------+----+---------+---------+
4 rows in set (0.00 sec)

SELECT lemmikloomad.id, liik, eesnimi, maakond FROM lemmikloomad 
  JOIN omanikud ON lemmikloomad.omaniku_id=omanikud.id;


+----+------+---------+---------+
| id | liik | eesnimi | maakond |
+----+------+---------+---------+
|  3 | koer | Juku    | Harju   |
|  1 | kass | Kati    | Harju   |
|  2 | kass | Kati    | Harju   |
|  4 | koer | Mati    | Rapla   |
+----+------+---------+---------+

--Millises maakonnas mitu omanikku:

SELECT maakond, COUNT(*), GROUP_CONCAT(eesnimi) FROM omanikud GROUP BY maakond;
+---------+----------+-----------------------+
| maakond | COUNT(*) | GROUP_CONCAT(eesnimi) |
+---------+----------+-----------------------+
| Harju   |        2 | Juku,Kati             |
| Rapla   |        2 | Mati,Madis            |
+---------+----------+-----------------------+


-- Millises maakonnas on mitu lemmiklooma?

SELECT maakond, COUNT(*) FROM omanikud
  JOIN lemmikloomad ON lemmikloomad.omaniku_id=omanikud.id
  GROUP BY maakond;  

+---------+----------+
| maakond | COUNT(*) |
+---------+----------+
| Harju   |        3 |
| Rapla   |        1 |
+---------+----------+
2 rows in set (0.00 sec)

-- Näita iga maakonna kohta, mitu kassi seal on


SELECT maakond, COUNT(*) FROM omanikud
  JOIN lemmikloomad ON lemmikloomad.omaniku_id=omanikud.id
  WHERE liik='kass'
  GROUP BY maakond;  

+---------+----------+
| maakond | COUNT(*) |
+---------+----------+
| Harju   |        2 |
+---------+----------+

SELECT maakond, liik, COUNT(*) FROM omanikud
  JOIN lemmikloomad ON lemmikloomad.omaniku_id=omanikud.id
  GROUP BY maakond, liik; 
+---------+------+----------+
| maakond | liik | COUNT(*) |
+---------+------+----------+
| Harju   | kass |        2 |
| Harju   | koer |        1 |
| Rapla   | koer |        1 |
+---------+------+----------+

-- Leidke loomaliigi juures, mitu protsenti moodustab see liik lemmikloomade koguarvust

SELECT liik, COUNT(*), 100*COUNT(*)/(SELECT COUNT(*) FROM lemmikloomad) AS protsent
  FROM lemmikloomad GROUP BY liik;
 
SELECT liik, 100*liigistloomi/loomikokku AS protsent FROM
(SELECT liik, COUNT(*) as liigistloomi, (SELECT COUNT(*) FROM lemmikloomad) AS loomikokku
  FROM lemmikloomad GROUP BY liik) AS tabel1;

+------+----------+
| liik | protsent |
+------+----------+
| kass |  50.0000 |
| koer |  50.0000 |
+------+----------+


 
-- Leidke iga maakonna ja loomaliigi juures, millise osakaalu moodustab see liik maakonna lemmikloomadest.

SELECT maakond, liik, COUNT(*), 
     (SELECT COUNT(*) FROM omanikud AS omanikud2 JOIN lemmikloomad as lemmikloomad2
	   ON lemmikloomad2.omaniku_id=omanikud2.id WHERE omanikud.maakond = omanikud2.maakond) 
	     AS lemmikloomimaakonnaskokku
   FROM omanikud
  JOIN lemmikloomad ON lemmikloomad.omaniku_id=omanikud.id
  GROUP BY maakond, liik; 
  
 
+---------+------+----------+---------------------------+
| maakond | liik | COUNT(*) | lemmikloomimaakonnaskokku |
+---------+------+----------+---------------------------+
| Harju   | kass |        2 |                         3 |
| Harju   | koer |        1 |                         3 |
| Rapla   | koer |        1 |                         1 |
+---------+------+----------+---------------------------+


SELECT maakond, liik, COUNT(*)/ 
     (SELECT COUNT(*) FROM omanikud AS omanikud2 JOIN lemmikloomad as lemmikloomad2
	   ON lemmikloomad2.omaniku_id=omanikud2.id WHERE omanikud.maakond = omanikud2.maakond) 
	     AS suhe
   FROM omanikud
  JOIN lemmikloomad ON lemmikloomad.omaniku_id=omanikud.id
  GROUP BY maakond, liik; 

+---------+------+--------+
| maakond | liik | suhe   |
+---------+------+--------+
| Harju   | kass | 0.6667 |
| Harju   | koer | 0.3333 |
| Rapla   | koer | 1.0000 |
+---------+------+--------+

SELECT maakond, liik, ROUND(100*kogus/lemmikloomimaakonnaskokku) AS protsent FROM
(SELECT maakond, liik, COUNT(*) as kogus, 
     (SELECT COUNT(*) FROM omanikud AS omanikud2 JOIN lemmikloomad as lemmikloomad2
	   ON lemmikloomad2.omaniku_id=omanikud2.id WHERE omanikud.maakond = omanikud2.maakond) 
	     AS lemmikloomimaakonnaskokku
   FROM omanikud
  JOIN lemmikloomad ON lemmikloomad.omaniku_id=omanikud.id
  GROUP BY maakond, liik) AS tabel1;
  
+---------+------+----------+
| maakond | liik | protsent |
+---------+------+----------+
| Harju   | kass |       67 |
| Harju   | koer |       33 |
| Rapla   | koer |      100 |
+---------+------+----------+


SELECT * FROM omanikud
  LEFT JOIN lemmikloomad ON lemmikloomad.omaniku_id=omanikud.id;
+----+---------+---------+------+------+------------+------------+
| id | eesnimi | maakond | id   | liik | synniaasta | omaniku_id |
+----+---------+---------+------+------+------------+------------+
|  1 | Juku    | Harju   |    3 | koer |       2009 |          1 |
|  2 | Kati    | Harju   |    1 | kass |       2009 |          2 |
|  2 | Kati    | Harju   |    2 | kass |       2010 |          2 |
|  3 | Mati    | Rapla   |    4 | koer |       2013 |          3 |
|  4 | Madis   | Rapla   | NULL | NULL |       NULL |       NULL |
+----+---------+---------+------+------+------------+------------+

SELECT omanikud.id, eesnimi, COUNT(lemmikloomad.id) AS loomadearv FROM omanikud
  LEFT JOIN lemmikloomad ON lemmikloomad.omaniku_id=omanikud.id
  GROUP BY omanikud.id;
+----+---------+------------+
| id | eesnimi | loomadearv |
+----+---------+------------+
|  1 | Juku    |          1 |
|  2 | Kati    |          2 |
|  3 | Mati    |          1 |
|  4 | Madis   |          0 |
+----+---------+------------+
  
-- Lisan kodutu koera

INSERT INTO lemmikloomad VALUES (NULL, 'koer', 2013, NULL);

SELECT * FROM omanikud
  RIGHT JOIN lemmikloomad ON lemmikloomad.omaniku_id=omanikud.id;
  
+------+---------+---------+----+------+------------+------------+
| id   | eesnimi | maakond | id | liik | synniaasta | omaniku_id |
+------+---------+---------+----+------+------------+------------+
|    2 | Kati    | Harju   |  1 | kass |       2009 |          2 |
|    2 | Kati    | Harju   |  2 | kass |       2010 |          2 |
|    1 | Juku    | Harju   |  3 | koer |       2009 |          1 |
|    3 | Mati    | Rapla   |  4 | koer |       2013 |          3 |
| NULL | NULL    | NULL    |  5 | koer |       2013 |       NULL |
+------+---------+---------+----+------+------------+------------+