SQL101-Homework Assignment #3

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.

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.

IF you do not have Example.Person in your database, please run the following Query:

IF NOT EXISTS (
	SELECT [SCHEMA_NAME]
	FROM INFORMATION_SCHEMA.SCHEMATA
	WHERE
		[SCHEMA_NAME] = 'Example'
)
BEGIN
	CREATE SCHEMA Example
END

IF NOT EXISTS (
	SELECT TABLE_NAME
	FROM INFORMATION_SCHEMA.TABLES
	WHERE
		TABLE_SCHEMA = 'Exmple'
		AND TABLE_NAME = 'Person'
)
BEGIN
	CREATE TABLE Example.Person (
		  Title NVARCHAR(8)
		, FirstName NVARCHAR(50)
		, MiddleName NVARCHAR(50)
		, LastName NVARCHAR(50)
		, Suffix NVARCHAR(10)
	)
END

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.

UPDATE: The answers can be found here.