Use Northwind Database for SQL coding

1.  Consider the following 4-table-join query (we looked at this in Module 1!):

select s.ShipperID, s.CompanyName, o.OrderID, o.ShippedDate,
e.EmployeeID, e.Lastname, o.CustomerID, c.CompanyName
from Shippers s 
join Orders o on s.ShipperID = o.ShipVia
join Employees e on e.EmployeeID = o.EmployeeID
join Customers c on c.Customerid = o.Customerid
Order by ShipperID, ShippedDate desc

Perform the following steps:
1)  Display an estimated execution plan (Highlight the query, go to your Query menu, select "Display Estimated Execution Plan")
2)  Study the execution plan - write it down, make a screenshot, look up what the icons mean, so that you understand this as much as you can.  
3)  Execute the following code to create an index:

CREATE NONCLUSTERED INDEX idxOrdersShipVia
ON [dbo].[Orders] ([ShipVia])
INCLUDE ([OrderID],[CustomerID],[EmployeeID],[ShippedDate])

4)  Do step #1 again, ie., display the estimated execution plan 
5)  Discuss below how the index changed (or did not change) the execution plan for the query.  Discuss whether or not you 
     think this index should be permanently implemented on the Northwind database. Use the course discussion area for this module 
     to get input from other students. 
*/
-- list your answers here:



/*
2.  Write code below using the Northwind database that will:

a)  Create a transaction
b)  Write a query to perform an update on the [order details] table that will add 10% to all 
  unit prices. 
c)  Create a save point after the query in b)
d)  Write a query to to perform an update on the Products table that will add $2.00 to all unit prices
e)  Write the statement to rollback to the save point
f)   Right under the statement in e), Write a statement that rolls back to the beginning of the 
      Transaction. 

*/
-- list your answers here:



/*
3..
You have a colleague that is executing some work in a transaction on the 
Customers table. She is running her transaction with an isolation level of Serializable.
You need to run a quick query for your boss that groups and counts all customers by their 
Country.  Write the query below that will execute (and not be blocked):
*/
-- list your answers here: