Course Script

SQL Script for the
Customer, Customer2, ServicePlans, and Orders Tables

Welcome to Introduction to SQL. Here you will find the SQL script to create and populate the tables used in the course. In order for you to run each of the examples in the course, you need to create four tables. With the exception of the Customer2 table, all of the following tables must be populated with records: Customer, ServicePlans, Orders and Customer2.

You can copy and paste the SQL script directly into Microsoft Access or you can use this script in other Database Management Systems (DBMSs).

Note: If you are using Microsoft Access 2003 or a later version, further down the page there is an option to download the course database as opposed to copying and pasting the SQL script into Microsoft Access.

The SQL script is written in the simplest form to accommodate multiple DBMSs but you may have to make minimal modifications to the script to make it compatible. Check your DBMS documentation for changes.

Following are links to sites that supply instructions on how to locate the interface that executes SQL script in some of the more popular DBMSs.

Locate interface for SQL script:

Microsoft Access instructions on where to paste or type SQL script.
Microsoft SQL Server's Query Analyzer . To locate "Query Analyzer", within "Enterprise Manager", click tools and select "Query Analyzer".

2005 Microsoft SQL Server's "Editor tool" . To locate the "SQL Editor Tool" in SQL Server. Using 2005 SQL Server "Management Studio".
Microsoft SQl Sever 2005 Tutorial
Oracles "SQL Plus" Editor
MySQL

DB2 links

Download the Microsoft Access 2003 or 2007 Course Database:

If you are using Microsoft Access 2003 or 2007, you have the option to download the course database from the following links:

2003: AccessDatabase.mdb

2007: AccessDatabase.accdb

Save the file to your desktop. Next, open the file from your desktop or from the pop-up box that appears after the download. Go to "SQL View" to run the queries in the lessons.

Note: The Customer table contains 2 additional records (12, 13) that will not be needed until lesson 7 and 8.

Note: The ServicePlans table contains 1 additional record (E1001) that will not be needed until lesson 7 and 8.

Create the Course Database Tables Using SQL Script:

In order to create and populate the Customer table, you must first copy, paste and run the Create Table script for the Customer table. Next, delete the Create Table script and copy, paste and run each Insert Statement ONE AT A TIME (Some DBMSs such as Microsoft SQL Server, allow you to run several inserts statements at the same time) to populate the Customer table. Each Insert Statement is separated by a semi colon.

Note: In Microsoft Access, each time you insert a new record, a message will display telling you that you are about to append 1 record. Click yes on this message.

Note: In some versions of Microsoft Access you may have to set your primary key in the Create Table scripts using the following method: CustomerID INTEGER NOT NULL CONSTRAINT PriKey Primary Key,

Note: Some DBMSs such as Microsoft SQL Server, allow you to run several inserts statements at the same time.

Note: To see the completed Customer table, type the following script:

SELECT *
FROM Customer;

**Follow the same preceding steps to create and populate the ServicePlans, and Orders tables. Create the Customer2 table, but do not populate it. The Customer2 table will be populated in lesson eight.

Important: Read the important notes below before you begin.

* Note: Before creating and populating your tables, most DBMSs (not Microsoft Access), require you to run an SQL statement that creates a name for your database and allocates space for your tables. The syntax for most DBMSs is as follows:

CREATE DATABASE DatabaseName;

For example, if you want to name your database SQLCourse, type the following:

CREATE DATABASE SQLCourse;

* Note: Do not run the INSERT Statements more than once or it may generate errors.

* Note: In some versions of Microsoft Access you may have to set your primary key in the Create Table scripts using the following method: CustomerID INTEGER NOT NULL CONSTRAINT PriKey Primary Key,

* Note: In Microsoft Access, when you do not state NOT NULL when creating a column the column is automatically set to NULL. In some DBMSs however, if you want to set a column to NULL you must explicitly state NULL.

* Note: MySQL requires that you specify where the table is to be created.

For example, the following script creates a table named "customer" in a database named "sqlCourse":

c:\MySQL\bin\mysql -f sqlCourse < create_customer.sql

* Note: Some DBMSs do not require an ending semicolon at the end of an SQL statement.

