Manipulación de datos

...

NUMBER(8, 2),

COMISION NUMBER(2, 2),

ID_GERENTE NUMBER (6,0),

ID_DEPARTAMENTO NUMBER (4,0),

CONSTRAINT PUESTO_EMPLEADO_FK FOREIGN KEY(ID_PUESTO)

REFERENCES PUESTO_MVV (ID_PUESTO),

CONSTRAINT DEPARTAMENTO_EMPLEADO_FK FOREIGN KEY(ID_DEPARTAMENTO)

REFERENCES DEPARTAMENTO_MVV (ID_DEPARTAMENTO)

);

// TAREA CAPITULO 2

SELECT ID_EMPLEADO, APATERNO, ID_PUESTO, ID_GERENTE, ID_DEPARTAMENTO

FROM E

WHERE ID_DEPARTAMENTO = 50 ;

SELECT ID_EMPLEADO, APATERNO,FECHA_CONTRATO, ID_PUESTO, ID_GERENTE, ID_DEPARTAMENTO

FROM E

WHERE FECHA_CONTRATO = ’17-AGO-02’ ;

SELECT ID_EMPLEADO,NOMBRE, APATERNO, SALARIO

FROM E

WHERE SALARIO < 6000 ;

SELECT ID_EMPLEADO,NOMBRE, APATERNO, SALARIO

FROM E

WHERE SALARIO = 2500 ;

SELECT ID_PUESTO, SALARIO

FROM E

WHERE SALARIO BETWEEN 5000 AND 9000 ;

SELECT ID_EMPLEADO,NOMBRE, SALARIO, ID_DEPARTAMENTO

FROM E

WHERE SALARIO >= 4800 AND ID_DEPARTAMENTO = 60 ;

SELECT ID_EMPLEADO,NOMBRE, SALARIO, ID_DEPARTAMENTO

FROM E

WHERE ID_DEPARTAMENTO NOT IN( 60, 100, 80) ;

SELECT ID_DEPARTAMENTO,N_DEPARTAMENTO, ID_LOCALIZACION, ID_GERENTE

FROM E

WHERE ID_DEPARTAMENTO NOT IN( 60, 100, 80) ;

//tarea expo 9.2

INSERT INTO E VALUES (207,’ALVARO’,’AVIÑA’,’AGUILAR’,’4981108752’,sysdate,’MK_REP’,3200,0.2,124,50);

INSERT INTO E VALUES (208,’MANUEL’,’AVIÑA’,’AGUILAR’,’4981108752’,sysdate,’MK_REP’,3200,0.2,124,50);

INSERT INTO E VALUES (209,’PIPO’,’AVIÑA’,’AGUILAR’,’4981108752’,sysdate,’MK_REP’,3200,0.2,124,50);

INSERT INTO E VALUES (210,’MARIO’,’AVIÑA’,’AGUILAR’,’4981108752’,sysdate,’MK_REP’,3200,0.2,124,50);

INSERT INTO E VALUES (211,’ROSA’,’AVIÑA’,’AGUILAR’,’4981108752’,sysdate,’MK_REP’,3200,0.2,124,50);

INSERT INTO E VALUES (212,’JESUS’,’AVIÑA’,’AGUILAR’,’4981108752’,sysdate,’MK_REP’,3200,0.2,124,50);

INSERT INTO E VALUES (213,’DANIEL’,’AVIÑA’,’AGUILAR’,’4981108752’,sysdate,’MK_REP’,3200,0.2,124,50);

INSERT INTO E VALUES (214,’ODET’,’AVIÑA’,’AGUILAR’,’4981108752’,sysdate,’MK_REP’,3200,0.2,124,50);

INSERT INTO E VALUES (215,’ANA’,’AVIÑA’,’AGUILAR’,’4981108752’,sysdate,’MK_REP’,3200,0.2,124,50);

INSERT INTO E VALUES (216,’CARMELA’,’AVIÑA’,’AGUILAR’,’4981108752’,sysdate,’MK_REP’,3200,0.2,124,50);

DELETE FROM E

WHERE SALARIO=3200;

ROLLBACK;

UPDATE E

SET ID_DEPARTAMENTO = 60

WHERE ID_EMPLEADO BETWEEN 207 AND 216;

SAVEPOINT TAREA;

DELETE FROM E

WHERE FECHA_CONTRATO BETWEEN 01/01/05 AND 31/12/06 ;

ROLLBACK TO TAREA;

INSERT INTO EMPLEADO_MVV (SELECT * FROM HR.EMPLOYEES);

create sequence EMPLEADO

increment by 1

start with 207

nocycle

nocache;

create synonym E

for EMPLEADO_MVV;

select * from E;

SELECT * FROM EMPLEADO_MVV;

DROP TABLE EMPLEADO_MVV;

CREATE TABLE HISTORIAL_TRABAJO_MVV(

ID_EMPLEADO NUMBER(6,0) CONSTRAINT HT_EMP_NN NOT NULL,

F_INICIO DATE CONSTRAINT HT_FIN_NN NOT NULL,

F_FIN DATE CONSTRAINT HT_FFIN_NN NOT NULL,

ID_PUESTO VARCHAR2(10) CONSTRAINT HT_PUESTO_NN NOT NULL,

ID_DEPARTAMENTO NUMBER(4,0),

CONSTRAINT PUESTO_HT_FK FOREIGN KEY (ID_PUESTO)

REFERENCES PUESTO_MVV (ID_PUESTO),

CONSTRAINT DEPARTAMENTO_HT_FK FOREIGN KEY (ID_DEPARTAMENTO)

REFERENCES DEPARTAMENTO_MVV (ID_DEPARTAMENTO),

CONSTRAINT EMPLEADO_HT_FK FOREIGN KEY (ID_EMPLEADO)

REFERENCES EMPLEADO_MVV (ID_EMPLEADO),

CONSTRAINT HT_PK PRIMARY KEY (ID_EMPLEADO, F_INICIO)

);

INSERT INTO HISTORIAL_TRABAJO_MVV (SELECT * FROM HR.JOB_HISTORY);

create synonym HT

for HISTORIAL_TRABAJO_MVV;

select