B I T M A N B I v: T M A N

Adatbázis Rendszerek MSc 3. Gy: MySQL – Lekérdezések

B I T MAN

1/61

B Iv: T2015.03.09 MAN

Témakörök



SQL alapok



DDL utasítások



DML utasítások

DQL utasítások  DCL utasítások 

2/61

B I T MAN

DQL – Adat lekérdező utasítás  Az SQL nyelvben egyetlen parancs, a SELECT parancs

szolgál az adatok lekérdezésére  Alkalmas a relációs algebra minden műveletének a leképzésére  Feladata: Egy vagy több adattáblából egy eredménytábla előállítása, amely a képernyőn listázásra kerül, vagy más módon használható fel. SELECT [DISTINCT] oszloplista FROM táblanévlista [WHERE feltétel]; DISTINCT: Az eredménytáblában az ismétlődő rekordokból csak egy jelenik meg.

3/61

B I T MAN

Projekció (vetítés) Projekció: csak a kijelölt mezők jelennek meg az eredmény relációban. Ár Rendszám Típus Szín Kor

Jele:

mezőlista(r)

Megvalósítása:

Fehér

6

1200000

JED-123

Nissan Ezüst

5

1500000

AKT-392

Trabant

32

25000

GBC-765

Opel

Kék

SELECT mezőnévlista FROM táblanév; SELECT Rendszám, Típus FROM autó;

Típus

EuroÁr

SELECT * FROM autó;

Opel

4285

Nissan

5357

Trabant

89

SELECT Típus, Ár/280 As EuroÁr FROM autó; 4/61

B I T MAN

Szelekció (kiválasztás) Szelekció: a megadott feltételnek eleget tévő rekordok kerülnek át az eredmény relációba.

Jele:

felt(r)

Megvalósítása: SELECT [DISTINCT] * FROM táblanév WHERE feltétel; SELECT * FROM autó WHERE Kor<10; Szín

Kor

Ár

Fehér

6

1200000

Nissan Ezüst

5

1500000

Rendszám Típus GBC-765 JED-123

5/61

Opel

B I T MAN

Feltételek megfogalmazása Használható operátorok:  Relációs operátorok (=, !=, <, >, !<, !>, <>, <=, >=)  Algebrai operátorok (+,-,*,/)  Logikai operátorok (AND, OR, NOT) Speciális logikai kifejezések:  x IS NULL: igaz, ha az x mező értéke NULL.  x BETWEEN a AND b: igaz, ha a  x  b.  x IN halmaz: igaz, ha x megegyezik a megadott halmaz egy elemével.  x LIKE minta: igaz, ha az x karaktersorozat megfelel a megadott mintának. Helyettesítő karakterek: _ - egy karakter, % - tetszőleges számú karakter 6/61

B I T MAN

Példák szelekciós feltételekre Ember:

Név

Cím Szül_Év

Kis Géza Eger

1983

Autó:

Rendszám Típus GBC-765

Szín

Opel Fehér

Kor

Ár

6

1200000

 SELECT * FROM Ember WHERE Szül_Év BETWEEN 1975 AND 1980;  SELECT * FROM Ember WHERE Cím IS NULL;

 SELECT * FROM Ember WHERE Név LIKE 'B%';  SELECT * FROM Ember WHERE Név LIKE ‘%_Géza%';  SELECT Rendszám FROM Autó WHERE Szín IN ('piros', 'kék', 'fehér');  SELECT Rendszám,Típus FROM Autó WHERE Ár < 1000000 AND Szín

NOT LIKE '%lila%';  SELECT Rendszám FROM Autó WHERE Rendszám LIKE '_ER-6%';  SELECT Rendszám FROM Autó WHERE Rendszám LIKE '%0' or Rendszám LIKE '%2' or Rendszám LIKE '%4' or Rendszám LIKE '%6' or Rendszám LIKE '%8'; 7/61

B I T MAN

Szelekciós függvények  ABS(n): abszolút é'rték Példa: ABS(-15) = 15

 LOWER(char): konverzió kisbetűsre.   



8/61

Példa: LOWER('Kovács') = 'kovács' UPPER(char): konverzió nagybetűsre. Példa: UPPER('Kovács') = 'KOVÁCS' INITCAP(char): konverzió nagy kezdőbetűsre. Példa: INITCAP('kovács') = 'Kovács' LTRIM(char): balról szóközök eltávolítása. Példa: LTRIM(' alma ') = 'alma ' RTRIM(char): jobbról szóközök eltávolítása. Példa: RTRIM(' alma ') = ' alma'

B I T MAN

Szelekciós függvények 2.  SUBSTR(char, m[, n]): a char string m-edik karakterétől

   

9/61

