Lekérdezések
SQL-specifikus lekérdezések
Az SQL-specifikus lekérdezések azért különlegesek az Access-ben, mert ezeket nem lehet a QBE rácson összedobni. Elkészítésükhöz mindenképpen szükségünk van az SQL nyelv ismeretére.
MegjegyzésIlletve némiképp meg lehet kerülni az SQL-t, de még ez esetben is szükség van a nyelv bizonyos szintű ismeretére.
Ebből következően csak az SQL nézetben lehet elkészíteni őket.
Az SQL-specifikus lekérdezésekbe az egyesítő, átadó és adatdefiniáló lekérdezések tartoznak. A következőkben ezeket tekintjük át röviden.
Egyesítő lekérdezés
Ennek a lekérdezésnek a segítségével több tábla tartalmát vagy lekérdezés eredményét fésülhetjük össze. Mint azt már korábban tárgyaltuk, a táblák és lekérdezések eredménye lényegében egy halmaz. Az egyesítő lekérdezés pedig nem más, mint az unió halmazművelet megvalósítása két vagy több lekérdezés vagy tábla között.
Felépítése:
Forráskód<Lekérdezés 1> UNION <Lekérdezés 2>
Az SQL kód használatát ennél a lekérdezés típusnál úgy lehet megkerülni, hogy elkészítjük Tervező nézetben azokat a lekérdezéseket, amelyeknek az eredményét szeretnénk egyesíteni. Ha ez megvan, akkor egy új lekérdezésbe bemásoljuk a már elkészített lekérdezések SQL kódját, és az kódok közé beírjuk a UNION kulcsszót. Arra azonban ügyeljünk, hogy az egyes lekérdezések SQL kódjának a végére az Access automatikusan beteszi az utasítást lezáró pontosvesszőt. Ezeket az egyes részlekérdezések végéről ki kell törölni, és a teljes lekérdezés végére lehet beírni.
MegjegyzésHa nem írjuk be, az sem gond, az Access automatikusan megteszi helyettünk, ezzel lezárva az SQL utasítást.
Vissza a tartalomjegyzékhez
Átadó lekérdezés
Ez a lekérdezés típus olyan esetekben használható, amikor egy távoli – akár más SQL-t, például Oracle, vagy MySQL-t használó, röviden ODBC – adatforráshoz kapcsolódva szeretnénk kihasználni az Access nyújtotta szolgáltatásokat, például jelentés- vagy űrlapkészítés céljából.
Nagy előny, hogy ilyenkor a távoli adatbázis tábláival közvetlenül dolgozhatunk; nincs szükség a táblák csatolására vagy importálására.
Vissza a tartalomjegyzékhez
Adatdefiniáló lekérdezés
Ezzel a típussal új táblákat hozhatunk létre. Ezt Access-ben nagyon egyszerűen ki tudjuk kerülni: a Táblatervező eszköz használatával.
Ami miatt esetleg csábító lehetne a használata, az talán az, hogy – más adatbázis-kezelő rendszerből kiindulva – azt gondoljuk, egymás után, pontosvesszőkkel elválasztva több ilyen utasítást futtathatunk egyszerre, ezáltal gyorsabban létrehozva a táblákat. Azonban erre sajnos nincs lehetőség. Egyszerre csak egy SQL utasítást lehet futtatni SQL nézetben.
Az adatdefiniáló lekérdezés SQL szintaxisára egy példa:
ForráskódCREATE TABLE tSzállás
([Szállás] integer,
[Épület] text,
[Szobaszám] text,
CONSTRAINT [pk_Szállás] PRIMARY KEY ([Szállás]));
Megjegyzés
- ha a tábla nevében szóköz szerepel, akkor a tábla nevét szögletes zárójelek közé kell írni
- a CREATE TABLE utasításon belül szögletes zárójelek közé kell írni a mezők neveit
- A CONSTRAINT kulcsszó után jönnek a mezőkre vonatkozó megszorítások (elsődleges kulcs, idegen kulcs, egyediség)
OKTV 2010/2011. 3. forduló 5. feladat (Állatvédelem) D részfeladat (emlősök)
FeladatEbben a részfeladatban meg kell határoznunk, hogy az egyes emlősfajokból hány egyedet észleltek összesen úgy 2010-ben, hogy azokhoz az emlősfajokhoz, amelyekből nem észleltek egyedet, azokhoz 0-t írjon a lekérdezés.
PéldaJavasolt megoldás:
Megoldási terv:
A feladat szövegéből látszik, hogy itt valójában két lekérdezés eredményét kell egyesítenünk. Első lépésként bontsuk szét a feladatot két részfeladatra:
- Készítsünk lekérdezést, ami megadja, hogy 2010-ben az egyes emlősfajokból hány egyedet észleltek összesen, majd
- készítsünk egy másik lekérdezést, amelyben azokat az emlősfajokat gyűjtjük össze, melyekből nem észleltek egy példányt sem 2010-ben. Ne feledkezzünk meg arról, hogy itt a darabszámhoz 0-t kell írnunk!
Miután elkészült a két segédlekérdezésünk, nincs más dolgunk, mint hogy uniózzuk az eredményüket!
Javasolt megoldás lépései:
- Készítsünk egy lekérdezést Tervező nézetben!
- Először készítsük el azt a lekérdezést, melyben a 2010-es észlelések számát adjuk meg. Adjuk hozzá a lekérdezéshez az Állatok és az Észlelések táblákat!
- Jelenítsük meg az Állatok táblából a Faj neve, az Észlelések táblából pedig az Észlelt egyed száma és a Dátum mezőket! Egyúttal a Dátum mezőre fogalmazzuk meg a feltételünket: Between #2010.01.01.# And #2010.12.31.#.
- Mivel faj szerint kellene megszámolni az egyedszámot, ezért kapcsoljuk be a QBE rács Összesítés sorát (Tervezés fül, Összesítés gomb)!
- A Faj neve mező Összesítés sorába állítsuk be a Group by záradékot, az Észlelt egyed száma mezőnél pedig a Sum összesítő függvényt! Mivel a Dátum mező szerint nem csoportosítunk, és nem is tartalmaz összesítő függvényt, ezért az Összesítés sorába állítsuk be a Where záradékot! Ha mindent jól csináltunk, akkor ezt kell látnunk:
- Mentsük a lekérdezést D_emlősök_észleltek néven!
MegjegyzésNem lenne kötelező elmenteni, elég lenne csak addig megnyitva tartani, míg az SQL kódjára szükség van.
- Most készítsük el a nem észlelt emlősöket tartalmazó lekérdezést! Hozzunk létre ismét egy új lekérdezést Tervező nézetben, és adjuk hozzá az Állatok táblát!
- Most azokat az emlős fajokat kell kilistáznunk, amelyeket nem észleltek 2010-ben. Adjuk hozzá a lekérdezéshez a Faj neve és az Osztály mezőket! Az Osztály mező feltétel sorába írjuk be, hogy emlősök, ezzel a lekérdezés egyik feltételét már el is készítettük.
- Mivel a nem észlelt fajok darabszáma 0, ezért hozzunk létre egy új mezőt Észlelt egyedek száma névvel, és adjunk neki konstans 0 értéket!
- Most már csak azt a feltételt kell elkészítenünk, amivel megkapjuk, hogy melyek azok a fajok, amelyeket nem észleltek. Ezt legegyszerűbben egy SQL allekérdezéssel tudjuk elkészíteni. Az allekérdezésben kigyűjtjük az Észlelések táblából azokat a fajokat, melyeket 2010.01.01. és 2010.12.31. között észleltek. Végül pedig megnézzük, hogy melyek azok a fajok, amelyek nem szerepelnek ennek az allekérdezésnek a végeredményében.
- Tehát a fentieket figyelembe véve a Faj neve mező Feltétel sorába a következő kód kerül:
ForráskódNot In (SELECT észlelések.faj
FROM észlelések
WHERE (észlelések.Dátum)>=#1/1/2010# And (észlelések.Dátum)<=#12/31/2010#)
FontosFontosnak tartom megjegyezni, hogy ezt a kódrészletet beírhatjuk a QBE rács Feltétel sorában, vagy a Kifejezésszerkesztőbe is. Viszont mivel itt már SQL kódot írunk, sajnos a dátumnál nem használhatjuk a magyar formátumot (év.hónap.nap), hanem csak és kizárólag a hónap/nap/év formátummal dolgozhatunk, különben szintaktikai hibaüzenetet ad a rendszer.
- Mentsük el a lekérdezést D_emlősök_nem_észleltek néven!
- Most már nincs más dolgunk, mint hogy vegyük ennek a két lekérdezésnek az unióját! Hozzunk létre egy lekérdezést Tervező nézetben! Ne adjunk hozzá semmilyen táblát, de a szalagon a Tervezés fülön állítsuk be a lekérdezés típusát Egyesítőre.
- Ha bezártuk volna, akkor nyissuk meg az előbb létrehozott két lekérdezést, és váltsunk át SQL nézetre!
- Jelöljük ki az első lekérdezés SQL kódját, majd másoljuk át az egyesítő lekérdezésbe! Ne felejtsük el kitörölni az utasítás végéről a pontosvesszőt!
- Hogy olvashatóbb legyen a kód, nyomjunk egy entert, majd az új sorba írjuk be a UNION utasítást, és nyomjunk ismét entert!
- Ezután másoljuk ide a másik lekérdezés SQL kódját is! Ha mindent jól csináltunk, akkor a következő SQL kódot kell látnunk:
ForráskódSELECT Állatok.[Faj neve], 0 AS [Észlelt egyedek száma]
FROM Állatok
WHERE (((Állatok.[Faj neve]) Not In (SELECT észlelések.faj FROM észlelések WHERE (észlelések.Dátum)>=#1/1/2010# And (észlelések.Dátum)<=#12/31/2010#)) AND ((Állatok.Osztály)="emlősök"))
UNION
SELECT Állatok.[Faj neve], Sum(Észlelések.[Észlelt egyed száma]) AS [Észlelt egyed száma]
FROM Állatok INNER JOIN Észlelések ON Állatok.[Faj neve] = Észlelések.Faj
WHERE (((Észlelések.Dátum) Between #1/1/2010# And #12/31/2010#))
GROUP BY Állatok.[Faj neve];
- Mentsük a lekérdezést D_emlősök néven, és zárjuk be!
FeladatTovábbi, hasonló módon megoldható feladatok:
Vissza a tartalomjegyzékhez
61_full.jpg61. ábra: A 2010-ben észlelt állatok száma
62_full.jpg62. ábra: Egyesítő lekérdezés beállítása
