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 (Region ID int not null, RegionName, varchar(40), CONSTRAINT PK Regions PRIMARY KEY (Region ID)); CREATE TABLE Countries (CountryID int not null, CountryName varchar(50) Region ID int not null, CONSTRAINT PK.Countries. PRIMARY KEY (CountryID CONSTRAINT EK CountriesRegions. FOREIGN KEY (Region ID References Regions): CREATE TABLE Offices CofficeID int OfficeName yanchar.se CountryID int not null CONSTRAINT pk offices PRIMARY KEY Office CONSTRAINT Kloffices Countries FOREIGN KEY CountryIR REFERENCES Countries) Trous CREATE TABLE Employees (ERIR int, FirstNane varchar(30), LastName varchar(50), Email varchar(50), Salary decimal(10, 2), Offiser int not null, Supervisncin int, CONSTRAINT Rk Employees. PRIMARY KEY (EMPID), CONSTRAINT fk. Employees..offices. FOREIGN KEY (fficeIR) REFERENCES Offices, CONSTRAINT fkuEmployees. Employees. FOREIGN KEY (SupervisorID) REFERENCES Employees) CREATE TABLE Clients (clientID int, LegalName, varchar(100), ClientName varchar(5e), Count.IR int not null, CONSTRAINT pkclients. PRIMARY KEY (ClientID), CONSTRAINT fk Clients Countries. FOREIGN KEY (CountryIDS REFERENCES Countries) CREATE TABLE Contract Types (Contract TypeID int not null, Contract TypeName varchar(50), CONSTRAINT pk Contract Types. PRIMARY KEY (Contract TypeID) CREATE TABLE Contracts (ContractID int not null, ContractDesc varchar(100), ClientID int not null, Contract TypeID int not null, CONSTRAINT PK Contracts PRIMARY KEY (ContractID), CONSTRAINT FK ContractsClients. FOREIGN KEY (ClientID) REFERENCES Clients, CONSTRAINT FK ContrastsContract Types. FOREIGN KEY (Contract TypeID REFERENCES Contract Types) CREATE TABLE Projects (ProtectID int Proiect Name varchar (100) ManagerID int not null Contractin int not null CONSTRAINT pk Projects. PRIMARY KEY K ProjectID CONSTRAINT fk Projects. Employees FOREIGN KEY (ManagerID REFERENCES Employees, CONSTRAINTfk Projects Contracts FOREIGN KEY ContractID) REFERENCES Contracts) CREATE TABLE EmployeeProjectAssignments, (ProjectID int, EmpID int, StartDate smalldatetime, EndDate smalldatetime. CONSTRAINT Rk 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 Work hours (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 ProiectID) REFERENCES Projects, CONSTRAINT fk WorkHours Employees FOREIGN KEY EmpID) REFERENCES Employees) 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