n hosszú részstringet ad vissza. (Ha n nem szerepel, akkor a végéig.) Az első karakter 1-es sorszámú. Példa: SUBSTR('ABCDEFG',2,3) = 'BCD' TO_CHAR(n): konverzió numerikusról vagy dátumról karakteresre. Példa: TO_CHAR(123) = '123' TO_DATE(char): konverzió karakteresről dátumra. Példa: TO_DATE('15-JAN-06') TO_NUMBER(char): konverzió karakteresről numerikusra. Példa: TO_NUMBER('123') = 123 SQRT(kifejezés): Négyzetgyök, numerikus kifejezésre vonatkozik. Példa: SQRT(16) = 4

B I T MAN

Rendezés a lekérdezésben  Nem szerepel az eredeti modellben, ugyanis az

halmazorientált, ahol nem értelmeztük a rendezettséget.  A gyakorlatban adatainkat listákban tároljuk, ahol rendszerint valamilyen rendezettséget valósítunk meg (akár több szempont szerint is). SELECT ... FROM ... [WHERE ...] ORDER BY oszlopnév [DESC], oszlopnév [DESC],...;  ASC (ascending) : növekvő sorrend - alapértelmezés  DESC (descending) : csökkenő sorrend  Alapértelmezésben növekvő, ha fordítva akarjuk akkor

DESC! SELECT * FROM autó ORDER BY Típus, Ár DESC; 10/61

B I T MAN

Aggregáció a lekérdezésben Aggregáció: a reláció rekordjaira összesítő érték(ek)et tartalmazó rekordot ad vissza Aggregációs függvények:  SUM(x) - összeg  AVG(x) - átlagérték  MAX(x) – legnagyobb érték  MIN(x) – legkisebb érték  COUNT(x) – elemek száma

x: numerikus mező vagy kifejezés értéke

*: minden rekordot érint

SELECT avg(Ár) FROM Autó;

SELECT count(*) FROM Autó; SELECT count(Kor) FROM Autó Where Kor > 10; SELECT Sum(Ár), Avg(Kor) FROM Autó; 11/61

B I T MAN

Csoportképzés a lekérdezésben SELECT ... FROM ... [WHERE ...] GROUP BY oszloplista;  Az egyes csoportba azok a rekordok kerülnek, melyeknél

oszloplista értéke azonos.  Az eredménytáblában egy csoportból egy rekord jelenik meg  Az összesítő függvények csoportonként hajtódnak végre. SELECT Típus, avg(Ár) FROM Autó GROUP BY Típus; SELECT Típus, count(*) FROM Autó WHERE Szín='Piros' GROUP BY Típus ORDER BY Típus; SELECT Szín, min(Ár), max(Ár) FROM Autó GROUP BY Szín;

12/61

B I T MAN

Csoportok szűrése SELECT ... FROM ... [WHERE ...] GROUP BY oszloplista HAVING feltétel;  A csoportképzés után a kapott eredményreláció szűkítése

bizonyos rekordokra.  A WHERE szelekciós opció nem alkalmazható, mert az az alapreláció rekordjain értelmezett szelekcióra vonatkozik! SELECT Típus, avg(Ár) FROM Autó GROUP BY Típus HAVING avg(Ár) > 500000; SELECT Típus, count(*) FROM Autó WHERE Szín='Piros' GROUP BY Típus HAVING count(*) > 3 ORDER BY Típus;

13/61

B I T MAN

Join (szorzat, illesztés)  Ha az eredménytábla két (vagy több) táblából akarjuk az

adatokat megjeleníteni, akkor a táblák összekapcsolására a JOIN parancsot kell használni.  SQL-ben a következő típusokat alkalmazhatjuk:

– – – –

14/61

Alap join (teljes illesztés), Szelekciós join, Outer join, Self join (tábla összekapcsolása önmagával).

B I T MAN

Alap join (teljes illesztés) SELECT mezőlista FROM táblanév1, táblanév2; Autó:

Ember:

Rendszám Típus GBC-765 JED-123

Szín

Ár

Tulaj

Ekód

Név

Cím

Fehér 1200000

114

114

Kis Géza Eger

Nissan Ezüst 1500000

178

178

Jó Éva

Opel

Telefon 20-9555666

Miskolc 30-8765432

SELECT Név,Típus,Ár FROM Autó,Ember; Név

Típus

Ár

Kis Géza

Opel

1200000

Jó Éva

Opel

1200000

Kis Géza Nissan 1500000 Jó Éva 15/61

Nissan 1500000

B I T MAN

Szelekciós join 1. változat SELECT mezőlista FROM tábla1, tábla2 WHERE feltétel;  A szelekciós feltétel és a join feltétel nem válik szét!

