Andmebaasidskript Rainer Is-06

create table Klient_RainerPL( klient_id INTEGER not null primary key, isikukood VARCHAR2(15) not null, eesnimi VARCHAR2(40) not null, perenimi VARCHAR2(40) not null, aadress VARCHAR2(50), telefon VARCHAR2(40), synniaeg DATE not null, dok_nr VARCHAR2(40) not null, doktyyp_id INTEGER not null, dok_id INTEGER ); CREATE TABLE Laenutus_RainerPL( lanutus_id INTEGER not null primary key, klient_id INTEGER not null , film_id INTEGER not null , personal_id INTEGER not null , laenutuse_kp DATE , tagastus_kp DATE, tegelik_tagastus_kp DATE, maksumus DECIMAL not null , viivis DECIMAL, kommentaar VARCHAR2(100) ); CREATE TABLE Zanr_RainerPL( zanr_id INTEGER not null primary key, nimi VARCHAR2(40) not null ); create table Personal_RainerPL( personal_id INTEGER not null primary key, isikukood VARCHAR2(40) not null , eesnimi VARCHAR2(40) not null , perenimi VARCHAR2(40)not null , Algus_kp DATE, Lopp_kp DATE ) create table Filmizanr_RainerPL( fz_id INTEGER not null primary key, film_id INTEGER not null, zanr_id INTEGER not null )

create table Kandja_film_RainerPL( kand_id INTEGER not null primary key, film_id INTEGER not null, kandja_id INTEGER not null , aktiivne INTEGER not null, kommentaar DECIMAL ) create table Kandja_RainerPL( kand_id INTEGER not null primary key, Nimetus VARCHAR2(40) not null )

create table Film_RainerPl( film_id INTEGER not null primary key, nimi VARCHAR2(100), aasta INTEGER, kestvus INTEGER, riik VARCHAR2(40), kommentaar VARCHAR2(100) ) create table DokTyyp_RainerPL( dok_id INTEGER primary key, nimi VARCHAR(40) not null ) alter table Klient_rainerPl ADD Sugu varchar(1) create index F_Nimi_Idx_RainerPL on Film_RainerPL( (nimi) ) create index KL_PERENIMI_IDX_RainerPL on Klient_RainerPL( upper(perenimi) ) create index L_LKP_IDX_RainerPL on Laenutus_RainerPL( (LAENUTUSE_KP) ) Ülesanne 2 insert into Film_RainerPL values ( Film_Seq_RainerPL.nextval,'Hanibal',2001,125,'USA','Väga hea ' ) insert into Film_RainerPL

