Mitu mitmele seos

Näitena kutsekool, kus on traktorid, nendel harjutavad õpilased ning seosena kirjas, et kes millise traktoriga harjutas.

CREATE TABLE traktorid(
   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   mass INT,
   mark VARCHAR(30)
);

INSERT INTO traktorid VALUES (NULL, 960, 'DT-20');
INSERT INTO traktorid VALUES (NULL, 6500, 'New Holland');
INSERT INTO traktorid VALUES (NULL, 7500, 'John Deere');

CREATE TABLE harjutajad(
   knimi VARCHAR(15) NOT NULL PRIMARY KEY,
   epost VARCHAR(25) NOT NULL,
   sugu ENUM('M', 'N') NOT NULL
);

INSERT INTO harjutajad VALUES ('juku', 'juku@pollukool.ee', 'M');
INSERT INTO harjutajad VALUES ('kati', 'kati@pollukool.ee', 'N');
INSERT INTO harjutajad VALUES ('mati', 'mati@pollukool.ee', 'M');


SELECT * FROM traktorid;
+----+------+-------------+
| id | mass | mark        |
+----+------+-------------+
|  1 |  960 | DT-20       |
|  2 | 6500 | New Holland |
|  3 | 7500 | John Deere  |
+----+------+-------------+

mysql> SELECT * FROM harjutajad;
+-------+-------------------+------+
| knimi | epost             | sugu |
+-------+-------------------+------+
| juku  | juku@pollukool.ee | M    |
| kati  | kati@pollukool.ee | N    |
| mati  | mati@pollukool.ee | M    |
+-------+-------------------+------+

CREATE TABLE harjutamine(
   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   traktori_id INT,
   harjutaja_knimi VARCHAR(15),
   aeg DATETIME
);

INSERT INTO harjutamine VALUES (NULL, 3, 'kati', NOW());
INSERT INTO harjutamine VALUES (NULL, 1, 'juku', NOW());
INSERT INTO harjutamine VALUES (NULL, 2, 'kati', NOW());


mysql> SELECT * FROM harjutamine;
+----+-------------+-----------------+---------------------+
| id | traktori_id | harjutaja_knimi | aeg                 |
+----+-------------+-----------------+---------------------+
|  1 |           3 | kati            | 2015-10-14 12:54:02 |
|  2 |           1 | juku            | 2015-10-14 12:54:48 |
|  3 |           2 | kati            | 2015-10-14 12:54:49 |
+----+-------------+-----------------+---------------------+


SELECT * FROM traktorid
  JOIN harjutamine ON harjutamine.traktori_id=traktorid.id
  JOIN harjutajad ON harjutamine.harjutaja_knimi=harjutajad.knimi;
  
 +----+------+-------------+----+-------------+-----------------+---------------------+-------+-------------------+------+
| id | mass | mark        | id | traktori_id | harjutaja_knimi | aeg                 | knimi | epost             | sugu |
+----+------+-------------+----+-------------+-----------------+---------------------+-------+-------------------+------+
|  3 | 7500 | John Deere  |  1 |           3 | kati            | 2015-10-14 12:54:02 | kati  | kati@pollukool.ee | N    |
|  1 |  960 | DT-20       |  2 |           1 | juku            | 2015-10-14 12:54:48 | juku  | juku@pollukool.ee | M    |
|  2 | 6500 | New Holland |  3 |           2 | kati            | 2015-10-14 12:54:49 | kati  | kati@pollukool.ee | N    |
+----+------+-------------+----+-------------+-----------------+---------------------+-------+-------------------+------+


-- Lisa mati sõitma New Hollandiga

INSERT INTO harjutamine VALUES (NULL, 2, 'mati', NOW());

-- Näita iga traktori kohta, millal temaga on sõitma mindud

SELECT traktorid.id, mark, aeg FROM traktorid 
  JOIN harjutamine ON harjutamine.traktori_id=traktorid.id;


+----+-------------+---------------------+
| id | mark        | aeg                 |
+----+-------------+---------------------+
|  3 | John Deere  | 2015-10-14 12:54:02 |
|  1 | DT-20       | 2015-10-14 12:54:48 |
|  2 | New Holland | 2015-10-14 12:54:49 |
|  2 | New Holland | 2015-10-14 13:20:06 |
+----+-------------+---------------------+
  
  
SELECT traktorid.id, mark, GROUP_CONCAT(aeg) FROM traktorid 
  JOIN harjutamine ON harjutamine.traktori_id=traktorid.id
  GROUP BY traktorid.id;

