Introduction to SQL References

In this exercise, you will have to update and delete data. Make sure to select the affected data upfront to only affect the desired records afterwards.

Copy all of your SQL statements for this exercise to a text editor and save the file as references_1.sql. Make screenshots of the outcomes of all select queries.

1. Create a Database and Tables
   Create the database matching the following ERD. Make sure to create a reference between the tables.
   
2. Adding Data
   

   Add the following data to your database. Why should you start with the departments table?

   departments
   department_id	name
   1	engineering
   2	marketing
   3	sales

   Now enter data to your employees table.

   employees
   employee_id	first_name	last_name	department_id
   1	Amelie		1
   2	Susanna		1
   3	Pat		2
   4	Chris		3

3. Adjust the Data
   
   1. Insert two new departments: domestic sales and international sales.
   2. After having added the new departments, try to remove the existing sales department. What happens? Why?
   3. Now update Chris to be in the new domestic sales department. Afterwards, try to remove the former sales department again. This time it should succeed. Why?
   4. Try to add a new employee having the first name `Elon` to the department with id = `17` which we didn't create. What happens?
   5. Finally, try to drop the entire departments table and explain what happens.
   
   
   
4. On delete cascade
   1. Try to remove the marketing department. Why does this fail? To fix this, we'll replace the existing foreign key by first removing it and then adding a new one that will delete the linked data when a department is deleted.
      That means, all employees in affected departments will also be removed when a department is disbanded. In order to remove a foreign key reference, you have to find out its name. The constraint's name is available in the error message when trying to delete a department with employees associated to it. Alternatively, you can obtain the name of the constraint via

      show create table employees;

      Remove the foreign key according to the documentation for alter table.
   2. The original foreign key referencing the departments tables was of type restrict. After dropping it, add a new foreign key between the tables that will cascade the delete operation to affected employees. This time, give the foreign key constraint the name fk_employees_departments to make it easier to work with it later on.
   3. Once that is complete, delete the marketing department again. Afterwards, use select to show all departments to check if the deletion was successful. Then show all employees to see what happend.
5. On delete set null
   1. Create a new department called R&D.
   2. Change the foreign key reference so that affected employees won't be deleted any more. Instead, when their department is deleted, they should have NULL in their department_id column.
   3. Remove the engineering department.
   4. List all employees. There should be employees that are not associated with a department.
6. Updating Data
   Finally, add the tangling employees (all employees that are currently not associated with a department) to the new 'R&D' department.