Sisselugemine

curl http://greeny.cs.tlu.ee/~jaagup/2016/abproj/koolid.txt | mysql -uif16 -pifikad16 if16_jaagup_2


mysql> show tables;
+-------------------------+
| Tables_in_if16_jaagup_2 |
+-------------------------+
| koolid                  |
| ounad                   |
| pilliparandus           |
+-------------------------+
3 rows in set (0.00 sec)

mysql> explain koolid;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| koolinimi | varchar(50) | YES  |     | NULL    |                |
| vald      | varchar(50) | YES  |     | NULL    |                |
| maakond   | varchar(50) | YES  |     | NULL    |                |
| opilasi   | int(11)     | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

mysql> SELECT maakond, COUNT(*) FROM koolid GROUP BY maakond;
+----------------------+----------+
| maakond              | COUNT(*) |
+----------------------+----------+
| Harju maakond        |      138 |
| Hiiu maakond         |        7 |
| Ida-Viru maakond     |       42 |
| Jõgeva maakond       |       25 |
| Järva maakond        |       21 |
| Lääne maakond        |       24 |
| Lääne-Viru maakond   |       40 |
| Põlva maakond        |       20 |
| Pärnu maakond        |       47 |
| Rapla maakond        |       21 |
| Saare maakond        |       19 |
| Tartu maakond        |       54 |
| Valga maakond        |       21 |
| Viljandi maakond     |       34 |
| Võru maakond         |       22 |
+----------------------+----------+
mysql> SELECT maakond, COUNT(*), SUM(opilasi) FROM koolid GROUP BY maakond;
+----------------------+----------+--------------+
| maakond              | COUNT(*) | SUM(opilasi) |
+----------------------+----------+--------------+
| Harju maakond        |      138 |        55056 |
| Hiiu maakond         |        7 |          918 |
| Ida-Viru maakond     |       42 |        14138 |
| Jõgeva maakond       |       25 |         3394 |
| Järva maakond        |       21 |         3296 |
| Lääne maakond        |       24 |         2815 |
| Lääne-Viru maakond   |       40 |         6843 |
| Põlva maakond        |       20 |         2804 |
| Pärnu maakond        |       47 |         9111 |
| Rapla maakond        |       21 |         3672 |
| Saare maakond        |       19 |         3187 |
| Tartu maakond        |       54 |        17648 |
| Valga maakond        |       21 |         3310 |
| Viljandi maakond     |       34 |         5089 |
| Võru maakond         |       22 |         3694 |
+----------------------+----------+--------------+
-- Näidake iga maakonna kohta kõige väiksem ja suurem õpilaste arv koolis

SELECT maakond, MIN(opilasi), MAX(opilasi) FROM koolid GROUP BY maakond;

mysql> SELECT maakond, MIN(opilasi), MAX(opilasi) FROM koolid GROUP BY maakond;
+----------------------+--------------+--------------+
| maakond              | MIN(opilasi) | MAX(opilasi) |
+----------------------+--------------+--------------+
| Harju maakond        |           10 |         1599 |
| Hiiu maakond         |           15 |          429 |
| Ida-Viru maakond     |           30 |          926 |
| Jõgeva maakond       |            7 |          686 |
| Järva maakond        |           16 |          569 |
| Lääne maakond        |           12 |          559 |
| Lääne-Viru maakond   |            9 |          871 |
| Põlva maakond        |           15 |          716 |
| Pärnu maakond        |            3 |          972 |
| Rapla maakond        |           17 |          725 |
| Saare maakond        |            6 |          886 |
| Tartu maakond        |           13 |         1439 |
| Valga maakond        |           13 |          850 |
| Viljandi maakond     |           12 |          727 |
| Võru maakond         |           20 |          845 |
+----------------------+--------------+--------------+

SELECT maakond, MIN(opilasi), MAX(opilasi) FROM koolid 
   GROUP BY maakond ORDER BY MIN(opilasi);
   
SELECT maakond, MIN(opilasi), MAX(opilasi) FROM koolid 
   GROUP BY maakond ORDER BY MAX(opilasi);
   
