SUB CONSULTAS

Para realizar los siguientes ejercicios, se recomienda descargar los archivosBDFacturacion_DML y BDFacturacion_DDL que se encuentran en la categorÍa 
BASE DE DATOS, de este blog.


--Listar los productos con sus respectivas categorías
select codigoProducto, nombreProducto, precioProducto,
(select nombreCategoria from tbCategoria
where codigoCategoria= tbProducto.codigoCategoria) as [categoriaProducto]
from tbProducto
--Listar las categorías con sus respectivos números de productos
select codigoCategoria,nombreCategoria,
(select count(*) from tbProducto
where codigoCategoria=tbCategoria.codigoCategoria) as [numeroProductos]
from tbCategoria
--Listar los productos que ayan vendido 2 unidades
select * from(select p.nombreProducto,count(*) as [cantidad]
from tbProducto p
inner join tbDetalleFactura df on p.codigoProducto=df.codigoProducto
group by p.nombreProducto,df.codigoProducto) as productos
where cantidad = 2
--Listar los productos cuyas ventas esten entre 100 y 500
select * from(select p.nombreProducto,
isnull((select sum(importeProducto) from tbDetalleFactura
where codigoProducto=p.codigoProducto),0) as [ventaProducto]
from tbProducto p) as productos
where ventaProducto between 100 and 500
--Listar aquellos productos que hayan sido facturados
select codigoProducto, nombreProducto, precioProducto
from tbProducto
where codigoProducto in (select distinct codigoProducto from tbDetalleFactura)
--Listar los productos de la categoría 'Memorias'
select codigoProducto, nombreProducto, precioProducto
from tbProducto
where codigoProducto in(select p.codigoProducto
from tbProducto p inner join tbCategoria c on c.codigoCategoria=p.codigoCategoria
where c.nombreCategoria = 'Memorias')
 

STORED PROCEDURES

Para realizar los siguientes ejercicios, se recomienda descargar los archivosBDFacturacion_DML y BDFacturacion_DDL que se encuentran en la categoria

BASE DE DATOS, de este blog.

-- Listar las facturas(número,total) correspondientes
-- al cliente con código 'c001'
create procedure procedureFactura
@codigo char(4)
as begin
declare @cliente as varchar(50)
declare @factura as varchar(10)
declare @total as varchar(10)
declare @cont as int
declare CursorFacura cursor for
select tbCliente.nombreCliente,tbFactura.numeroFactura,tbFactura.totalFactura
from tbCliente inner join tbFactura on tbCliente.codigoCliente=tbFactura.codigoCliente
where tbCliente.codigoCliente= @codigo
open CursorFacura
set @cont=1
print ' LISTA DE FACTURAS POR CLIENTE'
print ' ============================='
fetch CursorFacura into @cliente,@factura,@total
while (@@fetch_status=0)
begin
print 'Cliente '+convert(varchar(3),@cont)+': '+ @cliente
print ' Factura #'+@factura+' Total S/.'+ @total
print ''
fetch CursorFacura into @cliente,@factura,@total
set @cont=@cont+1
end
close CursorFacura
deallocate CursorFacura
end
--probando procedimiento
exec procedureFactura 'c001'

CURSORES

Para realizar los siguientes ejercicios, se recomienda descargar los archivos BDFacturacion_DML y BDFacturacion_DDL que se encuentran en la categoria

BASE DE DATOS, de este blog.