values ( Film_Seq_RainerPL.nextval,'Vosapets',2004,120,'Eesti','Vaadatav' ) insert into Film_RainerPL values ( Film_Seq_RainerPL.nextval,'Matrix',2001,160,'USA','Hea' ) insert into Film_RainerPL values ( Film_Seq_RainerPL.nextval,'Matrix Revolution',2002,160,'USA','Hea' ) insert into Film_RainerPL values ( Film_Seq_RainerPL.nextval,'Matrix Reloaded',2005,130,'USA','Keskmine' ) insert into Film_RainerPL values ( Film_Seq_RainerPL.nextval,'Suvi',1960,190,'Eesti','Väga hea' ) insert into Film_RainerPL values ( Film_Seq_RainerPL.nextval,'Kevad',1970,120,'Eesti','Väga hea' ) insert into Film_RainerPL values ( Film_Seq_RainerPL.nextval,'Suvi',1980,100,'Eesti','Hea' ) insert into Personal_RainerPL values ( PERSONAL_SEQ_RainerPL.nextval,'1879634','Kaivo','Kaev','01.05.2000','05.04.2002' ) insert into Personal_RainerPL values ( PERSONAL_SEQ_RainerPL.nextval,'2864334','Merit','Mesi','05.04.2002','02.09.2003'

) insert into Personal_RainerPL values ( PERSONAL_SEQ_RainerPL.nextval,'2844348','Maije','Valdur','25.11.2002','05.04.2004' ) insert into Personal_RainerPL values ( PERSONAL_SEQ_RainerPL.nextval,'1648348','Muki','Mats','15.1.2004','07.12.2004' ) insert into DokTyyp_RainerPL values dok_id,nimi ( 1,’ID kaart’ ) insert into DokTyyp_RainerPL values dok_id,nimi ( 2,’Pass’ ) insert into DokTyyp_RainerPL values dok_id,nimi ( 3,’Digitaal Allkiri’ ) insert into Klient_RainerPL ( klient_id,isikukood,eesnimi,perenimi,aadress,telefon,synniaeg,dok_nr,doktyyp_id,sugu) values ( KLIENT_SEQ_RainerPL.nextval,1751564,'Rainer','Plumer','Valga',56548462,'09.05.1987',2, 2,'M' ) insert into Klient_RainerPL ( klient_id,isikukood,eesnimi,perenimi,aadress,telefon,synniaeg,dok_nr,doktyyp_id,sugu) values ( KLIENT_SEQ_RainerPL.nextval,1751564,'Rivo','Nurk','Valga',56548456,'09.05.1985',2,2,'M' ) insert into Klient_RainerPL ( klient_id,isikukood,eesnimi,perenimi,aadress,telefon,synniaeg,dok_nr,doktyyp_id,sugu) values (

KLIENT_SEQ_RainerPL.nextval,271464,'Meelis','Mets','Valga',52544646,'10.12.1986',3,4,'M ' ) insert into Klient_RainerPL ( klient_id,isikukood,eesnimi,perenimi,aadress,telefon,synniaeg,dok_nr,doktyyp_id,Dok_id,sug u) values ( KLIENT_SEQ_RainerPL.nextval,1751564,'Juhan','Juurikas','Tartu',56464462,'09.05.1987',2,2 ,2,'M' ) insert into Klient_RainerPL ( klient_id,isikukood,eesnimi,perenimi,aadress,telefon,synniaeg,dok_nr,doktyyp_id,Dok_id,sug u) values ( KLIENT_SEQ_RainerPL.nextval,1551461,'Taivo','Tammela','Võru',55413469,'03.10.1985',4, 5,3,'M' ) insert into Klient_RainerPL ( klient_id,isikukood,eesnimi,perenimi,aadress,telefon,synniaeg,dok_nr,doktyyp_id,Dok_id,sug u) values ( KLIENT_SEQ_RainerPL.nextval,2551971,'Eva','Emerald','Vjaimela',64413421,'01.01.1955', 4,4,1,'N' ) insert into Klient_RainerPL ( klient_id,isikukood,eesnimi,perenimi,aadress,telefon,synniaeg,dok_nr,doktyyp_id,Dok_id,sug u) values ( KLIENT_SEQ_RainerPL.nextval,2551971,'Maiju','Mustikas','Tallinn',61325420,'02.02.1998', 4,2,1,'N' ) insert into Klient_RainerPL ( klient_id,isikukood,eesnimi,perenimi,aadress,telefon,synniaeg,dok_nr,doktyyp_id,Dok_id,sug u) values ( KLIENT_SEQ_RainerPL.nextval,1351976,'Joonas','Joodik','Kopli',56325358,'12.12.1908',4,2 ,1,'M' ) insert into Klient_RainerPL ( klient_id,isikukood,eesnimi,perenimi,aadress,telefon,synniaeg,dok_nr,doktyyp_id,Dok_id,sug u) values

( KLIENT_SEQ_RainerPL.nextval,1351976,'Kristiina','Kaevur','Kohila',5442788,'16.01.1908', 3,2,1,'N' ) insert into Klient_RainerPL ( klient_id,isikukood,eesnimi,perenimi,aadress,telefon,synniaeg,dok_nr,doktyyp_id,Dok_id,sug u) values ( KLIENT_SEQ_RainerPL.nextval,164971,'Vahur','Vilgas','Tartu',5212268,'10.05.2002',4,5,3,' M' ) insert into Klient_RainerPL ( klient_id,isikukood,eesnimi,perenimi,aadress,telefon,synniaeg,dok_nr,doktyyp_id,Dok_id,sug u) values ( KLIENT_SEQ_RainerPL.nextval,19121,'Kalev','Kalju','Valga',5622287,'21.07.1982',7,3,3,'M' ) /*4 filmi lisasin veel*/ insert into Zanr_RainerPL values ( 1,'Triller' ) insert into Zanr_RainerPL values ( 2,'Põnevik' ) insert into Zanr_RainerPL values ( 3,'Õudus' ) insert into Zanr_RainerPL values ( 4,'Komöödia' ) insert into Zanr_RainerPL values ( 5,'Seebikas'

) insert into FilmiZanr_RainerPL values ( 1,1,3 ) insert into FilmiZanr_RainerPL values ( 2,27,4 ) insert into FilmiZanr_RainerPL values ( 3,4,3 ) insert into FilmiZanr_RainerPL values ( 4,2,3 ) insert into FilmiZanr_RainerPL values ( 5,22,2 ) insert into Kandja_RainerPL values ( 1,'DVD' ) insert into Kandja_RainerPL values ( 2,'CD' ) insert into Kandja_Film_RainerPL values ( 1,26,2,0,NULL ) insert into Kandja_Film_RainerPL

