FRANCISCO JAVIER (PACHI)PRACTICA 8. VISTAS.

1. Crear una vista simple, llamada ProLSI, que contenga los datos de los profesores del área de ‘Lenguajes y Sistemas Informáticos’ (código ‘LSI’).

CREATE VIEW ProLSI AS SELECT * FROM profesores WHERE area='LSI';

2. Crear una vista compleja, llamada AsigMX23, que contenga las siglas, nombre, creditos y curso de las asignaturas que se imparten en martes (dia=’2’) o miércoles (dia=’3’), en segundo o tercer curso. Evitar que la vista contenga más de una vez la misma asignatura.

CREATE VIEW AsigMX23 AS SELECT DISTINCT asignaturas.siglas, nombre, creditos, asignaturas.curso, diaFROM asignaturas INNER JOIN docencia ON asignaturas.siglas=docencia.siglasWHERE dia IN (2, 3) AND asignaturas.curso IN (2, 3);

faltaba poner el “group by”

CREATE OR REPLACE VIEW AsigMX23 AS SELECT asignaturas.siglas, nombre, creditos, asignaturas.curso, diaFROM asignaturas INNER JOIN docencia ON asignaturas.siglas=docencia.siglasWHERE dia IN (2, 3) AND asignaturas.curso IN (2, 3)GROUP BY nombre;

3. Empleando las vistas anteriores, obtener un listado alfabético de los apellidos y nombre de los profesores del área de ‘Lenguajes y Sistemas Informáticos’ cuya dedicación no es a tiempo completo (TC).

SELECT nombre_pila, apellido1, apellido2FROM ProLSI ORDER BY nombre_pila, apellido1, apellido2;

4. Crear una vista, llamada AreasDep, que contenga el codigo y nombre de cada area y el codigo y nombre del departamento al que pertenece. Renombrar estas columnas con las etiquetas ‘codigo_area’, ‘nombre_area’, ‘codigo_departamento’ y ‘nombre_departamento’ respectivamente.

CREATE VIEW AreasDep AS SELECT areas.codigo "Codigo Area", areas.nombre "Nombre Area", departamentos.codigo "Codigo Dept.", departamentos.nombre "Nombre Dept."FROM areas INNER JOIN departamentos ON areas. departamento=departamentos.codigo;

5. Emplear la vista AreasDep para obtener el número de profesores de cada categoría, que pertenecen a cada una de las areas del departamento cuyo nombre es ‘Informática’. Encabezar el listado con las etiquetas ‘area’, ‘categoria’ y ‘numero_profesores’

a) Hay que cambiar primero algunos elementos de la vista “areasdep”

CREATE OR REPLACE VIEW AreasDep AS SELECT areas.codigo, areas.nombre "Nombre Area", departamentos.codigo "Codigo Dept.", departamentos.nombreFROM areas INNER JOIN departamentos ON areas.departamento=departamentos.codigo;

b) Hacer la consulta con los datos anteriores cambiados

SELECT area, categoria, COUNT(*) "numero_profesores"FROM areasdep INNER JOIN profesores ON profesores.area=areasdep.codigoWHERE nombre='Informática'GROUP BY area;

6. Crear una vista, llamada AsiTron, con la información de las asignaturas troncales (clase=’T’), permitiendo que a través de ella se puedan hacer actualizaciones de asignaturas de cualquier clase (troncales o no).

a) Creamos primero la vista

CREATE OR REPLACE VIEW AsiTron ASSELECT * FROM asignaturasWHERE clase='T';

b) Actulizamos una asignatura cualquiera que sea Troncal

UPDATE asignaturas SET creditos=11 WHERE siglas='AGE'; Inicialmente los creditos para AGE eran 9, ahora seran 11.

c) No se puede actulizar datos a través de la vista AsiTron que no sean Troncales, es imposible.

7. Modificar la vista anterior para que a través de ella sólo se puedan realizar operaciones de actualización sólo si la clase sigue siendo troncal.

CREATE OR REPLACE VIEW AsiTron ASSELECT * FROM asignaturasWHERE clase='T'WITH CHECK OPTION; --> Especifica que solo las filas accesibles para la vista pueden ser insertadas o modificadas. La cláusula WITH CHECK OPTION garantiza que las operaciones INSERT y UPDATErealizadas a través de una vista no pueden crear filas que no serían seleccionadas por lapropia vista.

8. Modificar la vista anterior para que sólo sea de consulta y no pueda hacerse ninguna actualización a través de ella.

a) En principio creo que es asi:

CREATE OR REPLACE VIEW AsiTron ASSELECT * FROM asignaturasWHERE clase='T'WITH READ ONLY;

b) Pero da error.

9. Emplear la vista anterior para crear otra vista, llamada MediaAlumnosTroncales, que muestre la media de alumnos en las asignaturas troncales, para cada curso.

CREATE OR REPLACE VIEW MediaAlumnosTroncales ASSELECT curso, clase, AVG(alumnos) FROM asignaturasWHERE clase='T'GROUP BY curso;

10. Eliminar la vista MediaAlumnosTroncales.

DROP VIEW MediaAlumnosTroncales;