SQL Book

SQL Script for "SQL Simplified:
Learn To Read and Write Structured Query
Language (SQL)"

This web site provides the SQL script to create and populate the tables used in the book entitled, "SQL Simplified: Learn To Read and Write Structured Query Language (SQL)." In order for you to run each of the examples in the book, you need to create and populate the tables on this site. Copy and paste the SQL script directly into your Database Management System (DBMS).

The script is written in it's simplest form to accommodate multiple DBMSs. For some DBMSs it may be necessary to 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.

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".
Oracles "SQL Plus" Editor
MySQL Editor
DB2 links

To create and populate each table copy, paste and run the Create Table script. Next, delete the Create Table script and copy, paste and run each Insert Statement ONE AT A TIME. Each Insert Statement is separated by a semi colon.

Important: Read the important notes below before you begin.

* Note: Before creating and populating your tables, most DBMSs excluding 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 SQLPractice, type the following:

CREATE DATABASE SQLPractice;

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

* Note: In some DBMSs you may have to set your primary key as follows: CustomerID INTEGER NOT NULL CONSTRAINT PriKey Primary Key.

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

* Note: In some DBMSs you may need to substitute a DECIMAL (8,2), or CURRENCY datatype for the MONEY datatype in the Create Table script.

* Note: Oracle users must use the DATE datatype in place of the DATETIME datatype in the Create Table script.

* 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 "sqlExamples ":

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

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

Committee1	Committee2	Courses	Customers	Customers2
Employees	Members	Numbers	Sales	Supplies

Committee1 Create Table Script

CREATE TABLE Committee1
(
SocialSecNum CHAR (11) NOT NULL PRIMARY KEY,
Firstname CHAR (50) NOT NULL,
Lastname CHAR (50) NOT NULL,
Address CHAR (50) NOT NULL,
Zipcode CHAR (10) NOT NULL,
Areacode CHAR (3) NULL,
PhoneNumber CHAR (8) NULL
);

Insert Statements for the Committee1 Table

INSERT INTO Committee1
VALUES ('263-73-1442', 'Adam', 'Williams', '1938 32nd Ave S. St. Pete, FL', 33711, 727, '321-2234');

INSERT INTO Committee1
VALUES ('226-73-1919', 'Jacob', 'Lincoln', '2609 40th Ave S Honolulu, HI', 96820, 808, '423-4111');

INSERT INTO Committee1
VALUES ('249-74-1682', 'Jackie', 'Fields', '2211 Peachtree St N Tampa, FL', 33612, 813, '827-2301');

INSERT INTO Committee1
VALUES ('123-88-1982', 'Debra', 'Fields', '1934 16th Ave N Atlanta, GA', 98718, 301, '897-3245');

INSERT INTO Committee1
VALUES ('211-73-1112', 'Tom', 'Jetson', '1311 2nd Ave E Atlanta, GA', 98718, 301, '897-9877');

Top

Committee2 Create Table Script

CREATE TABLE Committee2
(
SocialSecNum CHAR (11) NOT NULL PRIMARY KEY,
Firstname CHAR (50) NOT NULL,
Lastname CHAR (50) NOT NULL,
Address CHAR (50) NOT NULL,
Zipcode CHAR (10) NOT NULL,
Areacode CHAR (3) NULL,
PhoneNumber CHAR (8) NULL
);

Insert Statements for the Committee2 Table

INSERT INTO Committee2
VALUES ('266-73-1982', 'John', 'Dentins', '2211 22nd Ave N Atlanta, GA', 98718, 301, '897-4321');

INSERT INTO Committee2
VALUES ('266-11-4444', 'Sam', 'Elliot', '1601 Center Loop Tampa, FL', 33612, 813, '898-2134');

INSERT INTO Committee2
VALUES ('263-73-1442', 'Adam', 'Williams', '1938 32nd Ave S. St. Pete, FL', 33711, 727, '321-2234');

INSERT INTO Committee2
VALUES ('226-73-1919', 'Jacob', 'Lincoln', '2609 40th Ave S Honolulu, HI', 96820, 808, '423-4111');

