consultas avanzadas sqlserver_01

[Consultas Avanzadas Sql Server] Administracin de Base de Datos

Pgina 1

EMPLEO DE ALIAS EN LOS NOMBRES DE LAS TABLAS

El uso de alias en los nombres de tablas mejora la legibilidad de las secuencias de comandos, facilita la escritura de combinaciones complejas y simplifica el mantenimiento de Transact-SQL. Al escribir secuencias de comandos, puede sustituir un nombre de tabla descriptivo largo y complejo por un alias sencillo y abreviado. El alias se utiliza en lugar del nombre completo de la tabla.

Sintaxis parcial

SELECT * FROM servidor.baseDeDatos.esquema.tabla AS aliasTabla

Ejemplo: En este ejemplo se muestra el cdigo del cliente, el nombre de la compaa, la direccin y el pas de procedencia de los clientes. Este ejemplo utiliza alias en los campos seleccionados. Transact-SQL:

use nwind

go

select c.IdCliente, c.NombreCompaa,c.Direccin,c.Pas from

dbo.Clientes as c Resultado:

COMBINACIN DE DATOS DE VARIAS TABLAS

Una combinacin es una operacin que permite consultar dos o ms tablas para producir un conjunto de resultados que incorpore filas y columnas de cada una de las tablas. Las tablas se combinan en funcin de las columnas que son comunes a ambas tablas. Cuando se combinan tablas, Microsoft SQL Server 2008 compara los valores de las columnas especificadas fila por fila y, despus, utiliza los resultados de la comparacin para combinar los valores que cumplan los criterios especificados en nuevas filas. Las tablas se combinan para producir un nico conjunto de resultados que incorpore filas y columnas de dos o ms tablas.

Sintaxis parcial

SELECT columna [, columna ] FROM {} [, ...n] ::= [ INNER | { { LEFT | RIGHT | FULL } [OUTER] } ] [ ]


Pgina 2

JOIN ::= ON | CROSS JOIN |

Hay tres tipos de combinaciones: combinaciones internas, combinaciones externas y combinaciones cruzadas. Adicionalmente, en una instruccin SELECT se pueden combinar ms de dos tablas mediante un conjunto de combinaciones o se puede combinar una tabla consigo misma mediante una autocombinacin.

Una combinacin permite seleccionar columnas de varias tablas al expandir la clusula FROM de la instruccin SELECT. En la clusula FROM se incluyen dos palabras clave adicionales: JOIN y ON.

La palabra clave JOIN especifica qu tablas se van a combinar y cmo.

La palabra clave ON especifica las columnas que las tablas tienen en comn.

Consultas de dos o ms tablas para producir un conjunto de resultados

Una combinacin permite consultar dos o ms tablas para producir un nico conjunto de resultados. Al implementar combinaciones, tenga en cuenta los siguientes hechos e instrucciones:

Especifique la condicin de combinacin en funcin de claves principales y externas.

Si una tabla tiene una clave principal compuesta, cuando combine tablas debe hacer referencia a toda la clave en la clusula ON.

Para combinar tablas, utilice columnas comunes a las tablas especificadas. Dichas columnas deben tener tipos de datos iguales o similares.

Haga referencia al nombre de la tabla si las columnas de las tablas que va a combinar tienen el mismo nombre. Califique los nombres de las columnas con el formato tabla.columna.

Limite el nmero de tablas de las combinaciones porque cuantas ms tablas combine, mayor ser la duracin del proceso de la consulta.

Puede incluir varias combinaciones en una instruccin SELECT.

Uso de combinaciones internas INNER JOIN

Las combinaciones internas combinan tablas mediante la comparacin de los valores de las columnas que son comunes a ambas tablas. SQL Server slo devuelve las filas que cumplen las condiciones de la combinacin. Por qu se utilizan combinaciones internas INNER JOIN Utilice combinaciones internas para obtener informacin de dos tablas independientes y combinar dicha informacin en un conjunto de resultados. Al utilizar combinaciones internas, tenga en cuenta los siguientes hechos e instrucciones:

