Step 1

From the Windows start menu click on SQL Server Management Studio.

Step 2

Login to the database server. On campus enter the following:

For server type select Database Engine (which should be the default).

For the server name enter the name of our campus database server, which is cissql-1.

For authentication type select SQL Server Authentication.

For login name use your assigned login name. This should be the word "student" followed by your assigned two digit number. Do not login as student01 or studentXX.

Your password will be given to you in class.

From home enter:

For server type select Database Engine (which be the default).

At home your desktop or laptop will server as both the database client and the database server. For the server name enter the name of your desktop/laptop (it's probably filled in for you). You could also enter 127.0.0.1, which is the loop-back IP address that always points to your own machine.

At home select Windows Authentication.

You will not need to enter a user name or password. When you use Windows Authentication SQL Server uses the user name and password that you entered when you signed on to Windows.

Step 3

Open a SQL window in the appropriate database:

Click the plus sign next to Databases

Right click on the database you will be doing your homework in. On campus that will be studentXX (remember to replace XX with your assigned number). At home it will be the database you created when setting up SQL Server. Never use the master database.

Click on New query

Step 4

Let's create a table to hold the customer data for our fictitious mid-western manufacturing company. Enter the following SQL command into the query window.

CREATE TABLE Customers ( CustomerID DECIMAL NOT NULL, FirstName VARCHAR(30) NOT NULL, LastName VARCHAR(30) NOT NULL, City VARCHAR(30) NOT NULL, StateAbbr VARCHAR(2) NOT NULL, ZipCode VARCHAR(5) NOT NULL ); ALTER TABLE Customers ADD CONSTRAINT pk_Customers PRIMARY KEY (CustomerID); 

Execute the statement by pressing the F5 key (or pressing "Execute"). You should see "Command(s) completed successfully.".

Copy a screen shot of the entire SQL window (press Alt-PrtScr). Paste into the MS Word template.

Screenshot #1: CREATE TABLE statement

Step 5

Now we need to add some customers to the database. Enter and execute the following SQL statements:

INSERT INTO Customers (CustomerID,FirstName,LastName,City,StateAbbr,ZipCode) VALUES (1,'Jermaine','Patel','Miami','IL','43568'); INSERT INTO Customers (CustomerID,FirstName,LastName,City,StateAbbr,ZipCode) VALUES (2,'Denton','Snow','Lancaster','WI','53933'); INSERT INTO Customers (CustomerID,FirstName,LastName,City,StateAbbr,ZipCode) VALUES (3,'Nora','Garrett','Elsmere','IA','89736'); INSERT INTO Customers (CustomerID,FirstName,LastName,City,StateAbbr,ZipCode) VALUES (4,'Glenna','Freeman','Vincennes','IA','33152'); INSERT INTO Customers (CustomerID,FirstName,LastName,City,StateAbbr,ZipCode) VALUES (5,'Maia','Chavez','Brownsville','MI','70843'); INSERT INTO Customers (CustomerID,FirstName,LastName,City,StateAbbr,ZipCode) VALUES (6,'Baxter','Rosario','Woodruff','WI','30573'); INSERT INTO Customers (CustomerID,FirstName,LastName,City,StateAbbr,ZipCode) VALUES (7,'Iris','Lindsey','Bell','IL','84655'); INSERT INTO Customers (CustomerID,FirstName,LastName,City,StateAbbr,ZipCode) VALUES (8,'Quinlan','Fry','Elsmere','IL','05713'); INSERT INTO Customers (CustomerID,FirstName,LastName,City,StateAbbr,ZipCode) VALUES (9,'Oliver','Roberts','Attleboro','WI','19454'); INSERT INTO Customers (CustomerID,FirstName,LastName,City,StateAbbr,ZipCode) VALUES (10,'Jenna','Ford','Gaithersburg','MI','56330'); INSERT INTO Customers (CustomerID,FirstName,LastName,City,StateAbbr,ZipCode) VALUES (11,'Lev','McFadden','Blacksburg','IA','68046'); INSERT INTO Customers (CustomerID,FirstName,LastName,City,StateAbbr,ZipCode) VALUES (12,'Axel','Atkinson','Scottsdale','IL','57200'); INSERT INTO Customers (CustomerID,FirstName,LastName,City,StateAbbr,ZipCode) VALUES (13,'Kimberley','Waller','Cortland','IN','68824'); INSERT INTO Customers (CustomerID,FirstName,LastName,City,StateAbbr,ZipCode) VALUES (14,'Yvonne','Cardenas','Blythe','IN','62572'); INSERT INTO Customers (CustomerID,FirstName,LastName,City,StateAbbr,ZipCode) VALUES (15,'Howard','Vinson','Hornell','MI','35194'); INSERT INTO Customers (CustomerID,FirstName,LastName,City,StateAbbr,ZipCode) VALUES (16,'Garrett','Delgado','Webster Groves','IA','10111'); INSERT INTO Customers (CustomerID,FirstName,LastName,City,StateAbbr,ZipCode) VALUES (17,'Duncan','Rice','Alpharetta','IN','82421'); INSERT INTO Customers (CustomerID,FirstName,LastName,City,StateAbbr,ZipCode) VALUES (18,'TaShya','Henderson','Olean','WI','17649'); INSERT INTO Customers (CustomerID,FirstName,LastName,City,StateAbbr,ZipCode) VALUES (19,'Lani','Pennington','Shreveport','MI','59087'); INSERT INTO Customers (CustomerID,FirstName,LastName,City,StateAbbr,ZipCode) VALUES (20,'Berk','Barlow','Claremont','IA','81575'); 

Execute the statements by pressing the F5 key (or pressing "Execute"). You should see "(1 row(s) affected)" twenty times.

Step 6

Let's look at the data. Enter and execute the following query:

SELECT * FROM Customers;

You should see all twenty rows. Now let's look at only the customers who live in Indiana. Enter and execute the following query:

SELECT * FROM Customers WHERE StateAbbr = 'IN';

This time you should see three rows.

Step 7

The Indiana state legislature has banned our product because it is unsafe. We need to remove all customers who live in Indiana from our database. Enter and execute the following SQL statement:

DELETE FROM Customers WHERE StateAbbr = 'IN';

You should see "(3 row(s) affected)". Now look at all the rows in the table to verify that the customers are gone. Enter and execute the following query:

SELECT * FROM Customers;

This time you should see 17 rows (instead of 20).

Step 8

One of our customers, Jenna Ford, just got married. Her new last name is Parsons. First, let's lookup her record. Enter and execute the following query:

SELECT * FROM Customers WHERE LastName='Ford';

We see that her CustomerID is 10. Now let's update her record and see the result:

UPDATE Customers SET LastName = 'Parsons' WHERE CustomerID = 10; SELECT * FROM Customers WHERE CustomerID = 10; 

Step 9

The Environmental Protection Agency is investigating our company. The EPA has demanded that we give them a report listing all of our customers. The report should show the last name, first name, city, state name, and zip code. Sounds easy? Unfortunately the EPA format requires the state name to be spelled out (e.g. "Illinois"), not abbreviated. Additionally the report must be sorted by state name and then city name.

Start by creating a "lookup" table of states, with both the state abbreviation and full name. Then populate the table with the states in which we have customers. Enter and execute the following SQL statements:

CREATE TABLE States ( StateAbbr VARCHAR(2) NOT NULL, StateFullName VARCHAR(20) NOT NULL ); ALTER TABLE States ADD CONSTRAINT pk_States PRIMARY KEY (StateAbbr); INSERT INTO States VALUES ('IL','Illinois'); INSERT INTO States VALUES ('IA','Iowa'); INSERT INTO States VALUES ('MI','Michigan'); INSERT INTO States VALUES ('WI','Wisconsin'); SELECT * FROM States; 

Next, create a foreign key between the two tables:

ALTER TABLE Customers ADD FOREIGN KEY (StateAbbr) REFERENCES States (StateAbbr); 

Now run a query joining the two tables together to create the report for the EPA:

 SELECT FirstName, LastName, City, StateFullName, ZipCode FROM Customers INNER JOIN States ON Customers.StateAbbr = States.StateAbbr ORDER BY StateFullName, City; 

You should have 17 rows. The state field should have the complete spelling, and the rows should be ordered by state and city.

Copy a screen shot of the entire SQL window (press Alt-PrtScr). Paste into the MS Word template.

Screenshot #2: Select statement