• Author / Uploaded
• Benjamin Anguiano Morales

Citation preview

UNIVERSIDAD NACIONAL AUTONOMA DE MEXICO

FACULTAD DE INGENIERIA

LABORATORIO DE BASES DE DATOS

PRACTICA 11 Utilización de distintos tipos de JOIN, subconsultas y vistas

GRUPO: 04

ALUMNO: ANGUIANO MORALES BENJAMIN

OBJETIVO El alumno pondrá en práctica el uso de los distintos tipos de JOIN, realizará diferentes tipos de subconsultas y creará vistas.

INTRODUCCION JOIN La operación JOIN o combinación permite mostrar columnas de varias tablas como si se tratase de una sola tabla, combinando entre sí los registros relacionados usando para ello claves externas. INNER JOIN Devuelve únicamente aquellos registros/filas que tienen valores idénticos en los dos campos que se comparan para unir ambas tablas. Es decir, aquellas que tienen elementos en las dos tablas, identificados éstos por el campo de relación. En este caso se devuelven los registros que tienen nexo de unión en ambas tablas. Por ejemplo, en la relación entre las tablas de clientes y pedidos en Northwind, se devolverán los registros de todos los clientes que tengan al menos un pedido, relacionándolos por el ID de cliente.

SELECT COUNT(*) FROM Customers --[91] SELECT COUNT(*) FROM Orders --[830]

SELECT COUNT(*) FROM Customers, Orders --[75621]

SELECT * FROM Customers C INNER JOIN Orders O ON O.CustomerID = C.CustomerID --[830]

LEFT JOIN Se obtienen todas las filas de la tabla colocada a la izquierda, aunque no tengan correspondencia en la tabla de la derecha.

SELECT OrderID, C.CustomerID, CompanyName, OrderDate FROM Customers C LEFT JOIN Orders O ON C.CustomerID = O.CustomerID --[832]

RIGHT JOIN Análogamente, usando RIGHT JOIN se obtienen todas las filas de la tabla de la derecha, aunque no tengan correspondencia en la tabla de la izquierda. Si en nuestra base de datos de ejemplo queremos obtener todos los pedidos, aunque no tengan cliente asociado, junto a los datos de dichos clientes, escribiríamos:

SELECT OrderID, C.CustomerID, CompanyName, OrderDate

--[830]

FROM Customers C RIGHT JOIN Orders O ON C.CustomerID = O.CustomerID

En este caso se devuelven 830 registros que son todos los pedidos. Si hubiese algún pedido con el CustomerID vacío (nulo) se devolvería también en esta consulta (es decir, órdenes sin clientes), aunque en la base de datos de ejemplo no se da el caso.

INSERT INTO Orders VALUES(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL)

SELECT OrderID, C.CustomerID, CompanyName, OrderDate FROM Customers C RIGHT JOIN Orders O ON C.CustomerID = O.CustomerID

--[831]

FULL JOIN Se obtienen todas las filas en ambas tablas, aunque no tengan correspondencia en la otra tabla. Es decir, todos los registros de A y de B aunque no haya correspondencia entre ellos, rellenando con nulos los campos que falten:

Es equivalente a obtener los registros comunes (con un INNER) y luego añadirle los de la tabla A que no tienen correspondencia en la tabla B, con los campos de la tabla vacíos, y los registros de la tabla B que no tienen correspondencia en la tabla A, con los campos de la tabla A vacíos. SELECT OrderID, C.CustomerID, CompanyName, OrderDate

--[833]

FROM Customers C FULL JOIN Orders O ON C.CustomerID = O.CustomerID

VISTAS Una vista crea una tabla virtual cuyo contenido (columnas y filas) se define mediante una consulta. Una vista se puede utilizar para lo siguiente: 

Para centrar, simplificar y personalizar la percepción de la base de datos para cada usuario.



Como mecanismo de seguridad, que permite a los usuarios obtener acceso a los datos por medio de la vista, pero no les conceden el permiso de obtener acceso directo a las tablas base subyacentes de la vista.



