SQL alapismeretek 13_2_2 Középszintű érettségire felkészülés

A példa táblák létrehozása

autó - tulajdonos séma


create table tulaj (
szazon text(11) primary key,
nev text(30),
cím text(40);

create table auto(
rsz text(7) primary key,
tipus text(20),
szin text(20),
ar money,
evj decimal,
tul references tulaj(szazon);

Relációséma törlése

DROP TABLE táblanév;

Hatására a séma és a hozzá tartozó adattábla törlődik.

Relációséma módosítása

ALTER TABLE táblanév
[ADD (újelem, ..., újelem)]
[MODIFY (módosítás, ..., módosítás)]
[DROP (oszlop, ..., oszlop)];


A táblába új sor felvétele

INSERT INTO táblanév [(oszloplista)]
VALUES (értéklista);

A példatábla feltöltése:

insert into tulaj
values (27110142233, Kovács Ákos, Szerencs Sugár út 24);

insert into auto
values (abc-123, Trabi, kék, 130000, 1990, 27110142233);

Sor(ok) módosítása

UPDATE táblanév
SET oszlop = kifejezés, ..., oszlop = kifejezés
[ WHERE feltétel ];

Sor(ok) törlése

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

Példának töröljük a tulaj tábla egyetlen rekordját.

delete from tulaj;

A SELECT utasítás az alábbi alparancsokból állhat:

SELECT [DISTINCT] oszloplista --> projekció

FROM táblanévlista -->Descartes-szorzat

[WHERE feltétel] --> szelekció

[GROUP BY oszloplista] --> csoportosítás

[HAVING feltétel] --> csoport-feltétel

[ORDER BY oszloplista]; --> rendezés

SELECT [DISTINCT] oszloplista
FROM táblanévlista
[WHERE feltétel];

Egy mező értéke, két határérték között van-e?

mező érték BETWEEN érték1 And érték2

Tartalmazást:

mező érték IN (érték1, érték2, …)

Szöveg mintaillesztése:

mező érték LIKE illesztő szöveg

Illesztő karakterek: *, ?, #, [karakterlista],   [!karakterlista]

Például listázzuk ki az auto tábla adatait!

select * from auto;

Projekció Példa:

SELECT DISTINCT tipus FROM auto;

Projekció:

SELECT [DISTINCT] A1,...,An FROM T;

Példa:

SELECT DISTINCT tipus FROM auto;

Visszaadja a típusokat az auto táblából. A DISTINCT miatt csak az egyedi értékeket válogatja ki.

Szelekció:

SELECT * FROM T WHERE feltétel;

Példa:

SELECT * FROM auto ar > 200000;

Visszaadja a 200000-től nagyobb értékű autók minden adatát.

Descartes-szorzat: T1 x T2

SELECT * FROM T1,T2;

Példa:

SELECT * FROM auto, tulaj;

Megkapjuk a két tábla Descartes szorzatát.

Természetes összekapcsolás

Állítsuk elő azt az eredménytáblát, amely a tulajdonoshoz rendeli az autója rendszámát és a típusát.

SELECT tulaj.szazon, tulaj.nev, auto.rsz, auto.tipus
FROM tulaj, auto
WHERE tulaj.szazon = auto.tul;

Halmazműveletek relációk között

Unió:

(SELECT * FROM T1)
UNION
(SELECT * FROM T2);

Metszet:

(SELECT * FROM T1)
INTERSECT
(SELECT * FROM T2);

Különbség:

(SELECT * FROM T1)
MINUS
(SELECT * FROM T2);

Az eredménytábla rendezése

Bár a relációs modell nem definiálja a rekordok sorrendjét, a gyakorlatban rendszerint valamilyen rendezettségben kívánjuk látni az eredményt. Erre szolgál az
ORDER BY oszlopnév [DESC], ..., oszlopnév [DESC]
alparancs, amely a SELECT utasítás végére helyezhető, és az eredménytáblának a megadott oszlopok szerinti rendezését írja elő. Alapértelmezés szerint a rendezés növekvő sorrendben történik, ha fordítva kívánjuk, a DESC (descending) kulcsszó írandó a megfelelő oszlopnév után.

Például készítsünk egy olyan lekérdezést, amely visszaadja az auto tábla rekordjait ar mező szerint novekvő rendezettséggel.

SELECT * FROM auto ORDER BY ar;

Csoportosítás (GROUP BY, HAVING)

Ha a tábla sorait csoportonként szeretnénk összesíteni, akkor a SELECT utasítás a
GROUP BY oszloplista
alparanccsal bővítendő. Egy csoportba azok a sorok tartoznak, melyeknél oszloplista értéke azonos. Az eredménytáblában egy csoportból egy rekord lesz. Az összesítő függvények csoportonként hajtódnak végre. A SELECT után összesítő függvényen kívül csak olyan oszlopnév feltüntetésének van értelme, amely a GROUP BY-ban is szerepel.

A GROUP BY által képezett csoportok közül válogathatunk a
HAVING feltétel
alparancs segítségével,amelynél csak a feltételnek eleget tevő csoportok kerülnek összesítésre az eredménytáblába.

Összesítő függvények

Egy oszlop értékeiből egyetlen értéket hoznak létre (például átlag). Általános alakjuk:

függvénynév ( [DISTINCT] oszlopnév )

Ha DISTINCT szerepel, akkor az oszlopban szereplő azonos értékeket csak egyszer kell figyelembe venni. A számításnál a NULL értékek figyelmen kívül maradnak. Az egyes függvények:

AVG: átlagérték.
SUM: összeg.
MAX: maximális érték.
MIN: minimális érték.
COUNT: elemek száma.
Ennél a függvénynél oszlopnév helyére * is írható, amely valamennyi oszlopot együtt jelenti.

Jegyzetek:

http://dev.logisztika.bme.hu/logdb/irodalom/SQL.lekerdezesek.foldi.halandoknak.2009.eBOOk-digIT.pdf

http://www.beregszaszi-debr.sulinet.hu/tantargy/info/anyagok/sqljegyzet.pdf

http://www.inczedy.hu/~szikszai/adatbazis/sql.pdf

http://www.kepzesevolucioja.hu/dmdocuments/4ap/16_1618_006_101130.pdf

Az SQL nyelv alapjai
A DDL elemei
Sémák létrehozása, a create

A táblákat a CREATE TABLE paranccsal lehet létrehozni. Egy táblában <=1000 oszlop lehet. Több adattípust is lehet alkalmazni, de gyakorlatilag csak néhányra van szükség.

NUMBER(h, t) – numerikus adatok, hossza<=38, h a szám hosszát tartalmazza, t – a tizedes-jegyek számát;

DATE  – dátum és idő típus;

VARCHAR2(méret) – karakter típus, a hossza váltózó (max hossza<=4000);

CHAR(méret) –  karakter típus, fix hosszúságú (max hossza<=2000);

NCHAR(méret) –  azonos a CHAR típussal, de a max méret függ a karakterkészletétől,

LOB (large objects) – bináris v. szöveges formátum. Lehet kép-, hang-, vagy nagy méretű szöveges állomány.

        A LOB típuson belül lehetnek:

LONG   – szöveges adatok, max hossza<=2 Gbyte;

LONG RAW  – bináris adatok, max hossza<=2 Gbyte;

A LONG és LONG RAW típusokat csak egyszerű adatfeldolgozásban alkalmazzák.

CLOB,  NCLOB, BLOB – belső LOB típusok, mivel az AB-ban tárolódnak,  max hossza<=4 Gbyte;

CLOB – az adatokat tárolja az AB karakter-készlete alapján;

NCLOB – az adatokat tárolja a nemzeti karakter-készletben.

Az Oracle mindegyik táblába automatikusan helyez egy ROWID nevű pszeudooszlopot, amely a programozók ritkán szokták használni.

ROWID – pszeudooszlop, amely tartalmazza a sor logikai egyedi címét. A ROWID nem változható meg, de a SELECT parancsban lekérdezhető:

                        SELECT rowid FROM tabla_1;

A Sor sorszáma –  olyan szám, amely akkor rendelődik a sorhoz, amikor az bekerül a táblába. Ez a szám része a ROWID-nek.

Csak az a felhasználó hozhat létre táblákat, aki CREATE TABLE vagy CREATE ANY TABLE privilégiummal rendelkezik.

A STORAGE paraméter alkalmazása a CREATE TABLE parancsban

Egy táblát a STORAGE memória-paraméterek nélkül is lehet létrehozni. Ebben az esetben a tábla az alapértelmezési értékeket kapja, ami nem mindig jó megoldás. A memória egységeket (szegmenseket), melyekben a táblák tárolódnak, extentnek nevezzük. Az első extent neve INITIAL, a többi extentek pedig másodlagos extentek.

CREATE TABLE testtab

        (col1 VARCHAR2(10))

        STORAGE (INITIAL 100K

                NEXT 50K

                MINEXTENTS 1

                MAXEXTENTS 99

                PCINCREASE 20);

Az első extent 100K memóriát kap, a második – 50K. A PCINCREASE 20 érték alapján mindegyik következő extent mérete 20% növekszik az előzőhez képest. MINEXTENTS 1 azt jelenti, hogy a tábla először 1extentent kap, a MAXEXTENTS 99 – a tábla maximálisan  99 extentet kaphat.

A táblákat, mint az indextáblákat is, particionálhatjuk. A particionálás lehetőséget ad a nagyon nagy táblák és indextáblák kezelésére. A particionálás során a nagy táblát kisebb és könnyebben kezelhető részekre osztja a rendszer.

Partíciónált tábla – olyan tábla, amelynek sorai kisebb azonos szerkezetű táblákba, partíciókra, vannak szétosztva. A partíciók fizikailag különböző helyeken tárolhatjuk.

A partíciónálás előnyei:

Az I/O-terhelés jobb eloszlása

Biztonsági mentés és visszaállítás egyszerűbb végrehajtása

Az adatsérülés lehetőségének minimalizálása

Az archiválási folyamat segítése.

Példa.

CREATE TABLE nagy_tabla

        (col1 VARCHAR2(10),

        (col2 VARCHAR2(10))

             )

        PARTITION BY RANGE (col1, col2)

                (PARTITION p1 VALUES LESS THEN (…)  TABLESPACE p1,

                 PARTITION p2 VALUES LESS THEN (…)  TABLESPACE p2);

Sémaelemek módosítása, az alter

Az oszlop hosszát lehet növelni még akkor is, ha a tábla adatokat tartalmaz. Az oszlop hosszát pedig csak akkor lehet csökkenteni, ha a tábla üres.

ALTER TABLE testtab ADD col2 VARCHAR2(100);

ALTER TABLE testtab MODIFY col2 VARCHAR2(150);

ALTER TABLE testtab STORAGE ( NEXT 10K

     MAXEXTENTS 50);

ALTER TABLE testtab DROP  col1;

A táblákat átnevezhetjük:

                RENAME testtab TO testtab1;

és törölhetjük:

                DROP TABLE testtab;

Példa.

CREATE TABLE students(

    id                NUMBER(5) PRIMARY KEY,

    first_name        VARCHAR2(20),

    last_name         VARCHAR2(20),

    major             VARCHAR2(30),                -- szak

    current_credits   NUMBER(3) );         -- leadott vizsgák száma



Az ORACLE-rendszer tartalmazz egy bemutató sémát, amelynek Scott a tulajdonosa:

CREATE TABLE scott.dept

        (deptno  NUMBER(2) NOT NULL,

        dname  VARCHAR2(14),

        loc  VARCHAR2(13),

        CONSTRAINT pk_dept PRIMARY KEY (deptno));

CREATE TABLE scott.emp

        (empno  NUMBER(4) NOT NULL,

        ename  VARCHAR2(10),

        job    VARCHAR2(9),

        mgr    NUMBER(4),

  hiredate  DATE,

        sal       NUMBER(7,2),

         comm      NUMBER(7,2),

         sal       NUMBER(7,2),

  deptn     NUMBER(2),

        CONSTRAINT pk_emp PRIMARY KEY (empno));

Amikor egy olyan táblára (vagy más Oracle-objektumra) hivatkozunk, melynek nem mi vagyunk a tulajdonosa, akkor a tábla nevén kívül meg kell adni a tulajdonos nevét is

tulajdonos.tábla_név

CONSTRAINT integritási megszorítás alkalmazása

Hivatkozási integritás biztosítja, hogy egy adott oszlop csak olyan értékeket vehessen fel, amelyek megjelennek egy másik oszlopban. Integritási megszorításként lehet előírni.

Megszorítás – egy szabály vagy korlátozás az adatok egy részén kerül ellenőrzésre.

Integritási megszorítás – olyan szabály, amely egy vagy több oszlop lehetséges értékeinek körét határolják be. A megszorítást a tábla létrehozásánál az oszlopokhoz lehet hozzárendelni.

A CONSTRAINT záradékot a CREATE TABLE vagy ALTER TABLE parancsokban használhatjuk. A legtöbb megszorítás a CONSTRAINT záradék nélkül is megadható.

 Léteznek:

táblamegszorítások

oszlopmegszorítások

Táblamegszorítás – olyan integritási megszorítás, amelyet egy tábla több oszlopára alkalmazunk. Oszlopmegszorítás pedig egy olyan integritási megszorítás, amelyet egy tábla oszlopához rendelünk.

A táblamegszorítások listája:

UNIQUE

PRIMARY KEY

FOREIGN KEY

REFERENCES

ON DELETE CASCADE

CHECK

ENABLE VALIDATE

DISABLE

Az oszlopmegszorítások:

NULL

NOT NULL

A többi oszlopmegszorítások a táblamegszorításokkal azonosak.

A DISABLE opciót a integritási megszorítás vagy trigger kikapcsolására (nem törlésére) alkalmazhatjuk. Leggyakrabban az ALTER TABLE parancsban alkalmazzák.

ALTER TABLE tabla DISABLE UNIQUE oszlop, oszlop,..

ALTER TABLE tabla DISABLE PRIMARY KEY

ALTER TABLE tabla DISABLE CONSTRAINT megszorítás_név

DISABLE ALL TRIGGERS

A DML elemei

INSERT – új sor beszúrása

UPDATE – adatok módosítása a már létező sorokban

DELETE – sorok törlése

TRUNCATE – az összes sorok gyors törlése

Új adat felvitele, az insert parancs

Először az SQL*Plus-ban ellenőrizzük a testtab tábla struktúráját:

                DESCRIBE testtab;

                NAME          NULL ?               TYPE

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

                         COL1                                      VARCHAR2(10)

                COL2                                       NUMBER(38)

INSERT INTO testtab(col1, col2) VALUES(’szöveg’, 123);

Ugyan ezt az eredményt kapjuk, ha az

                INSERT INTO testtab VALUES (’szöveg’, 123);

parancsot alkalmazzuk.

A NULL-értékeket beszúrása-

                INSERT INTO testtab (col1, col2) VALUES (’szöveg’, NULL);

vagy

                INSERT INTO testtab (col1) VALUES (’szöveg’);

Arra is van lehetőség, hogy a táblába az INSERT parancs egyszerre több sort szúrjon be (e célból alkalmazzuk a beágyazott SELECT parancsot)

                INSERT INTO testtab (col1, col2) SELECT e_name, emp_no FROM emp;

Tábla létrehozása egy másik tábla alapján

CREATE TABLE emp_copy AS

        SELECT * FROM emp;

CREATE TABLE emp_copy2 AS

        SELECT emp_no, e_name FROM emp

WHERE e_name LIKE ’%a%’;

Adatok módosítása, az update

Az UPDATE parancs módosítja az összes olyan sorokat, amelyekre teljesül a WHERE-feltétel. Ha az UPDATE parancs WHERE- feltételt nem tartalmaz, akkor a módosítás minden sorban végrehajtódik. Ez nagyon ritkán fordul elő.

Példa.

UPDATE emp_copy2  SET e_name=’Kovács’ WHERE emp_no=7499;

             

UPDATE emp_copy2  ec  SET (emp_no, e_name) =

                (SELECT emp_no, e_name FROM emp e WHERE e.emp_no=ec.emp_no) WHERE e_name LIKE ’I%’;

Az utolsó parancsban ec és e a táblák másodlagos nevei. Az utolsó WHERE-feltétel az egész parancshoz tartozik (az UPDATE-hoz), és azokat a sorokat adja meg, amelyeket módosítani akarunk. A beágyazott SELECT parancs azokat az értékeket szerkeszti, melyek bekerülnek az emp_copy2  táblába. A SELECT-ben a WHERE-feltétel összekapcsolja a két tábla megfelelő sorait.

Adatok törlése, a delete

DELETE FROM emp_copy2 WHERE emp_no=7876;

             

DELETE FROM emp_copy2 WHERE emp_no IN

(SELECT emp_no FROM emp_copy);

Mindegyik SQL parancsban függvényeket lehet alkalmazni.

Példa.

UPDATE emp_copy  SET e_name=UPPER( SUBSTR(e_name, 1, 1))       || LOWER(SUBSTR(e_name, 2, LENGTH(e_name)-1));

Ugyan ezt az eredményt kapjuk, ha az INITCAP függvényt alkalmazzuk.

Adatok gyors törlése

A tábla összes sorait gyorsan lehet törölni a TRUNCATE paranccsal:

TRUNCATE TABLE emp_copy;

A parancs végrehajtása után a tábla struktúrája megmarad, de az egyetlen sort sem fog tartalmazni.

Jogok és felhasználók kezelése, a DCL

Privilégium (jogosultság) a felhasználó számára lehetővé teszi, hogy az AB-ban bizonyos műveleteket hajthasson végre.

A privilégium kéttípusú lehet:

rendszer-privilégium;

objektum-privilégium.

A rendszer-privilégiumok lehetővé teszik adatdefiníciós és adatvezérlő parancsok végrehajtását, és az AB-ba való belépést. Az objektum-privilégiumok az AB objektumokkal való műveletekhez adnak jogosultságot. A felhasználó a létrehozása után egyetlen privilégiummal sem rendelkezik, és a privilégiumokat az AB adminisztrátortól később kapja. Amikor a felhasználó kap egy privilégiumot, utána az engedélyezett műveteket végrehajtatja a felsorolt AB-objektumaival.

Egy AB-hoz általános esetben több felhasználó is kapcsolódhat. Az Oracle-ban minden objektumnak van felhasználó-tulajdonosa. A nem tulajdonos-felhasználó csak akkor végezhet bizonyos műveleteket az objektumokkal, ha megkapta a megfelelő privilégiumokat. Létezik több mint nyolcvan rendszer-privilégium:

ALTER

DELETE

EXECUTE

INDEX

INSERT

REFERENCES

SELECT

UPDATE

…

A következő táblázat tartalmazza egyes privilégiumok és AB-objektumok közti lehetséges kapcsolatokat.

Privilégium

Table

View

Sequence

Procedure

ALTER

+

+

DELETE

+

+

EXECUTE

+

INDEX

+

INSERT

+

+

REFERENCES

+

SELECT

+

+

+

UPDATE

+

+

A INSERT, UPDATE és REFERENCES privilégiumokat lehet a tábla egyes oszlopaihoz kötni. Ha megakarjuk tudni, hogy milyen rendszer privilégiumokat lehet alkalmazni, végre kell hajtani a következő parancsot:

        SELECT UNIQUE privilege FROM dba_sys_priv;

Privilégiumok adományozása

Az AB adminisztrátor a privilégiumokat a GRANT paranccsal adhatja meg a felhasználóknak:

GRANT  privilégium_lista ON objektum_lista TO felhasználó_lista

[WITH GRANT OPTION];

GRANT  privilégium_lista ON objektum_lista TO szerepkör_lista

[WITH GRANT OPTION];

GRANT  privilégium_lista ON objektum_lista TO PUBLIC

[WITH GRANT OPTION];

A WITH GRANT OPTION esetén a felhasználó az adott privilégiumot más felhasználónak is tovább adhatja.

Példa.

        GRANT SELECT, UPDATE ON emp_copy TO test_user;

A test_user felhasználó az emp_copy táblával végrehajthatja a SELECT és UPDATE parancsokat. Ezekben a parancsokban nem elég a tábla nevét emp_copy megadni, mivel a tábla tulajdonosát is meg kell adni. Például, ha a scott felhasználó az emp_copy tábla tulajdonosa, akkor a helyes hivatkozás:

SELECT * FROM scott.emp_copy;

Bizonyos esetekben ez a követelmény problémát is okozhat. Például, ha a tábla tulajdonosa a program létrehozása és futtatási ideje között megváltozott, akkor ezt figyelembe kell venni az összes táblára való hivatkozásban. Ilyen esetekben célszerű alkalmazni a tábla szinonimáját:

CREATE SYNONIM test FOR  scott.emp_copy;

A scott.emp_copy tábla megkapta a test szinonimát. Ha ezek után megváltozik a tábla tulajdonosa, akkor továbbra is a táblára lehet hivatkozni a szinonima segítségével

SELECT * FROM test;

A PUBLIC opció esetén az adott privilégiumokat az összes felhasználóra érvényes, ami azt jelenti, hogy az objektum nyilvános. A Nyilvános objektum elérhető, látható az összes felhasználó számára.

GRANT SELECT, UPDATE ON emp_copy TO PUBLIC;

Ha a privilégium tartalmazza az ANY opciót, akkor az az AB összes táblájára érvényes. A következő privilégiumok esetén:

DELETE ANY TABLE

UPDATE ANY TABLE

INSERT ANY TABLE,

a felhasználó az AB összes tábláit módosíthatja, még akkor is, ha nem tulajdonosa a táblának. Ezt a legnagyobb lehetséges jogosultság, és csak az adminisztrátor szintű felhasználó kaphatja azt.

A SESSION_PRIVS nézetben megtalálhatók az aktuális privilégiumok. Az ALL_TAB_PRIVS és ALL_COL_PRIVS nézetekből pedig megtudhatjuk, hogy milyen privilégiumokkal rendelkeznek a felhasználók.

Szerepkörök (ROLE)

Szerepkör – a privilégiumok együttese, és lehetőség a felhasználók csoportosítására. A felhasználókat egy csoportba lehet beosztani, és a csoporton belül mindegyik felhasználó azonos jogosultsággal rendelkezik. Például, létrehozunk egy szerepkört

CREATE ROLE kozos;

utána a szerepkörhöz privilégiumokat kapcsolunk

GRANT INSERT ON table_a TO kozos;

GRANT INSERT ON table_b TO kozos;

GRANT INSERT, DELETE  ON table_c  TO kozos;

GRANT UPDATE ON table_d TO kozos;

GRANT DELETE ON  table_e TO kozos;

GRANT SELECT  ON table_f TO kozos;

Ha a felh_1 és felh_2  felhasználók megkapják a kozos szerepkört

GRANT kozos  TO x;

GRANT kozos  TO y;

akkor rendelkezni fognak az összes kozos szerepkörhöz tartozó jogosultságokkal.

Beépített szerepkörök

Az Oracle-ban léteznek beépített szerepkörök is:

CONNECT-

ALTER SESSION

CREATE CLUSTER

CREATE DATABASE LINK

CREATE SEQUENCE

CREATE SESSION

CREATE SYNONIM

CREATE TABLE

CREATE VIEW

RESOURSE-

CREATE CLUSTER

CREATE PROCEDURE

CREATE  SEQUENCE

CREATE TABLE


Jogosultság a programok végrehajtására

Az AB-ban tárolódhatók

Eljárások (PROCEDURE)

Csomagok (PACKAGE)

Függvények  (FUNCTION).

Ezek az objektumok csak akkor érhetők el, ha a felhasználó EXECUTE privilégiummal rendelkezik.

Példa.

GRANT EXECUTE  ON my_package TO PUBLIC;

GRANT EXECUTE  ON my_func TO felh_2;

GRANT EXECUTE  ON my_proc TO felh_1;

Privilégiumok  visszavonása

Az AB adminisztrátor a privilégiumot a felhasználótól a REVOKE parancs által visszavonhat:

REVOKE privilégium ON objektum FROM felhasználó

[CASCADE CONSTRAINTS];

A CASCADE CONSTRAINTS a REFERENCES privilégium megvonása esetén törli az összes hivatkozási integritási megszorítást, amelyet a felhasználó hozott létre (CASCADE – lépcsőzetes).

REVOKE UPDATE ON emp_copy FROM test_user;

Ez a parancs után a test_user felhasználó nem módosíthatja a emp_copy táblát, de a SELECT parancsot továbbra is alkalmazhatja.



REVOKE  SELECT ON classes FROM user_1;

REVOKE ALTER TABLE, EXECUTE ANY PROCEDURE FROM user_2;

REVOKE  kozos FROM felh_1;

A lekérdezések, a QL

A legalapvetőbb SQL parancs egy teljes tábla, illetve a tábla valamely oszlopainak lekérdezésére szolgál. Ez tulajdonképpen a projekció relációalgebrai művelet megvalósítása.

A select parancs alapjai

A parancs általános szintaxisa a következő:

SELECT [ALL|DISTINCT] * | <oszlopnévlista> FROM <táblanév>

A parancs első részében a * jel azt jelenti, hogy a lekérdezés a teljes táblára vonatkozik, minden oszlopot, mezőt látni szeretnénk a lekérdezésben.

Az <oszlopnévlista> segítségével adhatjuk meg azt, hogy a lekérdezés eredményeképpen melyik oszlopokat kívánjuk megjeleníteni, amennyiben nem az összeset.

Lehetőség van különböző módosításokra, például a listában szereplő elemeket átnevezhetjük, esetleg kifejezéseket képezhetünk belőlük. Ennek módja, hogy a mezőneveket követően egy AS kulcsszó után megadjuk az alias nevet. Ez csak a lekérdezés idejére jön létre:

SELECT nev as „NÉV” …

Emellett alkalmazhatunk különböző függvényeket is, amelyek egy része az aggregáló függvények, másik része pedig olyan függvények, amelyeket a gazdanyelv biztosít az SQL környezet számára.

Az eredménytáblában az <oszlopnév> paraméterben megadott oszlopnév szerepel.

A parancsban használható ALL és DISTINCT kulcsszavaknak speciális jelentése van. Előfordulhat, hogy egy lekérdezés végrehajtása után a keletkezett elemek között ugyanaz a rekord többször előfordul.

Ilyenkor az ALL opció alkalmazásakor, illetve alapértelmezés szerint az azonos előfordulások többszörösen fognak megjelenni az eredményben.

Amennyiben a DISTINCT opciót alkalmazzuk, akkor minden azonos előfordulás csak egyszer jelenik meg az eredménytáblában.

A számított mezők és az aggregáló függvények

Amennyiben az oszlopkifejezésben aggregáló függvényt használunk, akkor az eredménytáblában nem a tábla egyes előfordulásainak adatai jelennek meg, hanem azoknak a megfelelő aggregáltját fogja képezni a parancs. A tábla soraiból álló halmazra végrehajtódik a megfelelő függvény.

 Az <oszlopkifejezés> a következő aggregáló függvényeket tartalmazhatja:

•        COUNT: Megadja a tábla sorainak számát. A pontos eredmény elérése céljából használjuk a *-ot vagy az elsődleges kulcs mezőt paraméternek.

•        SUM: Megadja a paraméterében szereplő oszlop adatainak az összegét az összes rekordra. Csak numerikus attribútumra alkalmazható.

•        AVG: Megadja a paraméterében szereplő oszlop adatainak az átlagát az összes rekordra. Csak numerikus attribútumra alkalmazható.

•        MIN: Megadja a paraméterében szereplő oszlop adatainak a minimumát az összes rekordra. Csak numerikus attribútumra alkalmazható.

•        MAX: Megadja a paraméterében szereplő oszlop adatainak a maximumát az összes rekordra. Csak numerikus attribútumra alkalmazható.

Tekintsünk néhány példát az egyszerű lekérdezésekre:

Ehhez használjuk a dolgozó táblát, ahol egy cég dolgozóinak nevét, születési idejét, lakhelyének városát és havi fizetését tároljuk.

Dolgozo

 
ID

Neve

Szdatum

Varos

Fizetes

12

Kiss Szilárd

1985.01.05

Eger

120000

13

Nagy József

1973.05.06

Eger

156000

15

Gipsz Jakab

1955.11.01

Miskolc

210000

17

Kovács Piroska

1996.07.15

Budapest

189000

1. példa

A teljes tábla lekérdezése a következő paranccsal történhet:

SELECT * FROM Dolgozo

2. példa:

Tegyük fel, hogy szeretnénk lekérdezni az egyes dolgozók nevét a fizetésükkel együtt. A megfelelő parancs a következő:

SELECT Neve, Fizetes FROM Dolgozo

A keletkezett eredménytábla a következőképpen néz ki:

Neve

Fizetes

Kiss Szilárd

120000

Nagy József

156000

Gipsz Jakab

210000

Kovács Piroska

189000

3. példa

Készítsünk olyan eredménytáblát, amely tartalmazza a dolgozók nevét, fizetését, valamint a 20%-kal megemelt fizetéseket. Az új oszlop neve legye A dolgozó emelt fizetése. A feladatot az alábbi paranccsal oldhatjuk meg:

SELECT Neve, Fizetes, 1.2*Fizetes AS „Emelt fizetés” FROM Dolgozo

A keletkezett eredménytábla a következő:

Neve

Fizetes

Emelt fizetés

Kiss Szilárd

120000

144000

Nagy József

156000

187200

Gipsz Jakab

210000

252000

Kovács Piroska

189000

226800

4. példa

Jelenítsük meg a dolgozók városát úgy, hogy a listában ne legyen két azonos érték.

A feladatot az alábbi paranccsal oldhatjuk meg:

SELECT DISTINCT Varos FROM Dolgozo

A keletkezett eredménytábla a következő:

Varos

Eger

Miskolc

Budapest

5. példa

Készítsünk olyan táblázatot, amely megadja a vállalat dolgozóinak számát, az összes, az átlagos, a legkisebb és a legnagyobb fizetéseket.

A feladatot a következő módon oldhatjuk meg:

SELECT COUNT(id) as „A dolgozók száma”,

        SUM(fizetes) as „Összes fizetés”,

        AVG(fizetes) as „Átlagos fizetés”,

        MIN(fizetes) as „Legkisebb fizetés”,

        MAX(fizetes) as „Legnagyobb fizetés”

FROM Dolgozo

A keletkezett eredménytábla a következő:

A dolgozók száma

Összes fizetés

Átlagos fizetés

Legkisebb fizetés

Legnagyobb fizetés

4

675000

168750

120000

210000

6. példa

Készítsünk olyan táblázatot, amely megadja a vállalat dolgozóinak nevét és születési évét. A születési évet tartalmazó oszlop neve legyen ’Születési év’.

A feladatot az Oracle függvényével oldjuk meg.

SELECT Neve, TO_CHAR(szdatum, ’yyyy’) as „Születési év” FROM dolgozo

Ahol a TO_CHAR függvény a szdatum mezőben tárolt dátum típusú értéket a megadott formátumúra alakítja, esetünkben 4 jegyű évvé.

A keletkezett eredménytábla a következő:

Neve

Születési év

Kiss Szilárd

1985

Nagy József

1973

Gipsz Jakab

1955

Kovács Piroska

1996

Szűrések, a where záradék

A kiválasztást végrehajtó parancsnál a FROM után a következő szintaxisnak megfelelően adhatjuk meg az alparancsot:

[WHERE <feltétel>]

A feltételben operanduszok és operátorok szerepelhetnek.

Az operátorok  összehasonlító (<,>,<=,>=,<>), aritmetikai (+,-,*,/) és logikai műveletek (AND, OR, NOT), míg az operanduszok lehetnek konstansok, reláció attribútumok, azaz oszlopnevek, illetve függvényhivatkozások.

A műveletekre érvényesek a szokásos precedencia szabályok, amelyeket természetesen zárójelezéssel felülbírálhatunk. Ügyelnünk kell arra, hogy a kifejezésnek mindig logikai értéket kell szolgáltatni, ugyanis az eredménytáblába azok az előfordulások fognak bekerülni, amelyekre a megadott kifejezés igaz (TRUE) értéket szolgáltat.

Létezik néhány jól használható predikátum függvény

 Ezek közül az első a BETWEEN predikátum függvény, amely a következőképpen használható:

[<oszlopkifejezés> BETWEEN <alsóérték> AND <felsőérték>]

Az <alsóérték>, illetve <felsőérték> valamely ismert elemi típusnak (numerikus, dátum) megfelelő konstans, az <oszlopkifejezés> ugyanilyen típusú kifejezés, amely oszlopnevekből van képezve. Eredményként azok a rekordok fognak eleget tenni a feltételnek, amelyekre a kifejezés értéke a két konstans közé esik. Esetleg egyenlő valamelyikkel. Tehát a BETWEEN zárt intervallumot ír le.

Ezért a feltételnek akkor van értelme, ha az <alsóérték> paraméterben megadott konstans kisebb, mint a <felsőérték> paraméterben megadott.

Természetesen a predikátum helyettesíthető összehasonlító operátorokból összeállított logikai kifejezéssel:

fizetes between 100000 and 200000

ugyan azt jelenti, mint

(fizetes >= 100000) and (fizetes <= 200000)

A következő az IN predikátum, amely a következőképpen használható:

[<oszlopkifejezés> [NOT] IN <értéklista>]     

A kifejezés hatására annak vizsgálata történik meg, hogy az <oszlopkifejezés> értéke szerepel-e a megadott értéklistában, vagy nem. Amennyiben szerepel a kifejezés értéke igaz lesz. Amennyiben használjuk a NOT kulcsszót, a kifejezés akkor lesz igaz, ha az értéke nem szerepel a listában. Az <értéklista> paraméterben tehát a kifejezés típusának megfelelő értékeket kell vesszővel elválasztva felsorolni.

A predikátum kiemelt szerepet kap a beágyazott lekérdezéseknél.

A harmadik fajta predikátum karakterlánc típusú kifejezésre alkalmazható. Általános formája az alábbi:

[<oszlopkifejezés> LIKE <karakterlánc>]

A <karakterlánc> konstansban idézőjelek között adhatunk meg karaktersorozatot.

 A karaktersorozatban két karakternek speciális jelentése van, ezek a % illetve az  _  jelek.

 Az <oszlopkifejezés> paraméternek karakteres értéket kell szolgáltatni, amely összehasonlításra kerül a konstanssal. Amennyiben a kettő megegyezik, a feltétel igaz lesz. Ha a konstansban a % jelet használjuk, akkor a két karakterláncnak csak eddig a jelig kell egyezni ahhoz, hogy a feltétel igaz legyen. Ennek megfelelően a % tetszőleges számú karaktert helyettesít. Az _ jel pontosan egy karaktert helyettesít.

7. példa

Készítsünk olyan táblázatot, amely megadja a vállalat azon dolgozóinak nevét és fizetését, akik 150000 Ft felett keresnek!

A feladatot az alábbi paranccsal oldhatjuk meg:

SELECT Neve, Fizetes FROM Dolgozo

WHERE Fizetes >= 150000

A keletkezett eredménytábla a következő:

Neve

Fizetes

Nagy József

156000

Gipsz Jakab

210000

Kovács Piroska

189000

8. példa

Készítsünk olyan táblázatot, amely megadja a vállalat azon dolgozóinak nevét és fizetését, akik Egerben laknak és a fizetésük 150 000 Ft felett van!

A feladatot az alábbi paranccsal oldhatjuk meg:

SELECT Neve, Fizetes FROM Dolgozó

WHERE (Varos = ’Eger’) AND (Fizetes >= 150000)

A keletkezett eredménytábla a következő:

Neve

Fizetes

Nagy József

156000

9. példa

Készítsünk olyan táblázatot, amely megadja a vállalat azon dolgozóinak nevét és fizetését, akiknek a fizetése 150000 és 200000 Ft közé esik!

A feladatot a következő módon oldhatjuk meg:

SELECT Neve, Fizetes FROM Dolgozó

  WHERE Fizetes BETWEEN 150000 AND 200000

A feladatot megoldhatjuk másképpen is:

SELECT Neve, Fizetes FROM Dolgozó

  WHERE (Fizetes >= 150000) and (Fizetes <= 200000)

A keletkezett eredménytábla a következő:

Neve

Fizetes

Nagy József

156000

Kovács Piroska

189000

10 példa

Készítsünk olyan táblázatot, amely megadja a vállalat azon dolgozóinak nevét és városát akik Egerben, Szegeden vagy Debrecenben laknak!

A feladatot a következő módon oldhatjuk meg:

SELECT Neve, Varos FROM dolozo

WHERE Varos IN („Eger”,”Szeged”,”Debrecen”)

A keletkezett eredménytábla a következő:

Neve

Varos

Kiss Szilárd

Eger

Nagy József

Eger

11. példa

Készítsünk olyan táblázatot, amely megadja a vállalat Kovács nevű dolgozóinak adatait!

A feladatot a következő módon oldhatjuk meg:

SELECT * FROM Dolgozo WHERE Neve LIKE "Kovács%"

A keletkezett eredménytábla a következő:

ID

Neve

Szdatum

Varos

Fizetes

17

Kovács Piroska

1996.07.15

Budapest

189000

Csoportosító lekérdezések, a group by és a having használata, rendezés
A csoportosítás azt jelenti, hogy a rekordokat egy vagy néhány adott mező értékei szerint csoportokra bontjuk. Ezután a csoportokhoz tartozó rekordokra különböző műveleteket hajthatunk végre, például alkalmazhatjuk a már ismert aggregációs függvényeket, esetleg a csoportokra vonatkozóan kiválasztó műveletet alkalmazhatunk.

A csoportosítás a következő utasítással hajtható végre:

GROUP BY <oszlopnév>,[<oszlopnév>]…

A csoportosítás a megadott oszlopnevek azonos értékei alapján fog történni. Amennyiben több oszlopot adunk meg, akkor az első oszlop azonos értékein belül a második oszlop azonos értékei szerint csoportosít, majd a harmadik szerint, stb. Az egyes mezőkhöz tartozó értékeket a megadás sorrendjében egymás mellé rakja a rendszer, és az így kapott minta alapján csoportosít.

A művelet végrehajtása után minden egyes csoportra egy sor keletkezik az eredménytáblában.

 Mivel speciális utasításról van szó, használata során a SELECT parancs egyéb részeire vonatkozóan is megkötéseket kell tennünk.

Így a lekérdezendő oszlopok adataira vonatkozóan mindenképpen alkalmaznunk kell valamilyen aggregáló operátort, vagy ha ezt nem tesszük, akkor az oszlopnak szerepelnie kell a csoportosításban részt vevő oszlopok között, azaz a GROUP BY után.

Az SQL nyelv lehetőséget biztosít arra is, hogy az aggregálással keletkezett adatokra vonatkozóan feltételeket adhassunk meg. Ebben az esetben a HAVING szót kell használnunk.

Az alparancs formája az alábbi:

HAVING <csoportfeltétel>

A <csoportfeltétel> paraméterben a hagyományos módon adhatunk meg feltételeket, azzal a különbséggel, hogy a feltételben szereplő oszlopneveknek tartalmazniuk kell valamilyen aggregáló operátort, és ennek ugyancsak szerepelnie kell a SELECT után.

Az SQL lehetőséget biztosít arra, hogy lekérdezéseink eredményét rendezetten jelenítsük meg. ORDER BY alparancs. Formája a következő:

ORDER BY <oszlopnév|oszlopsorszám> [ASC|DESC], <oszlopnév| oszlop-sorszám> [ASC|DESC]]…

A rendezés a megadott oszlopok szerint történik. Első szempontként az első oszlopot, további szempontként az utána megadott oszlopokat veszi figyelembe. Az oszlopok kétféleképpen adhatók meg.

Egyrészt hagyományosan a nevükkel, másrészt egy számmal, ami a táblázatban az oszlop sorszáma. A számozás 1-től kezdődik a táblázat fejrészében megadott sorrend szerint.

 Lényeges hogy a rendezési szempontként megadott oszlopnak szerepelni kell a SELECT parancs után is. Fontos még az ASC és a DESC kulcsszavak jelentése.

Az ASC az alapértelmezés, ami azt jelenti, hogy a rendezés növekvő sorrend szerint történik.

Amennyiben a DESC kulcsszót használjuk, akkor a megadott szempontnál a rendezés csökkenő lesz.

A következőkben nézzünk néhány példát a csoportosító, illetve a rendező SQL parancsok használatára.

12. példa

Készítsünk olyan listát, amely megadja városonként, hogy az adott városban hány dolgozó lakik!

Az alábbi parancsot használhatjuk:

SELECT Varos, COUNT(id) as „DB”  FROM dolgozo

GROUP BY Varos

Eredményképpen a következő táblát kapjuk:

Varos

DB

Eger

2

Miskolc

1

Budapest

1

13. példa

Készítsünk olyan listát, amely megadja városonként, hogy az ott lakó dolgozóknak mennyi az összes illetve az átlagfizetése!

Az alábbi parancsot használhatjuk:

SELECT varos, sum(Fizetes) as „Összes”, avg(Fizetes) as „Átlag”

FROM dolgozo

GROUP BY Varos

Eredményképpen a következő táblát kapjuk:

Varos

Összes

Átlag

Eger

276000

138000

Miskolc

210000

210000

Budapest

189000

189000

14. példa

Készítsünk olyan listát, amely megadja azokat a városokat, amelyekre igaz, hogy az adott városban élő dolgozóknak az átlagfizetése legfeljebb 150000 Ft!

Az alábbi parancsot használhatjuk:

SELECT varos, avg(Fizetes) as „Átlag”

FROM dolgozo

GROUP BY Varos

HAVING avg(Fizetes) <= 150000

Eredményképpen a következő táblát kapjuk:

Varos

Átlag

Eger

138000

15. példa

Készítsünk olyan listát, amely a dolgozók nevét és fizetését a fizetés szerint csökkenő, illetve azonos fizetés esetén név szerint ábécé sorrendben adja meg!

Az alábbi parancsot használhatjuk:

SELECT Neve, Fizetes FROM dolgozo

ORDER BY Fizetes DESC, Neve

Eredményképpen a következő táblát kapjuk:

Neve

Fizetes

Gipsz Jakab

210000

Kovács Piroska

189000

Nagy József

156000

Kiss Szilárd

120000

Táblák összekapcsolása
A legtöbb esetben az adatbázis szerkezete olyan, hogy a szükséges információk több táblában találhatók. Különösen igaz ez, ha normalizált relációkkal dolgozunk, hiszen mint láttuk, a normalizálás alaptevékenysége a több relációra bontás. Ilyen esetekben az információk összegyűjtéséhez minden táblára szükségünk van, ezért a lekérdezéseket ki kell terjeszteni úgynevezett többtáblás lekérdezésekké.

Az SQL nyelv lehetőséget biztosít az összekapcsolás relációalgebrai művelet közvetlen megvalósítására. Ez azt jelenti, hogy speciális utasítások állnak rendelkezésre, amelyek az összekapcsolás különböző fajtáit adják meg.

A legelső ilyen parancs magát a Descartes szorzatot hozza létre. A parancs megadásánál csak a FROM kulcsszó utáni részt definiáljuk:

<táblanév> CROSS JOIN <táblanév>

A parancs hatására a megadott két tábla Descartes szorzatát képezi a rendszer. Mint tudjuk, sokkal természetesebb a feltételen alapuló összekapcsolás.

Ennek formája az alábbi:

<táblanév> INNER JOIN <táblanév> ON <feltétel>

Feltételként tetszőleges, a WHERE parancs után használható feltételt adhatunk meg. Esetek legnagyobb részében a két kapcsolt táblából az egyik tábla (Nevezzük master-nek) elsődleges kulcs mezőjének értéke egyenlő a másik tábla (nevezzük detail-nek) idegen kulcs mezőjének értékével. Mivel a relációs adatbázis kezelők nem kezelik közvetlenül a több-több kapcsolatot, így ez a megfeleltetés egyértelmű.

Ezt az összekapcsolást szoros belső kapcsolatnak is nevezzük.

Tekintsük az alábbi Kifizetes táblát, ami a dolgozóknak történt kifizetések adatait tartalmazza. Ez egy – több kapcsolatban van a már ismert Dolgozo táblánkkal.

Dolgozo

 
ID

Neve

Szdatum

Varos

Fizetes

12

Kiss Szilárd

1985.01.05

Eger

120000

13

Nagy József

1973.05.06

Eger

156000

15

Gipsz Jakab

1955.11.01

Miskolc

210000

17

Kovács Piroska

1996.07.15

Budapest

189000

Kifizetes

 
ssz

Datum

Osszeg

Dolgozo

101

2012.01.05

120000

12

102

2012.01.05

160000

13

103

2012.01.05

200000

15

104

2012.02.05

123000

12

105

2012.02.05

240000

15

A két tábla között a kapcsolatot a Dolgozo tábla ID elsődleges kulcsa és a Kifizetes tábla dolgozo idegen kulcs mezője tartja.

16.példa

Készítsünk olyan listát, amely a dolgozók nevét és a számukra kifizetett összeget, és a kifizetés dátumát név szerint ábécé sorrendben adja meg!

Az alábbi parancsot használhatjuk:

SELECT dologzo.neve as „Név”,

  kifizetes.osszeg as „Összeg”,

  kifizetes.datum as „Dátum”

FROM dolgozo INNER JOIN kifizetes ON dolgozo.id = kifizetes.dolgozo

ORDER BY 1

A keletkezett eredménytábla az alábbi:

Név

Összeg

Dátum

Gipsz Jakab

200000

2012.01.05

Gipsz Jakab

240000

2012.02.05

Kiss Szilárd

120000

2012.01.05

Kiss Szilárd

123000

2012.02.05

Nagy József

160000

2012.01.05

Látható, hogy a Kovács Piroska nevű dolgozó nem kapott még kifizetést, nem is került be az eredmény táblába. Amennyiben szeretnénk azokat a rekordokat is megjeleníteni, melyekhez nincs kapcsolt rekord a kapcsolt táblában külső összekapcsolást kell alkalmazni.

Ezek tulajdonképpen annak a problémának a kezelésére használhatók, ami akkor jelentkezik, ha a két összekapcsolandó tábla valamely sorához nem tartozik a másik táblából elem. Ilyenkor az összekapcsolás művelet definíciója alapján ez a sor nem kerül be az eredménytáblába. A gyakorlatban előfordulhat, hogy ezekre a "lógó" előfordulásokat is szeretnénk szerepeltetni az összekapcsolt relációban. Ilyenkor használhatjuk a külső összekapcsolásokat. Egy külső összekapcsolás abban különbözik a hagyományostól, hogy az eredménybe minden olyan sor is bekerül, amely a másik tábla egyetlen sorához sem kapcsolódik. Egy külső összekapcsolás háromféle módon valósulhat meg. Az egyik mód az, amikor mindkét tábla "lógó" sorai bekerülnek az eredménybe, a másik kettő pedig amikor csak a bal, illetve a jobboldali tábláé.

Ennek megfelelően a következő esetek lehetnek:

<táblanév> FULL OUTER JOIN <táblanév> ON <feltétel>     

Ebben az esetben mindkét tábla "lógó" sorai bekerülnek az eredmény táblába.

<táblanév> LEFT OUTER JOIN <táblanév> ON <feltétel>

Ebben az esetben csak az első <táblanév> paraméterben megadott tábla "lógó" sorai kerülnek be az eredmény táblába.

<táblanév> RIGHT OUTER JOIN <táblanév> ON <feltétel>

Ebben az esetben pedig a második <táblanév> paraméterben megadott tábla "lógó" sorai kerülnek be az eredmény táblába.

17. példa

Készítsünk olyan listát, amely az előző példában szereplő feladatot úgy oldja meg, hogy a listába azok a dolgozók is belekerülnek, akiknek nem történt kifizetés.

A megoldás a következő:

SELECT dologzo.neve as „Név”,

  kifizetes.osszeg as „Összeg”,

  kifizetes.datum as „Dátum”

FROM dolgozo LEFT OUTER JOIN kifizetes

  ON dolgozo.id = kifizetes.dolgozo

ORDER BY 1

A keletkezett eredménytábla az alábbi:

Név

Összeg

Dátum

Gipsz Jakab

200000

2012.01.05

Gipsz Jakab

240000

2012.02.05

Kiss Szilárd

120000

2012.01.05

Kiss Szilárd

123000

2012.02.05

Nagy József

160000

2012.01.05

Kovács Piroska

null

null

Előfordulhat, hogy esetleg több táblát kell összekapcsolni. Ekkor is teljesen hasonlóan járunk el. Minden összekapcsolás egy JOIN parancs lesz.

Tehát ha 5 táblát szeretnénk összekapcsolni, akkor 4 JOIN lesz a lekérdezésünkben.

Tekintsük a dolgozók részvételét a céges projektekben. Ez több-több kapcsolat, így egy kapcsolótáblára is szükség lesz. Ebben az esetben így néz ki az adatbázisunk:



18. példa

Listázzuk ki a projektek neveit a résztvevő dolgozók neveivel rendezetten.

A megoldás a következő:

SELECT p.pNev, d.Nev

FROM dolgozo d

INNER JOIN DolgProj dp ON d.id = dp.dolgozo

        INNER JOIN projekt p ON dp.projekt = p.idProjekt

ORDER BY p.pNev, d.nev

Látható, hogy alkalmaztuk a táblák nevének rövidítését. A dolgozo tábla d-vel lett jelölve. Ennek célja a rövidebb írásmód a lekérdezésen belül. Szabály, hogy ha egy tábla nevét rövidítjük, akkor a teljes lekérdezésben a rövidített nevet kell használni.

Beágyazott lekérdezések
Az SQL nyelv lehetőséget biztosít arra, hogy a kiválasztó lekérdezések feltételében is használjunk SQL lekérdező parancsot. Ilyenkor megkülönböztetünk külső és belső SELECT parancsot, és az ilyen jellegű lekérdezéseket beágyazott lekérdezéseknek nevezzük.

A SELECT parancsok szintaxisa megegyezik a már ismert formákkal, csupán arra kell ügyelnünk, hogy a belső lekérdezésekre vonatkozóan bizonyos megkötéseknek kell teljesülnie. A belső lekérdezés jellege alapján több esetet különböztethetünk meg.

Ezek a következők:

1.        A belső lekérdezés egyetlen értéket szolgáltat. Ez a legegyszerűbb eset, ugyanis ilyenkor minden a hagyományos módon történik, azzal a különbséggel, hogy a feltételként megadott kifejezésben a belső lekérdezés által szolgáltatott értéket használja fel a rendszer.

2.        A belső lekérdezés egyoszlopos relációt szolgáltat. Ekkor olyan feltételeket adhatunk meg, amelyek a belső lekérdezés által szolgáltatott oszlop adatait használja fel. Ebben az esetben különböző predikátumokat használhatunk.

 Az alábbi három predikátum létezik:

<oszlopkifejezés> [NOT] IN <belső lekérdezés>

Ennél a típusnál az <oszlopkifejezés> paraméterben megadott kifejezés értékéről fogja eldönteni a rendszer, hogy szerepel-e a belső lekérdezés által előállított oszlop adatai között. Ha igen a feltétel értéke igaz, ha nem, akkor hamis lesz. A NOT kulcsszó hatására pontosan fordítva kell értelmezni a feltételt.

A következő predikátumok formája az alábbi:

[NOT] <oszlopkifejezés> <reláció> ALL|ANY <belső lekérdezés>

Ennél a típusnál az <oszlopkifejezés> paraméterben megadott kifejezés értékére vonatkozóan azt fogja vizsgálni a rendszer, hogy a megadott reláció teljesül-e a belső lekérdezés által előállított oszlop adataira. Ha az ALL kulcsszót használjuk, a feltétel akkor lesz igaz, ha a reláció az oszlop minden elemére teljesül, míg az ANY használatakor elegendő egyetlen elemre teljesülnie. A NOT kulcsszó itt is a feltétel ellentettjét jelenti.

A legáltalánosabb eset az, amikor a belső lekérdezés általános relációt szolgáltat. Ekkor csak kétféle feltételt vizsgálhatunk.

 Az egyik az, hogy a keletkezett reláció üres vagy sem. Ehhez a vizsgálathoz az EXISTS kulcsszót kell használnunk, amit természetesen a NOT módosíthat. A parancs formája az alábbi:

[NOT] EXISTS <belső lekérdezés>

A másik eset hasonló az 2. pont első részében leírtakhoz, azzal a különbséggel, hogy a feltételben több oszlopkifejezést adhatunk meg, amelyek egyezését külön külön fogja vizsgálni a rendszer a belső lekérdezés által adott tábla sorainak elemeivel. Természetesen a megadott lista és a lekérdezés eredménytáblája sorainak száma azonos kell hogy legyen. A pontos szintaxis a következő:

(<oszlopkifejezés> [,<oszlopkifejezés>]…) [NOT] IN <belső lekérdezés>

19. példa

Készítsünk olyan listát, amely a Dolgozó táblából kilistázza azon dolgozók nevét és fizetését, akik az átlag alatt keresnek!

SELECT Neve, Fizetes FROM Dolgozo

WHERE Fizetes <

(SELECT AVG(Fizetes) FROM Dolgozo)

A keletkezett eredménytábla az alábbi:

Neve

Fizetes

Kiss Szilárd

120000

Nagy József

156000

20. példa

Készítsünk olyan listát, amely a Dolgozó táblából kilistázza azon dolgozók nevét és fizetését, akik a fizetése a legnagyobb fizetéstől legfeljebb csak 50 000 Ft-tal tér el!

Az alábbi parancsot használhatjuk:

SELECT Neve, Fizetes FROM Dolgozo

WHERE Fizetes+50000 >

(SELECT MAX(Fizetes) FROM Dolgozo)

A keletkezett eredménytábla az alábbi:

Neve

Fizetes

Kiss Szilárd

120000

Nagy József

156000

Gipsz Jakab

210000

Kovács Piroska

189000

21. példa

Az előzőekben látott Kifizetés tábla felhasználásával készítsünk olyan listát, amely a Dolgozó táblából kilistázza azon dolgozók nevét és törzsszámát, akik számára még nem történt kifizetés!

Az alábbi parancsot használhatjuk:

SELECT Neve, ID FROM Dolgozo

WHERE ID NOT IN

(SELECT ID FROM Kifizetes)

Egy másik lehetséges megoldás:

SELECT Neve, ID FROM Dolgozo

WHERE NOT EXISTS

(SELECT ID FROM Kifizetes

WHERE Kifizetes.dolgozo = Dolgozo.ID)

Figyeljük meg, hogy ez a második megoldás egy olyan speciális esetet foglal magába, amikor a belső lekérdezésben felhasználjuk a külső lekérdezés táblájának egy mezőjét is.

A keletkezett eredménytábla az alábbi:

Neve

ID

Kovács Piroska

17

22. példa

Készítsünk olyan listát, amely a Dolgozó táblából kilistázza azon dolgozók adatait, akik minden Egerben vagy Szegeden lakó dolgozónál többet keresnek!

Az alábbi parancsot használhatjuk:

SELECT * FROM Dolgozo

WHERE Fizetes > ALL

(SELECT Fizetes FROM Dolgozo

WHERE Varos = "Eger" OR Varos = "Szeged")

ID

Neve

Szdatum

Varos

Fizetes

15

Gipsz Jakab

1955.11.01

Miskolc

210000

17

Kovács Piroska

1996.07.15

Budapest

189000

Feladatok
feladat

Alkotás – Alkotó

Az alkotásról tudjuk, hogy melyik évben készült, mi a műfaja (szobor, festmény, rajzkönyv stb) és mennyi az értéke forintban, ismerjük a címét, a művészt, a művész születési évét, a nemzetiségét. Egy alkotás mindig egy műfajhoz tartozik, és a művészek is egy nemzetnek fiai. A művészek akár teljes munkásságát is tudjuk feldolgozni!

Leadandó az adatbázis terv táblanevekkel, mezőnevekkel, mezőtípusokkal, kapcsolatokkal rajzban.

A táblákat létrehozó DDL parancsok.

Az adatfeltöltést végző DML utasítások.

A válaszokat adó QL, és DDL, DML parancsok.

1.        Tervezzen minimum 3NFben lévő adatbázist a fenti probléma kezelésére!

2.        Vegyen fel minimum 5 alkotót, és minimum 10 alkotást. Legyen legalább 3 féle műfaj és nemzet is. Töltse fel a megfelelő sorrendben a létrehozott adattáblákat.

3.        Válaszoljon SQL lekérdezésekkel az alábbi kérdésekre.

a.         Listázza ki a XX. Századi alkotások címeit,a szerző nevével, Szerző neve alapján növekvő sorrendben.

b.        Listázza ki a szobrok minden adatát.

c.         Listázza ki a magyar művészek adatait névsor szerint csökkenő sorrendben.

d.         Határozza meg nemzetenként az alkotások összes értékét.

e.         Listázza ki az olasz alkotók alkotásainak átlagértékénél magassabb értékű alkotásokat az alkotók neveivel.

f.         Listázza ki a műfajok neveit, de mindegyik csak egyszer szerepelhet.

g.        Listázza ki nemzetenként az összes alkotás darabszámát a nemzetek nevével, de csak a 3nál több alkotást jegyzők jelenjenek meg!

h.        Vegyen fel a megfelelő táblába egy plusz mezőt, mely az alkotó nemzetközi díjainak számát tartalmazza.

Megoldás az SQL lekérdezésekre:

A.

select al.cim, sz.nev

from alkotas al

        inner join K1 on al.al_id = K1.al_id

        inner join alkoto sz on k1.a_id = sz.a_id

where  al.al_ev between 1901 and 2000

order by sz.nev;

A/2

select al.cim, sz.nev

from alkotas al, K1, alkoto sz

where

(al.al_id = K1.al_id) and

(k1.a_id = sz.a_id) and

(al.al_ev between 1901 and 2000)

order by sz.nev;

B.     

select sz.nev, al.*, n.n_nev

from alkotas al

        inner join K1 on al.al_id = K1.al_id

        inner join alkoto sz on k1.a_id = sz.a_id

        inner join nemzet n on n.n_id = sz.n_id

        inner join mufaj m on m.m_id = al.m_id

where m.m_nev = "szobor"

order by al.al_nev

C.

select sz.*

from alkoto sz

        inner join nemzet n on n.n_id = sz.n_id

where n.n_nev = "magyar"

order by sz.a_nev desc;

D.

select n.n_nev as nemzet, sum(al.al_ertek) as osszes

from alkotas al

        inner join K1 on al.al_id = K1.al_id

        inner join alkoto sz on k1.a_id = sz.a_id

        inner join nemzet n on n.n_id = sz.n_id     

group by n.n_nev;

E.

select al.al_nev, sz.a_nev

from alkotas al

        inner join K1 on al.al_id = K1.al_id

        inner join alkoto sz on k1.a_id = sz.a_id

where

al.al_ertek >

        (

        select avg(al.al_ertek)

        from alkotas al

        inner join K1 on al.al_id = K1.al_id

        inner join alkoto sz on k1.a_id = sz.a_id

        inner join nemzet n on n.n_id = sz.n_id

        where n.n_nev = "olasz"

        )

order by sz.a_nev;

F.

select m.m_nev

from mufaj m

where m.m_id not in

(

        select distinct m.m_id

        from mufaj m

                inner join alkotas al on m.m_id = al.m_id

)

order by m.m_nev;

G.

select n.n_nev as nemzet, count(al.al_id) as szamossag

from alkotas al

        inner join K1 on al.al_id = K1.al_id

        inner join alkoto sz on k1.a_id = sz.a_id

        inner join nemzet n on n.n_id = sz.n_id

group by n.n_nev

having count(al.al_id) > 3

2. feladat

Alaptáblák

ember [ id integer primary key, nev varchar(40) not null, varos varchar(40) ]

auto [ rsz char(7) primary key, tulaj int, tipus varchar(20), szin varchar(20), ar numeric(7,0) ]

Feladatok:

1.        Kérdezzük le a piros színű autók árait.

2.        Az 500000 és 1000000 Ft közötti értékű autók árát növeljük 20%-al.

3.        Kérdezzük le a ’K’ betűvel kezdődő tulajdonosokat és autóik típusát.

4.        Kérdezzük le a miskolci és egri autótulajdonosok nevét és autóik árát, a tulajdonosok szerint névsorrendbe rendezve.

5.        Kérdezzük le azoknak az autótulajdonosoknak a nevét, akiknek 1 millió Ft-nál olcsóbb autójuk van.

6.        Kérdezzük le azon autótulajdonosoknak a nevét és címét, akiknek van autójuk.

7.        Azoknak az autóknak a rendszámát kérdezzük le, amelyeknek miskolci a tulajdonosuk.

8.        Kérdezzük le azokat az autókat, amelyeknek az ára nagyobb minden piros autóénál.

9.        Kérdezzük le a miskolci autók átlagárát.

10.        Kérdezzük le, hogy az egyes városokban hány autó van.

11.        Kérdezzük le az átlagárnál drágább autók rendszámát és tulajdonosaik nevét.

12.        Kérdezzük le azon autótulajdonosok számát lakóhelyük szerint csoportosítva és rendezve, akiknek egy és csakis egy autójuk van.

Megoldás az SQL lekérdezésekre:

1. Kérdezzük le a piros színű autók árait.

SELECT ar FROM auto WHERE szin = ’piros’;

2.        Az 500000 és 1000000 Ft közötti értékű autók árát növeljük 20%-al.

UPDATE auto SET ar=ar*1.2 WHERE ar BETWEEN 500000 AND 1000000



3.        Kérdezzük le a ’K’ betűvel kezdődő tulajdonosokat és autóik típusát.

SELECT e.nev, a.tipus

FROM ember e inner join

                auto a on e.id = a.tulaj

WHERE e.nev LIKE 'K%';

4.        Kérdezzük le a miskolci és egri autótulajdonosok nevét és autóik árát, a tulajdonosok szerint névsorrendbe rendezve.

SELECT ember.nev, auto.ar

FROM ember e inner join

                auto a on e.id = a.tulaj

WHERE  varos IN ('Miskolc', 'Eger')

ORDER BY ember.nev;

5.        Kérdezzük le azoknak az autótulajdonosoknak a nevét, akiknek 1 millió Ft-nál olcsóbb autójuk van.

SELECT ember.nev

FROM ember e inner join

                auto a on e.id = a.tulaj

WHERE  auto.ar < 1000000;

6.        Kérdezzük le azon autótulajdonosoknak a nevét és címét, akiknek van autójuk.

SELECT nev, varos

FROM ember

WHERE id IN (SELECT tulaj FROM auto);



7.        Azoknak az autóknak a rendszámát kérdezzük le, amelyeknek miskolci a tulajdonosuk.

SELECT a.rsz

FROM ember e inner join

                auto a on e.id = a.tulaj

WHERE e.varos LIKE ’Misk%’;

8.        Kérdezzük le azokat az autókat, amelyeknek az ára nagyobb minden piros autóénál.

SELECT *

FROM auto

WHERE ar >

 (SELECT max(ar) FROM auto WHERE szin LIKE ‘pir%’);



9.        Kérdezzük le a miskolci autók átlagárát.

SELECT AVG(ar)

FROM ember e inner join

                auto a on e.id = a.tulaj

WHERE varos LIKE ’Mi%’;

10.        Kérdezzük le, hogy az egyes városokban hány autó van.

SELECT varos, COUNT(*)

FROM ember e inner join

                auto a on e.id = a.tulaj

GROUP BY varos;

11.        Kérdezzük le az átlagárnál drágább autók rendszámát és tulajdonosaik nevét.

SELECT auto.rsz, ember.nev

FROM ember e inner join

                auto a on e.id = a.tulaj

WHERE  ar > (SELECT AVG(ar) FROM auto);

12.        Kérdezzük le azon autótulajdonosok számát lakóhelyük szerint csoportosítva és rendezve, akiknek egy és csakis egy autójuk van.

SELECT varos, COUNT(*)

FROM ember

WHERE nev IN (SELECT nev

FROM ember e inner join

                auto a on e.id = a.tulaj

        GROUP BY nev

        HAVING COUNT(*)=1)

GROUP BY varos

ORDER BY varos;

3. feladat

Egy videó kölcsönző

Egy video kölcsönző nyilvántartását vizsgáljuk. A kölcsönző kizárólag videofilmeket kölcsönöz, a videofilmekről nyilvántartást vezetnek, mely nyilvántartás tartalmazza a film címét, főszereplőjét, rendezőjét. A filmeknek egyedi azonosító száma van, a cím nem biztos, hogy egyedi. Egy film több kazettán is szerepelhet, a kölcsönző által készített másolatokban. A kazettáknak szintén egyedi azonosítójuk van, valamint a típusát is nyilvántartják. A kölcsönzőből kizárólag tagok kölcsönözhetnek. A tag nevét, címét, valamint egyedi sorszámát nyilvántartják. A tagok filme(ke)t kölcsönöznek, a kikölcsönzés dátumát és a visszahozás dátumát is pontosan követni kell. A kölcsönző egy nagykereskedőtől vásárolja a filmeket, minden rendelésnek van egy száma és a visszakeresés érdekében a rendelés dátumát is nyilvántartják. Egy rendelésen több film is szerepelhet.

A videó kölcsönzőnél található dokumentumok a következő adatokat tartalmazzák a különböző adatlapokon. Ezekből a dokumentumokból kell megalkotni a logikai adatbázis-modellt:



Kazetta száma

Kazetta típusa

Film címe

Film száma

Rendelés száma

Rendelés dátuma

Kölcsönzés száma

Tag sorszáma

Tag neve

Tag címe

Kölcsönzés dátuma

Visszahozás dátuma

Film főszereplője

Film rendezője

Lekérdezések

1.        Tudjuk meg azoknak a tagoknak az adatait, akik bár be vannak iratkozva, még nem kölcsönöztek egyetlen filmet sem !

2.        Tudjuk meg az összes rendelkezésre álló filmünk főszereplőjét !

3.        Állapítsuk meg az összes rendelkezésre álló film címét és rendezőjét !

4.        Jelenítsük meg azon filmek és kazetták számát és a film címét, amelyeket még soha nem kölcsönöztek ki !

5.        Listázzuk ki a mai kölcsönzők névlistáját!

6.        Listázzuk ki azokat a filmeket, amiket ma kell visszahozni!

7.        Listázzuk ki azokat a filmeket, amelyekkel már elkéstek!

8.        Készítsen filmlistát!

Megoldás az SQL lekérdezésekre:

1.

select * from tag

where tag_sorszam not in

        (select tag_sorszam from kolcsonzes)

order by nev

2.

select d.nev from dolgozo d

        inner join film_dolg fd on d.do_id = fd.do_id

        inner join dolg_tipus dt on dt.dt_id = fd.dt_id

where dt.leiras = "főszereplő"

order by d.nev

3.

select f.filmcime, d.nev from dolgozo d

        inner join film_dolg fd on d.do_id = fd.do_id

        inner join dolg_tipus dt on dt.dt_id = fd.dt_id

        inner join film f on f.filmszama = fd.filmszama

where dt.leiras = "rendező"

order by d.nev

4.

select f.filmcime, k.kazettaszama

from film f

        inner join kazetta k on f.filmszama = k.filmszama

where k.kazettaszama not in

(select disctinct k2.kazettaszama from kazetta k2

        inner join kolcsonzes ko

        on k2.kazettaszama = ko.kazettaszama)

5.

select t.nev from tag t inner join kolcsonzes k

        on k.tag_sorszama = t.tag_sorszama

where kolcsonzes_datuma = '2005.11.21'

6.     

select f.filmcime, k.kazettaszama

from film f

        inner join kazetta k on f.filmszama = k.filmszama

        inner join kolcsonzes ko

                on k.kazettaszam = ko.kazettaszama

where ko.visszahozas_datuma = '2005.11.21'

order by f.filmcime

7.

select f.filmcime, k.kazettaszama

from film f

        inner join kazetta k on f.filmszama = k.filmszama

        inner join kolcsonzes ko

                on k.kazettaszam = ko.kazettaszama

where ko.visszahozas_datuma < '2005.11.21'

order by f.filmcime

8.

select filmcime from film order by filmcime

4. feladat

Királyok

A magyar királyok uralkodásának idejét szeretnénk tárolni és feldolgozni. Ismert a királyok neve, az uralkodásuk kezdő és végső éve.

Tábla:

URALKODÓ (AZ, Név, Kezdő, Végső)

AZ        A király azonosítója (számláló), ez a kulcs

Név        A király neve (szöveg)

Kezdő        A király uralkodásának kezdő éve (szám)

Végső        A király uralkodásának befejező éve (szám)

1.        Listázza ki a királyokat névsorba! (Névsor)

2.        Írassa ki a László nevű királyokat az uralkodásuk hosszával együtt! Az Ulászlók ne szerepeljenek a listán! (Lászlók)

3.        Listázza ki a királyok nevét uralkodásuk hosszának sorrendjében csökkenően! (HosszSor)

4.        Az adatbázisból kérdezze le, hogy hány István keresztnevű uralkodónk volt? (Istvánok száma)

5.        1347-ben a királyi udvar Visegrádról átmenetileg Budára költözött. Ki volt ekkor a király? (Budai udvar)

6.        Írassa ki a 10 évnél hosszabb ideig uralkodó királyokat névsorba! (10 év)

7.        Hány király volt Magyarországon a XIV. században, azaz 1300-tól 1399-ig? Figyelem, a királyok uralkodásának csak egy része is eshetett a megjelölt időszakba! (Királyok száma)

8.        Allekérdezés segítségével határozza meg, hogy Mátyás előtt hány király uralkodott? (Mátyás előtt)

9.        Ki uralkodott legtovább? (MaxKirály)

5. feladat

Vízi sporteszközök kölcsönzése

A vízparti nyaralás során kölcsönözni lehet sporteszközöket naponta több órára és több alkalommal is.

Készítsen adatbázis-alkalmazást a kölcsönzőt üzemeltetők számára!

Az adatbázisban tárolja, hogy melyek azok az eszközök, amiket bérbe adnak, továbbá azt, hogy ezek milyen típusúak (pl. vízi-bicikli, csónak, kajak, surf), és mennyibe kerül a bérlés egy órára. Minden megkezdett óráért fizetni kell. Tárolja az ügyfeleket (kölcsönzőket) is, és azt, hogy melyik ügyfél, mikor, melyik eszközt kölcsönözte ki. A kölcsönzős megbízik az  ügyfeleiben, ezért nekik csak a nap végén egyszer, egy összegben kell fizetniük a napi kölcsönzésük után.

Mindehhez négy adattáblát készítsen, melyek a következő attribútumokat és kapcsolatokat tartalmazzák (*-gal jelöljük az elsődleges kulcsokat):

Eszkoztipus                *Tipus (szöveges), Ar_orara (szám)

Eszkoz                *Eazonosito (szám), Tipus (szöveges), Marka (szöveges)

Ugyfel                *Uazonosito (szám), Nev (szöveges), Cím (szöveges), Fizetendo (szám)

Kolcsonzes                Uazonosito (szám), Eazonosito (szám), Hany_ora (szám), Mitol (idő), Meddig (idő)

1.        Hozza létre az adattáblákat a kapott információk alapján! A meződefiniálás során a mezőméretet optimálisan válassza meg! Az idegen kulcsokat (kapcsolatokat) úgy állítsa be, hogy az adatbázis-kezelő megőrizze a hivatkozási integritást!

2.        Illessze be a táblákba a kolcson.xls munkafüzet munkalapjain levő adatokat!

3.        Készítsen lekérdezést, ami kiírja a Kolcsonzes tábla tartalmát úgy, hogy az Uazonosito mező helyett az ügyfél nevét, az Eazonosito mező helyett pedig az eszköz típusát és márkáját lássuk!

4.        Listázza ki az összes olyan eszköz típusát, ami déli 12 órakor bérbe volt adva!

5.        Készítsen lekérdezést, ami megadja az összes olyan eszköztípust (egyszer) amilyen eszközt legalább egyszer, legalább 2 órára kikölcsönöztek!

6.        Listázza ki az összes olyan eszköz minden adatát, amit nem kölcsönöztek ki

7.        Készítsen lekérdezést, ami kiszámolja, hogy egy-egy ügyfélnek összesen mennyit kell fizetni a nap végén (a napi kölcsönzéseinek darabszáma és időtartama (órák) és a bérelt eszköztípusok árai alapján)!

6. feladat

Verseny

Egy feladatmegoldó verseny eredményeinek nyilvántartására hozzon létre az alábbi adatszerkezetnek megfelelő adatbázist! (*-gal jelöljük az elsődleges kulcsokat)

Táblák:

VERSENYZŐ (VersenyzőAZ, Név, IskolaAZ, Osztály)

*VersenyzőAZ        A versenyző azonosítója (szöveg)

Név        A versenyző neve (szöveg)

IskolaAZ        A versenyző iskolájának azonosítója (szöveg)

Osztály        A versenyző évfolyama (szám)

EREDMÉNY (VersenyzőAZ, FelSorszám, Pontszám)

*E_id (szám)

VersenyzőAZ        A versenyző azonosítója (szöveg)

FelSorszám        A feladat sorszáma (szöveg)

Pontszám        Az adott feladatban a versenyző által elért pontszám (szám)

ISKOLA (IskolaAZ, IskolaNév, Irszám, Város, Utca, Házszám)

*IskolaAZ        Az iskola azonosítója (szöveg)

IskolaNév        Az iskola neve (szöveg)

Irszám        A versenyző évfolyama (szám)

Város        Az iskola város (szöveg)

Utca        Az iskola utcája (szöveg)

Házszám        Az iskola házszáma (szöveg)

A táblák közötti kapcsolatok:

ISKOLA – VERSENYZŐ: egy a többhöz típusú.

VERSENYZŐ – EREDMÉNY: egy a többhöz típusú.

Feladatok

Hozzon létre egy VERSENY nevű adatbázist a fenti táblaszerkezeteknek megfelelően!

Az alábbi mintáknak megfelelően töltse fel adatokkal a táblákat!





Minden versenyzőnek négy feladatot kellett megoldani! A feladatokat római számmal jelöljük.



Készítsen lekérdezéseket a következő kérdések megválaszolására! A feladatok megoldásának nevét a zárójelben levő betűkkel jelölje!

Írassa ki a versenyzők neveit és pontjainak összegét emelkedő sorrendben! (A)

Határozza meg feladatonként a legmagasabb pontszámot! (B)

Melyik iskolából (iskola név és város) hány versenyző indult? (C)

Gyűjtse ki azoknak a versenyzőknek a neveit ábécé rendbe, akik valamelyik feladatra 0 pontot kaptak! A felsorolásban egy név csak egyszer szerepelhet! (D)

Írassa ki azoknak a nevét, iskolája nevét és címét, akiknek van 10 pontnál magasabb pontszámú feladatuk! (E)

Az első feladatra a vidékiek közül ki kapta a legtöbb pontot? Nevét és iskolájának nevét adja meg a lekérdezés! (F)

7. feladat

Termékek

Készítse el a Termékek tábla szerkezetét az alábbi minta alapján

Termékek (Termékkód, Terméknév, Kategóriakód, Méret, Szín, Ár, Raktárkészlet)

Termékkód (számláló)

Terméknév (szöveg)

Kategóriakód (szám)

Méret (szöveg)

Szín (szöveg)

Ár (szám)

Raktárkészlet (szám)

Állítsa be a táblák kulcsait!

Változtassa meg a Termékek tábla Ár mezőjének formátumát úgy, hogy az árak egész pénznemként látszódjanak!

A feladatok megoldásánál ügyeljen a táblák közötti kapcsolatokra!

Feladatok:

Készítse el az alábbi feladatok megoldását! A zárójelben megadott néven mentse az egyes megoldásokat!

1.        Melyek azok a termékek és milyen színben illetve árban kaphatók, amelyek L-es méretűek és legalább 5 db van belőlük raktáron? Rendezze a listát úgy, hogy a legnagyobb árú termék legyen elöl! (A)

2.        Átlagosan menyibe kerülnek az egyes kategóriákba tartozó termékek? (B)

3.        Mekkora jelenleg a különböző színű és méretű pamutnadrágok és a vászonnadrágok raktárkészlet-értéke: A listában szerepeljen a termék neve, mérete, színe, kategóriája és raktárkészlet-értéke névsorrendben! (C)

4.        Melyek azok a fekete színű top-ok és milyen méretben illetve árban kaphatók, amelyekből van raktáron? (D)

5.        Összesen hány darab kötött pulóver van raktáron? (E)

6.        Melyek azok a termékek és mennyi van belőlük raktáron, amelyek ára 5 000 és 10 000 Ft között van? Rendezze a listát raktárkészlet szerint növekvő, azonos raktárkészlet esetén névsorrendbe! (F)

8. feladat

Párbajtőr

Az újkori olimpiák férfi, egyéni párbajtőr dobogós helyezetteinek adatait vizsgálja meg adatbázis-kezelő rendszer segítségével! Ezeket az információkat egy adattáblában tároljuk.

Készítsen új adatbázist parbaj néven!

A tábla szerkezete:

EGYÉNI (Azonosító, Év, Helyszín, Helyezés, Név, Ország)

Azonosító        A versenyző azonosítója (számláló), ez a kulcs

Év        Az olimpia időpontja (szám)

Helyszín        Az olimpiát rendező város (szöveg)

Helyezés        A versenyző helyezése a versenyszámban (szám)

Név        A versenyző neve (szöveg)

Ország        A versenyző országának rövidítése (szöveg)

Feladatok

Készítse el a következő feladatok megoldását!

1.        Listázza ki az aranyérmes versenyzők nevét, az olimpia idejét és helyét az időpont alapján növekvően! (A)

2.        Kulcsár Győző melyik olimpián és milyen helyezést ért el? (B)

3.        Írassa ki az atlantai olimpia érmeseinek nevét és helyezését! (C)

4.        A magyarok hány dobogós helyezést értek el? (D)

5.        Kik voltak a magyar dobogósok? Mindenki csak egyszer szerepeljen a helyezések számával! (E)

6.        Ki nyert többször aranyérmet? (F)

7.        Összesítse, hogy melyik hogy melyik ország hány aranyérmet szerzett! (G)

8.        Melyik városban rendeztek többször olimpiát? (H)

9. feladat

Tanfolyam

Adattábla neve: DOLGOZÓ



Adattábla neve: IRÁNYÍTÓSZÁM



Adattábla neve: TANFOLYAM



Adattábla neve: LÁTOGATÁS



Egy vállalattól a dolgozók különféle tanfolyamra járnak. Egy dolgozó több tanfolyamra is beiratkozhat.

1.        Készítse el a fenti adattáblákat, határozza meg a mezőtípusokat!

2.        Hozza létre értelemszerűen az elsődleges kulcsokat és hozza létre a táblák közötti megfelelő kapcsolatokat!

3.        Készítsen lekérdezést azon dolgozókról, akik informatika tanfolyamra járnak!

4.        Hányan járnak informatika tanfolyamra?

5.        Készítsen lekérdezést azon tanfolyamokról, amelyek 4500 Ft és 6500 Ft közötti összegbe kerülnek!

6.        Csökkentse 50%-al az összes díjat!

7.        Készítsen lekérdezést a tanfolyami hallgatók összesen befizetett díjáról!

10. feladat

Statisztika

Egy Budapest VII. kerületi iskola statisztikai kimutatásaihoz készít adatbázist.

1.        Tervezzen adatbázist statisztika néven!

2.        A diakok nevű adattáblát úgy tervezze meg, és hozza létre, hogy feltöltése után válaszolni tudjunk az alábbi kérdésekre.

Mennyi az osztályban a fiúk száma, lányok száma, magántanulók száma?

Mennyi a 14, 15, 16, 17, 18 évesek száma?

Kinek van 2, vagy annál több testvére?

Hányan laknak az iskola székhelyétől eltérő városban, kerületben, illetve hány „kerületi” diák ját az osztályba?

Hány kollégista van?

Kik veszik igénybe az iskolában a menzát, tanulószobát?

Mennyi azon családok száma, ahol a becsült, egy főre jutó jövedelem kisebb, mint 30 000 Ft?

3.        Az adattáblában adjon meg kulcsmezőt!

4.        Töltse fel az adattáblát 5 rekorddal, kitalált személyek adataival!

Az adatok alapján válaszoljon a következő kérdésekre, válaszait a kérdés után zárójelben található néven mentse:

5.        Kinek van 2, vagy annál több testvére? A diákok nevét, testvéreinek számát és egy főre jutó becsült jövedelmét jelenítse meg a jövedelem szerint növekvő rendben! (testver)

6.        Hányan laknak az iskola székhelyétől eltérő városban, kerületben? (mashely)

11. feladat

Nobel-díj

Az adatbázis-kezelő segítségével dolgozza fel a Nobel díjasok adatait!

Készítsen új adatbázist nobel néven!

A tábla a következő szerkezetű legyen:

évszám        a díjazás éve        (szám)

név                a díjazott neve         (szöveg)

ország                a díjazott országa        (szöveg)

díj                a díj fajtája        (szöveg)

1.        Az adatbázisból hiányzik Kertész Imre 2002-es irodalmi Nobel-díja. Egészítse ki az adatbázist!

2.        Készítse el a magyar Nobel-díjasok listáját! (magyar)

3.        Milyen díjat kapott, mikor és milyen ország színeiben Bárány Róbert? (Bárány)

4.        Milyen díjat kapott, mikor és milyen ország színeiben Wigner Jenő? (Wigner)

5.        Kik voltak a holland (ország jelölésük: NL) Nobel-díjasok? (NL)

6.        Melyik díjban részesültek a legtöbben, és hányan? (dijak)

7.        Melyik évben milyen díjat nem adtak ki? (hiany)

8.        Mely országok képviselői kaptak csak egyszer Nobel-díjat? (egyszer)

9.        Listázza ki, hogy melyik ország képviselői hány díjat kaptak! A listában csak tényleges országok szerepeljenek! A lista az országokat rangsorban mutassa! (rangsor)

10.        Listázza ki 1901 és 1939 között évenként és díjanként a díjazottakat és országukat! (1939)

12. feladat

A Stupido-Gigantic GmbH Ltd Kft S.A. főleg kereskedelemmel foglalkozik. Ennek megfelelően rendelések futnak be hozzá vásárlóktól, amelyeket a szállítók révén teljesít. Egy terméket csak egy szállító szállít, és egy szállító csak egy terméket szállít. Nyilvántartjuk a vásárlók pillanatnyi egyenlegét.  Egy rendelésen több tétel is szerepelhet, a tétel tartalma a termék megnevezése és a mennyiség. A szállítók, termékek és városok neve egyedi, de a vásárlók neve csak egy városon belül egyedi. Feltehetjük, hogy minden városnak csak egy irányítószáma van.

A tárolt adatok ömlesztve:

SzállítóIrányítóSzám, VásárlóIrányítóSzám, SzállítóNév, RendelésSzáma, Mennyiség, RendelésDátuma, VásárlóVárosnév, Egyenleg, Egységár, Terméknév, SzállítóVárosnév, Vásárlónév, Terméknév.

Feladatok:

1. Készítsük el a Stupido-Gigantic GmbH Ltd Kft. S.A. kereskedelmének logikai adatbázis-modelljét (a 3NF alakig)!

2. Valósítsuk meg a logikai adatmodellt!

3. Valósítsuk meg a következő lekérdezéseket:

a "gumimaci" nevű terméket szállító szállító városának az irányítószáma

1998 évi vásárlóink neve

ajkai vásárlóink egyenlege

a legnagyobb egyenlegű vásárló neve és városa

a leghűségésebb vásárlónk melyik szállítótól vásárolt a legtöbbször?

13. feladat

Alaptáblák

ember [ id integer primary key, nev varchar(40) not null, varos varchar(40) ]

auto [ rsz char(7) primary key, tulaj integer, tipus varchar(20), szin varchar(20), ar numeric(7,0) ]

Hozzul létre a két adattáblát, és töltsük fel adatokkal. Mindkét táblában legyen min 10-10 rekord.

Feladatok:

1.        Kérdezzük le a piros színű autók árait.

2.        Az 500000 és 1000000 Ft közötti értékű autók árát növeljük 20%-al.

3.        Kérdezzük le a ’K’ betűvel kezdődő tulajdonosokat és autóik típusát.

4.        Kérdezzük le a miskolci és egri autótulajdonosok nevét és autóik árát, a tulajdonosok szerint névsorrendbe rendezve.

5.        Kérdezzük le azoknak az autótulajdonosoknak a nevét, akiknek 1 millió Ft-nál olcsóbb autójuk van.

6.        Kérdezzük le azon autótulajdonosoknak a nevét és címét, akiknek van autójuk.

7.        Azoknak az autóknak a rendszámát kérdezzük le, amelyeknek miskolci a tulajdonosuk.

8.        Kérdezzük le azokat az autókat, amelyeknek az ára nagyobb minden piros autóénál.

9.        Kérdezzük le, hogy milyen típusú autó fordulnak elő az autó táblában (ismétlődés nélkül).

10.        Kérdezzük le a miskolci autók átlagárát.

11.        Kérdezzük le, hogy az egyes városokban hány autó van.

12.        Kérdezzük le az átlagárnál drágább autók rendszámát és tulajdonosaik nevét.

13.        Kérdezzük le a legolcsóbb miskolci autónál drágább autók rendszámát.

Nézetek és indexek
Nézet

A felhasználók egyes csoportjai nem látják a teljes adatbázist, esetleg annak részeit is másként látják mint ahogy azok a koncepciós modell szerint felépülnek.

Ilyen eset amikor egy adott csoport a személyi adatokból nem látja a személy rekord Fizetés mezőjét, egy másik csoport pedig a Születési_időt. A nézetek kialakítása is adatbázis-tervezési feladat. Az adatmodell három szintre bontható:

Külső szintek: Azt írják le, hogyan látják az egyes felhasználók az adatbázist.

A középső vagy koncepcionális szint: A teljes adatbázis koncepcionális szerkezetét ábrázolja.

A belső vagy fizikai szint: Az adatok fizikai elhelyezését és elérési módját írja le.

Az adatbázis függetlenség mint az adatbázis-kezelés egyik legfontosabb követelménye, a koncepcionális és a fizikai szint éles különválasztásának köszönhető. Megkülönböztetünk

logikai és fizikai adatfüggetlenséget.

A logikai adatfüggetlenséget a metaadatok biztosítják számunkra, vagyis nemcsak az adatokat,hanem az adatok jellemzőit és az adatcsoportok közötti kapcsolatokat leíró adatokat is tároljuk.

Az adatbázis-kezelő rendszerek (DBMS) a logikai adatfüggetlenséget alaptulajdonságuknál fogva biztosítják. Az adatszerkezet megváltoztatása csak a metaadatokban jelent változást, nem kell a felhasználói programot átírni. Lehetséges koncepcionális szinten változtatásokat végezni anélkül, hogy az adatmodell külső szintjében (nézet/view) változás történne.

A DBMS ugyanakkor biztosítja a fizikai adatfüggetlenséget is. Az adattárolási szerkezetek és

a hozzáférési módok - vagyis a fizikai adatbázis - változtatása nem vonja maga után a koncepcionális séma és az alkalmazói programok megváltozását. Például egy indexelést a felhasználó legfeljebb oly módon észlel, hogy bizonyos adataihoz gyorsabban hozzáfér, mint korában.

Minden jól megtervezett, a rétegezési koncepció alapján felépített rendszerben cél az, hogy a rétegek egymástól függetlenül megváltoztathatók, kicserélhetők legyenek, amennyiben a rétegek közötti interfészek közben változatlanok maradnak. Az adatbázis kezelés világában ezt az elvet az adatfüggetlenség elvének nevezzük.

Kétféle adatfüggetlenségről szokás beszélni: a fizikai és a fogalmi adatbázis között értelmezhető fizikai adatfüggetlenségről, illetve a fogalmi adatbázis és a nézetek között értelmezhető logikai adatfüggetlenségről.

A fizikai adatfüggetlenségen azt az elvárást értjük, hogy a fizikai szinten, a fizikai működés sémáiban véghezvitt változások ne érintsék a fogalmi (logikai) adatbázist. Ha ez teljesül (gyakorlatilag mindig), akkor a fizikai adathordozó egy teljesen más paraméterekkel rendelkezőre kicserélhető (pl. meghibásodás, technikai fejlődés, stb. miatt), vagy az állományszervezés módja megváltoztatható anélkül, hogy az adatbázisban bármilyen logikai változás lenne érzékelhető.

Logikai adatfüggetlenségről akkor beszélünk, ha a logikai adatbázis megváltozása nem jár az egyes felhasználásokhoz-felhasználókhoz tartozó nézetek megváltozásával. Ez az elvárás már nem teljesül minden esetben.

Nézetet a következő paranccsal hozunk létre.

CREATE VIEW <nézetnév>   [(<oszlopnév1>, …)]  AS <lekérdezés>;

A lekérdezésre egyetlen egy megkötés van, mégpedig hogy rendezést nem tartalmazhat.

Ha nem adunk meg oszopneveket, a nézet oszlopai a SELECT után felsorolt oszlopok neveivel azonosak.

Viszont ha a SELECT számított értéket is előállít, akkor meg kell adni az oszlopneveket.

Például:

CREATE VIEW dept_sal  (deptno, avg salary) AS SELECT deptno, AVG (sal)  FROM emp

GPOUP BY deptno;

A nézetek a lekérdezésekben a táblákkal megegyező módon használhatók.

Módosítható nézettáblák

A nézettáblák módosításával a mögöttük lévő adattáblák tartalma módosul

Alaphelyzetben nem minden nézettábla módosítható

A módosíthatóság kétféleképpen érhető el:

A módosíthatóság  szerkezeti feltételei

A nézettábla definíciója nem tartalmazhat:

Nem módosíthatók azok az oszlopok, amiknek értéke egy kifejezésből származik

Több táblára hivatkozó nézetnél ezen kívül

A nézettábla szerkezetének megváltoztatására a CREATE OR REPLACE VIEW parancsot használjuk.

Nézettábla törlése

Alakja:DROP VIEW nézet-név;

Példa:DROP VIEW budapesti_vevok_vw;

Az adatok nem törlődnek! Azok az adattáblákban vannak!

Foglaljuk össze hogy miért jók nekünk a nézettáblák:

Először is Biztonság: csak kiválasztott adatokhoz engednek hozzáférést

Egyszerűsítik a felhasználók által kiadandó utasításokat

Függetlenítik az alkalmazásokat a táblák szerkezetétől

Növelik a biztonságot

Az adatok különböző felhasználói igények szerint „tálalhatók”

Az indexek

Az indexrekordokban található mutatók két féle képen azonosítják a keresett rekordot:

Mutatót állíthatunk minden egyes adatrekordra, ez a sűrű indexelés, vagy

Mutatót állíthatunk adatrekordok egy csoportjára, tipikusan az egy blokkban lévőkre. Ez a ritka indexelés.

Ritka indexek:

Az indexrekordok azt határozzák meg, hogy az adatállomány rekordjai melyik blokkban találhatóak. –> egy blokkon belül az adatrekordok szabad rekordoknak tekinthetők.

Ritka indexek estén az adatállományt is rendezetten kell tárolni, azaz egy blokkban kell lennie

Az összes olyan adatrekordnak, melynek kulcsa egy intervallumba esik.

Keresés:

Tegyük fel hogy k1 kulcsú rekordot keressük. Az indexállományban megkeressük azt a rekordot, amelyiknek k2 kulcsa a legnagyobb azok közül, amelyek még kisebbek k1-nél.

A k2 kulcsú indexrekord mutatója megcímzi azt a blokkot, amelyet végig kell keresni a k1 kulcsú adatrekord után.

Beszúrás:

Tegyük fel hogy a k1 kulcsú rekordot tárolni akarjuk. Ehhez meg kell keresnünk az a blokkot, melyben a rekordnak lennie kellene, ha az adatállományban lenne, legyen ez a Bi blokk. Ezután két eset áll fenn:

Vagy van elegendő hely a Bi blokkban a k1 kulcsú rekord számára vagy nincs.

Ha van akkor nincs más dolgunk mint beírjuk a rekordot a Bi blokkba.

Ha nincs akkor helyet kell neki csinálni  kérünk egy új üres blokkot.



Törlés:

Tegyük fel hogy k1 kulcsú rekordot kívánjuk törölni.

Ehhez először keressük meg az a blokkot mely a rekordot tartalmazza, legyen ez Bi. Ha a k1 kulcs a blokkban nem a legkisebb, akkor a rekordot egyszerűen töröljük, a helyén keletkező űrt a rekordok blokkon belüli mozgatásával orvosolhatjuk.



Módosítás:

Módosítás során két lehetőség áll fenn. Vagy egyszerű, ha a módosítás nem érinti a rekord kulcsát, ekkor csak megkeressük a módosítandó rekordot, elvégezzük a módosítást, majd visszaírjuk a háttértárba.

A bonyolultabb eset ha kulcsmezőt érint. Ekkor egy törlést követő beszúrással valósíthatja meg egy rekord módosítását.

B*- fák, mint többszintes ritka indexek:

Az indexelt szervezésnél log2N-nel arányos átlagos keresési idő érhető el, ami kisebb mint a heap szervezésé, de nagyobb mint a hash szervezésé, cserébe viszont a háttértár kihasználtsága változó méretű adatbázis esetén is kézben tartható.

Egy csomópontban ábrázolt k - mutatóhoz csak k-1 kulcsot tárolnak, mert a kulcs jelentése a kijelölt részfában tárolt legkisebb kulcsérték. Így az indexblokkok első kulcsérték bejegyzése nem hordozna információt. Az ilyen indexelést nevezik B*- fa indexnek.

Keresés:

Az eljárás hasonló a ritka indexeknél tanultakhoz, csupán az indexállományban keresést végezzük több lépésben.

Tételezzük fel, hogy a v1 kulcsú rekordra van szükségünk. Az indexállomány csúcsán álló blokkban megkeressük azt a rekordot, amelyiknek v2 kulcsa a legnagyobb azok közül, amelyek még kisebbek v1-nél. Ennek a rekordnak a mutatója az eggyel alacsonyabb szintű indexben rámutat arra a blokkra, amelyben a keresést tovább kell folytatni egy olyan indexrekord után, amelyiknek V3 kulcsa a legnagyobb azok közül, amelyek még kisebbek v1-nél. Az eljárás mindaddig folytatandó, ameddig az utolsó mutató már az adatállomány egy blokkját azonosítja, amelyben a kulcsú rekordnak lennie kell.

Beszúrás:

Annyiban tér el a ritka indexben  összefoglaltaktól, hogy az indexállomány karbantartásánál gondosan ügyelni kell arra, hogy az eredeti fastruktúrát, annak kiegyenlítettségét fenntartsuk.

Törlés:

Megkeressük a keresett adatot és töröljük. Az adatblokkokat ha csak lehet összevonjuk. Összevonáskor, vagy ha egy adatblokk utolsó rekordját is töröljük, a megszűnt blokkhoz tartozó kulcsot is ki kell venni az index állomány érintett részfájából.

Módosítás:

Azonos a ritka indexnél tárgyaltakkal.

Sűrű indexek:

A ritka indexelés hátránya hogy az adatállományt rendezetten kell tárolni. Emiatt nincs mód arra, hogy egy-egy új rekordot tetszőleges szabad helyre szúrjunk be  csökken a háttértár kihasználtsága.

Megoldás lehet ha minden adatrekordhoz tartozik indexrekord. Az indexrekord továbbra is csak a rekordot tartalmazó blokkot azonosítja. Ezzel a megoldással a blokkon belüli keresési idő csökkenthető.

 A sűrű indexek elsősorban a fő állomány kezelését könnyítik meg, illetve a több kulcs szerinti keresést teszik lehetővé.

Hátrányai:

Plusz helyigény

Eggyel több lapelérés kell egy rekord kiolvasásához

Plusz adminisztrációval jár a karbantartása

Előnyei:

Nincs szükség rendezett tárolásra  helymegtakarítás

Felgyorsítja a rekordelérést

Több kulcs szerinti keresés

Az adatállomány rekordjai szabaddá tehetők, ha minden további rekordhívás a sűrű indexen keresztül történik.

Keresés:

Az indexelt állományban megkeressük a kulcsot, a hozzá tartozó mutatóval elérhetjük a tárolt rekordot.

Törlés:

Megkeressük a rekordot, foglaltsági jelzését szabadra állítjuk, a kulcsot kivesszük az index állományból, és az indexállományt időnként tömörítjük.



Beszúrás:

Keresünk egy üres helyet a rekordnak, ha nem találunk, akkor az állomány végére vesszük fel.

Beállítjuk a foglaltsági jelzést, és beírjuk az adatokat.

Módosítás:

Megkeressük a rekordot tartalmazó adatblokkot, majd módosítást követően visszaírjuk a háttértárra. Ha kulcsmezőt érintünk akkor az indexállományt újra rendezzük.

Megszorítások, integritási feltételek, triggerek
A megszorítások olyan előírások, korlátozások, amelyekkel megadhatjuk az adatbázis tartalmára vonatkozó kívánságainkat. Ha ezeket az adatbázisrendszerrel egyszer, egy helyen közöljük, az mindig gondoskodni fog betartásukról, ha a felhasználói rendszerbe építenénk be, akkor sok helyen kellene megadni, módosítani.

A megszorításokat az adatbázisrendszer minden olyan akció során ellenőrzi, amely eredményeként az adatbázis tartalma úgy változhat, hogy az a megszorítást már nem elégítené ki. A megszorítások megadásuktól kezdve érvényesülnek (nincs visszamenőleges hatásuk). Késleltetett ellenőrzés végrehajtás a DEFFERRED kulcsszóval.

Kulcsok
Az egyedek egyértelmű azonosítására alkalmas. Megszorításként azt jelenti, hogy ellenőrizze azt, hogy a relációban nem fordulhat elő két olyan sor, amelyben a kulcsattribútumok értéke páronként azonos lenne. Egy relációban több kulcs is lehetséges. Ezek közül szokás egy elsődleges kulcsot megadni. CREATE TABLE utasításnál megadható. Ha a kulcs egy attribútumos, akkor PRIMARY KEY vagy UNIQUE kulcsszavakkal adhatjuk meg, vagy az utasítás végén, ugyanezen kulcsszavak után zárójelben megadva felsorolásként. Ha nem egy attribútumos, akkor csak tábla végén.

A következő példában létrehozunk ugyanolyan megszorításokkal rendelkező táblákat hozunk létre,

a megszorítások két lehetséges megadási módját szemléltetve:

CREATE TABLE hallgatok(

neptun_kod CHAR(6) PRIMARY KEY,

nev VARCHAR2(30) NOT NULL,

szul_ev NUMBER(4) CHECK (szul_ev > 1900),

telefonszam NUMBER(10) UNIQUE);

CREATE TABLE atlagok(

neptun_kod CHAR(6) REFERENCES hallgatok(neptun_kod),

atlag NUMBER(3,2));

CREATE TABLE hallgatok2(

neptun_kod CHAR(6),

nev VARCHAR2(30) NOT NULL,

szul_ev NUMBER(4),

telefonszam NUMBER(10),

PRIMARY KEY (neptun_kod),

CHECK (szul_ev > 1900),

UNIQUE (telefonszam)

);

CREATE TABLE atlagok2(

neptun_kod CHAR(6),

atlag NUMBER(3,2),

FOREIGN KEY (neptun_kod) REFERENCES hallgatok2(neptun_kod)

)

A fenti attribútumok tehát a következő megszorításokkal rendelkeznek:

• a hallgatok és hallgatok2 táblában a neptun_kod elsődleges kulcs, tehát minden sorban különböző értéknek kell szerepelnie és nem lehet köztük null;

• a név mező nem tartalmazhat null értéket;

• a születési évnek nagyobbnak kell lennie, mint 1900;

• nem szerepelhet két azonos telefonszám, de előfordulhat, hogy valakinek nem ismerjük a telefonszámát;

• az átlagok és átlagok2 táblákba csak olyan rekord kerülhet, melyben a neptun_kod mező értéke

szerepel már a hallgatok tábla neptun_kod attribútumának értékei között.

Lássunk egy példát arra, hogyan adhatunk még attribútum halmazt elsődleges kulcsként (UNIQUE és

FOREIGN KEY hasonlóan adható meg attribútum halmazra):

CREATE TABLE hallgatok(

nev VARCHAR2(30),

szul_datum DATE,

anyja_neve VARCHAR2(30),

lakcim VARCHAR2(100),

PRIMARY KEY(nev, szul_datum, anyja_neve));

Ebben a táblában a nev, szul_datum és anyja_neve attribútumok vehetnek fel azonos értékeket két

rekordban külön-külön, de hogy mindhárom attribútum megegyezzen, arra minimális az esély, ezért

használják a gyakorlatban gyakran elsődleges kulcsként.

Hivatkozási integritás megszorítás
Hivatkozási integritás megszorítás azt jelenti, hogy egy reláció bizonyos attribútumaiban szereplő értékek csak olyan értékek lehetnek, amelyek egy adott másik táblában előforduló elsődleges kulcsértékek (idegen kulcsok). A hivatkozási épség előírás megadására két lehetőség van. Ha az idegen kulcs egy attribútumos, akkor az attribútum definiálásakor: REFERENCES tábla (attribútum) megadásával vagy a CREATE TABLE utasítás végén: FOREIGN KEY attribútumok REFERENCES tábla (attribútumok), módon. Ha az idegen kulcs nem egy attribútumos, akkor csak az utóbb megadási lehetőség van.

A hivatkozási épség sérülhet oly módon, hogy a hivatkozó táblába módosítás vagy beszúrás során olyan érték kerülne, amely a hivatkozott táblában nem fordul elő a megnevezett attribútumokon, és sérülhet úgy is, hogy a hivatkozott táblában módosítunk, vagy törlünk olyan sorokat, amelyek korábban helyes hivatkozások ottani párjai voltak. Az adatbázisrendszerek a hivatkozási épség megsértésekor nemcsak az ezt sértő beavatkozás tiltásával élhetnek, hanem kétféle reagálás lehetőségét kínálják fel:

A módosítások visszautasítása

Tovagyűrűző eljárás (CASCADE). Ha a hivatkozott helyen módosítjuk a hivatkozásban résztvevő attribútum értékeket, akkor a hivatkozó helyeken is ennek megfelelően automatikusan módosítja az adatbázisrendszer a hivatkozó értékeket, ezzel helyreállítja a hivatkozást.

NULL értékre állítás módszere, (SET NULL). Ha a hivatkozott helyen történő változás miatt a hivatkozás sérülne, akkor a hivatkozó helyen a hivatkozó értéket NULL-ra állítja.

Azt, hogy sérülés esetén ezek közül mi legyen az adatbázis reakciója azt a hivatkozó táblát létrehozó CREATE TABLE utasításban, a hivatkozás megadásával együtt adhatjuk meg. ON DELETE SET NULL: törlés miatti sérülés esetén a hivatkozó érték NULL-ra állítódik.

ON UPDATE CASCADE módosítás esetén, a hivatkozás helyén is történjen meg az attribútum értékek módosítása, hogy a hivatkozás ismét érvényes legyen.

Hivatkozási épség megszorítások: Előírhatjuk, hogy 1 attribútum v. attribútum-halmaz értékeinek elő kell fordulnia 1 másik reláció valamelyik sorának elsődleges kulcs attribú­tuma(i)ban. Ezt a relációséma megadásakor a REFERENCES v. a FOREIGN KEY kulcsszóval adhatjuk meg:

Ha az idegen kulcs egyetlen attribútum:

REFERENCES <tábla> (<attribútum>)

Ha az idegen kulcs több attribútumból áll:

FOREIGN KEY <attribútumok> REFERENCES <tábla>  (<attribútumok>)

Az idegen kulcsot kétféleképpen deklarálhatjuk, ugyanúgy ahogyan azt az elsődleges kulcsok esetében tettük.

Attribútum értékekre vonatkozó megszorítások

Az attribútumok lehetséges értékeik a típusuk megadása még alig korlátozza. Hibás adat bevitelének lehetőségek, hibásra módosítás lehetőségét csökkentik.

NOT NULL feltétel. Megadásával előírhatjuk, hogy az adott attribútumnak mindig érvényes értékkel kell rendelkeznie, értéke sosem lehet NULL. Megadása a relációt definiáló CREATE TABLE utasításban az érintett attribútum definiálásakor.

CHECK feltétel. Megadásával olyan korlátozásokat előírhatunk, mint a WHERE után. Megengedett értékek, aritmetikai kifejezések. Megadása a relációt definiáló CREATE TABLE utasításban az érintett attribútum definiálásakor.

A CHECK feltétellel nemcsak attribútum értékekre vonatkozó kikötéseket adhatunk, hanem sorokra vonatkozó megszorítást is. Ilyenkor a CHECK feltételt nem egy attribútum definiálásakor fűzzük, hanem a táblát definiáló utasítás végén adjuk meg.

Egy tábla definiálásakor a tábla nevén és az attribútumokon kívül egyéb információt is lehet megadni. Ilyenek a

kulcsok

az attribútum értékekre vonatkozó megszorítások.

Először a kulcsok, és az egyedi értékekkel bíró attribútumok megadásának módját ismertetjük. Az SQL-ben alapvetően az elsődleges kulcs megadására van lehetőségünk, ahogy azt a legtöbb ABKR megköveteli. Ha az elsődleges kulcsot szeretnénk definiálni, akkor a tábla létrehozását kibővíthetjük megfelelő opciókkal (záradékokkal). Ez a következőképpen néz ki:

CREATE TABLE <táblanév> { <attribútumdefiníció> [UNIQUE]  [,<attribútumdefiníció> [UNIQUE]]… [,PRIMARY KEY (<kulcsattribútum> [,<kulcsattribútum>]…)|UNIQUE(<kulcsattribútum>) ]}

A UNIQUE kulcsszó segítségével minden egyes attribútumnál megadhatjuk, hogy az adott attribútum csak egyedi értékeket vehet fel. A <kulcsattribútum> paraméterben kell megadni annak az attribútumnak a nevét, amely a kulcsot alkotja, vagy annak egy részét képezi. Amennyiben csak egy attribútum tartozik a kulcshoz, akkor használhatjuk mind a PRIMARY KEY, mind a UNIQUEparancsokat. Több attribútumból álló kulcsot csak a PRIMARY KEY kulcsszóval definiálhatunk.

Önálló megszorítások

Az SQL2 lehetőséget ad olyan megszorítások megadására, amely bármilyen feltétel ellenőrzését lehetővé teszi.

Általános alakja: CREATE ASSERTION megszorításnév CHECK feltétel;

Az önálló megszorítás lehetőségeit az SQL3-ban bővítették. Az ellenőrzést a programozó által megadott események váltják ki, valamint a megszorítás a tábla/táblák egyes soraira is vonatkozhat, nemcsak a teljes táblákra.

Megszorítások módosítása

Azért, hogy a megszorítást módosítani, törölni tudjuk, a létrehozásakor nevet kell neki adni. A névadást definiáláskor lehet megtenni. A megszorítás nevét a CONSTRAINT kulcsszó után adhatjuk meg. Például: CONSTRAINT titulus CHECK (paraméterek).

Ha a névvel megnevezett megszorítást törölni kívánjuk, akkor azt az ALTER TABLE táblanév DROP CONSTRAINT megszorításnév;

utasítással tehetjük meg.

Új megszorítás: ALTER TABLE táblanév ADD CONSTRAINT megszorításnév megszorítás definiálása;

Tábla szintű megszorítás:

ALTER TABLE <tábla név> ADD CONSTRAINT <név> <típus> <oszlop>;

Példafeladat:

Adjunk egy olyan megszorítást a tTanar táblához, aminek következtében nem tárolhatunk két azonos nevű tanárt.

ALTER TABLE tTanar ADD CONSTRAINT uq_tTanar UNIQUE  (Nev);

Ellenörzés:

INSERT INTO tTanar  VALUES (1, ’ Példa Béla’);

 Megszorítás Törlése:

ALTER TABLE <tábla név> DROP CONSTRAINT <megszorítás név>;

Példa:

Dobjuk el az előbbi megszorítást:

ALTER TABLE tTanar DROP CONSTRAINT  uq_tTanar;

Ellenőrzés:

INSERT INTO tTanar VALUES  (1, ’ Példa Béla’);

Az adatbázis konzisztencia megőrzése.

Konzisztencia sorozatok

Triggerek: (Oracle 10g)

A trigger olyan tevékenységet definiál mely automatikusan végbemegy, ha egy tábla vagy nézet módosul vagy ha egyéb felhasználói vagy rendszeresemények következnek be. Azaz bármilyen változás az adatbázisban egy triggert indít el. A trigger egy adatbázis- objektum.

A triggerek működése felhasználói szemszögből átlátszóan működik.

Működését kiválthatja:

Egy táblán vagy nézeten végrehajtott INSERT, DELETE vagy UPDATE utasítás

Egyes DDL utasítások

Szerverhibák

Felhasználói ki és bejelentkezés

Adatbázis elindítása leállítása

Az alábbi esetekben használjuk őket:

származtatott oszlopérték generálása

érvénytelen tranzakció megelőzése

védelem

hivatkozási integritási megszorítások definiálása

komplex üzleti szabályok kezelése

eseménynaplózás

követés

táblastatisztikák gyűjtése

adattöbbszörözés

A tiggereket többféle szempont alapján osztályozhatjuk. Egy triggernél meg kell határozni, hogy az eseményhez viszonyítva mikor és hányszor fusson le. Ez alapján a következő triggerekről beszélhetünk:

- sor és utasításszintű trigger

- Before és After trigger

- Instead Of trigger

- Rendszer triggerek

Sor szintű trigger:

Röviden összefoglalva annyiszor fut le ahányszor a tábla adatai módosulnak. PL:  Egy Delete utasításnál minden törölt sor aktiválja a triggert. Azonban ha nem módosul egyetlen sor sem akkor nem fut le egyszer sem a trigger.

A trigger megszámolja a 100 000-nél kisebb fizetésű új dolgozókat:

CREATE TRIGGER dolg_szamlal

AFTER INSERT ON dolgozo

FOR EACH ROW

WHEN (NEW.fizetes < 100000)

BEGIN

UPDATE szamlalo SET ertek=ertek+1;

END;

Utasítás szintű trigger:

Ez a trigger ellentétben a sor szintű társával csak egyetlen egyszer fut le, és akkor is lefut ha nem történt változás az adatbázisban.

Egyelemű segédtábla:

CREATE TABLE szamlalo (ertek NUMBER);

INSERT INTO szamlalo VALUES (-1);

Több új dolgozó felvételének esetére két triggert definiálunk. Az első nullázza a számlálót:

CREATE TRIGGER dolg_kezdo

BEFORE INSERT ON dolgozo

BEGIN

UPDATE szamlalo SET ertek=0;

END;

Before és After triggerek:

Egyaránt lehetnek sor és utasítás szintűek.  Csak táblához kapcsolhatók nézethez nem, ám egy alaptáblához kapcsolt trigger lefut a nézeten végrehajtott DML utasítás esetén is.

A Before trigger a hozzákapcsolt utasítás előtt fut le, míg az After trigger nevéből adódóan az utasítás után fut le.

Before:

CREATE OR REPLACE TRIGGER emp_alert_trig

BEFORE INSERT ON emp

BEGIN

DBMS_OUTPUT.PUT_LINE('New employees are about to be

added');

END;

Szúrjunk be egy sort!

INSERT INTO emp(empno, ename, deptno) VALUES(8000, ’valaki’,

40);

After:

Hozzuk létre az új táblát, ahol a módosításokat fogjuk letárolni!

CREATE TABLE empauditlog (

audit_date DATE,

audit_user VARCHAR2(20),

audit_desc VARCHAR2(20)

);

Hozzuk létre a triggert!

CREATE OR REPLACE TRIGGER emp_audit_trig

AFTER INSERT OR UPDATE OR DELETE ON emp

DECLARE

v_action VARCHAR2(20);

BEGIN

IF INSERTING THEN

v_action := 'Added employee(s)';

ELSIF UPDATING THEN

v_action := 'Updated employee(s)';

ELSIF DELETING THEN

v_action := 'Deleted employee(s)';

END IF;

INSERT INTO empauditlog VALUES (SYSDATE, USER, v_action);

END;

Instead of trigger:

Ez a trigger a hozzákapcsolt utasítás helyett fut le. Csak sor szintű lehet és csak nézeteken definiálható. Ha egy nézetet módosítani akarunk, de az nem tehetjük közvetlenül a DML utasítások segítségével, akkor használjuk az Instead Of triggert.

Rendszer triggerek:

Célja az előfizetők tájékoztatása az adatbázis eseményekről.

Már tudjuk hogy mik a triggerek, mikor lépnek működésbe, mikor használjuk őket, és milyen fajtái vannak. Azonban még azt nem tudjuk, hogy hogyan hozhatóak létre. A következő részben ezt fogjuk megvizsgálni.

Triggerek létrehozása:

Saját sémában   -----   CREATE TRIGGER

Másik felhasználó sémájában   -----   CREATE ANY TRIGGER

Az adatbázisban létrehozandóhoz pedig   -----   ADMINISTER DATABASE TRIGGER

Jogosultság szükséges.

Létrehozó utasítás:

CREATE [OR REPLACE] TRIGGER [séma. ] triggernév

{ BEFORE | AFTER | INSTEAD OF }

{dml_trigger | { ddl_esemény  [OR ddl_esemény] …|

Ab_esemény [OR  ab_esemény]…}

ON {DATABASE | [séma. ] SCHEMA}

[WHEN (feltétel) ]   {plsql_blokk | eljáráshívás}

Ahol

Dml_trigger::=

{INSERT | DELETE | UPDATE  [OF oszlop [ , oszlop]…}

[OR {INSERT | DELETE | UPDATE  [OF oszlop [ , oszlop]…} ]….

ON { [ séma. ] tábla |

[ NESTED TABLE  bát_oszlop OF ]  [séma. ] nézet}

[REFERENCING {OLD  [AS] régi | NEW [AS] új | PARENT [AS] szülő}

[ { OLD [AS]  régi | NEW [AS] új | PARENT [AS] szülő} ]…

[FOR EACH ROW]

Vegyük sorba mit miért írtunk:

Az OR REPLACE a már létező trigger újradefiniálása, annak előzetes megszüntetése nélkül.

A séma triggert tartalmazó séma neve.  Ha hiányzik, akkor a parancsot kiadó felhasználó sémájában jön létre a trigger, egyszóval nem ott ahol azt mi szeretnénk.

A trigger név a létrehozandó trigger neve.

A BEFORE, AFTER, INSTEAD OF a trigger típusát adja meg.

Az INSERT, DELETE, UPDATE  definiálja azt az SQL utasítást, melynek hatására a trigger lefut.

Az ON utasításrész azt az adatbázis-objektumot adja meg, melyen a triggert létrehozzuk.

A REFERENCING utasításrész korrelációs neveket( régi, új, szülő) határoz meg.

A NEW a módosítás utáni neveket adja meg.

A FOR EACH ROW sor szintű triggereket hoz létre.

A ddl_esemény egy olyan DDL utasítást, az ab_esemény egy olyan adatbázis-eseményt határoz meg, amelyek a triggert aktiválják.

Már csak egyetlen kérdés maradt a triggerekkel kapcsolatban:  Hogyan működnek?

A triggerek Működése:

Egy triggernek két állapota lehet: engedélyezett és letiltott. A letiltott trigger nem indul el, ha  a kiváltó esemény bekövetkezik. Az engedélyezett trigger esetén az Oracle automatikusan a következő eseményeket hajtja végre:

lefuttatja a triggert, ha ugyanarra az utasításra több azonos típusú trigger van definiálva, akkor sorrendjük nincs meghatározva.

Ellenőrzi a megszorításokat és felügyeli a triggereket hogy ne sértsék meg azokat.

Olvasási konzisztenciát biztosít a lekérdezésekhez.

Kezeli a trigger és a sémaobjektumok közötti függőségeket.

Osztott adatbázis esetén, ha a trigger távoli táblát módosított, kétfázisú véglegesítést alkalmaz.

A CREATE utasítás automatikusan engedélyezi a triggert.

Triggert letiltani és engedélyezni az ALTER TRIGGER és az ALTER TABLE utasítással lehet.

Azt már tudjuk hogy ha ugyan azon utasításon azonos triggerek vannak definiálva akkor nincs sorrend. De mi a helyzet ha nem azonosak?

Az Oracle egy végrehajtási modellt követ:

Végrehajtja az összes utasításszintű BEFORE triggert.

A DML utasítás által érintett minden sorra ciklikusan:

    a, végrehajtja a sorszintű triggereket

    b, zárolja és megváltoztatja a a sort és ellenörz az integritási megszorításokat.

        a zár csak a tranzakció végeztével oldódik,

    c, végrehajtja a sorszintű AFTER triggereket.

-  Ellenörzi a késleltetett integritási megszirításokat.

- Végrehajtja az utasítás szintű AFTER triggereket.

A végrehajtási modell rekurzív. Egy trigger működése közben újabb triggerek indulhatnak el.

Feladatok
1. feladat

Adott egy adatbázis az alábbi szerkezettel.



1.        Hozza létre az adatbázis táblákat, a saját adatbázisában.

2.        Töltsön a táblákba 5-5 megfelelő rekordot.

3.        Listázza ki a diákok nevét, zsebpénzét név szerint csökkenő sorrendben.

4.        Listázza ki a szakok nevét, az adott szakon tanuló diákok nevével. Rendezzen szak neve szerint és azon belül diák neve szerint.

5.        Számolja össze, hogy megyénként hány diák tanul az iskolában.

6.        Kik azok a diákok, akiknek az átlagosnál kevesebb a zsebpénze? Listázza ki a nevüket, és a zsebpénzüket, zsebpénz szerint csökkenő sorrendben.

7.        Írjon tárolt eljárást, mely meghatározza a legnagyobb különbséget a zsebpénzek között, és ezzel az értékkel csökkenti a legnagyobb zsebpénz összegét.

8.        Írjon tárolt eljárást, mely az Egerben lakó diákok zsebpénzét megnöveli 10%-al ha az átlagos zsebpénznél magasabbak, és 15%-al, ha alacsonyabbak.

2. feladat

Adott egy adatbázis az alábbi szerkezettel.



1.        Írd le a mozik táblát létrehozó utasítást!

2.        Vigyél fel egy új mozit a táblába. Az adatai: neve: Csillag, címe: Eger, Leányka út 4, 3300, és a telefonszámot nem tudjuk. Az elsődleges kulcs identity típusú.

3.        Módosítsd a Penge című film típusát romantikusra és hosszát 135 percre.

4.        Törölje a vasárnapi (április 22) műsor minden filmjét áramszünet miatt.

5.        Készíts műsor-listát az áprilisi filmekről !

6.        Hány darab filmet vetítettek márciusban az Uránia moziban?

7.        Listázd ki azokat a filmeket, melyek hossza rövidebb, mint az átlagos filmhossz.

8.        Melyik moziban vetítik a legtöbb filmet?

9.        Hány darab filmet találunk az adatbázisban nemzetiségenként? Rendezd ezeket csökkenő sorrendben!

10.        Hány moziban vetítik 2012-ben a Harry Potter 7 második részét?

11.         Írj tárolt eljárást, mely az összes 120 percnél hosszabb film percben mért hosszát összeadja.

3. feladat

Adott egy adatbázis az alábbi szerkezettel.



1.        Írd le a Tanulók táblát létrehozó utasítást.

2.        Új tanuló érkezett az osztályba, akinek az adatait be kell vezetni az osztály tanulóinak adatait tartalmazó adatbázisba (osztály.mdb, tanulók tábla). A tanuló adatai a következők: név: Kovács Péter, születési hely: Budapest, születési dátum: 1992. jún. 12., cím: 2120 Dunakeszi, Munkácsy u. 12. (Az azonosító a vezetéknév és a keresztnév kezdőbetűiből és egy számból áll, mely szám azt jelöli, hogy hányadik tanulónak van ilyen monogramja. Itt: KP1)

3.        Vágó László (VL1) születési helyét rosszul írta be az osztályfőnök, Bécs helyett Pécsen született. Javítsd ki a születési helyet az osztály adatbázis tanulók táblájában!

4.        Kovács Péter (KP1) szülei úgy döntöttek, hogy mégis más iskolába viszik a gyereküket, így Kovács Pétert ki kell törölni az osztály tanulóinak névsorából.

5.        Szűrd ki a tanulók táblából azokat, akik B-vel vagy V-vel kezdődő helyen születtek és rendezd őket születési dátum szerint növekvő sorrendbe.

6.        Számold össze, hogy az egyes nyelvekből külön-külön hány tanulónak van nyelvvizsgája.

7.        Készítsd el tanulónként az összes befizetést tartalmazó listát. Rendezd névsorba, azon belül összes befizetés szerint csökkenőleg.

8.        Hány tanuló befizetése van az átlag befizetés felett?

9.        Melyik városból jött az iskolába a legtöbb tanuló?

10.        Mely tanulók nem fizettek még be osztálypénzt?

11.        Írj tárolt eljárást, mely kiválogatja az angol és a német középfokú nyelvvizsgával rendelkező tanulókat, és összeveti az általuk befizetett osztálypénz átlagát.

A PL/SQL alapjai
A PL/SQL alapvető elemei

Karakterkészlet

Egy PL/SQL program forrásszövegének (mint minden forrásszövegnek) a legkisebb alkotóelemei a karakterek. A PL/SQL nyelv karakterkészletének elemei a következők:

• betűk, az angol kis- ás nagybetűk: A—Z és a—z;

számjegyek: 0—9;

• egyébkarakterek:        ()+-/<>=!~^;:.@%,#$&{}?[]

• szóköz, tabulátor, kocsi vissza.

A PL/SQL-ben a kis- és nagybetűk nem különböznek, a sztring literálok belsejét kivéve.

Lexikális egységek

A PL/SQL program szövegében a következő lexikális egységek használhatók:

•        elhatárolók,

•        szimbolikus nevek,

•        megjegyzések,

•        literálok.

Ezeket részletezzük a következőkben.

Elhatárolók



Szimbólikus nevek

Azonosítók: betűvel kezdődik és betűvel, számjeggyel vagy $ _ # karakterekkel folytatódik.

Nagy és kisbetűk nem különböznek!

hallgato, HALGGATO, Hallgato ugyanazt jelenti

További szimbólumok:



Foglalt szavak

(mod, or and, declare, … )

Idézőjeles azonosítók:

„Igen/Nem”

„mod”

„Ez is azonosító”

Az idézőjeles azonosítók karakterhelyesen tárolódnak!

Literálok

5, 33, 6.666, -1.0, 2.0f (BINARY_FLOAT),

2.1d (BINARY_DOUBLE)

’almafa’, ’mondta’

Címke

<< címke >>

Bármely sorban lehet. Azonosító!

Nevesített konstans

név CONSTANT típus [NOT NULL] {:=|DEFAULT} kifejezés;

VÁLTOZÓ

név típus [NOT NULL] {:=|DEFAULT} kifejezés;

Például:

DECLARE

  -- NOT NULL deklarációnál kötelező az értékadás

  v_Szam1 NUMBER NOT NULL := 10;

  -- v_Szam2 kezdőértéke NULL lesz

  v_Szam2 NUMBER;

BEGIN

  v_Szam1 := v_Szam2; -- VALUE_ERROR kivételt eredményez

END;

-- nevesített konstans deklarációja, az értékadó kifejezés függvényhívás

  c_Most       CONSTANT DATE := SYSDATE;

  -- változódeklaráció kezdőértékadás nélkül

  v_Egeszszam  PLS_INTEGER;

  v_Logikai    BOOLEAN;

  -- változódeklaráció kezdőértékadással

  v_Pozitiv    POSITIVEN DEFAULT 1;

  v_Idopecset  TIMESTAMP := CURRENT_TIMESTAMP;

  -- kezdőértékadás rekordtípus mezőjének

  TYPE t_Kiserlet IS RECORD (

     leiras       VARCHAR2(20),

     probalkozas  NUMBER := 0,

     sikeres      NUMBER := 0    );

  -- rekord mezőinek csak a típus deklarációban lehet kezdőértéket adni

  v_Kiserlet   t_Kiserlet;

Egyszerű és összetett típusok
A literálok, nevesített konstansok, változók mindegyike rendelkezik adattípus komponenssel, amely meghatározza az általuk felvehető értékek tartományát, az értékeken végezhető műveleteket és az értékek tárbeli megjelenítési formáját.

Az adattípusok lehetnek rendszerben definiált vagy felhasználói típusok.

Skalártípusok:

SKALÁRTÍPUSOK

 
Numerikus család

Karakteres család

Dátum/intervallum család

BINARY_DOUBLE

CHAR

DATE

BINARY_FLOAT

CHARACTER

INTERVAL DAY TO SECOND

BINARY_INTEGER

LONG

INTERVAL YEAR TO MONTH

DEC

LONG RAW

TIMESTAMP

DECIMAL

NCHAR

TIMESTAMP WITH TIME

DOUBLE PRECISION

NVARCHAR2

ZONE

FLOAT

RAW

TIMESTAMP WITH LOCAL

INT

ROWID

TIME ZONE

INTEGER

STRING



NATURAL

UROWID



NATURALN

VARCHAR



NUMBER

VARCHAR2



NUMERIC





PLS_INTEGER





POSITIVE





POSITIVEN

Logikai család



REAL

BOOLEAN



SIGNTYPE





SMALLINT





ÖSSZETETT TÍPUSOK

LOB TÍPUSOK

REFERENCIATÍPUSOK

RECORD

BFILE REF

CURSOR

TABLE

BLOB

SYS_REFCURSOR

VARRAY

CLOB

REF objektumtípus

NCLOB





NUMBER TÍPUS

Ez a típus egész és valós számokat egyaránt képes kezelni. Megegyezik a NUMBER adatbázistípussal. Tartománya: 1E–130..10E125. Belső ábrázolása fixpontos vagy lebegőpontos decimális. Szintaxisa a következő:

NUMBER [(p[,s])]

A teljes alakban p a pontosság, s a skála megadását szolgálja. Értékük csak egész literál lehet. A pontosság az összes számjegy számát, a skála a tizedes jegyek számát határozza meg.

Típus

Kezelendő érték

Tárolt érték

NUMBER

123.456

123.456

NUMBER(3)

321

321

NUMBER(3)

3210

Túlcsordulás

NUMBER(4,3)

11.2222

Túlcsordulás

NUMBER(4,3)

3.17892

3.1799

NUMBER(3,–3)

1234

1000

NUMBER(3,–1)

1234

1230

A NUMBER típus belső ábrázolása hatékony tárolást tesz lehetővé, de az aritmetikai műveletek közvetlenül nem végezhetők el rajta.

Ha egy egész értéket nem akarunk tárolni, csak műveletet akarunk vele végezni, akkor használjuk a BINARY_INTEGER adattípust. Ez a típus tehát egész értékeket kezel a –2147483647..2147483647 tartományban. Ezeket az értékeket fixpontosan tárolja, így a műveletvégzés gyorsabb.

A BINARY_INTEGER korlátozott altípusai a következők:

NATURAL

0..2147483647

NATURALN

0..2147483647 és NOT NULL

POSITIVE

1..2147483647

POSITIVEN

1..2147483647 és NOT NULL

SIGNTYPE

–1,0,1

Karakteres család

A karakteres típusok tartományának elemei tetszőleges karaktersorozatok. Reprezentációjuk CHAR TÍPUS

Fix hosszúságú karakterláncok kezelésére alkalmas. Szintaxisa:

CHAR [(h [CHAR|BYTE])]

ahol h az 1..32767 intervallumba eső egész literál, alapértelmezése 1. A h értéke bájtokban (BYTE) vagy karakterekben (CHAR – ez az alapértelmezés) értendő és a hosszat adja meg. A karakterláncok számára mindig ennyi bájt foglalódik le, ha a tárolandó karakterlánc ennél rövidebb, akkor jobbról kiegészül szóközökkel.

VARCHAR2 TÍPUS

Változó hosszúságú karakterláncok kezelésére alkalmas. Szintaxisa a következő:

VARCHAR2(h [CHAR|BYTE])

ahol h az 1..32767 intervallumba eső egész literál és a maximális hosszat adja meg. Értéke CHAR megadása esetén karakterekben, BYTE esetén bájtokban, ezek hiánya esetén karakterekben értendő. A maximális hossz legfeljebb 32767 bájt lehet.

A megadott maximális hosszon belül a kezelendő karakterláncok csak a szükséges mennyiségű bájtot foglalják el.

ROWID, UROWID TÍPUSOK

Minden adatbázistábla rendelkezik egy ROWID nevű pszeudooszloppal, amely egy bináris értéket, a sorazonosítót tárolja. Minden sorazonosító a sor tárolási címén alapul. A fizikai sorazonosító egy „normális” tábla sorát azonosítja, a logikai sorazonosító pedig egy asszociatív tömbét. A ROWID adattípus tartományában fizikai sorazonosítók vannak. Az UROWID adattípus viszont fizikai, logikai és idegen (nem Oracle) sorazonosítókat egyaránt tud kezelni.

Dátum/intervallum típusok

Ezen családon belül három alaptípus létezik: DATE, TIMESTAMP és INTERVAL.

DATE TÍPUS

Ez a típus a dátum és idő információinak kezelését teszi lehetővé. Minden értéke 7 bájton tárolódik, amelyek rendre az évszázad, év, hónap, nap, óra, perc, másodperc adatait tartalmazzák.

A típus tartományába az időszámítás előtti 4712. január 1. és időszámítás szerinti 9999. december 31. közötti dátumok tartoznak. A Julianus naptár alkalmazásával az időszámítás előtti 4712. január 1-jétől eltelt napok számát tartalmazza a dátum.

Az aktuális dátum és idő lekérdezhető a SYSDATE függvény visszatérési értékeként.

TIMESTAMP TÍPUS

Ezen típus tartományának értékei az évet, hónapot, napot, órát, percet, másodpercet és a másodperc törtrészét tartalmazzák. Időbélyeg kezelésére alkalmas. Szintaxisa:

TIMESTAMP[(p)] [WITH [LOCAL] TIME ZONE]

ahol p a másodperc törtrészének kifejezésére használt számjegyek száma. Alapértelmezésben 6. A WITH TIME ZONE megadása esetén az értékek még a felhasználó időzónájának adatát is tartalmazzák. A LOCAL megadása esetén pedig az adatbázisban tárolt (és nem a felhasználói) időzóna adata kerül kezelésre.

INTERVAL TÍPUS

A INTERVAL típus segítségével két időbélyeg közötti időtartam értékeit kezelhetjük. Szintaxisa:

INTERVAL {YEAR[(p)] TO MONTH|DAY[(np)] TO SECOND[(mp)]}

A YEAR[(p)] TO MONTH az időintervallumot években és hónapokban adja. A p az évek értékének tárolására használt számjegyek száma. Alapértelmezett értéke 2.

A DAY[(np)] TO SECOND [(mp)] az időintervallumot napokban és másodpercekben adja; np a napok, mp a másodpercek értékének tárolására használt számjegyek száma; np alapértelmezett értéke 2, mp-é 6.

Logikai típus

Egyetlen típus tartozik ide, a BOOLEAN, amelynek tartománya a logikai igaz, hamis és a NULL értéket tartalmazza. Logikai típusú literál nincs, de az Oracle három beépített nevesített konstanst értelmez. TRUE értéke a logikai igaz, FALSE értéke a logikai hamis és NULL értéke NULL.

A rekordtípus

A rekord logikailag egybetartozó adatok heterogén csoportja, ahol minden adatot egy-egy mező tárol. A mezőnek saját neve és típusa van. A rekordtípus teszi lehetővé számunkra, hogy különböző adatok együttesét egyetlen logikai egységként kezeljünk. A rekord adattípus segítségével olyan programeszközöket tudunk deklarálni, amelyek egy adatbázistábla sorait közvetlenül tudják kezelni. Egy rekordtípus deklarációja a következőképpen történik:

TYPE név IS RECORD(

mezőnév típus [[NOT NULL] {:=|DEFAULT} kifejezés]

[,mezőnév típus [[NOT NULL] {:=|DEFAULT} kifejezés]]…);

A név a létrehozott rekordtípus neve, a továbbiakban deklarációkban a rekord típusának megadására szolgál. A mezőnév a rekord mezőinek, elemeinek neve.

A típus a REF CURSOR kivételével bármely PL/SQL típus lehet.

A NOT NULL megadása esetén az adott mező nem veheti fel a NULL értéket. Ha futási időben mégis ilyen értékadás következne be, akkor kiváltódik a VALUE_ERROR kivétel. NOT NULL megadása esetén kötelező az inicializálás.

A :=|DEFAULT utasításrész a mező inicializálására szolgál. A kifejezés a mező kezdőértékét határozza meg. Egy rekord deklarációjának alakja:

rekordnév rekordtípus_név;

DECLARE

TYPE cikk IS RECORD (

cikkkod NUMBER NOT NULL := 0,

cikknev VARCHAR2(20),

afa_kulcs NUMBER := 0.27

);

Az adattípusok között konverziós függvényeket használhatunk a konvertálásra. Ezeket a következő táblázat foglalja össze:

Függvény

Leírás

Konvertálható családok

TO_CHAR

A megadott paramétert VARCHAR2 típusúra konvertálja, opcionálisan megadható a formátum.

Numerikus, dátum

TO_DATE

A megadott paramétert DATE típusúra konvertálja, opcionálisan megadható a formátum.

Karakteres

TO_TIMESTAMP

A megadott paramétert TIMESTAMP típusúra konvertálja, opcionálisan megadható a formátum.

Karakteres

TO_TIMESTAMP_TZ

A megadott paramétert TIMESTAMP WITH TIMEZONE típusúra konvertálja, opcionálisan megadható a formátum.

Karakteres

TO_DSINTERVAL

A megadott paramétert INTERVAL DAY TO SECOND típusúra konvertálja, opcionálisan megadható a formátum.

Karakteres

TO_YMINTERVAL

A megadott paramétert INTERVAL YEAR TO MONTH típusúra konvertálja, opcionálisan megadható a formátum.

Karakteres

TO_NUMBER

A megadott paramétert NUMBER típusúra konvertálja, opcionálisan megadható a formátum.

Karakteres

TO_BINARY_DOUBLE

A megadott paramétert BINARY_DOUBLE típusúra konvertálja, opcionálisan megadható a formátum.

Karakteres, Numerikus

TO_BINARY_FLOAT

A megadott paramétert BINARY_FLOAT típusúra konvertálja, opcionálisan megadható a formátum.

Karakteres, Numerikus

RAWTOHEX

A paraméterként megadott bináris érték hexadecimális reprezentációját adja meg.

Raw

HEXTORAW

A paraméterként megadott hexadecimális reprezentációjú értéket a bináris formájában adja meg.

Karakteres



(hexadecimális reprezentációt kell tartalmaznia).

CHARTOROWID

A karakteres reprezentációjával adott ROWID belső bináris alakját adja meg.

Karakteres (18-karakteres rowid formátumot kell tartalmaznia).

ROWIDTOCHAR

A paraméterként megadott belső bináris reprezentációjú ROWID külső karakteres reprezentációját adja meg.

Rowid

Vezérlési szerkezetek

A feltételes utasítás

A feltételes utasítás egymást kölcsönösen kizáró logikai feltételek közül választ ki egyet, és ez alapján hajt végre egy vagy néhány utasítást.

Alakja:

IF feltétel THEN utasítás [utasítás]…

[ELSIF feltétel THEN utasítás [utasítás]…]…

[ELSE utasítás [utasítás]…]

END IF;

A feltételes utasításnak három formája van:

IF-THEN,

IF-THEN-ELSE és

IF-THEN-ELSIF.

A legegyszerűbb alak esetén a tevékenységet a THEN és az END IF alapszavak közé zárt utasítássorozat írja le. Ezek akkor hajtódnak végre, ha a feltétel értéke igaz. Hamis és NULL feltételértékek mellett az IF utasítás nem csinál semmit.

Az IF-THEN-ELSE alak esetén az egyik tevékenységet a THEN és ELSE közötti, a másikat az ELSE és END IF közötti utasítássorozat adja. Ha a feltétel igaz, akkor a THEN utáni, ha hamis vagy NULL, akkor az ELSE utáni utasítássorozat hajtódik végre.

A harmadik alak egy feltételsorozatot tartalmaz. Ez a feltételsorozat a felírás sorrendjében értékelődik ki. Ha valamelyik igaz értékű, akkor az utána következő THEN-t követő utasítássorozat hajtódik végre. Ha minden feltétel hamis vagy NULL értékű, akkor az ELSE alapszót követő utasítássorozatra kerül a vezérlés, ha nincs ELSE rész, akkor ez egy üres utasítás.

Az IF utasítás esetén bármely tevékenység végrehajtása után (ha nem volt az utasítások között GOTO) a program az IF-et követő utasításon folytatódik.

A THEN és ELSE után álló utasítások között lehet újabb IF utasítás, az egymásba skatulyázás mélysége tetszőleges.

declare

x number;

y number;

nagyobb number;

if y > x then nagyobb = y else nagyobb = x;

A CASE utasítás

A CASE egy olyan elágaztató utasítás, ahol az egymást kölcsönösen kizáró tevékenységek közül egy kifejezés értékei, vagy feltételek teljesülése szerint lehet választani. Az utasítás alakja:

CASE [szelektor_kifejezés]

WHEN {kifejezés | feltétel} THEN utasítás [utasítás]…

[WHEN {kifejezés | feltétel} THEN utasítás [utasítás]…]…

[ELSE utasítás [utasítás]…]

END CASE;

Ha a CASE utasítás címkézett, az adott címke az END CASE után feltüntethető.

Tehát egy CASE utasítás tetszőleges számú WHEN ágból és egy opcionális ELSE ágból áll. Ha a szelektor_kifejezés szerepel, akkor a WHEN ágakban kifejezés áll, ha nem szerepel, akkor feltétel.

Működése a következő:

Ha szerepel szelektor_kifejezés, akkor ez kiértékelődik, majd az értéke a felírás sorrendjében hasonlításra kerül a WHEN ágak kifejezéseinek értékeivel. Ha megegyezik valamelyikkel, akkor az adott ágban a THEN után megadott utasítássorozat hajtódik végre, és ha nincs GOTO, akkor a működés folytatódik a CASE utasítást követő utasításon.

Ha a szelektor_kifejezés értéke nem egyezik meg egyetlen kifejezés értékével sem és van ELSE ág, akkor végrehajtódnak az abban megadott utasítások, és ha nincs GOTO, akkor a működés folytatódik a CASE utasítást követő utasításon. Ha viszont nincs ELSE ág, akkor a CASE_NOT_FOUND kivétel váltódik ki.

Ha a CASE alapszó után nincs megadva szelektor_kifejezés, akkor a felírás sorrendjében sorra kiértékelődnek a feltételek és amelyik igaz értéket vesz fel, annak a WHEN ága kerül kiválasztásra. A szemantika a továbbiakban azonos a fent leírtakkal.

DECLARE

v_Allat VARCHAR2(10);

BEGIN

v_Allat := 'hal';

CASE v_Allat || 'maz'

WHEN 'halló' THEN

DBMS_OUTPUT.PUT_LINE('A halló nem is állat.');

WHEN SUBSTR('halmazelmélet', 1, 6) THEN

DBMS_OUTPUT.PUT_LINE('A halmaz sem állat.');

WHEN 'halmaz' THEN

DBMS_OUTPUT.PUT_LINE('Ez már nem fut le.');

ELSE

DBMS_OUTPUT.PUT_LINE('Most ez sem fut le.');

END CASE;

END;

Ciklusok
A ciklusok olyan programeszközök, amelyek egy adott tevékenység tetszés szerinti (adott esetben akár nullaszoros) ismétlését teszik lehetővé. Az ismétlődő tevékenységet egy végrehajtható utasítássorozat írja le, ezt az utasítássorozatot a ciklus magjának nevezzük.

A PL/SQL négyfajta ciklust ismer, ezek a következők:

alapciklus (vagy végtelen ciklus);

WHILE ciklus (vagy előfeltételes ciklus);

FOR ciklus (vagy előírt lépésszámú ciklus);

kurzor FOR ciklus.

A ciklusmag ismétlődésére vonatkozó információkat (amennyiben vannak) a mag előtt, a ciklus fejében kell megadni. Ezek az információk az adott ciklusfajtára nézve egyediek. Egy ciklus a működését mindig csak a mag első utasításának végrehajtásával kezdheti meg. Egy ciklus befejeződhet, ha

az ismétlődésre vonatkozó információk ezt kényszerítik ki;

a GOTO utasítással kilépünk a magból;

az EXIT utasítással befejeztetjük a ciklust;

kivétel váltódik ki.

Alapciklus

Az alapciklus alakja a következő:

[címke] LOOP utasítás [utasítás]…

END LOOP [címke];

Az alapciklusnál nem adunk információt az ismétlődésre vonatkozóan, tehát ha a magban nem fejeztetjük be a másik három utasítás valamelyikével, akkor végtelenszer ismétel.

Példa egy látszólag végtelen ciklus. De kivétel miatt ez is befejeződik, hiszen a faktoriális értéke meghaladja az 5 számjegyet.

DECLARE

v_Faktorialis NUMBER(5);

i PLS_INTEGER;

BEGIN

i := 1;

v_Faktorialis := 1;

LOOP

v_Faktorialis := v_Faktorialis * i;

i := i + 1;

END LOOP;

EXCEPTION

WHEN VALUE_ERROR THEN

DBMS_OUTPUT.PUT_LINE(v_Faktorialis

|| ' a legnagyobb, legfeljebb 5-jegyű faktoriális.');

END;

/

WHILE ciklus

A WHILE ciklus alakja a következő:

[címke] WHILE feltétel

LOOP utasítás [utasítás]…

END LOOP [címke];

Ennél a ciklusfajtánál az ismétlődést egy feltétel szabályozza. A ciklus működése azzal kezdődik, hogy kiértékelődik a feltétel. Ha értéke igaz, akkor lefut a mag, majd újra kiértékelődik a feltétel. Ha a feltétel értéke hamis vagy NULL lesz, akkor az ismétlődés befejeződik, és a program folytatódik a ciklust követő utasításon.

A WHILE ciklus működésének két szélsőséges esete van. Ha a feltétel a legelső esetben hamis vagy NULL, akkor a ciklusmag egyszer sem fut le (üres ciklus). Ha a feltétel a legelső esetben igaz és a magban nem történik valami olyan, amely ezt az értéket megváltoztatná, akkor az ismétlődés nem fejeződik be (végtelen ciklus).

Az előző példát kivételkezelés nélkül is meg tudjuk oldani:

DECLARE

v_Faktorialis NUMBER(5);

i PLS_INTEGER;

BEGIN

i := 1;

v_Faktorialis := 1;

WHILE v_Faktorialis * i < 10**5 LOOP

v_Faktorialis := v_Faktorialis * i;

i := i + 1;

END LOOP;

DBMS_OUTPUT.PUT_LINE(v_Faktorialis

|| ' a legnagyobb, legfeljebb 5-jegyű faktoriális.');

END;

/

FOR ciklus

Ezen ciklusfajta egy egész tartomány minden egyes értékére lefut egyszer. Alakja:

[címke] FOR ciklusváltozó IN [REVERSE] alsó_határ..felső_határ

LOOP utasítás [utasítás]...

END LOOP [címke];

A ciklusváltozó (ciklusindex, ciklusszámláló) implicit módon PLS_INTEGER típusúnak deklarált változó, amelynek hatásköre a ciklusmag. Ez a változó rendre felveszi az alsó_határ és felső_határ által meghatározott egész tartomány minden értékét és minden egyes értékére egyszer lefut a mag. Az alsó_határ és felső_határ egész értékű kifejezés lehet. A kifejezések egyszer, a ciklus működésének megkezdése előtt értékelődnek ki.

A REVERSE kulcsszó megadása esetén a ciklusváltozó a tartomány értékeit csökkenően, annak hiányában növekvően veszi fel. Megjegyzendő, hogy REVERSE megadása esetén is a tartománynak az alsó határát kell először megadni:

FOR i IN REVERSE 1..10 LOOP …

A ciklusváltozónak a ciklus magjában nem lehet értéket adni, csak az aktuális értékét lehet felhasználni kifejezésben.

Ha az alsó_határ nagyobb, mint a felső_határ, a ciklus egyszer sem fut le (üres ciklus). A FOR ciklus nem lehet végtelen ciklus.

DECLARE

v_Osszeg PLS_INTEGER;

BEGIN

v_Osszeg := 0;

FOR i IN 1..100 LOOP

v_Osszeg := v_Osszeg + i;

END LOOP;

DBMS_OUTPUT.PUT_LINE(' 1 + 2 + ... + 100 = ' || v_Osszeg || '.');

END;

/

Az EXIT utasítás

Az EXIT utasítás bármely ciklus magjában kiadható, de ciklusmagon kívül nem használható. Hatására a ciklus befejezi a működését. Alakja:

EXIT [címke] [WHEN feltétel];

Az EXIT hatására a ciklus működése befejeződik és a program a követő utasításon folytatódik.

Vegyes tesztkérdések, feladatok

Vegyes feladatok

Adott a következő adattábla:

DOLGOZO(id integer not null primary key, nev varchar (50), szdatum date, fizu numeric(12,2), sz_hely varchar (30), belep_ev int, neme char(1));

Id: azonosító, nev: a dolgozó neve, szdatum: születési dátum, fizu: a dolgozó fizetése, sz_hely: a város, ahol született, belep_ev: a céghez belépés éve, neme: F-férfi, N-nő.

Válassza ki a helyes SQL mondatokat, melyek megadják a választ a feltett kérdésekre!

Egy kérdésre legalább egy jó választ kell kiválasztani.

1. Minden Kovács adata:

SELECT * FROM dolgozo WHERE nev LIKE „Kovács%”;

SELECT * FROM dolgozo WHERE nev = „Kovács%”;

SELECT * FROM dolgozo WHERE nev = Kovács ;

SELECT * FROM dolgozo WHERE nev LIKE Kovács%;

2. A 100 000 és 120 000 Ft közötti fizetések:

SELECT nev, fizu FROM dolgozo WHERE fizu BETWEEN 100000 AND 120000;

SELECT nev, fizu FROM dolgozo WHERE fizu BETWEEN „100000 Ft” AND „120000 Ft”;

SELECT nev, fizu FROM dolgozo WHERE (fizu >= 100000) AND (fizu <= 120000);

SELECT nev, fizu FROM dolgozo WHERE (fizu >= 100000) OR (fizu <= 120000);

3. Az 1970.01.01 után született dolgozók neve és fizetése, akik 100000 Ft-nál többet keresnek.

SELECT nev, fizu FROM dolgozo WHERE  (sz_datum > ’1970.01.01’) OR (fizu > 100000);

SELECT nev, fizu FROM dolgozo WHERE  sz_datum > ’1970.01.01’ AND fizu > 100000;

SELECT nev, fizu FROM dolgozo WHERE  sz_datum IN ’1970.01.01’ AND fizu < 100000;

SELECT DISTINCT nev, fizu FROM dolgozo WHERE  sz_datum > ’1970.01.01’ AND fizu < 100000;

4. Városonként az ott született dolgozó száma

SELECT sz_hely AS Hely, COUNT(id) as Törzsszám FROM dolgozo ORDER BY sz_hely;

SELECT sz_hely AS Hely, COUNT(id) as Törzsszám FROM dolgozo Where sz_hely in dolgozo;

SELECT sz_hely AS Hely, COUNT(id) as Törzsszám FROM dolgozo GROUP BY sz_hely;

SELECT sz_hely AS Hely, SUM(id) as Törzsszám FROM dolgozo GROUP BY sz_hely;

5. Melyek azok a városok, ahol a dolgozók átlagfizetése kisebb mint 120 000 Ft.

SELECT  sz_hely AS Hely,  AVG(fizu) as átlag FROM dolgozo GROUP BY sz_hely HAVING fizu < 120 000;

SELECT  sz_hely AS Hely,  AVG(fizu) as átlag FROM dolgozo Where fizu < 120 000 GROUP BY sz_hely ;

SELECT  sz_hely AS Hely,  AVG(fizu) as átlag FROM dolgozo GROUP BY sz_hely HAVING AVG(fizu) < 120 000;

SELECT  nev AS Hely,  AVG(fizu) as átlag FROM dolgozo GROUP BY sz_hely HAVING AVG(fizu) < 120 000;

6. Listázza ki a tavaly belépett nőket névsor szerint csökkenő sorrendben!

     

     

     

     

7. A legalább 5 éve itt dolgozó férfiak közül kik keresnek az átlagnál töbet?

     

     

     

     

8. Hány férfi és hány nő született Egerben?

             

     

     

     

9. A dolgozók 10%-al emelt fizetését ki tudjuk listázni a jelenlegi fizetés mellé, a dolgozó nevével, mert a lekérdezés az adatokat több táblából is össze tudja válogatni.

A. Igaz-Igaz-van összefüggés         B. Igaz-Igaz-Nincs összefüggés

C. Igaz-Hamis-Nincs összefüggés        D. Hamis-Igaz-Nincs összefüggés

D. Hamis-Hamis-Nincs összefüggés

10. A having záradék a csoportosított rekordokat szűri tovább, mert a having nagyobb prioritással rendelkezik, mint a where.

A. Igaz-Igaz-van összefüggés         B. Igaz-Igaz-Nincs összefüggés

C. Igaz-Hamis-Nincs összefüggés        D. Hamis-Igaz-Nincs összefüggés

D. Hamis-Hamis-Nincs összefüggés

11. A tulajdonság előfordulás az adattábla egy …

        A. Rekordja        C. Mezője

        B. Mezőérték        D. Mezőtípusa

12. A módosítási anomália azt jelenti hogy …

        A. Rekord módosításkor más rekord módosítása is szükségessé válik.

        B. Rekord módosításkor a módosítás sikertelen lesz.

        C. Rekord beszúrásakor más rekord beszúrása is szükségessé válik.

        D. Rekord beszúrásakor a beszúrás sikertelen lesz.

13. Melyek egész számokat tartalmazó típusok?

        A. SmallInt        B. Integer

        C. BigInt        D. Char

14. Melyek valós számokat tartalmazó típusok?

        A. VarChar        B. Real

        C. Boolean        D. Float

15. A fizetes táblában a 10%-os fizetésemelést így végezzük el minden dolgozóra:

        A. insert fizetes set fiz = 1.1*fiz;

        B. Update fizetes set fiz = 1.1*fiz;

        C. Update fizetes from fiz = 1.1*fiz;

        D. Update fizetes set fiz = 10% * fiz;

16. A Kis nevű dolgozók városa legyen Eger:

        A. Update dolgozo set varos = ’Eger’ having nev like ’Kis’;

        B. Update dolgozo from varos = ’Eger’ where nev like ’Kis’;

        C. Update dolgozo set varos = ’Eger’ where nev like ’Kis’;

17. Milyen záradékkal tudunk rendezni?

        A. Where        B. Broup by

        C. Order by        D. Having

18. Mivel tölti ki az üres helyeket a fix hosszúságú szöveg esetén a rendszer?

        A. Semmivel        B. Szóközzel

        C. Aláhúzással        D. #9 kódú karakterrel

19. Hogyan hozzuk létre a tanuló táblát?

        A. create new table tanulo (id int primary key, nev char(30));

        B. alter table tanulo add nev char (30);

        C. create table tanulo (id int primary key, nev char(30));

        D. alter table tanulo tanulo (id int primary key, nev char(30));

20. Írja le a 3. normálforma definícióját, definiálja az ehhez szükséges fogalmat is.

             

             

             

             

             

             

             

             

21. . Írja le SQL parancsokkal a következő lépéseket:

a. Hozza létre a STUDENT táblát, amiben szerepel az ID egész típusú mező, mint kulcs, a hallgató neve, születési dátuma.

b. Szúrja be új rekordként 17 azonosítóval Kiss Ferenc 1983. március 19-én született hallgató adatait.

c. Javítsa a 17-es azonosítójú hallgató nevét Kiss Mátyásra.

d. Bővítse a táblát a CITY mezővel, ahol a születési helyet fogja tárolni.

22. Válaszoljon a következő kérdésekre egy-egy lekérdezéssel:

a)        Készítsünk név szerint rendezett listát, azon tanulókról, akik Budapesten születtek és valamelyik keresztnevük Zsolt.

b)        Számoljuk ki születési helyenként az átlagos befizetendő menzapénzt, listázzuk születési hely szerint csökkenő sorrendben.

c)        Listázzuk ki az átlagos menzapénznél többet fizetők nevét és születési dátumát és menzapénzét, a pénz szerint növekvő sorrendben!

d)        Tegyük fel hogy létezik a STUDENT tábla mellett a következő tábla:

BEFIZ(ssz int PK, osszeg int, tanulo int, datum date)

Listázzuk ki tanulónként az összes befizetett összeget, név szerint növekvő sorrendben.

e)        Készítsünk listát a budapesti tanulók befizetéseiről, melyek 2009 utolsó hónapjában történtek.

23. Alaptáblák

ember [ id integer primary key, nev varchar(40) not null, varos varchar(40) ]

auto [ rsz char(7) primary key, tulaj integer, tipus varchar(20), szin varchar(20), ar numeric(7,0) ]

Hozzul létre a két adattáblát.

Feladatok:

1.        Kérdezzük le a piros színű autók árait.

2.        Az 500000 és 1000000 Ft közötti értékű autók árát növeljük 20%-al.

3.        Kérdezzük le a ’K’ betűvel kezdődő tulajdonosokat és autóik típusát.

4.        Kérdezzük le a miskolci és egri autótulajdonosok nevét és autóik árát, a tulajdonosok szerint névsorrendbe rendezve.

5.        Kérdezzük le azoknak az autótulajdonosoknak a nevét, akiknek 1 millió Ft-nál olcsóbb autójuk van.

6.        Kérdezzük le azon autótulajdonosoknak a nevét és címét, akiknek van autójuk.

7.        Azoknak az autóknak a rendszámát kérdezzük le, amelyeknek miskolci a tulajdonosuk.

8.        Kérdezzük le azokat az autókat, amelyeknek az ára nagyobb minden piros autóénál.

9.        Kérdezzük le, hogy milyen típusú autó fordulnak elő az autó táblában (ismétlődés nélkül).

10.        Kérdezzük le a miskolci autók átlagárát.

11.        Kérdezzük le, hogy az egyes városokban hány autó van.

12.        Kérdezzük le az átlagárnál drágább autók rendszámát és tulajdonosaik nevét.

13.        Kérdezzük le a legolcsóbb miskolci autónál drágább autók rendszámát.

24. Hozza létre az alábbi szerkezetű, DOLGOZOK nevű táblát:

       KOD VARCHAR2(4) NOT NULL

       NEV VARCHAR2(30) NOT NULL

       FIZETES NUMBER

       SZUL DAT DATE

25. Bővítse a DOLGOZOK táblát a CIM oszloppal, melynek típusa VARCHAR2(30)! A NEV hosszát módosítsa 40-re!

26. Hozza létre az UJ_RESZL1 nevű táblát, melynek szerkezete azonos a RESZLEG nevű tábla szerkezetével!

27. Hozza létre az UJ_RESZL2 nevű táblát, melynek szerkezete és tartalma azonos a RESZLEG nevű tábla szerkezetével és tartalmával!

28. Nevezze át az UJ_RESZL2 táblát RESZLEG2 névre!

29. Hozza létre azt a NEZET nevű nézettáblát, amely az ALKALMAZOTT és a RESZLEG táblából csak az 'ELADO' beosztású dolgozók kódját, nevét, beosztását, részlegének kódját, nevét és címét tartalmazza!

30. Hozza létre azt a VIDEK nevű nézettáblát, amely csak a nem Budapesti részlegek adatait tartalmazza!

31. Hozza létre az ATLAG nevű nézettáblát, amely a részlegek kódját és az ott dolgozók átlagfizetését tartalmazza! Készítsen listát a létrehozott nézettábla segítségével, amelyben a dolgozók neve, fizetése, részlegének kódja és a részleg átlagfizetése szerepel!

32. Az előző feladatban létrehozott ATLAG nevű nézettábla felhasználásával írassa ki a dolgozók nevét, fizetését, részlegének nevét és címét, valamint a részlegben dolgozók átlagfizetését!

33. Készítse el a RENDELES és AUTOK táblák alapján az UJ_RENDELES nézettáblát, melynek oszlopai az ügyfélszám, az autócsoport, a kölcsönzött autó típusa, a rendelés dátuma, a megrendelő személy neve, a kölcsönzési idő kezdete és időtartama, valamint a fizetés módja legyenek! Listázza ki a tábla tartalmát!

34. Módosítsa az előző feladatban létrehozott nézettábla szerkezetét úgy, hogy egy oszlop a kölcsönzési idő alatt futott km-ek számát is mutassa!

35. Hozza létre az UGYFELEK, TIPUSOK, AUTO_CSOP és RENDELES táblák alapján azt az UJ_UGYFEL nevű nézettáblát, amely a következő oszlopokat tartalmazza: az ügyfél száma, neve, a kapcsolatot tartó személy neve, a rendelt autó típusa, rendszáma, rendelési ideje, a kölcsönzés ideje alatt futott km, a kölcsönzési díj km-enként és naponként!

36. Hozza létre a KOLCSON_SZAM nevű nézettáblát a RENDELES tábla alapján, amely rendszámonként tartalmazza a kölcsönzések számát! Irassa ki a nézettábla tartalmának felhasználásával az AUTOK táblában szereplő autók rendszámát, típusát és a kölcsönzések számát! Az egyszer sem kölcsönzött autóknál a kölcsönzések száma 0 legyen!

37. Hozza létre azt a táblát, amely a rendszámot, az utolsó szerviz idején mutatott és a jelenlegi km-óra állást, valamint a kötelező szerviz intervallumot tartalmazza! A tábla neve KARBANTART legyen!

38. Az AUTOK tábla alapján hozza létre az ELADO_AUTOK nevű táblát, melynek oszlopai a rendszám, a típusnév, a vásárlás dátuma és a futott kilométerek száma legyen (új oszlopnevekkel)!

39. A KARBANTART nevű táblában növeljük meg a kilométeróra állását mutató oszlop hosszát 8-ra!

40. Bővítse a KARBANTART táblát a következő szerviz nevű oszloppal! Hossza legyen 8, típusa numerikus!

41. Hozzon létre indexet az AUTOK táblára a rendszám alapján!

42. Hozzon létre indexet a RENDELES táblához az ügyfélszám és az autó típus név szerint!

43. Vigye fel a RESZLEG2 táblába a 80-as kódú részleg adatait! Részleg név: AUTOKOLCSONZO, cím: SZEGED.

44. Vigye fel a RESZLEG2 táblába a RESZLEG táblából a kölcsönző irodák adatait!

45. Vigye fel a VIDEK nézettáblába a 99-es kódú részleget FORD --- AUTO névvel, DEBRECEN címmel, majd nézze meg, hogy bekerült-e a sor a RESZLEG táblába és a nézettáblába!

46. Módosítsa a RESZLEG2 táblában a 'KOZPONT' részleg nevét 'IRODAK'-ra!

47. Növelje meg a 10-es kódú részlegben dolgozók fizetését 15%-kal!

48. Növelje meg az'ELADO' beosztású dolgozók prémiumát 10000 Ft tal!

49. Törölje ki a RESZLEG2 táblából a debreceni részlegeket!

50. Vigye fel az AUTOK táblába a legújabban beszerzett autó adatait:

       Rendszám: CAR-342

       Típusnév: RENAULT ESPACE

       Autócsoport: LUXUS

       Vásárlás dátuma: 1994. június 23.

       Ár: 1.400.000 Ft

       Futott km: 100

       Utolsó szervíz: 0 km-nél

       Állapot: kiadható (A)

       Részleg: 20

51. Vigye fel az ELADO_AUTOK táblájába azokat az autókat, amelyek 150000-nél többet futottak!

52. Módosítsa az AUTO_CSOP táblában a kölcsönzés km-enkénti díját 10%-kal megnövelt értékre!

53. Módosítsa a NORMAL autócsoport autóira a szervízelések közötti intervallumot 12000 km-rel

54. Törölje az AUTOK táblából azokat az autókat, melyeket az ELADO_AUTOK táblába felvitt! (175. feladat)

55. Törölje az AUTOK táblából az ABC-022 rendszámú autót!

56. Írjon INSERT utasítást, amely az EXTRA autócsoport valamennyi autójának adatait beírja a most létrehozott, de még üres EX AUTOK táblába!

57. Írjon UPDATE utasítást, amely az EX_AUTOK tábla valamennyi 'OPEL ASTRA' típusú autó részleg kódját'99'-re módosítja!

58. Törölje az EX AUTOK tábla tartalmát a'99'-es kódú autók kivételével!

59. Vigye át az AUTOK táblából az ELADO_AUTOK táblába azon jármŰvek adatait, amelyek az autócsoportjuk átlagánál 50%-kal több kilométert futottak!

60. Új ügyfelet kell felvennie az UGYFELEK táblába!

       (Hozzon létre egy szekvenciát 351-es kezdőértékkel!)

       Ügyfélszáma: a táblában következő érték

       Neve: Karát KFT.

       Címe: 4025 Debrecen, Nyugati utca 7.

       Megbízott: Nagy Péterné

       A cég átutalással fizet.