Skip to content

SQL101-Homework Assignment #5 Answer Key

2006 March 24
tags:
by Shannon Lowder

These answers have been updated to work with Adventureworks on 2008R2.

1. Write an UPDATE query that would set your middle name, for only your record by personID.
(if there is no record for you, add it in first, then show me the update to set your middle name.)

UPDATE Person.Contact SET
     MiddleName = 'Dwight'
WHERE
     ContactID = 123

Of course the id would need to be your contactID, you can get that by doing a select on the Person.Contact table.

2. Write an UPDATE query that would swap your first and middle name, again, for your record only.

UPDATE Person.Contact SET
       FirstName = MiddleName
     , MiddleName = FirstName
WHERE
     ContactID = 123

3. Write an UPDATE query that would update the example.person record, setting middle names you find in the person.contact table. Match on first and last names between the two tables.  Limit the update to only work for people with the last name sanchez.

UPDATE ep SET
     ep.MiddleName = pc.MiddleName
FROM Example.Person ep
INNER JOIN Person.Contact pc
     ON ep.FirstName = pc.FirstName
     AND ep.LastName = pc.LastName
WHERE
     ep.LastName = 'Sanchez'

 

One Response leave one →

Trackbacks and Pingbacks

  1. SQL101-Homework Assignment #5 | Shannon Lowder

Leave a Reply

Note: You can use basic XHTML in your comments. Your email address will never be published.

Subscribe to this comment feed via RSS