A lekérdező nyelv
A lekérdező nyelv egyetlen utasításból áll, mely számos alparancsot tartalmazhat, és a lekérdező utasítások többszörös mélységben egymásba ágyazhatók. A SELECT utasítás általános alakjának megadása helyett részletesen áttekintjük az egyes tipikus lekérdezési utasításokat, az egyszerűektől a komplikáltakig. Figyelem, a szelekció művelete és a SELECT utasítás csak nevében hasonló, egymásnak nem felelnek meg.
Először tekintsük át az egy relációra vonatkozó lekérdezéseket. A projekció műveletét a következő utasítással valósíthatjuk meg:
SELECT [DISTINCT]
attribútum_név, attribútum_név, ... FROM reláció_név;
A megadott reláció felsorolt attribútumai jelennek meg az utasítás hatására soronként. A DISTINCT módosító megadása esetén csak az egymástól különböző sorok láthatók.Például a Diakok reláció Diak_azonosito és Nev attribútumainak lekérdezése a
SELECT Diak_azonosito, nev FROM Diakok;
paranccsal történhet.
A különböző tanár-tantárgy párosítások lekérdezése az Orarend relációból a következő paranccsal történhet:
SELECT DISTINCT Tanar_azonosito, Tantagy FROM Orarend;
A szelekció művelet megvalósítása esetén a SELECT utasítást egy feltétellel egészítjük ki:
SELECT attribútum_név, attribútum_név, ... FROM reláció_név
WHERE feltétel;
Ha az attribútum nevek helyett csak "*"-ot adunk meg, akkor az eredményben a reláció valamennyi attribútuma szerepelni fog:
SELECT * FROM Diakok WHERE osztaly = '3/b';
Megadott attribútumok esetén a projekció és a szelekció művelete összevonható egy utasítással:
SELECT idopont, tantargy FROM orarend WHERE osztaly = '3/b';
A keresési feltételben szerepelhetnek összehasonlító operátorok, melyek numerikus, karakteres és dátum típusú adatok esetén is használhatóak.
Összehasonlító operátorok
Operátor Értelmezés
= egyenlő
!= <> ^= nem egyenlő
> nagyobb
>= nagyobb egyenlő
"<" kisebb
"<=" kisebb egyenlő
Az összehasonlító operátorok segítségével attribútumokat és konstansokat hasonlíthatunk össze. A szöveg és dátum konstansokat idézőjelek között kell megadni. Az alapértelmezés szerinti dátum formátum nap-hónap-év. A hónap a hónap nevének három betűs angol rövidítése, az év pedig évezred és évszázad nélkül értendő. Az SQL tartalmaz olyan összehasonlító operátorokat is, melyek nem egy adott értékkel, hanem az értékek egy halmazával történő összehasonlítást eredményeznek.
Összehasonlító operátorok halmazokra
Operátor Értelmezés
BETWEEN x AND y adott értékek közé esik
IN (a, b, c, ...) az értékek között található
LIKE minta hasonlít a mintára
Az IN esetén egy halmazt adhatunk az elemek felsorolásával. A LIKE operátort karakteres mezők összehasonlítására alkalmazhatjuk. Két speciális karakter adható meg a mintában, a % jel tetszőleges hosszúságú karakter sorozatot helyettesít, az _ aláhúzás karakter pedig egy tetszőleges karaktert. Például:
Szöveges minta megadása
Operátor Értelmezés
LIKE 'a%' minden 'a' betűvel kezdődő
LIKE 'x_' minden 'x'-el kezdődő kétbetűs
LIKE '%a%' minden 'a' betűt tartalmazó
LIKE '_a%x' második betű 'a' és 'x'-re végződő
Az attribútumokra használható még egy speciális összehasonlító operátor, az IS NULL, melyek segítségével eldönthetjük, hogy a mező ki van-e töltve. Több keresési feltételt is összekapcsolhatunk a logikai operátorokkal, illetve segítségükkel a halmaz műveleteket valósíthatjuk meg.
Logikai operátorok
Operátor Értelmezés
NOT Logikai tagadás
AND Logikai és
OR Logikai vagy
Az összehasonlító operátorok precedenciája (végrehajtási sorrendje) csökkenő sorrendben:
=, !=, <>, ^=, >, >=, <, <=
NOT
AND
OR
Nézzünk néhány példát a lekérdezésekre. A 3.a osztályba járó diákok adatai:
SELECT * FROM Diakok WHERE Osztaly = '3/a';
A matematikát tanító tanárok azonosítói (DISTINCT itt is használható!):
SELECT DISTINCT Tanar_azonosito FROM Orarend WHERE Tantargy = 'matematika';
A 'C' betűvel kezdődő nevű diákok:
SELECT Nev, Osztaly FROM Diakok WHERE nev = 'C%';
A 3/a-ba járó diákok, akiknek nincs otthon telefonjuk: SELECT Nev FORM Diakok WHERE Osztaly = '3.a' AND Telefon IS NULL;
Termek, ahol matematika vagy informatika órát tartanak:
SELECT Terem FROM Orarend WHERE Tantargy = 'matematika' OR tantargy = 'informatika';
vagy ugyanez az IN felhasználásával:
SELECT Terem FROM Orarend WHERE Tantargy IN ('matematika', 'informatika');
Matematikából hármas és ötös közötti osztályzatot szerzett diákok:
SELECT Diak_azonosito FROM Osztalyzatok WHERE Osztalyzat BETWEEN 3 AND 5 AND tantargy = 'matematika';
Telefonnal rendelkező diákok:
SELECT Diak_azonosito FROM Diakok WHERE NOT IS NULL Telefon;
Az eddigi lekérdezések eredményei a sorok tárolt sorrendjében kerültek kiírásra. Az SQL lehetőséget biztosít a lekérdezés eredménysorainak rendezésére az ORDER BY alparancs segítségével.
SELECT attribútum, attribútum, ... FROM reláció
[WHERE feltétel]
ORDER BY attribútum [ASC|DESC], attribútum
[ASC | DESC], ...;
Az ORDER BY után megadott attribútumok alapján ASC esetén (ez az alapértelmezés) növekvő, vagy DESC esetén csökkenő sorrendbe rendezi az eredmény sorait. Ha több attribútumot adunk meg a rendezési feltételben, akkor a megadás sorrendje alapján történik a rendezés, azaz először az elsőnek megadott attribútum alapján rendezi sorba a sorokat, ha ez az attribútum azonos két sorra, akkor a másodikként megadott attribútum alapján, és így tovább. Karakteres attribútumoknál a rendezés a karakterek kódjai alapján történik (ASCII vagy EBCD), azaz például nevek esetén az angol ABC szerint. Például a 3/a osztály órarendje időrendi sorrendben az alábbi lekérdezéssel kapható meg:
SELECT Idopont, Tantargy, Terem FROM Orarend WHERE Osztaly = '3/a'
ORDER BY Idopont;
Egy terem elfoglaltsága időrendben:
SELECT Idopont, Tantargy, Osztály FROM Orarend WHERE Terem = 104
ORDER BY Idopont;
Osztályonkénti névsor az összes diákra:
SELECT Nev, Osztály FROM Diakok ORDERED BY Osztaly, Nev;
A lekérdezés eredményét csoportosíthatjuk és a csoportok között is további szelekciót alkalmazhatunk a GROUP BY és HAVING alparancsokkal.
SELECT attribútumok FROM reláció
[WHERE feltétel]
GROUP BY attribútum
[HAVING csoport_feltétel];
A GROUP BY alparancs után megadott attribum azonos értékei alapján csoportosítja az SQL a lekérdezés eredményeit és a csoport feltételnek megfelelő sorok kerülnek az eredménybe. A csoportok képzésekor az eredmények között az azonos csoportba tartozó sorokból levezetett további eredmények megjelenítésére is lehetőséget biztosít az SQL. Ez a következő függvényekkel valósítható meg:
Függvények halmazokra és eredményeik
az alábbi adatok esetén: 1, 2, 3, 4, 1, 4, 4, NULL, 5
Függvény Értelmezés Eredmény
ALL DISTINCT
AVG (attribútum) átlag 3 3
COUNT (attribútum) nem NULL elemek száma 8 5
COUNT (*) sorok száma NULL is 9 6
MAX (attribútum) maximális elem 5 5
MIN (attribútum) minimális elem 1 1
SUM (attribútum) összeg 24 15
STDDEV(attribútum) szórás
A függvények argumentuma előtt megadható a DISTINCT vagy ALL módosító. DISTINCT esetén csak a különböző értékek, ALL esetén minden érték részt vesz a számításban. Az alapértelmezés az ALL. A definiálatlan, NULL értékek nem szerepelnek a számításban.
Az ötnél nagyobb létszámú osztályok:
SELECT Osztály, COUNT (*) FROM Diakok GROUP BY Osztaly
HAVING COUNT (*) > 5
Diákok tanulmányi átlaga matematikából:
SELECT Diak_azonosito, AVG (Osztalyzat)
FROM Osztalyzatok
WHERE tantargy = 'matematika'
GROUP BY Diak_azonosito;
A függvények alkalmazhatók a GROUP BY nélkül is. Például a tanárok száma:
SELECT COUNT (*) FROM Tanarok;
Matematika osztályzatok száma és átlaga:
SELECT COUNT (*)', AVG (Osztalyzat) FROM Osztalyzatok
WHERE Tantargy = 'matematika';
A lekérdezésekben az attribútumok és a csoportokra vonatkozó függvények mellett az attribútumok aritmetikai kifejezéseit is használhatjuk. A kifejezésekben az alapműveletek használhatók, illetve zárójelek a műveletek végrehajtási sorrendjének módosítására. A karakteres adatok konkatenálására a || operátor használható.
Osztályzatok kiírása fordítva, vagyis az egyes a legjobb:
SELECT Diak_azonosito, 6 - Osztályzat FROM Osztalyzatok;
A kifejezésekben az alapműveletek mellett számos függvényt is használhatunk a karakteres, numerikus és dátum tipusú adatokra.
Karakteres függvények
Függvény Magyarázat Példa
ASC (szöveg) A szöveg első karakterének ASCII kódja ASC ('abc') = 65
CHR (egész) A számnak megfelelő kódú karakter CHR (65) = 'a'
INITCAP (szöveg) A szavak kezdőbetűit nagybetűvé INITCAP ('ló pál') = 'Ló Pál'
INSTR (szöveg1, szöveg2, kezdet, hányadik) A szöveg1-ben a szöveg2 hányadik előfordulása a kezdettől. Hányadik és kezdet elmaradhat INSTR ('abcd', 'cd') = 3
LENGTH (szöveg) A szöveg hosszát adja LENGTH ('abc') = 3
LOWER (szöveg) Kisbetűssé alakítja a szöveget LOWER ('ABC') = 'abc'
LPAD (szöveg, hossz, karakterek) A szöveget kiegészíti balról a megadott karakterekkel az adott hosszig, Karaktereket nem kötelező megadni, ekkor szóköz. LPAD ('x', 3) = ' x'
LPAD ('x', 5, '12') = '1212x'
LTRIM (szöveg, karakterek) A szöveg elejéről levágja a karakterekkel egyező részt. Karaktereket nem kötelező megadni, ekkor szóköz. LTRIM (' x') = 'x
LTRIM 'KUKURIKU', 'UK' = 'RIKU'
RPAD (szöveg, hossz, karakter) A szöveget kiegészíti jobbról a megadott karakterekkel az adott hosszig, Karaktereket nem kötelező megadni, ekkor szóköz RPAD ('x', 3) = 'x '
RPAD ('x', 5, '12') = 'x1212'
RTIM (szöveg, karakter) A szöveg végéről levágja a karakterekkel egyező részt. Karaktereket nem kötelező megadni, ekkor szóköz. LTRIM ('x ') = 'x'
LTRIM ('KUKURIKU', 'UKI' = 'KUKUR'
SUBSTR (szöveg, kezdet, hossz) A szöveg része a kezdet pozíciótól adott hosszban. Hossz nélkül a szöveg végéig SUBSTR ('abcd', 2, 1) = 'b'
SUBSTR ('abcd', 3) = 'cd'
TRANSLATE (szöveg, mit, mire) A szövegben előforduló mit karaktereket kicseréli a mire karaktereire TRANSLATE ('abc', 'ab', 'AB') = 'ABc')
UPPER (szöveg) Nagybetűssé alakítja a szöveget UPPER ('abc') = 'ABC'
Nem minden függvény található meg minden adatbáziskezelőben, előfordulhat, hogy más névvel találja meg.
Numerikus függvények
Függvény Magyarázat Példa
ABS (érték) Abszolút érték) ABS (-1) = 1
CEIL (érték)) Az értéknél nagyobb vagy egyenlő legkisebb egész) CEIL (6.12) = 7
FLOOR (érték)) Az értéknél kisebb vagy egyenlő legnagyobb egész) FLOOR (3.95) = 3
MOD (érték, osztó)) Osztási maradék) MOD (8, 3) = 2
POWER (érték, kitevő)) Hatványozás) POWER (3, 4) = 81
ROUND (érték, pontosság)) Kerekítés a megadott jegyig. Negatív pontosság is megadható.) ROUND (123.456, 1) = 123,5 ROUND (163.456,-2) = 200
SIGN (érték)) Előjel függvény) SIGN (-3) = -1
SQRT (érték)) Négyzetgyök vonás) SQRT (144) = 12
TRUNC (érték, pontosság)) Csonkítás a megadott jegyig. (Negatív pontosság is megadható.) TRUNC (123.456, 1) = 123.4 TRUNC (163.456,-2) = 100
Nem minden függvény található meg minden adatbáziskezelőben, előfordulhat, hogy más névvel találja meg.
Dátum függvények
Függvény Magyarázat Példa
ADD_MONTH (dátum, n) A dátumhoz n hónapot ad ADD_MONTH ('10-MAY-93',2) = '10-JUL-93'
LAST_DAY (dátum) A dátumban szereplő hónap utolsó apja LAST_DAY ('1-JAN-93') = '31-JAN-93')
MONTH_BETWEEN (dátum1, dátum2) A két dátum közötti idő hónapokban MONTH_BETWEEN (
NEXT_DAY (dátum, nap) A dátum utáni első nap nevű napra eső dátum NEXT_DAY ('10-MAY-93','TUESDAY') = '11-MAY-93')
ROUND (dátum, formátum) Dátum kerekítése a megadott formátum szerint
TO_CHAR (dátum, formátum) Dátum megadott karakteres formátumba konvertálása TO_CHAR (4-DEC-58, 'YY.MM.DD') = '58.12.04'
TO_DATE (szöveg, formátum) A szöveg dátummá alakítása a formátum szerint TO_DATE ('58.12.04', 'YY.MM.DD') = 4-DEC-58
TRUNC (dátum, formátum) Dátum csonkítása a megadott formátum szerint
Nem minden függvény található meg minden adatbáziskezelőben, előfordulhat, hogy más névvel találja meg.
A SELECT utasítás a relációk közötti szorzás művelet végrehajtására is alkalmas. Ehhez több reláció nevét kell megadni a FROM után. Például az összes lehetséges diák és tanár azonosító pár visszaírása:
SELECT diak_azonosito, tanat_azonosito FROM Diakok, Tanarok;
Ennél komplikáltabb szorzás műveletet is megfogalmazhatunk. Például a 3/a osztályban rendezendő körmérkőzéses sakk bajnokság összes lehetséges párosításának előállítása:
SELECT A.nev, B.nev
FROM Diakok A, Diakok B WHERE A.osztaly = '3/a' AND B.osztaly = '3/a' AND
A.diak_azonosito <> B.diak_azonosito;
A különböző diák azonosítókra vonatkozó feltétel megadására azért van szükség, hogy az önmagukkal alkotott párok ne kerüljenek be a lekérdezés eredményébe.
A SELECT SQL utasítás segítségével eddig is sokfajta kérdésre kaptuk meg közvetlenül a választ. Az összekapcsolás segítségével még komplexebb kérdéseket oldhatunk meg egy lépésben.
SELECT [reláció.]attribútum, [reláció.]attribútum, ...
FROM relációk
WHERE kapcsoló_attribútum operátor
kapcsoló_attribútum;
Az összekapcsolás esetén mindig legalább két relációval dolgozunk. Ha a két relációban azonos az attribútumok neve, akkor minősített névvel, reláció.attribútum segítségével hivatkozhatunk a kivánt elemre.
A diákok neve mellé írjuk ki az osztály, tantárgy és osztályzat értéket:
SELECT Nev, Osztaly, Tantargy, Osztalyzat FROM Diakok, Osztalyzatok
WHERE Diakok.Diak_azonosito = Osztalyzatok.Diak_azonosito;
Az előző egy egyen összekapcsolás volt, de például a 12 azonosítójú diák osztályzatainál jobbat szerzők listája is előállítható az egy reláción végrehajtott összekapcsolással:
SELECT DISTINCT A.Diak_azonosito, A.Osztalyzat
FROM Osztalyzatok A, Osztalyzatok
WHERE B.Osztalyzat < A.Osztalyzat AND B.Diak_azonosito = 12;
Ebben a lekérdezésben az 'A' és 'B' ugyanannak a relációnak az alias nevei, melyek segítségével két sort vizsgálhatunk ugyanabból a relációból. Az előző lekérdezést módosítsuk úgy, hogy a diákok neve is megjelenjen:
SELECT Nev, A.Osztalyzat FROM Osztalyzatok A, Osztalyzatok B, Diakok
WHERE B.Osztalyzat < A.Osztalyzat AND B.Diak_azonosito = 12; AND
Diakok.Diak_azonosito = A.Diak_azonosito;
Az SQL szabvány egy másik megoldást is biztosít a belső összekapcsolásra az INNER JOIN kulcsszavakkal:
SELECT [reláció.]attribútum, [reláció.]attribútum, ...
FROM első_tábla INNER JOIN második tábla
ON első_tábla.kulcs_mező = második_tábla.kulcs_mező;
A korábbi példa, a diákok neve mellé írjuk ki az osztály, tantárgy és osztályzat értéket, így is megfogalmazható:
SELECT Nev, Osztaly, Tantargy, Osztalyzat FROM Diakok INNER JOIN Osztalyzatok
ON Diakok.Diak_azonosito = Osztalyzatok.Diak_azonosito;
A LEFT JOIN illetve RIGHT JOIN kulcsszavakkal az úgynevezett külső összekapcsolás is megvalósítható. Ebben az esetben az egyik tábla minden sora megjelenik az eredményben, akkor is, ha nincs az összekapcsolás feltételének megfelelő sor a másik táblában. A LEFT JOIN esetén az első tábla, míg a RIGHT JOIN esetén a második tábla minden sora bekerül az eredmény táblába. A külső összekapcsolás nem fogalmazható meg a WHERE feltételben.
SELECT [reláció.]attribútum, [reláció.]attribútum, ...
FROM első_tábla LEFT JOIN második tábla
ON első_tábla.kulcs_mező = második_tábla.kulcs_mező;
Az előző példát alakítsuk át úgy, hogy azoknak a diákoknak a neve is megjelenjen a listában akiknek még nincs osztályzatuk:
SELECT Nev, Osztaly, Tantargy, Osztalyzat FROM Diakok LEFT JOIN Osztalyzatok
ON Diakok.Diak_azonosito = Osztalyzatok.Diak_azonosito;
Az összekapcsolás művelete mellett a lekérdezések további egymásba ágyazása is lehetséges. A WHERE alparancsban az összehasonlítás egyik oldalán szerepelhet egy újabb, zárójelbe tett SELECT utasítás. A mélyebben elhelyezkedő SELECT utasítás több sort is visszaadhat a relációból. Ezeket az eseteket az ANY, ALL és EXISTS segítségével is kezelhetjük. Az ANY és ALL esetén az összehasonlítás egyik oldalára egy listát írhatunk, mely származhat egy beágyazott lekérdezésből is. A legrosszabb osztályzat matematikából:
SELECT DISTINCT Osztalyzat FROM Osztalyzatok
WHERE Tantargy = 'matematika' AND Osztalyzat <= ALL
(SELECT Osztalyzat FROM Osztalyzatok
WHERE Tantargy = 'matematika');
Ugyanezt a kérdést egyszerűbben is megfogalmazhatjuk:
SELECT MIN (Osztalyzat) FROM Osztalyzatok WHERE Tantargy = 'matematika';
Az ANY és ALL használható a WHERE részben fix listákkal is, például x > ALL (12,21,8), x nagyobb, mint a lista legnagyobb eleme vagy y < ANY (5,4,7), y kisebb, mint a lista egyik eleme. Ezeket a feltételeket általában egyszerűbben is le tudjuk írni, az előző példák esetén x > 21 illetve y < 7. Az EXISTS esetén mindig egy újabb beágyazott lekérdezés következik. Ha egy beágyazott lekérdezés talál a feltételt kielégítő sort, akkor igaz értéket kapunk, különben hamisat. Például kinek nincs még osztályzata matematikából a 3/b-ben:
SELECT Nev FROM Diakok D WHERE NOT EXISTS
(SELECT * FROM Osztalyzatok
WHERE D.Diak_azonosito = Diak_azonosito AND
tantargy = 'matematika');
Beágyazott lekérdezések használatára még egy példa, kíváncsiak vagyunk Kiss János osztálytársainak a nevére:
SELECT Nev FROM Diakok WHERE Osztaly =
(SELECT Osztaly FROM Diakok WHERE Nev = 'Kiss János');
Az egyenlőség vizsgálat esetén a belső lekérdezés csak egy értéket adhat vissza. A relációkon értelmezett halmazműveletek bizonyos esetekben az OR, AND, NOT és IN operátorok segítségével is elvégezhetők. Az SQL ezek mellett az INTERSECT, UNION és MINUS műveleteket is biztosítja. Ezek segítségével két lekérdezést kapcsolhatunk össze a következő formában:
Halmaz muveletek megvalósítása az SQL-ben
Unió Metszet Különbség
SELECT ....
UNION
SELECT ... SELECT ...
INTERSECT
SELECT ... SELECT ...
MINUS
SELECT ...
A MINUS kulcsszó helyett az EXCEPT használandó néhány adatbáziskezelőben
Válasszuk ki azokat a termeket, ahol a 3/b-nek vagy a 3/a-nak vannak órái:
SELECT Tanterem FROM Orarend WHERE Osztaly = '3/b'
UNION
SELECT Tanterem FROM Orarend WHERE Osztaly = '3/a';
Ugyanez az IN operátorral rövidebb:
SELECT Tanterem FROM Orarend WHERE Osztaly IN ('3/a', '3/b');
Azon osztályok, melyeknek a 101 és 102 teremben is van órája:
SELECT Osztaly FROM Orarend WHERE Tanterem = 101
INTERSECT
SELECT Osztaly FROM Orarend WHERE Tanterem = 102;
Azon osztályok, melyeknek a 101-es teremben van, de a 102-es teremben nincs órája: SELECT Osztaly FROM Orarend WHERE Tanterem = 101
MINUS
SELECT Osztaly FROM Orarend WHERE Tanterem = 102;
Az utóbbi két lekérdezés is megoldható a halmazműveletek nélkül, de csak egymásba ágyazott lekérdezésekkel.
5.5 A vezérlő nyelv
A SQL vezérlő nyelv több funkciót lát el, ezek közül most csak a tranzakciók kezeléséhez szükséges parancsokat ismertetjük. A többi parancsról az adatbázis adminisztrátor parancsainál beszélünk. A logikailag egybe tartozó SQL utasításokat tranzakcióknak nevezzük. Az adatbázis ellentmondás mentes tartalmának megőrzéséhez a tranzakcióknak maradéktalanul végre kell hajtódniuk. Azonban egy tranzakció megszakadása esetén is gondoskodni kell az adatbázis konzisztenciájának megőrzéséről. Erre a COMMIT és ROLLBACK parancs pár szolgál. Az ORACLE nem az eredeti relációkon dolgozik. A sikeresen végrehajtott tranzakciók után a COMMIT parancs kiadásával véglegesíthetjük a tranzakció során végrehajtott változtatásokat a relációkban. A ROLLBACK parancs segítségével pedig visszaléphetünk az előző véglegesített állapothoz. Bizonyos parancsok automatikusan COMMIT-ot eredményeznek (CREATE TABLE, QUIT stb.). De az AUTOCOMMIT rendszerváltozó beállításától függően minden parancs kiválthat egy COMMIT-ot is.
SET AUTOCOMMIT ON
https://www.inf.u-szeged.hu/~gnemeth/adatbgyak/
http://www.agt.bme.hu/szakm/adatb/
http://aries.ektf.hu/~holovacs/AB/5_SQL_2.htm
/* teljes tablazat lekerdezese */
SELECT * FROM ALKALMAZOTT;
/* projekcio, csak a kivalasztott oszlopok */
SELECT OSZT_AZON, NEV, FONOK FROM ALKALMAZOTT;
/* kifejezesek az oszlopokban */
/* eves fizetes */
SELECT NEV, FIZETES * 12 "EVES FIZETES" FROM ALKALMAZOTT;
/* szoveges oszlopok osszevonasa */
SELECT ALK_AZON||'-'||NEV DOLGOZO, 'OSZTALY', OSZT_AZON FROM ALKALMAZOTT;
/* NULL ertekek a kifejezesben */
SELECT NEV, FIZETES * 12 + JUTALOM "EVES JOVEDELEM" FROM ALKALMAZOTT;
SELECT NEV, FIZETES * 12 + NVL(JUTALOM, 0) "EVES JOVEDELEM" FROM ALKALMAZOTT;
/* duplikalt sorok elhagyása */
SELECT OSZT_AZON FROM ALKALMAZOTT;
SELECT DISTINCT OSZT_AZON FROM ALKALMAZOTT;
SELECT DISTINCT OSZT_AZON, BEOSZTAS FROM ALKALMAZOTT;
/* eredmeny sorok rendezese */
SELECT NEV, BEOSZTAS, FIZETES * 12, OSZT_AZON FROM ALKALMAZOTT
ORDER BY NEV;
/* csokkeno sorrend */
SELECT NEV, BEOSZTAS, BELEPES FROM ALKALMAZOTT
ORDER BY BELEPES DESC;
/* tob rendezesi szempont */
SELECT OSZT_AZON, BEOSZTAS, NEV FROM ALKALMAZOTT
ORDER BY OSZT_AZON, FIZETES DESC;
/* a rendezesi szempont az oszlop sorszamaval */
SELECT NEV, FIZETES FROM ALKALMAZOTT
ORDER BY 2;
/* válogatás a sorok kozul */
select * from alkalmazott where nev = 'SKOT';
select * from alkalmazott where fizetés < 1200';
select * from alkalmazott where belepes > '04-JUN-87';
select * from alkalmazott where jutalom >= 500;
select * from alkalmazott where fizetes between 1000 and 2000;
select * from alkalmazott where fonok in (7902, 7566, 7788);
select * from alkalmazott where nev like 'S%';
select * from alkalmazott where nev like '____';
select * from alkalmazott where fonok is null;
select * from alkalmazott where fonok is not null;
/* osszetett feltetelek */
select alk_azon, nev, beosztas, fizetes from alkalmazott
where sal between 1000 and 2000 and beosztas = 'ELOADO';
select alk_azon, nev, beosztas, fizetes from alkalmazott
where sal between 1000 and 2000 or beosztas = 'ELOADO';
/* osztalyok kodjai es nevei rendezve */
select oszt_azon, nev from osztaly order by oszt_azon;
/* kulonbozo beosztasok */
select distinct beosztas from alkalmazott;
/* 10-es és a 20-as osztalyon dolgozok nev szerint rendezve */
select * from alkalmazott
where oszt_azon in (10,20)
order by nev;
/* 20-as osztalyon dolgozo eloadok neve es beosztasa */
select nev, beosztas from alkalmazott where oszt_azon = 20;
/* "ES"-t vagy "IR"-t tartalmazo nevek */
select nev from alkalmazott
where nev like '%ES%' or nev like '%IR%';
/* nev, beosztas, fizetes azokra akiknek van fonokuk */
select nev, beosztas, fizetes from alkalmazott
where fonok is not null;
/* az 1981-ben belepett alkalmazottak */
select nev, oszt_azon, belepes from alkalmazott
where belepes like '%81';
select nev, oszt_azon, belepes from alkalmazott
where belepes between '01-JAN-81' and '31-DEC-81';
/* osszevont oszlopok */
select nev||' '||beosztas||' beosztasban dolgozik '||belepes||' ota'
"ki, mit csinal, mi ota" from alkalmazott;
/* valtozokat tartalmazo kifejezesek */
select nev, belepes from alkalmazott
where belepes between '&kezdo' and '&veg';
select nev, beosztas, fizetes, fonok, oszt_azon from alkalmazott
where beosztas = '&beosztas';
/* sqlplus valtozo hasznalata */
define eves = 'fizetes*12+nvl(jutalom,0)'
select nev, &eves from alkalmazott where &eves > 30000;
/* karakteres fuggvenyek */
/* nagybetusse alakitas */
select nev, beosztas from alkalmazott
where beosztas = upper ('&beosztas');
/* kisbetusse alakitas */
select lower(beosztas) from alkalmazott;
/* szavak kezdo betui nagy betuve */
select initcap(nev) from alkalmazott;
/* feltoltes jobbrol vagy balrol */
select rpad(nev, 10, '_') nev, lpad(beosztas, 10) beosztas,
lpad (oszt_azon, 7, '.') reszleg from alkalmazott;
/* karakterek eltavolitasa jobb vagy bal szelrol */
select rtrim(beosztas, 'KR'), ltrim(nev, 'SM') from alkalmazott;
/* sztring pozicioja az oszlopban */
select nev, instr(nev, 'A') from alkalmazott;
/* sztring hossza */
select nev, length(nev) from alkalmazott;
/* karakterek csereje, ekezetek nelkuli kiiratas */
select translate(nev, 'ÁÉÓÖÔÚÜŰ', 'AEOOOUUU') from alkalmazott;
/* fuggvenyek egymasba agyazasa, E betuk szama a nevekben */
select nev, length(nev)-length(replace(nev, 'E')) E from alkalmazott;
/* numerikus fuggvények */
/* kerekites */
define x=fizetes/30
select &x, round(&x), round(&x,2), round(&x,-1) from alkalmazott
where oszt_azon = 10;
/* csonkitas */
select &x, trunc(&x), trunc(&x,2), trunc(&x,-1) from alkalmazott
where oszt_azon = 10;
/* fizetes 15%-al novelt erteke */
select nev, round(fizetes*1.15) "novelt fizetes" from alkalmazott;
/* datum fuggvenyek */
/* aktualis datum lekerdezese */
select sysdate from sys.dual;
/* aritmetikai muveletek datumokkal */
select belepes, belepes+7, sysdate-belepes from alkalmazott
where oszt_azon = 10;
/* kulonbseg honapokban, a 160 honapnal regebben belepett alkalmazottak */
select nev, belepes, months_between(sysdate, belepes)
from alkalmazott
where months_between(sysdate, belepes) > 160;
/* honapok hozzaadasa, levonasa */
select add_months(sysdate, 2) from sys.dual;
select add_months(sysdate, -5) from sys.dual;
/* kovetkezo nap */
select next_day(sysdate, 'SUNDAY') from sys.dual;
/* honap utolso napja */
select last_day(sysdate) from sys.dual;
/* datum kerekites */
select sysdate, round(sysdate, 'month'), round(sysdate, 'year') from sys.dual;
/* datum csonkitas */
select sysdate, trunc(sysdate, 'month'), trunc(sysdate, 'year') from sys.dual;
/* konverzios fuggvenyek */
/* szam sztringge alakitas */
select nev, to_char(fizetes, '$9,999') from alkalmazott
where beosztas = 'MANAGER';
/* datum sztringge alakitas */
select to_char(sysdate, 'yyyy month dd day') from sys.dual;
/* ido es datum */
select to_char(sysdate, 'yyyy.mm.dd HH:MI:SS') from sys.dual;
/* mahoz 2 evre milyen nap lesz */
select to_char(add_months(sysdate, 24), 'DAY') from sys.dual;
/* sztring datumma alakitas */
select to_date('96.11.20', 'YY.MM.DD') from sys.dual;
/* csoport fuggvenyek */
/* teljes tablara */
select min(fizetes), max(fizetes), sum(fizetes), avg(fizetes), count(fizetes)
from alkalmazott;
/* csoportokra */
select oszt_azon, min(fizetes), max(fizetes), sum(fizetes), avg(fizetes), count(fizetes)
from alkalmazott
group by oszt_azon;
/* valogatas a csoportok kozul */
select oszt_azon, avg(fizetes) from alkalmazott
group by oszt_azon having count(*) > 3;
/* valogatas a sorok es csoportok kozul is */
select oszt_azon, avg(fizetes) from alkalmazott
where beosztas != 'ELOADO'
group by oszt_azon having min(fizetes) > 1000;
/* beosztasonkent a legkisebb fizetes */
select beosztas, min(fizetes) MIN, max(fizetes) MAX from alkalmazott
group by beosztas;
/* managerek szama */
select count(*) from alkalmazott where beosztas = 'MANAGER';
/* lekerdezes tobb tablabol */
/* egyen osszekapcsolas */
select alkalmazott.nev, beosztas, osztaly.nev from alkalmazott, osztaly
where alkalmazott.oszt_azon = osztaly.oszt_azon;
/* Descartes szorzat, osszekapcsolas minden kombinacioban */
select osztaly.nev, alkalmazott.nev from osztaly, alkalmazott;
/* nem egyen osszekapcsolas */
select a.nev, a.fizetes, f.f_oszt from alkalmazott a, fiz_oszt f
where a.fizetes between f.min and f.max;
/* Budapesten dolgozok nevsora */
select a.nev from alkalmazott a, osztaly o
where a.oszt_azon = o.oszt_azon and o.varos = 'BUDAPEST';
/* kulso osszekapcsolas */
select o.oszt_azon, o.nev, a.nev from alkalmazott a, osztaly o
where a.oszt_azon (+) = o.oszt_azon;
/* tabla osszekapcsolasa onmagaval */
/* fonok beosztott parok */
select a.nev fonok, b.nev beosztott from alkalmazott a, alkalmazott b
where b.fonok = a. alk_azon;
select a.nev fonok, b.nev beosztott from alkalmazott a, alkalmazott b
where b.fonok = a. alk_azon (+);
/* alkalmazottak akik a fonokuk elott leptek be */
select a.nev BEOSZTOTT, a.belepes, b.nev FÔNÖK, b.belepes
from alkalmazott a, alkalmazott b
where a.fonok = b.alk_azon and a.belepes < b.belepes;
/* halmaz muveletek */
/* unio osztalyok, ahol manager vagy ugynok dolgozik */
select distinct oszt_azon from alkalmazott
where beosztas = 'MANAGER'
union
select distinct oszt_azon from alkalmazott
where beosztas = 'UGYNOK';
/* metszet, azok az osztalyok amelyeknek van dolgozoja */
select distinct oszt_azon from alkalmazott
intersect
select oszt_azon from osztaly;
/* kulonbseg, azok az osztalyok, ahol nem dolgozik senki */
select oszt_azon from osztaly
minus
select oszt_azon from alkalmazott;
/* egymasba agyazott lekerdezesek */
/* legkisebb fizetesu dolgozo */
select nev, fizetes from alkalmazott
where fizetes = (select min(fizetes) from alkalmazott);
/* szaboval azonos munkakorben dolgozok */
select nev, beosztas from alkalmazott
where beosztas = (select beosztas from alkalmazott
where nev = 'SZABO');
/* legkisebb fizetesu dolgozo osztalyonkent */
select nev, fizetes, oszt_azon from alkalmazott
where (fizetes, oszt_azon) in
(select min(fizetes), oszt_azon from alkalmazott
group by oszt_azon);
/* 30-as minimum fizetesenel tobbet keresok */
select nev, beosztas, fizetes from alkalmazott
where fizetes > any
(select fizetes from alkalmazott
where oszt_azon = 30);
/* a 30-as osztalyon legtobbet keresonel magasabb fizetesuek */
select fizetes, beosztas, nev from alkalmazott
where fizetes > all
(select fizetes from alkalmazott where oszt_azon = 30);
/* legmagasabb atlagfizetesu beosztas */
select beosztas, avg(fizetes) from alkalmazott
group by beosztas
having avg(fizetes) =
(select max(avg(fizetes)) from alkalmazott
group by beosztas);
/* korrelacio */
/* akiknek nagyobb a fizetesuk, mint az osztaly atlag */
select oszt_azon, nev, fizetes from alkalmazott a
where fizetes > (select avg(fizetes) from alkalmazott
where oszt_azon = a.oszt_azon)
order by oszt_azon;
/* akiknek legalabb egy beosztottja van */
select nev, beosztas, oszt_azon from alkalmazott a
where exists (select * from alkalmazott
where fonok = a.alk_azon);
/* osztolyonkent az utolsonak belepett dolgozo */
select oszt_azon, nev, belepes from alkalmazott
where (oszt_azon, belepes) in (select oszt_azon, max(belepes)
from alkalmazott group by oszt_azon)
order by belepes dec;
/* osztaly amelyiknek nincs dolgozoja */
select nev from osztaly o
where not exists (select * from alkalmazott
where oszt_azon = o.oszt_azon);
/* az az osztaly, ahol legmagasabb az eves atlagjovedelem */
define x=fizetes * 12+nvl(jutalek,0)
select oszt_azon, avg(&x) from alkalmazott
group by oszt_azon
having avg(&x) = (select max(avg(&x)) from alkalmazott
group by oszt_azon);
===========================================================
/* Demo tablak feltoltese */
/* set termout off */
DROP TABLE ALKALMAZOTT;
DROP TABLE OSZTALY;
DROP TABLE FIZ_OSZT;
DROP TABLE DUMMY;
DROP SEQUENCE AZON;
DROP VIEW OSZT_ATLAG;
DROP VIEW BEOSZT_ATLAG;
CREATE TABLE OSZTALY (
OSZT_AZON NUMBER(2) NOT NULL,
NEV VARCHAR(14),
VAROS VARCHAR(13));
INSERT INTO OSZTALY VALUES (10,'SZAMLAZAS','BUDAPEST');
INSERT INTO OSZTALY VALUES (20,'KUTATAS','ESZTERGOM');
INSERT INTO OSZTALY VALUES (30,'RAKTAR','BUDAORS');
INSERT INTO OSZTALY VALUES (40,'FORGALMAZAS','BUDAPEST');
CREATE TABLE ALKALMAZOTT (
ALK_AZON NUMBER(4) NOT NULL,
NEV VARCHAR(10),
BEOSZTAS VARCHAR(9),
FONOK NUMBER(4),
BELEPES DATE,
FIZETES NUMBER(7,2),
JUTALOM NUMBER(7,2),
OSZT_AZON NUMBER(2) NOT NULL);
INSERT INTO ALKALMAZOTT VALUES (7839,'KIRALY','ELNOK',NULL,'17-NOV-81',5000,NULL,10);
INSERT INTO ALKALMAZOTT VALUES (7698,'BIRO','MANAGER',7839,'1-MAY-81',2850,NULL,30);
INSERT INTO ALKALMAZOTT VALUES (7782,'CSALO','MANAGER',7839,'9-JUN-81',2450,NULL,10);
INSERT INTO ALKALMAZOTT VALUES (7566,'JONAS','MANAGER',7839,'2-APR-81',2975,NULL,20);
INSERT INTO ALKALMAZOTT VALUES (7654,'MARTON','UGYNOK',7698,'28-SEP-81',1250,1400,30);
INSERT INTO ALKALMAZOTT VALUES (7499,'ALI','UGYNOK',7698,'20-FEB-81',1600,300,30);
INSERT INTO ALKALMAZOTT VALUES (7844,'TURCSI','UGYNOK',7698,'8-SEP-81',1500,0,30);
INSERT INTO ALKALMAZOTT VALUES (7900,'JENES','ELOADO',7698,'3-DEC-81',950,NULL,30);
INSERT INTO ALKALMAZOTT VALUES (7521,'VAMOS','UGYNOK',7698,'22-FEB-81',1250,500,30);
INSERT INTO ALKALMAZOTT VALUES (7902,'FINESZ','ELEMZO',7566,'3-DEC-81',3000,NULL,20);
INSERT INTO ALKALMAZOTT VALUES (7369,'SZABO','ELOADO',7902,'17-DEC-80',800,NULL,20);
INSERT INTO ALKALMAZOTT VALUES (7788,'SKOT','ELEMZO',7566,'09-DEC-82',3000,NULL,20);
INSERT INTO ALKALMAZOTT VALUES (7876,'ADAM','ELOADO',7788,'12-JAN-83',1100,NULL,20);
INSERT INTO ALKALMAZOTT VALUES (7934,'MULLER','ELOADO',7782,'23-JAN-82',1300,NULL,10);
CREATE TABLE FIZ_OSZT (
F_OSZT NUMBER,
MIN NUMBER,
MAX NUMBER);
INSERT INTO FIZ_OSZT VALUES (1,700,1200);
INSERT INTO FIZ_OSZT VALUES (2,1201,1400);
INSERT INTO FIZ_OSZT VALUES (3,1401,2000);
INSERT INTO FIZ_OSZT VALUES (4,2001,3000);
INSERT INTO FIZ_OSZT VALUES (5,3001,9999);
CREATE TABLE DUMMY (
DUMMY NUMBER );
INSERT INTO DUMMY VALUES (0);
CREATE SEQUENCE AZON
INCREMENT BY 1
START WITH 7935
NOCACHE;
CREATE VIEW OSZT_ATLAG AS
SELECT OSZTALY.NEV, AVG(FIZETES) ATLAG FROM OSZTALY, ALKALMAZOTT
WHERE OSZTALY.OSZT_AZON = ALKALMAZOTT.OSZT_AZON
GROUP BY OSZTALY.NEV;
CREATE VIEW BEOSZT_ATLAG AS
SELECT BEOSZTAS, AVG(FIZETES) ATLAG FROM ALKALMAZOTT
GROUP BY BEOSZTAS;
COMMIT;
Fogalmak
Szelekció
A szelekció művelete során egy relációból csak egy adott feltételt kielégítő sorokat őrizzük meg az eredmény relációban.
1.1 ábra Szelekció, horizontális megszorítás
A projekció során egy reláció oszlopai közül csak bizonyosakat őrzünk meg az eredmény relációban.
A Descartes szorzat két reláció sorait minden kombinációban egymás mellé teszi az eredmény relációban.
Az összekapcsolás művelete két vagy több relációt kapcsol össze egy-egy attributum érték összehasonlításával. Az összekapcsolás leggyakoribb esete amikor az attributumok egyezését vizsgáljuk, ezt egyen összekapcsolásnak nevezzük. A nem egyenlőségen alapuló összekapcsolást Theta kapcsolásnak nevezzük.
Ez egy speciális szorzás mely a következő műveletsorral írható le
- Vegyük az első reláció egy sorát
- Az összekapcsolási feltételt vizsgáljuk meg a második táblázat összes sorára, ha igaz, adjuk mindkét reláció sorát az eredményhez
- Folytassuk az 1. ponttal amig van még sor az első relációban
Az összekapcsolás eredmény relációjában az első reláció csak azon sorai szerepelnek, melyekre található a feltételt kielégítő sor a második relációban. Gyakran arra van szükség, hogy az első reláció valamennyi sora szerepeljen legalább egyszer az eredmény relációban. Ezt a fajta összekapcsolást külső összekapcsolásnak nevezzük.
Halmaz műveletek
A halmazokkal kapcsolatos alapvető műveleteket, unió metszet, különbség, a relációkra is értelmezzük. Minden értelmezett halmazművelethez legalább két operandus szükséges, a különbség esetében több sem lehet. A halmaz műveletek csak azonos szerkezetű relációk között hajthatók végre, ez alatt azt értjük, hogy a műveletbe bevont két reláció oszlopainak meg kell egyeznie az elnevezésben és a tárolt adat tipusában is. A relációkra általában a komplemens képzés nem értelmezhető.
Unió
Az unió művelete azonos szerkezetű két vagy több reláció között végezhető el. Az eredmény reláció tartalmazza azokat a sorokat, melyek a műveletbe bevont relációk közül legalább egyben szerepelnek. Ha ugyanaz a sor az egyesítendő relációk közül többen is szerepelne, akkor is csak egyszer szerepel az eredmény relációban.
A metszet művelete azonos szerkezetű két vagy több reláció között végezhető el. Az eredmény reláció csak azokat a sorokat tartalmazza, melyek a műveletbe bevont relációk közül mindegyikben szerepelnek.
A különbség művelete azonos szerkezetű két reláció között végezhető el. Az eredmény reláció csak azokat a sorokat tartalmazza, melyek a első relációban megtalálhatóak, de a másodikban nem.
Nincsenek megjegyzések:
Megjegyzés küldése