omanikud
id, eesnimi, synniaasta

soidukid
regnr, mark, valmimisaasta, omaniku_id



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

INSERT INTO omanikud VALUES (1, 'Juku', 1963);
INSERT INTO omanikud VALUES (2, 'Mati', 1963);
INSERT INTO omanikud VALUES (3, 'Kati', 1983);

CREATE TABLE soidukid(
  regnr CHAR(6) NOT NULL PRIMARY KEY,
  mark VARCHAR(20),
  valmimisaasta INT,
  omaniku_id INT,
  FOREIGN KEY(omaniku_id) REFERENCES omanikud(id)
);

INSERT INTO soidukid VALUES ('123ABC', 'Volga', 1980, 2);
INSERT INTO soidukid VALUES ('223ABC', 'Volga', 1981, 2);
INSERT INTO soidukid VALUES ('323ABC', 'Lada', 1980, 1);
INSERT INTO soidukid VALUES ('423ABC', 'Volkswagen', 2005, 3);

mysql> SELECT * FROM omanikud;
+----+---------+------------+
| id | eesnimi | synniaasta |
+----+---------+------------+
|  1 | Juku    |       1963 |
|  2 | Mati    |       1963 |
|  3 | Kati    |       1983 |
+----+---------+------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM soidukid;
+--------+------------+---------------+------------+
| regnr  | mark       | valmimisaasta | omaniku_id |
+--------+------------+---------------+------------+
| 123ABC | Volga      |          1980 |          2 |
| 223ABC | Volga      |          1981 |          2 |
| 323ABC | Lada       |          1980 |          1 |
| 423ABC | Volkswagen |          2005 |          3 |
+--------+------------+---------------+------------+
4 rows in set (0.00 sec)

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

+--------+------------+---------------+------------+----+---------+------------+
| regnr  | mark       | valmimisaasta | omaniku_id | id | eesnimi | synniaasta |
+--------+------------+---------------+------------+----+---------+------------+
| 123ABC | Volga      |          1980 |          2 |  2 | Mati    |       1963 |
| 223ABC | Volga      |          1981 |          2 |  2 | Mati    |       1963 |
| 323ABC | Lada       |          1980 |          1 |  1 | Juku    |       1963 |
| 423ABC | Volkswagen |          2005 |          3 |  3 | Kati    |       1983 |
+--------+------------+---------------+------------+----+---------+------------+

SELECT eesnimi, mark FROM soidukid
  JOIN omanikud ON soidukid.omaniku_id=omanikud.id;
  
SELECT mark, COUNT(*) FROM soidukid GROUP BY mark;

+------------+----------+
| mark       | COUNT(*) |
+------------+----------+
| Lada       |        1 |
| Volga      |        2 |
| Volkswagen |        1 |
+------------+----------+


SELECT eesnimi, mark, valmimisaasta-synniaasta FROM soidukid
  JOIN omanikud ON soidukid.omaniku_id=omanikud.id;
  
-- iga omaniku sünniaasta kohta mitu millisest margist sõidukit tabelis

SELECT synniaasta, mark, COUNT(*) FROM soidukid
  JOIN omanikud ON soidukid.omaniku_id=omanikud.id
  GROUP BY synniaasta, mark;
+------------+------------+----------+
| synniaasta | mark       | COUNT(*) |
+------------+------------+----------+
|       1963 | Lada       |        1 |
|       1963 | Volga      |        2 |
|       1983 | Volkswagen |        1 |
+------------+------------+----------+

-- iga omaniku erineva sünniaasta kohta mitu autot on üldse tabelis
SELECT synniaasta, COUNT(*) FROM soidukid
  JOIN omanikud ON soidukid.omaniku_id=omanikud.id
  GROUP BY synniaasta;

+------------+----------+
| synniaasta | COUNT(*) |
+------------+----------+
|       1963 |        3 |
|       1983 |        1 |
+------------+----------+

SELECT synniaasta, COUNT(*), GROUP_CONCAT(mark) FROM soidukid
  JOIN omanikud ON soidukid.omaniku_id=omanikud.id
  GROUP BY synniaasta;

+------------+----------+--------------------+
| synniaasta | COUNT(*) | GROUP_CONCAT(mark) |
+------------+----------+--------------------+
|       1963 |        3 | Lada,Volga,Volga   |
|       1983 |        1 | Volkswagen         |
+------------+----------+--------------------+

SELECT synniaasta, COUNT(*), GROUP_CONCAT(DISTINCT mark) FROM soidukid
  JOIN omanikud ON soidukid.omaniku_id=omanikud.id
  GROUP BY synniaasta;
