SQL Server Express Edition and AdventureWorksLT2012 are used throughout this course. If you already have them installed from the prerequisite ITCO 221 course, proceed to Task #1 below.
If needed, download and install SQL Server 2012, Express Edition. Refer to Appendix A of the textbook.
If needed, create the AdventureWorksLT2012 database. It can be re-downloaded it here.
Task #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 |
Task #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 |
Task #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 |
Task #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 |
Task #5: Write a script to add the following records to the REGIONS table:
|
1 |
Eastern |
|
2 |
Western |
|
3 |
Northern |
|
4 |
Southern |
Task #6: Write a script to add the following records to the DEPARTMENTS table:
|
10 |
CEO |
|
20 |
Sales |
|
30 |
Marketing |
Task #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 |
Task #8: Write a script to establish a foreign key constraint between EMPLOYEES and DEPARTMENTS using the common field: DepartmentID;
Task #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.
0 comments