SHARE
TWEET

Untitled

a guest Dec 8th, 2019 69 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DROP TABLE FIRME CASCADE CONSTRAINTS;
  2. DROP TABLE AGENTI CASCADE CONSTRAINTS;
  3. DROP TABLE COMENZI CASCADE CONSTRAINTS;
  4. DROP TABLE RINDCOM CASCADE CONSTRAINTS;
  5. DROP TABLE PRODUSE CASCADE CONSTRAINTS;
  6.  
  7. create table firme
  8. (codfirma number(2) constraint PKey_firme primary key,
  9. denfirma varchar2(20) not null,
  10. loc varchar2(20),
  11. contbanca varchar(15),
  12. zona varchar2(15) CONSTRAINT FZONA_CK check (zona in ('MOLDOVA','ARDEAL','BANAT','MUNTENIA','DOBROGEA','TRANSILVANIA')));
  13.  
  14. create table agenti
  15. (codagent varchar2(3) constraint pk_agent primary key,
  16. numeagent varchar2(25) not null,
  17. dataang date default sysdate,
  18. datanast date,
  19. zona varchar2(15) CONSTRAINT AGZONA_CK check (zona in('MOLDOVA','ARDEAL','BANAT','MUNTENIA','DOBROGEA', 'TRANSILVANIA')),
  20. functia varchar2(20),
  21. codsef varchar2(3));
  22.  
  23. create table comenzi
  24. (nrcom number(4) constraint pk_comenzi primary key,
  25. codfirma number(2) not null,
  26. codagent varchar2(3) not null,
  27. data date default sysdate,
  28. CONSTRAINT FKAgent FOREIGN KEY (codagent) REFERENCES agenti(codagent),
  29. CONSTRAINT FKFirme FOREIGN KEY (codfirma) REFERENCES firme(codfirma));
  30.  
  31. create table produse
  32. (codprodus number(3) constraint pk_produse primary key,
  33. denprodus varchar2(20) not null,
  34. um varchar2(3),
  35. stoc number(4));
  36.  
  37. create table rindcom
  38. (nrcom number(4),
  39. codprodus number(3) not null,
  40. cant number(10),
  41. pret number(8),
  42. termenliv date,
  43. CONSTRAINT FKComenzi FOREIGN KEY (nrcom) REFERENCES comenzi(nrcom),
  44. CONSTRAINT FKProduse FOREIGN KEY (codprodus) REFERENCES produse(codprodus));
  45. delete from rindcom;
  46.  
  47. delete from comenzi;
  48.  
  49. delete from firme;
  50.  
  51. delete from agenti;
  52. delete from produse;
  53.  
  54. insert into firme values(10,'SC ALFA SRL','Cluj','bcr1000','TRANSILVANIA');
  55. insert into firme values(20,'SC MEDIA SA','Bucuresti','brd1111','MUNTENIA');
  56. insert into firme values(30,'SC SOFTY SRL','Ploiesti','bcr2222','MUNTENIA');
  57. insert into firme values(40,'SC MEGA SRL','Iasi','brd3333','MOLDOVA');
  58. insert into firme values(50,'SC STAR SA','Timisoara','bcr4444','BANAT');
  59. insert into firme values(60,'SC Sas SA','Timisoara','bcr333','BANAT');
  60.  
  61. insert into agenti
  62. values('1','Toma Alina',to_date('feb 3,04','mon dd,yy'),to_date('jan 23,44','mon dd,yy'),'BANAT','ECONOMIST','2');
  63. insert into agenti
  64. values('2','Rotaru Maria',to_date('apr 12,03','mon dd,yy'),to_date('feb 13,54','mon dd,yy'),'MOLDOVA','DIRECTOR','2');
  65. insert into agenti
  66. values('3','Popescu Ionel',to_date('may 30,04','mon dd,yy'),to_date('Sep 3,65','mon dd,yy'),'MUNTENIA','CONTABIL','1');
  67.  
  68.  
  69. insert into produse
  70. values(111,'napolitane','buc',1000);
  71. insert into produse
  72. values(222,'ciocolata','buc',4000);
  73. insert into produse
  74. values (333,'biscuiti','pac',3000);
  75. insert into produse
  76. values(444,'servetele','pac',1100);
  77.  
  78. insert into comenzi
  79. values(100,10,'2',to_date('oct 12,04','mon dd,yy'));
  80. insert into comenzi
  81. values(200,20,'3',to_date('oct 6,04','mon dd,yy'));
  82. insert into comenzi
  83. values(300,40,'1',to_date('nov 30,04','mon dd,yy'));
  84. insert into comenzi
  85. values(400,30,'2',to_date('dec 12,04','mon dd,yy'));
  86. insert into comenzi
  87. values(500,50,'1',to_date('jan 15,05','mon dd,yy'));
  88.  
  89. insert into rindcom
  90. values(100,111,150,5000,to_date('oct 31,04','mon dd,yy'));
  91. insert into rindcom
  92. values(200,222,300,20000,to_date('nov 30,04','mon dd,yy'));
  93. insert into rindcom
  94. values(300,444,1000,1500,to_date('dec 25,04','mon dd,yy'));
  95. insert into rindcom
  96. values(300,111,200,5000,to_date('jan 31,05','mon dd,yy'));
  97. insert into rindcom
  98. values(400,333,1500,5000,to_date('jan 31,05','mon dd,yy'));
  99. insert into rindcom
  100. values(500,111,100,5000,to_date('feb 20,05','mon dd,yy'));
  101. commit;
  102.  
  103. --1.adaugarea coloanei cod_fiscal varchar2(8) la firme
  104. select * from firme;
  105. alter table firme add cod_fiscal varchar2(8);
  106.  
  107. --2.    Codul fiscal al tuturor firmelor este 123
  108. update firme set cod_fiscal=123;
  109.  
  110. --3.    Sa se dezactiveze cheia primara a tabelei AGENTI
  111. alter table agenti drop primary key; -- nu se poate deoarece este cheie externa in alt tabel
  112.  
  113. --4. introducere de linie noua in agenti
  114. 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');
  115. select * from agenti;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top