INSERT INTO Committee2
VALUES ('249-74-1682', 'Jackie', 'Fields', '2211 Peachtree St N Tampa, FL', 33612, 813, '827-2301');

Top

Courses Create Table Script

CREATE TABLE Courses
(
CourseID CHAR (20) NOT NULL Primary Key,
StudentID CHAR (4) NOT NULL,
Course CHAR (50) NOT NULL,
StartTime CHAR (50) NOT NULL,
EndTime CHAR (50) NOT NULL,
StartDate DATETIME NOT NULL,
EndDate DATETIME NOT NULL,
Teacher CHAR (30) NOT NULL,
Credit CHAR (2) NOT NULL
);

Insert Statements for the Courses Table

INSERT INTO Courses
Values ('M1101', 1, 'Pre Algebra', '3:00pm', '5:00pm', '2/3/03', '5/3/03', 'Mr. Stevens', 3);

INSERT INTO Courses
Values ('M1102', 5, 'Pre Calculus', '3:00pm', '5:00pm', '2/3/03', '5/3/03', 'Mr. Dixon', 3);

INSERT INTO Courses
Values ('L1001', 3, 'Literature', '2:00pm', '4:00pm', '2/3/03', '5/3/03', 'Mrs. Donaldson', 3);

INSERT INTO Courses
Values ('R1001', 2, 'Reading', '1:00pm', '3:00pm', '2/3/03', '5/3/03', 'Ms Jackson', 3);

INSERT INTO Courses
Values ('M1103', 4, 'Statistics', '3:00pm', '5:00pm', '2/3/03', '5/3/03', 'Mr. Levin ', 3);

INSERT INTO Courses
Values ('D1000', 3, 'Database Basics', '1:00pm', '3:00pm', '2/3/03', '5/3/03', 'Mr. Carter', 3);

INSERT INTO Courses
Values ('A1000', 2, 'Accounting I', '2:00pm', '4:00pm', '2/3/03', '5/3/03', 'Mrs. Smith', 3);

INSERT INTO Courses
Values ('A1001', 5, 'Accounting II', '1:00pm', '3:00pm', '2/3/03', '5/3/03', 'Mrs. Terry', 3);

INSERT INTO Courses
Values ('P2000', 4, 'Physics', '2:00pm', '4:00pm', '2/3/03', '5/3/03', 'Mrs. Jones', 3);

INSERT INTO Courses
Values ('H1011', 1, 'Human Resource Mgt', '3:00pm', '5:00pm', '2/3/03', '5/3/03', 'Mr. Pen', 3);

Top

Customers Create Table Script

CREATE TABLE Customers
(
CustomerID INTEGER NOT NULL PRIMARY KEY,
Firstname CHAR (50) NOT NULL,
Lastname CHAR (50) NOT NULL,
Address CHAR (50) NOT NULL,
City CHAR (20) NOT NULL,
State CHAR (2) NOT NULL,
Zipcode CHAR (10) NOT NULL,
Areacode CHAR (3) NULL,
PhoneNumber CHAR (8) NULL
);

Insert Statements for the Customers Table

INSERT INTO Customers
VALUES (1, 'Tom', 'Evans', '3000 2nd Ave S', 'Atlanta', 'GA', 98718, 301, '232-9000');

INSERT INTO Customers
VALUES (2, 'Larry', 'Genes', '1100 23rd Ave S', 'Tampa', 'FL', 33618, 813, '982-3455');

INSERT INTO Customers
VALUES (3, 'Sherry', 'Jones', '100 Free St S', 'Tampa', 'FL', 33618, 813, '890-4231');

INSERT INTO Customers
VALUES (4, 'April', 'Jones', '2110 10th St S', 'Santa Fe', 'NM', 88330, 505, '434-1111');

INSERT INTO Customers
VALUES (5, 'Jerry', 'Jones', '798 22nd Ave S', 'St. Pete', 'FL', 33711, 727, '327-3323');

