Az SQL adatbáziskezelő nyelv

Az SQL (Structured Query Language) alapvetően lekérdező nyelv, vagyis az adatbázisban tárolt információk visszanyerésére szolgál, de rendelkezik néhány adatbeviteli és módosítási lehetőséggel is. Nem algoritmikus nyelv, így nem tartalmaz vezérlőszerkezeteket sem (elágazás, ciklus). Az SQL nyelvet sokszor valamilyen más programnyelvbe ágyazva alkalmazzák (embedded SQL). Számos adatbáziskezelő rendszer, így az ORACLE is az SQL nyelvet használja az adatok elérésére és módosítására.

Az utasítások formája egyszerű, a parancsok angol mondatok formájában fogalmazhatók meg. Egyaránt használják a felhasználók, fejlesztők és az adatbázis-adminisztrátorok feladataik végrehajtásához.

A következőkben az SQL nyelv alapjaival ismerkedünk meg.

Jelölések

A parancsok ismertetésénél a kulcsszavakat nagybetűvel írjuk. Ezeket betűről betűre pontosan le kell írni. A számítógépen tetszőlegesen írhatjuk őket kis-, vagy nagybetűkkel.

A csúcsos zárójelek < > közötti szöveg szimbólum. Ide változóneveket, vagy állandókat lehet írni.

A szögletes zárójelek [ ] közötti paraméterek megadása nem kötelező.

A | jel egymást helyettesítő szimbólumok ,vagy kulcsszavak elválasztására szolgál.

A kapcsos zárójel azt jelenti, hogy a kifejezés tetszés szerint ismételhető.

A csúcsos, szögletes és kapcsos zárójeleket a program szövegébe nem szabad beírni. Minden egyéb jelölt vesszőt, pontot és kerek zárójelet kötelezően le kell írni.

Az utasításokban tetszőlegesen elhelyezhetünk szóközöket, egy utasítást több sorban is leírhatunk. Az utasítást pontosvesszővel kell lezárni.

Az adatbázisok, táblák, mezők és más objektumok neveinek betűvel kell kezdődnie, betűket, számokat, az aláhúzásjelet és a $, vagy a # jelet tartalmazhatják. Az adatbázis-név legfeljebb 8 karakter hosszú lehet, a többi név pedig legfeljebb 30 karakterből állhat.

A táblák, vagy nézettáblák nevét minősíthetjük a tulajdonosa nevével, megkülönböztetve ezzel más felhasználó ugyanilyen nevű táblájától. Ennek jelölése: <tulajdonosnév>.<táblanév> .

A legfontosabb adattípusok

CHAR karakteres adatok tárolására szolgál, az adatok maximális hossza 255

karakter.

LONG változó hosszúságú szövegek tárolására szolgál, a szöveg mérete

2 Gbyte-ig terjedhet.

NUMBER numerikus adatok tárolására szolgál, a számokat maximum 38 jegy

pontosságig tudja ábrázolni. Az adatok lehetnek fixpontosak, vagy lebegőpontosak. A legkisebb ábrázolható szám a 10^-129, a legnagyobb pedig 9.99*10¹²⁴. Más adatbáziskezelő rendszerekkel való kompatibilitás miatt az SQL nyelv megenged más numerikus adattípusok deklarálását is (pl. INTEGER, FLOAT, DECIMAL, REAL) de ezek belső ábrázolása megfelel a NUMBER adattípusnak.

A numerikus adatok méretének definiálásánál az összes jegyek számát és a tizedesjegyek számát kell megadni vesszővel elválasztva.

DATE dátum- és időadatok tárolására szolgál.

Egy dátum típusú adat a következőket tartalmazza:

évszázad, év, hónap, nap, óra, perc, másodperc

A dátumrész szokásos formája NN-HHH-ÉÉ, például 12-NOV-93.

A rendszerdátumot és -időt a SYSDATE függvény segítségével kaphatjuk meg.

Egy dátum típusú adatnak leggyakrabban a dátum részét szoktuk használni, az évszázad nélkül. A további dátum és időinformációkat a dátum típusú adatból függvények segítségével tudjuk megjeleníteni.

Konstansok

Az SQL nyelvben karakteres, numerikus, logikai és dátum típusú konstansok használhatók. A karakteres és a dátum típusú konstansokat aposztrófok közé kell tenni.

Példák

'BUDAPEST'

12E2      (=1200)

9.85E-3 (=0.00985)

356e23

'09-MAR-92'

A NULL érték

Egy sorban egy adott oszlop értéke NULL, ha az adott oszlop értéke ismeretlen, vagy lényegtelen. A NULL érték nem azonos a nullával. A NULL értékkel műveletek is végezhetők, például 10*NULL=NULL.

Változók

Az SQL nyelvben a változók a konstans értékek tárolására szolgálnak.

Adatkonverzió

A kifejezések különböző adattípusokat tartalmazhatnak. Kiértékelésükhöz esetenként adatkonverzióra lehet szükség. Az ORACLE adatbáziskezelő a következő esetekben automatikusan elvégzi a konverziót:

- konstans konvertálása egy oszlop adattípusára

- egy oszlop értékének konvertálása egy konstans adattípusára

- egy oszlop értékének konvertálása egy másik oszlop adattípusára

Egyéb esetekben konverziós függvények segítségével lehet az adatkonverziót elvégezni.

Műveletek

Matematikai műveletek

összeadás

kivonás

szorzás

osztás

Karakteres műveletek

láncolás

Példa

A 'FEHÉR' || 'VÁR' eredménye 'FEHÉRVÁR' .

Relációs műveletek

egyenlő

!=,^=,<>            nem egyenlő

