USE {JagNetID}Northwind

-- Script 1: Provide a list of Territories (include all attributes) in the Southern region
-- by using an appropriate WHERE clause on the RegionID (i.e., do not JOIN tables)


-- Script 2: Insert a Territories record with TerritoryID 30904, 
-- TerritoryDescription Augusta, and a RegionID placing it in the Southern Region

	
-- Script 1 again: Provide a list of Territories (include all attributes) in the Southern region
-- by using an appropriate WHERE clause on the RegionID (i.e., do not JOIN tables)


-- Script 3: Insert an Employees record for yourself using your actual
-- names for LastName & FirstName (other columns can be ignored and left as NULL
-- or filled in with real or fabricated data)


-- The following code retrieves the EmployeeID assigned in the previous INSERT
-- and stores it in the variable @myEmployeeID
DECLARE @myEmployeeID int;
SET @myEmployeeID = @@IDENTITY;
PRINT 'You were assigned EmployeeID ' + CONVERT(varchar, @myEmployeeID);

-- Script 4: Insert an EmployeeTerritories record assigning Territory 30904
-- to you (i.e., the EmployeeID in @myEmployeeID)


-- Script 5: Update your Employees record so that your Title becomes AIST6410 
-- Student and your BirthDate becomes your actual date of birth
-- Hint: You will need a WHERE clause limiting to just your EmployeeID (i.e., the EmployeeID in @myEmployeeID)


-- The following query lists employees and their assigned territories in the 
-- Southern region; your name should be on it for Augusta
SELECT     Employees.EmployeeID, Employees.LastName, Employees.FirstName, Employees.Title, Employees.BirthDate, Territories.TerritoryDescription, Territories.RegionID
FROM       Employees INNER JOIN
                EmployeeTerritories ON Employees.EmployeeID = EmployeeTerritories.EmployeeID INNER JOIN
                Territories ON EmployeeTerritories.TerritoryID = Territories.TerritoryID
WHERE     (Territories.RegionID = 4)

-- Script 6: Write scripts to
--	DELETE any EmployeeTerritories assigned to your EmployeeID (i.e., the EmployeeID in @myEmployeeID)
--      DELETE your Employees record (i.e., the EmployeeID in @myEmployeeID)
--      DELETE Territories with TerritoryID 30904
