Kaks seotud andmebaasitabelit

(üks-mitmele seos)

maakonnad(id, maakonnanimi, rahvaarv)
keskkoolid(id, nimetus, maakonna_id, opilaste_arv)



CREATE TABLE maakonnad(
  id INT NOT NULL PRIMARY KEY,
  maakonnanimi VARCHAR(50),
  rahvaarv INT
);

INSERT INTO maakonnad VALUES(1, 'Hiiumaa', 8470);
INSERT INTO maakonnad VALUES(2, 'Läänemaa', 24184);
INSERT INTO maakonnad VALUES(3, 'Saaremaa', 31344);

CREATE TABLE keskkoolid(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  koolinimetus VARCHAR(50),
  maakonna_id INT,
  opilaste_arv int,
  FOREIGN KEY(maakonna_id) REFERENCES maakonnad(id)
);

INSERT INTO keskkoolid (koolinimetus, maakonna_id, opilaste_arv) 
  VALUES ('Haapsalu Gümnaasium', 2, 629);
INSERT INTO keskkoolid (koolinimetus, maakonna_id, opilaste_arv) 
  VALUES ('Haapsalu Wiedemanni Gümnaasium', 2, 383);
INSERT INTO keskkoolid (koolinimetus, maakonna_id, opilaste_arv) 
  VALUES ('Kärdla Ühisgümnaasium', 1, 494);
INSERT INTO keskkoolid (koolinimetus, maakonna_id, opilaste_arv) 
  VALUES ('Käina Gümnaasium', 1, 246);
INSERT INTO keskkoolid (koolinimetus, maakonna_id, opilaste_arv) 
  VALUES ('Orissaare Gümnaasium', 3, 243);
INSERT INTO keskkoolid (koolinimetus, maakonna_id, opilaste_arv) 
  VALUES ('Lihula Gümnaasium', 2, 229);
INSERT INTO keskkoolid (koolinimetus, maakonna_id, opilaste_arv) 
  VALUES ('Taebla Gümnaasium', 2, 219);
INSERT INTO keskkoolid (koolinimetus, maakonna_id, opilaste_arv) 
  VALUES ('Leisi Keskkool', 3, 209);
INSERT INTO keskkoolid (koolinimetus, maakonna_id, opilaste_arv) 
  VALUES ('Kullamaa Keskkool', 2, 183);
INSERT INTO keskkoolid (koolinimetus, maakonna_id, opilaste_arv) 
  VALUES ('Noarootsi Gümnaasium', 2, 159);
INSERT INTO keskkoolid (koolinimetus, maakonna_id, opilaste_arv) 
  VALUES ('Kuressaare Gümnaasium', 3, 866);
INSERT INTO keskkoolid (koolinimetus, maakonna_id, opilaste_arv) 
  VALUES ('Saaremaa Ühisgümnaasium', 3, 728);

  
mysql> SELECT * FROM maakonnad;
+----+--------------+----------+
| id | maakonnanimi | rahvaarv |
+----+--------------+----------+
|  1 | Hiiumaa      |     8470 |
|  2 | Läänemaa     |    24184 |
|  3 | Saaremaa     |    31344 |
+----+--------------+----------+



mysql> SELECT * FROM keskkoolid;
+----+--------------------------------+-------------+--------------+
| id | koolinimetus                   | maakonna_id | opilaste_arv |
+----+--------------------------------+-------------+--------------+
|  1 | Haapsalu Gümnaasium            |           2 |          629 |
|  2 | Haapsalu Wiedemanni Gümnaasium |           2 |          383 |
|  3 | Kärdla Ühisgümnaasium          |           1 |          494 |
|  4 | Käina Gümnaasium               |           1 |          246 |
|  5 | Orissaare Gümnaasium           |           3 |          243 |
|  6 | Lihula Gümnaasium              |           2 |          229 |
|  7 | Taebla Gümnaasium              |           2 |          219 |
|  8 | Leisi Keskkool                 |           3 |          209 |
|  9 | Kullamaa Keskkool              |           2 |          183 |
| 10 | Noarootsi Gümnaasium           |           2 |          159 |
+----+--------------------------------+-------------+--------------+


