SQL Repaso para final

/*1.1.	Crear una tabla llamada ESTVTA a partir de los
clientes cuya venta anual supera los S/.40,000. Los atributos a
considerar son: ID cliente, nombre, año ,venta del año.
Verificar definición de la nueva tabla (sp_Help).*/

select c.CustomerID, c.CompanyName, year(o.OrderDate)'Anio', SUM(od.Quantity*od.UnitPrice*(1-od.Discount)) as Monto
from Customers c join Orders o on c.CustomerID=o.CustomerID join [Order Details] od on od.OrderID=o.OrderID
group by c.CustomerID, c.CompanyName, year(o.OrderDate)
having SUM(od.Quantity*od.UnitPrice*(1-od.Discount))>40000

create Table ESTVTA
(
	CustomerID nchar(5),
	ContactName nvarchar(30),
	Anio int,
	Monto int
)

insert into ESTVTA
select c.CustomerID, c.CompanyName, year(o.OrderDate)'Anio', SUM(od.Quantity*od.UnitPrice*(1-od.Discount)) as Monto
from Customers c join Orders o on c.CustomerID=o.CustomerID join [Order Details] od on od.OrderID=o.OrderID
group by c.CustomerID, c.CompanyName, year(o.OrderDate)
having SUM(od.Quantity*od.UnitPrice*(1-od.Discount))>40000

select *
from ESTVTA


/*1.2.Adicionar una nueva columna a la tabla ESTVTA
llamada "QProductos" (de tipo INT). Verificar la nueva
estructura de Nueva_Tabla.(sp_help). Actualice el nuevo
atributo con la venta en unidades por cliente.*/

ALTER TABLE ESTVTA
ADD QProductos int

select c.CustomerID, c.CompanyName, year(o.OrderDate)'Anio', COUNT(od.Quantity) as Monto
from Customers c join Orders o on c.CustomerID=o.CustomerID join [Order Details] od on od.OrderID=o.OrderID
group by c.CustomerID, c.CompanyName, year(o.OrderDate)

update ESTVTA
set QProductos=(select SUM(od.Quantity) as Monto
				from Customers c join Orders o on c.CustomerID=o.CustomerID join [Order Details] od on od.OrderID=o.OrderID
				where c.CustomerID=ESTVTA.CustomerID and year(o.OrderDate)=ESTVTA.Anio)

/*1.3Escriba un trigger que reste el monto de venta y la cantidad de
productos de la tabla ESTVTA cada vez que se elimine un registro
de OrderDetails*/
go
create trigger tr_dimamo_dimqprod
on [Order Details]
for delete
as
begin
	UPDATE ESTVTA
	set ESTVTA.Monto=ESTVTA.Monto-(od.Quantity*od.UnitPrice*(1-od.Discount)), ESTVTA.QProductos=ESTVTA.QProductos-(od.Quantity)
	from deleted od join Orders o on od.OrderID=o.OrderID join Customers c on c.CustomerID=o.CustomerID
	where o.CustomerID=ESTVTA.CustomerID and year(o.OrderDate)=ESTVTA.Anio
end
go

select *
from [Order Details] od

select c.CustomerID, year(o.OrderDate)as Anio, SUM(od.Quantity*od.UnitPrice*(1-od.Discount)) 'Monto'
from [Order Details] od join Orders o on o.OrderID=od.OrderID join Customers c on c.CustomerID=o.CustomerID
group by c.CustomerID, year(o.OrderDate)

select o.CustomerID, o.OrderID, od.ProductID, year((o.OrderDate))as Anio, od.UnitPrice*od.Quantity as Monto
from [Order Details] od join Orders o on o.OrderID=od.OrderID
order by o.CustomerID

delete from [Order Details]
where OrderID=10514 and ProductID=20

/*ERNSH	10514	20	2017	3159.00*/

/*Modificar la tabla Empleados y agregar el campo ImpVendido de tipo INT NOT NULL con valor
 por defecto de 0.00 Crear un trigger en la tabla order details para que cada vez que se venda
 un producto se actualice el campo impVendido  de la tabla empleados con el el valor que tiene en
 ese momento más la cantidad vendida en el order details. */

 ALTER TABLE Employees
 drop column ImpVendido

 update Employees
 set ImpVendido=null

 select *
 from Employees



/*Pregunta 1 (3 puntos) Crear una función que retorne el nombre de cliente, nombre del proveedor,
código y nombre de los productos, cantidad vendida (quantity), total de importe bruto vendido(monto) de todas las órdenes
emitidas en un país y un rango de tiempo ingresados como parámetros. */


create FUNCTION fn_orders_from_cntry_nrange(@pais nvarchar(15), @rango int)
returns TABLE as
return
(

)

/*Pregunta 2 (3 puntos) Crear un Procedimiento para insertar una línea de detalle de la orden (Order Details), incluir actualizar unidades en stock y en orden. Debe especificar los parámetros necesarios para la inserción*/


/*Northwind desea saber si las ventas en cantidad de unidades y
en cantidad de órdenes están creciendo por región para saber si debe
expandir su red de Compañías de Envío en determinadas regiones.
Por lo tanto se le ha pedido obtener un procedimiento donde muestre la región que
ha tenido más venta en cantidad de unidades y en cantidad de órdenes dado un año
 y un mes ingresado como parámetro. Deberá mostrar además la cantidad de unidades y
 cantidades de órdenes vendidas en esas regiones el año anterior*/