in SQL, I have a sample database and want to update an employee with an employee
ID: 3838497 • Letter: I
Question
in SQL,
I have a sample database and want to update an employee with an employee_id of 201 and change it to 208, so far I have:
UPDATE EMPLOYEE
SET employee_id = 208
WHERE email = ('MHARTSTE' AND phone_number = '515.123.5555');
UPDATE DEPARTMENT
SET employee_id = 208
WHERE email = ('MHARTSTE' AND phone_number = '515.123.555');
UPDATE JOBHISTORY
SET employee_id = 208
WHERE email = ('MHARTSTE' AND phone_number = '515.123.555');
DELETE FROM EMPLOYEE
WHERE employee_id = 201;
The original question asks:
A company decides to replace employee_id 201 with a new employee_id 208. An email of the employee with employee_id equal to 201 is MHARTSTE and mobile phone is 515.123.5555. Update related data in the database.
also
If say employee with employee_id 102 left how would i delete him from the database?
Explanation / Answer
No. 1:
UPDATE EMPLOYEE SET employee_id=208 WHERE employee_id=201;
No. 2:
UPDATE DEPARTMENT SET employee_id=208 WHERE employee_id=201;
No. 3:
UPDATE JOBHISTORY SET employee_id=208 WHERE employee_id=201;
The above ways are when the previous employee id is known and we want to update it.
Now if suppose we have only the email and phone then we can update it in the following manner.
First we need to find out the employee id using mail and phone number:
SELECT employee_id FROM EMPLOYEE WHERE email = 'MHARTSTE' AND phone_number = '515.123.5555';
Now as we have the id we can use the previous SQL statements to update tables.
UPDATE EMPLOYEE SET employee_id=208 WHERE employee_id=<employee_id we got as result>;
UPDATE DEPARTMENT SET employee_id=208 WHERE employee_id=<employee_id we got as result>;
UPDATE JOBHISTORY SET employee_id=208 WHERE employee_id=<employee_id we got as result>;
Say if employee with employee_id 102 left then you need to run the following statements:
DELETE FROM JOBHISTORY WHERE employee_id=102;
DELETE FROM DEPARTMENT WHERE employee_id=102;
DELETE FROM EMPLOYEE WHERE employee_id=102;
Note: We need to maintain the sequence because in database you may have referenced primary table records using foreign key. And on doing so, if have not used "ON DELETE CASCADE" while refering then there will be errors if we do not follow the sequence of operations.
"ON DELETE CASCADE": If we use this keyword while refering to primary table, then when we modify the records in primary table say delete one record then the effect will also be on derived table i.e., the record with the same key will be deleted from derived table also.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.