Magyarország városai feladat

A következő feladatban Magyarország városainak (2013. július 15-i állapot) adatait kell feldolgoznia. Az adatbázis a következő táblákat tartalmazza:

Az elsődleges kulcsokat PK-val, az idegenkulcsokat FK-val jelöltük. Az adattáblák közti kapcsolatot az alábbi ábra mutatja:

A következő feladatokat megoldó SQL parancsokat rögzítse a megoldasok.sql állományban a feladatok végén zárójelben jelölt sor alá! A javítás során csak ennek az állománynak a tartalmát értékelik.

Ügyeljen arra, hogy a lekérdezésekben pontosan a kívánt mezők szerepeljenek, felesleges mezőt ne jelenítsen meg!

1. Hozzon létre a lokális SQL szerveren varosok néven adatbázist! Az adatbázis alapértelmezett rendezési sorrendje a magyar szabályok szerinti legyen! Ha az Ön által választott SQL szervernél nem alapértelmezés az UTF-8 kódolás, akkor azt is állítsa be alapértelmezettnek az adatbázis létrehozásánál! (1. feladat:)

CREATE DATABASE varosok CHARACTER SET utf8 COLLATE utf8_hungarian_ci;

2. A tablak.sql és az adatok.sql állományok tartalmazzák a táblákat létrehozó, valamint az adatokat a táblába beszúró SQL parancsokat! Futtassa a lokális SQL szerveren lévő varosok adatbázisban elsőként a tablak.sql, majd az adatok.sql parancsfájlt!

3. Listázza ki azokat a városokat, amelyek nevében bárhol szerepel a „vásár” szórészlet! (3. feladat:)

SELECT varos.vnev FROM varos WHERE varos.vnev LIKE "%vásár%"

4. Készítsen lekérdezést, amellyel kilistázza a 400 km2-nél nagyobb városok nevét, népességét és területét! A lista a népesség száma szerint csökkenő rendben jelenjen meg! (4. feladat:)

SELECT varos.vnev,varos.nepesseg,varos.terulet FROM varos WHERE varos.terulet>400 ORDER BY varos.nepesseg DESC

5. Listázza ki Fejér megye 15 000 főnél népesebb városait! (5. feladat:)

SELECT varos.vnev,varos.nepesseg FROM varos INNER JOIN megye ON megye.id=varos.megyeid WHERE megye.mnev LIKE "%fejér%" AND varos.nepesseg>15000

6. Készítsen lekérdezést, amely kilistázza várostípusonként a városok számát és az adott várostípusban élők számát! A várostípusok között ne szerepeljen a „Főváros” várostípus! Az első mező címkéje „Város típusa” legyen, a számított mezők címkéje pedig „Városok száma” és „Népesség” legyen! (6. feladat:)

SELECT varostipus.vtip AS "Város típusa",COUNT(varos.id) AS "Városok száma",SUM(varos.nepesseg) AS Népesség FROM varos INNER JOIN varostipus ON varostipus.id=varos.vtipid WHERE varostipus.vtip<>"főváros" GROUP BY varostipus.vtip

7. Lekérdezéssel határozza meg megyénként azon városok számát, amelyek esetében a kistérség neve nem egyezik meg a járás nevével! A listában csak azok a megyék jelenjenek meg, amelyekben a városok száma nagyobb mint nyolc! A lekérdezésben a minta szerint a megyék neve és a városok száma jelenjen meg a városok száma szerint csökkenő sorrendben! A számított mező címkéje „db” legyen! (7. feladat:)

SELECT megye.mnev,COUNT(varos.vnev) AS db FROM varos INNER JOIN megye ON megye.id=varos.megyeid WHERE varos.jaras<>varos.kisterseg GROUP BY megye.mnev HAVING db>8 ORDER BY db DESC