Las combinaciones internas son el tipo predeterminado de SQL Server. Puede abreviar la clusula INNER JOIN como JOIN.


Pgina 3

Para especificar las columnas que desea presentar en el conjunto de resultados, incluya los nombres calificados de las columnas en la lista de seleccin.

Incluya una clusula WHERE para restringir las filas que se devuelven en el conjunto de resultados.

No utilice valores NULL como condicin de combinacin, ya que no se evalan como iguales entre s.

Ejemplos: Obtener el nombre de los proveedores y los productos que estos suministran ordenados por el nombre del proveedor: Transact-SQL: USE Nwind

GO

SELECT PV.NombreCompaa , P.NombreProducto

FROM Proveedores AS PV INNER JOIN Productos AS P

ON PV.IdProveedor = P.IdProveedor

ORDER BY 1 Anlisis: Tabla Productos Tabla Proveedores

Consulta Resultante:

Ejemplo 02: Obtener el cdigo del producto, el nombre del producto y el nombre de la categora. Utilice la combinacin de tablas Productos y categoras.


Pgina 4

Transact-SQL:

USE Nwind

GO

SELECT P.IdProducto,P. NombreProducto, C.NombreCategora

FROM

Resultado:

Ejemplo 03: En este ejemplo se devuelven los nombres de los clientes que han hecho pedidos despus del 1/1/12. Observe que se utiliza una clusula WHERE para restringir las filas devueltas en el conjunto de resultados. Transact-SQL:

USE nwind

SELECT DISTINCT NombreCompaa, FechaPedido FROM dbo.Pedidos INNER

JOIN dbo.Clientes

ON dbo.Pedidos.IdCliente = dbo.Clientes.IdCliente WHERE FechaPedido >

'1/1/12'

GO

Resultado:


Pgina 5

Combinacin de ms de dos tablas

Se puede combinar cualquier nmero de tablas. Cualquier tabla a la que se haga referencia en una operacin de combinacin se puede combinar con otra tabla mediante una columna comn.

Por qu se combinan ms de dos tablas

Puede utilizar combinaciones mltiples para obtener informacin relacionada de varias tablas. Al combinar ms de dos tablas, tenga en cuenta los siguientes hechos e instrucciones:

Debe tener una o varias tablas con claves externas relacionadas con las tablas que vaya a combinar.

Debe definir una clusula JOIN por cada columna que forme parte de una clave compuesta.

Incluya una clusula WHERE para limitar el nmero de filas que se devolver Ejemplo 04: Obtener el cdigo del producto, el nombre del producto, el nombre de la categora y el nombre de la compaa del proveedor, donde el cdigo del proveedor es igual a 1. Utilice la combinacin de tablas Productos, categoras y proveedor. Transact-SQL:

USE Nwind

GO

SELECT P.IdProducto,P. NombreProducto, C.NombreCategora,

PV.NombreCompaa

FROM Productos AS P INNER JOIN Categoras AS C

ON P.IdCategora=C.IdCategora INNER JOIN Proveedores AS PV

ON P.IdProveedor= PV.IdProveedor

WHERE P.IdProveedor= 1

Resultado:

Uso de las combinaciones externas OUTER JOIN

Las combinaciones externas izquierda y derecha combinan filas de dos tablas que cumplen una condicin de combinacin, ms las filas de la tabla izquierda o derecha que no la cumplen, tal como se especifique en la clusula JOIN. Las filas que no cumplen la condicin de combinacin presentan NULL en el conjunto de resultados. Tambin se pueden utilizar combinaciones


Pgina 6

externas completas para presentar todas las filas de las tablas combinadas, independientemente de si hay valores que coincidan en las tablas. Por qu se utilizan las combinaciones externas izquierda o derecha LEFT JOIN, RIGHT JOIN Utilice las combinaciones externas izquierda o derecha cuando necesite una lista completa de los datos de una de las tablas combinadas ms la informacin que cumpla la condicin de combinacin. Al utilizar las combinaciones externas izquierda o derecha, tenga en cuenta los siguientes hechos e instrucciones:

