criabd

Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY';
ALTER SESSION SET NLS_DATE_LANGUAGE=AMERICAN;
/*******************************************************************/
/* CRIACAO DA TABELA DEPTO */
/*******************************************************************/
DROP TABLE DEPTO CASCADE CONSTRAINTS;
CREATE TABLE DEPTO ( NRO_DEPTO NUMBER(1) ,
 NOME VARCHAR2(50) ,
 COD_GER NUMBER(2)) ;
ALTER TABLE DEPTO ADD CONSTRAINT PK_DEPTO
 PRIMARY KEY (NRO_DEPTO);
INSERT INTO DEPTO VALUES (1,'PRESIDENCIA' ,10);
INSERT INTO DEPTO VALUES (2,'DIRETORIA' ,11);
INSERT INTO DEPTO VALUES (3,'ASSESSORIA ESPECIAL' ,13);
INSERT INTO DEPTO VALUES (4,'MARKETING' ,15);
INSERT INTO DEPTO VALUES (5,'INFORMATICA' ,18);
INSERT INTO DEPTO VALUES (6,'PESSOAL' ,24);
INSERT INTO DEPTO VALUES (7,'ENGENHARIA' ,36);
INSERT INTO DEPTO VALUES (8,'PRODUCAO' ,31);
/*******************************************************************/
/* CRIACAO DA TABELA FUNC */
/*******************************************************************/
DROP TABLE FUNC CASCADE CONSTRAINTS;
CREATE TABLE FUNC (COD_FUNC NUMBER(2) ,
 NOME VARCHAR2(50) ,
 DTA_NASC DATE ,
 SALARIO NUMBER(9,2) ,
 NRO_DEPTO NUMBER(1) ,
 COD_SUPERV NUMBER(2)) ;
ALTER TABLE FUNC ADD CONSTRAINT PK_FUNC 
 PRIMARY KEY (COD_FUNC);
ALTER TABLE FUNC ADD CONSTRAINT FK_FUNC_DEPTO
 FOREIGN KEY (NRO_DEPTO) REFERENCES DEPTO (NRO_DEPTO);
ALTER TABLE FUNC ADD CONSTRAINT FK_FUNC_SUPERV
 FOREIGN KEY (COD_SUPERV) REFERENCES FUNC (COD_FUNC);
INSERT INTO FUNC VALUES (10,'MARCELO PANINI' ,'31-DEC-1955',25000,1,NULL);
INSERT INTO FUNC VALUES (11,'AUGUSTA ROCHA' ,'29-SEP-1963',25000,2,10);
INSERT INTO FUNC VALUES (12,'CARLOS AUGUSTO DISRAELI','29-MAY-1961',20000,2,10);
INSERT INTO FUNC VALUES (13,'MARCIA MARIA COSTA' ,'01-JAN-1956',2500,3,12);
INSERT INTO FUNC VALUES (14,'ARNALDO NOVELLI' ,'10-AUG-1960',20000,2,10);
INSERT INTO FUNC VALUES (15,'ANDREA LEMOS' ,'24-DEC-1965',2500,4,11);
INSERT INTO FUNC VALUES (16,'GERALDO SANTOS' ,'15-OCT-1970',5000,4,14);
INSERT INTO FUNC VALUES (17,'NADIA PIERCING' ,'12-JAN-1968',750,5,16);
INSERT INTO FUNC VALUES (18,'CIBELE MARIA DO COUTO' ,'29-FEB-1972',4000,5,12);
INSERT INTO FUNC VALUES (19,'MARCO ANTONIO C. BRANCO','14-JUN-1964',4000,5,16);
INSERT INTO FUNC VALUES (20,'PEDRO RIBEIRO' ,'16-JUL-1961',4000,5,16);
INSERT INTO FUNC VALUES (21,'LUIZ CARLOS PINHEIRO' ,'02-NOV-1962',4000,5,16);
INSERT INTO FUNC VALUES (22,'JOSE MANUEL RIBEIRO' ,'26-MAR-1979',200,5,16);
INSERT INTO FUNC VALUES (23,'MARCOS CRUZ' ,'30-APR-1958',20000,2,10);
INSERT INTO FUNC VALUES (24,'MARCELA MONTES' ,'31-MAY-1975',4500,6,11);
INSERT INTO FUNC VALUES (25,'MARISA AMARAL' ,'31-AUG-1976',4500,6,23);
INSERT INTO FUNC VALUES (26,'PAULO SANTOS' ,'12-DEC-1960',4500,6,23);
INSERT INTO FUNC VALUES (27,'INES PAULINA MARIAL' ,'30-OCT-1972',2500,6,23);
INSERT INTO FUNC VALUES (28,'EDUARDO SILVA SOUZA' ,'24-JUL-1975',7500,6,23);
INSERT INTO FUNC VALUES (29,'MAGNOLIA MARIZ E BARROS','14-JUL-1977',7500,6,23);
INSERT INTO FUNC VALUES (30,'TITO LEMOS' ,'31-AUG-1955',7500,3,12);
INSERT INTO FUNC VALUES (31,'GERTRUDE STEINBERG' ,'05-NOV-1974',500,8,14);
INSERT INTO FUNC VALUES (32,'ROBERTO EMIL WILLY' ,'19-MAR-1975',120,8,27);
INSERT INTO FUNC VALUES (33,'FERNANDO BORGES RIBEIRO','01-MAY-1967',120,8,27);
INSERT INTO FUNC VALUES (34,'ADELAIDE DOUTEL LINK' ,'15-AUG-1966',750,7,26);
INSERT INTO FUNC VALUES (35,'ALBERTO MATTOS' ,'29-JUN-1973',500,8,27);
INSERT INTO FUNC VALUES (36,'REGINA TEIXEIRA' ,'16-JUN-1973',500,7,14);
INSERT INTO FUNC VALUES (37,'DIVA PEREIRA CARNEIRO' ,'10-APR-1974',500,7,26);
INSERT INTO FUNC VALUES (38,'AFFONSO ROMERO' ,'11-MAY-1975',500,7,26);
INSERT INTO FUNC VALUES (39,'MARCOS ALENCAR' ,'12-JUN-1976',500,7,26);
/*******************************************************************/
/* CRIACAO DA TABELA PROJETO */
/*******************************************************************/
DROP TABLE PROJETO CASCADE CONSTRAINTS;
CREATE TABLE PROJETO (COD_PROJ CHAR(3) ,
 NOME VARCHAR2(50) ,
 DURACAO NUMBER(2) ,
 NRO_DEPTO NUMBER(1)) ;
