SQL101-Homework Assignment #4 Answer Key

Here are my solutions updated to work with AdventureWorks on 2008R2.

1. Write a DELETE query that would remove a record from person.contact by contactID, pick a contactID that does not exist in person.contact.

DELETE FROM Person.Contact
WHERE
     ContactID = -1

2. Write a DELETE query that would remove a record from person.contact by first and last name.

DELETE FROM Person.Contact
WHERE
     FirstName = 'JarJar'
     AND LastName = 'Binks'
--Please, oh please delete him!

3. Write a DELETE query that would remove a record from example.person where they also exist in person.contact.

DELETE FROM ep
FROM Example.Person ep
INNER JOIN Person.Contact pc
     ON ep.FirstName = pc.FirstName
     AND ep.LastName = pc.LastName
WHERE
     pc.FirstName = 'JarJar'
     AND pc.LastName = 'Binks'
--We have to make sure he's gone!

If you have any problems with these solutions, please let me know.  I want to make sure we’re on the same page.