SQL Server slo devuelve filas nicas cuando se utilizan las combinaciones externas izquierda o derecha.

Utilice una combinacin externa izquierda para presentar todas las filas de la primera tabla especificada (la tabla de la parte izquierda de la expresin). Si invierte el orden en el que aparecen las tablas en la clusula FROM, la instruccin devuelve el mismo resultado que una combinacin externa derecha.

Utilice una combinacin externa derecha para presentar todas las filas de la segunda tabla especificada (la tabla de la parte derecha de la expresin). Si invierte el orden en el que aparecen las tablas en la clusula FROM, la instruccin devuelve el mismo resultado que una combinacin externa izquierda.

Puede abreviar las clusulas LEFT OUTER JOIN o RIGHT OUTER JOIN Como LEFT JOIN o RIGHT JOIN.

Las combinaciones externas slo se pueden utilizar entre dos tablas. Ejemplo 05: En este ejemplo se presentan todos los clientes y las fechas de sus pedidos. Con una combinacin externa izquierda se obtiene una fila por cada cliente y filas adicionales si el cliente ha efectuado varios pedidos. Se devuelve NULL en la columna Fecha Pedido del conjunto de resultados para los clientes que no han hecho ningn pedido. Observe las entradas NULL para los clientes FISSA y Paris Spcialits. Transact-SQL: USE Nwind

GO

SELECT C.IdCliente, C.NombreCompaa, P.IdPedido, P.FechaPedido

FROM Clientes AS C LEFT OUTER JOIN Pedidos AS P

ON P.IdCliente = C.IdCliente

ORDER BY P.IdCliente Resultado:


Pgina 7

Ejemplo 06: En este ejemplo se muestra a todos los clientes que no realizaron pedidos. utiliza una combinacin externa IZQUIERDA (LEFT OUTER JOIN) . Transact-SQL: USE Nwind

GO

SELECT C.IdCliente, C.NombreCompaa, P.IdPedido, P.FechaPedido

FROM Clientes AS C LEFT OUTER JOIN Pedidos AS P


WHERE P.Idpedido IS NULL Resultado:

Ejemplo 07: El ejemplo siguiente inserta 3 registros a la tabla Compaa de envos de la base de datos Nwind. Estos datos son necesarios para crear los ejercicios siguientes. Transact-SQL:

USE Nwind

GO

INSERT [Compaas de Envos]VALUES

(4, 'Aero Condor', '(503) 555-8831')

INSERT [Compaas de Envos] VALUES

(5, 'American Express', '(503) 555-9761')

INSERT [Compaas de Envos] VALUES

(6, 'Amazonas S.A.', '(503) 555-2231')

Ejemplo 08: El ejemplo siguiente retorna las columnas cdigo de pedido de la tabla pedidos y nombre de Compaa de la tabla compaa de envos utilizando una combinacin externa derecha (RIGHT OUTER JOIN). Transact-SQL:

USE Nwind

GO

SELECT P.IdPedido, CE.NombreCompaa

FROM Pedidos AS P

RIGHT OUTER JOIN [Compaas de Envos] AS CE

ON P.FormaEnvo = CE.IdcompaaEnvos

ORDER BY 2


Pgina 8

GO

Resultado:

La consulta retorna (833 filas afectadas), como es una combinacin externa derecha, retorna todas las filas de la tabla Compaa de Envos. Los tres registros ingresados en el ejercicio anterior no tienen pedidos asignados.

Ejemplo 09:

El siguiente ejemplo retorna el cdigo del cliente de la tabla pedidos y el nombre de la compaa de envos utilizando una combinacin externa completa (FULL OUTER JOIN).

Transact-SQL:

USE Nwind

GO

SELECT C.IdCliente, C.NombreCompaa ,P.IdPedido

FROM Clientes AS C FULL OUTER JOIN Pedidos AS P

ON C.IdCliente = P.IdCliente

ORDER BY 3

Resultado:


Pgina 9

