Crea las siguientes tablas, indicando las claves primarias y las ajenas:
PERSONAL
COD_CENTRO Varchar2, tamaño 10 Tiene que empezar con las letras ‘IES’
DNI Varchar2, tamaño 9 Termina en una letra
NOMBRE Varchar2, tamaño 30 No nulo
FUNCIÓN Varchar2, tamaño 15
FECHA_ALTA Fecha
SALARIO Número, 10 dígitos
CENTROS
COD_CENTRO Varchar2, tamaño 10 Tiene que empezar con las letras ‘IES’
TIPO_CENTRO Varchar2, tamaño 1 Sólo puede ser ‘S’ o ‘P’
NOMBRE Varchar2, tamaño 1 No nulo
DIRECCIÓN Varchar2, tamaño 30
TELEFONO Varchar2, tamaño 10 Empieza por 95
NUM_PLAZAS Numérico, 4 dígitos Positivo
1.- Añade las siguientes restricciones:
- La fecha de alta debe ser anterior a la del día actual.
- El salario debe ser positivo.
- El valor por defecto del campo num_plazas es 0.
2.- El script ofrecido por el profesor no es correcto, realiza las
modificaciones en las instrucciones insert para que no incumplan ninguna
restricción.
3.- Crear una vista que se llame CONSERJES que contenga el nombre del
centro y el nombre de sus conserjes.
4.- Añadir a la tabla PERSONAL una columna llamada COD_ASIG con dos
posiciones
numéricas.
5.- Crear la tabla TASIG con las siguientes columnas: COD_ASIG numérico, 2 posiciones
y NOM_ASIG cadena de 20 caracteres.
6.- Añadir la restricción de clave primaria a la columna COD_ASIG de la
tabla TASIG.
7.- Añadir la restricción de clave ajena a la columna COD_ASIG de la tabla COD_ASIG
8.- Visualizar los nombres de constraints y las columnas afectadas para las
tablas TASIG y PROFESORES.
9.- Eliminar los centros que no tengan personal.
10.- Añadir un nuevo profesor en el centro o en los centros cuyo número de administrativos
sea 1 en la especialidad de ‘IDIOMA’, con DNI 8790055 y de nombre ‘Clara
Salas’.
11.- Borrar al personal que este en centros de menos de 300 plazas y con
menos de dos profesores.
12.- Realizar una consulta en la que aparezcan por cada centro y en cada
especialidad el
número de profesores. Si el centro no tiene profesores, debe aparecer un 0
en la columna de número de profesores.
13.- Obtener por cada función el número de trabajadores.
14.- Crea una vista que se llame antigüedad donde se muestre el nombre del
personal, el
nombre del centro donde están y el número de años que lleva trabajando.
Solución en SQL
CREATE TABLE personal
(
cod_centro VARCHAR2 (10),
dni VARCHAR2 (9),
nombre VARCHAR2 (30) NOT NULL,
funcion VARCHAR2 (15),
fecha_alta DATE,
salario NUMBER (10),
CONSTRAINT pk_dni_personal
PRIMARY KEY (dni),
CONSTRAINT ck_cod_centro CHECK
(SUBSTR (cod_centro,1,3) ='IES'),
CONSTRAINT ck_dni CHECK
(SUBSTR (dni,9,2) BETWEEN 'A' AND 'Z')
);
CREATE TABLE centros
(
cod_centro VARCHAR2 (10),
tipo_centro VARCHAR2 (1),
nombre VARCHAR2 (1) NOT NULL,
direccion VARCHAR2 (30),
telefono VARCHAR2 (10),
num_plazas NUMBER (4),
CONSTRAINT pk_cod_centro
PRIMARY KEY (cod_centro),
CONSTRAINT ck_cod_centro_ant
CHECK (SUBSTR (cod_centro,1,3)='IES'),
CONSTRAINT ck_tipo_centro
CHECK (SUBSTR (tipo_centro,1,1) = 'S' OR 'P'),
CONSTRAINT ck_teléfono CHECK
(SUBSTR (telefono,1,2) ='95'),
CONSTRAINT ck_num_plazas CHECK
(SUBSTR (num_plazas,1,1)='>o')
);
ALTER TABLE personal ADD
CONSTRAINT ck_fecha
ALTER TABLE personal ADD
CONSTRAINT ck_salario > 0
ALTER TABLE centros ADD
CONSTRAINT ck_num_plazas_defecto
INSERT INTO personal VALUES
('IES4567890','12345678B', 'pepe','conserje','01-11-2012', '1000') ;
INSERT INTO personal VALUES
('123456789A','12345678C', 'juan','profesor','01-11-2013', '2000') ;
INSERT INTO personal VALUES
('IES4567894','123456784', 'luis','director','01-11-2011', '2500') ;
INSERT INTO centros VALUES
('IES4567890','S', 'psur','calle 1','950000122', '100') ;
INSERT INTO centros VALUES
('1214567890','S', 'psur1','calle 2','950000121', '101') ;
INSERT INTO centros VALUES
('IES4567891','A', 'psur2','calle 3','950000123', '102') ;
INSERT INTO centros VALUES
('IES4567895','P', 'psur3','calle 4','950000125', '103') ;
INSERT INTO centros VALUES ('IES4567898','P', 'psur4','calle
5','950000126', '0') ;
Solución en MYSQL
CRÉATE TABLE personal
(
cod_centro VARCHAR (10),
dni VARCHAR (9),
nombre VARCHAR (30) NOT NULL,
función VARCHAR (15),
fecha_alta FECHA,
salario NUMERIC (10),
CONSTRAINT pk_dni_personal
PRIMARY KEY (dni),
CONSTRAINT ck_cod_centro CHECK
(SUBSTR (cod_centro,1,3) ='IES'),
CONSTRAINT ck_dni CHECK
(SUBSTR (dni,9,2) BETWEEN 'A' AND 'Z')
);
CREATE TABLE centros
(
cod_centro VARCHAR (10),
tipo_centro VARCHAR (1),
nombre VARCHAR (1) NOT NULL,
direccion VARCHAR (30),
teléfono VARCHAR (10),
num_plazas NUMERIC (4),
CONSTRAINT pk_cod_centro
PRIMARY KEY (cod_centro),
CONSTRAINT ck_cod_centro_ant
CHECK (SUBSTR (cod_centro,1,3)='IES'),
CONSTRAINT ck_tipo_centro
CHECK (SUBSTR (tipo_centro,1,1) ='S' OR 'P'),
CONSTRAINT ck_teléfono CHECK
(SUBSTR (telefono,1,2) ='95'),
CONSTRAINT ck_num_plazas CHECK
(SUBSTR (num_plazas,1,1)='>o')
);
ALTER TABLE personal ADD
CONSTRAINT ck_fecha
ALTER TABLE personal ADD
CONSTRAINT ck_salario > 0
ALTER TABLE centros ADD CONSTRAINT
ck_num_plazas_defecto
INSERT INTO personal VALUES
('IES4567890','12345678B', 'pepe','conserje','01-11-2012', '1000') ;
INSERT INTO personal VALUES
('123456789A','12345678C', 'juan','profesor','01-11-2013', '2000') ;
INSERT INTO personal VALUES
('IES4567894','123456784', 'luis','director','01-11-2011', '2500') ;
INSERT INTO centros VALUES
('IES4567890','S', 'psur','calle 1','950000122', '100') ;
INSERT INTO centros VALUES
('1214567890','S', 'psur1','calle 2','950000121', '101') ;
INSERT INTO centros VALUES
('IES4567891','A', 'psur2','calle 3','950000123', '102') ;
INSERT INTO centros VALUES
('IES4567895','P', 'psur3','calle 4','950000125', '103') ;
INSERT INTO centros VALUES ('IES4567898','P', 'psur4','calle
5','950000126', '0') ;
No hay comentarios:
Publicar un comentario