Balogh Gábor
Dózsa SZKI Kalocsa
SQL feladatok és megoldások 1. Logikai feltételek: ÉS feltétel (AND): Feladat:
Listázd ki a 10 és 11 millió fő közé eső lakosságú országokat!
QBE megoldás: (a különböző oszlopokba írt feltételek egymással „és” kapcsolatban vannak)
SQL megoldás Access által generálva: SELECT ORSZAG.ORSZAGNEV, ORSZAG.OLELEKSZAM FROM ORSZAG WHERE (((ORSZAG.OLELEKSZAM)>10000) AND ((ORSZAG.OLELEKSZAM)<11000)); SQL megoldás egyszerűbben: (a felesleges táblaazonosítók és zárójelek nélkül) SELECT ORSZAGNEV, OLELEKSZAM FROM ORSZAG WHERE (OLELEKSZAM > 10000) AND (OLELEKSZAM < 11000); Az eredmény: ORSZAGNEV OLELEKSZAM Belorusszia 10250 Ecuador 10262 Görögország 10041 Jugoszlávia 10411 Kamerun 10817 Kuba 10450 Magyarország 10567 Portugália 10460 Zimbabwe 10119
Egy másik egyenértékű QBE megoldás:
1
Balogh Gábor
Dózsa SZKI Kalocsa
ÉS feltétel megvalósítása Between, And operátorok segítségével: QBE megoldás:
SQL megoldás Access által generálva: SELECT ORSZAG.ORSZAGNEV, ORSZAG.OLELEKSZAM FROM ORSZAG WHERE (((ORSZAG.OLELEKSZAM) Between 10000 And 11000)); Vagy feltétel (OR): Feladat:
Listázd ki a magyar és osztrák városokat!
Vigyázat! A feladat szövegében az „és” csak egy kötőszó! Az eredménytábla soraira nézve ez egy „vagy” feltétel! A magyar vagy osztrák városokat szeretném megjelenítetni, olyan város nem is létezik, amely egyszerre (ez lenne az „és”) mindkét országhoz tartozik. QBE megoldás: (a különböző sorokba írt feltételek egymással „vagy” kapcsolatban vannak)
SQL megoldás Access által generálva: SELECT VAROS.VAROSNEV, VAROS.ORSZAGNEV FROM VAROS WHERE (((VAROS.ORSZAGNEV)="Magyarország")) OR (((VAROS.ORSZAGNEV)="Ausztria")); Az eredmény: VAROSNEV Bécs Budapest Debrecen Graz Győr Innsbruck Kecskemét
ORSZAGNEV Ausztria Magyarország Magyarország Ausztria Magyarország Ausztria Magyarország
VAROSNEV Klagenfurt Linz Miskolc Nyíregyháza Pécs Salzburg Sant Pölten
2
ORSZAGNEV Ausztria Ausztria Magyarország Magyarország Magyarország Ausztria Ausztria
VAROSNEV Steyr Szeged Székesfehérvár Szolnok Szombathely Villach Wels
ORSZAGNEV Ausztria Magyarország Magyarország Magyarország Magyarország Ausztria Ausztria
Balogh Gábor
Dózsa SZKI Kalocsa
Egy másik egyenértékű QBE megoldás:
VAGY feltétel megvalósítása IN operátor segítségével: QBE megoldás:
SQL megoldás Access által generálva: SELECT VAROS.VAROSNEV, VAROS.ORSZAGNEV FROM VAROS WHERE (((VAROS.ORSZAGNEV) In ("Magyarország","Ausztria"))); Tagadás (NOT): Feladat:
Listázd ki a nem magyar városokat!
QBE megoldás:
SQL megoldás Access által generálva: SELECT VAROS.VAROSNEV, VAROS.ORSZAGNEV FROM VAROS WHERE ((Not (VAROS.ORSZAGNEV)="Magyarország"));
3
Balogh Gábor
Dózsa SZKI Kalocsa
Logikai műveletek sorrendje: Feladat:
Listázd ki a kétszázezer lakosnál népesebb magyar és osztrák városokat!
QBE megoldás:
SQL megoldás Access által generálva: SELECT VAROS.VAROSNEV, VAROS.ORSZAGNEV, VAROS.VLELEKSZAM FROM VAROS WHERE (((VAROS.ORSZAGNEV)="Magyarország") AND ((VAROS.VLELEKSZAM)>200)) OR (((VAROS.ORSZAGNEV)="Ausztria") AND ((VAROS.VLELEKSZAM)>200)); Az eredmény: VAROSNEV Bécs: Budapest Debrecen Graz Miskolc
ORSZAGNEV VLELEKSZAM Ausztria 1531 Magyarország 2115 Magyarország 220 Ausztria 243 Magyarország 208
Egy másik egyenértékű QBE megoldás:
SQL megoldás Access által generálva: SELECT VAROS.VAROSNEV, VAROS.ORSZAGNEV, VAROS.VLELEKSZAM FROM VAROS WHERE ( ((VAROS.ORSZAGNEV)="Magyarország" Or (VAROS.ORSZAGNEV)="Ausztria") AND ((VAROS.VLELEKSZAM)>200) );
4
Balogh Gábor
Dózsa SZKI Kalocsa
2. Igen/nem (logikai) típusú változók használata: Feladat:
Listázd ki a XX. században születet nem barbár gumimacikat!
QBE megoldás: (igaz/hamis)
SQL megoldás Access által generálva: (true/false) SELECT Gumimaci.maci_nev, Gumimaci.szül_idő, Gumimaci.barbar_e FROM Gumimaci WHERE (((Gumimaci.szül_idő) Between #1/1/1900# And #12/31/1999#) AND ((Gumimaci.barbar_e)=False)); Egy másik egyenértékű SQL megoldás: SELECT Gumimaci.maci_nev, Gumimaci.szül_idő, Gumimaci.barbar_e FROM Gumimaci WHERE((Gumimaci.szül_idő) Between #1/1/1900# And #12/31/1999#) AND (Not(Gumimaci.barbar_e)); Feladat:
Listázd ki a nőnemű barbár gumimacikat!
(A nőnemű macik kódjának első karaktere 1 értékű) QBE megoldás: (igaz/hamis)
SQL megoldás Access által generálva: (true/false) SELECT Gumimaci.maci_nev, Gumimaci.maci_kod, Gumimaci.barbar_e FROM Gumimaci WHERE ((Left([maci_kod],1)="1") AND ((Gumimaci.barbar_e)=True)); Egy másik egyenértékű SQL megoldás: SELECT Gumimaci.maci_nev, Gumimaci.maci_kod, Gumimaci.barbar_e FROM Gumimaci WHERE ((Left([maci_kod],1)="1") AND Gumimaci.barbar_e);
5
Balogh Gábor
Dózsa SZKI Kalocsa
3. Rendezés Feladat:
Rendezd a fővárosokat növekvő ill. csökkenő sorrendbe!
QBE megoldás:
SQL megoldás Access által generálva: SELECT ORSZAG.FOVAROSNEV FROM ORSZAG ORDER BY ORSZAG.FOVAROSNEV;
Feladat:
SELECT ORSZAG.FOVAROSNEV FROM ORSZAG ORDER BY ORSZAG.FOVAROSNEV DESC;
Listázd ki a városokat országok szerint növekvő és azon belül lakosság szerint csökkenő sorrendbe!
QBE megoldás:
SQL megoldás Access által generálva: SELECT VAROS.VAROSNEV, VAROS.ORSZAGNEV, VAROS.VLELEKSZAM FROM VAROS ORDER BY VAROS.ORSZAGNEV, VAROS.VLELEKSZAM DESC;
6
Balogh Gábor
Dózsa SZKI Kalocsa
Az eredmény: VAROSNEV
ORSZAGNEV VLELEKSZAM
Kabul
Afganisztán
1424
Kabul
VAROSNEV VLELEKSZAM ORSZAGNEV
Qandahar
Afganisztán
226
Qandahar
226 Afganisztán
Herat
Afganisztán
177
Herat
177 Afganisztán
Mazar-i-Sharif Afganisztán
131
Mazar-i-Sharif
131 Afganisztán
1424 Afganisztán
Jalalabad
Afganisztán
58
Jalalabad
58 Afganisztán
Qonduz
Afganisztán
57
Qonduz
57 Afganisztán
Baghlan
Afganisztán
41
Baghlan
41 Afganisztán
Magmana
Afganisztán
40
Magmana
40 Afganisztán
Pul-i-Khomri
Afganisztán
33
Pul-i-Khomri
33 Afganisztán
Ghazni
Afganisztán
32
Ghazni
Tirana
Albánia
226
Tirana
226 Albánia
Durres
Albánia
79
Durres
79 Albánia
Elbasan
Albánia
78
Elbasan
78 Albánia
Shkoder
Albánia
76
Shkoder
76 Albánia
Vlore
Albánia
68
Vlore
68 Albánia
Korce
Albánia
62
Korce
62 Albánia
Berat
Albánia
41
Berat
41 Albánia
Feladat:
32 Afganisztán
Oldjuk meg az előző feladatot úgy, hogy az országok az utolsó oszlopban legyenek!
QBE megoldás:
SQL megoldás Access által generálva: SELECT VAROS.VAROSNEV, VAROS.VLELEKSZAM, VAROS.ORSZAGNEV FROM VAROS ORDER BY VAROS.ORSZAGNEV, VAROS.VLELEKSZAM DESC; Egy másik egyenértékű QBE megoldás:
7
Balogh Gábor
Dózsa SZKI Kalocsa
4. Összegzés Az adatbázisban hány (darab) város tartozik az egyes országokhoz?
Feladat: QBE megoldás:
SQL megoldás Access által generálva: SELECT VAROS.ORSZAGNEV, Count(VAROS.VAROSNEV) AS CountOfVAROSNEV FROM VAROS GROUP BY VAROS.ORSZAGNEV; Az eredmény: ORSZAGNEV CountOfVAROSNEV Afganisztán 10 Albánia 9 Algéria 13 Amerikai Szamoa 5 Amerikai Virgin-szigetek 4 Andorra 2 Angola 7 Antigua 2 Arab Emírségek 4 Argentína 11
Mekkora a Föld lakossága, hány ország van rajta, mekkora legnagyobb, a legkisebb és az átlagos országlélekszám?
Feladat: QBE megoldás:
SQL megoldás Access által generálva: SELECT Sum(ORSZAG.OLELEKSZAM) AS SumOfOLELEKSZAM, Count(ORSZAG.OLELEKSZAM) AS CountOfOLELEKSZAM, Min(ORSZAG.OLELEKSZAM) AS MinOfOLELEKSZAM, Max(ORSZAG.OLELEKSZAM) AS MaxOfOLELEKSZAM, Avg(ORSZAG.OLELEKSZAM) AS AvgOfOLELEKSZAM FROM ORSZAG; Az eredmény: SumOfOLELEKSZAM CountOfOLELEKSZAM MinOfOLELEKSZAM MaxOfOLELEKSZAM AvgOfOLELEKSZAM 5251951
206
2
8
1112299
25494,9077669903
Balogh Gábor
Dózsa SZKI Kalocsa
QBE megoldás olvashatóbb mezőnevekkel az eredménytáblában:
SQL megoldás Access által generálva: SELECT Sum(ORSZAG.OLELEKSZAM) AS [Föld lakossága], Count(ORSZAG.OLELEKSZAM) AS [Országok száma], Min(ORSZAG.OLELEKSZAM) AS [Legkisebb népesség], Max(ORSZAG.OLELEKSZAM) AS [Legnagyobb népesség], Avg(ORSZAG.OLELEKSZAM) AS [Átlagos népesség] FROM ORSZAG; Az eredmény: Föld lakossága Országok száma Legkisebb népesség Legnagyobb népesség Átlagos népesség 5251951
Feladat:
206
2
1112299 25494,9077669903
Mekkora az átlagos városi lakosságszáma azoknak az országoknak, melyeknek a neve „m” vagy „s” betűvel kezdődik, és 10-nél több városuk van?
QBE megoldás:
SQL megoldás Access által generálva: SELECT VAROS.ORSZAGNEV AS Országnév, Count(VAROS.VAROSNEV) AS Városszám, Avg(VAROS.VLELEKSZAM) AS [Átlagos városi lélekszám] FROM VAROS GROUP BY VAROS.ORSZAGNEV HAVING (((VAROS.ORSZAGNEV) Like "m*") AND ((Count(VAROS.VAROSNEV))>10)) OR (((VAROS.ORSZAGNEV) Like "s*") AND ((Count(VAROS.VAROSNEV))>10)) ORDER BY Avg(VAROS.VLELEKSZAM) DESC; Az eredmény: Országnév Mexikó Marokkó Spanyolország Magyarország Malaysia Svédország Svájc
Városszám Átlagos városi lélekszám 15 1158,47 11 1071,18 19 566,95 11 323,64 13 217,69 15 175,53 11 117,73
9
Balogh Gábor
Dózsa SZKI Kalocsa 5. Számított mező Mekkora lesz az Indiai városok lakossága 10 év múlva, ha kb. 15% növekedéssel számolhatunk!
Feladat: QBE megoldás:
SQL megoldás Access által generálva: SELECT VAROS.ORSZAGNEV AS Országnév, VAROS.VAROSNEV AS Városnév, VAROS.VLELEKSZAM AS [Jelen városi lélekszám], [VLELEKSZAM]*1.15 AS [Becsült jövőbeli lélekszám] FROM VAROS WHERE (((VAROS.ORSZAGNEV)="India")) ORDER BY VAROS.VLELEKSZAM DESC; Az eredmény: Országnév India India India India India India India
Városnév
Jelen városi lélekszám
Bombay Új-Delhi Calcutta Madras Bangalore Hyderabad Ahmedabad
Becsült jövőbeli lélekszám
8243,00 5714,00 3305,00 3277,00 2476,00 2151,00 2060,00
9479,45 6571,1 3800,75 3768,55 2847,4 2473,65 2369
6. Azonos értékek szűrése Feladat:
Írasd ki az „a” betűs országokat a város táblából, de mindegyiket csak egyszer!
QBE megoldás:
SQL megoldás Access által generálva: SELECT DISTINCT VAROS.ORSZAGNEV FROM VAROS WHERE (((VAROS.ORSZAGNEV) Like "a*"));
10
Balogh Gábor
Dózsa SZKI Kalocsa 7. Csoportosítás számított mezőre
Feladat:
Listázd ki azokat a betűket, amelyekkel több mint 10 ország neve kezdődik!
QBE megoldás:
SQL megoldás Access által generálva: SELECT Left([ORSZAGNEV],1) AS Kezdőbetű, Count(ORSZAG.ORSZAGNEV) AS CountOfORSZAGNEV FROM ORSZAG GROUP BY Left([ORSZAGNEV],1) HAVING (((Count(ORSZAG.ORSZAGNEV))>10)) ORDER BY Count(ORSZAG.ORSZAGNEV) DESC; Az eredmény: Kezdőbetű S B M K A G T N
CountOfORSZAGNEV 23 20 17 16 14 12 11 11
8. Paraméteres lekérdezés Feladat:
Listázd ki a kezelő által megadott ország városait!
QBE megoldás:
SQL megoldás Access által generálva: SELECT VAROS.ORSZAGNEV, VAROS.VAROSNEV FROM VAROS WHERE (((VAROS.ORSZAGNEV)=[Kérem az ország nevét:])); Az eredmény: ORSZAGNEV Ausztria Ausztria Ausztria Ausztria Ausztria Ausztria
11
VAROSNEV Bécs Graz Innsbruck Klagenfurt Linz Salzburg
Balogh Gábor
Dózsa SZKI Kalocsa 9. Táblák egyesítése
Feladat:
Listázzuk ki az ország, illetve a város táblát!
QBE megoldás:
SQL megoldás Access által generálva: SELECT ORSZAG.* FROM ORSZAG; Az eredmény:
Feladat:
SELECT VAROS.* FROM VAROS;
Hozzuk létre a két tábla direktszorzatát! (Minden sort minden sorral párosítunk.)
QBE megoldás:
SQL megoldás Access által generálva: SELECT ORSZAG.*, VAROS.* FROM VAROS, ORSZAG; Az eredmény:
12
Balogh Gábor Feladat:
Dózsa SZKI Kalocsa Hozzuk létre a két tábla természetes összefésülését! (Minden várost csak a hozzá tartozó országgal párosítunk.)
QBE megoldás:
SQL megoldás Access által generálva: SELECT ORSZAG.*, VAROS.* FROM ORSZAG INNER JOIN VAROS ON ORSZAG.ORSZAGNEV = VAROS.ORSZAGNEV; Az eredmény:
Feladat:
Hozzuk létre a két tábla balról illesztését! (Minden várost csak a hozzá tartozó országgal párosítunk, de azokat az országokat is kiírjuk, melyeknek nincs városuk az adatbázisban.)
QBE megoldás:
SQL megoldás Access által generálva: SELECT ORSZAG.*, VAROS.* FROM ORSZAG LEFT JOIN VAROS ON ORSZAG.ORSZAGNEV = VAROS.ORSZAGNEV;
13
Balogh Gábor
Dózsa SZKI Kalocsa
Az eredmény:
Feladat:
Mely országoknak nincs városuk?
QBE megoldás:
SQL megoldás Access által generálva: SELECT ORSZAG.ORSZAGNEV, VAROS.VAROSNEV FROM ORSZAG LEFT JOIN VAROS ON ORSZAG.ORSZAGNEV = VAROS.ORSZAGNEV WHERE (((VAROS.VAROSNEV) Is Null)); Az eredmény: ORSZAGNEV VAROSNEV Eldorádó Tündérország
Feladat:
Listázd ki a fővárosokat népesség szerint csökkenő sorrendben!
QBE megoldás:
14
Balogh Gábor
Dózsa SZKI Kalocsa
SQL megoldás Access által generálva: SELECT ORSZAG.ORSZAGNEV, ORSZAG.FOVAROSNEV, VAROS.VLELEKSZAM FROM ORSZAG INNER JOIN VAROS ON (ORSZAG.FOVAROSNEV = VAROS.VAROSNEV) AND (ORSZAG.ORSZAGNEV = VAROS.ORSZAGNEV) ORDER BY VAROS.VLELEKSZAM DESC; Az eredmény: ORSZAGNEV Egyiptom Dél-Korea Mexikó Oroszország Franciaország Japán Nagy-Britannia
Feladat:
FOVAROSNEV VLELEKSZAM Kairó 11000 Szöul 9646 Mexikóváros 8831 Moszkva 8769 Párizs 8707 Tokió 8324 London 6678
Mely országok nevei városnevek is egyben?
QBE megoldás:
SQL megoldás Access által generálva: SELECT ORSZAG.ORSZAGNEV FROM ORSZAG INNER JOIN VAROS ON ORSZAG.ORSZAGNEV = VAROS.VAROSNEV; Az eredmény: ORSZAGNEV Macau Monaco Salvador San Marino
15
Balogh Gábor
Dózsa SZKI Kalocsa 10. Összetett lekérdezések
Feladat:
Mely városnevek szerepelnek kettőnél több országban, és melyek ezek az országok?
QBE megoldás első lépcső:
SQL megoldás Access által generálva: SELECT VAROS.VAROSNEV, Count(VAROS.ORSZAGNEV) AS CountOfORSZAGNEV FROM VAROS GROUP BY VAROS.VAROSNEV HAVING (((Count(VAROS.ORSZAGNEV))>2)) ORDER BY Count(VAROS.ORSZAGNEV) DESC; Az eredmény Többszörös városnevek néven elmentve: VAROSNEV CountOfORSZAGNEV Victoria 4 Santiago 4 Georgetown 4 San Juan 3 Hamilton 3
QBE megoldás második lépcső:
SQL megoldás Access által generálva: SELECT VAROS.VAROSNEV, VAROS.ORSZAGNEV, VAROS.VLELEKSZAM FROM VAROS INNER JOIN [Többszörös városnevek] ON VAROS.VAROSNEV = [Többszörös városnevek].VAROSNEV;
16
Balogh Gábor
Dózsa SZKI Kalocsa
Az eredmény: VAROSNEV Georgetown Georgetown Georgetown Georgetown Hamilton Hamilton Hamilton San Juan San Juan San Juan
ORSZAGNEV Malaysia Saint Vincent Gambia Guyana Új-Zéland Kanada Bermuda Argentína Dominikai Köztársaság Puerto Rico
17
VLELEKSZAM 251 1 3 200 104 307 3 292 50 449