Paso 1: Ejecutar el siguiente script para ejecutar los ejercicios que se presentan a continuacin. El script crea dos tablas clientes B y Facturas y se ingresan algunos registros. Clientes B tiene 5 registros (algunos clientes no tienen facturas) Facturas tiene 7 registros (algunas facturas no tienen clientes). Transact-SQL: USE Nwind

GO

--Si existe la tabla ClienteB la eliminamos

IF OBJECT_ID('ClientesB') IS NOT NULL

DROP TABLE ClientesB

GO

CREATE TABLE ClientesB

(

IdCliente char(5),

Nombre char(20),

Telefono char(7)

)

GO

--

--Si existe la tabla Facturas la eliminamos

IF OBJECT_ID('Facturas') IS NOT NULL

DROP TABLE Facturas

GO

CREATE TABLE Facturas

(

IdFactura char(5),

IdCliente char(5),

Fecha DateTime,

Total Money

)

GO

-- ingreso de datos a la tabla ClientesB

INSERT ClientesB VALUES ('C0001', 'Luis Perez', '4532222')

INSERT ClientesB VALUES ('C0002', 'Ana Rosales', '5532522')

INSERT ClientesB VALUES ('C0003', 'Jose Martinez', '8539229')

INSERT ClientesB VALUES ('C0004', 'Raul Gomez', '4532227')

INSERT ClientesB VALUES ('C0005', 'Gloria Morales', '9532789')

GO

--

-- ingreso de datos a la tabla Facturas

INSERT Facturas VALUES ('F0001','C0001', '01-08-2001', 600)

INSERT Facturas VALUES ('F0002', 'C0002','02-08-2001', 50)

INSERT Facturas VALUES ('F0003','C0003', '02-08-2001', 100)


--registros sin clientes--




GO


Pgina 10

Ejercicio 01:

Elaborar una consulta que retorne el cdigo y el nombre de la tabla clientes B y adems el nmero de la factura utilizando una combinacin interna. Resultado:

Ejercicio 02: Elaborar una consulta que retorne el cdigo y el nombre de la tabla clientes B y adems el nmero de la factura utilizando una combinacin externa izquierda. Resultado

Ejercicio 03: Elaborar una consulta que retorne el cdigo y el nombre de la tabla clientes B y adems el nmero de la factura utilizando una combinacin externa derecha. Resultado


Pgina 11

Ejercicio 04: Elaborar una consulta que retorne el cdigo y el nombre de la tabla clientes B y adems el nmero de la factura utilizando una combinacin externa completa. Resultado

Uso de las combinaciones cruzadas

Las combinaciones cruzadas presentan todas las combinaciones de todas las filas de las tablas combinadas. En este tipo de combinaciones no se requiere una columna comn. Por qu se utilizan las combinaciones cruzadas Aunque las combinaciones cruzadas no se suelen utilizar en una base de datos normalizada, se pueden utilizar para generar datos de prueba para una base de datos o listas de todas las combinaciones posibles para elaborar listas de comprobacin o patrones comerciales. Cuando se utilizan combinaciones cruzadas, SQL Server genera un producto cartesiano en el que el nmero de filas del conjunto de resultados es igual al nmero de filas de la primera tabla multiplicado por el nmero de filas de la segunda tabla. Por ejemplo, si hay 8 filas en una tabla y 9 filas en la otra, SQL Server devuelve un total de 72 filas. Ejemplo 10: En este ejemplo se presenta una combinacin cruzada entre las tablas compaa de envos y proveedores que puede ser til para enumerar todas las formas de envo de los proveedores. Al utilizar una combinacin cruzada se presentan todas las combinaciones posibles de las filas de las dos tablas. La tabla compaa de envos tiene 3 filas y la tabla proveedores tiene 29 filas. El conjunto de resultados contiene 87 filas. Transact-SQL:

USE nwind

SELECT Proveedores.NombreCompaa, [Compaas de

envos].IdCompaaEnvos

FROM dbo.Proveedores

CROSS JOIN [Compaas de envos]

GO


Pgina 12

Resultado:

