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

Welcome to Introduction to SQL. Further below 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.

Note: Important! Before reading this document, please ensure that you have read my WELCOME posting (Getting Started, Trial Software) located in the lesson one discussion area of the Introduction to SQL course.

You can copy and paste the SQL script directly into SQL Server, or you can use this script in other Database Management Systems (DBMSs) that contain query interfaces that you are accustomed to using.

Note: SQL Server: Click on SQL Server to view instructions on creating the Course tables in SQL Server.

Note: Microsoft Access: If you are using Microsoft Access, you must instead "Download the Course Database". Click on Microsoft Access Download. to download the Microsoft Access database.When you download the database the tables will ALREADY BE CREATED AND POPULATED with records.

Microsoft Access users download the course database instead of creating the tables manually because the SQL View interface of Microsoft Access only enables you to run one SQL statement at a time. This is very time consuming, therefore the database is provided with all of the tables and data. In lesson 9 we will discuss table creation and inserting records into tables so DO NOT feel as though you missed out on anything.

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 out the NOTES at the top of each page containing the SQL script or post a question concerning syntax in the discussion area of the course.

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.


Download the Microsoft Access "2003", "2007", "2010" "2013"or "2016" Course Database:

If you are using Microsoft Access 2003, 2007, 2010, 2013, or 2016 you will download the Course Database from the following links below:

If you are using Microsoft Access 2013 or 2016 you can download either the Microsoft Access 2007 or 2010 Course Database since it is compatible with the Microsoft Access 2013 and 2016 file format.

2003: Practice.mdb

2007: Practice.accdb

2010: Practice.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.


Instructions to Create the Course Database Tables:

Note: SQL SERVER users must refer to the following link to create the tables: SQL Server instructions

Note: Microsoft Access users will download the course database with the tables already created and populated. Click on: Microsoft Access Download

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: Be sure not to include the headings (CUSTOMER CREATE TABLE SCRIPT, CUSTOMER TABLE INSERT STATEMENTS) for the scripts in your code. Only paste the code into your database software.

Note: To view any completed table, type the following script (Replace the table name in the script to view different tables). For example, to view the 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: Do not run the INSERT Statements more than once or it may generate errors.

* 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);

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