1: Write a script to create an EMPLOYEES table in the AdventureWorksLT database using the following specifications:
|
Column Name
|
Datatype
|
Primary Key (Y/N)
|
Nullable (Y/N)
|
|
EmployeeID
|
Integer
|
Y
|
N
|
|
Hire Date
|
datetime
|
N
|
Y
|
|
LastName
|
Character (20)
|
N
|
N
|
|
FirstName
|
Character (10)
|
N
|
N
|
|
PostalCode
|
Character (10)
|
N
|
Y
|
|
Region
|
Character (15)
|
N
|
Y
|
|
Title
|
Character (30)
|
N
|
Y
|
|
County
|
Character (20)
|
N
|
Y
|
2: Write a script to create a REGIONS table in the AdventureWorksLT database using the following specifications:
|
Column Name
|
Datatype
|
Primary Key (Y/N)
|
Nullable (Y/N)
|
|
RegionID
|
Integer
|
Y
|
N
|
|
RegionDescription
|
Character (50)
|
N
|
N
|
3: Write a script to create a DEPARTMENTS table in the AdventureWorks database using the following specifications:
|
Column Name
|
Datatype
|
Primary Key (Y/N)
|
Nullable (Y/N)
|
|
DepartmentID
|
Integer
|
Y
|
N
|
|
DepartmentDescription
|
Character (50)
|
N
|
N
|
4: Write a script to add the following records to the EMPLOYEES table:
| 1 |
6/7/1998 |
Sellers |
Andrew |
12345 |
IL |
Administrator |
Cook |
| 2 |
3/15/1987 |
Jones |
Jim |
40677 |
IL |
CEO |
Dupage |
| 3 |
4/5/2000 |
Johnson |
Jennifer |
89036 |
CA |
Manager |
Kings |
| 4 |
11/19/2000 |
Lark |
Ron |
23435 |
NY |
Manager |
Queens |
| 5 |
4/6/2008 |
Buchanan |
Steven |
74536 |
VA |
Administrator |
Richmond |
5: Write a script to add the following records to the REGIONS table:
|
1
|
Eastern
|
|
2
|
Western
|
|
3
|
Northern
|
|
4
|
Southern
|
6: Write a script to add the following records to the DEPARTMENTS table:
|
10
|
CEO
|
|
20
|
Sales
|
|
30
|
Marketing
|
7: Write a script to add the following column to the EMPLOYEES table:
|
Column Name
|
Datatype
|
Primary Key (Y/N)
|
Nullable (Y/N)
|
|
DepartmentID
|
Integer
|
N
|
N
|
8: Write a script to establish a foreign key constraint between EMPLOYEES and DEPARTMENTS using the common field: DepartmentID;
9: Create a database diagram of the final database clearly depicting all tables and relationships.
Submit the SQL, DDL, and DML scripts needed to complete tasks and the database diagram of the final database.