Autó:

Ember:

Rendszám Típus

Szín

Ár

GBC-765 Opel Fehér 1200000 JED-123 Nissan Ezüst 1500000 AKT-392 Trabant Kék 25000

Tulaj

Ekód

114 199 102

114 178 102 155

Név

Cím

Telefon

Kis Géza Jó Éva Kék Béla Bő Jenő

Eger Miskolc Ózd Ózd

20-9555666 30-8765432 70-3355440 70-3355440

SELECT Név,Típus,Ár FROM Autó,Ember Where Tulaj=Ekód; Név

Típus

Ár

Kis Géza

Opel

1200000

Kék Béla Trabant 16/61

25000

B I T MAN

Szelekciós join 2. változat SELECT mezőlista FROM tábla1 INNER JOIN tábla2 ON join_feltétel WHERE feltétel;  A szelekciós feltétel és a join feltétel külön-külön megadható!

Autó:

Ember:

Rendszám Típus

Szín

Ár


Tulaj

Ekód

114 199 102

114 178 102 155

Név

Cím

Telefon



20-9555666 30-8765432 70-3355440 70-3355440

SELECT Név,Típus,Ár FROM Autó INNER JOIN Ember ON Tulaj=Ekód Where Ár > 500000; Név Kis Géza 17/61

Típus

Ár

Opel

1200000

B I T MAN

Outer join (külső illesztés) SELECT …FROM tábla1 [LEFT | RIGHT | FULL] OUTER JOIN tábla2 ON join_feltétel [WHERE feltétel] …;  Az illeszkedő pár nélküli rekordok is bekerülnek az eredménybe.

Autó:

Ember:

Rendszám Típus

Szín

Ár


Tulaj

Ekód

114 199 102

114 178 102 155

Név

Cím

Telefon



20-9555666 30-8765432 70-3355440 70-3355440

SELECT Rendszám,Név FROM Autó RIGHT OUTER JOIN Ember ON Tulaj=Ekód Where Cím='Ózd'; Rendszám

Név

AKT-392 Kék Béla 18/61

Bő Jenő

RIGHT: A jobb oldali táblából jönnek a pár nélküli rekordok is, üres értékkel kiegészítve.I T

B

MAN

Outer join (külső illesztés) – Példa  Hány darab autója van az egyes embereknek? Autó: Rendszám Típus

Ember: Szín

Ár

GBC-765 Opel Fehér 1200000 JED-123 Nissan Ezüst 1500000 AKT-392 Trabant Kék 25000 LOG-416 Volvo Piros 2825000

Tulaj

Ekód

114 178 102 114

114 178 102 155

SELECT Név, count(Tulaj) As Darab FROM Autó RIGHT OUTER JOIN Ember ON Tulaj=Ekód Group By Név;

19/61

Név

Cím

Telefon



20-9555666 30-8765432 70-3355440 70-3355440

Név Kis Géza Jó Éva Kék Béla Bő Jenő

Darab 2 1 1 0

B I T MAN

Self join – önillesztés :-) SELECT …FROM tábla t1, tábla t2 [WHERE feltétel] …;  A tábla kétszer szerepel, különböző álnevekkel.

Autó: Rendszám Típus GBC-765 JED-123 HGZ-392 LOG-416

Opel Nissan Nissan Opel

Kor

Ár

Tulaj

5 8 7 6

1200000 1000000 900000 1300000

114 178 102 115

Azon autók rendszáma, melyek idősebbek, mégis drágábbak saját típustársuknál: SELECT a1.Rendszám FROM Autó a1, Autó a2 WHERE a1.Típus=a2.Típus AND a1.Kor>a2.Kor AND a1.Ár>a2.Ár);

20/61

Rendszám JED-123 LOG-416

B I T MAN

Halmazműveletek Unió: SELECT …UNION [ALL] SELECT …; Metszet: SELECT …INTERSECT SELECT …; Különbség: SELECT …MINUS SELECT …;  Csak azonos szerkezetű táblák esetén alkalmazhatók! Rendszám Típus

Szín

Ár

GBC-765 Opel Fehér 1200000 JED-123 Nissan Ezüst 1500000 AKT-392 Trabant Kék 25000 LOG-416 Volvó Kék 2825000

Tulaj

Ekód

114 178 102 114

114 178 102 155

Azok az emberek, akiknek nincs autójuk: SELECT név FROM ember MINUS SELECT név FROM ember WHERE Ekód IN (SELECT Tulaj FROM autó); 21/61

Név

Cím

Telefon



20-9555666 30-8765432 70-3355440 70-3355440

Név

Név


Kis Géza Jó Éva Kék Béla

Név Bő Jenő