+------------+----------+-----------------------------+
| synniaasta | COUNT(*) | GROUP_CONCAT(DISTINCT mark) |
+------------+----------+-----------------------------+
|       1963 |        3 | Volga,Lada                  |
|       1983 |        1 | Volkswagen                  |
+------------+----------+-----------------------------+


--leia iga isiku kohta tema kõige uuema auto valmimisaasta

SELECT omanikud.id, eesnimi, MAX(valmimisaasta) FROM omanikud
   JOIN soidukid ON soidukid.omaniku_id=omanikud.id
   GROUP BY omanikud.id;
+----+---------+--------------------+
| id | eesnimi | MAX(valmimisaasta) |
+----+---------+--------------------+
|  1 | Juku    |               1980 |
|  2 | Mati    |               1981 |
|  3 | Kati    |               2005 |
+----+---------+--------------------+

SELECT omanikud.id, eesnimi, 
  (SELECT MAX(valmimisaasta) FROM soidukid WHERE soidukid.omaniku_id=omanikud.id) AS uusim
 FROM omanikud;
+----+---------+-------+
| id | eesnimi | uusim |
+----+---------+-------+
|  1 | Juku    |  1980 |
|  2 | Mati    |  1981 |
|  3 | Kati    |  2005 |
+----+---------+-------+

-- Milline osakaal sõidukite tabelis olevatest sõidukitest kuulub 
-- igale omanike tabelis olevale omanikule?

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

+----+---------+----------+
| id | eesnimi | COUNT(*) |
+----+---------+----------+
|  1 | Juku    |        1 |
|  2 | Mati    |        2 |
|  3 | Kati    |        1 |
+----+---------+----------+


SELECT omanikud.id, eesnimi, COUNT(*)/(SELECT COUNT(*) FROM soidukid) FROM omanikud
   JOIN soidukid ON soidukid.omaniku_id=omanikud.id
   GROUP BY omanikud.id;

+----+---------+------------------------------------------+
| id | eesnimi | COUNT(*)/(SELECT COUNT(*) FROM soidukid) |
+----+---------+------------------------------------------+
|  1 | Juku    |                                   0.2500 |
|  2 | Mati    |                                   0.5000 |
|  3 | Kati    |                                   0.2500 |
+----+---------+------------------------------------------

SELECT eesnimi, ROUND(100*kogus/(SELECT COUNT(*) FROM soidukid)) AS protsent FROM
(SELECT omanikud.id, eesnimi, COUNT(*) AS kogus FROM omanikud
   JOIN soidukid ON soidukid.omaniku_id=omanikud.id
   GROUP BY omanikud.id) AS tabel1;
   
+---------+----------+
| eesnimi | protsent |
+---------+----------+
| Juku    |       25 |
| Mati    |       50 |
| Kati    |       25 |
+---------+----------+

INSERT INTO omanikud (eesnimi, synniaasta) VALUES ('Madis', 2005);

INSERT INTO soidukid (regnr, mark, valmimisaasta, omaniku_id) VALUES ('002BMW', 'BMW', 2016, NULL);

mysql> SELECT * FROM omanikud;
+----+---------+------------+
| id | eesnimi | synniaasta |
+----+---------+------------+
|  1 | Juku    |       1963 |
|  2 | Mati    |       1963 |
|  3 | Kati    |       1983 |
|  4 | Madis   |       2005 |
+----+---------+------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM soidukid;
+--------+------------+---------------+------------+
| regnr  | mark       | valmimisaasta | omaniku_id |
+--------+------------+---------------+------------+
| 002BMW | BMW        |          2016 |       NULL |
| 123ABC | Volga      |          1980 |          2 |
| 223ABC | Volga      |          1981 |          2 |
| 323ABC | Lada       |          1980 |          1 |
| 423ABC | Volkswagen |          2005 |          3 |
+--------+------------+---------------+------------+

SELECT eesnimi, mark FROM omanikud
  LEFT JOIN soidukid ON soidukid.omaniku_id=omanikud.id;

+---------+------------+
| eesnimi | mark       |
+---------+------------+
| Juku    | Lada       |
| Mati    | Volga      |
| Mati    | Volga      |
| Kati    | Volkswagen |
| Madis   | NULL       |
+---------+------------+

SELECT eesnimi, mark FROM omanikud
  RIGHT JOIN soidukid ON soidukid.omaniku_id=omanikud.id;

+---------+------------+
| eesnimi | mark       |
+---------+------------+
| NULL    | BMW        |
| Mati    | Volga      |
| Mati    | Volga      |
| Juku    | Lada       |
| Kati    | Volkswagen |
+---------+------------+