UTNianos

Versión completa: Parcial resuelto Gdd Zaffaroni
Actualmente estas viendo una versión simplificada de nuestro contenido. Ver la versión completa con el formato correcto.
Hola les dejo el parcial del 2do cuatri de 2019 con las resoluciones del profe.

Éxitos!!

Query



CREATE VIEW comprasFabricanteLider (nombreFabricante, nomCliente, apeCliente, producto, totalPesos, totalCantidad)
AS
SELECT manu_name,
fname,
lname,
description, sum(unit_price * quantity) totalPesos,
sum(quantity) totalCantidad
FROM orders o JOIN items i ON o.order_num=i.order_num
JOIN customer c ON o.customer_num=c.customer_num
JOIN product_types p ON i.stock_num=p.stock_num
JOIN manufact m ON i.manu_code=m.manu_code
JOIN (SELECT TOP 1 manu_code, SUM(unit_price * quantity) totsum
FROM items
GROUP BY manu_code
ORDER BY totsum DESC) max ON max.manu_code=i.manu_code
WHERE description LIKE '%ball%'
GROUP BY manu_name, c.customer_num, fname, lname, description
HAVING SUM(unit_price * quantity)/SUM(quantity)>150



Stored Procedure



CREATE Procedure vxmProc
@fecha datetime
as
begin
begin try
begin transaction

declare @cadenaFecha varchar(6)
set @cadenaFecha = cast(year(@fecha)*100+month(@fecha) as varchar(6))

insert into VENTASxMES
Select @cadenaFecha, i.stock_num, i.manu_code,
sum(case when p.unit_code = 1 then quantity
when p.unit_code = 2 then quantity*2
when p.unit_code = 3 then quantity*12
end),
sum(quantity*i.unit_price)
from orders o
join items i on o.order_num = i.order_num
join products p on p.manu_code = i.manu_code
and p.stock_num = i.stock_num
where year(order_date) = year(@fecha) and
month(order_date) = month(@fecha)
group by i.manu_code, i.stock_num;
--
commit;
end try
begin catch
rollback;
end catch
end



Trigger



create trigger auditCustomer on customer
after delete, update as
begin
declare @customer_num int,
@apeynomNew varchar(40), @stateNew char(2),
@customer_num_refered_byNew int,
@apeynomOld varchar(40), @stateOld char(2),
@customer_num_refered_byOld int

declare auditCur CURSOR FOR
select d.customer_num,
i.lname + '' + i.fname, i.state, i.customer_num_referedby,
d.lname + '' + d.fname, d.state, d.customer_num_referedby
from deleted d
left join inserted i on i.customer_num = d.customer_num;

OPEN auditCur
FETCH NEXT FROM auditCur
into @customer_num,
@apeynomNew, @stateNew, @customer_num_refered_byNew,
@apeynomOld, @stateOld, @customer_num_refered_byOld;

while (@@FETCH_STATUS = 0)
begin
begin try
begin transaction
if not exists(select 1 from inserted)
begin
insert into customer_audit(customer_num, update_Date, apeynom_OLD,
state_Old, customer_num_referedby_OLD, update_user)
values (@customer_num, getDate(), @apeynomOld, @stateOld,
@customer_num_refered_byOld, SYSTEM_USER)
end
else
begin
if not exists(select 1 from customer
where customer_num = @customer_num_refered_byNew)
THROW 50001, 'Referente inexistente', 1;
if not exists (select 1 from state where state = @stateNEW)
THROW 50002, 'Estado inexistente', 1;

insert into customer_audit(customer_num, update_Date,
apeynom_NEW, state_NEW, customer_num_referedby_NEW,
apeynom_OLD, state_Old, customer_num_referedby_OLD,
update_user)
values (@customer_num, getDate(),
@apeynomNew, @stateNew, @customer_num_refered_byNew,
@apeynomOld, @stateOld, @customer_num_refered_byOld, SYSTEM_USER)
end
commit transaction
end try

begin catch
rollback transaction
end catch

FETCH NEXT FROM auditCur
into @customer_num,
@apeynomNew, @stateNew, @customer_num_refered_byNew,
@apeynomOld, @stateOld, @customer_num_refered_byOld;
end

close auditCur
deallocate auditCur
END;


URLs de referencia