B I T MAN

AL-SELECT  Az SQL támogatja azt a lehetőséget, hogy a szelekciós

  



feltételben nemcsak létező, letárolt adatelemekre hivatkozzunk, hanem számított kifejezéseket is alkalmazhassunk. A számítást egy másik SELECT utasítással tudjuk megadni. Tehát az egyik lekérdezés szelekciós feltételében hivatkozunk egy másik lekérdezés eredményére. Az al-lekérdezést mindig zárójelben kell megadni, hogy elemei elkülönüljenek. Formailag megegyezik a normál SELECT utasítással (kivétel:al-select-ben nem lehet rendezni)

22/61

B I T MAN

Az AL-SELECT operátorai Az al-lekérdezés eredményétől függően különböző operátorokat kapcsolhatunk az AL-SELECT-hez:  Ha az eredmény egy rekord: skalár operátorok (relációs operátorok) használhatók.  Ha az eredmény több rekord: halmazoperátorok (IN, ANY, ALL, EXISTS) használhatók. SELECT …FROM …WHERE mező IN (al-select); SELECT …FROM …WHERE mező θ ANY (al-select); SELECT …FROM …WHERE mező θ ALL (al-select); SELECT …FROM …WHERE EXISTS (al-select); θ: tetszőleges relációs operátor 23/61

B I T MAN

Az AL-SELECT operátorai 2.  ANY: a halmaz minden eleméhez hasonlít, ha egyet

talál, akkor igazzal tér vissza  ALL: a halmaz minden eleméhez hasonlít, ha akár egyre nem teljesül, akkor hamisat ad vissza  EXISTS: az eredményhalmaz üres-e (ha üres, akkor hamissal tér vissza)

24/61

B I T MAN

Példák AL-SELECT-re Autó: Rendszám Típus

Ember: Szín

Ár

GBC-765 Opel Piros 1200000 JED-123 Nissan Ezüst 1500000 AKT-392 Trabant Kék 25000 LOG-416 Volvo Piros 2825000

Tulaj

Ekód

114 178 102 114

114 178 102 155

Név

Cím

Telefon



20-9555666 30-8765432 70-3355440 70-3355440

 Az egri tulajdonosok autóinak árát növeljük meg 20%-al:

UPDATE Autó SET Ár=Ár*1.2 WHERE Tulaj IN (SELECT Ekód FROM Ember WHERE Cím='Eger');  Töröljük azokat az embereket, akiknek több mint 2 autójuk van:

DELETE FROM Ember WHERE Ekód IN (SELECT Ekód, count(*) FROM Ember, Autó WHERE Ekód=Tulaj GROUP BY Ekód HAVING count(*) > 2); 25/61

B I T MAN


Ember: Szín

Ár


Tulaj

Ekód

114 178 102 114

114 178 102 155

Név

Cím

Telefon



20-9555666 30-8765432 70-3355440 70-3355440

 Bármely piros autó áránál olcsóbb autók rendszámai:

SELECT Rendszám FROM Autó WHERE Ár < ANY (SELECT Ár FROM Autó WHERE Szin='piros');  Minden piros autó áránál olcsóbb autók rendszámai:

SELECT rsz FROM autó WHERE ár < ALL (SELECT ár FROM autó WHERE szin=‘piros’); 26/61

Rendszám GBC-765 JED-123 AKT-392 Rendszám AKT-392

B I T MAN


Ember: Szín

Ár


Tulaj

Ekód

114 178 102 114

114 178 102 155

Név

Cím

Telefon



20-9555666 30-8765432 70-3355440 70-3355440

 Azok az emberek, akiknek nincs autójuk (1. változat):

SELECT Név FROM Ember WHERE Ekód NOT IN (SELECT Tulaj FROM Autó);  Azok az emberek, akiknek nincs autójuk (2. változat):

SELECT E.Név FROM Ember E WHERE NOT EXISTS (SELECT * FROM Autó A WHERE A.Tulaj=E.Ekód); 27/61

B I T MAN



28/61

Gyakorló feladatok 

Termék-Vásárlás-Vásárló



Oktató-Tantárgy-Hallgató

B I T MAN

Gyakorló feladat: Termék-Vásárlás-Vásárló Kód

Név

Név

VID Vásárlás

Termék Ár

Vásárló Dátum

Leírás

Termék Kód C5

Lakcím

Darab

FizMód

Vásárló Név C25

Ár N6

Leírás C25

Név C25

Lakcím FizMód VID C40 C15 C5

Vásárlás Kód C5 29/61

Dátum Darab D N6

Azon C5

B I T MAN

Gyakorló feladat: Termék-Vásárlás-Vásárló 2. Termék Kód C5

Vásárló Név C25