ALTER TABLE PROJETO ADD CONSTRAINT PK_PROJETO
 PRIMARY KEY (COD_PROJ);
ALTER TABLE PROJETO ADD CONSTRAINT FK_PROJ_DEPTO
 FOREIGN KEY (NRO_DEPTO) REFERENCES DEPTO (NRO_DEPTO);
INSERT INTO PROJETO VALUES ('FAT','FATURAMENTO' ,6 , 4);
INSERT INTO PROJETO VALUES ('CLI','CAPTACAO CLIENTES' ,4 , 4);
INSERT INTO PROJETO VALUES ('WEB','INTRANET' ,3 , 5);
INSERT INTO PROJETO VALUES ('SEG','SEGURANCA DA INFO' ,8 , 5);
INSERT INTO PROJETO VALUES ('FOL','FOLHA DE PAGAMENTO' ,12 ,6 );
INSERT INTO PROJETO VALUES ('PRO','NOVOS PRODUTOS' ,10 , 5);
/*******************************************************************/
/* CRIACAO DA TABELA PARTICIPA */
/*******************************************************************/
DROP TABLE PARTICIPA CASCADE CONSTRAINTS;
CREATE TABLE PARTICIPA (COD_PROJ CHAR(3) , 
		 COD_FUNC NUMBER(2) ,
 HORAS_TRAB NUMBER(3));
ALTER TABLE PARTICIPA ADD CONSTRAINT PK_PARTICIPA
 PRIMARY KEY (COD_PROJ,COD_FUNC);
ALTER TABLE PARTICIPA ADD CONSTRAINT FK_PART_PROOJ
 FOREIGN KEY (COD_PROJ) REFERENCES PROJETO (COD_PROJ);
ALTER TABLE PARTICIPA ADD CONSTRAINT FK_PART_FUNC
 FOREIGN KEY (COD_FUNC) REFERENCES FUNC(COD_FUNC);
ALTER TABLE DEPTO ADD CONSTRAINT FK_DEPTO_GER
 FOREIGN KEY (COD_GER) REFERENCES FUNC (COD_FUNC);
INSERT INTO PARTICIPA VALUES ('FAT',18,20);
INSERT INTO PARTICIPA VALUES ('FAT',19,40);
INSERT INTO PARTICIPA VALUES ('FAT',20,55);
INSERT INTO PARTICIPA VALUES ('CLI',13,100);
INSERT INTO PARTICIPA VALUES ('CLI',16,110);
INSERT INTO PARTICIPA VALUES ('WEB',17,120);
INSERT INTO PARTICIPA VALUES ('WEB',19,30);
INSERT INTO PARTICIPA VALUES ('WEB',20,50);
INSERT INTO PARTICIPA VALUES ('WEB',21,60);
INSERT INTO PARTICIPA VALUES ('SEG',17,80);
INSERT INTO PARTICIPA VALUES ('FOL',19,90);
INSERT INTO PARTICIPA VALUES ('FOL',24,20);
INSERT INTO PARTICIPA VALUES ('FOL',25,40);
INSERT INTO PARTICIPA VALUES ('FOL',26,50);
INSERT INTO PARTICIPA VALUES ('FOL',27,35);
INSERT INTO PARTICIPA VALUES ('PRO',11,135);
INSERT INTO PARTICIPA VALUES ('PRO',12,115);
INSERT INTO PARTICIPA VALUES ('PRO',13,165);
INSERT INTO PARTICIPA VALUES ('PRO',15,55);
INSERT INTO PARTICIPA VALUES ('PRO',16,75);
INSERT INTO PARTICIPA VALUES ('PRO',21,55);
INSERT INTO PARTICIPA VALUES ('PRO',22,85);