SQL101-Homework Assignment #3 Answer Key

Here are the answers to the previous practice problems.

1. Write the query that would put the full name (as much of it as you know)  of your favorite Star Wars character into the Person.Contact table.  Use the title field to identify them as  ‘SWChar’. You can use the INSERT that uses VALUES or SELECT to do this, again, extra points if you show me both variations.  You will find that several columns are required to be not null, pass any valid data for those fields.

 INSERT INTO Person.Contact
 (Title, FirstName, LastName, PasswordHash, PasswordSalt)
 VALUES
 ('SWChar','Lando','Calrissian','','')

 INSERT INTO Person.Contact
 (Title, FirstName, LastName, PasswordHash, PasswordSalt)
 SELECT 'SWChar','Han','Solo','',''

2. Write the query that would take one row from person.contact and insert it into example.person, filling in as much of example.person as possible.

INSERT INTO Example.Person
(Title, FirstName, MiddleName, LastName, Suffix)
SELECT TOP 1
	  Title
	, FirstName
	, MiddleName
	, LastName
	, Suffix
FROM Person.Contact
WHERE
	LastName = 'Solo'

3. Do the last query again, except this time, I want you to make sure the title is set to ‘test’ You’re mixing a INSERT with SELECT with a manually set value.

INSERT INTO Example.Person
(Title, FirstName, MiddleName, LastName, Suffix)
SELECT TOP 1
	  'test' AS title
	, FirstName
	, MiddleName
	, LastName
	, Suffix
FROM Person.Contact
WHERE
	LastName = 'Solo'

Please let me know if you have any questions about these questions.