Manipulación de datos

CREATE TABLE REGION_MVV(

ID_REGION NUMBER (4) CONSTRAINT REGION_PK PRIMARY KEY,

NOMBRE_REGION VARCHAR2 (25)

);

INSERT INTO REGION_MVV (SELECT * FROM HR.REGIONS);

create sequence REGION

increment by 1

start with 5

nocycle

nocache;

create synonym R

for REGION_MVV;

select * from R;

CREATE INDEX REGION_NOMBRE ON REGION_MVV (NOMBRE_REGION);

SELECT * FROM REGION_MVV;

DROP TABLE REGION_MVV;

CREATE TABLE PAISES_MVV(

ID_PAIS CHAR (2) CONSTRAINT PAIS_PK PRIMARY KEY,

NOMBRE VARCHAR2 (40),

ID_REGION NUMBER (4),

CONSTRAINT REG_PAIS_FK FOREIGN KEY (ID_REGION) REFERENCES REGION_MVV (ID_REGION)

);

INSERT INTO PAISES_MVV (SELECT * FROM HR.COUNTRIES);

create synonym PA

for PAISES_MVV;

select * from PA;

DROP SYNONYM PA;

CREATE INDEX PAIS_NOMBRE ON PAISES_MVV (NOMBRE);

SELECT * FROM PAISES_MVV;

DROP TABLE PAISES_MVV;

CREATE TABLE LOCALIZACION_MVV(

ID_LOCALIZACION NUMBER (4, 0) CONSTRAINT ID_LOCALIDAD_PK PRIMARY KEY,

CALLE_DIR VARCHAR2 (40),

CODIGO_POST VARCHAR2 (12),

CIUDAD VARCHAR2 (30) CONSTRAINT CIUDAD_NN NOT NULL,

ESTADO VARCHAR2 (25),

ID_PAIS CHAR (2),

CONSTRAINT PAIS_LOC_FK FOREIGN KEY (ID_PAIS)

REFERENCES PAISES_MVV (ID_PAIS)

);

INSERT INTO LOCALIZACION_MVV (SELECT * FROM HR.LOCATIONS);

create sequence LOCALIZACION

increment by 100

start with 3300

nocycle

nocache;

create synonym L

for LOCALIZACION_MVV;

select * from L;

CREATE INDEX DIRECCION ON LOCALIZACION_MVV (CALLE_DIR);

CREATE INDEX POSTAL ON LOCALIZACION_MVV (CODIGO_POST);

SELECT * FROM LOCALIZACION_MVV;

DROP TABLE LOCALIZACION_MVV;

CREATE TABLE PUESTO_MVV(

ID_PUESTO VARCHAR2(10) CONSTRAINT ID_PUESTO_PK PRIMARY KEY,

N_PUESTO VARCHAR2(35) CONSTRAINT PU_NPUESTO_NN NOT NULL,

SALARIO_MIN NUMBER(6,0),

SALARIO_MAX NUMBER(6,0)

);

INSERT INTO PUESTO_MVV (SELECT * FROM HR.JOBS);

create synonym P

for PUESTO_MVV;

select * from P;

DROP SYNONYM P;

CREATE INDEX PUESTO_NOMBRE ON PUESTO_MVV (N_PUESTO);

SELECT * FROM PUESTO_MVV;

DROP TABLE PUESTO_MVV;

CREATE TABLE DEPARTAMENTO_MVV(

ID_DEPARTAMENTO NUMBER (4, 0) CONSTRAINT ID_DEPA_PK PRIMARY KEY,

N_DEPARTAMENTO VARCHAR2 (30) CONSTRAINT DEP_NDEPA_NN NOT NULL,

ID_GERENTE NUMBER (6, 0),

ID_LOCALIZACION NUMBER (4, 0),

CONSTRAINT DEPA_LOC_FK FOREIGN KEY (ID_LOCALIZACION)

REFERENCES LOCALIZACION_MVV (ID_LOCALIZACION)

);

INSERT INTO DEPARTAMENTO_MVV (SELECT * FROM HR.DEPARTMENTS);

create sequence DEPARTAMENTO

increment by 10

start with 280

nocycle

nocache;

create synonym D

for DEPARTAMENTO_MVV;

DROP SYNONYM D;

select * from D;

--drop synonym e;

CREATE INDEX NOMBRE_DEPARTAMENTO ON DEPARTAMENTO_MVV (N_DEPARTAMENTO);

SELECT * FROM DEPARTAMENTO_MVV;

DROP TABLE DEPARTAMENTO_MVV;

CREATE TABLE EMPLEADO_MVV(

ID_EMPLEADO NUMBER(6, 0) CONSTRAINT EM_IDEMP_PK PRIMARY KEY,

NOMBRE VARCHAR2(20),

APATERNO VARCHAR(25) CONSTRAINT EM_APELLIDO_NN NOT NULL,

CORREO VARCHAR2(25) CONSTRAINT EM_CORREO_NN NOT NULL,

TELEFONO VARCHAR2(20),

FECHA_CONTRATO DATE CONSTRAINT EM_FECHCONT_NN NOT NULL,

ID_PUESTO VARCHAR2(10) CONSTRAINT EM_PUESTO_NN NOT NULL,

SALARIO 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

...