SQL – DQL (Data Query Language ) adat lekérdezések

1. SQL – DQL (Data QueryLanguage )adat lekérdezések SELECT [kijelentés] { * | tábla.* | [tábla.]mező1 [AS alias1] [, [tábla.]mező2 [AS alias2] [, ...]]}FROMtábla kifejezés [, ...] [IN külső adatbázis][WHERE... ][GROUP BY... ][HAVING... ][ORDER BY... ]

2. SELECT és FROM záradék A FROM záradékban adjuk meg azokat a táblákat (vagy lekérdezéseket), amelyekből szeretnénk lekérdezni adatokat. SELECT záradékban adhatjuk meg azokat oszlopneveket, kifejezéseket, függvényeket,…, amelyek az eredmény oszlopai lesznek. Ha az összes mezőt szeretnénk látni az eredményben, akkor használhatjuk a * karaktert az oszlopnevek felsorolása helyett. Az AS kulcsszó segítségével álneveket rendelhetünk a táblákhoz és a mezőnevekhez (pl.: rövidítés). SELECT * FROM táblanév; 1. Kérdezzük le a diákok adatait. SELECT * FROM diak; SELECT oszlopnév1, oszlopnév2, ..., oszlopnévN FROM táblanév; 2. Kérdezzük le az összes diák nevét és születési dátumát. SELECT nev, szulido FROM diak;

3. DISTINCT Az eredmény ismétlődő soraiból csak egyet tart meg. SELECT DISTINCT oszlopnév1, oszlopnév2, ..., oszlopnévN FROM táblanév; 3. Milyen hajú diákok vannak a nyilvántartásban? SELECT DISTINCT haja FROM diak;

4. WHERE záradék Azok a sorok kerülnek az eredménybe, amelyek teljesítik a WHERE záradékba írt feltételt (logikai kifejezést). SELECT mezőlistaFROM tábla kifejezésWHERE feltétel 4. Kérdezzük le a Kovács Péter nevű diákok nevét és születési dátumát. SELECT nev, szulido FROM diak WHERE nev='Kovács Péter'; 5. Kérdezzük le azokat a Kovács Péter nevű diákokat, amelynek anyja neve Kiss Anna. SELECT * FROM diak WHERE nev='Kovács Péter' and anyja='Kiss Anna';

5. LIKE operátor A LIKE operátorral szöveges kifejezést hasonlíthatunk össze egy mintával. Szöveges_kifejezésLIKE ‘minta’

6. LIKE operátor 6. Kérdezzük le Kovács kezdetű névvel rendelkező fiú diákokat. SELECT * FROM diak WHERE nev like 'Kovács*' and neme='F'; 7. Kérdezzük le Kovács vezetéknévvel rendelkező fiú diákokat. SELECT * FROM diak WHERE nev like 'Kovács *' and neme like 'F'; 8. Kérdezzük le Rá?z kezdetű névvel rendelkező diákokat. (vigyázat Kovácsnál cs betű egynek számít ? nem működik ) SELECT * FROM diak WHERE nev like 'Rá?z*';

7. IS [NOT] NULL NULL érték vizsgálata nem lehetséges az =, <> operátorokkal, csak az IS predikátummal, vagy az adatbázis-kezelő rendszer egy megfelelő függvényével. oszlopnév IS NULLoszlopnév IS NOT NULL 9. Kérdezzük le azokat a diákokat, amelyek haja vörös vagy nincs kitöltve a hajszín. SELECT * FROM diak WHERE haja is null or haja='vörös';

8. BETWEEN … AND … operátor Megadja, hogy egy kifejezés értéke a meghatározott tartományba esik-e. érték1<=kifejezés<=érték2 kifejezés [Not] Betweenérték1Andérték2 10. Kérdezzük le azokat a diákokat akik nyáron születtek. (month() függvény megadja egy dátum hónap részét 1-12) SELECT * FROM diak WHERE month(szulido) BETWEEN 6 and 8;

9. IN operátor Segítségével meghatározhatja, hogy egy kifejezés értéke egyenlő-e a megadott listában szereplő értékek bármelyikével. A lista lehet alkérdés eredménye is (lásd később). kifejezés [Not] In (érték1, érték2, . . .) 11. Kérdezzük le a sötét hajú diákokat (‘barna’, ‘fekete’). SELECT * FROM diak WHERE haja in ('barna','fekete');

