DATABASE MANAGEMENT

1.Create a table as in the video. Use a new database you create. Show how the begin transaction code locks the record so a new connection to the same record is blocked. Show how to change Isolation level from read committed to read uncommitted. Show the results of the select query with Isolation level committed and isolation level uncommitted. Show effect of rolling back the transaction. Show the effect of committing the transaction.

https://youtu.be/shkt9Z5Gz-U for 1 question.

2.create a trigger in your ToDo database that adds a new line to an audit table when you add a new task.

3. Using the ToDo database that you created, create a stored procedure with a try/catch block to get a detailed list of all completed task for a particular period with period dates entered as parameters. 

Try/Catch video.

Stored Procedure video. 

4. Create and populate the ToDo database with primary and foreign keys and cascading constraints for updates and deletes.  Include nulls, defaults (like a timestamp for date the task is created, and auto-incremented primary keys. Include a constraint that prohibits you from entering a completion date before the start date. Submit a .sql file with comments. Use indents for clarity.

5.

i, What are Forward Only, Static, Keyset, and Dynamic cursors. When would I use e

ach?

ii. Create a copy of the products table in Northwind.

iii. Using the copy of the products table, create a cursor that adds five to the reorder level when there is a 'c' in the product name and subtracts 5 if there is no 'c' in the product name. Reorder level cannot go below 0, however.

6.

1. What is a Common Table Expression?

2. Using the Adventureworks Database, find a list of employees are paid greater than the average rate

submit a .sql file named Lab15_yourname

CTE video 1

CTE Video 2

7.