Workshop

The Workshop provides quiz questions to help you solidify your understanding of the material covered and exercises to provide you with experience in using what you've learned. Try to answer the quiz and exercise questions before checking the answers in Appendix A, "Answers to Quizzes and Exercises," and make sure that you understand the answers before continuing to the next lesson.

Quiz

set implicit_transactions on
set xact_abort on
set lock_timeout 0

Exercise

In the following exercise, correct this batch to avoid a common transaction programming problem. Describe the problem and then suggest a solution.

begin transaction
if (
    select avg(UnitPrice)
    from Products
    where Category = 1
) > 15
update Products
   set UnitPrice = UnitPrice * .90
 where CategoryID = 1
/* implement 30-day price protection */
update [Order Details]
   set UnitPrice = UnitPrice * .90
 where ProductID in (
       select ProductID
       from Products
       where CategoryID = 1
       )
   and OrderID in (
   select OrderID
   from Orders
   where datediff(dd, OrderDate, getdate()) <= 30
   )
commit transaction