20-12-2019, 18:36
Hola les dejo el parcial del 2do cuatri de 2019 con las resoluciones del profe.
Éxitos!!
Query
Stored Procedure
Trigger
É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;