taller básico de joins, subquerying, apply, cte

http://julycastiblanco.blogspot.com/

Pag

e1

Taller de mezcla de Tablas

SQL SERVER 2012 (APLICA TAMBIÉN PARA SS2008/R2)

Autor: Julián Castiblanco Palacios

Email: [email protected]

blog: http://julycastiblanco.blogspot.com/

Demo version: 1.0.0

Last updated: 2/20/2014

mailto:[email protected]



Pag

e2

CONTENIDO

INTRODUCCIÓN .......................................................................................................................................... 3 PUNTOS CLAVES .................................................................................................................................... 3

requisitos tecnológicos .............................................................................................................................. 3

Tiempo estimado del taller ........................................................................................................................ 4

CONFIGURACIÓN Y PREREQUISITOS ..................................................................................................... 4

SOLUCIONE EL SIGUIENTE TALLER PASO A PASO ............................................................................. 6 CONTENIDO #1 – Creación de una solución en SQL Server Management Studio ................................ 6

CONTENIDO #2a – JOINS ..................................................................................................................... 10

CONTENIDO #2B – JOINS..................................................................................................................... 14

CONTENIDO #3 – SUBCONSULTAS Y CTE ........................................................................................ 16

contenido #4 – APPLY ............................................................................................................................ 18


Pag

e3

INTRODUCCIÓN Este taller tiene como finalidad evaluar los conocimientos técnicos en la implementación de consultas que realizan mezclas de dos o más tablas, adicionalmente

pretende evaluar característicias adicionales como las subconsultas, el comando EXISTS, las expresiones comunes de tabla y las funciones de tipo tabla entre

otros.

Cualquier duda con la resolución de los ejercicios y/o aclaración de los conceptos no dude en contactarme a la cuenta de correo

[email protected].

PUNTOS CLAVES 1. Los conceptos a evaluar en este taller son: CROSS JOIN, INNER JOIN, OUTER JOIN, SUBCONSULTAS, Tablas derivadas, CTE (expresiones comunes de

tablas), APPLY.

2. Toda la teoría de este taller puede ser encontrada en el siguiente material disponible en la red:

a. http://www.slideshare.net/juliancastiblanco/introduccin-a-joins-cte-apply-y-subconsultas

b. http://www.amazon.com/Training-Kit-Exam-70-461-Microsoft/dp/0735666059/ref=sr_1_1?ie=UTF8&qid=1359206206&sr=8-

1&keywords=querying+microsoft+sql+server+2012+training+kit+exam+70-461

c. http://www.microsoftvirtualacademy.com/training-courses/querying-microsoft-sql-server-2012-databases-jump-start#?fbid=XW_QVoNzp3g

REQUISITOS TECNOLÓGICOS Este taller usa los siguientes productos y tecnologías:

1. Microsoft SQL Server 2012 SP1 (evaluation, developer, express edition):

a. Database Engine

b. SQL Server Management Studio (Full or Express)

mailto:[email protected]

http://www.slideshare.net/juliancastiblanco/introduccin-a-joins-cte-apply-y-subconsultas

http://www.amazon.com/Training-Kit-Exam-70-461-Microsoft/dp/0735666059/ref=sr_1_1?ie=UTF8&qid=1359206206&sr=8-1&keywords=querying+microsoft+sql+server+2012+training+kit+exam+70-461



http://www.microsoftvirtualacademy.com/training-courses/querying-microsoft-sql-server-2012-databases-jump-start#?fbid=XW_QVoNzp3g


Pag

e4

TIEMPO ESTIMADO DEL TALLER

Taller esta diseñado para un tiempo promedio entre 45 y 60 minutos.

CONFIGURACIÓN Y PREREQUISITOS REQUERIMIENTOS DE SISTEMA

Microsoft SQL Server 2012 SP1:

◦ Database Engine

◦ SQL Server Management Studio

