Eszköztár

156 Kezdőlap - Webszerkesztés - MySQL - Reptér

Feladat

Ezúttal egy valódi adatbázissal lesz dolgunk - a világ repülőtereinek adatbázisával, benne 77.800 repülőtér és leszállóhely adataival.

Az adatbázis forrását az OurAirports.com kínálja, melynek adatait rendszeresen frissítik, karbantartják. Mi jelenleg a 2023. decemberi adatokkal fogunk dolgozni. Az oldal egyébként az adatokon túl térképen is izgalmasan szemlélteti a világ repülőtereit.

Az adatbázisból mi négy táblát fogunk használni, melyek az alábbi linkekről tölthetőek le SQL formátumban:

• airports.sql - 77.800 repülőtér adataival   (13MB)
• countries.sql - 248 ország nevével   (27kb)
• regions.sql - 3.932 régió megnevezésével   (0.5MB)
• runways.sql - 45.383 kifutópálya paramétereivel   (5MB)

Pontosítsunk egy picit! Az adatbázisban nem csupán nagy nemzetközi légikikötők, hanem pici, kisgépes, füves pályájú repülőterek, sőt helikopter leszállóhelyek is szerepelnek. Továbbá találhatóak benne olyan légibázisok is, melyek valaha ugyan valóban léteztek, ám már nem működnek, esetenként akár már a helyüket sem feltétlenül találnánk meg a helyszínen járva. Ezek a tulajdonságok azonban jelölve vannak a táblákban, tehát lesz lehetőségünk csak a létező helyekkel dolgoznunk, és azon belül is a sajátosságokra rákeresnünk, szűrnünk.
Hozzon létre egy mappát az adatfájlok letöltéséhez, és egyesével töltse le a fenti négy fájlt a mappába, ahonnan majd az impotálást elvégezheti! Letöltéshez kattintson a hivatkozásokra a jobb egérgombbal, és válassza a Link mentése másként..., vagy az ennek megfelelő funkciót!


Saját phpMyAdmin felületén hozzon létre egy XY_Repter nevű adatbázist, ahol XY az ön nevének monogramját jelöli, ékezetek nélkül. Azért szükséges az adatbázist saját névre létrehoznia, mert a későbbi feladatok között lesznek olyanok, ahol az adatbázis tartalmát is módosítania kell - így munkája nem fog "összeveszni" mások munkájával, akik még esetleg szintén az ön tanulói gépén dolgoznak. Egyesével importálja a létrehozott adat­bázisba az előzőleg lementett négy darab adattáblát. A sikeres importálást követően a mappába mentett adatfájlokat, illetve a mappát is törölheti, hiszen a folytatában már nem lesz rájuk szükség.

A phpMyAdmin felületen ismerkedjen meg az adattáblák szerkezetével, fedezze fel az azok között kialakítható kapcsolatokat, majd ezt követően SQL lekérdezésekkel adjon választ az alábbi kérdésekre. Egy-egy kérdés megválaszolása után a működő lekérdezést mentse el egy .txt szöveges fájlba a kérdésekhez tartozó sorszám feltüntetésével.



Kérdések, feladatok:

1. Hány darab különféle megnevezés létezik az airports tábla type oszlopában?

   SELECT COUNT(DISTINCT type)
   FROM airports

2. Milyen megnevezések fordulnak elő az airports tábla type mezőjében? Mindegyik megnevezést csak egyszer jelenítse meg!
3. Hány nagy repülőtér (large_airport) szerepel az airports tábla type mezőiben?
4. Összesen hány kis, közepes, vagy nagy repülőtér (small_airport, medium_airport, large_airport) szerepel az airports tábla type mezőiben?

   A lekérdezés feltételében használjuk ki, hogy mindhárom leszállóhely-típus - és csak ezek - megnevezése úgy végződik, hogy airport.

5. Hány földrészen, hány országban, összesen hány helikopter leszállóhely (heliport) szerepel az adat­bázisban?
   
6. Hány darab bejegyzés szerepel az egyes típus-jelölésekből az airports tábla type oszlopában?

   A folytatásban szereplő kérdésekben csak a valóban repülőtereket (large_airport, medium_airport, small_airport), nevezzük repülőtérnek. Ennek megfelelően az egyéb típusú leszállóhelyeket (heliport, balloonport, seaplane_base) hagyja figyelmen kívül a repülőterekre vonatkozó kérdések esetén!

