Mitu-mitmele seos

tabelid:

maakonnad(id, maakonnanimi, rahvaarv)
keskkoolid(id, nimetus, maakonna_id, opilaste_arv)
yritused(id, nimetus, maakonna_id)
osalemine(id, keskkooli_id, yrituse_id)

Maakondade ja koolide loomise näide failis kakstabelit.txt

CREATE TABLE yritused(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  nimetus VARCHAR(50),
  maakonna_id INT,
  FOREIGN KEY(maakonna_id) REFERENCES maakonnad(id)
);

CREATE TABLE osalemine(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  keskkooli_id INT,
  yrituse_id INT,
  osalejate_arv INT,
  FOREIGN KEY(yrituse_id) REFERENCES yritused(id),
  FOREIGN KEY(keskkooli_id) REFERENCES keskkoolid(id)  
);


INSERT INTO maakonnad (id, maakonnanimi, rahvaarv) 
  VALUES (4, 'Tartumaa', 150287);
  
INSERT INTO yritused(nimetus, maakonna_id) VALUES ('Informaatikaolümpiaad', 4);
INSERT INTO yritused(nimetus, maakonna_id) VALUES ('Hiiu folk', 1);

Viis Wiedemanni õppurit käis Tartus informaatikaolümpiaadil:

INSERT INTO osalemine(keskkooli_id, yrituse_id, osalejate_arv)  VALUES (2, 1, 5);

INSERT INTO osalemine(keskkooli_id, yrituse_id, osalejate_arv)  VALUES (4, 1, 3);
INSERT INTO osalemine(keskkooli_id, yrituse_id, osalejate_arv)  VALUES (3, 2, 100);

INSERT INTO osalemine(keskkooli_id, yrituse_id, osalejate_arv)  VALUES (3, 1, 2);


+----+--------------+------------+---------------+
| id | keskkooli_id | yrituse_id | osalejate_arv |
+----+--------------+------------+---------------+
|  1 |            2 |          1 |             5 |
|  2 |            4 |          1 |             3 |
|  3 |            3 |          2 |           100 |
|  4 |            3 |          1 |             2 |
+----+--------------+------------+---------------+



Keskkooli ja ürituse kohale nimetus

SELECT 
   koolinimetus,
   yritused.nimetus as yritusenimi,
   osalejate_arv
  FROM keskkoolid, osalemine, yritused
   WHERE osalemine.keskkooli_id=keskkoolid.id
     AND osalemine.yrituse_id=yritused.id;


+--------------------------------+-----------------------+---------------+
| koolinimetus                   | yritusenimi           | osalejate_arv |
+--------------------------------+-----------------------+---------------+
| Haapsalu Wiedemanni Gümnaasium | Informaatikaolümpiaad |             5 |
| Käina Gümnaasium               | Informaatikaolümpiaad |             3 |
| Kärdla Ühisgümnaasium          | Hiiu folk             |           100 |
| Kärdla Ühisgümnaasium          | Informaatikaolümpiaad |             2 |
+--------------------------------+-----------------------+---------------+


Näita ürituste kaupa, mitu inimest igal üritusel on käinud

SELECT yritused.nimetus, sum(osalejate_arv)
  FROM yritused, osalemine
    WHERE osalemine.yrituse_id=yritused.id
    GROUP BY yritused.id;

    
+-----------------------+--------------------+
| nimetus               | sum(osalejate_arv) |
+-----------------------+--------------------+
| Informaatikaolümpiaad |                 10 |
| Hiiu folk             |                100 |
+-----------------------+--------------------+


Näita, millistest koolidest kus üritustel käidi - ürituste kaupa

SELECT yritused.nimetus, GROUP_CONCAT(koolinimetus)
  FROM yritused, osalemine, keskkoolid
    WHERE osalemine.yrituse_id=yritused.id
      AND osalemine.keskkooli_id=keskkoolid.id
    GROUP BY yritused.id;

+-----------------------+-----------------------------------------------------------------------+
| nimetus               | GROUP_CONCAT(koolinimetus)                                            |
+-----------------------+-----------------------------------------------------------------------+
| Informaatikaolümpiaad | Haapsalu Wiedemanni Gümnaasium,Kärdla Ühisgümnaasium,Käina Gümnaasium |
| Hiiu folk             | Kärdla Ühisgümnaasium                                                 |
+-----------------------+-----------------------------------------------------------------------+


Koolide osalemine üritustel koos koolide ja ürituste maakondadega

SELECT 
   koolinimetus,
   koolimaakonnad.maakonnanimi as koolimaakond,
   yritused.nimetus as yritusenimi,
   yritusemaakonnad.maakonnanimi as yritusemaakond,
   osalejate_arv
  FROM keskkoolid, osalemine, yritused,
     maakonnad as koolimaakonnad,
     maakonnad as yritusemaakonnad
   WHERE osalemine.keskkooli_id=keskkoolid.id
     AND osalemine.yrituse_id=yritused.id
     AND keskkoolid.maakonna_id=koolimaakonnad.id
     AND yritused.maakonna_id=yritusemaakonnad.id;
     

+--------------------------------+--------------+-----------------------+----------------+---------------+
| koolinimetus                   | koolimaakond | yritusenimi           | yritusemaakond | osalejate_arv |
+--------------------------------+--------------+-----------------------+----------------+---------------+
| Haapsalu Wiedemanni Gümnaasium | Läänemaa     | Informaatikaolümpiaad | Tartumaa       |             5 |
| Käina Gümnaasium               | Hiiumaa      | Informaatikaolümpiaad | Tartumaa       |             3 |
| Kärdla Ühisgümnaasium          | Hiiumaa      | Hiiu folk             | Hiiumaa        |           100 |
| Kärdla Ühisgümnaasium          | Hiiumaa      | Informaatikaolümpiaad | Tartumaa       |             2 |
+--------------------------------+--------------+-----------------------+----------------+---------------+