Subconsultas – Casos prácticos 2

Subconsultas – Casos prácticos 2

Las subconsultas se explicaron en un post previo (Ver Subconsultas), este post presenta ejercicios algo más complejos, además de realizar el análisis los costos de ejecución usando el Plan de ejecución.

Ejercicios

 

  1. Listar los proveedores que no tiene asignado una región,  incluir la cantidad de productos que provee

Primero los proveedores que no tienen asignada una región

select S.SupplierID, S.CompanyName  from Suppliers As S where S.Region is Null
go

Como ejemplo vamos a contar los productos que provee el proveedor 1

select Count(P.ProductId) from products As P where P.SupplierID = 1
go

Construir la instrucción con Subconsulta

select S.SupplierID As ‘Código Proveedor’, S.CompanyName As ‘Nombre’,
(select Count(P.ProductId) from products As P where P.SupplierID = S.SupplierID)
As ‘Cantidad de Productos’
from Suppliers As S where S.Region is Null
go
— Costo: 0.0097787

Usando Joins  (Ver Joins)

select S.SupplierID As ‘Código Proveedor’, S.CompanyName As ‘Nombre’,
Count(P.ProductId) As ‘Cantidad de Productos’
from Suppliers As S
join Products As P on S.SupplierID = P.SupplierID
where S.Region is Null
group by S.SupplierID, S.CompanyName
go
— Costo: 0.0099151

Note que con el uso de Joins el costo es mayor.



2. — Cientes de Mèxico, incluyan la cantidad de órdenes, el monto total de las òrdenes

Clientes de México

select C.CustomerID As ‘Código Cliente’, C.Companyname As ‘Cliente’
from customers As C where country = ‘Mexico’
go

Cantidad de Órdenes de un Cliente

select Count(O.OrderID) from Orders As O where O.CustomerID = ‘ANATR’

Monto total de Órdenes de un Cliente

select Sum(O.Freight) from Orders As O where O.CustomerID = ‘ANATR’
go

 Construir la instrucción final con Subconsultas

select C.CustomerID As ‘Código Cliente’, C.Companyname As ‘Cliente’,
(select Count(O.OrderID) from Orders As O where O.CustomerID = C.CustomerID) As ‘Cantidad de Órdenes’,
(select Sum(O.Freight) from Orders As O where O.CustomerID = C.CustomerID) As ‘Monto Total’
from customers As C where country = ‘Mexico’
go

3. — Productos vendidos en Agosto de 1997, incluir la cantidad vendida y el monto total

Select distinct D.ProductID ,
(select P.ProductName from Products As P where P.ProductID = D.ProductID) As ‘Descripción’,
(select sum(OD.Quantity) from [Order Details] As Od
join Orders As O on OD.ORderID = O.OrderId
where Month(O.OrderDate) = 8 and
Year(O.OrderDate) = 1997
and OD.ProductID = P.ProductID ) As ‘Cantidad de Productos’,
(select sum(OD.Quantity * OD.UnitPrice) from [Order Details] As Od
join Orders As O on OD.ORderID = O.OrderId
where Month(O.OrderDate) = 8 and
Year(O.OrderDate) = 1997
and OD.ProductID = P.ProductID ) As ‘Monto Total’
from [Order Details] As D
join Products As P on D.ProductID = P.ProductID
where D.OrderID in
(select O.OrderID from Orders as O where Month(O.OrderDate) = 8 and
Year(O.OrderDate) = 1997)
order by D.ProductID
go