* Note: 1. In AS400/DB2 remove the ";" at the end of each statement. 2. When using RUNSQLSTM, AS400 wants the text file containing the SQL statements to be no longer than 80 characters. You need to adjust the SQL statements accordingly. 3. Insert each insert statement one at a time.

CUSTOMER CREATE TABLE SCRIPT

CREATE TABLE Customer
(
CustomerID INTEGER Primary Key NOT NULL,
FirstName CHAR(50) NOT NULL,
LastName CHAR(50) NOT NULL,
HomePhone CHAR(20),
Address CHAR(50),
State CHAR(02),
City CHAR(30),
PostalCode INTEGER
);

CUSTOMER TABLE INSERT STATEMENTS

INSERT INTO Customer (CustomerID, FirstName, LastName, HomePhone, Address, State, City, PostalCode)
Values (1, 'John', 'Dentins', '(808) 897-4321', ' 2211 22nd Ave N', 'GA', 'Atlanta', 98718);

INSERT INTO Customer (CustomerID, FirstName, LastName, HomePhone, Address, State, City, PostalCode)
Values (2, 'Sam', 'Elliot', '(719) 898-2134', '1601 Center loop', 'FL', 'Tampa', 98982);

INSERT INTO Customer (CustomerID, FirstName, LastName, HomePhone, Address, State, City, PostalCode)
Values (3, 'Adam', 'Williams', '(898) 321-2234', '3890-A Cherry loop', 'ND', 'Fargo', 58729);

INSERT INTO Customer (CustomerID, FirstName, LastName, HomePhone, Address, State, City, PostalCode)
Values (4, 'Jacob', 'Lincoln', NULL, '2609 40th Ave S', 'OK', 'Tulsa', 36711);

INSERT INTO Customer (CustomerID, FirstName, LastName, HomePhone, Address, State, City, PostalCode)
Values (5, 'Timothy', 'Coney', '(202) 827-4331', '1900 3rd St. N', 'OR', 'Salem', 44812);

INSERT INTO Customer (CustomerID, FirstName, LastName, HomePhone, Address, State, City, PostalCode)
Values (6, 'Jill', 'Stephens', '(813) 215-5551', '1544 33rd Ave E', 'FL', 'Miami', 98911);

INSERT INTO Customer (CustomerID, FirstName, LastName, HomePhone, Address, State, City, PostalCode)
Values (7, 'Tina', 'West', '(813) 565-4984', '1000 45th Ave N', 'FL', 'Miami', 98911);

INSERT INTO Customer (CustomerID, FirstName, LastName, HomePhone, Address, State, City, PostalCode)
Values (8, 'Shawn', 'Leaven', '(813) 248-8854', '1908 22nd Ave S', 'FL', 'Miami', 98912);

INSERT INTO Customer (CustomerID, FirstName, LastName, HomePhone, Address, State, City, PostalCode)
Values (9, 'Jackie', 'Justin', NULL, '1800 4rd St. N', 'FL', 'Miami', 98902);

INSERT INTO Customer (CustomerID, FirstName, LastName, HomePhone, Address, State, City, PostalCode)
Values (10, 'Lilly', 'Ward', '(813) 244-5522', '1010 33rd Ave N', 'FL', 'Miami', 98909);

NOTE: The Customer table in lesson 7 and 8 contains 2 extra records. Below you will find the two extra records. You can add the records now or you can add them when you get to lessons 7 and 8. Adding the records now is not a problem as long as you remember that you added them since you may see the records in some of your results.

INSERT INTO Customer (CustomerID, FirstName, LastName, HomePhone, Address, State, City, PostalCode)
Values (12, 'Larry', 'Carr', '(808) 423-7894', '114 - C 45th Ave N', 'HI', 'Honolulu', 96818);

INSERT INTO Customer (CustomerID, FirstName, LastName, HomePhone, Address, State, City, PostalCode)
Values (13, 'Lisa', 'Glenn', '(808) 421-7515', '1542 33rd Ave N', 'HI', 'Honolulu', 96818);

ServicePlans Table

Orders Table

Customer2 Table