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.