DROP TABLE FIRME CASCADE CONSTRAINTS;
DROP TABLE AGENTI CASCADE CONSTRAINTS;
DROP TABLE COMENZI CASCADE CONSTRAINTS;
DROP TABLE RINDCOM CASCADE CONSTRAINTS;
DROP TABLE PRODUSE CASCADE CONSTRAINTS;

create table firme
(codfirma number(2) constraint PKey_firme primary key,
denfirma varchar2(20) not null,
loc varchar2(20),
contbanca varchar(15),
zona varchar2(15) CONSTRAINT FZONA_CK check (zona in ('MOLDOVA','ARDEAL','BANAT','MUNTENIA','DOBROGEA','TRANSILVANIA')));

create table agenti
(codagent varchar2(3) constraint pk_agent primary key,
numeagent varchar2(25) not null,
dataang date default sysdate,
datanast date,
zona varchar2(15) CONSTRAINT AGZONA_CK check (zona in('MOLDOVA','ARDEAL','BANAT','MUNTENIA','DOBROGEA', 'TRANSILVANIA')),
functia varchar2(20),
codsef varchar2(3));

create table comenzi
(nrcom number(4) constraint pk_comenzi primary key,
codfirma number(2) not null,
codagent varchar2(3) not null,
data date default sysdate,
CONSTRAINT FKAgent FOREIGN KEY (codagent) REFERENCES agenti(codagent),
CONSTRAINT FKFirme FOREIGN KEY (codfirma) REFERENCES firme(codfirma));

create table produse
(codprodus number(3) constraint pk_produse primary key,
denprodus varchar2(20) not null,
um varchar2(3),
stoc number(4));

create table rindcom
(nrcom number(4),
codprodus number(3) not null,
cant number(10),
pret number(8),
termenliv date,
CONSTRAINT FKComenzi FOREIGN KEY (nrcom) REFERENCES comenzi(nrcom),
CONSTRAINT FKProduse FOREIGN KEY (codprodus) REFERENCES produse(codprodus));
delete from rindcom;

delete from comenzi;

delete from firme;

delete from agenti;
delete from produse;

insert into firme values(10,'SC ALFA SRL','Cluj','bcr1000','TRANSILVANIA');
insert into firme values(20,'SC MEDIA SA','Bucuresti','brd1111','MUNTENIA');
insert into firme values(30,'SC SOFTY SRL','Ploiesti','bcr2222','MUNTENIA');
insert into firme values(40,'SC MEGA SRL','Iasi','brd3333','MOLDOVA');
insert into firme values(50,'SC STAR SA','Timisoara','bcr4444','BANAT');
insert into firme values(60,'SC Sas SA','Timisoara','bcr333','BANAT');

insert into agenti
values('1','Toma Alina',to_date('feb 3,04','mon dd,yy'),to_date('jan 23,44','mon dd,yy'),'BANAT','ECONOMIST','2');
insert into agenti
values('2','Rotaru Maria',to_date('apr 12,03','mon dd,yy'),to_date('feb 13,54','mon dd,yy'),'MOLDOVA','DIRECTOR','2');
insert into agenti
values('3','Popescu Ionel',to_date('may 30,04','mon dd,yy'),to_date('Sep 3,65','mon dd,yy'),'MUNTENIA','CONTABIL','1');


insert into produse
values(111,'napolitane','buc',1000);
insert into produse
values(222,'ciocolata','buc',4000);
insert into produse
values (333,'biscuiti','pac',3000);
insert into produse
values(444,'servetele','pac',1100);

insert into comenzi
values(100,10,'2',to_date('oct 12,04','mon dd,yy'));
insert into comenzi
values(200,20,'3',to_date('oct 6,04','mon dd,yy'));
insert into comenzi
values(300,40,'1',to_date('nov 30,04','mon dd,yy'));
insert into comenzi
values(400,30,'2',to_date('dec 12,04','mon dd,yy'));
insert into comenzi
values(500,50,'1',to_date('jan 15,05','mon dd,yy'));

insert into rindcom
values(100,111,150,5000,to_date('oct 31,04','mon dd,yy'));
insert into rindcom
values(200,222,300,20000,to_date('nov 30,04','mon dd,yy'));
insert into rindcom
values(300,444,1000,1500,to_date('dec 25,04','mon dd,yy'));
insert into rindcom
values(300,111,200,5000,to_date('jan 31,05','mon dd,yy'));
insert into rindcom
values(400,333,1500,5000,to_date('jan 31,05','mon dd,yy'));
insert into rindcom
values(500,111,100,5000,to_date('feb 20,05','mon dd,yy'));
commit;

--1.adaugarea coloanei cod_fiscal varchar2(8) la firme
select * from firme;
alter table firme add cod_fiscal varchar2(8);

--2.	Codul fiscal al tuturor firmelor este 123
update firme set cod_fiscal=123;

--3.	Sa se dezactiveze cheia primara a tabelei AGENTI
alter table agenti drop primary key; -- nu se poate deoarece este cheie externa in alt tabel

--4. introducere de linie noua in agenti
insert into agenti values('4','George Alina',to_date('feb 3,04','mon dd,yy'),to_date('jan 23,44','mon dd,yy'),'BANAT','INGINER','2');
select * from agenti;