Vissza az előzőleg látogatott oldalra (nem elérhető funkció)Vissza a tananyag kezdőlapjára (P)Ugrás a tananyag előző oldalára (E)Ugrás a tananyag következő oldalára (V)Fogalom megjelenítés (nem elérhető funkció)Fogalmak listája (nem elérhető funkció)Oldal nyomtatása (nem elérhető funkció)Oldaltérkép megtekintése (D)Keresés az oldalon (nem elérhető funkció)Súgó megtekintése (S)

Tehetséggondozás az informatikában – Adatbázis-kezelés / Lekérdezések /SQL-specifikus lekérdezések

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és

Illetve 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és

Ha 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)

Feladat

Ebben 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élda

Javasolt 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és

Nem 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#)
Fontos

Fontosnak 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!
Feladat

További, hasonló módon megoldható feladatok:

Vissza a tartalomjegyzékhez

Új Széchenyi terv
A projekt az Európai Unió támogatásával, az Európai Szociális Alap társfinanszirozásával valósul meg.
Készült az "Országos koordinációval a pedagógusképzés megújításáért” című TÁMOP-4.1.2.B.2-13/1-2013-0007 pályázat keretében.

A tananyag az ELTESCORM keretrendszerrel készült