--Exercise 3 ================================================================================
go
create view Categories as
select b.Category
 , QtySold = sum(case when d.Qty is not null then d.Qty else 0 end)
 , TotalRevenue = sum(d.price* d.Qty)
from OrderDetail d
left join OrderHeader h on h.OrderNo = d.OrderNo
left join Book b on b.ISBN = d.ISBN
group by b.Category

-- Exercise 4 ===============================================================================
go
create trigger ValidateUnitPriceOnInsert
on OrderDetail after insert
as
delete od
  from OrderDetail od
  join inserted i on i.OrderNo = od.OrderNo
                 and i.OrderLine = od.OrderLine
  join Book k on i.ISBN = k.ISBN
  where i.Price < (k.Price)/2
go
create trigger ValidateUnitPriceOnUpdate
on OrderDetail after update as
update od
   set ISBN = d.ISBN
     , Price = d.Price
	 , Qty = d.Qty
  from OrderDetail od
  join deleted d on d.OrderNo = od.OrderNo
                and d.OrderLine = od.OrderLine
  join inserted i on i.OrderNo = od.OrderNo
			    and i.OrderLine = od.OrderLine
  join Book k on i.ISBN = k.ISBN
  where i.Price < (k.Price)/2
    and (d.ISBN < > i.ISBN
	     or d.Price < > i.Price)