10. ORDER BY záradék A lekérdezés eredményeként kapott rekordokat a megadott mező vagy mezők szerint növekvő vagy csökkenő sorrendbe rendezi. SELECT mezőlistaFROM táblaWHERE feltétel[ORDER BY mező1 [ASC | DESC ][, mező2 [ASC | DESC ]][, ...]]] Az alapértelmezett rendezési sorrend a növekvő ASC, csökkenő sorrendű rendezéshez a DESC szót kell beilleszteni rendezendő mező neve után. 12. Rendezzük a lányokat név szerint növekvő, azonos nevűek esetén születési dátum szerint csökkenő sorrendbe. SELECT * FROM diak WHERE neme='N' ORDER BY nev, szulido DESC;

11. TOP n [PERCENT] predikátum Akkor használjuk, ha a lekérdezés eredményének csak az első n darab sorát (PERCENT esetén az első n%-át) szeretnénk megkapni. Általában az ORDER BY záradékkal együtt használjuk, ekkor a rendezés szerinti első n db. sort adja meg. Rendezettség nélkül n db. tetszőleges sort kapunk. SELECT TOP n [PERCENT] mezőlistaFROM táblaWHERE feltétel[ORDER BY mező1 [ASC | DESC ][, mező2 [ASC | DESC ]][, ...]]] 13. Adjuk meg a három legidősebb diák nevét és születési dátumát. SELECT TOP 3 nev,szulido FROM diak ORDER BY szulido TOP predikátum nem választ a rendezettség alapján egyenlő értékek közül, tehát ha az n. és n+1,n+2,... sorok a rendezés szerinti mezőn azonos értéket tartalmaznak, akkor azokat mind megjeleníti. 14. Hány sort jelenít meg a következő lekérdezés? SELECT TOP 3 nev,neme FROM diak ORDER BY neme

12. Összesítő függvények Olyan függvények, amelyek egy adathalmaz elemeiből néhány egyszerű statisztikai adatot adnak meg. COUNT: értékek halmazának számosságát adja. MIN, MAX: értékek halmazának legkisebb/legnagyobb értékét adja. AVG: értékek halmazának számtani középértékét számolja ki. SUM: értékek halmazának összegét számolja ki. 15. Mikor született a legfiatalabb diák? SELECT max(szulido) FROM diak; 16. Hány diák van a nyilvántartásban? SELECT count(*) FROM diak; 17. Mennyi a diákok átlagéletkora? Ahol legyen az átlagéletkor = AVG( (akt_dátum hónapja és szül.idő hónapja közötti hónapok száma)/12). Használjuk a DATEDIFF függvényt. SELECT ROUND(AVG(datediff('m',szulido,now())/12),2)FROM diak;

13. GROUP BY záradék SELECT mezőlistaFROM táblaWHERE feltétel[GROUP BYmezőcsoportlista] A mezőcsoportlistában megadott mezők értékeinek azonossága alapján csoportokat képez. Azok a rekordok kerülnek egy csoportba, ahol mezőcsoportlista minden mezőjének értéke azonos. A csoportosítást legtöbbször összesítő SQL függvényekkel együtt szoktuk használni. A SELECT mezőlista minden mezőjének szerepelnie kell vagy a GROUP BY záradékban, vagy az összesítő SQL-függvény argumentumai között. 18. Hány barna, szőke,... hajú diák van? SELECT haja, count(*) FROM diak GROUP BY haja; 19. Hány fiú és lány diák van? SELECT neme, count(*) FROM diak GROUP BY neme;

14. HAVING záradék Miután a rekordokat a GROUP BY záradékkal csoportosítottuk, a HAVING záradékkal megadhatjuk, hogy mely rekordok jelenjenek meg. A HAVING záradékban csak a csoportosításban szereplő mezőkre vagy összesítő SQL függvényekre vonatkozó kifejezéseket írhatunk. SELECT mezőlistaFROM táblaWHERE feltételGROUP BY csoportmezőlista[HAVINGcsoportosítási feltétel] 20. Melyik évben született több mint 2 diák? SELECT year(szulido) FROM diak GROUP BY year(szulido) HAVING count(*)>2;

