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 [Link] = [Link] --[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, [Link], CompanyName, OrderDate
FROM Customers C LEFT JOIN Orders O ON [Link] = [Link]
--[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, [Link], CompanyName, OrderDate --[830]
FROM Customers C RIGHT JOIN Orders O ON [Link] =
[Link]
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, [Link], CompanyName, OrderDate --[831]
FROM Customers C RIGHT JOIN Orders O ON [Link] =
[Link]
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, [Link], CompanyName, OrderDate --[833]
FROM Customers C FULL JOIN Orders O ON [Link] = [Link]
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 <view_attribute> [ ,...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
[Link]
[Link]
[Link]
[Link]
view-transact-sql?view=sql-server-2017