Mérési Segédlet

Informatika II

SQL nyelv

Mérés Célja

A mérés során a hallgatók megismerkednek az adatbázisok általános lekérdező nyelvével az SQL nyelvvel, azon belül is hangsúlyosan a DML (Data MAnipulation Language) kifejezésekkel.

2. Szükséges ismeretek

A mérés elvégzéséhez SQL nyelv 111g66b ismerete szükséges, valamint néhány speciális Oracle Server beépített függvény ismerete (dátum és string manipulációs függvények). Az SQL nyelvet előadáson került ismertetésre, a feladatok megoldásához a következő Oracle függvényekre lesz szükség:

• Sysdate: Ez a makró az aktuális rendszerdátumot adja vissza.
• to_date(érték, formátum string): string àdátum konverzió. Pl: to_date('2006.04.16','YYYY.MM.DD')
• to_char(érték, formátum string): dátum, számàstring konverzió. A formátum string szintaktikája megegyezik a to_date-nél alkalmazott szintaktikával.
• Dátum+szám: Az adott dátum értékét növeli megadott számú nappal.

3. Minta példasor

A mérés során hasonló példasort kell megoldani.

Egy könyvtári rendszer néhány alaptábláját, és azok adatait hozza létre az adatbázist generáló SQL  állományt lefuttatva.

Listázza ki azon tagoknak a nevét, akiknek lejárt a tagságuk!

Listázza ki azon tagoknak a nevét, akiknek lejárt a kölcsönzésük, de még nem hozták vissza a könyvet!

Hosszabbítsa meg azoknak a kölcsönzéseit, akiknek még nem járt le a kölcsönzési határidejük, és még nem hozták vissza a könyvet!

Listázza ki azokat a szerzőket, akiknek még egyetlen könyvét sem kölcsönözték ki, de van könyvük a könyvtárban!

Listázza ki azokat a szerzőket, akik a legdrágább könyvet írták!(könyvcím, ár, vezetéknév, keresztnév)

Listázza ki, a visszahozott kölcsönzések alapján, átlag hány napig vannak kikölcsönözve az egyes könyvek! (Könyvcím, átlagkölcsönzési idő)

Listázza ki, a visszahozott kölcsönzések alapján, hogy egyes könyvek minimum, maximum, átlag mennyi ideig vannak kikölcsönözve! (cím, min, max, átlag)

Listázza ki, a visszahozott kölcsönzések alapján, hogy egyes tagok minimum, maximum, átlag mennyi ideig kölcsönöznek ki egy könyvet! (név, min, max, átlag)

Listázza ki azon tagokat, akik átlagban több, mint 30 napig kölcsönöznek ki egy könyvet!

Listázza ki, hogy összesen hány napot volt kikölcsönözve az egy embernél legtovább kikölcsönzött könyv!(cím, összes nap)

Listázza ki azon tagokat, akiknek a tagságuk 2000-ben jár le.

Hosszabbítsa meg azon tagok le nem járt kölcsönzéseit, akiknek a neve kov-val kezdődik.

4. Megoldások:

create table konyv