Ejemplo 11: El siguiente ejemplo retorna el cdigo y nombre de la compaa de la tabla Cliente B y adems el cdigo de la factura empleando una combinacin cruzada.

Transact-SQL:

USE Nwind

GO

SELECT C.Idcliente, C.Nombre,F.IdFactura

FROM ClientesB AS C CROSS JOIN Facturas AS F

ORDER BY 1 Resultado:


Pgina 13

Combinacin de varios conjuntos de resultados

El operador UNION combina el resultado de dos o ms instrucciones SELECT en un nico conjunto de resultados.

Utilice el operador UNION cuando los datos que desee obtener residan en ubicaciones diferentes y no se pueda tener acceso a ellas con una nica consulta. Al utilizar el operador UNION, tenga en cuenta los siguientes hechos e instrucciones:

SQL Server requiere que las tablas a las que se hace referencia tengan tipos de datos similares, el mismo nmero de columnas y el mismo orden de columnas en la lista de seleccin de cada consulta.

SQL Server quita las filas duplicadas del conjunto de resultados. Sin embargo, si utiliza la opcin ALL, se incluyen todas las filas en el conjunto de resultados, incluso las duplicadas.

Debe especificar los nombres de las columnas en la primera instruccin SELECT. Por lo tanto, si desea definir nuevos encabezados de columnas en el conjunto de resultados, deber crear los alias de columnas en la primera instruccin SELECT.

Si desea que todo el conjunto de resultados aparezca con un orden especfico, debe especificar la ordenacin con una clusula ORDER BY en la instruccin UNION. De lo contrario, puede que el conjunto de resultados no se devuelva en el orden que desea.

Puede lograr un mayor rendimiento si divide una consulta compleja en varias instrucciones SELECT y, despus, utiliza el operador UNION para combinarlas.

Sintaxis instruccinSelect UNION [ALL] instruccinSelect


Pgina 14

Una subconsulta es una sentencia SELECT que aparece dentro de otra sentencia. SELECT, que llamaremos consulta principal. Una subconsulta tiene la misma sintaxis que una sentencia SELECT normal exceptuando que aparece encerrada entre parntesis, no puede contener la clusula ORDER BY, ni puede ser la UNION de varias sentencias SELECT, adems tiene algunas restricciones en cuando a nmero de columnas segn lugar donde aparece en la consulta principal. Se aconseja no utilizar campos calculados en las subconsultas. Ejemplo 12: El ejemplo siguiente muestra las columnas cdigo de pedido, fecha de pedido y mximo precio unitario en el pedido. Transact-SQL:

USE Nwind

GO

SELECT P.IdPedido , P.FechaPedido,

-- se calcula el maximo precio unitario en el pedido

-- cada pedido tiene diferentes articulos

(SELECT MAX(DP.PrecioUnidad)

FROM [Detalles de pedidos] AS DP

WHERE P.IdPedido= DP.IdPedido) AS [Maximo precio unitario en

el pedido]

FROM Pedidos AS P

GO

Resultado:

Ejemplo 13: El ejemplo siguiente retorna todos los productos con precio unitario igual al mayor precio de los artculos que son abastecidos por el proveedor con cdigo igual a 1. Transact-SQL: USE Nwind

GO

SELECT * FROM Productos AS P1

WHERE PrecioUnidad=

(SELECT MAX(PrecioUnidad)

FROM Productos as P2

WHERE IdProveedor=1)

GO Resultado:


Pgina 15

Ejemplo 14: El ejemplo siguiente retorna las columnas Nombre de compaa, ciudad y pas de los clientes que residen en el mismo pas de la empresa Antonio Moreno Taquera Transact-SQL: USE Nwind

GO

SELECT NombreCompaa,Ciudad,Pas

FROM Clientes

WHERE Pas IN

(SELECT Pas FROM Clientes

WHERE NombreCompaa='Antonio Moreno Taquera') Resultado:


Pgina 16

1. Elaborar una consulta que me permita visualizar el nombre del producto, con su precio

respectivo agregando el nombre de la categora a la que pertenece. Transact-SQL: select p.IdProducto,

