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.

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

1 comment

Leave a comment

Your email address will not be published. Required fields are marked *