Ár N6

Leírás C25

Név C25

Lakcím C40

FizMód C15

VID C5

Vásárlás Kód C5

Dátum Darab D N6

Azon C5

Séma leírás: Termék [ Kód, Név, Ár, Leírás ] Vásárló [ Név, Lakcím, FizMód, VID ] Vásárlás [ Kód (Termék), Dátum, Darab, Azon (Vásárló) ]

30/61

B I T MAN

Gyakorló feladat: Termék-Vásárlás-Vásárló 3. Termék [ Kód, Név, Ár, Leírás ] Vásárló [ Név, Lakcím, FizMód, VID ] Vásárlás [ Kód (Termék), Dátum, Darab, Azon (Vásárló) ] create table termék( kód char(5) NOT NULL PRIMARY KEY, név char(25), ár number(6), leiras char(25)); create table vásárló( VID char(5) NOT NULL PRIMARY KEY, név char(25), lakcím char(40), fizmód char(15)); create table vásárlás( kód char(5) REFERENCES termék NOT NULL, dátum date, darab number(6), azon char(5) REFERENCES vásárló NOT NULL ); 31/61

B I T MAN

Gyakorló feladat: Termék-Vásárlás-Vásárló 4. Termék [ Kód, Név, Ár, Leírás ] Vásárló [ Név, Lakcím, FizMód, VID ] Vásárlás [ Kód (Termék), Dátum, Darab, Azon (Vásárló) ]

Alter table vásárló modify lakcím char(50); Alter table vásárló add szülidő date; Nem lehet mező szélességét csökkenteni, és mezőt törölni! Megoldás: Új tábla létrehozása a megfelelő mezőszerkezettel, ezután az adatok átmásolása ebbe a táblába, és az eredeti tábla törlése!

Drop table vásárlás; Drop table vásárló; Drop table termék; 32/61

Nem lehet olyan táblát törölni, amelyre más tábla hivatkozik! Fordított sorrend törléskor, mint létrehozáskor!

B I T MAN


insert into termék Values ('A0123','Sál',1500,'Női-férfi termék'); insert into termek Values (kód,név,ár) Values('A0123','Sál',1500); insert into termek Values ('A0123','Sál',1500,NULL); insert into termék Values ('A0123','Sál',NULL,'Női-férfi termék');

UPDATE termék SET ár=ár*1.1; UPDATE termék SET ár=1200 WHERE kód LIKE 'A%'; Delete from termék; Delete from termék where kód LIKE 'A%'; 33/61

B I T MAN


SELECT név, ár FROM termék;

SELECT név, ár FROM termék ORDER BY név; SELECT név, ár FROM termék ORDER BY ár DESC; SELECT név, ár FROM termék WHERE leírás LIKE '%női%'; SELECT név, lakcím FROM vásárló WHERE fizmód= 'bankkártya'; SELECT név, lakcím FROM vásárló WHERE fizmód= 'bankkártya' OR fizmód= 'utánvét' ; SELECT név, lakcím FROM vásárló WHERE fizmód IN ('bankkártya', 'utánvét'); 34/61

B I T MAN


SELECT név, ár FROM termék WHERE ár BETWEEN 200 AND 500; SELECT név FROM termék WHERE leírás IS NULL; SELECT név, darab FROM termék,vásárlás WHERE termék.kód=vásárlás.kód AND darab>5; SELECT termék.név, vásárló.név FROM termék,vásárlás,vásárló WHERE termék.kód=vásárlás.kód AND vásárló.vid=vásárlás.azon; SELECT termék.név, darab, dátum FROM termék,vásárlás,vásárló WHERE termék.kód=vásárlás.kód AND vásárló.vid=vásárlás.azon AND vásárló.név= 'Kovács Béla'; 35/61

B I T MAN


SELECT vásárló.név FROM termék,vásárlás,vásárló WHERE termék.kód=vásárlás.kód AND vásárló.vid=vásárlás.azon

AND fizmód= 'bankkártya' AND termék.név LIKE '%sál%'; SELECT vásárló.név, dátum FROM vásárlás,vásárló WHERE vásárló.vid=vásárlás.azon AND darab > 3;

SELECT vásárló.név, termék.név FROM termék,vásárlás,vásárló WHERE termék.kód=vásárlás.kód AND vásárló.vid=vásárlás.azon AND dátum = '2009.11.11'; 36/61

B I T MAN


SELECT count(*) FROM termék; SELECT max(ár) FROM termék; SELECT avg(ár) FROM termék; SELECT szum(ár*darab) FROM termék,vásárlás WHERE termék.kód=vásárlás.kód;

37/61

B I T MAN


