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.

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 *