advertisement

Taller básico de JOINS, SUBQUERYING, APPLY, CTE

50 %
50 %
advertisement
Information about Taller básico de JOINS, SUBQUERYING, APPLY, CTE
Technology

Published on February 20, 2014

Author: juliancastiblanco

Source: slideshare.net

Description

este taller es un paso a paso para reforzar los conocimientos en creación de consultas en SQL Server utilizando los elementos JOIN, SUBQUERIES, APPLY y CTE. esta totalmente diseñado para realizarse con recursos gratuitos y disponibles en la red
advertisement

http://julycastiblanco.blogspot.com/ Taller de mezcla de Tablas SQL SERVER 2012 (APLICA TAMBIÉN PARA SS2008/R2) Autor: Julián Castiblanco Palacios Email: julian_castiblancop@hotmail.com blog: http://julycastiblanco.blogspot.com/ Last updated: 2/20/2014 1 1.0.0 Page Demo version:

http://julycastiblanco.blogspot.com/ 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 Page 2 contenido #4 – APPLY ............................................................................................................................ 18

http://julycastiblanco.blogspot.com/ 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 julian_castiblancop@hotmail.com. 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=81&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: Microsoft SQL Server 2012 SP1 (evaluation, developer, express edition): Database Engine b. SQL Server Management Studio (Full or Express) 3 a. Page 1.

http://julycastiblanco.blogspot.com/ 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 http://msftdbprodsamples.codeplex.com/releases/view/55330 Page  4 Si no cuenta aún con las bases de datos de pruebas, puede descargarlas de la siguiente url

http://julycastiblanco.blogspot.com/ PREPARACIÓN 1. Asegúrese de que la configuración fue correcta. 2. Inicie SQL Server Management Studio con privilegios de administrador. Page 5 FIGURE 1. PRIVILEGIOS DE ADMINISTRADOR EN WINDOWS 8.1

http://julycastiblanco.blogspot.com/ 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 Script  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. 6 1. Ingrese a SQL Server Management Studio. Screenshot Page Action

http://julycastiblanco.blogspot.com/ En el menu “file” en la opción “new” seleccione la primera opción “project”. Para crear un nuevo proyecto de base de datos. 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 3. En el asistente para creación de nuevos proyectos Seleccione la opción SQL Server Scripts 7  Page 2.

http://julycastiblanco.blogspot.com/ cual podrá visualizar los componentes de una solución de SQL Server script. 5. Oprima clic derecho sobre la carpeta denominada “connections” para agregar una nueva conexión a la solución. 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 8  Page 4. En el menu “view” seleccione la opción “Solution Explorer”. Para poder visualizar los componentes de la solución.

http://julycastiblanco.blogspot.com/ 6. Note que una nueva conexión ha sido creada en el proyecto.  Renombre la consulta a “001_Joins.sql”, oprimiento botón derecho y la opción “rename” 7. Ahora, seleccione “queries” y agregue una nueva consulta del 9 cambios Page 8. Guarde los proyecto.

http://julycastiblanco.blogspot.com/ CONTENIDO #2A – JOINS Action Script 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  select * from [Sales].[CurrencyRate] select * from [Sales].Currency 10 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] Page 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. Screenshot

http://julycastiblanco.blogspot.com/ 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. select * from [Sales].[vIndividualCustomer] 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. 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”. 11 select * from SALES.Customer Page 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.

http://julycastiblanco.blogspot.com/ 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. 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. SalesOrderHeader(sales) CurrencyRate(sales) Currency(sales) Customer(Sales) vIndividualCustomer(Sales) Page 12 8. Guarde el diagrama con el nombre “diag_Ventas por tipo de moneda”

http://julycastiblanco.blogspot.com/ 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. 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 El reporte debe mostrar dicha información solo para las órdenes que fueron creadas en el año 2008 y en libras inglesas. ,c.Name as moneda from [Sales].[SalesOrderHeader] as ???? ???? [Sales].[CurrencyRate] AS CR ???? SOH.CurrencyRateID=CR.CurrencyRateID ???? Sales.Currency AS C 13 ????SOH.OrderDate=CR.CurrencyRateDate Page Modifique en el script adjunto los campos ???? por la sentencia apropiada para lograr su cometido.