SELECT szum(ár*darab) FROM termék,vásárlás,vásárló WHERE termék.kód=vásárlás.kód AND vásárló.vid=vásárlás.azon AND vásárló.név= 'Kovács Béla';

SELECT vásárló.név, szum(ár*darab) FROM vásárlás,vásárló WHERE vásárló.vid=vásárlás.azon AND fizmód= 'utánvét' GROUP BY vásárló.név;

38/61

B I T MAN



39/61

Gyakorló feladatok 

Termék-Vásárlás-Vásárló




B I T MAN

Gyakorló feladat: Tantárgy Tkód Cím C4 C20

Kredit N1


Oktató C6

Kapcsoló Tkód Hkód Tanév/félév C4 C6 C20

Hallgató Hkód Név C6 C30

40/61

Oktató Okód Név C6 C30

Tanszék Fizetés C20 N7

Vizsga Tkód Hkód C4 C6

Dátum Jegy D N1

Város Utca C20 C20

Hsz SzülD C5 D

B I T MAN

Gyakorló feladat:

Oktató-Tantárgy-Hallgató 2.

Hozzuk létre a táblákat: A táblák létrehozásának sorrendje lényeges: először azokat kell létrehozni amikre később hivatkozunk, azután a hivatkozó táblákat. Oktató Okód Név C6 C20


CREATE TABLE oktató( okód char(6) PRIMARY KEY, név char(20) NOT NULL, tanszék char(25), fizetés number(7) CHECK (fizetes>=100000)); 41/61

B I T MAN

Gyakorló feladat: Tantárgy Tkód Cím C4 C20

Kredit N1

Oktató C6

Oktató-Tantárgy-Hallgató 3. Hallgató Hkód Név C6 C30

Város Utca C20 C20

Hsz SzülD C5 D

CREATE TABLE tantárgy ( tkód char(4) PRIMARY KEY, cím char(20) NOT NULL, kredit number(1) CHECK(kredit IN(2,3,4,5,6)), oktató REFERENCES oktató NOT NULL); CREATE TABLE hallgató( hkód char(6) PRIMARY KEY, név char(30) NOT NULL, város char(20), utca char(20), hsz char(5), szüld date); 42/61

B I T MAN

Gyakorló feladat: Kapcsoló Tkód Hkód Tanév/félév C4 C6 C20

Oktató-Tantárgy-Hallgató 4. Vizsga Tkód Hkód C4 C6

Dátum Jegy D N1

CREATE TABLE kapcsoló( tkód char(4) REFERENCES tantárgy, hkód char(6) REFERENCES hallgató, tanév/félév char(20), PRIMARY KEY (tkód, hkód)); Ha a Vizsga táblát nem implementáljuk, akkor a Kapcsoló táblában nem szükséges az elsődleges kulcs kijelölése.

CREATE TABLE vizsga ( tkód char(4), hkód char(6), dátum date, jegy number(1), FOREIGN KEY (tkód, hkód) REFERENCES kapcsoló); B I T MAN 43/61

Gyakorló feladat:


Bővítsük az Oktató táblát egy Beosztás mezővel: ALTER TABLE oktató ADD (beosztás char(20)); Töröljük a táblákat: A táblák törlésének sorrendje lényeges: először a hivatkozó táblákat kell törölni, és csak azután lehet azokat, amelyekre van hivatkozás.

DROP TABLE vizsga; DROP TABLE kapcsoló; DROP TABLE hallgató; DROP TABLE tantárgy; DROP TABLE oktató;

44/61

B I T MAN

Gyakorló feladat: Oktató Okód Név C6 C20 Not Null



Beosztás C20

>100000

Vigyünk fel két új Oktató rekordot a megadott integritási feltételek betartásával (ügyeljünk a mezők sorrendjére, számára és típusára): INSERT INTO oktató VALUES ('I4IUF2', 'Kiss Tamás', 'Matematika', 152000,'tanársegéd'); INSERT INTO oktató VALUES ('AB2312', 'Nagy Ferenc', 'Informatika', 220000,NULL);

45/61

B I T MAN

Gyakorló feladat: Oktató Okód C6 Primary I4IUF2 AB2312

Név C20 Not Null Kiss Tamás Nagy Ferenc


Tanszék C25 Matematika Informatika

Fizetés Beosztás N7 C20 >100000 152000 tanársegéd 220000

Vigyünk fel Oktató rekordokat, szándékosan figyelmen kívül hagyva az integritási feltételeket:

INSERT INTO oktató VALUES ('AB2312', 'Kovács Zoltán', 'Fizika', 220000,NULL); Hiba: az elsődleges kulcs már létezik, egyediség sérül