p.NombreProducto,p.PrecioUnidad,c.IdCategora,c.NombreCategora

from Productos as P , Categoras as c

where P.IdCategora = c.IdCategora Utilizando inner join: select p.IdProducto,

p.NombreProducto,p.PrecioUnidad,c.IdCategora,c.NombreCategora

from Productos as P inner join Categoras as c

on P.IdCategora = c.IdCategora

Resultado:

2. Obtener el nombre de los proveedores y los productos que estos suministran ordenados por el nombre del proveedor

Transact-SQL: USE Nwind

GO

SELECT PV.NombreCompaa , P.NombreProducto

FROM Proveedores AS PV INNER JOIN Productos AS P

ON PV.IdProveedor = P.IdProveedor

ORDER BY 1

Resultado:

3. El ejemplo siguiente retorna el IdPedido, FechaPedido y todas las columnas de la tabla

cliente. Esta consulta nos responde a la pregunta Mustrame todos los nmeros de pedidos, fecha y todos los datos del cliente que hizo el pedido.


GO

SELECT P.IdPedido, P.FechaPedido, C.*

FROM Pedidos AS P INNER JOIN Clientes AS C


ORDER BY P.IdCliente


Pgina 17

Resultado:

4. Obtener el idproducto, NombreProducto, el NombreCategoria y el nombre compaa

donde IdProveedor es igual a 1. Utilice una combinacin de las tablas Productos, Categoras y Proveedores. (Combinacin de tres tablas)


GO

SELECT P.IdProducto,P. NombreProducto, C.NombreCategora,

PV.NombreCompaa

FROM Productos AS P INNER JOIN Categoras AS C

ON P.IdCategora=C.IdCategora INNER JOIN Proveedores AS PV

ON P.IdProveedor= PV.IdProveedor

WHERE P.IdProveedor= 1

Resultado:

5. El siguiente ejemplo es similar al anterior retorna los clientes que residen en un paios diferente al de Antonio Moreno Taquera. Se utiliza la palabra clave Not IN


GO

SELECT NombreCompaa,Ciudad,Pas

FROM Clientes

WHERE Pas NOT IN

(SELECT Pas FROM Clientes

WHERE NombreCompaa='Antonio Moreno Taquera') Resultado:


Pgina 18

Ejercicios Propuestos

1. Elaborar una consulta que muestre el nmero total de artculos pedidos y adems el monto total

2. Elaborar una consulta que muestre el promedio de los precios de los artculos que abastece el proveedor Tokyo Traders

3. Elaborar una consulta que me muestre el valor mximo y el valor mnimo de los productos abastecidos por el proveedor Pavlova, Ltd.

4. Utilizando count ,Elaborar una consulta que muestre cuantos pedidos realiz HILARIN-Abastos

5. Utilizando count , Elaborar una consulta que muestre cuantos clientes residen en Alemania

6. Utilizando group by, elaborar una consulta que muestre cuantos productos existen por categora.

7. Obtener todos los pedidos atendidos por las compaas de envos.

8. Obtener el nombre de los productos, de aquellos cuyo precio unitario sea igual al del producto con el nombre Licor Cloudberry.

9. Obtener el nombre completo de los empleados que hayan efectuado pedidos entre las fechas 12/06/2011 al 12/12/2011

10. Obtener el nombre de los productos adquiridos por el cliente con el cdigo de Wolza.

11. Obtener el nombre de los productos y las unidades vendidas de los productos que pertenecen al cdigo de categora 4

12. Elaborar una consulta que permita mostrar los pedidos con el nombre del cliente y el nombre del empleado que participaron en dicha transaccin.

13. Elaborar una consulta que muestre el nmero de pedido y el nombre del producto, est informacin se obtiene de las tablas pedidos y productos mediante el uso de la tabla detalles de pedidos como vnculo. Por ejemplo, si desea obtener la lista de los productos que se piden cada da, necesitara informacin de las tablas pedidos y productos. Un pedido puede contener muchos productos y un producto puede aparecer en muchos pedidos.

consultas avanzadas sqlserver_01

Documents