SELECT maakond, MIN(opilasi), COUNT(*) FROM koolid 
   GROUP BY maakond ORDER BY COUNT(*);

SELECT MIN(opilasi) FROM koolid WHERE maakond='Hiiu maakond';
+--------------+
| MIN(opilasi) |
+--------------+
|           15 |
+--------------+

SELECT koolinimi FROM koolid WHERE maakond='Hiiu maakond' AND opilasi=15;

SELECT koolinimi FROM koolid WHERE maakond='Hiiu maakond' AND opilasi=
  (SELECT MIN(opilasi) FROM koolid WHERE maakond='Hiiu maakond');

+---------------------+
| koolinimi           |
+---------------------+
| Kõpu Internaatkool  |
+---------------------+

-- Proovige leida konkreetse maakonna suurima õpilaste arvuga kool

SELECT koolinimi FROM koolid WHERE maakond='Hiiu maakond' AND opilasi=
  (SELECT MAX(opilasi) FROM koolid WHERE maakond='Hiiu maakond');
  
  

SELECT koolinimi FROM koolid WHERE maakond='Hiiu maakond' ORDER BY opilasi LIMIT 1;

-- Leidke iga maakonna kohta vähima õpilaste arvuga kool

SELECT koolinimi, opilasi, maakond FROM koolid AS k1 
  WHERE opilasi=(SELECT MAX(opilasi) FROM koolid AS k2 
                   WHERE k2.maakond=k1.maakond);
+-------------------------------+---------+----------------------+
| koolinimi                     | opilasi | maakond              |
+-------------------------------+---------+----------------------+
| Viimsi Keskkool               |    1599 | Harju maakond        |
| Tartu Kivilinna Gümnaasium    |    1439 | Tartu maakond        |
| Pärnu Koidula Gümnaasium      |     972 | Pärnu maakond        |
| Narva Humanitaargümnaasium    |     926 | Ida-Viru maakond     |
| Kuressaare Gümnaasium         |     886 | Saare maakond        |
| Rakvere Reaalgümnaasium       |     871 | Lääne-Viru maakond   |
| Valga Põhikool                |     850 | Valga maakond        |
| Võru Kreutzwaldi Gümnaasium   |     845 | Võru maakond         |
| Viljandi Jakobsoni Kool       |     727 | Viljandi maakond     |
| Kohila Gümnaasium             |     725 | Rapla maakond        |
| Põlva Ühisgümnaasium          |     716 | Põlva maakond        |
| Põltsamaa Ühisgümnaasium      |     686 | Jõgeva maakond       |
| Türi Põhikool                 |     569 | Järva maakond        |
| Haapsalu Gümnaasium           |     559 | Lääne maakond        |
| Kärdla Ühisgümnaasium         |     429 | Hiiu maakond         |
+-------------------------------+---------+----------------------+
				  

SELECT koolinimi, opilasi, maakond FROM koolid AS k1 
  WHERE opilasi=(SELECT MIN(opilasi) FROM koolid AS k2 
                   WHERE k2.maakond=k1.maakond);
				   
+----------------------------+---------+----------------------+
| koolinimi                  | opilasi | maakond              |
+----------------------------+---------+----------------------+
| Sonda Kool                 |      30 | Ida-Viru maakond     |
| Pikakannu Kool             |      20 | Võru maakond         |
| Kuimetsa Algkool           |      17 | Rapla maakond        |
| Kabala Kool-Lasteaed       |      16 | Järva maakond        |
| Kõpu Internaatkool         |      15 | Hiiu maakond         |
| Roosi Kool                 |      15 | Põlva maakond        |
| Pikasilla Algkool          |      13 | Valga maakond        |
| Unipiha Algkool            |      13 | Tartu maakond        |
| Ramsi Lasteaed-Kool        |      12 | Viljandi maakond     |
| Vatla Põhikool             |      12 | Lääne maakond        |
| Prangli Põhikool           |      10 | Harju maakond        |
| Lahu Algkool               |       9 | Lääne-Viru maakond   |
| Saduküla Lasteaed-Algkool  |       7 | Jõgeva maakond       |
| Ruhnu Põhikool             |       6 | Saare maakond        |
| Massiaru Algkool           |       3 | Pärnu maakond        |
+----------------------------+---------+----------------------+
				   