INSERT INTO oktató VALUES ('GFO123', NULL, 'Informatika', 220000,NULL); Hiba: a név mező nem maradhat üres (Az idegen kulcs mező sem!) 46/61

B I T MAN

Gyakorló feladat: Oktató Okód C6 Primary I4IUF2 AB2312

Név C20 Not Null Kiss Tamás Nagy Ferenc


Tanszék C25 Matematika Informatika

Fizetés Beosztás N7 C20 >100000 152000 tanársegéd 220000

Vigyünk fel Oktató és Tantárgy rekordokat, szándékosan figyelmen kívül hagyva az integritási feltételeket:

INSERT INTO oktató VALUES ('EXE234', 'Tóth László', 'Informatika', 80000,NULL); Hiba: a fizetés mezőértéke nem megfelelő

INSERT INTO tantárgy VALUES ('GEIAL222','Adatbázis_1',5,'111111'); Hiba: a hivatkozott oktató nem létezik 47/61

B I T MAN

Gyakorló feladat:


Oktató[ Okód, Név, Tanszék, Fizetés, Beosztás ] Tantárgy [ Tkód, Cím, Kredit, Oktató(Oktató) ] Hallgató [ Hkód, Név, Város, Utca, Hsz, SzülD ] Kapcsoló [ Tkód(Tantárgy), Hkód, Tanév/félév ] Vizsga [ Tkód(Kapcsoló), Hkód(Kapcsoló), Dátum, Jegy ]

Növeljük a tanársegédek fizetését 10%-kal: UPDATE Oktató SET Fizetés=Fizetés*1.1 WHERE Beosztás='tanársegéd';

Növeljük eggyel minden tárgy kreditjét: UPDATE Tantárgy SET Kredit=Kredit+1; Töröljük az Informatika tanszéken dolgozókat:

DELETE FROM Oktató WHERE Tanszék='Informatika'; 48/61

B I T MAN

Gyakorló feladat:


Adjunk olvasási jogot az Oktató táblára mindenkinek: GRANT SELECT ON oktató TO PUBLIC; Adjunk beszúrási jogot az Oktató táblára Péter felhasználónak úgy, hogy továbbadhassa azt: GRANT INSERT ON oktató TO Péter WITH GRANT OPTION;

Vonjunk vissza minden jogot az Oktató táblára vonatkozóan Pétertől: REVOKE ALL ON oktató FROM Péter; Adjunk törlési és módosítási jogot az Oktató táblára Tamásnak:

GRANT DELETE,UPDATE ON oktató TO Tamás; 49/61

B I T MAN

Gyakorló feladat:



Oktatók neve névsorrendben: SELECT név FROM oktató ORDER BY név;

Az Informatika tanszéken oktatók neve: SELECT név FROM oktató WHERE tanszék='Informatika'; Az 5 kreditnél többet érő tárgyak kódja és címe: SELECT tkód, cím FROM tantárgy WHERE kredit>5; Oktatók minden adata, fizetésük csökkenő sorrendjében: SELECT * FROM oktató ORDER BY fizetés DESC; 50/61

B I T MAN

Gyakorló feladat:



Oktatók neve és tárgyaik címe, név és cím szerint sorrendben: SELECT név, cím FROM oktató, tantárgy WHERE okód=oktató ORDER BY név, cím; Az Informatika tanszéken oktatók neve és tárgyaik címe: SELECT név, cím FROM oktató, tantárgy WHERE okód=oktató AND tanszék='Informatika'; Az Informatika tanszéken oktatott, 5 kreditnél többet érő tárgyak kódja: SELECT tkód FROM tantárgy INNER JOIN oktató ON oktató=okód WHERE kredit>5 AND tanszék LIKE 'Inf%'; 51/61

B I T MAN

Gyakorló feladat:



Tanszékek listája ismétlődés nélkül: SELECT DISTINCT tanszék FROM oktató;

Minden oktató neve és tárgyaik címe: SELECT név, cím FROM oktató LEFT OUTER JOIN tantárgy ON okód=oktató; Azok az oktatók, akiknek nincs 5 kreditnél többet érőtárgya: SELECT Név FROM oktató WHERE okód NOT IN (SELECT oktató FROM tantárgy WHERE kredit>5);

52/61

B I T MAN

Gyakorló feladat:



A 150 és 220 ezer Ft között kereső oktatók neve, fizetése: SELECT Név, Fizetés FROM oktató WHERE fizetés BETWEEN 150000 AND 220000; SELECT Név, Fizetés FROM oktató WHERE fizetés >= 150000 AND fizetés <= 220000; Az átlagos kreditpontszám: SELECT avg(kredit) FROM tantárgy; Az Informatika tanszéken oktatók létszáma: SELECT count(*) FROM oktató WHERE tanszék LIKE 'Inf%'; 53/61

