Alampäringud

Aluseks koolide ja maakondade tabelid:

http://minitorn.tlu.ee/~jaagup/kool/java/kursused/12/andmebaasid/kakstabelit.txt

Alampäringud saavad olla
 * Väärtuse asendajana
 * Loetelu asendajana
 * Tabeli asendajana

 
* Väärtuse asendajana

Iga arvu asemele on võimalik panna päring, mis väljastab ühe arvu. St. tabeli, millel on üks rida ja üks veerg ning kus sees on sobiv arv. 

mysql> select * from maakonnad;
+----+-----------------+----------+
| id | maakonnanimi    | rahvaarv |
+----+-----------------+----------+
|  1 | L?¤?¤nemaa      |    27000 |
|  2 | Harjumaa        |   525000 |
|  3 | L?¤?¤ne-Virumaa |    67000 |
|  4 | Hiiumaa         |    10000 |
+----+-----------------+----------+



mysql> SELECT AVG(rahvaarv) FROM maakonnad;
+---------------+
| AVG(rahvaarv) |
+---------------+
|   157250.0000 |
+---------------+




mysql> SELECT maakonnanimi, rahvaarv FROM maakonnad WHERE rahvaarv<157000;
+-----------------+----------+
| maakonnanimi    | rahvaarv |
+-----------------+----------+
| L?¤?¤nemaa      |    27000 |
| L?¤?¤ne-Virumaa |    67000 |
| Hiiumaa         |    10000 |
+-----------------+----------+

SELECT maakonnanimi, rahvaarv FROM maakonnad WHERE rahvaarv < (SELECT AVG(rahvaarv) FROM maakonnad);


mysql> SELECT maakonnanimi, rahvaarv FROM maakonnad WHERE rahvaarv< (SELECT AVG(rahvaarv) FROM maakonnad);
+-----------------+----------+
| maakonnanimi    | rahvaarv |
+-----------------+----------+
| L?¤?¤nemaa      |    27000 |
| L?¤?¤ne-Virumaa |    67000 |
| Hiiumaa         |    10000 |
+-----------------+----------+

* Alampäring loetelu asendajana

mysql> SELECT maakonna_id FROM koolid WHERE lastearv>450;
+-------------+
| maakonna_id |
+-------------+
|           2 |
|           2 |
|           4 |
+-------------+


mysql> SELECT * FROM maakonnad WHERE id in (SELECT maakonna_id FROM koolid WHERE lastearv>450);
+----+--------------+----------+
| id | maakonnanimi | rahvaarv |
+----+--------------+----------+
|  2 | Harjumaa     |   525000 |
|  4 | Hiiumaa      |    10000 |
+----+--------------+----------+

mysql> SELECT * FROM maakonnad WHERE id NOT IN (SELECT maakonna_id FROM koolid WHERE lastearv>450);
+----+-----------------+----------+
| id | maakonnanimi    | rahvaarv |
+----+-----------------+----------+
|  1 | L?¤?¤nemaa      |    27000 |
|  3 | L?¤?¤ne-Virumaa |    67000 |
+----+-----------------+----------+
2 rows in set (0.00 sec)



* Alampäring tabeli asendajana

mysql> SELECT COUNT(*) as kogus, maakonna_id FROM koolid GROUP BY maakonna_id;
+-------+-------------+
| kogus | maakonna_id |
+-------+-------------+
|     1 |        NULL |
|     3 |           1 |
|     2 |           2 |
|     1 |           4 |
+-------+-------------+
4 rows in set (0.00 sec)

mysql> SELECT MAX(kogus) FROM (SELECT COUNT(*) as kogus, maakonna_id FROM koolid GROUP BY maakonna_id) as tabel1;
+------------+
| MAX(kogus) |
+------------+
|          3 |
+------------+


___________


Rekursiivsed alampäringud

Sama tabel päringus kaks korda

Palun koolid, mille õpilaste arv ületab selle maakonna koolide keskmise õpilaste arvu.

SELECT nimetus, lastearv FROM koolid as k1 WHERE lastearv > 
 (SELECT AVG(lastearv) FROM koolid as k2 
    WHERE k1.maakonna_id=k2.maakonna_id);

mysql> SELECT nimetus, lastearv FROM koolid as k1 WHERE lastearv >   (SELECT AVG(lastearv) FROM koolid as k2      WHERE k1.maakonna_id=k2.maakonna_id);
+---------------------------------+----------+
| nimetus                         | lastearv |
+---------------------------------+----------+
| Haapsalu Wiedemanni G?¼mnaasium |      400 |
| Pelgulinna G?¼mnaasium          |      850 |
+---------------------------------+----------+



Harjutus

Loo tabel osakonnad (id, nimetus)
Loo tabel tootajad  (id, perekonnanimi, palk, osakonna_id)

CREATE TABLE osakonnad(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  nimetus VARCHAR(50)
);

INSERT INTO osakonnad (nimetus) VALUES ('kirurgia');
INSERT INTO osakonnad (nimetus) VALUES ('haldus');
INSERT INTO osakonnad (nimetus) VALUES ('stomatoloogia');
INSERT INTO osakonnad (nimetus) VALUES ('otorinolaryngoloogia');

CREATE TABLE tootajad(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  perekonnanimi VARCHAR(30), 
  palk INT,
  osakonna_id INT
);

