1. Generar las siguientes tablas para guardar esta información TABLA ALUMNOS TABLA CURSOS TABLA PROFESOR NOMBRE NOMBRE_CURSO NOMBRE APELLIDO1 CODIGO_CURSO APELLIDO1 APELLIDO2 PROFESOR APELLIDO2 CODIGO_ALUMNO MAXIMO_ALUMNOS DNI DIRECCION FECHA_INICIO DIRECCION SEXO FECHA_FIN TITULO FECHA_NACIMIENTO NUM_HORAS GANA CURSO En las definiciones explicitar las siguientes restricciones: No es posible dar de alta un alumno si no se matricula en un curso. La información del número de horas del curso es imprescindible para almacenarlo. El campo GANA de la tabla PROFESOR no puede estar en ningún caso vacío. Dos cursos no pueden llamarse igual. Lo mismo le pasa a los profesores. Podemos identificar las tuplas de las tablas CURSOS y ALUMNOS mediante el atributo CODIGO correspondiente y en PROFESORES usando el DNI. Cumplir la relación normal entre fecha comienzo y fecha fin (orden cronológico). Los valores para el atributo sexo son sólo M y H (en mayúsculas). Se ha de mantener la regla de integridad de referencia. CREATE TABLE ALUMNOS (CODIGO NUMBER(9), NOMBRE VARCHAR2(20), APELLIDO1 VARCHAR2(20), APELLIDO2 VARCHAR2(20), DIRECCION VARCHAR2(20), SEXO CHAR(1), FECHA_NAC DATE, CURSO NUMBER(9), CONSTRAINT alumnos_pk PRIMARY KEY (CODIGO), CONSTRAINT alumnos_valor_sexo CHECK (SEXO IS NOT NULL AND (SEXO='H' OR SEXO='M')), CONSTRAINT alumnos_curso_vacio CHECK (CURSO IS NOT NULL) ); CREATE TABLE CURSOS ( CODIGO NUMBER(9), NOMBRE VARCHAR2(20), PROFESOR NUMBER(9), MAXIMO_ALUMNOS NUMBER(9), FECHA_INICIO DATE, FECHA_FIN DATE, NUM_HORAS NUMBER(9), CONSTRAINT cursos_n_horas CHECK (NUM_HORAS IS NOT NULL), CONSTRAINT cursos_nombres_distintos UNIQUE (NOMBRE), CONSTRAINT cursos_pk PRIMARY KEY (CODIGO), CONSTRAINT cursos_fechas_compatibles CHECK (FECHA_INICIO < FECHA_FIN) ); CREATE TABLE PROFESORES ( DNI NUMBER(9), NOMBRE VARCHAR2(20), APELLIDO1 VARCHAR2(20), APELLIDO2 VARCHAR2(20), DIRECCION VARCHAR2(20), TITULO VARCHAR2(20), GANA NUMBER(9), CONSTRAINT profesores_gana_vacio CHECK (GANA IS NOT NULL), CONSTRAINT profesores_nombres_distintos UNIQUE (NOMBRE, APELLIDO1, APELLIDO2), CONSTRAINT profesores_pk PRIMARY KEY (DNI) ); ALTER TABLE ALUMNOS ADD (CONSTRAINT alumnos_fk_cursos FOREIGN KEY (CURSO) REFERENCES CURSOS); ALTER TABLE CURSOS ADD (CONSTRAINT cursos_fk_profesores FOREIGN KEY (PROFESOR) REFERENCES PROFESORES); 2.- Insertar las siguientes tuplas. Tabla PROFESOR NOMBRE APELLIDO1 APELLIDO2 DNI DIRECCION TITULO GANA Juan Arch López 32432455 Puerta Negra, 4 Ing. Informática 7500 María Oliva Rubio 43215643 Juan Alfonso 32 Lda. Fil. Inglesa 5400 INSERT INTO PROFESORES VALUES (32432455, 'JUAN', 'ARCH', 'LOPEZ', 'PUERTA NEGRA 4', 'ING INFORMATICA', 7500); INSERT INTO PROFESORES VALUES (43215643, 'MARIA', 'OLIVA', 'RUBIO', 'JUAN ALFONSO 32', 'FIL INGLESA', 5400); Tabla CURSO NOMBRE_CURSO CODIGO COD_PROF MAX_ALU FECH_INI FECH_FIN NUM_HORAS Inglés Básico 1 43215642 15 1 nov 2000 22 dic 2000 120 Admins. Linux 2 32432455 1 sept 2000 80 INSERT INTO CURSOS VALUES (1, 'INGLES BASICO', 43215643, 15, '1-11-2000', '22-12-2000', 120); INSERT INTO CURSOS VALUES (2, 'ADMINS LINUX', 32432455, NULL, '1-9-2000', NULL, 80); Tabla ALUMNO NOMBRE APELLIDO1 APELLIDO2 CODIGO DIRECC SEXO FECHA_NAC CURSO Lucas Manilva López 1 Alamar, 3 V 1 nov 1980 1 Antonia López Alcántara 2 Juan Maniquí, 32 M 2 Manuel Alcántara Pedrós 3 Julián, 2 2 José Pérez Caballar 4 Jarcha, 5 V 3 feb 1977 1 INSERT INTO ALUMNOS VALUES (1, 'LUCAS', 'MANILVA', 'LOPEZ', 'ALAMAR, 3', 'H', '1-9-1980', 1); INSERT INTO ALUMNOS VALUES (2, 'ANTONIA', 'LOPEZ', 'ALCANTARA', 'JUAN MANIQUI, 32', 'M', NULL, 2); INSERT INTO ALUMNOS VALUES (3, 'MANUEL', 'ALCANTARA', 'PEDROS', 'JULIAN, 2', 'H', NULL, 2); INSERT INTO ALUMNOS VALUES (4, 'JOSE', 'PEREZ', 'CABALLAR', 'JARCHA, 5', 'H', '3-2-1977', 1); 3.- Insertar la siguiente tupla en la tabla alumnos: NOMBRE APELLIDO1 APELLIDO2 CODIGO DIRECC SEXO FECHA_NAC CURSO Sergio Navas Retal 1 P ------------> NO LO HAGO PQ VA A PETAR 4.- Añadir el campo edad de tipo numércio a la tabla profesor. ALTER TABLE PROFESORES ADD (EDAD NUMBER(9)); 5.- Añadir las siguientes restricciones: La edad de los profesores está entre 18 y 65 años. No se puede añadir un curso si su número de alumnos máximo es menor que 10. El número de horas de los cursos debe ser mayor que 100. UPDATE PROFESORES SET EDAD=25 WHERE DNI=32432455; UPDATE PROFESORES SET EDAD=40 WHERE DNI=43215643; ALTER TABLE PROFESORES ADD (CONSTRAINT profesores_edad_limitada CHECK (EDAD IS NOT NULL AND (EDAD >= 18 AND EDAD <= 65))); ALTER TABLE CURSOS ADD (CONSTRAINT cursos_numero_minimo_plazas CHECK (MAXIMO_ALUMNOS >= 10)); UPDATE CURSOS SET NUM_HORAS=150 WHERE CODIGO=2; ALTER TABLE CURSOS ADD (CONSTRAINT cursos_numero_minimo_horas CHECK (NUM_HORAS >= 100)); 6.- Eliminar la restricción que controla los valores permitidos para el atributo sexo. ALTER TABLE ALUMNOS MODIFY CONSTRAINT alumnos_valor_sexo DISABLE; 7.- Eliminar la restricción de tipo NOT NULL del atributo GANA. ALTER TABLE PROFESORES DROP CONSTRAINT profesores_gana_vacio; 8.- Modificar el tipo de datos de DIRECCION a cadena de caracteres de 40 como máximo y el del atributo DNI para poder introducir la letra del NIF ALTER TABLE PROFESORES MODIFY (DIRECCION VARCHAR2(40)); ALTER TABLE PROFESORES MODIFY (DNI VARCHAR2(10)); 9.- Insertar restricción no nula en el campo FECHA_INICO de CURSOS. ALTER TABLE CURSOS ADD (CONSTRAINT cursos_finicio_nonulo CHECK (FECHA_INICIO IS NOT NULL)); 10.- Insertar un nuevo campo RENTABLE que indica si un curso es rentable. Sólo tiene dos valores SI/NO y el valor por defecto es NO. ¿Qué ocurre con los valores de las tuplas anteriores? ALTER TABLE CURSOS ADD (RENTABLE CHAR(1) DEFAULT 'N'); ALTER TABLE CURSOS ADD (CONSTRAINT cursos_valor_rentable CHECK (RENTABLE IS NOT NULL AND (RENTABLE='S' OR RENTABLE='N'))); 11.- Poner que si un curso tiene más de 150 horas, entonces debe darse en un periodo no inferior a seis meses. ALTER TABLE CURSOS ADD (CONSTRAINT cursos_masde150horas_6meses CHECK ((NUM_HORAS <= 150) OR (NUM_HORAS > 150 AND MONTHS_BETWEEN(FECHA_INICIO,FECHA_FIN) >= 6))); 12.- Cambiar la clave primaria de Profesor al nombre y apellidos. ALTER TABLE PROFESORES DROP CONSTRAINT profesores_pk; ALTER TABLE PROFESORES ADD (CONSTRAINT profesores_pk PRIMARY KEY (NOMBRE, APELLIDO1, APELLIDO2)); 13.- Insertar las siguientes tuplas: NOMBRE APELLIDO1 APELLIDO2 CODIGO DIRECC SEXO FECHA_NAC CURSO María Jaén Sevilla 5 Martos, 5 M 10 marzo 1979 3 INSERT INTO ALUMNOS VALUES (5, 'MARIA', 'JAEN', 'SEVILLA', 'MARTOS, 5', 'M', '10-3-1979', 3); 14. -La fecha de nacimiento de Antonia López está equivocada. La verdadera es 23 de diciembre de 1976. UPDATE ALUMNOS SET FECHA_NAC='23-12-1976' WHERE (UPPER(NOMBRE)='ANTONIA' AND UPPER(APELLIDO1)='LOPEZ'); 15.- Cambiar a Antonia López al curso de código 5. UPDATE ALUMNOS SET CURSO='5' WHERE (UPPER(NOMBRE)='ANTONIA' AND UPPER(APELLIDO1)='LOPEZ'); 16.- Eliminar la profesora Laura Jiménez. DELETE FROM PROFESORES WHERE UPPER(NOMBRE)='LAURA' AND UPPER(APELLIDO1)='JIMENEZ'; 17.- Crear una tabla NOMBRE DE ALUMNOS que tenga un solo atributo (NOMBRE_COMPLETO) de tipo cadena de caracteres y con el contenido de la tabla alumnos en esos campos. CREATE TABLE NOMBRE_ALUMNOS (NOMBRE_COMPLETO) AS SELECT NOMBRE||' '||APELLIDO1||' '||APELLIDO2 FROM ALUMNOS; ---------create view Alumnos as select alumnos from otra tabla; 18.- Borrar las tablas DROP TABLE ALUMNOS; DROP TABLE CURSOS; DROP TABLE PROFESORES; DROP TABLE NOMBRE_ALUMNOS; ********************** ******************************************** ******************************************** ******************************************** ******************************************** ******************************************** ******************************************** ******************************************** ******************************************** ******************************************** ******************************************** ******************************************** ******************************************** ******************************************** ******************************************** ******************************************** ******************************************** ********************** 1.- Se desea tener la información relativa a las tablas del sistema de forma más amigable y completa. De este modo se pide que se generen las siguientes vistas: Nombre de la vista Informacion_tablas ---Atributos Nombre_tabla: Nombre dado por el usuario a la tabla Esquema: Nombre del esquema donde está la tabla Num_cons_Check: Número de restricciones de tipo CHECK Num_cons_Unique: Número de claves candidatas Num_columnas: Número de atributos en la tabla Primaria: Dice si la tabla tiene definida una clave primaria CREATE OR REPLACE VIEW INFO_TABLAS_CONS_CHECK (NOMBRE_TABLA, NUM_CONS_CHECK) AS ( SELECT UT.TABLE_NAME, COUNT(*) FROM USER_TABLES UT, USER_CONSTRAINTS UC WHERE UT.TABLE_NAME = UC.TABLE_NAME AND UC.CONSTRAINT_TYPE = 'C' GROUP BY UT.TABLE_NAME ); CREATE OR REPLACE VIEW INFO_TABLAS_CONS_UNIQUE (NOMBRE_TABLA, NUM_CONS_UNIQUE) AS ( SELECT UT.TABLE_NAME, COUNT(*) FROM USER_TABLES UT, USER_CONSTRAINTS UC WHERE UT.TABLE_NAME = UC.TABLE_NAME AND UC.CONSTRAINT_TYPE = 'U' GROUP BY UT.TABLE_NAME ); CREATE OR REPLACE VIEW INFO_TABLAS_N_COLUMNAS (NOMBRE_TABLA, NUM_COLUMNAS) AS ( SELECT UT.TABLE_NAME, COUNT(*) FROM USER_TABLES UT, USER_TAB_COLUMNS UTC WHERE UT.TABLE_NAME = UTC.TABLE_NAME GROUP BY UT.TABLE_NAME ); CREATE OR REPLACE VIEW INFORMACION_TABLAS (NOMBRE_TABLA, NUM_CONS_CHECK, NUM_CONS_UNIQUE, NUM_COLUMNAS) AS ( SELECT CH.NOMBRE_TABLA, CH.NUM_CONS_CHECK, UN.NUM_CONS_UNIQUE, NC.NUM_COLUMNAS FROM INFO_TABLAS_CONS_CHECK CH, INFO_TABLAS_CONS_UNIQUE UN, INFO_TABLAS_N_COLUMNAS NC WHERE CH.NOMBRE_TABLA = UN.NOMBRE_TABLA AND CH.NOMBRE_TABLA = NC.NOMBRE_TABLA GROUP BY CH.NOMBRE_TABLA, CH.NUM_CONS_CHECK, UN.NUM_CONS_UNIQUE, NC.NUM_COLUMNAS ); SELECT * FROM INFORMACION_TABLAS; ---Nombre de la vista Informacion_Primary Atributos Atributo: Atributo que forma parte de una clave primaria Nombre_Tabla: Tabla a la que pertenece ese atributo como parte de la primaria Nombre_Restriccion: Nombre dado por el usuario a la restricción de clave primaria Estado: Dice si la restricción está ACTIVA o INACTIVA CREATE OR REPLACE VIEW INFO_PRIMARY (ATRIBUTO, TABLA, NOMBRE_CONSTRAINT, ESTADO) AS ( SELECT UTC.COLUMN_NAME, UC.TABLE_NAME, UC.CONSTRAINT_NAME, UC.STATUS FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UTC WHERE UC.CONSTRAINT_NAME = UTC.CONSTRAINT_NAME AND UC.CONSTRAINT_TYPE = 'P' GROUP BY UTC.COLUMN_NAME, UC.TABLE_NAME, UC.CONSTRAINT_NAME, UC.STATUS ); SELECT * FROM INFO_PRIMARY; ---Nombre de la vista Informacion_Foreign Atributos Nombre_Tabla: Tabla a la que pertenece la restricción de clave foránea Nombre_Restriccion: Nombre dado por el usuario a la restricción de clave foránea Nombre_Tabla_Destino: Nombre de la tabla hacia donde hace referencia la restricción Estado: Dice si la restricción está ACTIVA o INACTIVA CREATE OR REPLACE VIEW INFO_FOREIGN (ATRIBUTO, TABLA, NOMBRE_CONSTRAINT, ESTADO) AS ( SELECT UTC.COLUMN_NAME, UC.TABLE_NAME, UC.CONSTRAINT_NAME, UC.STATUS FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UTC WHERE UC.CONSTRAINT_NAME = UTC.CONSTRAINT_NAME AND UC.CONSTRAINT_TYPE = 'F' GROUP BY UTC.COLUMN_NAME, UC.TABLE_NAME, UC.CONSTRAINT_NAME, UC.STATUS ); SELECT * FROM INFO_FOREIGN; ********************** ******************************************** ******************************************** ******************************************** ******************************************** ******************************************** ******************************************** ******************************************** ******************************************** ******************************************** ******************************************** ******************************************** ******************************************** ******************************************** ******************************************** ******************************************** ******************************************** ********************** -------------------------------------------------------------------------------- 1 Ver qué tablas tenemos: -------------------------------------------------------------------------------- SQL> select table_name from user_tables; TABLE_NAME ------------------------------ BONUS DEPT EMP SALGRADE SQL> describe EMP Name Null? Type ------------------------ ----------------- ---------------------------- EMPNO NOT NULL NUMBER(4) ENAME NOT NULL VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NOT NULL NUMBER(2) -------------------------------------------------------------------------------- 2 Ver qué constraints tenemos -------------------------------------------------------------------------------- SQL> describe user_constraints Nombre ¿Nulo? Tipo -------------------------------------- --------------- ---------------------------- OWNER NOT NULL VARCHAR2(30) CONSTRAINT_NAME NOT NULL VARCHAR2(30) CONSTRAINT_TYPE VARCHAR2(1) TABLE_NAME NOT NULL VARCHAR2(30) SEARCH_CONDITION LONG R_OWNER VARCHAR2(30) R_CONSTRAINT_NAME VARCHAR2(30) DELETE_RULE VARCHAR2(9) ? SQL> select constraint_name, constraint_type, r_constraint_name from user_constraints where table_name='EMP'; CONSTRAINT_NAME C R_CONSTRAINT_NAME ------------------------------ - ------------------------------ SYS_C0080915 C SYS_C0080916 C EMP_EMPNO_PK P EMP_MGR_FK R EMP_EMPNO_PK EMP_DEPTNO_FK R DEPT_PRIMARY_KEY (tipo P= clave primaria, R= referencial, C=Check o Not Null, U=Unique ) Column search_condition format a25; (para que no ocupe tanto ancho) SQL> select constraint_name, search_condition from user_constraints where table_name='EMP'; CONSTRAINT_NAME SEARCH_CONDITION ---------------- -------------------------------- SYS_C0080915 "EMPNO" IS NOT NULL SYS_C0080916 "DEPTNO" IS NOT NULL EMP_DEPTNO_FK Observar que la restricción EMP_DEPTNO_FK tiene nulo el campo SEARCH_CONDITION porque no pusimos en la consulta WHERE Constraint_Type = 'C', con lo que sólo hubíeramos sacado las de tipo CHECK. -------------------------------------------------------------------------------- 3 Ver sobre qué columnas actúan las constraints: -------------------------------------------------------------------------------- SQL> describe user_cons_columns Nombre ¿Nulo? Tipo -------------------------------- ----------- ---------------------------- OWNER NOT NULL VARCHAR2(30) CONSTRAINT_NAME NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME VARCHAR2(4000) POSITION NUMBER SQL> select table_name, column_name from user_cons_columns where constraint_name='FK_DEPTNO'; TABLE_NAME COLUMN_NAME -------------------- ---------------------------------------------------------------- EMP DEPTNO -------------------------------------------------------------------------------- 4 Borrando constraints: Borrar la clave primaria: -------------------------------------------------------------------------------- SQL> alter table emp drop constraint emp_empno_pk cascade constraints; -------------------------------------------------------------------------------- 5 Añadiendo constraints: Añadir de nuevo la clave primaria -------------------------------------------------------------------------------- SQL> alter table emp add (constraint claveprim primary key (empno)); -------------------------------------------------------------------------------- 6 Borrando constraints: Borrar la claves externas: -------------------------------------------------------------------------------- SQL> alter table emp drop constraint emp_deptno_fk; -------------------------------------------------------------------------------- 7 Añadiendo constraints: Añadir de nuevo la referencia a departamentos: -------------------------------------------------------------------------------- SQL> alter table emp add (constraint claveexterna foreign key (deptno) references dept(deptno)); -------------------------------------------------------------------------------- 8 Modificar una columna para que su valor no pueda ser nulo: -------------------------------------------------------------------------------- SQL> alter table emp modify (ename not null); -------------------------------------------------------------------------------- 9 Hacer una copia de la tabla: -------------------------------------------------------------------------------- SQL> create table COPIA as select * from emp; -------------------------------------------------------------------------------- 10 Borrar una columna: -------------------------------------------------------------------------------- SQL> alter table COPIA drop column COMM; -------------------------------------------------------------------------------- 11 Añadir una columna: -------------------------------------------------------------------------------- SQL> alter table COPIA add (MIA varchar2(3)); -------------------------------------------------------------------------------- 12 Comentar una tabla: -------------------------------------------------------------------------------- SQL> comment on table COPIA is 'tabla de prueba'; -------------------------------------------------------------------------------- 13 Crear una nueva tabla: -------------------------------------------------------------------------------- SQL> create table CONTADOR ( c number(2) primary key); -------------------------------------------------------------------------------- 14 Crear un trigger a la tabla emp para que cuando se borre un empleado, sume uno a la tabla contador: -------------------------------------------------------------------------------- SQL> create trigger cuenta 2 after insert on emp 3 begin 4 update contador set c = c + 1; 5 end cuenta; 6 / Este trigger sólo se efectúa una vez, aún cuando el insert original haya producido una inserción múltiple (por ejemplo el que aparece en el paso 9). Se aumenta el contador en 1. SQL> create trigger cuenta 2 after insert on emp 3 for each row 4 begin 5 update contador set c = c + 1; 6 end cuenta; 7 / Este trigger se efectúa tantas veces como tuplas se haya insertado. Se aumenta el contador en el número de tuplas insertado. -------------------------------------------------------------------------------- 15 Borrar una tabla: -------------------------------------------------------------------------------- Drop table COPIA; -------------------------------------------------------------------------------- 16 Crear una vista que contenga nombre de empleado y nombre de su departamento -------------------------------------------------------------------------------- SQL> create view empdep (empleado, departamento) as 2 select e.ename, d.dname 3 from emp e, dept d 4 where e.deptno = d.deptno; -------------------------------------------------------------------------------- 17 Ver el contenido de la vista -------------------------------------------------------------------------------- SQL> select text from user_views where view_name='EMPDEP';