( isbn number(10,0),

cim varchar2(30),

nyelv varchar2(10),

ar number(6,0),

megjelenes date,

kiado varchar2(30),

leiras varchar2(1500)

create table szerzo

( id number(10,0),

vezeteknev varchar2(30),

keresztnev varchar2(30),

nemzetiseg varchar2(10)

create table konyv_szerzo

( id number(10,0),

konyvisbn number(10,0),

szerzoid number(10,0),

reszesedes number(3,2)

create table tag

( id number(10,0),

vezeteknev varchar2(30),

keresztnev varchar2(30),

iranyitoszam number(4,0),

varos varchar2(20),

utca varchar2(40),

tagsagiervenyes date

create table kolcsonzesek

( id number(10,0),

konyvisbn number(10,0),

tagid number(10,0),

kiviteldatum date,

lejaratdatum date,

visszahozataldatum date

select vezeteknev, keresztnev
from tag
where tagsagiervenyes< sysdate

select distinct vezeteknev, keresztnev
from tag,kolcsonzesek
where tag.id=kolcsonzesek.tagid
and lejaratdatum < sysdate
and visszahozataldatum is null

update kolcsonzesek
set lejaratdatum = lejaratdatum +10
where lejaratdatum > sysdate
and visszahozataldatum is null

Első megoldás:

select distinct vezeteknev,keresztnev,szerzo.id
from szerzo, konyv_szerzo, konyv
where szerzo.id=konyv_szerzo.szerzoid
and konyv.isbn=konyv_szerzo.konyvisbn
minus
select vezeteknev,keresztnev,szerzo.id
from szerzo, kolcsonzesek, konyv_szerzo
where szerzo.id=konyv_szerzo.szerzoid
and konyv_szerzo.konyvisbn=kolcsonzesek.konyvisbn

Második megoldás:

select distinct vezeteknev,keresztnev
from szerzo, konyv_szerzo, konyv
where szerzo.id=konyv_szerzo.szerzoid
and konyv.isbn=konyv_szerzo.konyvisbn
and szerzo.id not in
(
select szerzo.id
from szerzo, kolcsonzesek, konyv_szerzo
where szerzo.id=konyv_szerzo.szerzoid
and konyv_szerzo.konyvisbn=kolcsonzesek.konyvisbn
)

Harmadik megoldás:

select distinct vezeteknev,keresztnev
from szerzo, konyv_szerzo, konyv,
(
select szerzo.id as id
from szerzo, kolcsonzesek, konyv_szerzo
where szerzo.id=konyv_szerzo.szerzoid
and konyv_szerzo.konyvisbn=kolcsonzesek.konyvisbn
)b
where szerzo.id=konyv_szerzo.szerzoid
and konyv.isbn=konyv_szerzo.konyvisbn
and szerzo.id = b.id(+)
and b.id is null

select cim,vezeteknev, keresztnev
from szerzo, konyv_szerzo, konyv,
(
select max(ar) as maxar
from konyv
) a
where szerzo.id=konyv_szerzo.szerzoid
and konyv.isbn=konyv_szerzo.konyvisbn
and konyv.ar=a.maxar

select isbn,cim,avg(visszahozataldatum-kiviteldatum) as atlag
from kolcsonzesek,konyv
where konyv.isbn=kolcsonzesek.konyvisbn
and visszahozataldatum is not null
group by isbn,cim

select isbn,cim,
avg(visszahozataldatum-kiviteldatum),
min(visszahozataldatum-kiviteldatum),
max(visszahozataldatum-kiviteldatum)
from kolcsonzesek,konyv
where konyv.isbn=kolcsonzesek.konyvisbn
and visszahozataldatum is not null
group by isbn,cim

select tag.id,vezeteknev,keresztnev,
avg(visszahozataldatum-kiviteldatum),
min(visszahozataldatum-kiviteldatum),
max(visszahozataldatum-kiviteldatum)
from kolcsonzesek,tag
where tag.id=kolcsonzesek.tagid
and visszahozataldatum is not null
group by tag.id,vezeteknev,keresztnev

select tag.id,vezeteknev,keresztnev
from kolcsonzesek,tag
where tag.id=kolcsonzesek.tagid
and visszahozataldatum is not null
group by tag.id,vezeteknev,keresztnev

having avg(visszahozataldatum-kiviteldatum)>30

select isbn,cim,sum(k2.visszahozataldatum-k2.kiviteldatum)
from kolcsonzesek k1, kolcsonzesek k2, konyv,
(
select max(visszahozataldatum-kiviteldatum) as maxkolcs
from kolcsonzesek
where visszahozataldatum is not null
) a
where k1.visszahozataldatum-k1.kiviteldatum=a.maxkolcs
and konyv.isbn=k1.konyvisbn

and k2.konyvisbn=k1.konyvisbn

and k2.visszahozataldatum is not null
group by isbn,cim

Első megoldás:

select vezeteknev,keresztnev
from tag
where tagsagiervenyes between to_date('2000.01.01','yyyy.mm.dd') and to_date('2000.12.31','yyyy.mm.dd')

Második megoldás:

select vezeteknev,keresztnev
from tag
where to_char(tagsagiervenyes,'yyyy.mm.dd') like '2000%'

update kolcsonzesek
set lejaratdatum = lejaratdatum+10
where lejaratdatum < sysdate
and visszahozataldatum is null
and tagid in
(
select id
from tag
where upper(vezeteknev) like 'KOV%'
)