Baza de date creata vanzarea biletelor la diferite concerte prin agentiile de vanzare de bilete. Are in componenta trei tabele (concert,formatie,agentie,comenzi si client).

Tabelele concert si formatie sunt legate intre ele prin codconcert,tipul de legatura dintre ele fiind 1 la la m ; tabelele concert si agentie sunt legate prin codconcert, legatura fiinf m la m ; tabelele agentie si comenzi au o legatura d tip m la m ,fiinf legate prin codcomanda, iar tabelele clienti si comenzi sunt legate prin id_client si prezinta o legatura de tip 1 la m.

FORMATIE

codformatie numeformatie nr_artisti tarif gen_muzical Codconcert       

CONCERT  

codconcert locatie data_concert nr_bilete durata pret 

AGENTIE  

codagentie nr_bilete codconcert codcomanda   

COMENZI  

codcomanda cantitate id_client pret 

CLIENTI  

id_client nume_client prenume_client

DROP TABLE concert CASCADE CONSTRAINTS;DROP TABLE formatie CASCADE CONSTRAINTS;DROP TABLE agentie CASCADE CONSTRAINTS;DROP TABLE comenzi CASCADE CONSTRAINTS;DROP TABLE client CASCADE CONSTRAINTS;

create table concert(codconcert number(3) constraint PKey_concert primary key,locatie varchar2(20) not null,data_concert date,nr_bilete number(10),durata number(5),pret number(10));

create table formatie(codformatie number(3),numeformatie varchar2(25) not null,

1

nr_artisti number(2),tarif number(10),codconcert number(3) not null,gen_muzical varchar2(10));alter table formatie add constraint pk_agnt primary key(codformatie);

create table agentie(codagentie number(4) constraint pk_comenzi primary key,codconcert number(3) not null,codcomanda number(3) not null,nr_bilete number(10),CONSTRAINT FK_concert FOREIGN KEY (codconcert) REFERENCES concert(codconcert));

create table comenzi(codcomanda number(3) not null,codclient number(3) not null,cod_concert number(3) not null,cantitate number(4),nrcom number(6));

Create table client (id_client number(6) not null,nume_client varchar2(20),prenume_client varchar2(20),nrcom number(4) not null);

alter table comenzi add constraint pk_codcomanda primary key (codcomanda);alter table client add constraint pk_idclient primary key (id_client);

alter table agentie modify (codcomanda number(3));alter table agentie add constraint FK_comenzi FOREIGN KEY (codcomanda) REFERENCES comenzi(codcomanda);

alter table formatie add CONSTRAINT FK_concrt FOREIGN KEY (codconcert) REFERENCES concert(codconcert);

alter table comenzi add id_client number(6);alter table comenzi add constraint fk_idclient foreign key(id_client) references client(id_client);alter table comenzi add constraint ck_cant check (cantitate>0);

alter table client add varsta number(3);alter table client set unused column varsta;alter table client drop unused column;

2

insert into concert values (100,'sala palatului',to_date('11-08-2004','DD-MM-YYYY'),2000,'2h',45000);insert into concert values (101,'parcul izvor',to_date('12-06-2005','DD-MM-YYYY'),500,'3h',0);insert into concert values (110,'sala parlamentului',to_date('05-12-2004','DD-MM-YYYY'),2500,'1h30',500000);insert into concert values (120,'palatului copiilor',to_date('20-01-2006','DD-MM-YYYY'),1000,'1h',60000);insert into concert values (130,'parcul izvor',to_date('14-08-2007','DD-MM-YYYY'),500,'30min',10000);insert into concert values (140,'sala palatului',to_date('17-05-2008','DD-MM-YYYY'),3000,'2h',500);insert into concert values (150,'palatul copiilor',to_date('25-12-2006','DD-MM-YYYY'),100,'1h',0);insert into concert values (160,'sala parlamentului',to_date('03-06-2005','DD-MM-YYYY'),1800,'4h',150000);insert into concert values (200,'parcul izvor',to_date('11-11-2004','DD-MM-YYYY'),1000,'40min',1000);insert into concert values (210,'sala palatului',to_date('20-04-2007','DD-MM-YYYY'),2600,'2h20',250000);insert into concert values (250,'sala parlamentului',to_date('01-02-2005','DD-MM-YYYY'),1550,'1h40',75000);insert into concert values (300,'parcul izvor',to_date('16-04-2007','DD-MM-YYYY'),1200,'3h',0);

