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.

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.

Leave a comment

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