B I T MAN

Gyakorló feladat:



A legnagyobb kreditpontszámú tárgyak címe: SELECT cím FROM tantárgy WHERE kredit = (SELECT max(kredit) FROM tantárgy); Az átlagnál alacsonyabb kreditpontú tárgyak címe és oktatóik neve: SELECT cím,név FROM tantárgy, oktató WHERE oktató=okód AND kredit < (SELECT avg(kredit) FROM tantárgy); Tanszékenként az oktatók létszáma, létszám szerint csökkenő sorrendbe rendezve: SELECT tanszék, count(*) FROM oktató GROUP BY tanszék ORDER BY count(*) DESC; 54/61

B I T MAN

Gyakorló feladat:



Azon oktatók, akiknek 2-nél több tárgyuk van: SELECT * FROM oktató WHERE okód IN (SELECT okód, count(*) FROM oktató, tantárgy WHERE okód=oktató GROUP BY okód HAVING count(*)>2); Saját tanszékükre jellemző átlagnál kevesebbet kereső oktatók neve: SELECT o1.név FROM oktató o1 WHERE o1.fizetés < (SELECT avg(o2.fizetés) FROM oktató o2 WHERE o1.tanszék=o2.tanszék);

55/61

B I T MAN

Órai feladat – Bolt adatbázis  A bolt adatbázisban oldja meg a következő feladatokat: 1. Írassa ki a kategóriák minden adatát. 2. Írassa ki a ruha kategóriájú termékek nevét, árát.

3. Írassa ki a 200 Ft-nál olcsóbb termékek nevét, árát. 4. Írassa ki a 4000 Ft-nál drágább, de 12000 Ft-nál olcsóbb

5. 6.

7. 8. 56/61

termékek nevét, árát. Írassa ki Korcs Éva vásárlásait (időpont, termék neve, darabszáma) Kik vásároltak készpénzért? Elég a nevük. Hány féle pia kategóriájú termék van? Összesen hány darab pia kategóriájú terméket adtak már el?

B I T MAN

Órai feladat – Bolt adatbázis 9. Írassa ki az egri vásárlók nevét, címét.

10. Írassa ki a nem egri vásárlók nevét, címét. 11. Írassa ki a bankkártyával fizető egriek nevét. 12. Ki vásárolt puskát?

13. Írassa ki a sört vagy bort vásárlók nevét? 14. Benzinből ki, mikor, mennyit vásárolt? 15. Összesen mennyi volt a bevétel benzinből?

16. Ki, mikor, milyen nőt vásárolt? 17. Ki, mikor vásárolt mackót, és milyet (alsó, póló v. felső)? 18. Hány darab mackó (alsó, póló v. felső) fogyott összesen?

19. Összesen mennyi volt az utánvétes bevétel? 20. Ki, melyik településen lakik? 57/61

B I T MAN

Órai feladat – Bolt adatbázis 21. Melyik településen hányan laknak?

22. Melyek az s-betűre végződő leírású termékek? (Név, leírás) 23. Hány darab termék nevében van c-betű? 24. Mely termékek kódja végződik 2-esre? (Név, kód)

25. A nem kaja kategóriájú termékek közül melyek olcsóbbak 26. 27. 28. 29. 58/61

1000 Ft-nál? (Név, ár) A Béla keresztnevű ember milyen egyéb kategóriájú terméket vásárolt? (Termék neve) Összesen hány darab csipszet vásároltak már? (a meglévő adatok alapján) Milyen termékeket vásároltak már átutalással? (Név, de egy név csak egyszer szerepeljen!) Milyen termékeket nem vásároltak még átutalással?

B I T MAN

Órai feladat – Bolt adatbázis 30. Az egriek mennyi pénzt költöttek el összesen?

31. Az egyes termék kategóriákban hány darab termék van? 32. Kinek nincs megadva a lakcíme? 33. Törölje ki Ká Rozi lacímét! Futtassa a 31. parancsot újra!

34. Ki az, aki átutalással fizet, de nincs megadva a lakcíme? 35. Mely termékek kódja végződik nem 2-esre? (Név, kód)

59/61

B I T MAN

Felhasznált irodalom  Kovács László: PL/SQL, elektronikus jegyzet

 Barabás Péter: Adatbázis rendszerek 2.,

elektronikus jegyzet  Jeffery D. Ullman, Jennifer Widom: Adatbázisrendszerek  Kende Mária, Nagy István: ORACLE példatár  Don Burleson: Oracle Tips (www.dba-oracle.com)

60/61

B I T MAN

VÉGE V ÉGE 61/61

B I T MAN

B I T M A N B I v: T M A N

Recommend Documents