{"id":805,"date":"2017-09-18T22:20:16","date_gmt":"2017-09-18T22:20:16","guid":{"rendered":"http:\/\/www.manualsqlserver.com\/?p=805"},"modified":"2020-07-17T15:37:59","modified_gmt":"2020-07-17T15:37:59","slug":"subconsultas-casos-practicos-3","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=805","title":{"rendered":"Subconsultas en SQL Server &#8211; Casos pr\u00e1cticos 3"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_SubconsultasEjemploD.png\" alt=\"\" class=\"wp-image-1179\" width=\"253\" height=\"199\"\/><\/figure>\n\n\n\n<h1 class=\"wp-block-heading\"><strong>Subconsultas &#8211; Casos pr\u00e1cticos 3<\/strong><\/h1>\n\n\n\n<p>Las subconsultas se explicaron en un post previo (<a href=\"http:\/\/www.manualsqlserver.com\/?p=219\">Ver Subconsultas<\/a>), este post presenta ejercicios algo m\u00e1s complejos, adem\u00e1s de realizar el an\u00e1lisis los costos de ejecuci\u00f3n usando el Plan de ejecuci\u00f3n.<\/p>\n\n\n\n<!--more-->\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Ejercicios<\/strong><\/h2>\n\n\n\n<p><strong>1. &#8212; Listar los empleados y la cantidad de \u00f3rdenes generadas<\/strong><\/p>\n\n\n\n<p><strong>Primero la lista de empleados<\/strong><\/p>\n\n\n\n<p>Select E.EmployeeId, E.LastName, E.FirstName<br>from Employees As E<br>go<\/p>\n\n\n\n<p><strong>Contar las \u00f3rdenes para un empleado (ejemplo 5)<\/strong><\/p>\n\n\n\n<p>select Count(O.OrderID) from Orders As O where O.EmployeeID = 5<br>go<\/p>\n\n\n\n<p><strong>Construir la instrucci\u00f3n con subconsulta<\/strong><\/p>\n\n\n\n<p>Select E.EmployeeId, E.LastName, E.FirstName,<br>(<strong>select Count(O.OrderID) from Orders As O where O.EmployeeID = E.EmployeeId<\/strong> )<br>As &#8216;Cantidad de \u00d3rdenes&#8217;<br>from Employees As E<br>go<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Consultas_Subconsultas_Caso3_01.png\" alt=\"\" class=\"wp-image-1197\" width=\"775\" height=\"453\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Consultas_Subconsultas_Caso3_01.png 602w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Consultas_Subconsultas_Caso3_01-300x175.png 300w\" sizes=\"auto, (max-width: 775px) 100vw, 775px\" \/><\/figure><\/div>\n\n\n\n<p>Si visualizamos el Plan de ejecuci\u00f3n estimado, el Costo de Sub\u00e1rbol estimado es&nbsp; 0.0123845<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Consultas_Subconsultas_Caso3_02-1024x561.png\" alt=\"\" class=\"wp-image-1198\" width=\"819\" height=\"449\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Consultas_Subconsultas_Caso3_02-1024x561.png 1024w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Consultas_Subconsultas_Caso3_02-300x164.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Consultas_Subconsultas_Caso3_02-768x421.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Consultas_Subconsultas_Caso3_02.png 1068w\" sizes=\"auto, (max-width: 819px) 100vw, 819px\" \/><\/figure><\/div>\n\n\n\n<p><strong>Analizamos el costo para SQL Server usando Joins para la misma instrucci\u00f3n<\/strong><\/p>\n\n\n\n<p>Select E.EmployeeId, E.LastName, E.FirstName,<br>Count(O.OrderID) As &#8216;Cantidad de \u00d3rdenes&#8217;<br>from Employees As E<br>join Orders As O on E.EmployeeId = O.EmployeeID<br>Group by E.EmployeeId, E.LastName, E.FirstName<br>go<\/p>\n\n\n\n<p>El conjunto de resultados es el mismo, al analizar el Costo de Sub\u00e1rbol estimado es&nbsp;0.0100238<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Consultas_Subconsultas_Caso3_03.png\" alt=\"\" class=\"wp-image-1199\" width=\"818\" height=\"539\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Consultas_Subconsultas_Caso3_03.png 922w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Consultas_Subconsultas_Caso3_03-300x198.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Consultas_Subconsultas_Caso3_03-768x506.png 768w\" sizes=\"auto, (max-width: 818px) 100vw, 818px\" \/><\/figure><\/div>\n\n\n\n<p>Si comparamos los resultados, el uso de Join (<a href=\"http:\/\/www.manualsqlserver.com\/?p=215\">Ver Post de Join<\/a>) es menos costoso para SQL Server, y obviamente, recomendado.<\/p>\n\n\n\n<p><strong>2. &#8212; Listar las \u00f3rdenes de los clientes de M\u00e9xico y Francia (France)<\/strong><\/p>\n\n\n\n<p><strong>Se necesita primero los c\u00f2digos de los clientes de M\u00e9xico y Francia<\/strong><\/p>\n\n\n\n<p>select C.CustomerID from customers As C where Country = &#8216;Mexico&#8217; or Country = &#8216;France&#8217;<br>go<\/p>\n\n\n\n<p><strong>Listado de las \u00f3rdenes, note que la subconsulta se ha utilizado en la cl\u00e1usula Where.<\/strong><\/p>\n\n\n\n<p>select * from Orders<br>where CustomerID in<br>(select C.CustomerID from customers As C where Country = &#8216;Mexico&#8217; or Country = &#8216;France&#8217;)<br>go<\/p>\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<p>3. &#8212; Incluir el nombre del Empleado y el nombre del Cliente<\/p>\n\n\n\n<p><strong>Para mostrar el Empleado<\/strong><br>select Empleado = E.LastName + Space(1) + E.Firstname from Employees As E<br>go<\/p>\n\n\n\n<p><strong>Para mostrar el Cliente<\/strong><br>Select C.Companyname from customers As C<br>go<\/p>\n\n\n\n<p><strong>Las instrucciones anteriores las incluiremos en la consulta como subconsultas, adem\u00e1s se ha incluido el pa\u00eds para comprobar que son los clientes de M\u00e9xico y France.<\/strong><\/p>\n\n\n\n<p>select O.OrderId, Format(O.OrderDate , &#8216;dd\/MM\/yyyy&#8217;) As &#8216;Fecha&#8217;,<br>(select Empleado = E.LastName + Space(1) + E.Firstname from Employees As E<br>where E.EmployeeID = O.EmployeeID) As &#8216;Empleado&#8217;,<br>(Select C.Companyname from customers As C<br>where C.CustomerId = O.CustomerId ) As &#8216;Cliente&#8217;,<br>(Select C.Country from customers As C<br>where C.CustomerId = O.CustomerId ) As &#8216;Pa\u00eds&#8217;<br>from Orders As O<br>where CustomerID in<br>(select C.CustomerID from customers As C where Country = &#8216;Mexico&#8217; or Country = &#8216;France&#8217;)<br>go<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Consultas_Subconsultas_Caso3_05.png\" alt=\"\" class=\"wp-image-1201\" width=\"806\" height=\"463\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Consultas_Subconsultas_Caso3_05.png 834w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Consultas_Subconsultas_Caso3_05-300x173.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Consultas_Subconsultas_Caso3_05-768x442.png 768w\" sizes=\"auto, (max-width: 806px) 100vw, 806px\" \/><\/figure><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Subconsultas &#8211; Casos pr\u00e1cticos 3 Las subconsultas se explicaron en un post previo (Ver Subconsultas), este post presenta ejercicios algo m\u00e1s complejos, adem\u00e1s de realizar el an\u00e1lisis los costos de ejecuci\u00f3n usando el Plan de ejecuci\u00f3n.<\/p><p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=805\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":1179,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,231],"tags":[40,51,55],"class_list":["post-805","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-consultasdedatos","category-subconsultas","tag-join","tag-select","tag-subconsultas","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/805","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=805"}],"version-history":[{"count":1,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/805\/revisions"}],"predecessor-version":[{"id":1203,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/805\/revisions\/1203"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/1179"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=805"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=805"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=805"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}