Assignment: SQL Statements

The goal is develop a database for the Wedgewood Pacific Corporation using MS SQL Server.

1. In MS SQL Server, create a new database called Wpc.

2. Create the Department and Employee tables shown below along with the Project and Assignment tables shown on the next page, and set the relationships between the tables explained below:

A. To set a column to be a Primary Key, right-click on the column name (while in the design mode) and select Set Primary Key.

B. To set a column as a Foreign Key, right-click on the column name (while in the design mode) and select Relationship. Click the Add button and then click on “Tables and Columns Specification” and then click on the button next to it (with three dots) and complete the relationship by selecting the Primary Key table and under that pick its column name, and match that with the Foreign Key column name in the current table.

C. The Department column in the Employee and Project tables are both a Foreign Key of the Department Name column from the Department table. ProjectID in the Assignment table is a Foreign Key of ProjectID in the Project table, and EmployeeNumber in the Assignment table is a Foreign Key of EmployeeNumber in the Employee table. Notice that ProjectID and EmployeeNumber are also Primary Keys in the Assignment table.

D. The Employee Number column in the Employee table is an Identity column, which means its value will be automatically assigned (AutoNumber). To set it as an Identity column, click on the column name, then go into the properties window below it and scroll to “Identity Specification” and open that property line and click on “(Is Identity)” and then click on the down arrow on the right side and select “Yes”.

3. Now create the Project and Assignment tables as shown here. Note the Foreign Key relationships in these tables and set them up as explained above.

Department Data
 
Employee Data

Project Data
 
Assignment Data

A. What projects are in the Project table? Show all information for each project.

B. What are the ProjectID, Name, StartDate, and EndDate values of projects in the Project table?

C. What projects in the Project table started before August 1, 2008? Show all the information for each project.

D. What projects in the Project table have not been completed? Show all the information for each project.

E. Who are the employees assigned to each project? Show ProjectID, Employee-Number, LastName, FirstName, and Phone.

F. Who are the employees assigned to each project? Show the Project ID, Name, and Department. Show Employee Number, Last Name, First Name, and Phone.

G. Who are the employees assigned to each project? Show ProjectID, Name, Department, and Department Phone.  Show EmployeeNumber,  LastName,  FirstName,  and  Employee  Phone.

Sort by ProjectID in ascending order.

H. Who are the employees assigned to projects run by the marketing department? Show ProjectID, Name, Department, and Department Phone. Show EmployeeNumber, LastName, FirstName, and Employee Phone. Sort by ProjectID in ascending order.

I. How many projects are being run by the marketing department? Be sure to assign an appropriate column name to the computed results.

J. What is the total MaxHours of projects being run by the marketing department? Be sure to assign an appropriate column name to the computed results.

K. What is the average MaxHours of projects being run by the marketing department? Be sure to assign an appropriate column name to the computed results.

L. How many projects are being run by each department? Be sure to display each Department Name and to assign an appropriate column name to the computed results.