Näita iga maakonna kohta koolid, kus on õpilasi vähem kui selles maakonnas keskmiselt

SELECT koolinimi, opilasi, maakond FROM koolid AS k1 
  WHERE opilasi<(SELECT AVG(opilasi) FROM koolid AS k2 
                   WHERE k2.maakond=k1.maakond);
				   
SELECT koolinimi, opilasi, maakond FROM koolid AS k1 
  WHERE opilasi>(SELECT AVG(opilasi) FROM koolid AS k2 
                   WHERE k2.maakond=k1.maakond);			


SELECT maakond, COUNT(*) FROM koolid GROUP BY maakond;
				   
SELECT maakond, 100*koolemaakonnas/(SELECT COUNT(*) FROM koolid) AS protsent FROM
(SELECT maakond, COUNT(*) AS koolemaakonnas FROM koolid GROUP BY maakond) AS vahetabel
  ORDER BY protsent;	 

+----------------------+----------+
| maakond              | protsent |
+----------------------+----------+
| Hiiu maakond         |   1.3084 |
| Saare maakond        |   3.5514 |
| Põlva maakond        |   3.7383 |
| Järva maakond        |   3.9252 |
| Valga maakond        |   3.9252 |
| Rapla maakond        |   3.9252 |
| Võru maakond         |   4.1121 |
| Lääne maakond        |   4.4860 |
| Jõgeva maakond       |   4.6729 |
| Viljandi maakond     |   6.3551 |
| Lääne-Viru maakond   |   7.4766 |
| Ida-Viru maakond     |   7.8505 |
| Pärnu maakond        |   8.7850 |
| Tartu maakond        |  10.0935 |
| Harju maakond        |  25.7944 |
+----------------------+----------+

-- Näidake iga maakonna kohta sealne õpilaste arv
  SELECT maakond, SUM(opilasi) FROM koolid GROUP BY maakond;
-- Näidake õpilaste arv Eesti peale kokku
  SELECT SUM(opilasi) FROM koolid;
-- Näidake iga maakonna kohta, mitu % Eesti õpilastest õpib selle maakonna koolides
  SELECT maakond, SUM(opilasi)/(SELECT SUM(opilasi) FROM koolid) FROM koolid GROUP BY maakond;
 
-- Näidake iga maakonna kohta, mitu kooli on seal kokku
   SELECT maakond, COUNT(*) FROM koolid GROUP BY maakond;
   
   SELECT maakond, (SELECT COUNT(*) FROM koolid AS k2 WHERE k1.maakond=k2.maakond) 
          FROM koolid AS k1 GROUP BY maakond
		  
-- Näidake iga maakonna kohta, mitmes koolis on vähem kui 100 õpilast
SELECT maakond, COUNT(*) FROM koolid WHERE opilasi < 100 GROUP BY maakond;
 
SELECT maakond, 
 (SELECT COUNT(*) FROM koolid AS k2 WHERE k1.maakond=k2.maakond) AS koolemaakonnas, 
 (SELECT COUNT(*) FROM koolid AS k3 WHERE k1.maakond=k3.maakond AND k3.opilasi<100) AS v2ikseidkoole
          FROM koolid AS k1 GROUP BY maakond
 
-- Näidake iga maakonna kohta osakaal, kui paljudes koolides on vähem kui 100 õpilast

SELECT maakond, v2ikseidkoole/koolemaakonnas AS suhe FROM
(SELECT maakond, 
 (SELECT COUNT(*) FROM koolid AS k2 WHERE k1.maakond=k2.maakond) AS koolemaakonnas, 
 (SELECT COUNT(*) FROM koolid AS k3 WHERE k1.maakond=k3.maakond AND k3.opilasi<100) AS v2ikseidkoole
          FROM koolid AS k1 GROUP BY maakond) AS vahetabel
 ORDER BY suhe;