Si no cuenta aún con una instancia de SQL Server, puede validar el paso a paso de como realizar la instalación en la siguiente dirección:

http://julycastiblanco.blogspot.com/2012/03/instalacion-de-sql-server-2012-sobre.html

Posibles errores en la instalación

http://julycastiblanco.blogspot.com/2012/02/this-sql-server-setup-media-does-not.html

AdventureWorks Sample Databases for SQL Server 2012:

◦ AdventureWorksDW20012

◦ AdventureWorks

Si no cuenta aún con las bases de datos de pruebas, puede descargarlas de la siguiente url

http://msftdbprodsamples.codeplex.com/releases/view/55330

http://julycastiblanco.blogspot.com/2012/03/instalacion-de-sql-server-2012-sobre.html

http://julycastiblanco.blogspot.com/2012/02/this-sql-server-setup-media-does-not.html




Pag

e5

PREPARACIÓN 1. Asegúrese de que la configuración fue correcta.

2. Inicie SQL Server Management Studio con privilegios de administrador.

FIGURE 1. PRIVILEGIOS DE ADMINISTRADOR EN WINDOWS 8.1


Pag

e6

SOLUCIONE EL SIGUIENTE TALLER PASO A PASO El siguiente taller está compuesto por los siguientes contenidos:

1. Creación de una solución en SQL Server Management Studio

2. Aplicación de Consultas tipo CROSS, JOIN, OUTER

3. Aplicación de subconsultas, CTE y tablas derivadas

4. Aplicación de commando APPLY

CONTENIDO #1 – CREACIÓN DE UNA SOLUCIÓN EN SQL SERVER MANAGEMENT STUDIO

Action Script Screenshot

1. Ingrese a SQL Server Management Studio.

En el campo Server name, ingrese el nombre de su servidor respectivo por defecto el nombre de su máquina

En el campo Autentication, deje el nombre de su usuario de windows, si realizó la instalación con este usuario, podrá ingresar a la instancia.


Pag

e7

2. En el menu “file” en la opción “new” seleccione la primera opción “project”. Para crear un nuevo proyecto de base de datos.

3. En el asistente para creación de nuevos proyectos Seleccione la opción SQL Server Scripts

En el campo name escriba “Join_Apply_CTE solutions” deje la ruta por defecto y habilite el check para crear una carpeta nueva para la solución

En la parte derecha de la ventana aparecerá una nueva ventana en la


Pag

e8

4. En el menu “view” seleccione la opción “Solution Explorer”. Para poder visualizar los componentes de la solución.

5. Oprima clic derecho sobre la carpeta denominada “connections” para agregar una nueva conexión a la solución.

cual podrá visualizar los componentes de una solución de SQL Server script.

Nuevamente se abre la ventana de autenticación a un servidor, diligencie los datos del servidor donde se encuentra la base de datos de AdventureWorks


Pag

e9

6. Note que una nueva conexión ha sido creada en el proyecto.

7. Ahora, seleccione “queries” y agregue una nueva consulta

8. Guarde los cambios del proyecto.

Renombre la consulta a “001_Joins.sql”, oprimiento botón derecho y la opción “rename”


Pag

e10

CONTENIDO #2A – JOINS


1. Interacción inicial con AdventureWorks2012. La primera tabla con que se va a trabajar pertenece al esquema de las ventas y posee información de la cabecera de las facturas de la compañía que se dedica

2. La segunda tabla con la que trabajaremos será currencyRate en al cual se encuentra las tasas de cambio entre dólar y varias monedas del mundo, esta tasa de cambio aplica para cada día, por lo cual siempre que seleccione un código de tasa, deberá también seleccionar la fecha para la cual necesita la información.

3. La tercera tabla con que trabajaremos es la tabla se encuentra el nombre de todas las monedas, hay una relación entre Currency y CurrencyRate

Use AdventureWorks2012 Go select * from [Sales].[SalesOrderHeader]

