Subconsultas Vistas

1

Tema: SUBCONSULTAS Y CREACION DE VISTAS.

 Definir que es una subsonsulta

 Utilizar vistas para seguridad de los datos

 Computadora con SQL Server 2008.

 Guía Número 8

¿Qué es una subconsulta?

Una subconsulta es una consulta T-SQL normal anidada dentro de otra consulta, se crean utilizando paréntesis en una instrucción SELECT que sirve como base para cualquier parte de los datos o de la condición de otra consulta.

Normalmente las subconsultas se utilizan para satisfacer una o un par de las siguientes necesidades:

 Desglosar una consulta en una serie de pasos lógicos.

 Proporcionar un listado que va a ser el destino de una cláusula WHERE con [IN |

EXISTS |ANY | ALL].

 Proporcionar una búsqueda dirigida por cada registro individual de una consulta principal.

Crear una subconsulta anidada.

Las subconsultas pueden anidarse de forma que una subconsulta aparezca en la cláusula WHERE (por ejemplo) de otra subconsulta que a su vez forma parte de otra consulta principal. En la práctica, una consulta consume mucho más tiempo y memoria cuando se incrementa el número de niveles de anidamiento. La consulta resulta también más difícil de leer , comprender y mantener cuando contiene más de uno o dos niveles de

Facultad: Ingeniería Escuela: Computación Asignatura: Base de datos I

Objetivo

Específico

Materiales

y

Equipo

2

subconsultas.

Ejemplo 1:

SELECT numemp, nombre

FROM empleados

WHERE numemp = (SELECT rep FROM pedidos WHERE clie = (SELECT numclie

FROM clientes WHERE nombre = 'Julia Antequera'))

En este ejemplo, por cada línea de pedido se calcula la subconsulta de clientes, y esto se repite por cada empleado, en el caso de tener 10 filas de empleados y 200 filas de pedidos (tablas realmente pequeñas), la subconsulta más interna se ejecutaría 2000 veces (10 x 200).

Evidentemente, la sintaxis exacta variará, no sólo por sustituir la lista de selección y los nombres exactos de las tablas, sino también porque tenemos uniones de múltiples tablas en la consulta interior, en la exterior o en ambas.

Consultas anidadas utilizando instrucciones SELECT: de un solo valor vamos a practicar un ejemplo explicito.

Supongamos, por ejemplo, que deseamos conocer los ProductID (ID de productos) de todos los artículos vendidos el primer día que se compró cualquier producto desde el sistema.

Si ya sabemos cuál es el primer día en que se insertó un pedido en el sistema, no habrá ningún problema; la consulta sería como la siguiente:

USE NORTHWIND

SELECT DISTINCT (o.OrderDate), od.ProductID

FROM Orders o

INNER JOIN [Order Details] od

ON o.OrderID = od.OrderID

WHERE OrderDate = ' 1996/7/4' --Esta es la primera fecha de un pedido en el sistema

Pero ahora supongamos, por ejemplo, que depuramos regularmente los datos de nuestro sistema y deseamos seguir haciendo la misma pregunta como parte de un informe automatizado. Como se va a automatizar, no podemos ejecutar una consulta para buscar cual es la primera fecha en el sistema e insertarla manualmente en nuestra consulta, lo mejor es automatizar el procedimiento.

USE NORTHWIND

SELECT DISTINCT o.OrderDate, od.ProductID

FROM Orders o

INNER JOIN [Order Details] od

ON o.OrderID = od.OrderID

Base de datos I. Guía 8 3

Consultas anidadas utilizando subconsultas que devuelven: Múltiples valores: La consulta que vamos a realizar en estos momentos es para buscar una lista de todas las tiendas que tienen registros de descuento. Las tiendas se encuentran en una tabla denominada

Stores (Tiendas). Los descuentos están en una tabla denominada discounts

(descuentos).

USE PUBS

SELECT stor_id, stor_name

FROM Stores

WHERE stor_id IN (SELECT stor_id FROM Discounts)

Mezclar tipos de datos: CAST y CONVERT Podrá ver CAST y CONVERT utilizadas con mucha frecuencia. Considerando que vamos a utilizar con frecuencia estas dos funciones éste es el momento ideal para examinarlas con más detalle para saber lo que pueden hacer por nosotros.

Tanto CAST como CONVERT ejecutan conversiones de tipos de datos. En su mayoría, ambas hacen lo mismo, exceptuando a CONVERT que también realiza algunas conversiones de formatos de fecha que CAST no ofrece.

Vamos a examinar la sintaxis de ambas funciones:

CAST (expresión AS tipo-de-datos)

CONVERT (tipo-de-datos, expresión [, estilo])

CAST y CONVERT pueden tratar una amplia variedad de conversiones de tipos de datos que vamos a necesitar cuando SQL Server no lo haga implícitamente por nosotros. Por ejemplo, convertir un número en una cadena es una necesidad muy común.

use Northwind

SELECT 'El cliente ha realizado la siguiente orden' + OrderID FROM Orders

WHERE CustomerID = 'ALFKI'

Nos va a producir un error: Pero si cambiamos el código para que convierta primero el número:

SELECT 'El cliente ha realizado la siguiente orden: ' + CAST (OrderID AS varchar)

FROM Orders

WHERE CustomerID = 'ALFKI'

Observe que con CAST no tenemos el mismo control sobre el formato de fechas que con CONVERT.

