SQL101-Homework Assignment #2 Answer Key

Here are my answers to the questions based on AdventureWorks. I recently updated the answers to work with the 2008R2 version of Adventureworks. If your answers are different than mine, please get in touch with me. There are always multiple ways of solving a problem, I need to make sure we’re on the same page, and you have the fundamentals down.

1. What query would show the First name, last name, and title for all employees? (HINT: all employees are contacts.)

SELECT
	FirstName, LastName, e.Title
FROM Person.Contact c
INNER JOIN HumanResources.Employee e
	on c.ContactID = e.ContactID

2. What query would show the First and last name for NON Employees? (HINT: you’re looking for records that don’t exist in both tables.)

SELECT
	FirstName, LastName
FROM Person.Contact c
LEFT JOIN HumanResources.Employee e
	on c.ContactID = e.ContactID
WHERE
	e.EmployeeID IS NULL

3. Based on the last query, are there any employees that are not in our contacts table? (HINT: You can use a LEFT or a RIGHT join to find this. Bonus points if you show me both.)

SELECT
	FirstName, LastName
FROM HumanResources.Employee e
LEFT JOIN  Person.Contact c
	on e.ContactID = c.ContactID
WHERE
	c.ContactID IS NULL

--or you could do a right join

SELECT
	FirstName, LastName
FROM Person.Contact c
RIGHT JOIN HumanResources.Employee e
	on c.ContactID = e.ContactID
WHERE
	c.ContactID IS NULL

There you go… The solutions. Let me know if you have a problem with any of them.

Leave a Reply Cancel reply