select * from [Sales].[CurrencyRate]

select * from [Sales].Currency


Pag

e11

4. La cuarta tabla es “Customer” en la cual se relaciona el id del cliente, la ubicación en que realizó la compra, el id de la tienda. Note que existe un campo PersonID, que es el campo llave para relacionarse con la vista que contiene la información básica de los clientes.

5. Ahora bien, utilizaremos una vista que contiene la información detallada de los clientes como el nombre, teléfono, email. Etc. Analice cada una de las tablas e identifique cuales campos son llave y permitirán mezcalr la información de las tablas.

6. Ahora Cree un diagrama que le permitirá visualizar gráficamente la relación entre las tablas y las llaves de relación entre las mismas.

select * from SALES.Customer

select * from [Sales].[vIndividualCustomer]

Para esto en la carpeta “database diagrams” dentro de la base de datos adventure Works, oprima clic derecho y seleccione la opción “New database diagram”.


Pag

e12

7. selecciones las siguientes tablas al diagrama: Como puede notarlo, las vistas no aparecen listadas, por lo cual no pueden ser incluidas en el diagrama.

8. Guarde el diagrama con el nombre “diag_Ventas por tipo de moneda”

Si obtiene un mensaje de error, es porque la base de datos no tiene un dueño asociado, para lo cual basta con configurar al usuario SA como dueño de la base de datos en las propiedades de la misma. En la columna derecha encontrará el lugar exacto donde debe configurarlo.

SalesOrderHeader(sales)

CurrencyRate(sales)

Currency(sales)

Customer(Sales)

vIndividualCustomer(Sales)


Pag

e13

9. ahora bien, suponga que ha sido contratado como desarrollador de sql server en adventure Works para desarrollar un informe de ventas, que entregue la siguiente información:

Se necesita un reporte que en una columna coloque el nombre completo del cliente, caso que no esté registrado, debe colocar un valor de “No name registred” debe traer la ciudad del cliente, la dirección, el número telefónico de contacto, que muestre el valor de la compra en libras inglesas a la tasa promedio del día de compra, igual mente para el valor de los impuestos y el valor total de la venta, por último el campo con el nombre completo de la moneda.

El reporte debe mostrar dicha información solo para las órdenes que fueron creadas en el año 2008 y en libras inglesas.

Modifique en el script adjunto los campos ???? por la sentencia apropiada para lograr su cometido.

select SOH.SalesOrderNumber,SOH.OrderDate

