SQL DDL for Employee, Center, Department, and Pay Tables
SQL DDL for Employee, Center, Department, and Pay Tables
Remove Existing Constraints
The following SQL statements remove existing constraints from the tables.
ALTER TABLE DEPARTAMENTO DROP CONSTRAINT FK_DEPARTAMENTO_CENTRO;
ALTER TABLE EMPLEADO DROP CONSTRAINT FK_EMPLEADO_PAGA;
ALTER TABLE DEPARTAMENTO DROP CONSTRAINT FK_DEPARTAMENTO_EMPLEADO;
ALTER TABLE EMPLEADO DROP CONSTRAINT FK_EMPLEADO_DEPARTAMENTO;
ALTER TABLE DEPARTAMENTO DROP CONSTRAINT FK_DEPARTAMENTO_DEPARTAMENTO;
ALTER TABLE CENTRO DROP CONSTRAINT UK_CENTRO;
ALTER TABLE DEPARTAMENTO DROP CONSTRAINT UK_DEPARTAMENTO;
ALTER TABLE PAGA DROP CONSTRAINT UK_PAGA;
ALTER TABLE EMPLEADO DROP CONSTRAINT PK_EMPLEADO;
ALTER TABLE DEPARTAMENTO DROP CONSTRAINT PK_DEPARTAMENTO;
ALTER TABLE CENTRO DROP CONSTRAINT PK_CENTRO;
ALTER TABLE PAGA DROP CONSTRAINT PK_PAGA;
Create Employee Table
The following SQL statements create the EMPLEADO table and insert data.
DROP TABLE EMPLEADO;
CREATE TABLE EMPLEADO (
NUMEMPLE NUMBER(3),
NOMEMPLE VARCHAR(25) NOT NULL,
NUMCAT NUMBER(1),
EXTEL NUMBER(3),
FECHNAC DATE,
FECHIN DATE NOT NULL,
SALARIO NUMBER(4),
COMISION NUMBER(3),
NUMHIJOS NUMBER(2),
NUMDEPTO NUMBER(3) NOT NULL,
ESTADO CHAR(1)
);
INSERT INTO EMPLEADO VALUES (7, 'SANCHEZ, FEDERICO', 1, 115, '02-07-1950', '15-02-1977', 150, NULL, 6, 111, 'V');
INSERT INTO EMPLEADO VALUES (8, 'ROMO, MERCEDES', 7, 041, '14-04-1955', '15-02-1990', 305, NULL, 1, 110, 'C');
INSERT INTO EMPLEADO VALUES (10, 'SANZ, ANTONIO', 7, 125, '12-12-1940', '04-2-1978', 355, NULL, 2, 111, 'C');
INSERT INTO EMPLEADO VALUES (11, 'ABENGOZA, RAQUEL', 5, 110, '01-03-1959', '01-03-1975', 205, NULL, 3, 100, 'S');
INSERT INTO EMPLEADO VALUES (12, 'MUÑOZ, ESTELA', 3, 110, '04-02-1964', '01-02-1996', 85, NULL, 0, 100, 'D');
INSERT INTO EMPLEADO VALUES(13, 'LOPEZ, LUCIANO', 1, 115, '25-07-1944', '20-01-1960', 180, NULL, 2, 111, 'C');
INSERT INTO EMPLEADO VALUES(15, 'PEREZ, JULIO', 7, 025, '18-06-1961', '22-02-1992', 253, NULL, 1, 120, 'C');
INSERT INTO EMPLEADO VALUES(17, 'MORAN, CARMEN', 7, 444, '15-07-1956', '03-01-1985', 208, 255, 2, 140, 'C');
INSERT INTO EMPLEADO VALUES(18, 'ESCAMILLAS, JESUS', 7, 046, '12-02-1955', '07-10-1979', 301, 395, 4, 110, 'C');
INSERT INTO EMPLEADO VALUES(19, 'OLMO, EPIFANIO', 4, 444, '15-04-1962', '23-01-1997', 81, 115, 0, 140, 'S');
INSERT INTO EMPLEADO VALUES(21, 'SALAN, VERONICA', 2, 115, '12-03-1966', '02-02-1997', 77, NULL, 2, 111, NULL);
INSERT INTO EMPLEADO VALUES(23, 'ROMO, PILAR', 3, 222, '04-02-1964', '01-02-1990', 110, NULL, 1, 122, 'S');
INSERT INTO EMPLEADO VALUES(24, 'ROJO, VICTOR', 6, 333, '16-06-1970', '17-01-1992', 105, 125, 0, 137, 'S');
INSERT INTO EMPLEADO VALUES(26, 'VEIGA, CAMELIA', 7, 011, '15-05-1960', '12-03-1987', 400, NULL, 3, 100, 'C');
INSERT INTO EMPLEADO VALUES(27, 'GIL, FERNANDO', 7, 088, '14-04-1955', '15-07-1992', 325, 350, 4, 112, 'C');
INSERT INTO EMPLEADO VALUES(28, 'PINO, ANA', 5, 222, '12-03-1961', '01-09-1992', 195, NULL, 3, 122, 'C');
INSERT INTO EMPLEADO VALUES(29, 'MOLINA, ESTHER', 2, 212, '22-10-1970', '22-10-1990', 83, NULL, 0, 122, 'S');
INSERT INTO EMPLEADO VALUES(30, 'TORRES, DIANA', 4, 111, '15-03-1964', '11-01-1996', 125, NULL, 2, 121, 'C');
INSERT INTO EMPLEADO VALUES(31, 'DIEZ, AURELIO', 7, 025, '11-03-1953', '15-02-1978', 350, NULL, 3, 130, 'C');
INSERT INTO EMPLEADO VALUES(32, 'MARTIN, CARMEN', 5, 111, '10-10-1959', '20-05-1981', 205, NULL, 4, 121, 'C');
INSERT INTO EMPLEADO VALUES(33, 'SUAREZ, NURIA', 3, 021, '04-10-1970', '05-02-1997', 105, 99, 0, 110, 'S');
INSERT INTO EMPLEADO VALUES(35, 'CAMACHO, MIGUEL', 5, 021, '11-11-1960', '15-05-1985', 220, 155, 2, 110, 'C');
INSERT INTO EMPLEADO VALUES (36,'GARRIDO, ENRIQUE',5,25,'10-01-1957','01-09-1985',225,NULL,2,130,'C');
INSERT INTO EMPLEADO VALUES (37,'MORALEJA, DORINDA',4,444,'17-08-1965','02-02-1997',105,122,1,140,'C');
INSERT INTO EMPLEADO VALUES (38,'LARA,HONORIO',2,135,'22-02-1967','21-01-1997',99,NULL,1,135,'S');
INSERT INTO EMPLEADO VALUES (39,'DURAN, GEMA',2,135,'16-03-1970','16-02-1997',105,NULL,0,130,'S');
INSERT INTO EMPLEADO VALUES (40,'TORRES, RUBEN',3,135,'15-12-1966','11-01-1985',112,NULL,3,130,'C');
INSERT INTO EMPLEADO VALUES (41,'RUIZ, FABIOLA',4,135,'03-02-1970','02-01-1997',155,NULL,3,135,'S');
INSERT INTO EMPLEADO VALUES (42,'DIEZ, AMELIA',6,113,'22-03-1953','25-03-1990',175,150,2,112,'D');
INSERT INTO EMPLEADO VALUES (44,'ALONSO, CARMEN',3,135,'12-04 1968','11-02-1997',115,NULL,1,135,'C');
INSERT INTO EMPLEADO VALUES (45,'ACEÑA, EUGENIA',6,444,'11-07-1963','13-06 1991',200,190,0,140,'C');
INSERT INTO EMPLEADO VALUES (48,'CLEMENTE, JORGE',6,444,'01-01-1955','15-02-1990',190,205,4,140,'C');
INSERT INTO EMPLEADO VALUES (49,'PERAL, JAVIER',6,444,'02-12-1956','03-01-1996',125,145,4,140,'C');
INSERT INTO EMPLEADO VALUES (50,'PRESA, ANTONIA',6,089,'16-05-1963','15-02-1987',150,220,2,122,'C');
INSERT INTO EMPLEADO VALUES (51,'SIEBING, JAVIER',3,111,'15-05-1957','18-01-1995',150,NULL,1,121,'C');
INSERT INTO EMPLEADO VALUES (52,'BOLAÑOS, JOSE',6,089,'12-06-1961','13-11-1978',145,201,0,112,'C');
INSERT INTO EMPLEADO VALUES (57,'LUCENA, ANTONIO',6,333,'31-12-1956','08-04-1988',180,160,3,137,'C');
INSERT INTO EMPLEADO VALUES (59,'CASAS, GUILLERMO',7,333,'03-02-1960','22-04-1989',250,205,3,137,'C');
INSERT INTO EMPLEADO VALUES (60,'FERNANDEZ, MANOLO',5,111,'18-12-1950','03-06-1996',180,NULL,5,121,'C');
INSERT INTO EMPLEADO VALUES (61,'LANCHAS, ROSA',1,115,'06-10-1970','03-02-1997',92,NULL,0,111,'S');
INSERT INTO EMPLEADO VALUES (62,'CARREIRA, JALIAN',7,35,'04-02-1963','03-02-1997',225,NULL,1,135,'C');
INSERT INTO EMPLEADO VALUES (63,'GARCIA, FRANCISCO',1,115,'01-05-1973','05-02-1997',92,NULL,0,111,NULL);
INSERT INTO EMPLEADO VALUES (64,'PEREZ, Mª LUISA',7,066,'03-08-1958','05-07-1980',350,NULL,2,121,'C');
INSERT INTO EMPLEADO VALUES (65,'JIMENEZ, CRISTINA',7,055,'24-12-1964','06-06-1991',365,NULL,0,122,'C');
INSERT INTO EMPLEADO VALUES (69,'TORRES, FERNANDO',2,135,'17-04-1968','02-02-1996',107,NULL,0,130,'S');
INSERT INTO EMPLEADO VALUES (71,'DE LA FUENTE, ALBA',7,077,'17-02-1965','16-11-1996',265,NULL,0,100,'S');
ALTER TABLE EMPLEADO ADD CONSTRAINT PK_EMPLEADO PRIMARY KEY(NUMEMPLE);
Create Center Table
The following SQL statements create the CENTRO table and insert data.
DROP TABLE CENTRO;
CREATE TABLE CENTRO (
NUMCE NUMBER(2),
NOMCE VARCHAR(25),
SEÑAS VARCHAR(40),
TELEFONO VARCHAR(10)
);
INSERT INTO CENTRO VALUES(10,'SEDE CENTRAL','C. PASEO DE LA HABANA,130. MADRID','91-2502126');
INSERT INTO CENTRO VALUES(15,'RELACION CON CLIENTES','C. VITRUBIO,55. MADRID','91-3565051');
INSERT INTO CENTRO VALUES(20,'CENTRO DE PROCESOS','C. NUEVA,20. TRES CANTOS. MADRID','91-7251515');
INSERT INTO CENTRO VALUES(25,'DELEGACION NORTE','C. LAS VEGAS, 15. BILBAO','94-262502');
INSERT INTO CENTRO VALUES(30,'DELEGACION SUR','AVDA. DEL OESTE, 45. SEVILLA','95-454678');
ALTER TABLE CENTRO ADD CONSTRAINT PK_CENTRO PRIMARY KEY(NUMCE);
Create Department Table
The following SQL statements create the DEPARTAMENTO table and insert data.
DROP TABLE DEPARTAMENTO;
CREATE TABLE DEPARTAMENTO (
NUMDEPTO NUMBER(3),
NOMDEPTO VARCHAR(25),
DIRECTOR NUMBER(3) NOT NULL,
PRESUPUESTO NUMBER(4,1),
DEPDE NUMBER(3),
NUMCE NUMBER(2) NOT NULL
);
INSERT INTO DEPARTAMENTO VALUES (100,'DIRECCION GENERAL', 26, 15.2, NULL, 10);
INSERT INTO DEPARTAMENTO VALUES (110,'DIRECCION COMERCIAL', 18,15,100,15);
INSERT INTO DEPARTAMENTO VALUES (111,'PRODUCCION', 10,16,110,15);
INSERT INTO DEPARTAMENTO VALUES (112,'COMERCIAL', 27,25.4,110,15);
INSERT INTO DEPARTAMENTO VALUES (120,'ORGANIZACION', 15,6.3,100,10);
INSERT INTO DEPARTAMENTO VALUES (121,'RECURSOS HUMANOS', 64,5.2,120,10);
INSERT INTO DEPARTAMENTO VALUES (122,'INFORMATICA', 65,7.25,120,20);
INSERT INTO DEPARTAMENTO VALUES (130,'CONTABILIDAD Y FINANZAS', 31,5,100,10);
INSERT INTO DEPARTAMENTO VALUES (135,'MARKETING', 62,10,110,10);
INSERT INTO DEPARTAMENTO VALUES (137,'COMERCIAL NORTE', 59,5,112,25);
INSERT INTO DEPARTAMENTO VALUES (140,'COMERCIAL SUR', 17,5.8,112,30);
ALTER TABLE DEPARTAMENTO ADD CONSTRAINT PK_DEPARTAMENTO PRIMARY KEY(NUMDEPTO);
Create Pay Table
The following SQL statements create the PAGA table and insert data.
DROP TABLE PAGA;
CREATE TABLE PAGA (
NUMCAT NUMBER(1),
DESCAT VARCHAR(20) NOT NULL,
JULIO NUMBER(4,1),
NAVIDAD NUMBER(4,1)
);
INSERT INTO PAGA VALUES (1, 'SIN CUALIFICAR', 65.2, 75);
INSERT INTO PAGA VALUES (2, 'TECNICO AUXILIAR', 72, 81.5);
INSERT INTO PAGA VALUES (3, 'TECNICO ESPECIALISTA', 85, 89);
INSERT INTO PAGA VALUES (4,'EJECUTIVO JUNIOR',86.2,93);
INSERT INTO PAGA VALUES (5,'EJECUTIVO',94.5,105.2);
INSERT INTO PAGA VALUES (6,'COMERCIAL',95.2,106.8);
INSERT INTO PAGA VALUES (7,'DIRECTOR',105,118);
ALTER TABLE PAGA ADD CONSTRAINT PK_NUMCAT PRIMARY KEY(NUMCAT);
Add Constraints
The following SQL statements add constraints to the tables.
ALTER TABLE EMPLEADO ADD CONSTRAINT CK_EMPLEADO CHECK (ESTADO='S' OR ESTADO='C' OR ESTADO='D' OR ESTADO='V');
alter table EMPLEADO add CONSTRAINT FK_EMPLEADO_PAGA FOREIGN KEY (NUMCAT) REFERENCES PAGA(NUMCAT);
alter table DEPARTAMENTO add CONSTRAINT FK_DEPARTAMENTO_EMPLEADO FOREIGN KEY (DIRECTOR) REFERENCES EMPLEADO(NUMEMPLE);
alter table EMPLEADO add CONSTRAINT FK_EMPLEADO_DEPARTAMENTO FOREIGN KEY (NUMDEPTO) REFERENCES DEPARTAMENTO(NUMDEPTO);
alter table DEPARTAMENTO add CONSTRAINT FK_DEPARTAMENTO_DEPARTAMENTO FOREIGN KEY (DEPDE) REFERENCES DEPARTAMENTO(NUMDEPTO);
alter table DEPARTAMENTO add CONSTRAINT FK_DEPARTAMENTO_CENTRO FOREIGN KEY (NUMCE) REFERENCES CENTRO(NUMCE);
ALTER TABLE CENTRO ADD CONSTRAINT UK_CENTRO UNIQUE (NOMCE);
ALTER TABLE DEPARTAMENTO ADD CONSTRAINT UK_DEPARTAMENTO UNIQUE (NOMDEPTO);
ALTER TABLE PAGA ADD CONSTRAINT UK_PAGA UNIQUE (DESCAT);
COMMIT;