{"id":395,"date":"2017-09-18T22:25:25","date_gmt":"2017-09-18T22:25:25","guid":{"rendered":"http:\/\/www.manualsqlserver.com\/?p=395"},"modified":"2020-07-17T15:37:22","modified_gmt":"2020-07-17T15:37:22","slug":"subconsultas-casos-practicos","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=395","title":{"rendered":"Subconsultas en SQL Server &#8211; casos pr\u00e1cticos"},"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=\"286\" height=\"225\"\/><\/figure>\n\n\n\n<h1 class=\"wp-block-heading\"><strong>Subconsultas &#8211; ejercicios<\/strong><\/h1>\n\n\n\n<p>Las subconsultas permiten extraer informaci\u00f3n que incluyen varias tablas, estas generalmente reportan un solo dato a un conjunto de resultados con una columna. Para la parte te\u00f3rica completa y las condiciones y restricciones ver <a href=\"http:\/\/www.manualsqlserver.com\/?p=219\">Subconsultas<\/a>.<\/p>\n\n\n\n<p>En este art\u00edculo veremos como desarrollar ejemplos analizando los datos y armando por pasos la subconsulta<\/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<h3 class=\"wp-block-heading\">Ejercicios<\/h3>\n\n\n\n<p><strong>Usando Northwind<\/strong><\/p>\n\n\n\n<p>use northwind<br>go<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Algo de teor\u00eda de Subconsultas<\/strong><\/h2>\n\n\n\n<p><strong>Pueden utilizarse de dos formas:<\/strong><\/p>\n\n\n\n<p>1. Dentro de la Lista de campos de la instrucci\u00f3n Select<\/p>\n\n\n\n<p>select ListaCampos, (Select Campo from TablaSubconsulta where&#8230;)&nbsp; from TablaPrincipal<\/p>\n\n\n\n<p>2. En la cl\u00e1sula Where<\/p>\n\n\n\n<p>Select ListadeCampos, OtroCampo, UltimoCampo from Tabla<br>Where Campo =( Select\u2026 )<\/p>\n\n\n\n<p><strong>1. &#8212; Listado de Clientes (Customers) que compraron en Agosto de 1997<\/strong><\/p>\n\n\n\n<p>select * from Customers<br>where CustomerID in<br>(<strong>select distinct CustomerID from Orders where Datename(mm,OrderDate) = &#8216;Agosto&#8217;&nbsp;<\/strong><br><strong> and year(OrderDate) = 1997<\/strong>)<br>go<\/p>\n\n\n\n<p>Note la subconsulta en negrita.<\/p>\n\n\n\n<p><strong>2. &#8212; Listado con las cantidades vendidas de los productos descontinuados.&nbsp;<\/strong><strong>Incluir solamente los que tienen Stock<\/strong><\/p>\n\n\n\n<p><strong>Primero: los productos descontinuados<\/strong><br>select * from Products where Discontinued = 1<br>go<\/p>\n\n\n\n<p><strong>\u00bfD\u00f3nde est\u00e1n las unidades vendidas?<\/strong><br>select * from [Order Details]<br>go<\/p>\n\n\n\n<p><strong>Armando la soluci\u00f3n<\/strong><\/p>\n\n\n\n<p>select P.ProductID, P.ProductName, p.UnitPrice from Products As P<br>where P.Discontinued = 1 and P.UnitsInStock &gt; 0<br>go<br><strong>C\u00f3digos de los descontinuados que tiene Stock<\/strong><br>select P.ProductID from Products &nbsp;As P where P.Discontinued = 1 and P.UnitsInStock &gt; 0<br>go<\/p>\n\n\n\n<p><strong>Cantidades vendidas de cada producto<\/strong><br>select Sum(Od.Quantity), OD.ProductID from [Order Details] As OD<br>where OD.ProductID in (select P.ProductID from Products<br>As P where P.Discontinued = 1 and P.UnitsInStock > 0)<br>Group by OD.ProductID<br>go<\/p>\n\n\n\n<p><strong>SOLUCION<\/strong><br>select P.ProductID, P.ProductName, p.UnitPrice,<br>(select Sum(Od.Quantity) from [Order Details] As OD &nbsp;where OD.ProductID in (select P.ProductID from Products As P where P.Discontinued = 1 and P.UnitsInStock &gt; 0)<br>AND od.ProductID = p.ProductID) As &#8216;Cantidad Vendidas&#8217;<br>from Products As P &nbsp;where P.Discontinued = 1 and P.UnitsInStock &gt; 0<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_SubconsultasEjemplo01.png\" alt=\"\" class=\"wp-image-1175\" width=\"857\" height=\"222\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_SubconsultasEjemplo01.png 830w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_SubconsultasEjemplo01-300x78.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_SubconsultasEjemplo01-768x200.png 768w\" sizes=\"auto, (max-width: 857px) 100vw, 857px\" \/><\/figure><\/div>\n\n\n\n<p><strong>3. &#8212; Empleados y la cantidad de \u00f3rdenes generadas y NO atendidas<\/strong><\/p>\n\n\n\n<p><strong>Empleados<\/strong><br>select E.EmployeeID, Empleado = E.LastName + Space(1) + E.FirstName<br>from Employees As E<\/p>\n\n\n\n<p><strong>Conteo de las Ordenes NO ATENDIDAS<\/strong><br>select COUNT(O.OrderID) As &#8216;\u00d3rdenes no Atendidas&#8217; &nbsp;from Orders As O where ShippedDate is null<br>go<\/p>\n\n\n\n<p><strong>SOLUCION<\/strong><br>select E.EmployeeID, Empleado = E.LastName + Space(1) + E.FirstName,<br>( &#8212; INICIO DE LA SUBCONSULTA<br>select COUNT(O.OrderID) from Orders As O where ShippedDate is null<br>AND O.EmployeeID = E.EmployeeID<br>) As &#8216;\u00d3rdenes no Atendidas&#8217;&#8211; FINAL DE LA SUBCONSULTA<br>from Employees As E<br>where (select COUNT(O.OrderID) from Orders As O where ShippedDate is null<br>AND O.EmployeeID = E.EmployeeID) &gt; 0<br>order by &#8216;\u00d3rdenes no Atendidas&#8217; desc<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_SubconsultasEjemplo02.png\" alt=\"\" class=\"wp-image-1176\" width=\"819\" height=\"342\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_SubconsultasEjemplo02.png 680w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_SubconsultasEjemplo02-300x125.png 300w\" sizes=\"auto, (max-width: 819px) 100vw, 819px\" \/><\/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><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Subconsultas &#8211; ejercicios Las subconsultas permiten extraer informaci\u00f3n que incluyen varias tablas, estas generalmente reportan un solo dato a un conjunto de resultados con una columna. Para la parte te\u00f3rica completa y las condiciones y restricciones ver Subconsultas. En este art\u00edculo veremos como desarrollar ejemplos analizando los datos y armando por pasos la subconsulta<\/p><p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=395\">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-395","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\/395","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=395"}],"version-history":[{"count":1,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/395\/revisions"}],"predecessor-version":[{"id":1180,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/395\/revisions\/1180"}],"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=395"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=395"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=395"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}