INSERT INTO Customers
VALUES (6, 'John', 'Little', '1500 Upside Loop N', 'St. Pete', 'FL', 33711, 727, '346-1234');

INSERT INTO Customers
VALUES (7, 'Gerry', 'Lexingtion', '5642 5th Ave S', 'Atlanta', 'GA', 98718, 301, '832-8912');

INSERT INTO Customers
VALUES (8, 'Henry', 'Denver', '8790 8th St N', 'Holloman', 'NM', 88330, 505, '423-8900');

INSERT INTO Customers
VALUES (9, 'Nancy', 'Kinn', '4000 22nd St S', 'Atlanta', 'GA', 98718, 301, '879-2345');

INSERT INTO Customers
VALUES (10, 'Derick', 'Penns', '2609 15th Ave N', 'Tampa', 'FL', 33611, 813, '346-1232');

Top

Customers2 Create Table Script

CREATE TABLE Customers2
(
CustomerID INTEGER NOT NULL PRIMARY KEY,
Firstname CHAR (50) NOT NULL,
Lastname CHAR (50) NOT NULL,
Address CHAR (50) NOT NULL,
City CHAR (20) NOT NULL,
State CHAR (2) NOT NULL,
Zipcode CHAR (10) NOT NULL,
Areacode CHAR (3) NULL,
PhoneNumber CHAR (8) NULL
);

Insert Statements for the Customers2 Table

INSERT INTO Customers2
VALUES (1, 'Tom', 'Evans', '3000 2nd Ave S', 'Atlanta', 'GA', 98718, 301, '232-9000');

INSERT INTO Customers2
VALUES (2, 'Larry', 'Genes', '1100 23rd Ave S', 'Tampa', 'FL', 33618, 813, '982-3455');

INSERT INTO Customers2
VALUES (3, 'Sherry', 'Jones', '100 Free St S', 'Tampa', 'FL', 33618, 813, '890-4231');

INSERT INTO Customers2
VALUES (4, 'April', 'Jones', '2110 10th St S', 'Santa Fe', 'NM', 88330, 505, '434-1111');

INSERT INTO Customers2
VALUES (5, 'Jerry', 'Jones', '798 22nd Ave S', 'St. Pete', 'FL', 33711, 727, '327-3323');

INSERT INTO Customers2
VALUES (6, 'John', 'Little', '1500 Upside Loop N', 'St. Pete', 'FL', 33711, 727, '346-1234');

INSERT INTO Customers2
VALUES (7, 'Gerry', 'Lexingtion', '5642 5th Ave S', 'Atlanta', 'GA', 98718, 301, '832-8912');

INSERT INTO Customers2
VALUES (8, 'Henry', 'Denver', '8790 8th St N', 'Holloman', 'NM', 88330, 505, '423-8900');

INSERT INTO Customers2
VALUES (9, 'Nancy', 'Kinn', '4000 22nd St S', 'Atlanta', 'GA', 98718, 301, '879-2345');

INSERT INTO Customers2
VALUES (10, 'Derick', 'Penns', '2609 15th Ave N', 'Tampa', 'FL', 33611, 813, '346-1232');

INSERT INTO Customers2
VALUES (11, 'Adam', 'Williams', '1333 5th St N', 'Tampa', 'FL', 33611, 813, '326-7777');

INSERT INTO Customers2
VALUES (12, 'Stan', 'Willows', '1837 30th Ave S', 'Tampa', 'FL', 33611, 813, '346-1100');

INSERT INTO Customers2
VALUES (13, 'Ricky', 'Canton', '1009 50th Ave N', 'Tampa', 'FL', 33611, 813, '346-3223');

INSERT INTO Customers2
VALUES (14, 'Pete', 'West', '2000 4th Ave N', 'Tampa', 'FL', 33611, 813, '346-8778');

Top

Employees Create Table Script

