Select – Subconsultas

Las subconsultas son instrucciones Select que se utilizan dentro de otra instrucción Select.

Pueden utilizarse de dos formas:

1. Dentro de la Lista de campos de la instrucción Select

select  ListaCampos, (Select ….)  from Tabla

2. En las clásulas Where o Having

Select ListadeCampos, OtroCampo, UltimoCampo from Tabla
Where Campo =( Select… )Ejemplos:

1. Listar Clientes y el monto comprado por cada uno

select  C.CustomerID As ‘Código’, C.CompanyName As ‘Cliente’, C.Address As ‘Dirección’,
(select sum(freight) from Orders As O   where O.CustomerID = C.CustomerID) As ‘Monto Comprado’ ,City As ‘Ciudad’
from Customers As C  order by ‘Monto Comprado’ desc
go

Note la subconsulta entre paréntesis.

2. Por empleado, Cantidad de órdenes, monto total ordenados por monto descendente

select TitleOfCourtesy + space(1)+ FirstName + SPACE(1)+ LastName As Empleado ,
(select count(*)  from Orders As O  where O.EmployeeID = E.EmployeeID )  As ‘Cantidad’,
(select sum(o.Freight)  from Orders As O  where O.EmployeeID = E.EmployeeID ) As ‘Monto Total’
from Employees As E
order by ‘Monto Total’ desc
go

3. Listar las órdenes de los clientes de España

select O.OrderID, O.CustomerID, C.CompanyName, C.Country,  O.OrderDate,O.Freight
from orders AS O  JOIN Customers As C on O.CustomerID=C.CustomerID
WHERE O.CustomerID IN  (select CustomerID from Customers  where country = ‘Spain’)
go

Note la subconsulta entre paréntesis.

3. Montos Totales y la cantidad de Órdenes de los Representantes de Ventas (Sales Representative). Muestre nombre del empleado

select  e.EmployeeID, Empleado = E.LastName + Space(1) + E.FirstName,
sum(O.Freight) As ‘Monto Total’,  count(O.OrderID) As ‘Cantidad de Órdenes’
from Employees As E  join Orders As O on E.EmployeeID = O.EmployeeID
where e.EmployeeID in (select EmployeeID from Employees where Title = ‘Sales Representative’)
Group by e.EmployeeID, E.LastName + Space(1) + E.FirstName  order by E.EmployeeID
go