7. Mi a neve a világ legészakabbra fekvő repülőterének? Mely szélességi foknál található?
8. Milyen jelölésekkel nevesíti a különböző kontinenseket az airports tábla continent mezője? Vajon mely földrészekre utalnak a jelölések?
9. Melyik városban található Európa legészakibb repülőtere?
10. Hány repülőtér van az Antarktiszon?
11. Hány repülőtér van az egyes földrészeken?
12. Az airports tábla iso_country oszlopa alapján melyik az a 15 ország, ahol a legtöbb repülőtér található?
    Vajon mely országokat jelentik az egyes betűpárok?
13. A countries tábla szerint mely országokat jelölik az előző lekérdezés eredményében megjelenő CO, PG, illetve ZA betűpárok?
    
14. Hogyan nevezi a countries tábla az antarktiszi országot?
15. Hány különböző ország megnevezése szerepel a countries táblában?
16. Hány különböző ország leszállóhely-adatai szerepelnek az airports táblában? A korábbi, már bezárt leszállóhelyeket is vegye figyelembe ennél a kérdésnél!
17. Melyek azok az országok, amelyek bár szerepelnek a countries táblában (lásd 15. kérdés), ám leszállóhely-adat nem tartozik hozzájuk az airports táblában (lásd 16. kérdés)?

    SELECT * FROM countries
    WHERE code NOT IN (SELECT iso_country FROM airports)

    
18. Melyek azok az országok, amelyek légi úton csak helikopterrel közelíthetőek meg?

    SELECT code, name, continent FROM countries
    WHERE code IN
       (SELECT iso_country FROM airports
        WHERE iso_country NOT IN
           (SELECT iso_country FROM airports
            WHERE type!='heliport' AND type!='closed'))

    
    

    Olyan országokat keresünk tehát, amelyekhez kizárólag heliport leszállóhely található az airports táblában, ám sem működő reptér, sem működő balloonport nem létezik jelenleg. Ha korábban bezárt repterek most már nem számítanak leszállóhelynek.

19. Mely kontinens részének tekinti a countries tábla Oroszországot, illetve Törökországot?
    
20. Hány leszállóhelyet tartalmaz az airports tábla Oroszország, illetve Törökország esetén Európában, valamint Ázsában?

    SELECT iso_country,
           COUNT( IF( continent='EU', 1, null ) ) AS 'Europe',
           COUNT( IF( continent='AS', 1, null ) ) AS 'Asia'
    FROM airports
    WHERE iso_country='RU' OR iso_country='TR'
    GROUP BY iso_country
    ORDER BY 1

    
    

    A 19. kérdés eredményétől függetlenül nem volna helyes, ha Oroszország, illetve Törökország minden leszállóhelyét ugyanarra a kontinensre helyezné az adatbázis, mint magát az országot, hiszen mindkét országnak van európai és ázsiai területe is.

    Hol van Európa és Ázsia határa?

    
    

    A kérdésre azért nehéz a válasz, mert az biztosan szubjektív lesz. Bizonyíték erre például a rengeteg különféle térkép, ami felfedezhető az interneten. Az azonban megfigyelhető, hogy ez a határ Oroszország területén nagyjából minden változatban egy többé-kevésbé függőleges vonal, vagyis leginkább arról lehet vitatkozni, hogy hol is helyezzük el ezt a vonalat. Ha például a 4. legnépesebb város, Jekatyerinburg nyugati szélén található Európa-Ázsia határát jelző szoborra esik a választásunk, akkor a határ a keleti hosszúság 60.35°-nák húzódhat.
    
21. Léteznek-e olyan leszállóhelyek Oroszországban, amelyek hibásan európai helyszínként vannak megjelölve az airports táblában, miközben valójában Ázsiában találhatóak? Ha léteznek ilyenek, listázza ki őket kelet felé haladva!

    SELECT ... FROM ... WHERE ...
    ORDER BY SIGN(longitude_deg) DESC, longitude_deg

    
    

    Némi egyszerűsítéssel tekintsük Oroszország egyértelműen ázsiai területének a keleti hosszúság 60.35°-tól keletre eső részét! Ám az SQL kérés megfogalmazásánál legyen körültekintő, ugyanis keleti területeknek számítanak azok a legmesszebb fekvő távol-keleti tájak is, amelyek földrajzi koordinátáit már nyugati hosszúsággal (az adatbázisban negatív számokkal) azonosítjuk!