insert into formatie values (01,'compact',5,10000,100,'rock');insert into formatie values (02,'andreea balan',1,30000,101,'pop');insert into formatie values (03,'pink',1,100000,110,'');insert into formatie values (04,'blondy',2,20000 ,120,'pop');insert into formatie values (05,'vita de vie',3,25000,140,'rock');insert into formatie values (10,'sistem',6,50500,150,' ');insert into formatie values (11,'mandinga',11,20000,160,'latino');insert into formatie values (15,'elena',1,2000 ,200,'pop');insert into formatie values (20,'blondy', 2,0 , 250,' ');

insert into client values (001005,'matei','simona');insert into client values (001010,'stefan','maria');

3

insert into client values (001015,'marin','florin');insert into client values (001020,'stoica','ana');insert into client values (001001,'vasile','ion');insert into client values (001002,'constantinescu','maria');insert into client values (001003,'baciu','florin');insert into client values (001004,'nastase','raluca');

insert into comenzi values (300,5,001001,30000);insert into comenzi values (310,2,001005,10000);insert into comenzi values (320,10,001010,23000);insert into comenzi values (330,1,001002,5000);insert into comenzi values (340,3,001003,90000);insert into comenzi values (350,6,001015,40000);insert into comenzi values (360,12,001020,100000);insert into comenzi values (370,9,001004,55000);

insert into agentie values (1000,100,300,2000);insert into agentie values (1100,101,350,500);insert into agentie values (1200,120,360,1000);insert into agentie values (1300,130,370,500);insert into agentie values (1400,140,330,3000);insert into agentie values (1500,160,340,1800);insert into agentie values (1600,210,310,2600);

4

5

6

1. . Folosind un cursor sa se afiseze formatiile si totalul biletelor vandute de formatie.

declare cursor c1 is select numeformatie, sum(nr_bilete) total from formatie f, concert c where f.codconcert=c.codconcertgroup by numeformatie;beginfor rec_formatie in c2 loopdbms_output.put_line(rec_formatie.numeformatie || ' a vandut ' ||rec_formatie.total || ' bilete');end loop;end;

rezultat:mandinga a vandut 1800 biletecompact a vandut 2000 bileteblondy a vandut 2550 bileteandreea balan a vandut 500 bileteelena a vandut 1000 biletepink a vandut 2500 biletevita de vie a vandut 3000 biletesistem a vandut 100 bilete

2. Sa se afiseze ultimele 5 concerte care au avut loc, folosind un cursor.

declare v_data concert.data_concert%type; v_nume concert.locatie%type;cursor c1 is select data_concert,locatie from concert order by data_concert desc ;beginopen c1;for i in 1..5loopfetch c1 into v_data,v_nume;exit when c1%notfound;dbms_output.put_line(v_data || ‘ ‘ ||v_nume);end loop;close c1;end;

rezultat:17-MAY-08 sala palatului14-AUG-07 parcul izvor20-APR-07 sala palatului16-APR-07 parcul izvor25-DEC-06 palatul copiilor

7

3. Sa se afiseze preturile biletelor crescator ,locatia si formatia care canta.

beginfor rec_pret in (select pret_bilete,locatie,numeformatie from concert c,formatie f where c.codconcert=f.codconcert order by 1)loopdbms_output.put_line(rec_pret.pret_bilete || ' lei,la ' ||rec_pret.locatie ||' canta ' ||rec_pret.numeformatie);end loop;end;

rezultat:0 lei,la palatul copiilor canta sistem0 lei,la parcul izvor canta andreea balan500 lei,la sala palatului canta vita de vie1000 lei,la parcul izvor canta elena45000 lei,la sala palatului canta compact60000 lei,la palatului copiilor canta blondy75000 lei,la sala parlamentului canta blondy150000 lei,la sala parlamentului canta mandinga500000 lei,la sala parlamentului canta pink

4. Sa se afiseze genul musical si formatia care canta, in functie de locatia introdusa de la tastatura.