Por ejemplo:

4

Es un resultado algo diferente de CAST. De hecho, podríamos haber convertido a uno de los formatos de año de dos dígitos.

SELECT OrderDate, CONVERT(varchar(12), OrderDate, 5) AS “Convertido” FROM Orders

WHERE OrderID = 11050

Para elegir el formato de fecha que deseamos, lo que tenemos que hacer es suministrar un código al final de la función CONVERT. (111 en el ejemplo anterior nos habría proporcionado el estándar japonés, con un año de cuatro dígitos; 5 es el estándar italiano, con un año de dos dígitos). Los códigos de 100 son para años de cuatro dígitos; los códigos menores que 100 (con algunas excepciones) son para años de dos dígitos. Los formatos disponibles se pueden encontrar en el tema CONVERT o CASE de los libros en pantalla de SQL Server.

PERTENENCIA A CONJUNTO IN

Examina si el valor de la expresión es uno de los valores incluidos en la lista de valores

SELECT numemp, nombre, oficina FROM empleados

WHERE oficina IN (12,14,16) Lista los empleados de las oficinas 12, 14 y 16 SELECT numemp, nombre FROM empleados

WHERE (oficina = 12) OR (oficina = 14) OR (oficina = 16)

Obtenemos lo mismo que en el ejemplo anterior. Los paréntesis son opcionales

La subconsulta debe generar una única columna y las filas que sean. Si la subconsulta no produce ninguna fila, el test da falso.

Sintaxis:

SELECT numemp, nombre, oficina FROM empleados

WHERE oficina IN (SELECT oficina FROM oficinas WHERE region = 'este')

Con la subconsulta se obtiene la lista de los números de oficina del este y la consulta principal obtiene los empleados cuyo número de oficina sea uno de los números

de oficina del este.

Por lo tanto lista los empleados de las oficinas del este.

Vistas

Las vistas tiene una tendencia a ser utilizadas mucho o poco: en raras ocasiones se utilizan en su justa medida. Las vistas se podrían utilizar para:

Base de datos I. Guía 8 5

2) Prevenir la selección de columnas confidenciales a la vez que permite el acceso a otros datos importantes En el fondo, una vista no es más que una consulta almacenada. lo extraordinario es que podemos combinar y hacer corresponder datos desde tablas base (o desde otras vistas) para crear lo que, en general, funciona como cualquier otra tabla base.

Ejemplo 1:

USE northwind

CREATE VIEW Categorias AS

SELECT CategoryID, CategoryName, Description FROM Categories

Para llamar a la vista creada: SELECT * FROM Categorias

Ejemplo 2:

CREATE VIEW Vista_Products AS

SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice FROM Products

Para llamar a la vista creada: SELECT * FROM Vista_Products

Si queremos modificar la vista del ejemplo 2 agregándole un nuevo campo, lo podemos hacer con el comando ALTER de la siguiente manera

ALTER VIEW Vista_Products AS

SELECT ProductID, ProductName, QuantityPerUnit,

UnitPrice,UnitsInStock FROM Products

Las vistas se pueden utilizar como tablas. Para eliminar una vista podemos utilizar DROP.

Ejemplo:

DROP VIEW Vista_Products

6

Realice los siguientes ejercicios sin utilizar Join, solo subconsultas.

1. Escriba un script utilizando subconsulta, liste los empleados de la editorial (pub_name) New Moon Books. La base a utilizar es PUBS y las tablas son

employee y publishers.

2. Haga una consulta haciendo uso de subconsultas, que liste el nombre de los procutos (ProductID, ProductName de la tabla product) que tengan una cantidad(quantity de la tabla [Order Details]), de pedido mayor a 115 3. Cree las vistas que contengan las siguientes consultas.

Realizar una consulta que muestre los siguientes campos:

1) de la tabla HumanResources.EmployeeAddress el campo EmployeeID 2) de la tabla Person.Address los campos AddressLine1 y PostalCode

3) de la tabla Person.StateProvince los campos StateProvinceID y nameDe la base de datos AdventureWorks

Realizar una consulta que muestre los siguientes campos:

1) de la tabla Customers el campo CustomerID. 2) de la tabla Orders el campo OrderDate.

3) de la tabla Employees los campos FirstName, LastName y BirthDate de la base de datos Northwind.



Investigar que son los Procedimientos Almacenados en SQL Server.



Investigación

Complementaria

Guía 3

Guía 4

fía

Bibliografía

Guía 3

Guía 4

fía

Procedimiento

Bibliografía

Base de datos I. Guía 8 7

EVALUACION

% 1-4 5-7 8-10 Nota

CONOCIMIENTO Del 20 al 30%

Conocimie nto deficient e de los fundament os teóricos Conocimiento y explicación incompleta de los fundamentos teóricos Conocimiento completo y explicación clara de los fundamentos teóricos APLICACIÓN DEL CONOCIMIENTO Del 40% al 60% ACTITUD Del 15% al 30% No tiene actitud proactiva . Actitud propositiva y con propuestas no aplicables al contenido de la guía. Tiene actitud proactiva y sus propuestas son concretas.

TOTAL 100%

Máquina No: Máquina No: Alumno: Alumno: Docente: Docente: GL: GL: Fecha:

Guía

8:

SUBCONSULTAS

Y

CREACION DE VISTAS

Tema: Presentación del programa

Hoja de cotejo: