Before beginning this lab, run the following script in the SQL Server query window. If you have already created a database called hourglass, be sure to drop the database before running these queries.

create database hourglass

go

use hourglass

go

CREATE TABLE Regions

(RegionID int not null,

RegionName varchar(40),

CONSTRAINT PK_Regions PRIMARY KEY (RegionID));

CREATE TABLE Countries

(CountryID int not null,

CountryName varchar(50),

RegionID int not null,

CONSTRAINT PK_Countries PRIMARY KEY (CountryID),

CONSTRAINT FK_CountriesRegions FOREIGN KEY (RegionID) References Regions);

CREATE TABLE Offices

(OfficeID int,

OfficeName varchar(50),

CountryID int not null,

CONSTRAINT pk_Offices PRIMARY KEY (OfficeID),

CONSTRAINT fk_Offices_Countries FOREIGN KEY (CountryID) REFERENCES Countries)

CREATE TABLE Employees

(EmpID int,

FirstName varchar(30),

LastName varchar(50),

Email varchar(50),

Salary decimal(10, 2),

OfficeID int not null,

SupervisorID int,

CONSTRAINT pk_Employees PRIMARY KEY (EmpID),

CONSTRAINT fk_Employees_Offices FOREIGN KEY (OfficeID) REFERENCES Offices,

CONSTRAINT fk_Employees_Employees FOREIGN KEY (SupervisorID) REFERENCES Employees)

CREATE TABLE Clients

(ClientID int,

LegalName varchar(100),

ClientName varchar(50),

CountryID int not null,

CONSTRAINT pk_Clients PRIMARY KEY (ClientID),

CONSTRAINT fk_Clients_Countries FOREIGN KEY (CountryID) REFERENCES Countries)

CREATE TABLE ContractTypes

(ContractTypeID int not null,

ContractTypeName varchar(50),

CONSTRAINT pk_ContractTypes PRIMARY KEY (ContractTypeID))

CREATE TABLE Contracts

(ContractID int not null,

ContractDesc varchar(100),

ClientID int not null,

ContractTypeID int not null,

CONSTRAINT PK_Contracts PRIMARY KEY (ContractID),

CONSTRAINT FK_ContractsClients FOREIGN KEY (ClientID) REFERENCES Clients,

CONSTRAINT FK_ContractsContractTypes FOREIGN KEY (ContractTypeID) REFERENCES ContractTypes)

CREATE TABLE Projects

(ProjectID int,

ProjectName varchar(100),

ManagerID int not null,

ContractID int not null,

CONSTRAINT pk_Projects PRIMARY KEY (ProjectID),

CONSTRAINT fk_Projects_Employees FOREIGN KEY (ManagerID) REFERENCES Employees,

CONSTRAINT fk_Projects_Contracts FOREIGN KEY (ContractID) REFERENCES Contracts)

CREATE TABLE EmployeeProjectAssignments

(ProjectID int,

EmpID int,

StartDate smalldatetime,

EndDate smalldatetime,

CONSTRAINT pk_EmployeeProjectAssignments PRIMARY KEY (ProjectID, EmpID),

CONSTRAINT fk_EmployeeProjectAssignments_Projects FOREIGN KEY (ProjectID) REFERENCES Projects,

CONSTRAINT fk_EmployeeProjectAssignments_Employees FOREIGN KEY (EmpID) REFERENCES Employees)

CREATE TABLE WorkHours

(ProjectID int,

EmpID int,

Day int,

Month int,

Year int,

HoursWorked float,

CONSTRAINT pk_WorkHours PRIMARY KEY (ProjectID, EmpID, Day, Month, Year),

CONSTRAINT fk_WorkHours_Projects FOREIGN KEY (ProjectID) REFERENCES Projects,

CONSTRAINT fk_WorkHours_Employees FOREIGN KEY (EmpID) REFERENCES Employees)

Deliverables:

Part 1:

Given the worksheets in the Lab 5 Insert Update Delete (DML) Data.xls file, populate the tables you created above using SQL Insert statements. Note that there are multiple sheets in this workbook. Each sheet contains data for a separate table.

You may find it easier to create formulas in your Excel file to generate insert statements and then paste those queries into a sql script file. Conversely, you may find it easier to pull the data from Excel into Microsoft Access and import it directly into SQL Server using ODBC. To use this latter technique, refer to the How to Import Data from Access to SQL Server document in your lab folder.

Once the data are loaded, execute the following SELECT statements in SQL Server and paste a screenshot of your results below each SELECT query. Make sure your screenshot includes the bottom status bar in the query results window that indicates the number of rows displayed.

Select * from Regions

Select * from Countries

Select * from Offices

Select * from Employees

Select * from ContractTypes

Select * from Clients

Select * from Contracts

Select * from Projects

Select * from EmployeeProjectAssignments

Select * from Workhours

For example:

Part 2:

Write and execute the queries below in SQL Server. Take a screenshot of your query as well as the query results and paste it below each question.

1. Write and execute a query that will change the name of United Kingdom to Great Britain in the Countries table.

2. Write and execute a query that will increase every employees salary by 10%.

3. Write and execute a query that will change the contract type of every Time and Materials contract to Fixed Price.

4. Write and execute queries to delete the employee Paul Davis. [Note that before you remove him from the Employees table, you will need to remove his records from any other tables that refer to his ID.] You may use a separate query for each table that requires removal of Paul Daviss records, but only one query per table should be used. That is, if he has multiple records in the EmployeeProjectAssignments table, only one query should be used to remove all of his records in that table. You may also need to reassign any of the projects that he manages before you can delete him. That is, if Paul Davis manages any projects, reassign those projects to Matthew Smith. Do this in a single query as well. Write each query below that you used to remove Paul Davis from the database and then execute this Select statement:

Select * from employees where firstname = 'Paul' and lastname = 'Davis'

Take a screenshot of the results of this query and paste it below, as well.

5. Write and execute a query that will reassign all employees in the Cambridge office to the Denver office.

6. Write and execute a query that will end the employee project assignment for Mark Jones and the DT Work Order Customization project. Give the row an end date of August 1, 2017.

7. Write and execute a query that will remove the contract type Time and Materials from the ContractTypes table.

8. Write and execute a query that will delete all countries that are not assigned to an office or a client.

You must do this in a single query to receive credit for this question.

Write the delete query below and then execute the following statement in SQL Server:

Select * from Countries.

Take a screenshot of your select query results and paste them below your delete query that you constructed.

SQLQuerylsql-ME Melesambolt (52))* X Select from Workhours 100 % Results Messages ProjectID EmplDStartDate EndDate 2016-12-20 00:00:00 2017-05-15 00:00:00 2016-12-20 00:00:00 2017-05-15 00:00:00 2016-12-20 00:00:00 2017-05-15 00:00:00 2017-05-16 00:00:00 NULL 2017-06-23 00:00:00 2017-07-01 00:00:00 2017-07-02 0000:00 NULL 2016-10-18 00:00:00 NULL 2016-10-18 00:00:00 NULL 2017-02-03 00:00:00 2017-02-28 00:00:00 2017-02-15 00:00:00 2017-03-20 00:00:00 2017-03-01 00:00:00 2017-04-01 00:00:00 2017-05-15 00:00:00 NULL 6 4 4 7 8 10 7 11 8 12 9 Query executed successfully 9 MELESASQLEXPRESS (12.0 RTM) Melesa\mbolt (52) hourglass 00:00:00 27 rows