http://julycastiblanco.blogspot.com/ ????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. select ISNULL((isnull(PE.FirstName,'')+' '+isnull(pe.MiddleName,'')+' '+isnull(pe.LastName,'')),'No name registred') AS NAME 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 ,PE.City ,PE.AddressLine1 Screenshot 14 Script Page Action

http://julycastiblanco.blogspot.com/ ,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 ???? 15 ORDER BY OrderDate Page 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.

http://julycastiblanco.blogspot.com/ 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. SELECT * FROM [HumanResources].[Employee] 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 SELECT * FROM [HumanResources].[EmployeeDepartmen tHistory] 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].[Department] Screenshot 16 Script Page Action

http://julycastiblanco.blogspot.com/ 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. select businessentityid from ???? where (????) ???? ('William Vong','Laura Norman','Sheela Word') and ????='EM' Page 17 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.

http://julycastiblanco.blogspot.com/ 6. Realice la misma consulta reemplazando la subconsulta por una CTE, a continuación un script guía para lograrlo WITH EMPLOYESS AS(???) (businessentityid) SELECT DP.GroupName,DP.Name,EM.JobTitle,(F irstName+' '+LastName) AS NOMBRE FROM ???? INNER JOIN EMPLOYESS ON EM.BusinessEntityID = EMPLOYESS.businessentityid WHERE EndDate IS NULL CONTENIDO #4 – APPLY Action Script 1. Cree una nueva base de datos llamada “DEMOAPPLY”. Screenshot CREATE DATABASE DEMOAPPLY GO  Page GO 18 USE DEMOAPPLY

http://julycastiblanco.blogspot.com/ --Create Empleados table and insert values. CREATE TABLE Empleados ( empid ,mgrid int int NOT NULL NULL ,empname varchar(25) NOT NULL ,salary money NOT NULL CONSTRAINT PK_Empleados PRIMARY KEY(empid) ); GO , $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' INSERT INTO Empleados VALUES(6 , 2 , 'Michael' , $2500.00); , $2500.00); 19 INSERT INTO Empleados VALUES(1 , NULL, 'Nancy' Page 2. Cree una nueva table denominada “Empleados” e ingrese los siguientes registros.

http://julycastiblanco.blogspot.com/ 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 ,deptname INT NOT NULL PRIMARY KEY VARCHAR(25) NOT NULL GO 20 ,deptmgrid INT NULL REFERENCES Empleados); Page 3. Cree la taba de “departamentos” que equivale a las secciones de la compañía

http://julycastiblanco.blogspot.com/ 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 ,lvl ) AS INT NULL INT NOT NULL 21 ,mgrid Page 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.

http://julycastiblanco.blogspot.com/ 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) JOIN Empleados_Subtree AS es Page FROM Empleados AS e 22 SELECT e.empid, e.empname, e.mgrid, es.lvl+1

http://julycastiblanco.blogspot.com/ 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 23 CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST; Page 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.

Add a comment

Related presentations

Presentación que realice en el Evento Nacional de Gobierno Abierto, realizado los ...

In this presentation we will describe our experience developing with a highly dyna...

Presentation to the LITA Forum 7th November 2014 Albuquerque, NM

Un recorrido por los cambios que nos generará el wearabletech en el futuro

Um paralelo entre as novidades & mercado em Wearable Computing e Tecnologias Assis...

Microsoft finally joins the smartwatch and fitness tracker game by introducing the...

Related pages

Taller básico de JOINS, SUBQUERYING, APPLY, CTE

este taller es un paso a paso para reforzar los conocimientos en creación de consultas en SQL Server utilizando los elementos JOIN, SUBQUERIES, APPLY y ...
Read more

¡ Muéstrame el dinero!. Cómo: Cuando: Quién: Cuánto: Apply ...

Taller básico de JOINS, SUBQUERYING, APPLY, CTE este taller es un paso a paso para reforzar los conocimientos en ... APPLY y CTE. esta ...
Read more

Apply TMP - Education

Taller básico de JOINS, SUBQUERYING, APPLY, CTE este taller es un paso a paso para reforzar los conocimientos en ... APPLY y CTE. esta ...
Read more

Ejercicios de Din Acel Cte - Documents

Taller básico de JOINS, SUBQUERYING, APPLY, CTE ... Cte Resumen Extinciones Campanas de Cocina CTE documento de trabajo tercera sesión Comments.
Read more