proiect baze de date sgbd baza de date

create table furnizori(

PROIECT BAZE DE DATE Bitca Teodor ValentinGrupa 1025, Seria A, An II

A Definirea schemei bazei de date tabele (minim 4) cu legturi i alte restrictii de integritate (CREATE TABLE), populate (INSERT).

create table furnizori(cod_furniz varchar2(5),

nume_furniz varchar2(20),

tara_furniz varchar2(12),

constraint furnizori_pk primary key(cod_furniz));

create table produse(cod_prod varchar2(5),

den_prod varchar2(35),cod_furniz varchar2(5),

pret_intrare number(12,4),

pret_iesire number(12,4),

stoc number(12,4),

constraint produse_pk primary key(cod_prod),

constraint produse_furnizori_fk foreign key(cod_furniz) references furnizori);

create table clienti(cod_client varchar2(5),

nume_client varchar2(30),

localit_client varchar2(15),

data_n date,

constraint clienti_pk primary key(cod_client));

create table comenzi(cod_comanda varchar2(5),

cod_client varchar2(5),

data_comanda date,

data_livrare date,

constraint comenzi_pk primary key(cod_comanda),

constraint comenzi_clienti_fk foreign key(cod_client) references clienti);

create table rindc(

cod_comanda varchar2(5),

cod_prod varchar2(5),

nr_prod number(3) not null,

constraint rindc_pk primary key(cod_comanda,cod_prod),

constraint rindc_comenzi_fk foreign key(cod_comanda) references comenzi,

constraint rindc_produse_fk foreign key(cod_prod) references produse);

FURNIZORI

COD_FURNIZNUME_FURNIZTARA_FURNIZ

PRODUSE

COD_PRODDEN_PRODCOD_FURNIZPRET_INTRAREPRET_IESIRESTOC

CLIENTI

COD_CLIENTNUME_CLIENTLOCALIT_CLIENTDATA_N

COMENZI

COD_COMANDACOD_CLIENTDATA_COMANDADATA_LIVRARE

RINDCCOD_COMANDACOD_PRODNR_PROD

1.SQL> insert into furnizori values('RIH','Riho','Olanda');

SQL> insert into furnizori values('GRO','Grohe','Germania');

SQL> insert into furnizori values('LAU','Laufen','Elvetia');

SQL> insert into furnizori values('EUROC','Euroceramica','Cehia');

SQL> insert into furnizori values('KLU','Kludi','Germania');

SQL> insert into furnizori values('JIK','Jika','Cehia');

COD_F NUME_FURNIZ TARA_FURNIZ

-----

-------------------- ---------------

RIH

Riho

Olanda

GRO Grohe Germania

LAU Laufen Elvetia

EUROC Euroceramica Cehia

KLU Kludi Germania

JIK

Jika Cehia

2.SQL> insert into produse values('001','Baterie dus','KLU','156','179,4','200');

SQL> insert into produse values('002','Baterie cada','GRO','130','149,5','150');

SQL> insert into produse values('003','Cuier','GRO','30','34,5','100');

SQL> insert into produse values('004','Policioara cristal','GRO','41','47,15','10');

SQL> insert into produse values('005','Policioara','LAU','35','39,15','70');

SQL>

insert into produse values('006','Suport prosop','JIK','37','42,25','90');

SQL> insert into produse values('007','Cabina dus','RIH','272','312,8','0');

SQL> insert into produse values('008','Cada hidromasaj', 'LAU', '789', '907,35','0');

SQL> insert into produse values('009','Picior chiuveta','KLU','120','138','40');

SQL> insert into produse values('010','Dulap mic simplu', 'EUROC', '100','115','55');

COD_P DEN_PROD COD_F PRET_INTRARE PRET_IESIRE STOC

----- -------------- ------- ---------------- -------------- ----------

001 Baterie dus KLU 156

179,4 200

002 Baterie cada GRO 130

149,5 150

003 Cuier GRO 30

34,5 100

004 Policioara cristal GRO 41

47,15 10

005 Policioara LAU 35

39,15 70

006 Suport prosop JIK 37

42,25 90

007 Cabina dus RIH 272

312,8 0

008 Cada hidromasaj LAU 789

907,35 0

009 Picior chiuveta KLU 120

138

40

010 Dulap mic simplu EUROC 100

115

553.SQL> insert into clienti values('1','Anghelmob SA','Iasi',to_date('11091959','ddmmyyyy'));

SQL> insert into clienti values('2','Ilia SA','Brasov',to_date('20051967','ddmmyyyy'));

SQL> insert into clienti values('3','Heraa SA','Bucuresti',to_date('07091977','ddmmyyyy'));

SQL> insert into clienti values('4','Romdor SA','Focsani',to_date('19121964','ddmmyyyy'));

SQL> insert into clienti values ('5','Gavan Constantin','Iasi',to_date('10021958', 'ddmmyyyy'));

SQL> insert into clienti values('6','Toader Adelina','Bucuresti',to_date('17041978', 'ddmmyyyy'));

SQL> insert into clienti values('7','Badea Bogdan','Calarasi',to_date('25061966', 'ddmmyyyy'));

SQL> insert into clienti values('8','Radulescu Maria','Bucuresti',to_date('25081974', 'ddmmyyyy'));

COD_C NUME_CLIENT LOCALIT_CLIENT

DATA_N

----- ----------------------- ---------------------- -------------------1

Anghelmob SA Iasi 11-09-1959

2

Ilia SA

Brasov 20-05-1967

3

Heraa SA Bucuresti 07-09-1977

4

Romdor SA Focsani 19-12-1964

5

Gavan Constantin Iasi

10-02-1958

6

Toader Adelina Bucuresti

17-04-1978

7

Badea Bogdan Calarasi

25-06-1966

8

Radulescu Maria Bucuresti

25-08-19744. SQL> insert into comenzi values('1','1',to_date ('240406','ddmmyy'), to_date('020506', 'ddmmyy'));

SQL> insert into comenzi values('2','5',to_date('190506','ddmmyy'), to_date('280506', 'ddmmyy'));





COD_CO COD_C DATA_COMAN

DATA_LIVRA

--------- --------- ------------------ ----------------

1

1 24-04-2006

02-05-2006

2

5 19-05-2006

28-05-2006

3

6 19-05-2006

26-05-2006

4

3 21-07-2006

30-07-2006

5

8 15-10-2006

25-10-2006

6

6 17-11-2006

20-11-20065.SQL> insert into rindc values('1','003','7');

SQL> insert into rindc values('1','006','10');







COD_C COD_P NR_PROD

--------- --------- -------------

1

003 7

1

006 10

2

010 6

3

007 1

4

005 9

4

003 9

5

007 7

B 5 exemple variate cu ALTER TABLE, pe coloane i pe restricii.1. In tabela PRODUSE, sa se aloce pentru campul cod_prod doar 3 caractere, in loc de 5SQL>alter table produse modify(cod_prod varchar2(3));

2. Sa se adauge in tabela FURNIZORI coloana cont_bancarSQL>alter table furnizori add(cont_bancar varchar2(5));

3. Sa se stearga din tabela FURNIZORI coloana creata anterior(cont_bancar)SQL>alter table furnizori drop column cont_bacar;

4. Sa se dezactiveze cheia primara a tabelei RINDCSQL>alter table rindc disable constraint rindc_pk;

5. Sa se reactiveze cheia primara a tabelei RINDC

SQL>alter table rindc enable constraint rindc_pk;

C 5 exemple variate cu operaiile de actualizare a datelor (comenzile DML UPDATE, DELETE, [MERGE] pentru nregistrari). 1. Sa se stearga din tabela PRODUSE produsele furnizate de firma Kludi (cod KLU)alter table rindc disable constraint rindc_produse_fk;

delete from produse where cod_furniz=KLU;

[alter table rindc enable constraint rindc_produse_fk;]

2. Sa se stearga clientii din Iasi si Focsani

alter table comenzi disable constraint comenzi_clienti_fk;

delete from clienti where localit_client in(`Iasi`,`Focsani`);

[alter table comenzi enable constraint comenzi_clienti_fk;]

3. Sa se mareasca preturile de iesire ale tuturor produselor cu 2%

update produse set pret_iesire=pret_iesire*1.02;

4. Sa se maresca stocul cu cate 5 produse pentru produsele furnizate de Grohe (cod GRO)

update produse set stoc=stoc+5 where cod_furniz=GRO;

5. Sa se modifice localitatea clientului Toader Adelina in Iasiupdate clienti set localit_client='Iasi'

where nume_client='Toader Adelina';

D Minim 15 exemple cu interogri ct mai variate (SELECT):

Utilizarea operatorilor de comparatie

Utilizarea funciilor la nivel de rnd (upper, substr, decode, case, nvl, to_date, to_char)

Utilizarea functiilor de grup si conditii asupra acestora

GROUP BY, HAVING

Join-uri (INNER, OUTER)

Utilizarea operatorilor UNION, MINUS, INTERSECT

Subcereri (cereri imbricate)

[Cereri ierarhice]

1. Sa se afiseze datele de livrare si localitatile in care trebuie facute livrarile din lunile mai si iunie 2006 si sa se sorteze dupa localitatea din care se afla clientulSQL>

select data_livrare, localit_client from comenzi c, clienti cl

where c.cod_client=cl.cod_client

and c.data_comanda

between to_date('010506','ddmmyy') and to_date('300606','ddmmyy')

order by cl.localit_client;

DATA_LIVRA LOCALIT_CLIENT

-------------- ---------------

28-05-2006 Iasi

2. Sa se afiseze denumirea produselor si numele furnizorilor pentru produsele cu pretul de intrare mai mare decat 100

SQL> select den_prod, nume_furniz

from produse p, furnizori fwhere p.cod_furniz=f.cod_furniz

and p.pret_intrare>100;

DEN_PROD NUME_FURNIZ

----------------------------------- --------------------

Cabina dus Riho

Cada hidromasaj Laufen

3. Sa se afiseze numele si codurile clientilor din IasiSQL>

select nume_client, cod_clientfrom clienti where localit_client='Iasi';

NUME_CLIENT COD_C

------------------------------ --------

Toader Adelina 64. Sa se afiseze comenzile care contin 2 produse diferiteSQL>

select cod_comanda

from rindc

group by cod_comanda

having count(*)=2;

COD_COMANDA-----

1

45. Sa se calculeze valoarea comenzii 2 (pretul produsului al carui cod corespunde comenzii 2)SQL>select pret_iesire*nr_prod as "val comanda2"

from produse p, rindc rwhere p.cod_prod=r.cod_prod and r.cod_comanda=2;Val Comanda2----------------------------- 703,86. Sa se calculeze de cate ori a fost comandat fiecare produs

SQL> select cod_prod, count(*) from rindc group by cod_prod order by cod_prod;

COD_P COUNT(*)

--------- -----------

003 2

005 1

006 1

007 2

010 1

7. Sa se afiseze produsele care nu au fost comandate

SQL>

select den_prod from rindc r , produse p where r.cod_prod(+)=p.cod_prod and r.cod_prod is null;

DEN_PROD

-----------------------------------

Policioara cristal

Cada hidromasaj

8. Sa se afiseze clientii nascuti in aprilie si data lor de nastere

SQL>

select nume_client, data_n

from clienti where to_char(data_n,'mm')='04';

NUME_CLIENT DATA_N

------------------------------ ----------

Toader Adelina 17-04-1978

9. Sa se afiseze numarul de furnizori din fiecare tara

SQL> select distinct(tara_furniz),count(*) from furnizorigroup by tara_furniz;

TARA_FURNIZ COUNT(*)

------------

----------

Cehia 2

Germania 2

Olanda 1

Elvetia 1

10. Sa se selecteze toate produsele fabricate in Germania

SQL> select den_prodfrom produse

where cod_furniz

in (select cod_furniz from furnizori where tara_furniz='Germania');

DEN_PROD

-----------------------------------

Cuier

Policioara cristal

11. Pentru fiecare comanda, sa se afiseze numele clientului si localitatea in care trebuie livrata comanda

SQL> select cod_comanda, nume_client, localit_client

from clienti cl, comenzi c

where cl.cod_client=c.cod_client

order by cod_comanda;

COD_C NUME_CLIENT LOCALIT_CLIENT

----- ------------------------------ ---------------

3 Toader Adelina Bucuresti

4 Heraa SA Bucuresti

5 Radulescu Maria Bucuresti

6 Toader Adelina Bucuresti

12. Sa se afiseze produsele care au stocul 0

SQL> select cod_prod as "Cod produs, den_prod as Denumire from produse where stoc='0';

13. Sa se afiseze stocul tuturor produselor comandate

SQL >

select distinct(p.cod_prod), p.den_prod, p.stoc from produse p, rindc r

where r.cod_prod=p.cod_prod

order by cod_prod;

COD DEN_PROD STOC

--- -------------------------------- ----------

003 Cuier 100

005 Policioara 70

006 Suport prosop 90

007 Cabina dus 0

010 Dulap mic simplu 55

14. Sa se afiseze produsul cu cel mai mare pret de intrareSQL> select den_prod, pret_intrare from produse where pret_intrare=(select max(pret_intrare) from produse);

DEN_PROD PRET_INTRARE

----------------------------------- ------------

Cada hidromasaj 789

E 5 exemple cu gestiunea altor obiecte ale bazei de date: vederi (se pot realiza view-uri cu cerintele de la punctul D), partiii, indeci , sinonime, secvente. 1. Sa se realizeze o vedere care sa contina denumirea produselor, preturile lor de iesire si furnizorii pentru fiecare produs

SQL> create view v_produse as select den_prod,pret_iesire, nume_furniz

from produse p, furnizori fwhere p.cod_furniz=f.cod_furniz;DEN_PRODPRET_IESIRENUME_FURNIZ

Cuier35,19Grohe

Policioara cristal48,093Grohe

Policioara39,933Laufen

Suport prosop43,095Jika

Cabina dus319,056Riho

Cada hidromasaj925,497Laufen

Dulap mic simplu117,3Euroceramica

row(s) 1 - 7 of 7

2. Sa se creeze un index la tabela PRODUSE

SQL>create index i_produse

on produse (den_prod);

3. Sa se creeze un sinonim pentru tabela FURNIZORI

SQL> create synonym furnizori2 for furnizori;

4. Sa se stearga sinonimul tabelei furnizori

SQL> drop synonym firme;

proiect baze de date sgbd baza de date

Documents