Skip to content

SQL101-Homework Assignment #3

2006 February 17
tags:
by Shannon Lowder

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.

No comments yet

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