+----+-------------+-----------------------------------------+
| id | mark        | GROUP_CONCAT(aeg)                       |
+----+-------------+-----------------------------------------+
|  1 | DT-20       | 2015-10-14 12:54:48                     |
|  2 | New Holland | 2015-10-14 13:20:06,2015-10-14 12:54:49 |
|  3 | John Deere  | 2015-10-14 12:54:02                     |
+----+-------------+-----------------------------------------+
-- Näita iga traktori kohta, mitu korda on temaga harjutatud

 SELECT traktorid.id, mark, COUNT(aeg) FROM traktorid 
  JOIN harjutamine ON harjutamine.traktori_id=traktorid.id
  GROUP BY traktorid.id;

+----+-------------+------------+
| id | mark        | COUNT(aeg) |
+----+-------------+------------+
|  1 | DT-20       |          1 |
|  2 | New Holland |          2 |
|  3 | John Deere  |          1 |
+----+-------------+------------+


-- Näita iga traktori kohta, millisest soost õppurid on temaga harjutanud

 SELECT traktorid.id, mark, GROUP_CONCAT(sugu) FROM traktorid 
  JOIN harjutamine ON harjutamine.traktori_id=traktorid.id
  JOIN harjutajad ON harjutamine.harjutaja_knimi=harjutajad.knimi
  GROUP BY traktorid.id;
  
+----+-------------+--------------------+
| id | mark        | GROUP_CONCAT(sugu) |
+----+-------------+--------------------+
|  1 | DT-20       | M                  |
|  2 | New Holland | N,M                |
|  3 | John Deere  | N                  |
+----+-------------+--------------------+

____

Mitu mitmele Workbenchi abil

INSERT INTO kool_riigid (riigikood, riiginimi) VALUES ('NED', 'Holland');
INSERT INTO kool_riigid (riigikood, riiginimi) VALUES ('USA', 'Ameerika Ühendriigid');
INSERT INTO kool_riigid (riigikood, riiginimi) VALUES ('BLR', 'Valgevene');
INSERT INTO kool_riigid (riigikood, riiginimi) VALUES ('FIN', 'Soome');


INSERT INTO kool_traktorid(id, mass, mark, valmistajariigi_kood) VALUES 
  (NULL, 960, 'DT-20', 'BLR');
INSERT INTO kool_traktorid(id, mass, mark, valmistajariigi_kood) VALUES 
  (NULL, 7500, 'John Deere', 'BLR');
INSERT INTO kool_traktorid(id, mass, mark, valmistajariigi_kood) VALUES 
  (NULL, 6500, 'New Holland', 'NED');
  
  
INSERT INTO kool_harjutajad(knimi, epost, sugu) VALUES ('juku', 'juku@hot.ee', 'M');
INSERT INTO kool_harjutajad(knimi, epost, sugu) VALUES ('kati', 'katitamm@gmail.com', 'N');
INSERT INTO kool_harjutajad(knimi, epost, sugu) VALUES ('mati', 'mati@hot.ee', 'M');
INSERT INTO kool_harjutajad(knimi, epost, sugu) VALUES ('madis', 'madis@hot.ee', 'M');


INSERT INTO kool_harjutamine (traktori_id, harjutaja_knimi) VALUES (3, 'juku');
INSERT INTO kool_harjutamine (traktori_id, harjutaja_knimi) VALUES (3, 'juku');
INSERT INTO kool_harjutamine (traktori_id, harjutaja_knimi) VALUES (2, 'kati');
INSERT INTO kool_harjutamine (traktori_id, harjutaja_knimi) VALUES (3, 'kati');
INSERT INTO kool_harjutamine (traktori_id, harjutaja_knimi) VALUES (1, 'mati');

INSERT INTO kool_reisid (knimi, riigikood, aasta) VALUES ('juku', 'FIN', 2012);
INSERT INTO kool_reisid (knimi, riigikood, aasta) VALUES ('mati', 'FIN', 2012);
INSERT INTO kool_reisid (knimi, riigikood, aasta) VALUES ('madis', 'USA', 2012);
INSERT INTO kool_reisid (knimi, riigikood, aasta) VALUES ('kati', 'USA', 2012);
INSERT INTO kool_reisid (knimi, riigikood, aasta) VALUES ('kati', 'NED', 2015);

------

Millise ID-ga traktoriga mitu korda harjutatud:

SELECT traktori_id, COUNT(*) FROM kool_harjutamine GROUP BY traktori_id;