values ( 2,27,1,1,NULL ) insert into Kandja_Film_RainerPL values ( 3,2,3,0,NULL ) insert into Kandja_Film_RainerPL values ( 4,5,1,1,NULL ) insert into Kandja_Film_RainerPL values ( 5,4,2,1,NULL ) insert into Kandja_Film_RainerPL values ( 6,0,1,1,NULL )

alter table Laenutus_rainerPL add constraint klient_id_fk foreign key (klient_id) references Klient_rainerpl(klient_id) alter table Laenutus_rainerPL add constraint personal_id_fk foreign key (personal_id) references Personal_rainerpl(personal_id) alter table Kandja_Film_rainerPL add constraint Film_id_fk foreign key (film_id) references Film_rainerpl(film_id) alter table Kandja_Film_rainerPL add constraint Kandja_id_fk foreign key (Kandja_id) references Kandja_rainerpl(Kand_id) alter table Filmizanr_rainerPL add constraint films_id_fk foreign key (film_id) references Film_rainerpl(film_id) alter table Filmizanr_rainerPL add constraint zanr_id_fk foreign key (zanr_id) references Zanr_rainerpl(zanr_id)

select film_id,nimi,aasta from Film_RainerPL where film_id = 1 order by nimi desc alter table Kandja_film_RainerPL modify aktiivne constraint cons_piirang check(aktiivne =0 or aktiivne = 1) CREATE SEQUENCE Laenutus_SeQ_RainerPL INCREMENT BY 1 Nominvalue nomaxvalue START WITH 1 CREATE SEQUENCE tabeli_nimi INCREMENT BY 10 START WITH 0 MIN VALUE 0; foreign key DokTyyp(doc_id) Ülesanne 3 select eesnimi,perenimi from klient_rainerpl order by perenimi select Distinct eesnimi,perenimi from Klient_rainerpl order by perenimi desc select eesnimi ||' '||perenimi as Nimed from Klient_rainerpl select * from Klient_rainerpl where isikukood = NULL select lanutus_id, klient_id, film_id from Laenutus_RainerPL where tagastus_kp = NULL order by klient_id asc,laenutuse_kp desc select * from Klient_RainerPL where klient_id = 1 or klient_id = 2 select *

from Klient_RainerPL where eesnimi like 'M%' and perenimi like '%l' select * from Laenutus_RainerPL where (SysDate-laenutuse_kp) > 30 select * from Kandja_Film_RainerPL where aktiivne = 1 select * from Klient_RainerPL where aadress = 'Valga' /*Väljastab kõik valga laenutajad*/

Select last_name,nvl(overtime_rate,5) From f_staff Select name Nvl2(end_date,’End in 2 Weeks’,Sys_date) From menus Select lastname,dep_id, CASE When dep_id = 10 then 1.25*salary When dep_id = 90 then 1.5*salary When dep_id =130 then 1.75*salary Else salary END palk From employes Select id,title,duration, Decode(Duration,’2 min’,’Shortest’, ’10 min’,’Longest’,null) AS ’Playtime’ From Songs ÜL 4: select abs(-32) from Dual --32 select ceil(125.23) from Dual126 select floor(125.23) from Dual125 select MOD(5,3) from Dual2 select Power(5,3) from Dual125 select round(2.15493,3) from Dual2.155 select sqrt(16) from Dual4 select trunc(46.597321) from Dual46