15. Direktszorzat Ha FROM záradékban két táblát adunk meg, akkor az eredmény a két tábla rekordjainak direkt szorzata lesz. WHERE feltétel megadásával ezt a direktszorzatot tudjuk szűrni, tehát a kapcsolatokat tudjuk megjeleníteni. 21. Képezzünk direktszorzatot a tantárgy és a témakör táblák között. SELECT * FROM tantargy, temakor; 22. Képezzünk direktszorzatot a tantárgy és a témakörök között, de csak azokat tartsuk meg, ahol a kapcsoló mező azonos. Az eredményt rendezzük tantárgy és témakör szerint. SELECT tantargy, temakor FROM tantargy AS tt, temakorAS te WHERE tt.tt_id=te.tt_id ORDER BY tantargy, temakor; 23. Kérdezzük le azinfromatika ill. a matematikatantárgyakrajáródiákoknévsorát (tantárgy, név). Rendezzükazeredményttantárgyszerint, azonbelülpedignévsorba. SELECT tantargy, nev FROM diak AS d, diak_tt AS dtt, tantargy AS tt WHERE d.diak_id=dtt.diak_id and dtt.tt_id=tt.tt_id and tt.tantargy in ('informatika','matematika') ORDER BY tantargy, nev;

16. INNER JOIN Az INNER JOIN művelet segítségével táblákat tudunk összekapcsolni. A kapcsolt táblákból táblakifejezéseket hozunk létrehozni, amelyeket a FROM záradékban használhatunk. FROM tábla1INNER JOIN tábla2ONtábla1.mezőösszehasonlító operátor tábla2.mező2 JOIN utasítások egymásba is ágyazhatók a következő szintaxis alkalmazásával: SELECT mezőkFROM tábla1INNER JOIN(tábla2INNER JOINtábla3 ONtábla2.mező2összehasonlító operátortábla3.mező3) ONtábla1.mező1összehasonlító operátortábla2.mező2; 24. Adjuk meg a tantárgyakat és a hozzá tatozó témaköröket. Az eredményt rendezzük tantárgy és témakör szerint. SELECT tantargy, temakor FROM tantargy AS tt INNER JOIN temakor AS te ON tt.tt_id=te.tt_id ORDER BY tantargy, temakor; 25. Kérdezzük le az informatika ill. a matematika tantárgyakra járó diákok névsorát (tantárgy, név). Rendezzük az eredményt tantárgy szerint, azon belül pedig névsorba. SELECT tantargy, nev FROM diak AS d INNER JOIN (diak_tt AS dtt INNER JOIN tantargy AS tt ON dtt.tt_id=tt.tt_id) ONd.diak_id=dtt.diak_id WHERE tt.tantargyin ('informatika','matematika') ORDER BY tantargy, nev;

17. OUTER JOIN – LEFT JOIN, RIGHT JOIN A LEFT JOIN műveletet bal oldali külső illesztés létrehozására használhatjuk. A bal oldali külső illesztés a két tábla közül az első (a bal oldali) tábla minden rekordját tartalmazza, még akkor is, ha a második (jobb oldali) tábla nem tartalmaz illeszkedő értékeket. A RIGHT JOIN műveletet jobb oldali külső illesztés létrehozására használhatjuk. A jobb oldali külső illesztés a két tábla közül a második (a jobb oldali) tábla minden rekordját tartalmazza, még akkor is, ha az első (bal oldali) tábla nem tartalmaz illeszkedő értékeket. FROM tábla1[ LEFT | RIGHT ] JOIN tábla2ONtábla1.mező1összehasonlító operátor tábla2.mező2 LEFT JOIN vagy RIGHT JOIN műveletet beágyazhatunk INNER JOIN műveletbe, INNER JOIN műveletet azonban nem ágyazhatunk LEFT JOIN vagy RIGHT JOIN műveletbe. 26. Adjuk meg a Nagy vezetéknevű diákokat (nevét, születési dátumát) és tantárgyaikat. Azok a Nagy vezetéknevű diákok is kerüljenek az eredménybe, akik nem járnak egyik tantárgyra sem. Az eredményt rendezzük név, születési dátum és tantárgy szerint. SELECT nev, szulido, tantargy FROM diak AS d LEFT JOIN (diak_tt AS dtt LEFT JOIN tantargy AS tt ON dtt.tt_id=tt.tt_id) ONd.diak_id=dtt.diak_id WHERE d.nevlike 'nagy *' ORDER BY nev, szulido, tantargy;

18. ANY Beágyazott lekérdezések esetén, akkor ad igaz értéket, ha a beágyazott lekérdezésnek van olyan sora, amelyre teljesül az ANY kulcsszó előtt megadott összehasonlító feltétel. WHERE … kifejezés <= ANY (SELECT oszlopnév FROM táblanév …) 27. Melyik tantárgyakra jár vörös hajú diák? SELECT * FROM tantargy AS tt WHERE 'vörös' = ANY (select haja fromdiak d,diak_ttdttwhered.diak_id=dtt.diak_id and dtt.tt_id=tt.tt_id);