22. Amennyiben az előző kérdés során fedezett fel hibákat az adatbázisban, akkor módosítsa az airports táblát aszerint, hogy az Oroszország ázsiai területén található leszállóhelyek valóban ázsiai helyszínként legyenek megjelölve, ne pedig európaiként.

    UPDATE airports
    SET continent = 'AS'
    WHERE iso_country='RU' AND continent='EU'
    AND (longitude_deg>60.35 OR longitude_deg<0)

    VIGYÁZZON! A módosító utasítás helytelen megírása olyan adatokat is átírhat a táblázatban, amiket nem szeretnénk megváltoztatni. A MŰVELET NEM VISSZAVONHATÓ! Körültekintően dolgozzon!

    
23. Hol, és milyen tengerszint feletti (tszf.) magasságon találhatjuk meg a világ legmagasabban elhelyezkedő leszállóhelyét? A magasságot az airport táblában szereplő elevation_ft mező alapján láb mérték­egységben is, és méterben is adja meg!

    SELECT name, elevation_ft, ROUND(elevation_ft*0.3048) AS elevation_m
    FROM airports
    ORDER BY elevation_ft DESC
    LIMIT 0, 1

    
    

    1 láb = 0.3048 méter.

24. Melyik városban, hány méteres tszf. magasságon található a világ legmagasabban fekvő nemzetközi repülőtere?

    Nemzetközi repülőterek azok a repterek, amelyek esetén az airports tábla name mezőjében szerepel az International szó.

25. Hozzon létre az airports táblában egy új oszlopot az elevation_ft oszlop után, elevation_m néven, INT típussal. Ezt követően az UPDATE utasítás segítségével töltse fel az új oszlopot az elevation_ft értékeinek felhasználásával!

    UPDATE airports
    SET elevation_m = ROUND(elevation_ft*0.3048)

    Tesszük mindezt azért, hogy ebben az oszlopban az egyes leszállóhelyek tengerszint feletti magassága méterben is szerepelhessen a táblázatban, a további kérdések megválaszolásának megkönnyítése érdekében.

26. Az előző pontban létrehozott elevation_m mező adatai alapján adja meg, hogy mely országok rendelkeznek leszállóhellyel a tengerszint felett 4.000 méternél magasabban! Az országok nevét a countries táblából olvassa ki, vagyis a lekérdezés során használja mind a két szükséges táblát! Minden ország neve mellett tüntesse fel a földrész betűjelét is, ahol az adott ország található!

    SELECT name, continent
    FROM countries
    WHERE code IN
       (SELECT iso_country FROM airports
        WHERE elevation_m > 4000 )
    ORDER BY 2, 1

    
    
27. Melyik országban, illetve városban található Európa legmagasabban fekvő nemzetközi repülőtere? Milyen tszf. magasságon épült?

    SELECT ... , ... , ... , ...
    FROM airports, countries
    WHERE airports.iso_country = countries.code
    AND ...
    AND ...
    ORDER BY ... LIMI 0, 1

    
    

    A folytatásban a kifutópályák adatait tartalmazó runways táblával fogunk dolgozni. Ez a tábla az airport_ref mezőjén keresztül kapcsolódik az airports táblához, annak elsődleges kulcsát használva. Szükség esetén ennek megfelelően kapcsolhatja össze a két táblát a következő lekérdezésekben.

28. A runways tábla a kifutópályák hosszát és szélességét a length_ft és a width_ft mezőkben tartja nyilván, lábbban mérve. Ám itt is azt szeretnénk, ha az adatok méterben is meglennének. Éppen ezért hozzon létre két új oszlopot a táblában (pl. a width_ft mező után) length_m és a width_m néven, INT típussal, és oldja meg, hogy az új oszlopokban szerepeljenek a kívánt értékek méterben, egész számra kerekítve!
29. Hány méter hosszú a leghosszabb, kivilágítással is rendelkező felszállópálya?
30. Melyik városban található az előző pontban beazonosított felszállópálya?
31. Melyik országban található az előző pontban beazonosított város?

    SELECT ... ... ...
    FROM runways, airports, countries
    WHERE runways.airport_ref=airports.id
    AND airports.iso_country=countries.code
    AND lighted=1
    ORDER BY ...
    LIMIT 0, 1