,????((PE.Title+' '+PE.FirstName+'

'+PE.LastName),'No name registred') AS

NAME

,PE.City,PE.AddressLine1

,PE.PhoneNumber,( ????*SOH.SubTotal) AS

SubtotalEnlibras

,(CR.AverageRate*????) AS

ImpuestosEnLibras

,( ????*SOH.TotalDue) AS

ValorTotalEnLibras

,c.Name as moneda

from [Sales].[SalesOrderHeader] as ????

???? [Sales].[CurrencyRate] AS CR

???? SOH.CurrencyRateID=CR.CurrencyRateID

????SOH.OrderDate=CR.CurrencyRateDate

???? Sales.Currency AS C


Pag

e14

????CR.ToCurrencyCode=C.CurrencyCode

???? SALES.Customer CU

???? CU.CustomerID=SOH.CustomerID

???? [Sales].[vIndividualCustomer] AS PE

ON CU.PersonID=PE.BusinessEntityID

WHERE C.CurrencyCode=????

AND ???? = 2008

CONTENIDO #2B – JOINS


1. Basado en las tablas anteriormente trabajadas supongamos este nuevo caso.

Usted ha sido contratado como desarrollador de sql server 2012 para adventure Works, el departamento de mercadeo premiará a sus clientes que hicieron compras en el año 2008, sin embargo solo recibirán premio

select ISNULL((isnull(PE.FirstName,'')+' '+isnull(pe.MiddleName,'')+' '+isnull(pe.LastName,'')),'No name registred') AS NAME

,PE.City

,PE.AddressLine1


Pag

e15

especial los que hicieron compras explícitamente el día 12 de febrero. Por lo cual le solicitan un reporte en el cual entregue una lista de todos los clientes que compraron en el 2008 con nombre, teléfono, ciudad y dirección y en una columna adicional llamada “premio” la palabra “NO PARTICIPA” si la fecha fue diferente al 12 de febrero y “GANA PREMIO si fue durante este día. Modifique los espacios ???? en el siguiente script para que se cumpla la condición.

,PE.PhoneNumber,OrderDate

, ?????????????? as Premio

from SALES.Customer CU

???? [Sales].[vIndividualCustomer] AS PE

???? CU.PersonID=PE.BusinessEntityID

???? [Sales].[SalesOrderHeader] as SOH

???? CU.CustomerID=SOH.CustomerID

WHERE ????

ORDER BY OrderDate


Pag

e16

CONTENIDO #3 – SUBCONSULTAS Y CTE


1. Ahora vamos a conocer un Nuevo grupo de tablas que existen en el esquema de recursos humanos. La tabla “Employee” en una misma tabla contiene todo el organigrama de la compañía, el campo bussinesEntityID permite unir la información con la tabla “person” la cual como hemos visto tiene el nombre y otros datos de la entidad persona.

2. La tabla [EmployeeDepartmentHistory], contiene la historia de cargos que han tenido los empleados dentro de la institución es decir, suponga que ud. Ingresó a la compañía como mensajero, luego ascendió a analista de operaciones y al terminar sus estudios pasó al departamento de tecnología para ser el arquitecto de bases de datos SQL Server. Note que cuando la columna EndDate es nula, quiere decir que es el cargo actual de la persona

3. La tabla de “departament" contiene el nombre de los departamentos de la compañía, como podrá imaginar estas 4 tablas se relacionan entre sí; con lo cual

SELECT * FROM [HumanResources].[Employee]

SELECT * FROM [HumanResources].[EmployeeDepartmentHistory]

SELECT * FROM [HumanResources].[Department]


Pag

e17

está preparado para resolver el siguiente ejercicio.

4. Recursos humanos lo ha requerido para que entregue el BusinessentityID de los siguientes empleados “William Vong”,”Laura Norman”,”Sheela Word” quienes serán promovidos de cargo. Recuerde que en la información de nombres se encuentra en la tabla Person, sin embargo una persona puede estar más de una vez, una con el rol de empleado otra con el rol de cliente y/o proveedor. Por lo cual verique que ha seleccionado el valor correcto para tipo de persona.

5. Una vez que logró resolver la incertidumbre, recibe un nuevo llamado por parte de recursos humanos donde le pide que además de la información enviada, precisan conocer el Grupo del departamento, el nombre del departamento, el cargo actual y el nombre de estos empleados. Realice esta consulta teniendo como base la subconsulta generada en el punto 4.

select businessentityid from ???? where (????)

???? ('William Vong','Laura Norman','Sheela Word') and ????='EM'


Pag

e18

6. Realice la misma consulta reemplazando la subconsulta por una CTE, a continuación un script guía para lograrlo

WITH EMPLOYESS (businessentityid) AS(???)

SELECT DP.GroupName,DP.Name,EM.JobTitle,(FirstName+' '+LastName) AS NOMBRE

FROM ???? INNER JOIN EMPLOYESS

ON EM.BusinessEntityID = EMPLOYESS.businessentityid

WHERE EndDate IS NULL

CONTENIDO #4 – APPLY


1. Cree una nueva base de datos llamada “DEMOAPPLY”.

CREATE DATABASE DEMOAPPLY

GO

USE DEMOAPPLY

GO


Pag

e19

2. Cree una nueva table denominada “Empleados” e ingrese los siguientes registros.

--Create Empleados table and insert values.

CREATE TABLE Empleados

(

empid int NOT NULL

,mgrid int NULL

,empname varchar(25) NOT NULL

,salary money NOT NULL

CONSTRAINT PK_Empleados PRIMARY KEY(empid)

);

GO

INSERT INTO Empleados VALUES(1 , NULL, 'Nancy' , $10000.00);

INSERT INTO Empleados VALUES(2 , 1 , 'Andrew' , $5000.00);

INSERT INTO Empleados VALUES(3 , 1 , 'Janet' , $5000.00);

INSERT INTO Empleados VALUES(4 , 1 , 'Margaret', $5000.00);

INSERT INTO Empleados VALUES(5 , 2 , 'Steven' , $2500.00);

INSERT INTO Empleados VALUES(6 , 2 , 'Michael' , $2500.00);


Pag

e20

3. Cree la taba de “departamentos” que equivale a las secciones de la compañía

INSERT INTO Empleados VALUES(7 , 3 , 'Robert' , $2500.00);

INSERT INTO Empleados VALUES(8 , 3 , 'Laura' , $2500.00);

INSERT INTO Empleados VALUES(9 , 3 , 'Ann' , $2500.00);

INSERT INTO Empleados VALUES(10, 4 , 'Ina' , $2500.00);

INSERT INTO Empleados VALUES(11, 7 , 'David' , $2000.00);

INSERT INTO Empleados VALUES(12, 7 , 'Ron' , $2000.00);

INSERT INTO Empleados VALUES(13, 7 , 'Dan' , $2000.00);

INSERT INTO Empleados VALUES(14, 11 , 'James' , $1500.00);

GO

CREATE TABLE Departamentos

( deptid INT NOT NULL PRIMARY KEY

,deptname VARCHAR(25) NOT NULL

,deptmgrid INT NULL REFERENCES Empleados);

GO


Pag

e21

4. A continuación, cree una función de tipo tabla que se encargará de retornar a cada uno de los empleados del departamento y sus correspondientes subalternos. Note que esta función contiene también un CTE que es utilizado para garantizar las consultas recursivas para tomar todos los miembros del departamento y sus subalternos.

INSERT INTO Departamentos VALUES(1, 'HR', 2);

INSERT INTO Departamentos VALUES(2, 'Marketing', 7);

INSERT INTO Departamentos VALUES(3, 'Finance', 8);

INSERT INTO Departamentos VALUES(4, 'R&D', 9);

INSERT INTO Departamentos VALUES(5, 'Training', 4);

INSERT INTO Departamentos VALUES(6, 'Gardening', NULL);

CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT)

RETURNS @TREE TABLE

(

empid INT NOT NULL

,empname VARCHAR(25) NOT NULL

,mgrid INT NULL

,lvl INT NOT NULL

)

AS


Pag

e22

BEGIN

WITH Empleados_Subtree(empid, empname, mgrid, lvl)

AS

(

-- Anchor Member (AM)

SELECT empid, empname, mgrid, 0

FROM Empleados

WHERE empid = @empid

UNION all

-- Recursive Member (RM)

SELECT e.empid, e.empname, e.mgrid, es.lvl+1

FROM Empleados AS e

JOIN Empleados_Subtree AS es


Pag

e23

5. una vez ha creado la función, ejecute la siguiente consulta, como podrá ver, cada uno de los registros devueltos por la tabla Departamentos, va a ser un parámetro para ejecutar la función de tipo tabla que a su vez devolverá una tabla de empleados que coinciden en el departamento.

ON e.mgrid = es.empid

)

INSERT INTO @TREE

SELECT * FROM Empleados_Subtree;

RETURN

END

GO

SELECT D.deptid, D.deptname, D.deptmgrid

,ST.empid, ST.empname, ST.mgrid

FROM Departamentos AS D

CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST;

taller básico de joins, subquerying, apply, cte

Technology