Creación de una Vista Compleja

Creamos una vista compleja de nombres de departamento, salarios mínimos y salarios máximos por departamento.
Observe que se han especificado nombres alternativos para la vista.
Esto es un requisito si alguna columna de la vista se deriva de una función o una expresión.

Ejemplo:

• Cree una vista compleja que contenga el nombre de departamento, 2 funciones de grupo para visualizar los valores mínimo y máximo de dos tablas.

CREATE VIEW v_dept_sum_avg
(Nombre, Salario_min, Salario_ max)
AS
SELECT d.dname, MIN(e.sal), MAX(e.sal)
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname;

http://liang.armstrong.edu:5560/isqlplus

Veamos su estructura:

Describe v_dept_sum_avg

Ahora veamos los datos:

Select *
From v_dept_sum_avg;

Modificación de una Vista

Con la opción OR REPLACE, se puede crear una vista incluso si ya existe otra con el mismo nombre, sustituyendo así la versión antigua de la vista para su propietario.
Esto significa que la vista se puede modificar sin necesidad de borrar, volver a crear u otorgar, de nuevo, privilegios de objeto.

Ejemplo:

• Crear una vista de los empleados del departamento 20, que tenga como cabeceras de columnas codigo, nombre, salario y departamento.

CREATE OR REPLACE VIEW v_emp20
(Codigo, Nombre, Salario, Departamento)
AS SELECT empno, ename, sal, deptno
FROM emp
WHERE deptno = 20;

http://liang.armstrong.edu:5560/isqlplus

Veamos los datos:

Select *
From v_emp20;

Debe recordar que:
Cuando asigne alias de columna en la cláusula CREATE VIEW, recuerde que los alias se enumeran en el mismo orden que las columnas de la subconsulta.

Vistas : Simples y Complejas

Una vista es una representación lógica de una o más tablas. Una vista no contiene datos. Todos los datos son derivados de una o más tablas subyacentes.

Para qué sirve la vista:

• Para restringir el acceso a los datos
• Para simplificar las consultas complejas
• Para proporcionar independencia de datos
• Para presentar diferentes vistas de los mismos datos

Vistas Simples y Vistas Complejas
Existen dos clasificaciones para vistas: simples y complejas.
La diferencia básica radica en las operaciones DML (INSERT, UPDATE y DELETE).

Una vista simple:

• Deriva sus datos de sólo una tabla.
• No contiene funciones ni grupos de datos.
• Puede realizar operaciones DML a través de la vista.

Una vista compleja:

• Genera sus datos de muchas tablas.
• Contiene funciones o grupos de datos.
• No siempre permite operaciones DML a través de la vista.
  

Sintaxis:
CREATE [OR REPLACE] [FORCENOFORCE]
VIEW nombre_vista [(alias, [alias]…)]
AS sentencia_select
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];


Debe recordar que:
Una vez que la vista es creada, se pueden listar las columnas de la vista usando el comando DESCRIBE, como si fuera una tabla.
La información acerca de las vistas en la base de datos son almacenados en el diccionario de datos en la vista USER_VIEWS.

Ejemplo:

• Crear una vista que muestre el número, nombre, cargo, fecha de ingreso y salario de los empleados que tengan el cargo de analistas (ANALYST).

CREATE OR REPLACE VIEW v_analistas
AS
(SELECT empno, ename, job, hiredate, sal
FROM emp
WHERE job='ANALYST');

http://liang.armstrong.edu:5560/isqlplus

Veamos sus datos:

SELECT * FROM v_analistas;

Ejemplo:

• Cree una vista cuyas columnas sean numero de empleado, nombre y el campo calculado sal*12, para todos los empleados del departamento 20, utilizando alias de columnas en la subconsulta.

CREATE VIEW V_sal20
AS SELECT empno Codigo, ename Nombre,
sal*12 Sal_anual
FROM emp
WHERE deptno = 20;

Veamos su estructura:

DESCRIBE V_SAL20

Veamos sus datos:

SELECT * FROM v_sal20;

Borrar una tabla

El comando DROP TABLE permite borrar una tabla.

Ejemplo:

• Borre la tabla dpto.

DROP TABLE dpto;

http://liang.armstrong.edu:5560/isqlplus

Renombrar una tabla

El comando RENAME permite renombrar una tabla

Sintaxis:
RENAME nombre_tabla TO nombre_nuevo

Ejemplo:

• Renombre la tabla dept1 a dpto. (Obviamente, primero hay que crear la Tabla dept1).

RENAME dept1 TO dpto;

http://liang.armstrong.edu:5560/isqlplus

Eliminar Columnas

Sintaxis:
ALTER TABLE nombre_tabla
DROP COLUMN nombre_columna;

Ejemplo:

• Elimine la columna telefono de la tabla emp1;

ALTER TABLE emp1
DROP COLUMN telefono;

http://liang.armstrong.edu:5560/isqlplus

Ejercicios : Filtro de Filas

1) Cree una consulta para mostrar el apellido, el cargo y el salario de los empleados que ganan más de 2000.
http://liang.armstrong.edu:5560/isqlplus

2) Cree una consulta para mostrar el apellido del empleado, el cargo y el número de departamento para el número de empleado 7788.

3) Mostrar el apellido, el cargo y el salario para todos los empleados cuyos salarios no están comprendidos entre 1000 y 3000.

4) Muestre el apellido del empleado, el cargo, el director y la fecha de inicio de los empleados contratados entre el 20 de febrero de 1981 y el 1 de mayo de 1982. Ordene la consulta en orden ascendente por fecha de inicio.

5) Muestre el apellido, el cargo y el número de departamento de todos los empleados de los departamentos 10 y 20 en orden alfabético por apellido.

6) Muestre el apellido, el cargo y el salario de los empleados que ganan entre 1000 y 3000, y están en el departamento 10 ó 20. Etiquete las columnas con Empleado y Salario Mensual, respectivamente.

7) Muestre el apellido, el cargo y la fecha de contratación de todos los empleados contratados en 1980.

8) Muestre el apellido, el salario y el cargo de todos los empleados que no tienen director.

9) Muestre el apellido, el cargo, el salario y la comisión para todos los empleados que ganan comisiones. Ordene los datos en orden descendente de salarios y comisiones.

10) Muestre el apellido de todos los empleados que tengan la c como segunda letra.

11) Muestre el apellido de todos los empleados que tengan una i y una o en el apellido.

12) Muestre el apellido de todos los empleados que tengan un apóstrofe (') en el apellido.

13) Muestre el apellido, el cargo, el director y el salario de todos los empleados cuyos cargos(job) sean ventas(SALES) o cajeros(CLERK) y cuyos salario no sean iguales a 2500, 3000 ni 3500.