+-------------+----------+
| traktori_id | COUNT(*) |
+-------------+----------+
|           1 |        1 |
|           2 |        1 |
|           3 |        3 |
+-------------+----------+


-- Lisa juurde traktori mark
SELECT traktori_id, kool_traktorid.mark,  COUNT(*) FROM kool_harjutamine
  JOIN kool_traktorid ON kool_harjutamine.traktori_id=kool_traktorid.id
  GROUP BY traktori_id;

-- lisa juurde harjutajate epostid

SELECT traktori_id, kool_traktorid.mark,  COUNT(*), GROUP_CONCAT(epost) FROM kool_harjutamine
  JOIN kool_traktorid ON kool_harjutamine.traktori_id=kool_traktorid.id
  JOIN kool_harjutajad ON kool_harjutamine.harjutaja_knimi=kool_harjutajad.knimi
  GROUP BY traktori_id;
  
+-------------+-------------+----------+--------------------------------------------+
| traktori_id | mark        | COUNT(*) | GROUP_CONCAT(epost)                        |
+-------------+-------------+----------+--------------------------------------------+
|           1 | DT-20       |        1 | mati@hot.ee                                |
|           2 | John Deere  |        1 | katitamm@gmail.com                         |
|           3 | New Holland |        3 | katitamm@gmail.com,juku@hot.ee,juku@hot.ee |
+-------------+-------------+----------+--------------------------------------------+


Millise traktoriga millisest soost isik mitu korda harjutanud

SELECT kool_traktorid.mark, sugu, COUNT(*) FROM kool_harjutamine
  JOIN kool_traktorid ON kool_harjutamine.traktori_id=kool_traktorid.id
  JOIN kool_harjutajad ON kool_harjutamine.harjutaja_knimi=kool_harjutajad.knimi
  GROUP BY traktori_id, sugu;
  
+-------------+------+----------+
| mark        | sugu | COUNT(*) |
+-------------+------+----------+
| DT-20       | M    |        1 |
| John Deere  | N    |        1 |
| New Holland | M    |        2 |
| New Holland | N    |        1 |
+-------------+------+----------+

SELECT kool_traktorid.id, mark, 
  (SELECT COUNT(*) FROM kool_harjutamine WHERE traktori_id=kool_traktorid.id) as kogus
  FROM kool_traktorid;
  

+----+-------------+-------+
| id | mark        | kogus |
+----+-------------+-------+
|  1 | DT-20       |     1 |
|  2 | John Deere  |     1 |
|  3 | New Holland |     3 |
+----+-------------+-------+

SELECT kool_traktorid.id, mark, 
  (SELECT COUNT(*) FROM kool_harjutamine JOIN kool_harjutajad 
     ON kool_harjutamine.harjutaja_knimi=kool_harjutajad.knimi 
	 WHERE traktori_id=kool_traktorid.id AND sugu='M') as meestekogus
  FROM kool_traktorid;
  
-- Lisage iga traktori kohta ka harjutanud naiste kogus


SELECT kool_traktorid.id, mark, 
  (SELECT COUNT(*) FROM kool_harjutamine JOIN kool_harjutajad 
     ON kool_harjutamine.harjutaja_knimi=kool_harjutajad.knimi 
	 WHERE traktori_id=kool_traktorid.id AND sugu='M') as meestekogus,
  (SELECT COUNT(*) FROM kool_harjutamine JOIN kool_harjutajad 
     ON kool_harjutamine.harjutaja_knimi=kool_harjutajad.knimi 
	 WHERE traktori_id=kool_traktorid.id AND sugu='N') as naistekogus
  FROM kool_traktorid;
  
-- 
 
 SELECT kool_traktorid.mark, riiginimi,  sugu, COUNT(*) FROM kool_harjutamine
  JOIN kool_traktorid ON kool_harjutamine.traktori_id=kool_traktorid.id
  JOIN kool_riigid ON kool_traktorid.valmistajariigi_kood=kool_riigid.riigikood
  JOIN kool_harjutajad ON kool_harjutamine.harjutaja_knimi=kool_harjutajad.knimi
  GROUP BY traktori_id, sugu;
  

+-------------+-----------------------+------+----------+
| mark        | riiginimi             | sugu | COUNT(*) |
+-------------+-----------------------+------+----------+
| DT-20       | Valgevene             | M    |        1 |
| John Deere  | Ameerika Ühendriigid  | N    |        1 |
| New Holland | Holland               | M    |        2 |
| New Holland | Holland               | N    |        1 |
+-------------+-----------------------+------+----------+