Assignment:
1. Create the following tables in the Northwind database:
o Regions table
|
Column Name
|
Datatype
|
Primary Key (Y/N)
|
Nullable (Y/N)
|
|
RegionID
|
Integer
|
Y
|
N
|
|
RegionDescription
|
Character (50)
|
N
|
N
|
o Department table
|
Column Name
|
Datatype
|
Primary Key (Y/N)
|
Nullable (Y/N)
|
|
DepartmentID
|
Integer
|
Y
|
N
|
|
DepartmentDescription
|
Character (50)
|
N
|
N
|
2. Insert the following data into the 2 tables:
o Regions table
|
1
|
Eastern
|
|
2
|
Western
|
|
3
|
Northern
|
|
4
|
Southern
|
o Department table
|
10
|
CEO
|
|
20
|
Sales
|
|
30
|
Marketing
|
3. Alter the Employees table, and add a column:
o Employees table
|
Column Name
|
Datatype
|
Primary Key (Y/N)
|
Nullable (Y/N)
|
|
DepartmentID
|
Integer
|
N
|
Y
|
4. Alter the Employee table to establish a foreign key between Employees and Departments.
o Employees.DepartmentID references Departments.DepartmentID
5. Alter the Territories table to establish a foreign key between Territories and Regions.
o Territories.RegionID references Regions.RegionID
6. Update the data in the Employees table so that employees with the following managers are listed as working in the following departments:
|
ReportsTo
|
Department
|
|
Andrew Fuller
|
Sales
|
|
Steven Buchanan
|
Marketing
|
|
All Others
|
CEO
|