Keskkoolide arv (tabelis) kokku

SELECT COUNT(*) FROM keskkoolid;


+----------+
| COUNT(*) |
+----------+
|       10 |
+----------+


Õpilaste arv keskkoolide tabelis kokku:

mysql> SELECT SUM(opilaste_arv) FROM keskkoolid;
+-------------------+
| SUM(opilaste_arv) |
+-------------------+
|              2994 |
+-------------------+


Hiiumaa keskkoolide õpilaste arv

SELECT SUM(opilaste_arv) FROM keskkoolid WHERE maakonna_id=1;

+-------------------+
| SUM(opilaste_arv) |
+-------------------+
|               740 |
+-------------------+



Koolide nimetused koos nende maakondade nimetustega

SELECT koolinimetus, maakonnanimi
  FROM keskkoolid, maakonnad
  WHERE keskkoolid.maakonna_id=maakonnad.id;

+--------------------------------+--------------+
| koolinimetus                   | maakonnanimi |
+--------------------------------+--------------+
| Kärdla Ühisgümnaasium          | Hiiumaa      |
| Käina Gümnaasium               | Hiiumaa      |
| Haapsalu Gümnaasium            | Läänemaa     |
| Haapsalu Wiedemanni Gümnaasium | Läänemaa     |
| Lihula Gümnaasium              | Läänemaa     |
| Taebla Gümnaasium              | Läänemaa     |
| Kullamaa Keskkool              | Läänemaa     |
| Noarootsi Gümnaasium           | Läänemaa     |
| Orissaare Gümnaasium           | Saaremaa     |
| Leisi Keskkool                 | Saaremaa     |
+--------------------------------+--------------+



mysql> SELECT * FROM keskkoolid;
+----+--------------------------------+-------------+--------------+
| id | koolinimetus                   | maakonna_id | opilaste_arv |
+----+--------------------------------+-------------+--------------+
|  1 | Haapsalu Gümnaasium            |           2 |          629 |
|  2 | Haapsalu Wiedemanni Gümnaasium |           2 |          383 |
|  3 | Kärdla Ühisgümnaasium          |           1 |          494 |
|  4 | Käina Gümnaasium               |           1 |          246 |
|  5 | Orissaare Gümnaasium           |           3 |          243 |
|  6 | Lihula Gümnaasium              |           2 |          229 |
|  7 | Taebla Gümnaasium              |           2 |          219 |
|  8 | Leisi Keskkool                 |           3 |          209 |
|  9 | Kullamaa Keskkool              |           2 |          183 |
| 10 | Noarootsi Gümnaasium           |           2 |          159 |
| 11 | Kuressaare Gümnaasium          |           3 |          866 |
| 12 | Saaremaa Ühisgümnaasium        |           3 |          728 |
+----+--------------------------------+-------------+--------------+

UPDATE keskkoolid SET maakonna_id=3 WHERE id=12;



Keskkoolide arvud maakondade kaupa

SELECT maakonna_id, COUNT(*) FROM keskkoolid GROUP BY maakonna_id;

+-------------+----------+
| maakonna_id | COUNT(*) |
+-------------+----------+
|           1 |        2 |
|           2 |        6 |
|           3 |        4 |
+-------------+----------+

SELECT maakonnanimi, COUNT(*) AS keskkoolide_arv 
  FROM keskkoolid, maakonnad 
  WHERE keskkoolid.maakonna_id=maakonnad.id
  GROUP BY maakonnanimi;
  
+--------------+-----------------+
| maakonnanimi | keskkoolide_arv |
+--------------+-----------------+
| Hiiumaa      |               2 |
| Läänemaa     |               6 |
| Saaremaa     |               4 |
+--------------+-----------------+


Summeerige kokku, kui palju õpilasi käib keskkoolides igas maakonnas

SELECT maakonnanimi, SUM(opilaste_arv) AS opilasi_keskkoolides 
  FROM keskkoolid, maakonnad 
  WHERE keskkoolid.maakonna_id=maakonnad.id
  GROUP BY maakonnanimi;
  