--Listar todas las categorías
declare @codigo as varchar(4)
declare @nombre as varchar(50)declare CursorCategoria cursor for
select * from tbCategoria
open CursorCategoria
print 'LISTA DE CATEGORIAS'
print '==================='
fetch CursorCategoria into @codigo,@nombre
while (@@fetch_status=0) begin
print @codigo +' '+ @nombre fetch CursorCategoria into @codigo,@nombreend
close CursorCategoria
deallocate CursorCategoria
--Listar las facturas(número,total) con su respectivos clientes
declare @cliente as varchar(50)
declare @factura as varchar(10)
declare @total as varchar(10)declare @cont as int
declare CursorFacura cursor for select tbCliente.nombreCliente,tbFactura.numeroFactura,tbFactura.totalFactura
from tbCliente
inner join tbFactura on tbCliente.codigoCliente=tbFactura.codigoCliente
open CursorFacura
set @cont=1
print ' LISTA DE FACTURAS POR CLIENTE'
print ' ============================='
fetch CursorFacura into @cliente,@factura,@total
while (@@fetch_status=0)begin
print 'Cliente '+convert(varchar(3),@cont)+ ': ' + @cliente
print ' Factura #'+@factura+' Total S/.'+ @total
print ''
fetch CursorFacura into @cliente,@factura,@total
set @cont=@cont+1end
close CursorFacura
deallocate CursorFacura
--Listar las facturas(número,total) con su respectivos clientes y el detalle
declare @cliente as varchar(50)
declare @factura as varchar(10)
declare @total as varchar(10)declare @cont as int
declare CursorFacura cursor for
select  tbCliente.nombreCliente,tbFactura.numeroFactura,tbFactura.totalFactura
from tbCliente
inner join tbFactura on tbCliente.codigoCliente=tbFactura.codigoCliente
open CursorFacura
set @cont=1
print 'LISTA DE FACTURAS POR CLIENTE'
print '============================='
fetch CursorFacura into @cliente,@factura,@total
while (@@fetch_status=0)begin
print 'Cliente '+convert(varchar(3),@cont)+': '+ @cliente
print ' Factura #'+@factura+' Total S/.'+ @total
print ' DETALLE DE LA FACTURA # '+ @factura
print ' ------------------------------'
declare @producto as varchar(30)
declare @cantidad as varchar(10)
declare @precio as varchar(10)
declare @importe as varchar(10)
declare CursorDetalle cursor for
select tbProducto.nombreProducto,tbDetalleFactura.cantidadProducto,tbProducto.precioProducto,
tbDetalleFactura.importeProducto
from tbProducto
inner join tbDetalleFactura on tbProducto.codigoProducto=tbDetalleFactura.codigoProducto
where tbDetalleFactura.numeroFactura = @factura
open CursorDetalle
fetch CursorDetalle into @producto,@cantidad,@precio,@importe
while (@@fetch_status=0)begin
print ' Producto: '+ @producto
print ' Precio: S/.'+ @precio+' Cantidad: '+@cantidad+' Importe: S/.'+ @importe
fetch CursorDetalle into @producto,@cantidad,@precio,@importe
end
close CursorDetalle
deallocate CursorDetalle
print ''
fetch CursorFacura into @cliente,@factura,@total
set @cont=@cont+1end
close CursorFacura
deallocate CursorFacura

JUNTURAS

--1
select country as [Pas],titleofcourtesy as [Ttulo],count(titleofcourtesy) as [Cantidad]
from employees
group by country,titleofcourtesy
--2
select e.lastname+' '+e.firstname as [empleado],
c.companyname as [cliente],c.country,sum(od.unitprice*od.quantity) as [total]
from orders o 
inner join customers c on o.customerid=c.customerid
inner join employees e on e.employeeid=o.employeeid
inner join [order details] od on o.orderid=od.orderid
where c.country='Germany'
group by od.orderid,e.lastname,e.firstname,c.companyname,c.country
--3
select e.employeeid,t.territoryDescription
from employees e
inner join employeeTerritories et on e.employeeid=et.employeeid
right join territories t on t.territoryid=et.territoryid
--4
select p.productname,p.unitprice,p.unitsinstock,s.companyname,c.categoryname
from products p
inner join suppliers s on s.supplierid=p.supplierid
inner join categories c on c.categoryid=p.categoryid
where c.categoryname like '[b-m]%'
--5
select c.companyname,count(o.customerid),c.country
from customers cinner join orders o on c.customerid=o.customerid
where c.country='germany'
group by o.customerid,c.companyname,c.country
--6
select p.categoryid,p.supplierid,count(p.productid)
from suppliers s
inner join products p on p.supplierid=s.supplierid
inner join categories c on c.categoryid=p.categoryid
where p.supplierid between 1 and 3 and p.unitprice>18
group by p.supplierid,p.categoryid
--7
select e.employeeid,e.lastname+' '+e.firstname as [empleado],t.territorydescription,r.regiondescription
from employees e 
inner join employeeterritories et on e.employeeid=et.employeeid
inner join territories t on t.territoryid=et.territoryid
inner join region r on r.regionid=t.regionid
--8
select e.lastname,e.firstname,count(et.territoryid) as [cantidad]
from employees e 
inner join employeeterritories et on e.employeeid=et.employeeid
inner join territories t on t.territoryid=et.territoryid
group by et.employeeid,e.lastname,e.firstname
order by e.lastname desc