declare cursor c1 (loc varchar2) is select locatie,gen_muzical,numeformatie from concert c, formatie f where c.codconcert=f.codconcert and locatie=loc;rec_ang c1%rowtype;v_loc varchar2(30);beginv_loc:='sala palatului';open c1(v_loc);loopfetch c1 into rec_ang;exit when c1%notfound;dbms_output.put_line(rec_ang.locatie || ' ' ||rec_ang.gen_muzical || ' ' ||rec_ang.numeformatie);end loop;close c1;end;

8

rezultat:sala palatului rock compactsala palatului rock vita de vie

parcul izvor pop andreea balanparcul izvor pop Elena

sala parlamentului pinksala parlamentului latino mandingasala parlamentului blondy

5. Sa se afiseze numele si prenumele clientilor care au dat o comanda mai mare de 5 bilete.

beginfor rec_client in (select nume_client,prenume_client from client where id_client in (select id_client from comenzi where cantitate>5))loopdbms_output.put_line ( rec_client.nume_client || ' ' || rec_client.prenume_client );end loop;end;

rezultat:nastase ralucastefan mariamarin florinstoica ana

6. Pentru concertele dintr-o locatie data de la tastatura, sa se mareasca numarul biletelor cu 10%.

create or replace procedure nr_bilete (loc in concert.locatie%type)as beginupdate concert set nr_bilete=nr_bilete*1.10 where locatie=loc;end nr_bilete;

declare v_loc varchar2(20);beginv_loc:='%parc%';nr_bilete(v_loc);end; 7. Sa se afiseze numele si prenumele unui client care a dat o comanda si a se trateze exceptiile care pot aparea.

9

declare exc exception;v_nume client.nume_client%type;v_pren client.prenume_client%type;beginselect nume_client,prenume_client into v_nume,v_pren from client where id_client in (select id_client from comenzi where codcomanda=400);dbms_output.put_line (v_nume || ' ' || v_pren);exception when no_data_found then dbms_output.put_line ('Nu exista acest client');end;

vasile ion

8. Pentru un an dat de la tastatura,sa se afiseze detalii despre concertele din acel an.

declarecursor c1(an number) is select locatie,nr_bilete,durata from concert where extract (year from data_concert) =an;rec_an c1%rowtype;v_an number;beginv_an:=2004;open c1(v_an);loopfetch c1 into rec_an;exit when c1%notfound;dbms_output.put_line('In anul ' || v_an || ' la ' || rec_an.locatie || ' s-au vandut: ' || rec_an.nr_bilete || ' bilete si concertul a durat: ' || rec_an.durata);end loop;close c1;end;

rezultat:In anul 2004 la sala palatului s-au vandut: 2000 bilete si concertul a durat: 2hIn anul 2004 la sala parlamentului s-au vandut: 2500 bilete si concertul a durat: 1h30In anul 2004 la parcul izvor s-au vandut: 1100 bilete si concertul a durat: 40min

In anul 2007 la parcul izvor s-au vandut: 550 bilete si concertul a durat: 30minIn anul 2007 la sala palatului s-au vandut: 2600 bilete si concertul a durat: 2h20In anul 2007 la parcul izvor s-au vandut: 1320 bilete si concertul a durat: 3h

9. Sa se stabileasca tariful formatiilor,in functie de numarul de artisti din care sunt formate.

10

create or replace function tarife (nr_art number) return numberis v_tarif formatie.tarif%type;beginselect tarif into v_tarif from formatie;v_tarif := case when nr_art =1 then 1000when nr_art=2 then 2000when nr_art=3 then 3000else 4000 end;return v_tarif;end tarife;

10.Sa se creeze o functie care pe baza codului de comanda,sa afiseze numarul de bilete comandate.

create or replace function comanda(id comenzi.codcomanda%type)return numberis v_cant comenzi.cantitate%type;beginselect cantitate into v_cant from comenzi where codcomanda=id;return v_cant;exception when no_data_found then return null;end;

declarev_id comenzi.codcomanda%type;v_com number;beginv_id:='320';dbms_output.put_line (' acest client a comandat: ');v_com:=comanda(v_id); dbms_output.put_line (v_com || ' bilete' );end;

rezultat: acest client a comandat: 10 bilete