+--------------+----------------------+
| maakonnanimi | opilasi_keskkoolides |
+--------------+----------------------+
| Hiiumaa      |                  740 |
| Läänemaa     |                 1802 |
| Saaremaa     |                 2046 |
+--------------+----------------------+

SELECT maakonnanimi, 
    COUNT(*) AS keskkoolide_arv,
    SUM(opilaste_arv) AS opilasi_keskkoolides,
    SUM(opilaste_arv)/ COUNT(*) AS opilasi_koolis_keskmiselt    
  FROM keskkoolid, maakonnad 
  WHERE keskkoolid.maakonna_id=maakonnad.id
  GROUP BY maakonnanimi;
  

+--------------+-----------------+----------------------+---------------------------+
| maakonnanimi | keskkoolide_arv | opilasi_keskkoolides | opilasi_koolis_keskmiselt |
+--------------+-----------------+----------------------+---------------------------+
| Hiiumaa      |               2 |                  740 |                  370.0000 |
| Läänemaa     |               6 |                 1802 |                  300.3333 |
| Saaremaa     |               4 |                 2046 |                  511.5000 |
+--------------+-----------------+----------------------+---------------------------+


SELECT maakonnanimi, 
    COUNT(*) AS keskkoolide_arv,
    SUM(opilaste_arv) AS opilasi_keskkoolides
  FROM keskkoolid, maakonnad 
  WHERE keskkoolid.maakonna_id=maakonnad.id
  GROUP BY maakonnad.id;
  

+--------------+-----------------+----------------------+
| maakonnanimi | keskkoolide_arv | opilasi_keskkoolides |
+--------------+-----------------+----------------------+
| Hiiumaa      |               2 |                  740 |
| Läänemaa     |               6 |                 1802 |
| Saaremaa     |               4 |                 2046 |
+--------------+-----------------+----------------------+

SELECT maakonnanimi, 
  opilasi_keskkoolides/keskkoolide_arv AS opilasi_koolis_keskmiselt 
  FROM
   (SELECT maakonnanimi, 
      COUNT(*) AS keskkoolide_arv,
      SUM(opilaste_arv) AS opilasi_keskkoolides
     FROM keskkoolid, maakonnad 
     WHERE keskkoolid.maakonna_id=maakonnad.id
     GROUP BY maakonnad.id) AS tabel1;


+--------------+---------------------------+
| maakonnanimi | opilasi_koolis_keskmiselt |
+--------------+---------------------------+
| Hiiumaa      |                  370.0000 |
| Läänemaa     |                  300.3333 |
| Saaremaa     |                  511.5000 |
+--------------+---------------------------+


Leidke, mitu protsenti iga maakonna rahvaarvust moodustavad
  keskkoolides käivad õpilased


SELECT maakonnanimi, 
    SUM(opilaste_arv) AS opilasi_keskkoolides,
    maakonnad.rahvaarv
  FROM keskkoolid, maakonnad 
  WHERE keskkoolid.maakonna_id=maakonnad.id
  GROUP BY maakonnad.id;

+--------------+----------------------+----------+
| maakonnanimi | opilasi_keskkoolides | rahvaarv |
+--------------+----------------------+----------+
| Hiiumaa      |                  740 |     8470 |
| Läänemaa     |                 1802 |    24184 |
| Saaremaa     |                 2046 |    31344 |
+--------------+----------------------+----------+


SELECT maakonnanimi, 
    ROUND(100*SUM(opilaste_arv)/maakonnad.rahvaarv, 1) 
      AS keskkooliopilaste_protsent
  FROM keskkoolid, maakonnad 
  WHERE keskkoolid.maakonna_id=maakonnad.id
  GROUP BY maakonnad.id;
  
+--------------+----------------------------+
| maakonnanimi | keskkooliopilaste_protsent |
+--------------+----------------------------+
| Hiiumaa      |                        8.7 |
| Läänemaa     |                        7.5 |
| Saaremaa     |                        6.5 |
+--------------+----------------------------+