>=                      nagyobb, vagy egyenlő

<=                      kisebb, vagy egyenlő

[NOT] IN          tartalmazás (listában)

[NOT] BETWEEN x AND y               igaz, ha a vizsgált érték nagyobb, vagy

egyenlő x-nél és kisebb, vagy egyenlő

y-nál.

IS [NOT] NULL igaz, ha a vizsgált érték NULL

[NOT] LIKE      karaktersorozat mintával történő

összehasonlítása

Ha a NOT kulcsszót is megadjuk, akkor a művelet eredménye az eredeti művelet eredményének ellentéte lesz.

A LIKE operátor segítségével egy szöveget lehet összehasonlítani egy mintával. Ha pontos egyezést akarunk vizsgálni, akkor az = operátort kell alkalmazni. A LIKE operátornál a mintában helyettesítő karaktereket alkalmazhatunk. A % karakter tetszőleges számú karaktert helyettesíthet, az _ (aláhúzás) karakter pedig pontosan egyet. A vizsgálandó szöveget a LIKE előtt, a mintát a LIKE kulcsszó után kell megadni.

A karakterek összehasonlításánál a kis- és a nagybetű különböző karakternek számít!

Példák:

A val IN(10,20,40) kifejezés értéke igaz, ha a val változó értéke megegyezik a zárójelben felsorolt értékek valamelyikével.

A val BETWEEN 100 AND 200 értéke igaz, ha a val változó értéke a 100-200 értékek között van, vagy egyenlő valamelyikkel.

A szoveg LIKE 'B%' értéke igaz, ha a szoveg változóban található karaktersorozat B betűvel kezdődik.

A szoveg LIKE '_LO' értéke igaz, ha a szoveg változóban található karaktersorozat 3 betűből áll, és a 2., 3. karaktere LO .

Logikai műveletek

NOT                            negálás

OR                               logikai vagy

AND                            logikai és

A logikai műveletek ismertetése a CLIPPER nyelv leírásánál található.

Kifejezések

Egy kifejezés lehet

- oszlopnév, konstans, vagy speciális érték (pl. SYSDATE, NULL)

- függvényhívás

- kifejezések műveleti jelekkel és zárójelekkel összekapcsolva

SQL utasítások

Az SQL utasítások típusai

Adatdefiníciós utasítások (Data Definition Language - DDL)

Ezek szolgálnak az adatbázis objektumainak (táblák, indexek, nézettáblák, stb.) definiálására, létrehozására, megszüntetésére.

Adatmanipulációs utasítások (Data Manipulation Language - DML)

Ezekkel az utasításokkal tudjuk a táblák tartalmát megváltoztatni (új sor felvitele, sorok törlése, mezők módosítása). Egyes csoportosítások ide sorolják a lekérdező utasítást is.

Lekérdező utasítások (Data Query Language - DQL)

Más csoportosítások megkülönböztetik a lekérdező utasítást az adatmanipulációs utasításoktól és külön csoportba sorolják.

Tranzakció vezérlő utasítások (Transaction Control Language - TCL)

A műveletek logikai csoportokba, tranzakciókba történő csoportosítását teszik lehetővé. Két legfontosabb utasítása a COMMIT és a ROLLBACK.

Adatvezérlő utasítások (Data Control Language - DCL)

Az adatok tárolását vezérlő utasítások, ezek közé tartozik az adatbázisok létrehozására szolgáló parancs is.

Táblák létrehozása, karbantartása, törlése

Egy táblát a következő utasítással hozhatunk létre:

CREATE TABLE táblanév

(<oszlopnév> <adatleírás>

[,<oszlopnév> <adatleírás>]...)

táblanév a létrehozandó tábla neve

oszlopnév         egy oszlop neve

adatleírás az oszlopban tárolandó adatok típusának és méretének megadása

Az adatok méretét a típus után zárójelben lehet megadni. Karakteres oszlopoknál a karakterek számát adhatjuk meg, numerikus oszlopoknál pedig az összes jegyek, valamint a tizedesjegyek számát, vesszővel elválasztva.

Az oszlopok megadásánál a következő opciókat alkalmazhatjuk még:

NOT NULL az adott oszlopban nem lehet üres mező

UNIQUE az adott oszlopban minden sorban

különböző értéknek kell szerepelnie

DEFAULT <kifejezés> alapértelmezés szerint ebbe az oszlopba

a megadott kifejezés értéke

kerül, ha nem adunk meg mást az adatfeltöltéskor

PRIMARY KEY az oszlop a táblában elsődleges kulcs

Az elsődleges kulcs egyedi kulcs, ezért a UNIQUE opciót nem szabad a PRIMARY KEY opcióval együtt megadni.

Példák

CREATE TABLE hallgato

(hkod NUMBER(5) PRIMARY KEY,

nev CHAR(20),

szak CHAR(10),

evf NUMBER(1) NOT NULL,

csop NUMBER(1),

eredm NUMBER(3,2),

szuld DATE)

CREATE TABLE anyag

(akod NUMBER(4) UNIQUE,

anev CHAR(20),

meny_e CHAR(3) DEFAULT 'KG' )

Ha egy táblát meg akarunk szüntetni, ezt a következő utasítással tehetjük meg:

DROP TABLE <táblanév>

Egy táblába egy sort felvinni a következő utasítással lehet:

