IT 526_Unit 1 Create Table Resource Data document 1
Abstract
This document provides examples and content to aid in working with unit one’s assignment
requirements.
Part 1. Database and Table Creation
ERD:
IT 526_Unit 1 Create Table Resource Data document 2
Figure 1: Entity-Relationship Diagram (ERD) Example
IT 526_Unit 1 Create Table Resource Data document 3
Database Creation
There are two ways to create a database.
One way is to open a query window, type and execute a CREATE DATABASE command:
CREATE DATABASE PetAdoption;
GO
Because you have created the database using Transact-SQL (T-SQL) code, your database will not show
under the Databases folder in the Object Explorer pane until you refresh the Databases folder (or
collapse and then expand that folder, or expand that folder after executing your CREATE DATABASE
statement.
The other way is to create the database using the SQL Server Management Studio (SSMS) GUI
Right-click on the Databases folder in the Object Explorer pane; select New Database...
Figure 2: Create New Database in SQL Server Management Studio (SSMS)
IT 526_Unit 1 Create Table Resource Data document 4
Name the database.
Click OK to create the database.
Figure 3: Name the new database in SQL Server Management Studio (SSMS)
Database Table Creation
To use T-SQL to create your tables,
select your database, then click the New Query button to open a query window.
Note: When the query window opens, make sure your database name is in the white drop-down box to
the left of the Execute button.
Figure 4: Selecting Database in SQL Server Management Studio (SSMS)
IT 526_Unit 1 Create Table Resource Data document 5
Type each CREATE TABLE statement into the query window. It is recommended that you highlight and
execute each CREATE TABLE statement one at a time.
/* Part 2: Create Database Tables */
USE PetAdoption
GO
[The above code will determine the database that the query window is connected to. If your query
window is already connected to your database, you need not execute the above code.]
The GO keyword is a batch separator that is used to end a batch. A batch is code that is executed as one
unit. A CREATE TABLE batch must finish before the table can be used in subsequent code statements.
-- Create Table FosterHome
CREATE TABLE FosterHome (
FosterHomeID INT not null
,ContactFirstName VARCHAR(35) not null
,ContactLastName VARCHAR(50) not null
,Address VARCHAR(50) not null
,City VARCHAR(50) not null
,State CHAR(2) not null
,Phone VARCHAR(20) not null
,PetType VARCHAR(20) not null
,Capacity INT not null
,constraint pk_FosterHome primary key (FosterHomeID)
);
GO
-- Create Table Pet
CREATE TABLE Pet (
PetID VARCHAR(20) not null
,Species VARCHAR(30) not null
,Breed VARCHAR(30) not null
,Name VARCHAR(30) not null
,DateOfBirth DATE not null
,constraint pk_Pet primary key (PetID)
);
GO
-- Create Table FosterPlacement.
-- This shows creation of FK constraints with the table creation.
CREATE TABLE FosterPlacement (
PetID VARCHAR(20) not null
,BeginDate DATE not null
,FosterHomeID INT not null
,EndDate DATE null
,constraint pk_FosterPlacement primary key (PetID, BeginDate)
,constraint fk_FosterHome foreign key (FosterHomeID)
references FosterHome (FosterHomeID)
,constraint fk_PetFoster foreign key (PetID) references Pet (PetID)
);
GO
IT 526_Unit 1 Create Table Resource Data document 6
-- Create Table ClientFamily
CREATE TABLE ClientFamily (
ClientFamilyID INT not null
,ClientFamilyFirstName VARCHAR(35) not null
,ClientFamilyLastName VARCHAR(50) not null
,Address VARCHAR(50) not null
,City VARCHAR(50) not null
,State CHAR(2) not null
,Phone VARCHAR(20) not null
,constraint pk_ClientFamily primary key (ClientFamilyID)
);
GO
-- Create Table Adoption
-- Foreign key constraints are made in table creation
CREATE TABLE Adoption (
PetID VARCHAR(20) not null
,AdoptionDate DATE not null
,ClientFamilyID INT not null
,constraint pk_Adoption primary key (PetID, AdoptionDate)
,constraint fk_Pet2 foreign key (PetID) references Pet (PetID)
,constraint fk_ClientFamily2 foreign key (ClientFamilyID)
references ClientFamily (ClientFamilyID)
);
GO
-- Create Table Surrender
-- These statements were created to show how to use ALTER TABLE
-- to create the foreign key constraints after the table is created.
CREATE TABLE Surrender (
PetID VARCHAR (20) not null
,SurrenderDate DATE not null
,ClientFamilyID INT not null
,constraint pk_Surrender primary key (PetID, SurrenderDate)
);
GO
ALTER TABLE Surrender
ADD CONSTRAINT fk_Pet1 FOREIGN KEY (PetID) REFERENCES Pet (PetID)
, CONSTRAINT fk_ClientFamily1 FOREIGN KEY (ClientFamilyID)
REFERENCES ClientFamily (ClientFamilyID);
GO
IT 526_Unit 1 Create Table Resource Data document 7
This is what your SSMS window will look like when a CREATE TABLE statement executes without error:
Figure 5: Create Table SQL Code in SQL Server Management Studio (SSMS)
[The border between the upper query window and the results pane was pulled down to give the query
part of the pane more "real estate."
You can toggle the Results pane to disappear or reappear by typing CTRL+R.
Oh, gee! I just got points deducted by my instructor for including an unnecessary screen shot!]
IT 526_Unit 1 Create Table Resource Data document 8
Part 3A. Insert Statements for Created Tables
[Note: Even though values are being inserted into all columns, it is still a good idea to name the
columns in the INSERT statement.]
/* Insert Statements */
-- Each of these is inserted in the same order as created to ensure
integrity
/* Notice that
INTEGERS and other numeric data (decimal or money) is NOT quoted;
CHAR, VARCHAR, DATE, and TIME data are enclosed in single quotes.
*/
INSERT FosterHome (FosterHomeID, ContactFirstName, ContactLastName,
Address, City, State, Phone, PetType, Capacity)
VALUES
(1001, 'Laura', 'Martin', '72 Elm St', 'Saco', 'ME', '207-555-9999',
'Cats', 8),
(1002, 'Julie', 'Prince', '43 Oak Rd', 'Wells', 'ME', '207-555-8888',
'Cats', 12),
(1003, 'Steve', 'Smith', '22 Maple Dr', 'York', 'ME', '207-555-7654',
'Dogs', 2),
(1004, 'Isabel', 'Harper', '64 Rhea Ct', 'Saco', 'ME', '207-555-4567',
'Cats', 14),
(1005, 'Pat', 'Shawn', '87 Pine Ave', 'Wells', 'ME', '207-555-9876',
'Cats', 6);
GO
INSERT Pet (PetID, Species, Breed, Name, DateOfBirth)
VALUES
('A1234B5678C9012', 'Feline', 'Maine Coon', 'Smokey', '2009-06-01'),
('F3456H5678J9382', 'Feline', 'Siamese', 'Yang', '2001-03-07'),
('J3982G2498N3918', 'Canine', 'Golden Retriever', 'Buddy', '2006-04-22'),
('B5678S9876V2244', 'Feline', 'Domestic Shorthair', 'Big Mike', '2002-10-05'),
('M7654F2345H9288', 'Feline', 'Persian', 'Snowflake', '1998-05-18'),
('T7654B5678C9012', 'Canine', 'Labrador Retriever', 'Togo', '2010-06-14'),
('Q2345H5678J9382', 'Canine', 'Lab - Pit Bull mix', 'Henry', '2009-03-17'),
('W8394G2498N3918', 'Feline', 'Siamese', 'Anubis', '2006-09-07'),
('A5968S9876V2244', 'Feline', 'Tabby', 'Moskato', '2006-10-25'),
('F9876F2345H9288', 'Canine', 'Terrier', 'Loki', '2008-02-09');
GO
INSERT FosterPlacement (PetID, FosterHomeID, BeginDate, EndDate)
VALUES
('A1234B5678C9012', 1001, '2011-03-03', '2011-04-02' ),
('F3456H5678J9382', 1002, '2011-03-09', NULL ),
('J3982G2498N3918', 1003, '2011-02-21', '2011-04-24' ),
('B5678S9876V2244', 1004, '2011-04-22', NULL ),
('M7654F2345H9288', 1005, '2011-02-18', '2011-05-08' );
GO
IT 526_Unit 1 Create Table Resource Data document 9
INSERT ClientFamily (ClientFamilyID, ClientFamilyFirstName,
ClientFamilyLastName, Address, City, State, Phone)
VALUES
(1, 'John', 'Jones', '22 Main St', 'Berwick', 'ME', '207-555-6464'),
(2, 'Marcia', 'Smith', '89 Jasper Ln', 'Kennebunk', 'ME', '207-555-8989'),
(3, 'Linda', 'Martin', '745 Jay Rd', 'Dover', 'NH', '603-555-8888'),
(4, 'Bill', 'Lane', '45 Center Dr', 'Eliot', 'ME', '207-555-2435'),
(5, 'Debra', 'Haskell', '381 Ash St', 'Somersworth', 'NH', '603-555-9999'),
(6, 'Susan', 'Ponti', '73 Oak Rd', 'Wells', 'ME', '207-555-0342'),
(7, 'Kate', 'Ender', '84 Grace Ln', 'York', 'ME', '207-555-6464'),
(8, 'Michael', 'Chung', '395 Tolan Rd', 'Portsmouth', 'NH', '603-555-7342'),
(9, 'Peter', 'Goldberg', '876 Bard St', 'Rochester', 'NH', '603-999-8564'),
(10, 'Olivia', 'Shastov', '92 Avon Ave', 'Eliot', 'ME', '207-555-0258');
GO
INSERT dbo.Adoption (PetID, AdoptionDate, ClientFamilyID)
VALUES
('T7654B5678C9012', '2011-04-02', 1),
('Q2345H5678J9382', '2011-04-09', 2),
('W8394G2498N3918', '2011-04-24', 3),
('A5968S9876V2244', '2011-05-02', 4),
('F9876F2345H9288', '2011-05-08', 5);
GO
INSERT dbo.Surrender (PetID, SurrenderDate, ClientFamilyID)
VALUES
('A1234B5678C9012', '2011-03-03', 6),
('F3456H5678J9382', '2011-03-09', 7),
('J3982G2498N3918', '2011-02-21', 8),
('B5678S9876V2244', '2011-04-22', 9),
('M7654F2345H9288', '2011-02-18', 10);
GO
IT 526_Unit 1 Create Table Resource Data document 10
Part 3B. SELECT from Tables to Show Content
/* Query each table and provide output. */
USE PetAdoption;
GO
SELECT * FROM FosterHome;
FosterHomeID ContactFirstName ContactLastName Address City
State Phone PetType Capacity
1001 Laura Martin 72 Elm St Saco
ME 207-555-9999 Cats 8
1002 Julie Prince 43 Oak Rd Wells
ME 207-555-8888 Cats 12
1003 Steve Smith 22 Maple Dr York
ME 207-555-7654 Dogs 2
1004 Isabel Harper 64 Rhea Ct Saco
ME 207-555-4567 Cats 14
1005 Pat Shawn 87 Pine Ave Wells
ME 207-555-9876 Cats 6
[The above used the click-upper-left-corner-to-select-all-then-right-click-select-"Copy with Headers"
method. Then TABs had to be used to align the results. The column title row was underlined. The
instructor prefers single line spacing.]
SELECT * FROM Pet;
PetID Species Breed Name DateOfBirth
A1234B5678C9012 Feline Maine Coon Smokey 2009-06-01
A5968S9876V2244 Feline Tabby Moskato 2006-10-25
B5678S9876V2244 Feline Domestic Shorthair Big Mike 2002-10-05
F3456H5678J9382 Feline Siamese Yang 2001-03-07
F9876F2345H9288 Canine Terrier Loki 2008-02-09
J3982G2498N3918 Canine Golden Retriever Buddy 2006-04-22
M7654F2345H9288 Feline Persian Snowflake 1998-05-18
Q2345H5678J9382 Canine Lab - Pit Bull mix Henry 2009-03-17
T7654B5678C9012 Canine Labrador Retriever Togo 2010-06-14
W8394G2498N3918 Feline Siamese Anubis 2006-09-07
[The above used the click-upper-left-corner-to-select-all-then-right-click-select-"Copy with Headers"
method. Then the results were selected in the Word document, and on the ribbon (Office 2007) the
following was performed: Insert > Table > Convert Text to Table > (Accept row and column count). The
first row of column titles were made bold. Column widths were adjusted to avoid wasted length.]
IT 526_Unit 1 Create Table Resource Data document 11
SELECT * FROM FosterPlacement;
PetID BeginDate FosterHomeID EndDate
A1234B5678C9012 2011-03-03 1001 2011-04-02
B5678S9876V2244 2011-04-22 1004 NULL
F3456H5678J9382 2011-03-09 1002 NULL
J3982G2498N3918 2011-02-21 1003 2011-04-24
M7654F2345H9288 2011-02-18 1005 2011-05-08
SELECT * FROM ClientFamily;
Client
ClientFamil ClientFamily
Family Address City State Phone
yFirstName LastName
ID
207-
1 John Jones 22 Main St Berwick ME 555-
6464
207-
2 Marcia Smith 89 Jasper Ln Kennebunk ME 555-
8989
603-
3 Linda Martin 745 Jay Rd Dover NH 555-
8888
207-
4 Bill Lane 45 Center Dr Eliot ME 555-
2435
603-
5 Debra Haskell 381 Ash St Somersworth NH 555-
9999
207-
6 Susan Ponti 73 Oak Rd Wells ME 555-
0342
207-
7 Kate Ender 84 Grace Ln York ME 555-
6464
603-
8 Michael Chung 395 Tolan Rd Portsmouth NH 555-
7342
603-
9 Peter Goldberg 876 Bard St Rochester NH 999-
8564
207-
10 Olivia Shastov 92 Avon Ave Eliot ME 555-
0258
IT 526_Unit 1 Create Table Resource Data document 12
SELECT * FROM Adoption;
PetID AdoptionDate ClientFamilyID
A5968S9876V2244 2011-05-02 4
F9876F2345H9288 2011-05-08 5
Q2345H5678J9382 2011-04-09 2
T7654B5678C9012 2011-04-02 1
W8394G2498N3918 2011-04-24 3
[Column title row had shading added.]
SELECT * FROM Surrender;
Figure 6: SQL Select Statement in SQL Server Management Studio (SSMS)
[Screen shot method. If this method is used, ALL columns and rows must be shown in the screen shot.
If you need more width for the results, you can "dock" the Object Explorer pane by clicking on its push
pin in its upper right-hand corner in the grey bar containing the title "Object Explorer".]
IT 526_Unit 1 Create Table Resource Data document 13
1. Query one table and use WHERE to filter the results. The SELECT clause should have a column list,
not an asterisk (*). State the purpose of the query; show the query and the output.
The purpose of the query is to list all foster homes that accept cats.
SELECT ContactFirstName, ContactLastName, Phone
FROM FosterHome
WHERE PetType = 'cats';
ContactFirstName ContactLastName Phone
Pat Shawn 207-555-9876
Julie Prince 207-555-8888
Laura Martin 207-555-9999
Isabel Harper 207-555-4567
2. Get information from at least 3 tables in one statement and provide the output using the Join
operator. State the purpose of the query; show the query and the output.
The purpose of the query is to list all client families that have surrendered cats.
SELECT ClientFamilyFirstName AS [First Name],
ClientFamilyLastName AS [Last Name], SurrenderDate, Name AS [Name of Cat]
FROM ClientFamily c JOIN Surrender s
ON c.ClientFamilyID = s.ClientFamilyID
JOIN Pet p
ON p.PetID = s.PetID
WHERE Species = 'Feline'
ORDER BY SurrenderDate;
First Name Last Name SurrenderDate Name of Cat
Olivia Shastov 2011-02-18 Snowflake
Susan Ponti 2011-03-03 Smokey
Kate Ender 2011-03-09 Yang
Peter Goldberg 2011-04-22 Big Mike
IT 526_Unit 1 Create Table Resource Data document 14
Required screen image for query 2:
Figure 7: Multi Table Join in SQL Server Management Studio (SSMS)
3. Get information from 2 tables in one statement and provide the output using the Left Outer Join
operator. State the purpose of the query; show the query and the output. If the OUTER JOIN will
not return any non-matching rows, state why.
The purpose of the query is to show all pets that have never been adopted.
SELECT Species, Breed, Name
FROM Pet p LEFT JOIN adoption a
ON p.PetID = a.PetID
WHERE a.PetID IS NULL;
Species Breed Name
Feline Maine Coon Smokey
Feline Domestic Shorthair Big Mike
Feline Siamese Yang
Canine Golden Retriever Buddy
Feline Persian Snowflake
Note: All returned rows represent non-matches because of the WHERE clause.
IT 526_Unit 1 Create Table Resource Data document 15
4. Create a query using the IN keyword with a subquery. State the purpose of the query; show the
query and the output.
The purpose is to find all foster homes that currently contain pets.
SELECT ContactFirstName AS [First Name],
ContactLastName AS [Last Name], Phone
FROM FosterHome
WHERE FosterHomeID IN
( SELECT FosterHomeID
FROM FosterPlacement
WHERE EndDate IS NULL);
First Name Last Name Phone
Julie Prince 207-555-8888
Isabel Harper 207-555-4567
5. Create a query using an aggregate function (i.e., min, max, avg, sum, count) and the GROUP BY
command. State the purpose of the query; show the query and the output.
The purpose of the query is to count the number of client families in each city.
SELECT City, State, COUNT(City) as [Number in each city]
FROM ClientFamily
GROUP BY City, State;
City State Number in each city
Berwick ME 1
Eliot ME 2
Kennebunk ME 1
Wells ME 1
York ME 1
Dover NH 1
Portsmouth NH 1
Rochester NH 1
Somersworth NH 1
IT 526_Unit 1 Create Table Resource Data document 16
6. Create a query using an aggregate function (i.e., min, max, avg, sum, count) and the GROUP BY
command using the HAVING clause to filter the aggregate results. State the purpose of the query;
show the query and the output.
The purpose of the query is to list cities with more than one client family.
SELECT City, State, COUNT(City) as [Number in each city]
FROM ClientFamily
GROUP BY City, State
HAVING COUNT(City) > 1;
City State Number in each city
Eliot ME 2
7. Update one row. State the purpose of the query; show the result set for the row(s) before the
update; show the query; show the row(s) after the update.
The purpose of the query is to change the address of a foster family who moved.
Table before the update:
Foster Contact Contact Address City State Phone PetType Capacity
HomeID FirstName LastName
1001 Laura Martin 72 Elm St Saco ME 207-555-9999 Cats 8
1002 Julie Prince 43 Oak Rd Wells ME 207-555-8888 Cats 12
1003 Steve Smith 22 Maple Dr York ME 207-555-7654 Dogs 2
1004 Isabel Harper 64 Rhea Ct Saco ME 207-555-4567 Cats 14
1005 Pat Shawn 87 Pine Ave Wells ME 207-555-9876 Cats 6
UPDATE FosterHome
SET Address = '93 Ash Ln'
WHERE FosterHomeID = 1004;
SELECT * FROM FosterHome
WHERE FosterHomeID = 1004; -- shows row after UPDATE
Row after update:
Foster Contact Contact Address City State Phone PetType Capacity
HomeID FirstName LastName
1004 Isabel Harper 64 Rhea Ct Saco ME 207-555-4567 Cats 14
8. Delete one row. State the purpose of the query; show the result set before the delete; show the
query; show the result set after the delete.
IT 526_Unit 1 Create Table Resource Data document 17
The purpose of the query is to delete a foster placement of a kitten who passed away.
Table before deletion:
PetID BeginDate FosterHomeID EndDate
A1234B5678C9012 2011-03-03 1001 2011-04-02
B5678S9876V2244 2011-04-22 1004 NULL
F3456H5678J9382 2011-03-09 1002 NULL
J3982G2498N3918 2011-02-21 1003 2011-04-24
M7654F2345H9288 2011-02-18 1005 2011-05-08
DELETE FosterPlacement
WHERE PetID = 'F3456H5678J9382';
Table after deletion:
PetID BeginDate FosterHomeID EndDate
A1234B5678C9012 2011-03-03 1001 2011-04-02
B5678S9876V2244 2011-04-22 1004 NULL
J3982G2498N3918 2011-02-21 1003 2011-04-24
M7654F2345H9288 2011-02-18 1005 2011-05-08
IT 526_Unit 1 Create Table Resource Data document 18
RESOURCES
Hoffer, J. A., Ramesh, V., & Topi, H. (2011). Modern database management.
