Using SQL, create an empty table, that is the same as the employees table, and name it
newEmployees.
1.Execute the following commands.
SET AUTCOMMIT OFF;
SET TRANSACTION READ WRITE;
2. Make an INSERT statement to populate the newEmployees table with the rows of the sample data.
Insert the NULL value for the reportsTo column. (Write a single INSERT statement to insert all the rows)
3. Make a query that shows all the inserted rows from the newEmployees table. How many rows are
selected?
4. Execute the rollback command. Display all rows and columns from the newEmployees table. How many
rows are selected?
5. Repeat Task 4. Make the insertion permanent to the table newEmployees. Display all rows and columns
from the newEmployee table. How many rows are selected?
6. Make an update statement to update the value of column jobTitle to 'unknown' for all the employees
in the newEmployees table.
7. Make your changes permanent.
8. Execute the rollback command.
a. Display all employees from the newEmployees table whose job title is 'unknown'. How many
rows are still updated?
b. Was the rollback command effective?
c. What was the difference between the result of the rollback execution from Task 6 and the result
of the rollback execution of this task?
9. Begin a new transaction and then create a statement to delete to employees from the newEmployees
table
10. make a VIEW, called vwNewEmps, that queries all the records in the newEmployees table sorted by
last name and then by first name.
DBS211 - Introduction to Database Systems Summer 2020
11. Perform a rollback to undo the deletion of the employees
a. How many employees are now in the newEmployees table?
b. Was the rollback effective and why?
12. Begin a new transaction and rerun the data insertion from Task 4 (copy the code down to Task 14 and
run it)
13. Set a Savepoint, called insertion, after inserting the data
14. Rerun the update statement from Task 8 and run a query to view the data (copy the code down and run
it again)
15. Rollback the transaction to the Savepoint created in task 15 above and run a query to view the data.
What does the data look like (i.e. describe what happened?
16. Use the basic for of the rollback statement and again view the data. Describe what the results look like
and what happened.
Part B - Permissions
17. Make a statement that denies all access to the newemployees table for all public users
18. Make a statement that allows a classmate (use their database login) read only access to the
newemployees table.
19. Make a statement that allows the same classmate to modify (insert, update and delete) the data of the
newemployees table.
20. make a statement the denies all access to the newemployees table for the same classmate.
Part C - Clean up
21. Make statements to permanently remove the view and table created for this lab

Answer rating: 100% (QA)

Below is a sequence of SQL statements to accomplish the tasks youve described Please note that the actual implementation may vary based on the databas... View the full answer