11.Se da un nume de la tastatura.Sa se afle daca exista vreun client cu acest nume. create or replace function cauta (nume client.nume_client%type) return booleanis v_nume client.nume_client%type;begin

11

select prenume_client into v_nume from client where nume_client=nume;if v_nume=nume then return true;else return false;end if;exceptionwhen no_data_found then return null;end;

declarev_nume client.nume_client%type;beginv_nume:='matei';if cauta(v_nume) is null then dbms_output.put_line ('nu exista comenzi pe acest nume');elsif cauta(v_nume) then dbms_output.put_line ('exista acest client');else dbms_output.put_line ('nu exista');end if;end;

rezultat: exista acest client

12. Sa se creeze o procedura care pe baza codului de comanda,afiseaza numarul de bilete si pretul acestora.

create or replace procedure cant ( codc in comenzi.codcomanda%type,v_cantitate out comenzi.cantitate%type,v_pret out comenzi.pret%type)isbeginselect cantitate,pret into v_cantitate,v_pret from comenzi where codcomanda=codc;end ;

declarenr comenzi.codcomanda%type;v_cantitate comenzi.CANTITATE%type;v_pret comenzi.PRET%type;beginnr:=300;cant(nr,v_cantitate,v_pret);dbms_output.put_line ('cantitatea de: '|| v_cantitate|| ' bilete si pretul de : ' || v_pret || ' lei');end;

rezultat:

cantitatea de: 5 bilete si pretul de : 30000 lei

12

13. Sa se realizeze o procedura care sa afiseze comenzile pentru un an dat.

create or replace procedure com_an (an in number, codc out comenzi.codcomanda%type, cant out comenzi.cantitate%type,pret out comenzi.pret%type)asbeginselect codcomanda,cantitate,pret into codc,cant,pret from comenzi where codcomanda in (select codcomanda from agentie where codconcert in (select codconcert from concert where extract(year from data_concert)=an));end;

declarean number;v_codc comenzi.codcomanda%type;v_cant comenzi.CANTITATE%type;v_pret comenzi.PRET%type;beginan:=2008;com_an(an,v_codc,v_cant,v_pret);dbms_output.put_line ('Comanda are codul: '|| v_codc|| ' o cantitate de: '|| v_cant || ' bilete si un pret de: '|| v_pret || ' lei');end;

Comanda are codul: 330 o cantitate de: 1 bilete si un pret de: 5000 lei

An:=2005Sunt mai multe comenzi in acel an

14. Sa se creeze un declansator pentru tabela comenzi,atunci cand se adauga o noua inregistrare.

create or replace trigger com before insert on comenzibegindbms_output.put_line ('S-a adaugat o comanda');end;

insert into comenzi values (380,7,001005,35000);rezultat:S-a adaugat o comanda

15.Sa se creeze un declansator care sa nu permita comenzi mai mici de 2 bilete.

13

CREATE OR REPLACE TRIGGER cantBEFORE INSERT or UPDATE on comenzi FOR EACH ROW BEGIN IF :new.cantitate<2 then RAISE_APPLICATION_ERROR (-20202, 'Pentru o comanda e nevoie de cel putin 2 bilete'); end if;END;/update comenziset cantitate=1where codcomanda=310;

16. Sa se afiseze detalii despre declansatoul creat anterior.

select trigger_type,table_name,triggering_event from user_triggerswhere trigger_name='cant';

17.Sa se creeze un declansator care sa impuna data concertului mai mare de 20 de minute. create or replace TRIGGER concertBEFORE INSERT or UPDATE on concertFOR EACH ROW BEGIN IF :new.durata<20 then RAISE_APPLICATION_ERROR (-20000, 'Un concert dureaza mai mult decat durata introdusa'); end if;END;

update concertset durata=15where codconcert=100;

18. Sa se creeze un declansator care sa actualizeze codul clientului in tabela comenzi atunci cand el este modificat in tabela clienti.

CREATE OR REPLACE TRIGGER inlocBEFORE UPDATE OF id_client ON clientFOR EACH ROWBEGINupdate comenziset id_client = :id_clientwhere id_client = :old.id_client;

14

END;update client set id_client=001000 where id_client=001005;

19.Sa se impuna o restrictie a tarifului,in cazul in care acesta este prea mare.