19. [NOT] EXISTS Beágyazott lekérdezések esetén, akkor ad igaz értéket, ha a beágyazott lekérdezésnek eredménye nem üres halmaz. WHERE … EXISTS (SELECT oszlopnév1,oszlopnév2,… FROM táblanév …) 28. Melyik tantárgyakra jár vörös hajú diák? SELECT * FROM tantargy AS tt WHERE EXISTS (select * fromdiak d,diak_ttdttwhered.diak_id=dtt.diak_id and dtt.tt_id=tt.tt_idand haja='vörös'); 29. Melyik az a diák, amelyiknek nincs tantárgya? SELECT * FROM diak AS d WHERE not exists(select * from diak_ttdtt where dtt.diak_id=d.diak_id);

20. [NOT] IN Beágyazott lekérdezések esetén, akkor ad igaz értéket, ha a beágyazott lekérdezésnek az eredményében megtalálható a [NOT] IN előtt megadott kifejezés értéke. WHERE … kifejezés[NOT] IN (SELECT oszlopnév FROM táblanév …) 30. Melyik tantárgyakra jár vörös hajú diák? SELECT * FROM tantargy AS tt WHERE tt.tt_idin (selectdtt.tt_idfromdiak d,diak_ttdttwhered.diak_id=dtt.diak_id and haja='vörös'); 31. Melyik az a diák, amelyiknek nincs tantárgya? SELECT * FROM diak WHERE diak_id not in(select diak_id from diak_tt);

21. ALL Beágyazott lekérdezések esetén, akkor ad igaz értéket, ha a beágyazott lekérdezésnek minden sorára teljesül az ALL kulcsszó előtt megadott összehasonlító feltétel. WHERE … kifejezés <= ALL (SELECT oszlopnév FROM táblanév …) 32. Melyek azok a tantárgyak, ahol a tantárgy összes diákja 1990 után született? (születési év>1990) SELECT * FROM tantargy AS tt WHERE 1990 < ALL (selectyear(szulido) fromdiak d,diak_ttdttwhered.diak_id=dtt.diak_id and dtt.tt_id=tt.tt_id); 33. Melyik az a tantárgy, amelyre csupa különböző hajú diák jár? (nincs UNIQUE) SELECT * FROM tantargy AS tt WHERE 1 = ALL (selectcount(*) fromdiak_ttdtt,diak d wheredtt.tt_id=tt.tt_id and dtt.diak_id=d.diak_id and d.haja is not null groupbyd.haja );

22. UNION [ALL] Egyesítő lekérdezést hoz létre, amely két vagy több, egymástól független lekérdezés vagy tábla adatait egyesíti. Alapértelmezés szerint a UNION művelet eredményében nem ismétlődnek rekordok (unió halmaz művelet), az ALL kijelentéssel azonban elérheti, hogy minden rekord megjelenjen. lekérdezés1UNION [ALL]lekérdezés2UNION [ALL]lekérdezés3 … 34. Képezzük az informatika és matematika órára járó diákok halmazának unióját. SELECT d.* FROM diak d,diak_ttdtt,tantargytt WHERE d.diak_id=dtt.diak_id and dtt.tt_id=tt.tt_idandtt.tantargy='informatika' UNION SELECT d.* FROM diak d,diak_ttdtt,tantargytt WHERE d.diak_id=dtt.diak_id and dtt.tt_id=tt.tt_idandtt.tantargy='matematika';

23. Metszet Access-ben az INTERSECT nem működik, oldjuk meg másként. 35. Kik azok a diákok akik informatika és matematika órára is járnak? SELECT * FROM diak WHERE diak_idin (selectdiak_idfromdiak_ttdtt,tantargyttwherett.tt_id=dtt.tt_id and tt.tantargy='informatika') and diak_idin (selectdiak_idfromdiak_ttdtt,tantargyttwherett.tt_id=dtt.tt_id and tt.tantargy='matematika');

24. Különbség Access-ben az MINUS nem működik, oldjuk meg másként. 36. Kik azok a diákok akik járnak informatika órára és nem járnak matematika órára? SELECT * FROM diak WHERE diak_idin (selectdiak_idfromdiak_ttdtt,tantargyttwherett.tt_id=dtt.tt_id and tt.tantargy='informatika') and diak_idnotin (selectdiak_idfromdiak_ttdtt,tantargyttwherett.tt_id=dtt.tt_id and tt.tantargy='matematika');

25. Beágyazott lekérdezések közötti reláció 37. Melyik diák jár az összes tantárgyra? SELECT * FROM diak AS d WHERE (select count(*) from tantargy)=(select count(*) from diak_ttdtt where dtt.diak_id=d.diak_id);