Para proporcionar una interfaz compatible con versiones anteriores para emular una tabla cuyo esquema ha cambiado.

Las vistas tienen un alcance global. Una vez que se crea una vista con CREATE VIEW, es accesible para todas las partes de la aplicación (4D remoto vía SQL, bases externas creadas con el comando CREATE DATABASE, otras bases utilizan el comando SQL LOGIN, etc.) y durante la sesión, hasta que se borra utilizando el comando DROP VIEW o se cierre la base.

CREACIÓN DE UNA VISTA



FORCE: Crea la vista sin importar que la tabla base exista o no.



WITH CHECK OPTION: Especifica que solamente las filas accesibles a la vista pueden ser insertadas o actualizadas.



CONSTRAINT: Nombre asignado a la restricción CHECK OPTION.



WITH READ ONLY: Asegura que ninguna operación DML pueda realizarse sobre esta vista.

MODIFICACION DE UNA VISTA Modifica una vista creada anteriormente. Esto incluye una vista indizada. ALTER VIEW no afecta a desencadenadores ni procedimientos almacenados dependientes y no cambia permisos. ALTER VIEW [ schema_name . ] view_name [ ( column [ ,...n ] ) ] [ WITH [ ,...n ] ] AS select_statement [ WITH CHECK OPTION ] [ ; ]

  

schema_name: Es el nombre del esquema al que pertenece la vista. view_name: Es la vista que se va a cambiar. Column: Es el nombre de una o más columnas, separadas por comas, que van a formar parte de la vista especificada.

ELIMINACIÓN DE UNA VISTA



Al borrar una vista no perderá los datos, porque la vista está basada en tablas subyacentes de la B.D.



Únicamente el creador o un usuario con el privilegio DROP ANY VIEW puede eliminar una vista.

SELECT * FROM Products --[77]

CREATE VIEW vwProducts AS SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice FROM Products

SELECT * FROM vwProducts --[77]

ACTIVIDADES PARA REALIZAR 1. A partir del script proporcionado de una base de datos ejemplo, realice subconsultas con distintos tipos de operadores en la cláusula SELECT, FROM, WHERE, HAVING y JOIN, además de utilizar funciones de agregación. 2. Consultas que ejemplifiquen los distintos tipos de JOIN, por ejemplo: INNER JOIN, NATURAL JOIN y la cláusula USING; LEFT, RIGHT y FULL OUTER JOIN; CROSS JOIN, SELF JOIN. 3. Diferentes tipos de vistas empleando las consultas anteriores.

ACTIVIDADES

Utilizando la base de datos Northwind, obtener:

1. Obtener el nombre y apellido en una sólo columna de los empleados y el número de empleados que cada uno de estos tiene a su cargo. [2]

2. Crear una vista con los Productos descontinuados que contenga Código del producto, Nombre del producto, Precio y Stock [8]

3. Obtener el nombre de empleado, el número de pedidos tramitado por cada empleado, de aquellos empleados que han tramitado más de 15 pedidos. [9]

4. Crear una vista de los Clientes que hicieron compras con importes mayores a 2000 que contenga el Código del Cliente, la Compañía del Cliente, Dirección, País y Total comprado [5]

5. Obtener el código de Territorio, nombre del Territorio con su código de Región y nombre de la región de los Territorios que no tienen asignado un Empleado [4]

CONCLUSIONES Es bastante importante tener en cuenta como es que funciona cada sentencia de JOIN y cada tipo de estos ya que a pesar de que suenan intuitivos puedes obtener un resultado completamente diferente al esperado. El utilizar las vistas claramente nos ayudara a futuro ya que son bastante útiles y fáciles de crear y usar.

BIBLIOGRAFIA  Manual de practicas  https://doc.4d.com/4Dv15/4D/15/CREATE-VIEW.3002288122.es.html  http://www.mundoracle.com/vistas.html?Pg=sql_plsql_9.htm  https://docs.microsoft.com/es-mx/sql/t-sql/statements/alterview-transact-sql?view=sql-server-2017