Lekérdezések gyakorlása

Report
SQL – DQL (Data Query Language )
adat lekérdezések
SELECT [kijelentés] { * | tábla.* | [tábla.]mező1 [AS alias1] [,
[tábla.]mező2 [AS alias2] [, ...]]}
FROM tábla kifejezés [, ...] [IN külső adatbázis]
[WHERE... ]
[GROUP BY... ]
[HAVING... ]
[ORDER BY... ]
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;
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;
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őlista
FROM tábla kifejezés
WHERE 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';
LIKE operátor
A LIKE operátorral szöveges kifejezést hasonlíthatunk össze egy mintával.
Szöveges_kifejezés LIKE ‘minta’
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*';
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 NULL
oszlopné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';
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ék1 And é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;
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');
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őlista
FROM tábla
WHERE 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;
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őlista
FROM tábla
WHERE 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
Ö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;
GROUP BY záradék
SELECT mezőlista
FROM tábla
WHERE feltétel
[GROUP BY mező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;
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őlista
FROM tábla
WHERE feltétel
GROUP BY csoportmezőlista
[HAVING csoportosí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;
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, temakor AS te
WHERE tt.tt_id=te.tt_id
ORDER BY tantargy, temakor;
23. Kérdezzük le az infromatika 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, 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;
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ábla1 INNER JOIN tábla2 ON tá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ők
FROM tábla1 INNER JOIN
(tábla2 INNER JOIN tábla3 ON tábla2.mező2 összehasonlító operátor tábla3.mező3)
ON tábla1.mező1 összehasonlító operátor tá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) ON
d.diak_id=dtt.diak_id
WHERE tt.tantargy in ('informatika','matematika')
ORDER BY tantargy, nev;
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ábla2
ON tá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) ON
d.diak_id=dtt.diak_id
WHERE d.nev like 'nagy *'
ORDER BY nev, szulido, tantargy;
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 from diak d,diak_tt dtt where d.diak_id=dtt.diak_id and
dtt.tt_id=tt.tt_id);
[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 * from diak d,diak_tt dtt where d.diak_id=dtt.diak_id and dtt.tt_id=tt.tt_id
and 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_tt dtt where dtt.diak_id=d.diak_id);
[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_id in (select dtt.tt_id from diak d,diak_tt dtt where d.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);
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 (select year(szulido) from diak d,diak_tt dtt where d.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 (select count(*)
from diak_tt dtt,diak d
where dtt.tt_id=tt.tt_id and dtt.diak_id=d.diak_id
and d.haja is not null
group by d.haja
);
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és1 UNION [ALL] lekérdezés2 UNION [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_tt dtt,tantargy tt
WHERE d.diak_id=dtt.diak_id and dtt.tt_id=tt.tt_id and tt.tantargy='informatika'
UNION SELECT d.*
FROM diak d,diak_tt dtt,tantargy tt
WHERE d.diak_id=dtt.diak_id and dtt.tt_id=tt.tt_id and tt.tantargy='matematika';
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_id in (select diak_id from diak_tt dtt,tantargy tt where tt.tt_id=dtt.tt_id and
tt.tantargy='informatika')
and
diak_id in (select diak_id from diak_tt dtt,tantargy tt where tt.tt_id=dtt.tt_id and
tt.tantargy='matematika');
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_id in (select diak_id from diak_tt dtt,tantargy tt where tt.tt_id=dtt.tt_id and
tt.tantargy='informatika')
and
diak_id not in (select diak_id from diak_tt dtt,tantargy tt where tt.tt_id=dtt.tt_id and
tt.tantargy='matematika');
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_tt dtt where dtt.diak_id=d.diak_id);

similar documents