***USE SQL Server Express 2014***
Before beginning this lab, run the following script in the SQL Server query window (Included in “Lab 5 Insert Update Delete (DML) – Instructions” file).
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.
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.
- Write and execute a query that will change the name of United Kingdom to Great Britain in the Countries table.
- Write and execute a query that will increase every employee’s salary by 10%.
- Write and execute a query that will change the contract type of every “Time and Materials” contract to “Fixed Price”.
- 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 Davis’s 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:
- Write and execute a query that will reassign all employees in the Cambridge office to the Denver office.
- 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.
- Write and execute a query that will remove the contract type “Time and Materials” from the ContractTypes table.
Select * from employees where firstname = ‘Paul’ and lastname = ‘Davis’
Take a screenshot of the results of this query and paste it below, as well.
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.
0 comments