declare exc exception;v_tarif formatie.tarif%type;cursor c1(nume varchar2) is select tarif from formatie where numeformatie=nume;v_nume varchar2(30);beginv_nume:='compact';open c1(v_nume);loopfetch c1 into v_tarif;exit when c1%notfound;if v_tarif>50000 then raise exc;elsif v_tarif in (20000,50000) then dbms_output.put_line('tariful cerut este acceptabil');else dbms_output.put_line ('Tariful cerut este bun');end if;end loop;exception when exc then dbms_output.put_line('Tariful cerut este prea mare');end;Tariful cerut este bun

V_nume=’sistem’:Tariful cerut este prea mare

20.Sa se creeze un pachet care sa actualizeze tabela comenzi. create or replaceTRIGGER lunaBEFORE INSERT or UPDATE on concertFOR EACH ROW declarev_dur varchar2(50);BEGINselect to_char(data_concert, 'yyyymm')into v_dur from concert where codconcert=100;if v_dur='200408' then RAISE_APPLICATION_ERROR (-21010, 'Nu se pot tine concerte in aceasta luna'); end if;END;

update concert set data_concert=to_date('16-08-2005','DD-MM-YYYY') where codconcert=101;

15

22. create or replace PACKAGE actualizare_comanda ISprocedure adauga_comanda(p_codc comenzi.codcomanda%type,p_cant comenzi.cantitate%type,p_id_client comenzi.id_client%type,p_pret comenzi.pret%type);

procedure modifica_comanda(p_codc comenzi.codcomanda%type,p_cant comenzi.cantitate%type,p_d_client comenzi.id_client%type,p_pret comenzi.pret%type);

procedure modifica_comanda(p_codc comenzi.codcomanda%type,p_cant comenzi.cantitate%type);

procedure sterge_comanda(p_codc comenzi.CODCOMANDA%type);

function exista_cod(p_codc comenzi.codcomanda%type)return boolean;

exceptie exception;

END;

CREATE OR REPLACE PACKAGE BODY actualizare_comanda ISprocedure adauga_comanda(p_codc comenzi.codcomanda%type,p_cant comenzi.cantitate%type,p_id_client comenzi.id_client%type,p_pret comenzi.pret%type)isbeginif exista_cod(p_codc) thenraise exceptie;elseinsert into comenzi values (p_codc, p_cant, p_id_client, p_pret);end if;exception

16

when exceptie then dbms_output.put_line('comanda existenta!');end;

procedure modifica_comanda(p_codc comenzi.codcomanda%type,p_cant comenzi.cantitate%type,p_id_client comenzi.id_client%type,p_pret comenzi.pret%type)isbeginif exista_cod(p_codc) thenupdate comenziset cantitate=p_cant,id_client=p_id_client , pret=p_pretwhere codcomanda=p_codc;elseraise exceptie;end if;exceptionwhen exceptie then dbms_output.put_line('comanda cu aceast cod nu exista!');end;

procedure modifica_comanda(p_codc comenzi.codcomanda%type,p_cant comenzi.cantitate%type)isbeginif exista_cod(p_codc) thenupdate comenziset cantitate=p_cantwhere codcomanda=p_codc;elseraise exceptie;end if;exceptionwhen exceptie then dbms_output.put_line('Comanda cu aceast cod nu exista!');end;

procedure sterge_comanda(p_codc comenzi.codcomanda%type)isbegin

17

if exista_cod(p_codc) thendelete from comenziwhere codcomanda=p_codc;dbms_output.put_line('Comanda cu codul '||p_codc||' a fost stearsa!');elseraise exceptie;end if;exceptionwhen exceptie thendbms_output.put_line('comanda cu aceast cod nu exista!');end;

function exista_cod(p_codc comenzi.codcomanda%type)return booleanisv_unu number;beginselect 1 into v_unufrom comenziwhere codcomanda=p_codc;return true;exceptionwhen no_data_found thenreturn false;end;

END;

execute actualizare_comanda.adauga_comanda(330,00105, 12, 2000);select * from comenzi;

execute actualizare_comanda.modifica_comanda(320,001010, 8, 20000);select * from comenzi;

execute actualizare_comanda.modifica_comanda(320, 5);select * from comenzi;

execute actualizare_comanda.sterge_comanda(301);select * from produse;

18