Harasztos Barnabás lapja

10. hét: Al- és segédlekérdezések ('tisztek' adatbázis)

Egy másik adatbázisban folytatjuk a tanulást. Új adatbázisunk egy fiktív laktanya rendfokozattal rendelkezõ katonáinak adatait tartalmazza (még a régi idõkbõl).

0. feladat: Töltse le a home könyvtárába a tisztek.sql fáljt! (Jobb egér a linken/hivatkozás mentése másként...)

Indítson egy terminált, majd mportálja a tisztek adatbázist a mysql-be!
A parancs:

barnabas@sir-kan:$ mysqldump -u erettsegi -p < tisztek.sql

Lépjen be az adatbáziskezelõbe! (mysql -u erettsegi -p, majd a jelszó)

Nyissa meg az tisztek adatbázist!

mysql>use tisztek;
Blablabla...

Database changed
mysql>

Az adatbázisban két tábla van, nevük: tisztek és rang.

A tisztek tábla egy részlete (ön is lekérdezheti: select * from tisztek limit 5;):

Minden sor 8 adatot tartalmaz:

Nev: a katona személy neve (típusa: szöveg);
Anyja: a katona anyjának neve (típusa: szöveg);
Lakohely: a település neve, ahol a katona lakik (típusa: szöveg);
Rendfokozat: az illetõ rendfokozata (típusa: szöveg);
Kategória: a rendfokozathoz tartozó kategória (típusa: szöveg);
Beosztas: a katona szolgálati beosztása (típusa: szöveg);
Fizetés: az illetõ havi fizetése (típusa: egész szám);
Születés: a katona születésének éve (típusa: egész szám).

A rang tábla egy részlete (ön is lekérdezheti: select * from rang;):

+--------------------+---------+
| rendfokozat        | sorszam |
+--------------------+---------+
| tizedes            |       1 |
| szakaszvezetõ      |       2 |
| õrmester           |       3 |
| törzsõrmester      |       4 |
| fõtörzsõrmester    |       5 |
| ...                |     ... |

Minden sor 2 adatot tartalmaz:

rendfokozat: az adott rendfokozat megnevezése (típusa: szöveg);
sorszam: az adott rendfokozat sorszáma: minél nagyobb, annál magasabb az adott rendfokozat (típusa: egész szám).

Feladatok

A fehér hátterû dobozban lévõ feladatokat itt megoldjuk, a szürke hátterûek házi feladatok, amiket a feladat végén megadott, sql bõvítményû fájlban mentsen el, majd ezeket a fájlokat küldje el nekem.

1. feladat: Készítsen lekérdezést, amely kiírja hogy a "tábornok"-ok hány %-át teszik ki a teljes létszámnak!

A parancs: select CONCAT(100*(select count(*) from tisztek where Kategoria="tábornok")/(select count(*) from tisztek),"%") as tabornokok_aránya;

2. házi feladat: Készítsen lekérdezést, amely kiírja hogy mennyi "tábornok"-ok és a "tiszt"-ek számának aránya. (Várhatóan egy 1-nél kisebb szám lesz az eredmény.) (2tabornok-tiszt)

3.1. feladat: Ki a "Budakalász"-on lakó katonák közt a legmagasabb rendfokozatú? Adja meg a katona nevét, rendfokozatát és a beosztását.

A parancs: select Nev, tisztek.Rendfokozat, Beosztas from tisztek, rang where tisztek.Rendfokozat=rang.rendfokozat and Lakohely="Budakalász" and sorszam=(select MAX(rang.sorszam) from tisztek, rang where tisztek.Rendfokozat=rang.rendfokozat and Lakohely="Budakalász");

3.2 feladat: Településenként adja meg, hogy ki az adott településen lakó katonák közt a legmagasabb rendfokozatú (városparancsnok). Jelenjen meg a település neve, a katona neve, rendfokozata és beosztása.

A parancs: select tisztek.Lakohely, Nev, tisztek.Rendfokozat, Beosztas from tisztek, rang, (select Lakohely, MAX(rang.sorszam) as ssz from tisztek, rang where tisztek.Rendfokozat=rang.rendfokozat group by Lakohely) as r where tisztek.Rendfokozat=rang.rendfokozat and tisztek.Lakohely=r.Lakohely and sorszam=ssz order by tisztek.Lakohely;

4.1. házi feladat: Ki a legfiatalabb a "Budakalász"-on lakó katonák közt? Adja meg a katona nevét, rendfokozatát, és azt, hogy 2000-ben mennyi idõs volt. (Ez utóbbi "kor" fejléccel jelenjen meg.) (41Budakalasz)

4.2. házi feladat: Telepólésenként adja meg, hogy ki ott a legfiatalabb katona. Jelenjen meg a település neve, a katona neve, rednfokozata, beosztása és az, hogy a katona mennyi idõs volt 2000-ben. (Ez utóbbi "kor" fejléccel.) (42legfiatalabbak)

5.1. feladat: Milyen rendfokozatok fordulnak elõ a "Kóspallag"-on lakók közt? (Minden ilyen rendfokozat csak egyszer jelenjen meg a listában.)

A parancs: select distinct Rendfokozat from tisztek where Lakohely="Kóspallag";

5.2 feladat: Melyek azok a rendfokozatok, amik "Kóspallag"-on és "Pomáz"-on is elõfordulnak?

Megoldás: select distinct Rendfokozat from tisztek where Lakohely="Kóspallag" and Rendfokozat in (select distinct Rendfokozat from tisztek where Lakohely="Pomáz");

6.1. házi feladat: Mely beosztások fordulnak elõ a "Szob"-on lakó katonák közt? Minden beosztás csak egyszer jelenjen meg. (61Szob)

6.2. házi feladat: Melyek azok a beosztások, amelyek a "Szob"-on és a "Tahi"-n lakó katonák közt is elõfordulnak? Minden beosztás csak egyszer jelenjen meg. (62beosztasok)

7. feladat

A) Kinek hány testvére van az adatbázisban szereplõ katonák közt? A listában csak azok a katonák jelenjenek meg, akiknek legalább egy testvére van az adatbázisban. Adja meg a katona nevét, rendfokozatát és testvérinek számát ("testvérszám" fejléccel). Testvérnek tekintünk két katonát, ha ugyanaz az anyjuk neve.

A parancs: select Nev, Rendfokozat, tsz-1 as testverek from tisztek, (select Anyja, COUNT(*) as tsz from tisztek group by Anyja having tsz>1) as anyak where tisztek.Anyja=anyak.Anyja;

B) Kik azok, akiknek a legtöbb testvére van az adatbázisban? Jelenjenek meg: a katonák neve, rendfokozata és testvérei száma.

A parancs: select Nev, Rendfokozat, (select COUNT(*) from tisztek group by Anyja order by COUNT(*) desc limit 1)-1 as testverek from tisztek where Anyja in (select Anyja from tisztek group by Anyja having COUNT(*)=(select COUNT(*) from tisztek group by Anyja order by COUNT(*) desc limit 1));

8. házi feladat: Kik azok a katonák, akiknek van a laktanyában "vezérezredes" rendfokozatú testvére? (8protekciosok)

Blabla

Jó munkát!