Here is my work for Exercise 8 Deleting Using Other Tables.
In this exercise, we learn how to delete rows by creating a temporary table through SELECT and using WHERE IN or WHERE NOT IN the temporary table.
- Practice writing SELECT commands and then put them in a DELETE WHERE IN to remove those records found. Try deleting any dead pets owned by you.
- Do the inverse and delete people who have dead pets.
I added a Tabby cat and a new person Charlie Brown. Charlie Brown owns Mr. Whiskers the tabby cat who is unfortunately dead. 😦 I then delete any people who own dead pets, only Charlie Brown fits the bill and is deleted. Finally, I delete the relationship from the person_pet table.
- Do you really need to delete dead pets? Why not just remove their relationship in person_pet and mark them dead? Write a query that removes dead pets from person_pet.
Since I didn’t have any dead pets left, I first added Mr. Jingles as my dead pet. (I added him as a pet, and then added our relationship in the person_pet table.) Initially the person_pet table shows that I own three pets, Mr. Jingles, Drogon, and Jaws. Nobody owns Mr. Whiskers.
I then deleted any dead pets from person_pet. The person_pet relationship between me and Mr. Jingles is removed from the table.
This exercise is a good brain workout, like solving mini puzzles!