CREATE TABLE Employees
(
SocialSecNum CHAR (11) NOT NULL PRIMARY KEY,
Firstname CHAR (50) NOT NULL,
Lastname CHAR (50) NOT NULL,
Address CHAR (50) NOT NULL,
Zipcode CHAR (10) NOT NULL,
Areacode CHAR (3) NULL,
PhoneNumber CHAR (8) NULL
);

Insert Statements for the Employees Table

INSERT INTO Employees
VALUES ('266-73-1982', 'John', 'Dentins', '2211 22nd Ave N Atlanta, GA', 98718, 301, '897-4321');

INSERT INTO Employees
VALUES ('266-11-4444', 'Sam', 'Elliot', '1601 Center Loop Tampa, FL', 33612, 813, '898-2134');

INSERT INTO Employees
VALUES ('263-73-1442', 'Adam', 'Williams', '1938 32nd Ave S. St. Pete, FL', 33711, 727, '321-2234');

INSERT INTO Employees
VALUES ('226-73-1919', 'Jacob', 'Lincoln', '2609 40th Ave S Honolulu, HI', 96820, 808, '423-4111');

INSERT INTO Employees
VALUES ('249-74-1682', 'Jackie', 'Fields', '2211 Peachtree St N Tampa, FL', 33612, 813, '827-2301');

INSERT INTO Employees
VALUES ('123-88-1982', 'Debra', 'Fields', '1934 16th Ave N Atlanta, GA', 98718, 301, '897-3245');

INSERT INTO Employees
VALUES ('211-73-1112', 'Tom', 'Jetson', '1311 2nd Ave E Atlanta, GA', 98718, 301, '897-9877');

INSERT INTO Employees
VALUES ('980-22-1982', 'Shawn', 'Lewis', '1601 4th Ave W Atlanta, GA', 98718, 301, '894-0987');

INSERT INTO Employees
VALUES ('982-24-3490', 'Yolanda', 'Brown', '1544 16th Ave W Atlanta, GA', 98718, 301, '892-1234');

INSERT INTO Employees
VALUES ('109-83-4765', 'Shaun', 'Rivers', '1548 6th Ave S Atlanta, GA', 98718, 301, '894-1973');

Top

Members Create Table Script

CREATE TABLE Members
(
MemberID INTEGER NOT NULL PRIMARY KEY,
Firstname CHAR (50) NOT NULL,
Lastname CHAR (50) NOT NULL,
Address CHAR (50) NOT NULL,
City CHAR (20) NOT NULL,
State CHAR (2) NOT NULL,
Zipcode CHAR (10) NOT NULL,
Areacode CHAR (3) NULL,
PhoneNumber CHAR (8) NULL
);

Insert Statements for the Members Table

INSERT INTO Members
VALUES (1, 'Jeffrey', 'Lindley', '3980 14th Ave S', 'Atlanta', 'GA', 98700, 301, '451-5451');

INSERT INTO Members
VALUES (2, 'Jerry', 'Lindsey', '4000 3rd Ave S', 'Tampa', 'FL', 33600, 813, '923-7852');

INSERT INTO Members
VALUES (3, 'Gerry', 'Pitts', '3090 13th St N', 'Tampa', 'FL', 33611, 813, '286-4821');

INSERT INTO Members
VALUES (4, 'Stan', 'Benson', '1825 8th St N', 'Santa Fe', 'NM', 88388, 505, '464-1578');

INSERT INTO Members
VALUES (5, 'Peter', 'Gable', '1097 10th Ave S', 'St. Petersburg', 'FL', 33754, 727, '327-1253');

Top

Numbers Create Table Script

CREATE TABLE Numbers
(
Column1 INTEGER NOT NULL,
Column2 INTEGER NOT NULL,
Column3 INTEGER NOT NULL
);

Insert Statements for the Numbers Table

INSERT INTO Numbers
VALUES (20.00, 4, 21.3);

INSERT INTO Numbers
VALUES (10.00, 5, 20.3);

INSERT INTO Numbers
VALUES (30.00, 10, 16.8);

INSERT INTO Numbers
VALUES (50.00, 2, 18.3);

INSERT INTO Numbers
VALUES (60.00, 30, 12.6);

