SQL gyakorlatok Oracle környezetben 2. gyakorlat Adatdefiníciós és adatmanipulációs parancsok gyakorlása
1. feladat Egy vállalat dolgozóit kell tárolni egy adatbázisban. Szükség van a dolgozó személyi igazolvány számára, nevére, címére, fizetésére, születési dátumára, nemére, az osztály kódjára és nevére ahol dolgozik, illetve arra, hogy az osztály az épület hányadik emeletén található. A feladat relációs modellje: OSZTÁLY(Azonosító, Tevékenység, Emelet)
DOLGOZÓ(Szigszám, Név, Város, Utca_hsz, Fizetés, Születési_dátum, Nem, Dolgozik) Hozzuk létre a táblákat az alábbiak szerint: OSZTALY • oid: karakteres, fix 4 hosszú, elsődleges kulcs • tevekenyseg: karakteres, változó hosszúságú, max 20 karakter • emelet: numerikus egész, 2 számjegy hosszú, értéke 0 és 99 közé eső szám lehet DOLGOZO • szigszam: karakteres, fix 8 hosszú, elsődleges kulcs • nev: karakteres, változó hossz, max 30 karakter, kötelező kitölteni • varos: karakteres, változó hossz, max 20 karakter • utca_hsz: karakteres, változó hossz, max 30 karakter • fizetes: numerikus egész, 7 számjegy hosszú, értéke legalább 50000 • szuldat: dátum típus • nem: karakteres fix 1 hosszú, értéke ‘F’ vagy ‘N’ lehet, alapértelmezett értéke ‘F’ • dolgozik: karakteres, fix 4 hosszú, idegen kulcs, az OSZTALY tábla oid mezőjére mutat
1
Vegyük fel a következő rekordokat: OSZTALY tábla oid karb info gazd
DOLGOZO tábla szigszam nev aaaaaaaa Gipsz Jakab bbbbbbbb Tub Ica cccccccc Bármi Áron dddddddd Lapos Elemér eeeeeeee Gub Era ffffffff Kukor Ica
tevekenyseg karbantartás informatika gazdasági
varos Kisapostag
Emelet 2 5 3
utca_hsz Ady 12.
fizetes 70000
szuldat 1941-05-15
nem F
dolgozik karb
Dunaújváros Arany 21. Rácalmás Kossuth 65. Dunaújváros
63500 52140
1962-07-23 1974-03-06
N F
gazd karb
Dunaújváros Kulcs
82500 69000
98000
info N N
info gazd
Végezzük el a következő feladatokat: • A nem Dunaújvárosi lakosok kapjanak 3000 forintos fizetésemelést. • Bármi Áron elköltözött, új címe: Mezőfalva, Pálinka sor 11. • Az informatika osztályon dolgozó nők kapjanak 5%-os béremelést. • A cégnél felszámolják a karbantartó részleget, töröljük mindkét táblából a megfelelő rekordokat. • Töröljük mindkét táblát.
2
2. feladat Egy futóverseny résztvevőit tartalmazó adatbázist kell létrehoznunk. Tárolni kell a versenyzők nevét, címét, és életkorát. Minden versenyző rajtszámot kap a versenyre való jelentkezéskor. Az első rajtszám 1-gyes legyen, a következő szám pedig eggyel nagyobb. A feladat relációs modellje: VERSENYZŐ(Rajtszám, Név, Cím, Életkor) Hozzuk létre a táblát az alábbiak szerint: VERSENYZO • rajtszam: numerikus egész, 3 számjegy hosszú, elsődleges kulcs • nev: karakteres, változó hossz, max 30 karakter, kötelező kitölteni • cim: karakteres, változó hossz, max 30 karakter • eletkor: numerikus egész, 2 számjegy hosszú, értéke 0 és 99 közé eshet. Vegyük fel a következő rekordokat: A rajtszámot egy szekvencia segítségével generáljuk. A szekvencia kezdőértéke 1, növelés mértéke 1, maximális érték nincs. rajtszam 1 2 3 4
nev Vincs Eszter Henger Elek Glett Elek Bud Spencer
cim Budapest HDD u. 10. Győr Fő u. 32. Tatabánya Turul u. 23. Valahol Európában
eletkor 15 39 22 70
Végezzük el a következő feladatokat: • Henger Elek betegség miatt visszamondta szereplését, emiatt töröljük az adatbázisból. • Vegyünk fel egy új rekordot tetszőleges adatokkal. • Figyeljük meg az új rekord sorszámát, a törölt rekord sorszáma nem került újragenerálásra.
3
3. feladat Egy videotéka nyilvántartási rendszerét kell elkészíteni. Tárolni kell az ügyfelek személyes adatait (személyi igazolvány száma, név, cím, telefon), a kölcsönözhető filmek adatait (cím, hossz, készítés éve, kölcsönzési díj, műfaj, kazetta sorszáma). A kikölcsönzött kazettákat is nyilván kell tartani a kölcsönző adataival összepárosítva. A feladat relációs modellje: ÜGYFÉL(szigszám, név, cím, telefon) KÖLCSÖNZÉS(sorszám, szigszám, dátum) KAZETTA(sorszám, f_cím) FILM(cím, hossz, év, kölcs_díj, m_azon) MŰFAJ(azon, név) Hozzuk létre a táblákat az alábbiak szerint: UGYFEL • szigszam: karakteres, fix 8 hosszú, elsődleges kulcs • nev: karakteres, változó hossz, max 30 karakter, kötelező kitölteni • cim: karakteres, változó hossz, max 30 karakter, kötelező kitölteni • telefon: karakteres, fix 10 hosszú, kötelező kitölteni MUFAJ • azon: karakteres, fix 1 hosszú, elsődleges kulcs • nev: karakteres, változó hossz, max 20 karakter FILM • cim: karakteres, változó hossz, max 30 karakter, elsődleges kulcs • hossz: numerikus egész, 3 számjegy hosszú • ev: numerikus egész, 4 számjegy hosszú, értéke nagyobb 1900-nál • kolcs_dij: numerikus egész, 4 számjegy hosszú, nem lehet negatív • m_azon: karakteres fix 1 hosszú, idegen kulcs, a MUFAJ tábla azon mezőjére mutat. KAZETTA • sorszam: numerikus egész, 3 számjegy hosszú, elsődleges kulcs • f_cim: karakteres, változó hossz, max 30 karakter, idegen kulcs, a FILM tábla cim mezőjére mutat
4
KOLCSONZES • sorszam: numerikus egész, 3 számjegy hosszú, elsődleges kulcs, idegen kulcs, a KAZETTA tábla sorszam mezőjére mutat • szigszam: karakteres, fix 8 hosszú, idegen kulcs, az UGYFEL tábla szigszam mezőjére mutat • datum: dátum típus, alapértelmezett értéke a mai dátum (SYSDATE függvény az aktuális dátumot adja vissza)
Vegyük fel a következő rekordokat: UGYFEL tábla szigszam ad111111 ad222222 ad333333 ad444444 ad555555
nev Zúz Mara Kukor Ica Kup Ica Bármi Áron Lapos Elemér
cim Dunaújváros, Kossuth 2. Dunaújváros, Kinizsi 23. Dunaújváros, Jókai 11. Rácalmás, Fő 62. Baracs, Legelő 9.
telefon 25/111111 25/222222 25/333333 25/444444 25/555555
MUFAJ tábla azon nev k kaland a akció t thriller h horror r romantikus v vígjáték i ismeretterjesztő m mese s sci-fi
FILM tábla cim Terminátor I Terminátor II A dszungel fia Az élő bolygó 1-8 Aladdin Szeszmeralda
hossz 110 130 93 230 90 96
ev 1983 1991 1972 1987 1996 1999
kolcs_dij 200 300 100 300 200 400
m_azon s s k i m r
5
KAZETTA tábla sorszam f_cim 1 Terminátor I 2 Terminátor I 3 Terminátor II 4 Terminátor II 5 A dszungel fia 6 Az élő bolygó 1-8 7 Aladdin 8 Szeszmeralda 9 Szeszmeralda 10 Szeszmeralda
KOLCSONZES tábla sorszam szigszam 1 ad222222 4 ad222222 5 ad111111 7 ad111111 9 ad111111 6 ad555555
datum 2003-10-12 2003-10-12 2003-11-01 2003-11-01 2003-11-23 2003-12-11
Végezzük el a következő feladatokat: • Kukor Ica (ad222222) visszahozta a kikölcsönzött filmeket, töröljük a vele kapcsolatos bejegyzéseket a KOLCSONZES táblából. • Bármi Áron kiköttette vezetékes telefonját, mobilszáma: 20/1234567, módosítsuk a bejegyzést. • Az 1990-nél régebben készült sci-fik kölcsönzési díját csökkentsük le 100 forintra. • Hajtsunk végre egy általános 10%-os áremelést minden filmnél. • A 9-es sorszámú kazetta elveszett, töröljük az adatbázisból. • Kup Ica elvinné a „Szeszmeralda” című filmet, ha bent van a film valamelyik példánya, akkor jegyezzük be a kölcsönzés tényét. • Vegyünk fel egy új ügyfelet tetszőleges adatokkal és adjuk oda neki a „Terminátor II” szabad példányát. • Töröljük a táblákat.
6
