{"id":757,"date":"2017-09-18T22:23:33","date_gmt":"2017-09-18T22:23:33","guid":{"rendered":"http:\/\/www.manualsqlserver.com\/?p=757"},"modified":"2020-07-17T15:37:59","modified_gmt":"2020-07-17T15:37:59","slug":"subconsultas-casos-practicos-2","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=757","title":{"rendered":"Subconsultas SQL Server &#8211; casos pr\u00e1cticos 2"},"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=\"261\" height=\"205\"\/><\/figure>\n\n\n\n<h1 class=\"wp-block-heading\"><strong>Subconsultas &#8211; Casos pr\u00e1cticos 2<\/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<ol class=\"wp-block-list\"><li><strong>Listar los proveedores que no tiene asignado una regi\u00f3n,&nbsp;&nbsp;incluir la cantidad de productos que provee<\/strong><\/li><\/ol>\n\n\n\n<p><strong>Primero los proveedores que no tienen asignada una regi\u00f3n<\/strong><\/p>\n\n\n\n<p>select S.SupplierID, S.CompanyName&nbsp; from Suppliers As S where S.Region is Null<br>go<\/p>\n\n\n\n<p><strong>Como ejemplo vamos a contar los productos que provee el proveedor 1<\/strong><\/p>\n\n\n\n<p>select Count(P.ProductId) from products As P where P.SupplierID = 1<br>go<\/p>\n\n\n\n<p><strong>Construir la instrucci\u00f3n con Subconsulta<\/strong><\/p>\n\n\n\n<p>select S.SupplierID As &#8216;C\u00f3digo Proveedor&#8217;, S.CompanyName As &#8216;Nombre&#8217;,<br>(select Count(P.ProductId) from products As P where P.SupplierID = S.SupplierID)<br>As &#8216;Cantidad de Productos&#8217;<br>from Suppliers As S where S.Region is Null<br>go<br><strong>Podemos mostrar el Plan de ejecuci\u00f3n estimado para ver el costo estimado de sub\u00e1rbol cuyo valor es: 0.0097787<\/strong><\/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_SubconsultasEjemplo03-1024x347.png\" alt=\"\" class=\"wp-image-1177\" width=\"812\" height=\"275\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_SubconsultasEjemplo03-1024x347.png 1024w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_SubconsultasEjemplo03-300x102.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_SubconsultasEjemplo03-768x260.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_SubconsultasEjemplo03.png 1080w\" sizes=\"auto, (max-width: 812px) 100vw, 812px\" \/><\/figure><\/div>\n\n\n\n<p><strong>Usando Joins&nbsp; (<a href=\"http:\/\/www.manualsqlserver.com\/?p=215\">Ver Joins<\/a>)<\/strong><\/p>\n\n\n\n<p>select S.SupplierID As &#8216;C\u00f3digo Proveedor&#8217;, S.CompanyName As &#8216;Nombre&#8217;,<br>Count(P.ProductId) As &#8216;Cantidad de Productos&#8217;<br>from Suppliers As S<br>join Products As P on S.SupplierID = P.SupplierID<br>where S.Region is Null<br>group by S.SupplierID, S.CompanyName<br>go<br><strong>Podemos mostrar el Plan de ejecuci\u00f3n estimado para ver el costo estimado de sub\u00e1rbol cuyo valor es:<\/strong> <strong>0.0099151<\/strong><\/p>\n\n\n\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_SubconsultasEjemplo04.png\" alt=\"\" class=\"wp-image-1178\" width=\"860\" height=\"415\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_SubconsultasEjemplo04.png 854w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_SubconsultasEjemplo04-300x145.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_SubconsultasEjemplo04-768x371.png 768w\" sizes=\"auto, (max-width: 860px) 100vw, 860px\" \/><\/figure>\n\n\n\n<p>Note que con el uso de Joins el costo es mayor.<\/p>\n\n\n\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_Consultas_Subconsultas_Caso2_00.png\" alt=\"\" class=\"wp-image-1182\" width=\"898\" height=\"331\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Consultas_Subconsultas_Caso2_00.png 828w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Consultas_Subconsultas_Caso2_00-300x111.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Consultas_Subconsultas_Caso2_00-768x284.png 768w\" sizes=\"auto, (max-width: 898px) 100vw, 898px\" \/><\/figure>\n\n\n\n<p><strong>2. &#8212; Cientes de M\u00e8xico, incluyan la cantidad de \u00f3rdenes, el monto&nbsp;total de las \u00f2rdenes<\/strong><\/p>\n\n\n\n<p><strong>Clientes de M\u00e9xico<\/strong><\/p>\n\n\n\n<p>select C.CustomerID As &#8216;C\u00f3digo Cliente&#8217;, C.Companyname As &#8216;Cliente&#8217;<br>from customers As C where country = &#8216;Mexico&#8217;<br>go<\/p>\n\n\n\n<p><strong>Cantidad de \u00d3rdenes de un Cliente<\/strong><\/p>\n\n\n\n<p>select Count(O.OrderID) from Orders As O where O.CustomerID = &#8216;ANATR&#8217;<\/p>\n\n\n\n<p><strong>Monto total de \u00d3rdenes de un Cliente<\/strong><\/p>\n\n\n\n<p>select Sum(O.Freight) from Orders As O where O.CustomerID = &#8216;ANATR&#8217;<br>go<\/p>\n\n\n\n<p><strong>&nbsp;Construir la instrucci\u00f3n&nbsp;final con Subconsultas<\/strong><\/p>\n\n\n\n<p>select C.CustomerID As &#8216;C\u00f3digo Cliente&#8217;, C.Companyname As &#8216;Cliente&#8217;,<br>(select Count(O.OrderID) from Orders As O where O.CustomerID = C.CustomerID) As &#8216;Cantidad de \u00d3rdenes&#8217;,<br>(select Sum(O.Freight) from Orders As O where O.CustomerID = C.CustomerID) As &#8216;Monto Total&#8217;<br>from customers As C where country = &#8216;Mexico&#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_Caso2_01.png\" alt=\"\" class=\"wp-image-1183\" width=\"793\" height=\"196\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Consultas_Subconsultas_Caso2_01.png 878w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Consultas_Subconsultas_Caso2_01-300x74.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Consultas_Subconsultas_Caso2_01-768x191.png 768w\" sizes=\"auto, (max-width: 793px) 100vw, 793px\" \/><\/figure><\/div>\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><strong>3. &#8212; Productos vendidos en Agosto de 1997, incluir la cantidad vendida y el monto total<\/strong><\/p>\n\n\n\n<p>Select distinct D.ProductID ,<br>(select P.ProductName from Products As P where P.ProductID = D.ProductID) As &#8216;Descripci\u00f3n&#8217;,<br>(select sum(OD.Quantity) from [Order Details] As Od<br>join Orders As O on OD.ORderID = O.OrderId<br>where Month(O.OrderDate) = 8 and<br>Year(O.OrderDate) = 1997<br>and OD.ProductID = P.ProductID ) As &#8216;Cantidad de Productos&#8217;,<br>(select sum(OD.Quantity * OD.UnitPrice) from [Order Details] As Od<br>join Orders As O on OD.ORderID = O.OrderId<br>where Month(O.OrderDate) = 8 and<br>Year(O.OrderDate) = 1997<br>and OD.ProductID = P.ProductID ) As &#8216;Monto Total&#8217;<br>from [Order Details] As D<br>join Products As P on D.ProductID = P.ProductID<br>where D.OrderID in<br>(select O.OrderID from Orders as O where Month(O.OrderDate) = 8 and<br>Year(O.OrderDate) = 1997)<br>order by D.ProductID<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_Caso2_03.png\" alt=\"\" class=\"wp-image-1184\" width=\"895\" height=\"384\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Consultas_Subconsultas_Caso2_03.png 810w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Consultas_Subconsultas_Caso2_03-300x129.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Consultas_Subconsultas_Caso2_03-768x330.png 768w\" sizes=\"auto, (max-width: 895px) 100vw, 895px\" \/><\/figure><\/div>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Subconsultas &#8211; Casos pr\u00e1cticos 2 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=757\">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-757","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\/757","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=757"}],"version-history":[{"count":1,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/757\/revisions"}],"predecessor-version":[{"id":1185,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/757\/revisions\/1185"}],"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=757"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=757"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=757"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}