INSERT INTO Numbers
VALUES (70.00, 2, 2.1);

INSERT INTO Numbers
VALUES (10.00, 39, 2.9);

INSERT INTO Numbers
VALUES (40.00, 29, 19.2);

INSERT INTO Numbers
VALUES (80.00, 54, 15.8);

INSERT INTO Numbers
VALUES (20.00, 66, 23.1);

Top

Sales Create Table Script

CREATE TABLE Sales
(
SalesID INTEGER NOT NULL PRIMARY KEY,
SupplyID CHAR (7) NOT NULL,
CustomerID INTEGER NOT NULL,
DateSold DATETIME NOT NULL
);

Insert Statements for the Sales Table

INSERT INTO Sales
VALUES (1, 'AR100', 2, '2/3/03');

INSERT INTO Sales
VALUES (2, 'WC100', 8, '2/5/03');

INSERT INTO Sales
VALUES (3, 'AR100', 7, '2/6/03');

INSERT INTO Sales
VALUES (4, 'FL100', 1, '2/8/03');

INSERT INTO Sales
VALUES (5, 'MT100', 3, '2/8/03');

INSERT INTO Sales
VALUES (6, 'GR100', 4, '2/10/03');

INSERT INTO Sales
VALUES (7, 'WC100', 5, '2/22/03');

INSERT INTO Sales
VALUES (8, 'PS100', 9, '2/20/03');

INSERT INTO Sales
VALUES (9, 'CD100', 6, '2/18/03');

INSERT INTO Sales
VALUES (10, 'CP100', 10, '2/17/03');

INSERT INTO Sales
VALUES (11, 'CP100', 10, '2/17/03');

INSERT INTO Sales
VALUES (12, 'CP100', 5, '2/17/03');

INSERT INTO Sales
VALUES (13, 'CC100', 4, '2/17/03');

INSERT INTO Sales
VALUES (14, 'GR100', 3, '2/8/03');

INSERT INTO Sales
VALUES (15, 'MT100', 2, '2/17/03');

INSERT INTO Sales
VALUES (16, 'WC100', 1, '2/8/03');

INSERT INTO Sales
VALUES (17, 'CP100', 3, '2/8/03');

Top

Supplies Create Table Script

CREATE TABLE Supplies
(
SupplyID CHAR (7) NOT NULL PRIMARY KEY,
SupplyName CHAR (50) NOT NULL,
Price MONEY NOT NULL,
SalePrice MONEY NOT NULL,
InStock INTEGER NOT NULL,
OnOrder INTEGER NOT NULL
);

Note: For Microsoft SQL Server, substitute DECIMAL (8, 2) for the MONEY datatype.

Insert Statements for the Supplies Table

INSERT INTO Supplies
VALUES ('CD100', 'China Doll', 20.00, 18.00, 200, 0);

INSERT INTO Supplies
VALUES ('CP100', 'China Puppy', 15.00, 13.50, 20, 40);

INSERT INTO Supplies
VALUES ('WC100', 'Wooden Clock', 11.00, 9.90, 100, 0);

INSERT INTO Supplies
VALUES ('GR100', 'Glass Rabbit', 50.00, 45.00, 50, 20);

INSERT INTO Supplies
VALUES ('CC100', 'Crystal Cat', 75.00, 67.50, 60, 20);

INSERT INTO Supplies
VALUES ('PS100', 'Praying Statue', 25.00, 22.50, 3, 40);

INSERT INTO Supplies
VALUES ('MT100', 'Miniature Train Set', 60.00, 54.00, 1, 30);

INSERT INTO Supplies
VALUES ('DB100', 'Dancing Bird', 10.00, 9.00, 10, 20);

INSERT INTO Supplies
VALUES ('FL100', 'Friendly Lion', 14.00, 12.60, 0, 30);

INSERT INTO Supplies
VALUES ('AR100', 'Animated Rainbow', 20.00, 18.00, 10, 20);

Top

Author: Cecelia L. Allison
E-mail

JAFFA INC. Computer Learning Center