INSERT INTO tootajad (perekonnanimi, palk, osakonna_id) VALUES ('Tamm', 2222, 1);
INSERT INTO tootajad (perekonnanimi, palk, osakonna_id) VALUES ('Kask', 1900, 1);
INSERT INTO tootajad (perekonnanimi, palk, osakonna_id) VALUES ('Kuusk', 2100, 1);

INSERT INTO tootajad (perekonnanimi, palk, osakonna_id) VALUES ('Lepp', 500, 2);
INSERT INTO tootajad (perekonnanimi, palk, osakonna_id) VALUES ('Haab', 530, 2);
INSERT INTO tootajad (perekonnanimi, palk, osakonna_id) VALUES ('Paju', 320, 2);
INSERT INTO tootajad (perekonnanimi, palk, osakonna_id) VALUES ('Jalakas', 1500, 2);


INSERT INTO tootajad (perekonnanimi, palk, osakonna_id) VALUES ('Kadakas', 1500, 3);

INSERT INTO tootajad (perekonnanimi, palk, osakonna_id) VALUES ('Karusmari', 1200, 4);

INSERT INTO tootajad (perekonnanimi, palk, osakonna_id) VALUES ('Toomingas', 900, 4);

INSERT INTO tootajad (perekonnanimi, palk, osakonna_id) VALUES ('Sirel', 1000, 4);



Lisa mõned andmed.

Loo päringud

* Mõelge/proovige töötajatega läbi sarnased päringud
  nagu tehti koolide õpilastega.

* Väljasta töötajad, kes saavad asutuse piires maksimumpalka.
* Väljasta töötajad, kes saavad osakonna piires maksimumpalka.
* Väljasta osakonnad, kus on tööl keskmisest rohkem inimesi.
* Väljasta töötajad osakondadest, kus on tööl keskmisest 
   rohkem inimesi.
* Näita eelmises päringus töötaja kõrval ka osakonna nimetust.

   

* Väljasta osakonnad, kus on tööl keskmisest rohkem inimesi.
   
 Töötajaid osakonna kohta keskmiselt:  
   
mysql> SELECT (SELECT COUNT(*) FROM tootajad) / (SELECT COUNT(*) FROM osakonnad);
+--------------------------------------------------------------------+
| (SELECT COUNT(*) FROM tootajad) / (SELECT COUNT(*) FROM osakonnad) |
+--------------------------------------------------------------------+
|                                                             2.7500 |
+--------------------------------------------------------------------+
1 row in set (0.01 sec)

Osakonna id-d ja kogused
mysql> SELECT osakonna_id, COUNT(*)  FROM tootajad GROUP BY osakonna_id;
+-------------+----------+
| osakonna_id | COUNT(*) |
+-------------+----------+
|           1 |        3 |
|           2 |        4 |
|           3 |        1 |
|           4 |        3 |
+-------------+----------+


 SELECT osakonna_id, COUNT(*) AS kogus  
    FROM tootajad GROUP BY osakonna_id 
	  HAVING kogus>
	   (SELECT (SELECT COUNT(*) FROM tootajad) / (SELECT COUNT(*) FROM osakonnad));  
   

+-------------+-------+
| osakonna_id | kogus |
+-------------+-------+
|           1 |     3 |
|           2 |     4 |
|           4 |     3 |
+-------------+-------+

* Osakondade nimetused, kus töötajate arv osakonnas ületab töötajate keskmist arvu osakonna kohta.

SELECT (SELECT nimetus FROM osakonnad WHERE osakonnad.id=t1.osakonna_id)
  as osakonnanimetus
   FROM
  (SELECT osakonna_id, COUNT(*) AS kogus  
    FROM tootajad GROUP BY osakonna_id 
	  HAVING kogus>
	   (SELECT (SELECT COUNT(*) FROM tootajad) / (SELECT COUNT(*) FROM osakonnad))) as t1;  


+----------------------+
| osakonnanimetus      |
+----------------------+
| kirurgia             |
| haldus               |
| otorinolaryngoloogia |
+----------------------+


   * Osakonna id osakondadel, kus on tööl keskmisest rohkem inimesi
 SELECT osakonna_id 
    FROM tootajad GROUP BY osakonna_id 
	  HAVING COUNT(*)>
	   (SELECT (SELECT COUNT(*) FROM tootajad) / (SELECT COUNT(*) FROM osakonnad));   


+-------------+
| osakonna_id |
+-------------+
|           1 |
|           2 |
|           4 |
+-------------+
	   
	
* Väljasta töötajad osakondadest, kus on tööl keskmisest 
   rohkem inimesi.

mysql>  SELECT perekonnanimi FROM tootajad WHERE osakonna_id IN (1, 2, 4);
+---------------+
| perekonnanimi |
+---------------+
| Tamm          |
| Kask          |
| Kuusk         |
| Lepp          |
| Haab          |
| Paju          |
| Jalakas       |
| Karusmari     |
| Toomingas     |
| Sirel         |
+---------------+

SELECT perekonnanimi FROM tootajad WHERE osakonna_id IN (
  SELECT osakonna_id 
    FROM tootajad GROUP BY osakonna_id 
	  HAVING COUNT(*)>
	   (SELECT (SELECT COUNT(*) FROM tootajad) / (SELECT COUNT(*) FROM osakonnad))
);


+---------------+
| perekonnanimi |
+---------------+
| Tamm          |
| Kask          |
| Kuusk         |
| Lepp          |
| Haab          |
| Paju          |
| Jalakas       |
| Karusmari     |
| Toomingas     |
| Sirel         |
+---------------+