32. Az airports tábla repülőtereinek hány százalékához tartozik felszállópálya-adatsor a runways táblában?

    SELECT CONCAT( ROUND(
        (SELECT COUNT(DISTINCT airport_ref) FROM runways)
      / (SELECT COUNT(id) FROM airports)
    * 100 , 1) , '%' ) AS szazalek

    Mivel kevesebb sor van a runways táblában, mint az airports táblában, biztosak lehetünk benne, hogy nem minden reptérhez tartozik kifutópálya adat is az adatbázisban. Ám abban már nem lehetünk biztosak, hogy minden reptérhez legfeljebb egy kifutópálya tartozik.

33. Melyik reptérnek van a legtöbb felszállópályája a világon? Hány darab tartozik hozzá?
34. Mely reptereknek van a legtöbb felszállópályája Európában? Hány darab tartozik hozzájuk?

    CREATE TEMPORARY TABLE eu_biggest
      SELECT name, COUNT(*) AS db
      FROM airports, runways
      WHERE airports.id=runways.airport_ref
      AND continent='EU'
      AND type!='closed'
      GROUP BY airports.id ;
    
    SELECT * FROM eu_biggest
    WHERE db = (SELECT MAX(db) FROM eu_biggest)

35. Milyen burkolattípus-jelölések léteznek a runways tábla surface oszlopában? Jelenítse meg mindegyiknél az előfordulások számát is! Kezdje a leggyakoribb előfordulásokkal!

    A lekérdezés eredményében megjelenő rövidítések könnyebb értelmezéhez szükséges lehet rájuk keresni a neten. A találatok között segíthet pl. ez a link: Runway surface types

36. Vezessünk be sokkal kevesebb, és egységesebb burkolattípus megnevezéseket, mint amikkel az előző kérdésben találkozhattunk! Ehhez hozzon létre a surface oszlop után egy új oszlopot, surface_type néven. Az új oszlopot az alábbi elnevezésekkel töltse fel:

    solid	szilárd burkolatként jelöljük meg az aszfalt, ill. a beton burkolatú leszállóhelyeket, vagyis azokat, ahol a surface oszlopban szereplő kifejezésnek része az ASP, a BIT, a CON, vagy a PEM karaktersorozat.
    grass	tehát füves megjelölést használjunk ott, ahol a surface oszlopnak része a GRAS, GRE, GRS, TURF karaktersorozatok valamelyike.
    unknown	ezt a jelölést használjuk ott, ahol a surface mező kitöltetlen, vagy szerepel benne az UNK karaktersorozat.
    other	legyen ez a surface_type tartalma minden más jelölés esetén!

    A feladatot egy lépésben nem, csak négy önálló UPDATE utasítással tudja elvégezni.

37. Az előző pontban létrehozott burkolattípusoknak hány százaléka szilárd, illetve hány százaláka füves jelölésű?
38. Melyik az a nemzetközi repülőtér Európában, amelyik nem rendelkezik sziárd burkolatú felszállópályával?

    A következő három kérdés során kifejezetten helikopter leszállóhelyekkel foglalkozunk. Tekintsük most helikopter leszállóhelynek azokat a sorokat a runways táblából, ahol a length_ft és a width_ft mezők értéke megegyezik, és értelemszerűen nagyobb nullánál.

    
39. Mekkora a három legtipikusabb, vagyis leggyakrabban előforduló helikopter leszállóhely mérete?
40. Melyek azok az annyira tipikus helikopter leszállóhely méretek, amelyekből legalább 200 előfordulás szerepel az adatbázisban?
41. Mekkora a legnagyobb füves helikopter leszállóhely? Mi a neve, melyik országban, illetve városban található?

---

Eddig 1 hozzászólás van a témához:

---

Új hozzászólás:
E-mail cím:	Erre a címre küldjük ki a hozzászólás jóvá- hagyásához szükséges linket. Az e-mail címet sehol nem tesszük közzé.
Név:	Ez a név fog megjelenni az Ön hozzászólásai mellett.
Mennyi tizenhét + öt? Számjegyekkel írja be!	Ez a robotok beírása elleni védelem miatt szükséges ellenőrzés.

© infojegyzet.hu, 2024. március