Modificant les dades: insert, update, delete

Introducció

És la secció 11 que estem seguint del tutorial:

• https://www.sqltutorial.org/
• https://www.sqltutorial.org/sql-insert/

Insert

INSERT INTO dependents( first_name, last_name, relationship, employee_id) VALUES ( 'Dustin', 'Johnson', 'Child', 178 );

# comprovació:
SELECT * FROM dependents WHERE employee_id = 178;

INSERT INTO dependents( first_name, last_name, relationship, employee_id) VALUES 
( 'Cameron', 'Bell', 'Child', 192 ), 
( 'Michelle', 'Bell', 'Child', 192 );

SELECT * FROM dependents WHERE employee_id = 192;

Per tal de fer-ho necessitem la taula dependents_archive que tingui la mateixa estructura que dependents.
(fer-ho)

INSERT INTO dependents_archive SELECT * FROM dependents;

Update

UPDATE employees SET last_name = 'Lopez' WHERE employee_id = 192;

# comprovació:
SELECT employee_id, first_name, last_name FROM employees WHERE employee_id = 192;

UPDATE dependents SET last_name = 'Lopez' WHERE employee_id = 192;

# fem un update amb una subquery

UPDATE dependents SET last_name =
( SELECT last_name FROM employees WHERE employee_id = dependents.employee_id);

Delete

# comprovació:
SELECT count(*) FROM dependents WHERE dependent_id = 16;

DELETE FROM dependents WHERE dependent_id = 16;

# comprovació:
SELECT count(*) FROM dependents WHERE dependent_id = 16;


DELETE FROM dependents WHERE employee_id IN(100 , 101, 102);

DELETE FROM employees WHERE employee_id = 192;
DELETE FROM dependents WHERE employee_id = 192;

foreign key constraint

---

creat per Joan Quintana Compte, desembre 2021