INSERT INTO <táblanév> [(<oszlopnév>[,<oszlopnév>...)]

táblanév egy létező tábla neve

oszlopnév                     a tábla egy oszlopának neve

érték                            az adott oszlopnak megfelelő típusú érték

Ha nem adunk meg oszlopneveket, akkor az új sorban a tábla minden oszlopának adunk értéket. Ha megadjuk az oszlopneveket, akkor csak azok az oszlopok kapnak értéket, amelyek a felsorolásban szerepelnek. A többi oszlop értéke - ha nem adtunk meg default értéket - NULL lesz. Ezek az oszlopok nem lehetnek NOT NULL opcióval definiálva.

Példák

INSERT INTO hallgato (hkod,nev,szak,evf)

VALUES (10001,'KEREK ERVIN','KONYVTAR',2)

INSERT INTO anyag VALUES(2233,'AGYAG','T')

Adatmódosításokat egy táblában a következő utasítással lehet végrehajtani:

UPDATE <táblanév>

SET <oszlopnév> = <kif> [,<oszlopnév> = <kif>...]

[WHERE <feltétel>]

táblanév egy létező tábla neve

oszlopnév                     a tábla egy oszlopának neve

kif                                az adott oszlopnak megfelelő típusú értéket eredményező kifejezés

feltétel                          logikai kifejezés, értéke igaz, vagy hamis

Az adatok módosítása abban a sorban, vagy sorokban történik meg, melyekre a feltétel igaz. A megadott oszlopokba az adott értékek kerülnek beírásra.

Példák

UPDATE hallgato

SET eredm=3.85 WHERE nev='KIS KATALIN'

UPDATE hallgato

SET eredm=0 WHERE evf=2

Egy táblából a következő utasítás segítségével törölhetünk sorokat:

DELETE [FROM] <táblanév> [WHERE <feltétel>]

táblanév egy létező tábla neve

feltétel                          logikai kifejezés, értéke igaz, vagy hamis

A táblából azon sor, vagy sorok törlődnek, melyekre a megadott feltétel igaz. Ha nem adunk meg feltételt, akkor minden sor törlődik.

Példák

DELETE FROM hallgato WHERE hkod=11234

DELETE FROM anyag

Egy táblához hozzáadhatunk új oszlopokat, vagy módosíthatjuk a meglevő oszlopok definícióját a következő utasítással:

ALTER TABLE <táblanév>

[ADD (<oszlopnév> <adatleírás>

[,<oszlopnév> <adatleírás>]...)]

[MODIFY (<oszlopnév> <adatleírás>

[,<oszlopnév> <adatleírás>]...)]

táblanév egy létező tábla neve

oszlopnév         egy oszlop neve

adatleírás az oszlopban tárolandó adatok típusának és méretének megadása

Az ADD opcióval új oszlopot tudunk hozzávenni a táblázathoz.

A MODIFY opcióval egy meglevő oszlop típusát, méretét és a NOT NULL opciót tudjuk megváltoztatni.

Példa

ALTER TABLE hallgato

ADD (szhely CHAR(20))

MODIFY (nev char(30))

COMMIT és ROLLBACK

Az ORACLE egy tranzakciónak tekinti az SQL utasítások egy sorozatát. A tranzakció egy COMMIT, vagy ROLLBACK utasítás utáni első SQL utasítással - illetve a legelső SQL utasítással - kezdődik és a következő COMMIT, vagy ROLLBACK utasításig - illetve a munka befejezéséig - tart.

A COMMIT utasítás megerősíti a tranzakció során elvégzett műveleteket, véglegesíti az adatokon történt változtatásokat. Hatására feloldódnak a tranzakció során létrejött lockolások.

A ROLLBACK utasítás hatására törlődnek a tranzakció során végrehajtott adatváltoztatások és feloldódnak a tranzakció során létrejött lockolások.

Ha egy tranzakció során a munka valamilyen oknál fogva megszakad, akkor az utolsó COMMIT utasítás utáni változtatások törlődnek.

Lekérdezések

Az SQL nyelv legfontosabb utasítása a SELECT utasítás, melynek segítségével egy vagy több adattáblából nyerhetünk információkat. A lekérdezés eredménye egy táblázat, amely megjeleníthető a képernyőn, vagy kinyomtatható, de lehetőség van a további feldolgozására is. Az utasítás formája:

SELECT [ ALL | DISTINCT ]

[, ]...

FROM <táblanév> [, <táblanév>]...

[ WHERE <feltétel> ]

[ GROUP BY <kif> [, <kif>]... [HAVING <feltétel>] ]

[ SELECT ... ]

[ ORDER BY [ASC | DESC]

[, [ASC | DESC]] ]...

táblanév egy létező tábla neve

kif                    lehet oszlopnév, vagy konstans, vagy ezekből különböző műveletekkel,

függvényekkel alkotott kifejezés

feltétel logikai kifejezés, értéke igaz, vagy hamis

A SELECT utasítással a FROM opcióban felsorolt táblák direkt szorzatából a FROM előtt felsorolt oszlopokat választjuk ki. Az erdménytábla sorait pedig a WHERE feltétellel definiálhatjuk. Ha nem adunk meg feltételt, akkor minden sort kiválasztunk a direkt szorzatból.

A különböző opciók jelentése:

ALL                             a lekérdezés eredményében a feltételnek megfelelő minden sor szerepel -

ez az alapértelmezés

DISTINCT a lekérdezés eredményében az azonos sorok csak egyszer

szerepelnek

a FROM opcióban megadott minden tábla minden oszlopa

szerepeljen az eredménytáblában

<táblanév>.* az adott tábla minden oszlopa szerepeljen az

eredménytáblában

A SELECT utasításban rendelkezhetünk a kiválasztott sorok csoportosításáról, rendezéséről is, erre szolgál a GROUP BY és az ORDER BY opció.

A GROUP BY opcióban megadhatunk egy, vagy több oszlopot, amelyben azonos értéket tartalmazó sorokat csoportosítani szeretnénk, általában valamilyen összesítés céljára.

Az ORDER BY opcióban azokat az oszlopokat definiálhatjuk, melyek szerint az eredménytábla rendezett lesz. Az ASC opció növekvő sorrendet jelent - ez az alapértelmezés - a DESC pedig csökkenőt.

A SELECT utasítás segítségével különböző relációalgebrai műveleteket valósíthatunk meg. Az oszlopok megadásával projekciót végzünk, a WHERE opcióban megadott feltétellel pedig szelekciót. Ha az utasítás több táblára vonatkozik, akkor ezen táblák direkt szorzatát készítjük el, amely részhalmazait a WHERE opcióban megadott feltételekkel választhatjuk ki (összekapcsolás, vagy join).

A SELECT utasításban definiálhatunk allekérdezést is, és képezhetjük lekérdezések egyesítését (UNION) , metszetét (INTERSECT) , vagy különbségét (MINUS).

A létezik (EXISTS) és minden (ALL) operátorok segítségével pedig a relációanalízist alkalmazhatjuk.

Példák SELECT utasításra

Példáinkban a következő táblákat fogjuk felhasználni:

Példák

SELECT * FROM hallgato

Ezzel az utasítással minden sort és oszlopot kiválasztunk a hallgato táblából.

Oszlopok kiválasztása

SELECT nev,eredm FROM hallgato

Ezzel az utasítással a hallgato táblából a nev és a jegy oszlopokat választjuk ki. Eredménye a következő tábla:

NEV EREDM

-------- ----- ------ ---------

KOVÁCS JÓZSEF 4.33

KEREK ANNA 3.96

SOMOGYI ERVIN 4.88

ANGYAL RÓZSA 2.56

SÓS ENDRE 3.76

SZABÓ TAMÁS 2.12

KISS TÜNDE 4.87

Sorok kiválasztása, rendezés

SELECT DISTINCT szak,evf,csop FROM hallgato

A fenti utasítás eredménytáblájának három oszlopa lesz, a szak, az evf, és a csop, és az ezen oszlopokban azonos értékeket tartalmazó sorok közül csak egy kerül át az eredménytáblába:

SZAK EVF CSOP

-------- ----- ------ -------------

GÉPÉSZ 5 4

KÖNYVTÁR 1 2

KÖNYVTÁR 1 3

KÖNYVTÁR 2 2

MEZŐGAZDÁSZ 3 1

SELECT * FROM hallgato WHERE szak='KÖNYVTÁR'

Ezzel az utasítással a hallgato táblából kiválasztunk minden oszlopot, de csak azok a hallgatók fognak szerepelni az eredménytáblában, akik a KÖNYVTÁR szakra járnak.

SELECT * FROM hallgato WHERE szak='KÖNYVTÁR' ORDER BY evf,csop

Az eredménytábla tartalma megegyezik az előzővel, csak a hallgatók évfolyam, azon belül pedig csoport szerinti rendezettségben szerepelnek benne:

HKOD NEV SZAK EVF CSOP EREDM -------- ----- ------ -------- ----- ------ ----- ----- ---------------- 10012 SZABÓ TAMÁS KÖNYVTÁR 1 2 2.12

10001 KOVÁCS JÓZSEF KÖNYVTÁR 1 3 4.33

10342 KISS TÜNDE KÖNYVTÁR 1 3 4.87

10054 SOMOGYI ERVIN KÖNYVTÁR 2 2 4.88

SELECT * FROM hallgato WHERE szak='KÖNYVTÁR'

ORDER BY evf,eredm DESC

Az eredménytábla ugyanazokat a sorokat tartalmazza, mint az előző, de a rendezettsége évfolyam szerint növekvő, eredmény szerint pedig csökkenő. Vagyis az azonos évfolyamra járó hallgatók eredmény szerint csökkenő sorrendben követik egymást:

HKOD NEV SZAK EVF CSOP EREDM

-------- ----- ------ -------- ----- ------ ----- ----- ----------------

10342 KISS TÜNDE KÖNYVTÁR 1 3 4.87

10001 KOVÁCS JÓZSEF KÖNYVTÁR 1 3 4.33

10012 SZABÓ TAMÁS KÖNYVTÁR 1 2 2.12

10054 SOMOGYI ERVIN KÖNYVTÁR 2 2 4.88

SELECT nev,eredm FROM hallgato WHERE eredm>3.5

Ezen utasítás hatására létrejövő eredménytábla csak azokat a sorokat fogja tartalmazni a hallgato táblából, ahol az ered oszlopban található érték 3.5-nél nagyobb:

NEV EREDM

-------- ----- ------ -----

KOVÁCS JÓZSEF 4.33

KEREK ANNA 3.96

SOMOGYI ERVIN 4.88

SÓS ENDRE 3.76

KISS TÜNDE 4.87

SELECT nev FROM hallgato WHERE szak IN('KÖNYVTÁR','GÉPÉSZ')

Az eredménytábla azon hallgatók nevét fogja tartalmazni, akik a KÖNYVTÁR, vagy a GÉPÉSZ szakra járnak:

NEV

----- ----- --------------

KOVÁCS JÓZSEF

SOMOGYI ERVIN

SÓS ENDRE

SZABÓ TAMÁS

KISS TÜNDE

SELECT nev,eredm FROM hallgato

WHERE eredm BETWEEN 3 AND 4.5

Az eredménytábla azon hallgatók nevét és eredményét fogja tartalmazni, akik eredménye 3.00 és 4.50 közé esik, illetve egyenlő valamelyikkel:

NEV EREDM

-------- ----- ------ --------

KOVÁCS JÓZSEF 4.33

KEREK ANNA 3.96

SÓS ENDRE 3.76

Összetett feltételt is megadhatunk logikai műveletek segítségével:

SELECT nev,eredm FROM hallgato

WHERE eredm>3.5 AND szak='KÖNYVTÁR'

Az eredménytábla a következő lesz:

NEV EREDM

-------- ----- ------ -----

KOVÁCS JÓZSEF 4.33

SOMOGYI ERVIN 4.88

KISS TÜNDE 4.87

SELECT nev,eredm FROM hallgato

WHERE eredm>4 OR eredm<3

Az eredménytábla ez lesz:

NEV EREDM

-------- ----- ------ -------

KOVÁCS JÓZSEF 4.33

SOMOGYI ERVIN 4.88

ANGYAL RÓZSA 2.56

SZABÓ TAMÁS 2.12

KISS TÜNDE 4.87

Ezt a kiválasztást a következőképpen is felírhatjuk:

SELECT nev,eredm FROM hallgato

WHERE eredm NOT (BETWEEN 3 AND 4)

Táblázatok összekapcsolása

SELECT tnev,vizsga.* FROM tantargy,vizsga

WHERE tantargy.tkod=vizsga.tkod

ORDER BY jegy DESC

Ezzel az utasítással elkészítjük a tantárgy és a vizsga tábla direkt szorzatát, majd ebből azokat a sorokat választjuk ki a WHERE opcióban megadott feltétel segítségével, amelyekben a két tantárgykód értéke megegyezik. Vagyis a tantárgy és a vizsga tábla természetes összekapcsolását készítjük el. A vizsga tábla egy sorához a tantárgy táblának az a sora fog kapcsolódni, amelyik az adott sorban szereplő tantárgyra vonatkozik. Az eredménytábla sorainak száma megegyezik a vizsga tábla sorainak számával. Az oszlopok közül a tantárgynév oszlopot és a vizsga táblában szereplő minden oszlopot kiválasztunk.

Az eredménytábla a jegyek szerinti csökkenő rendezettségű lesz:

TNEV HKOD TKOD JEGY

-------- ----- ------ -------- ----- ------ ----- ----- ----------------

INFORMATIKA 10103 102 5

SZOCIOLÓGIA 10001 105 5

KÖNYVTÁRI ISMERETEK 10001 101 4

SZOCIOLÓGIA 10276 105 4

ETIKA 10012 103 4

STATISZTIKA 10103 104 4

KÖNYVTÁRI ISMERETEK 10276 101 3

INFORMATIKA 10342 102 3

ETIKA 10004 103 3

KÖNYVTÁRI ISMERETEK 10342 101 2

INFORMATIKA 10054 102 2

STATISZTIKA 10004 104 1

SELECT nev,tnev,jegy FROM hallgato,tantargy,gyakjegy

WHERE hallgato.hkod=gyakjegy.hkod AND

gyakjegy.tkod=tantargy.tkod

Ezzel az utasítással három tábla direkt szorzatát készítjük el. Ebből azokat a sorokat választjuk ki, amelyekben a hallgatóhoz tartozó gyakorlati jegy és az adott tantárgyhoz tartozó leírás szerepel (természetes összekapcsolás). A létrejövő tábla minden sora egy hallgató nevét, egy tantárgy nevét és a hallgató abból elért eredményét tartalmazza. Egy hallgató annyi sorban szerepel, ahány tárgyból gyakorlati jegyet szerzett:

NEV TNEV JEGY

-------- ----- ------ -------- ----- ------ ----- ----- -------

KOVÁCS JÓZSEF KÖNYVTÁRI ISMERETEK 5

KOVÁCS JÓZSEF SZOCIOLÓGIA 3

SÓS ENDRE ETIKA 4

SÓS ENDRE STATISZTIKA 2

SZABÓ TAMÁS ETIKA 4

SOMOGYI ERVIN INFORMATIKA 2

KEREK ANNA INFORMATIKA 5

KEREK ANNA STATISZTIKA 5

ANGYAL RÓZSA KÖNYVTÁRI ISMERETEK 1

ANGYAL RÓZSA SZOCIOLÓGIA 4

KISS TÜNDE KÖNYVTÁRI ISMERETEK 3

KISS TÜNDE INFORMATIKA 4

SELECT DISTINCT nev FROM hallgato,vizsga

WHERE hallgato.hkod=vizsga.hkod AND jegy=1

Ezzel az utasítással azoknak a hallgatóknak a nevét kapjuk meg, akiknek van elégtelen vizsgajegye. Ha egy hallgatónak több elégtelen vizsgajegye van, akkor is csak egyszer fog a neve szerepelni az eredménytáblában a DISTINCT opció miatt.

Esetünkben csak egyetlen hallgatónak van elégtelen vizsgajegye, így az eredménytáblában csak egy név fog szerepelni: SÓS ENDRE.

SELECT DISTINCT tnev FROM tantargy,vizsga

WHERE tantargy.tkod=vizsga.tkod AND jegy<3

Ezzel az utasítással azon tantárgyneveket választottuk ki, amelyekből volt hármasnál rosszabb vizsgajegy. Az eredménytábla:

TNEV

-------- ----- ------ ------

INFORMATIKA

KÖNYVTÁRI ISMERETEK

STATISZTIKA

Egy táblát önmagával is össze lehet kapcsolni:

SELECT x.nev FROM hallgato x,hallgato y

WHERE x.eredm>y.eredm AND y.nev='KEREK ANNA'

Ezzel az utasítással azokat a hallgatókat választjuk ki a hallgato táblából, akiknek eredménye jobb, mint a KEREK ANNA nevű hallgatóé. Hogy meg tudjuk különböztetni a tábla kétféle felhasználását egymástól, helyettesítő neveket kellett bevezetnünk. Az x a hallgato tábla egyik neve, y pedig a másik. Az oszlopnevek megadásakor ezeket a helyettesítő neveket kell használni.

Két, vagy több eredménytábla egyesítése, metszete, különbsége

Két SELECT utasítás eredményének akkor lehet az egyesítését, metszetét, vagy különbségét képezni, ha az eredménytáblák azonos oszlopszámúak és a megfelelő oszlopok kompatibilis típusúak. Az oszlopneveknek nem kell azonosnak lenniük. Az eredménytáblába csak a különböző sorok kerülnek be.

SELECT hkod FROM vizsga WHERE jegy=1

UNION

SELECT hkod FROM gyakjegy WHERE jegy=1

Ezzel az utasítással azokat a hallgatókat választottuk ki, akiknek valamely tárgyból van elégtelen vizsga- , vagy gyakorlati jegye. Az eredménytábla:

HKOD

-----------

10004

10276

SELECT hkod FROM vizsga WHERE jegy=5

INTERSECT

SELECT hkod FROM gyakjegy WHERE jegy=5

Ezzel az utasítással azoknak a hallgatóknak a kódját kapjuk eredményül, akiknek volt jeles vizsgajegyük és gyakorlati jegyük is.

Az eredménytábla:

HKOD

-----------

10001

10103

SELECT hkod FROM vizsga WHERE jegy=5

MINUS

SELECT hkod FROM gyakjegy WHERE jegy!=5

Ezzel az utasítással azon hallgatók kódját választottuk ki, akiknek csak jeles gyakorlati jegyük van és volt jeles vizsgajegyük is.

Az eredménytábla:

HKOD

-----------

10103

Ha azokat a hallgatókat szeretnénk kiválasztani, akiknek csak jeles vizsga- és gyakorlati jegyük van, ezt a következő utasítással érhetjük el:

SELECT hkod FROM vizsga WHERE jegy=5

MINUS

SELECT hkod FROM vizsga WHERE jegy!=5

MINUS

SELECT hkod FROM gyakjegy WHERE jegy!=5

Láthatjuk, hogy azonos táblára vonatkozó SELECT-ek eredményét is összekapcsolhatjuk egymással. Esetünkben a fenti utasítás eredménye üres tábla lesz, mert nincs ilyen hallgató a táblákban.

Csoportképzés, oszlopfüggvények

Gyakran szükségünk van arra, hogy a kiválasztott adatokról valamilyen összesített információt kapjunk, esetleg valamilyen csoportosítás szerint. Az oszlopfüggvények arra szolgálnak, hogy egy-egy oszlop adatain végezzenek műveletet.

A leggyakrabban használt oszlopfüggvények a következők:

SUM(<oszlopnév>)                 összegzi az oszlopban szereplő értékeket

MIN(<oszlopnév>) az oszlopban szereplő legkisebb értéket adja

eredményül

MAX(<oszlopnév>)                 az oszlopban szereplő legnagyobb értéket adja

eredményül

AVG(<oszlopnév>) az oszlopban szereplő értékek átlagát számítja ki

COUNT megadja a kiválasztott sorok számát

COUNT(<oszlopnév>) megadja azon kiválasztott sorok számát,

ahol az adott oszlop értéke nem NULL

Az oszlopfüggvények - a COUNT(*) kivételével - azokat a sorokat figyelmen kívül hagyják, ahol az adott oszlopban NULL érték található.

Az oszlopfüggvények paraméterezésénél - a COUNT(*) kivételével - megadható a DISTINCT opció, melynek hatására a kiválasztott értékek közül csak a különbözőeket veszi figyelembe a függvény.

Példák

SELECT COUNT(*) FROM hallgato

A fenti utasítás segítségével azt kapjuk meg, hogy hány hallgató van a hallgato táblában.

SELECT COUNT(*) FROM hallgato WHERE szak='KÖNYVTÁR'

Ezen utasítás eredménye az, hogy hány KÖNYVTÁR szakos hallgató van a hallgato táblában.

SELECT COUNT(DISTINCT evf) FROM hallgato

Ezen utasítás eredményeként azt kapjuk meg, hogy hány féle évfolyamra járnak a hallgatók.

SELECT AVG(eredm) FROM hallgato WHERE evf=1

Ezen utasítás eredményeként megkapjuk az elsős hallgatók átlagos tanulmányi eredményét.

SELECT MAX(eredm) FROM hallgato

Ezzel az utasítással a legjobb tanulmányi eredmény értékét kapjuk meg.

A sorokat a GROUP BY opció segítségével csoportosíthatjuk. Az opcióban megadott oszlopban azonos értékkel rendelkező sorok egy csoportot alkotnak. Az opcióban több oszlopot is megadhatunk. Ekkor bármelyik oszlop megváltozása új csoportot képez.

Példák

SELECT szak,MAX(eredm) FROM hallgato GROUP BY szak

Az eredménytábla a következő lesz:

SZAK MAX(EREDM)

-------- ----- ------ ----- ----- -----

GÉPÉSZ 3.76

KÖNYVTÁR 4.88

MEZŐGAZDÁSZ 3.96

SELECT szak,evf,MAX(eredm) FROM hallgato GROUP BY szak,evf

Az eredménytábla a következő lesz:

SZAK EVF MAX(EREDM)

-------- ----- ------ ----- ----- --------- ----- ------

GÉPÉSZ 5 3.76

KÖNYVTÁR 1 4.87

KÖNYVTÁR 2 4.88

MEZŐGAZDÁSZ 3 3.96

Ha a csoportképzést valamilyen feltételhez akarjuk kötni, ezt a HAVING opcióban adhatjuk meg.

Példa

SELECT evf,MIN(eredm) FROM hallgato GROUP BY evf HAVING evf<3

Hatására az első két évfolyam legrosszabb tanulmányi eredményét kapjuk meg:

EVF MIN(EREDM)

----- ----- --------- ----- -------

1 2.12

2 4.88

Belső SELECT-ek (subqueries)

Egy SELECT utasítás WHERE feltételében állhat egy újabb SELECT utasítás (allekérdezés). Erre általában akkor van szükség, ha a sorok kiválasztása valamely más táblázatban található információn alapul. A belső SELECT hivatkozhat ugyanarra a táblázatra is, mint a külső. Ez akkor fordul elő, ha nem tudjuk egyetlen kifejezésben megfogalmazni a leválogatási feltételt. A belső SELECT-ben szerepelhet oszlopfüggvény is.

A belső SELECT-ek használata több szintű is lehet, vagyis egy belső SELECT-nek is lehet belső SELECT-je. A lekérdezések végrehajtása belűről kezdődik és kifelé halad. A legbelső SELECT hajtódik végre legelőször, majd sorban a fölötte levő szintek.

Példák

SELECT nev FROM hallgato

WHERE evf=(SELECT evf FROM hallgato WHERE nev='KOVÁCS JÓZSEF')

Ezzel az utasítással azoknak a hallgatóknak a nevét kapjuk meg, akik Kovács Józseffel egy évfolyamra járnak. A belső SELECT-ben meghatározzuk , hogy melyik évfolyamra jár Kovács József, a külső SELECT segítségével pedig azoknak a hallgatóknak a nevét választjuk ki, akiknél az évfolyam értéke megegyezik a belső SELECT eredményével.

SELECT nev,eredm FROM hallgato

WHERE eredm=(SELECT MAX(eredm) FROM hallgato)

Ezzel az utasítással megkapjuk a hallgato táblázatból a legjobb eredményt elért hallgató nevét és eredményét. A belső SELECT kiválasztja az eredményoszlopból a legnagyobb értéket, a külső SELECT segítségével meghatározzuk azokat a hallgatókat, akiknek az eredménye megegyezik ezzel az értékkel.

Az egyenlőségként megfogalmazott belső SELECT-nek mindig egyetlen értéket kell eredményeznie. Az előző belső SELECT csak egyetlen értéket ad eredményül, hiszen a legjobb eredmény értéke egyetlen érték akkor is, ha több hallgató is elérte azt. A külső SELECT eredményezhet több sort is:

SELECT tkod FROM gyakjegy

WHERE jegy=(SELECT max(jegy) FROM gyakjegy)

A belső SELECT eredménye egyetlen érték (5), a külső SELECT azonban azoknak a tantárgyaknak a kódját fogja kiválasztani a gyakjegy táblából, amelyekből volt 5-ös gyakorlati jegy. Így a következő eredményt kapjuk:

TKOD

-----------

101

104

102

Ha a belső SELECT több értéket eredményez, akkor a külső SELECT-ben az IN operátort alkalmazhatjuk:

SELECT nev FROM hallgato

WHERE hkod IN (SELECT hkod FROM vizsga WHERE jegy=1)

Ezzel az utasítással azoknak a hallgatóknak a nevét kapjuk eredményül, akiknek elégtelen vizsgajegye van. A belső select a vizsga táblából leválogatja azokat a hallgatókódokat, amelyekhez elégtelen vizsgajegy tartozik. A külső SELECT segítségével a hallgató táblából leválogatjuk a hallgatókódokhoz tartozó neveket. Láthattuk az előzőekben, hogy ezt az információt a hallgato és a vizsga táblák összekapcsolásával is megkaphatjuk.

A SELECT utasításban alkalmazható további operátorok

ALL (lista) relációs operátorral együtt alkalmazható.

Akkor ad igaz értéket eredményül, ha a reláció a lista minden elemére teljesül. A lista helyett belső SELECT is állhat.

Példa:

SELECT nev FROM hallgato WHERE

eredm > All (SELECT eredm FROM hallgato WHERE evf=1)

Ezzel az utasítással azoknak a hallgatóknak a nevét kapjuk eredményül, akiknek minden első éves hallgatónál jobb a tanulmányi eredményük. Esetünkben egy ilyen hallgató van: SOMOGYI ERVIN.

Ezt a lekérdezést az ALL kulcsszó helyett a MAX függvény segítségével is felírhattuk volna:

SELECT nev FROM hallgato WHERE evf>1 AND

eredm > (SELECT MAX(eredm) FROM hallgato WHERE evf=1)

ANY (lista) relációs operátorral együtt alkalmazható.

Akkor ad igaz értéket eredményül, ha a reláció a lista valamelyik elemére teljesül. A lista helyett belső SELECT is állhat.

Példa:

SELECT nev FROM hallgato WHERE evf>1 AND

eredm > ANY (SELECT eredm FROM hallgato WHERE evf=1)

Ezzel az utasítással azoknak a nem elsős hallgatóknak a nevét kapjuk meg, akik eredménye jobb valamely első éves hallgató eredményénél:

NEV

----- ----- ----------------

KEREK ANNA

SOMOGYI ERVIN

ANGYAL RÓZSA

SÓS ENDRE

Ezt a lekérdezést az ANY kulcsszó helyett a MIN függvény segítségével is felírhattuk volna:

SELECT nev FROM hallgato WHERE evf>1 AND

eredm > (SELECT MIN(eredm) FROM hallgato WHERE evf=1)

EXISTS belső SELECT-tel együtt alkalmazható. Akkor ad igaz eredményt, ha a belső SELECT legalább egy sort eredményezett.

Példa:

SELECT nev FROM hallgato

WHERE EXISTS (SELECT * FROM vizsga

WHERE vizsga.hkod=hallgato.hkod AND tkod=104)

Ezzel az utasítással azon hallgatók nevét kapjuk eredményül, akiknek van vizsgajegyük a 104-es kódú tantárgyból. A belső SELECT segítségével azt vizsgáljuk meg a hallgató tábla minden sorára, hogy a vizsga táblában van-e olyan sor az adott hallgatókóddal, amelyben a tantárgykód értéke 104. Ha van ilyen sor, akkor a hallgató neve bekerül az eredménytáblába, egyébként pedig nem.

A példában egy speciális típusú belső SELECT-et alkalmaztunk (correlated query). Itt a belső SELECT a külső SELECT-ben szereplő tábla minden sorára kiértékelődik. Ez azért történik így, mert a belső SELECT WHERE opciójában kapcsolatot határoztunk meg a külső SELECT-ben szereplő tábla és a belső SELECT-ben szereplő tábla között. Más típusú belső SELECT-ek esetén a belső SELECT csak egyszer értékelődik ki.

Ezt a lekérdezést másképpen is megfogalmazhatjuk:

SELECT nev FROM hallgato WHERE hkod IN (SELECT hkod FROM vizsga WHERE tkod=104)

A következő lekérdezéssel azon hallgatók nevét kapjuk meg, akiknek van vizsgajegyük statisztikából.

SELECT nev FROM hallgato WHERE hkod IN (SELECT hkod FROM vizsga WHERE tkod=(SELECT tkod FROM tantargy WHERE tnev='STATISZTIKA'))

Ebben a megfogalmazásban két egymásba ágyazott belső SELECT-et alkalmaztunk. A második belső SELECT a tantárgy táblából meghatározza a statisztika nevű tantárgy kódját. Az első belső SELECT pedig azon hallgatókódokat választja ki a vizsga táblából, akiknek van jegyük a második belső SELECT-tel meghatározott tantárgykódú tantárgyból. A külső SELECT azon hallgatók nevét eredményezi, akiknek a kódja szerepel az első belső SELECT eredménylistájában.

Az ALL, ANY és EXISTS operátorok mindegyike előtt használható a NOT kulcsszó. Ekkor a művelet eredményének negált értékét kapjuk.

Példa:

SELECT nev FROM hallgato

WHERE NOT EXISTS (SELECT * FROM vizsga

WHERE vizsga.hkod=hallgato.hkod AND jegy=1)

Ezzel a SELECT utasítással azoknak a hallgatóknak a nevét válogatjuk le, akiknek nincs elégtelen vizsgajegye.

A SELECT utasítás segítségével egy-egy lekérdezést többféleképpen is megfogalmazhatunk. Célszerű azt a megfogalmazást választani, amely az adott szempontokat a legáttekinthetőbben, a legolvashatóbban írja le. Nagy adatállományok esetén azonban az elsődleges szempont az, hogy melyik megfogalmazás biztosítja az adatok leggyorsabb megkeresését. (Ez pedig függ az SQL nyelv konkrét megvalósításától.)

Nézettáblák

Az SQL nyelvben lehetőségünk van arra, hogy egy vagy több táblából nézettáblát hozzunk létre, melynek segítségével az adatokat a számunkra könnyebben kezelhető formában láthatjuk. A nézettábla csak logikai tábla, fizikailag nem létezik. Az adatbázisban a nézettábla kialakítására szolgáló lekérdezés kerül tárolásra.

Nézettáblát az alábbi utasítással lehet létrehozni:

CREATE VIEW <név> AS <lekérdezés>

név                               a nézettábla neve

lekérdezés                    azon táblák oszlopainak és sorainak megadása, amelyeken a

nézettábla alapul. Általában ez egy SELECT utasítás, amely

nem tartalmazhat ORDER BY opciót.

Nézettáblákat bármelyik SQL utasításban használhatunk, ahol táblák használata megengedett, azzal a megszorítással, hogy amennyiben a nézettábla nem egyetlen tábla adatain alapul, vagy a nézettáblát definiáló lekérdezésben a GROUP BY opció szerepel, akkor csak lekérdezhetünk adatokat a nézettáblából. Egyébként módosításokat is végezhetünk a nézettáblában szereplő adatokon.

Példák

CREATE VIEW atlag AS SELECT hkod,nev,eredm FROM hallgato

Ezen utasítás hatására létrejön az atlag nézettábla, amely a hallgato táblából a hkod, nev, eredm oszlopokat tartalmazza.

CREATE VIEW statjegy

AS SELECT nev,evf,csop,jegy FROM hallgato,vizsga

WHERE hallgato.hkod=vizsga.hkod AND

tkod=(SELECT tkod FROM tantargy

WHERE tnev='STATISZTIKA')

A statjegy nézettábla a hallgato és a vizsga táblákon alapul. Az belső SELECT a vizsga táblából csak azokat a sorokat választja ki, ahol a tantárgy a statisztika.

A nézettáblákat a következő utasítással szüntethetjük meg:

DROP VIEW <név>

név                               a nézettábla neve

Procedurális SQL

Az SQL nyelvhez kifejlesztették annak procedurális változatát, a PL/SQL nyelvet.

A PL/SQL tartalmazza az SQL utasításokat, továbbá vezérlő utasításokat - elágazást, kilépést, ciklusszervező utasításokat és értékadó utasítást. Ezt a nyelvet a fejlesztők használják, hatékony és fontos eszköze az alkalmazások készítésének.