select concat('Hello ','World') from DualHello World select concat(eesnimi,perenimi) from Klient_rainerpl /*Ees- ja perenimed ühes lahtris*/ select lower(perenimi) from Klient_rainerpl /*perenimed väikeste tähtedega*/ select lpad(perenimi,2,' ') from Klient_rainerpl /*Kõik perenimede Esimesed kaks tähte*/ select ltrim(perenimi,perenimi) from Klient_rainerplkustutab eesnime eest ära perenime select rtrim(perenimi,'er') from Klient_rainerplkustutab perenime seest ’ER’ tähed select replace(perenimi,'er','Hello') from Klient_rainerplperenimes leiduv ’er’ asendatakse sõnaga ’Hello’ nt PlumerPlumHello select substr(perenimi,3,5) from Klient_rainerplkuvab perenimedest 5 tähte,alates kolmandast. Nt Plumer umer select upper(eesnimi) from Klient_rainerplkõik eesnimed suurte tähtedega. select instr('Plumer','m') from Klient_rainerpl4 select length('Plumer') from Klient_rainerpl6 select add_months('01.05.2007',5) from dualADD_MONTHS 01.10.2007 select months_between('06.11,2007','01.05.2007') from dual6.16129032 select Round(sysdate) from dual22.11.2007 select trunc(sysDate,'mm') from dual01.11.2007 select TO_CHAR (laenutuse_kp,'yyyy') from Laenutus_rainerpl kuvab kuupäevast ainult aasta select TO_CHAR (laenutuse_kp,'mm') from Laenutus_rainerpl kuvab kuupäevast ainult kuu select TO_CHAR (laenutuse_kp,'dd') from Laenutus_rainerpl kuvab kuupäevast ainult päevad select Klient_rainerpl.isikukood, case when substr(isikukood,1,1) in ('3','5') then 'M' when substr(isikukood,1,1) in ('4','6') then 'N' else 'U' end Sugu from Klient_rainerpl select isikukood, decode(isikukood,'10000','v','12530','M') as 'Isikukood' from Klient_rainerpl select NVL(eesnimi,'nimi puudub') from Klient_rainerpl kirjutab tabelitesse kus eesnimi puudub teksti ’nimi puudub’ select NVL2(eesnimi,'Nimi olemas','nimi puudub') from Klient_rainerpl kirjutab tabelitesse kuhu väärtused ON sisestatud teksti ’nimi olemas’, ja tühjadesse lahtritesse teksti ’Nimi puudub’

alter table Klient_rainerpl add constraint sugu_cs check (sugu = 'M' or sugu = 'N' or sugu = 'U') select Klient_rainerpl.isikukood, case when substr(isikukood,1,1) in ('3','5') then 'M' when substr(isikukood,1,1) in ('4','6') then 'N' else 'U' end Sugu from Klient_rainerpl SELECT klient_rainerpl.eesnimi, klient_rainerpl.perenimi, film_rainerpl.nimi, Laenutus_rainerpl.laenutuse_kp, Laenutus_rainerpl.tagastus_kp, NVL2(l.tegelik_tagastus_kp, TO_CHAR(l.tegelik_tagastus_kp), 'Tagastamata') FROM Laenutus_rainerpl l LEFT JOIN Klient_rainerpl k ON klient_rainerpl.klient_id = laenutus.klient_id LEFT JOIN Film_rainerpl f ON film_rainerpl.film_id = laenutus_.film_id SELECT SUBSTR(isikukood, 2, 2) Aasta, SUBSTR(isikukood, 4, 2) Kuu, SUBSTR(isikukood,6, 2) Päev FROM Klient_rainerpl select klient_id, NVL2(isikukood,'Tuvastatud','Tuvastamata') from Klient_rainerpl select k.eesnimi,k.perenimi,d.nimi,d.dok_id,k.dok_id from klient_rainerpl k,doktyyp_rainerpl d where d.dok_id = k.dok_id and d.nimi = 'ID Kaart' select k.eesnimi,k.perenimi,l.laenutuse_kp,f.nimi from klient_rainerpl k,Laenutus_rainerpl l,film_rainerpl f where k.klient_id = l.klient_id

select k.eesnimi,k.perenimi,l.laenutuse_kp,f.nimi from klient_rainerpl k,Laenutus_rainerpl l,film_rainerpl f where k.klient_id = l.klient_id and l.tagastus_kp = NULL