Deleting More Precisely
You can restrict deletions further by combining search conditions. For example, enter the following statement to delete records of everyone in the sales department hired before January 1, 1992:
DELETE FROM Employee
WHERE job_code = 'Sales'
AND hire_date < '01-Jan-1992'
You can try entering this statement, but you’ll get an error because there’s a foreign key column in the Employee_project table that references the Employee table. If you were to delete these rows, some values in the Employee_project table would no longer have matching values in the Employee table, violating the foreign key constraint that says any employee who has a project must also have an entry in the Employee table.
In addition, you can use subqueries to delete data, just as you use them to update. The following statement would delete all rows from the Employee table where the employees were in the same department as Katherine Young.
DELETE FROM Employee
WHERE dept_no = (SELECT dept_no FROM Employee
WHERE full_name = 'Young, Katherine')
Again, you cannot actually execute this statement because it would violate foreign key constraints on other tables.