Appendix E SQL Script to Create the Tables in This Book

This appendix provides the SQL script to create and populate 16 of the tables used in the examples throughout the book. To create and populate a single table, run the Create Table script. Next, delete the Create Table script and copy, paste, and run each INSERT statement one at a time.

Note: In Microsoft Access, each time you insert a new record, a message will display telling you that you are about to append one record. Click Yes in response to this message.

Create and Populate the Activities Table


CREATE TABLE Activities
(
ActivityID NUMBER CONSTRAINT ActID PRIMARY KEY,
ActivityName CHAR (50) NOT NULL,
StartDate DATE,
EndDate DATE
);

INSERT INTO Activities (ActivityID, ActivityName, StartDate,
       EndDate)
VALUES (1, 'Aerobics', '01/1/08', '01/9/08'),

INSERT INTO Activities (ActivityID, ActivityName, StartDate,
       EndDate)
VALUES (2, 'Games', '01/2/08', '01/10/08'),

INSERT INTO Activities (ActivityID, ActivityName, StartDate,
       EndDate)
VALUES (3, 'Outdoor activities', '01/3/08', '01/10/08'),

INSERT INTO Activities (ActivityID, ActivityName, StartDate,
       EndDate)
VALUES (4, 'Trips and tours', '01/1/08', '01/17/08'),

INSERT INTO Activities (ActivityID, ActivityName, StartDate,
       EndDate)
VALUES (5, 'Arts and crafts', '01/17/08', '01/27/08'),

INSERT INTO Activities (ActivityID, ActivityName, StartDate,
       EndDate)
VALUES (6, 'Resident discussion groups', '01/9/08', '01/17/08'),

INSERT INTO Activities (ActivityID, ActivityName, StartDate,
       EndDate)
VALUES (7, 'Coffee or cocktail hours', '01/1/08', NULL);

Create and Populate the Committee1 Table


CREATE TABLE Committee1
(
CommitteeID INTEGER CONSTRAINT Com1ID 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 INTO Committee1
VALUES (1, 'Yolanda', 'Cole', '3466 42nd Ave E. St. Pete, FL',
        33711, 727, '321-1111'),

INSERT INTO Committee1
VALUES (2, 'John', 'Allison', '2345 40th Ave N Honolulu, HI', 96820,
        808, '423-4222'),

INSERT INTO Committee1
VALUES (3, 'Kayla', 'Fields', '2211 Peachtree St S Tampa, FL',
        33612, 813, '827-4532'),

INSERT INTO Committee1
VALUES (4, 'Debra', 'Brown', '1900 12th Ave S Atlanta, GA', 98718,
        301, '897-0987'),

INSERT INTO Committee1
VALUES (5, 'Leonard', 'Miles', '400 22nd Ave N Atlanta, GA', 98718,
        301, '897-1723'),

Create and Populate the Committee2 Table


CREATE TABLE Committee2
(
CommitteeID INTEGER CONSTRAINT Com2ID 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 INTO Committee2
VALUES (1, 'Leonard', 'Cole', '1323 13th Ave N Atlanta, GA', 98718,
        301, '897-1241'),

INSERT INTO Committee2
VALUES (2, 'Panzina', 'Coney', '9033 Colfax Loop Tampa, FL', 33612,
        813, '223-6754'),

INSERT INTO Committee2
VALUES (3, 'Kayla', 'Fields', '2211 Peachtree St S Tampa, FL',
        33612, 813, '827-4532'),

INSERT INTO Committee2
VALUES (4, 'Jerru', 'London', '6711 40th Ave S Honolulu, HI', 96820,
        808, '611-2341'),

INSERT INTO Committee2
VALUES (5, 'Debra', 'Brown', '1900 12th Ave S Atlanta, GA', 98718,
        301, '897-0987'),

Create and Populate the Computers Table


CREATE TABLE Computers
(
SerialNum CHAR (11) CONSTRAINT CompIDPk PRIMARY KEY,
Brand CHAR (20) NOT NULL,
Department CHAR (20) NOT NULL,
OfficeNumber NUMBER NOT NULL
);

INSERT INTO Computers
VALUES ('M6289288289', 'Dell', 'Accounting', 134);

INSERT INTO Computers
VALUES ('G9277288282', 'Dell', 'HR', 122);

INSERT INTO Computers
VALUES ('X8276538101', 'Dell', 'HR', 311);

INSERT INTO Computers
VALUES ('W2121040244', 'Gateway', 'CustomerService', 22);

INSERT INTO Computers
VALUES ('R2871620091', 'Dell', 'Information Systems', 132);

Create and Populate the Customers Table


CREATE TABLE Customers
(
CustomerID NUMBER CONSTRAINT CusID 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 INTO Customers
VALUES (1, 'Kayla', 'Allison', '6725 3rd Ave N', 'Atlanta', 'GA',
        98700, 301, '897-3412'),

INSERT INTO Customers
VALUES (2, 'Devin', 'Fields', '1001 30th St S', 'Tampa', 'FL',
        33677, 813, '828-8754'),

INSERT INTO Customers
VALUES (3, 'Gene', 'Spencer', '3910 35th Ave S.', 'St. Pete', 'FL',
        33700, 727, '321-1111'),

INSERT INTO Customers
VALUES (4, 'Spencer', 'Madewell', '32101 60th Ave E', 'Honolulu',
        'HI', 96822, 808, '423-4444'),

INSERT INTO Customers
VALUES (5, 'Reggie', 'Collins', '1526 1st St N', 'Tampa', 'FL',
        33622, 813, '847-9002'),

INSERT INTO Customers
VALUES (6, 'Penny', 'Penn', '2875 Treetop St N', 'Tampa', 'FL',
        33621, 813, '821-7812'),

Create and Populate the Customers2 Table


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 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', 'Lexington', '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'),

Create and Populate the Departments Table


CREATE TABLE Departments
(
DepartmentID INTEGER CONSTRAINT DepID PRIMARY KEY,
SocialSecNum CHAR (50) NOT NULL,
DepartmentName CHAR (50) NOT NULL
);

INSERT INTO Departments
VALUES (01, '444-57-3892', 'Human Resources'),

INSERT INTO Departments
VALUES (02, '666-15-3392', 'Finance'),

INSERT INTO Departments
VALUES (03, '165-35-4892', 'Information Systems'),

INSERT INTO Departments
VALUES (04, '111-10-1029', 'Customer Service'),

INSERT INTO Departments
VALUES (05, '452-72-0123', 'Human Resources'),

Create and Populate the Employees Table


CREATE TABLE Employees
(
SocialSecNum CHAR (11) CONSTRAINT SocID 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 INTO Employees
VALUES ('444-57-3892', 'John', 'Allison', '1400 22nd Ave N Atlanta,
        GA', 98700, 301, '897-1600'),

INSERT INTO Employees
VALUES ('666-15-3392', 'Rosa', 'Coney', '4399 Center Loop Tampa,
        FL', 33677, 813, '898-0001'),

INSERT INTO Employees
VALUES ('165-35-4892', 'Willie', 'Coney', '3900 35th Ave S. St.
        Pete, FL', 33700, 727, '321-1111'),

INSERT INTO Employees
VALUES ('111-10-1029', 'Tanya', 'Levin', '2001 40th Ave S Honolulu,
        HI', 96822, 808, '423-5671'),

INSERT INTO Employees
VALUES ('452-72-0123', 'Yolanda', 'Cole', '9021 Peachtree St N
        Tampa, FL', 33622, 813, '827-4411'),

Create and Populate the Friends Table


CREATE TABLE Friends
(
FriendsID NUMBER CONSTRAINT FrdID 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,
Email CHAR (20) NULL
);

INSERT INTO Friends
VALUES (1, 'John', 'Hill', '2322 3rd Ave S Atlanta, GA', 98753, 301,
        '822-1600', '[email protected]'),

INSERT INTO Friends
VALUES (2, 'Gina', 'Jones', '7123 Kendle Rd Tampa, FL', 33673, 813,
        '811-0001', NULL);

INSERT INTO Friends
VALUES (3, 'Timothy', 'Jones', '1000 6th Ave N. St. Pete, FL',
        33700, 727, '366-1111', '[email protected]'),

INSERT INTO Friends
VALUES (4, 'Reginald', 'Coney', '3210 7th Ave E Honolulu, HI',
        96111, 808, '423-0022', NULL);

INSERT INTO Friends
VALUES (5, 'Otis', 'Rivers', '2400 Ferry Rd N Tampa, FL', 33623,
        813, '321-1432', '[email protected]'),

Create and Populate the Manufacturers Table


CREATE TABLE Manufacturers
(
ManufacturerID INTEGER CONSTRAINT ManfID PRIMARY KEY,
ToyID INTEGER NOT NULL,
CompanyName CHAR (50) NOT NULL,
Address CHAR (50) NOT NULL,
City CHAR (20) NOT NULL,
State CHAR (2) NOT NULL,
PostalCode CHAR (5) NOT NULL,
AreaCode CHAR (3) NOT NULL,
PhoneNumber CHAR (8) NOT NULL UNIQUE,
CONSTRAINT ToyFk FOREIGN KEY (ToyID) REFERENCES Toys (ToyID)
);

INSERT INTO Manufacturers (ManufacturerID, ToyID, CompanyName,
       Address, City, State, PostalCode, AreaCode, PhoneNumber)
VALUES (1, 1, 'Matel', '2892 23rd Ave S', 'St. Petersburg', 'FL',
        33710, 727, '324-5421'),

INSERT INTO Manufacturers (ManufacturerID, ToyID, CompanyName,
       Address, City, State, PostalCode, AreaCode, PhoneNumber)
VALUES (2, 2, 'Jurnes', '1231 Lindsay Ave N', 'Tampa', 'FL', 33618,
        813, '234-3982'),

INSERT INTO Manufacturers (ManufacturerID, ToyID, CompanyName,
       Address, City, State, PostalCode, AreaCode, PhoneNumber)
VALUES (3, 3, 'Radae', '1872 3rd Ave N', 'Baltimore', 'MD', 21210,
        240, '713-0011'),

INSERT INTO Manufacturers (ManufacturerID, ToyID, CompanyName,
       Address, City, State, PostalCode, AreaCode, PhoneNumber)
VALUES (4, 4, 'Winnies', '6000 16th Ave N', 'San Diego', 'CA',
        92101, 213, '981-8745'),

INSERT INTO Manufacturers (ManufacturerID, ToyID, CompanyName,
       Address, City, State, PostalCode, AreaCode, PhoneNumber)
VALUES (5, 5, 'Lenar', '1230 9th Ave N', 'Baltimore', 'MD', 21202,
        301, '321-0987'),

Create and Populate the Numbers Table


CREATE TABLE Numbers
(
ColumnOne INTEGER NOT NULL,
ColumnTwo INTEGER NOT NULL,
ColumnThree INTEGER NOT NULL
);

INSERT INTO Numbers
VALUES (5, 2, 98);

INSERT INTO Numbers
VALUES (1, 8, 11);

INSERT INTO Numbers
VALUES (10, 1, 22);

INSERT INTO Numbers
VALUES (90, 6, 12);

INSERT INTO Numbers
VALUES (40, 27, 6);

INSERT INTO Numbers
VALUES (90, 7, 4);

INSERT INTO Numbers
VALUES (70, 43, 3);

INSERT INTO Numbers
VALUES (70, 61, 144);

Create and Populate the Products Table


CREATE TABLE Products
(
ProductID CHAR (7) NOT NULL PRIMARY KEY,
ProductName CHAR (50) NOT NULL,
Price MONEY NOT NULL,
SalePrice MONEY NOT NULL,
InStock INTEGER NOT NULL,
OnOrder INTEGER NOT NULL
);

INSERT INTO Products
VALUES ('VR300', 'China Doll', 20.00, 13.00, 100, 0);

INSERT INTO Products
VALUES ('CT200', 'China Puppy', 15.00, 13.50, 20, 40);

INSERT INTO Products
VALUES ('ET100', 'Wooden Clock', 11.00, 9.90, 100, 0);

INSERT INTO Products
VALUES ('PO200', 'Glass Rabbit', 50.00, 45.00, 50, 20);

INSERT INTO Products
VALUES ('TH100', 'Crystal Cat', 75.00, 67.50, 60, 20);

INSERT INTO Products
VALUES ('RX300', 'Praying Statue', 25.00, 22.50, 3, 40);

INSERT INTO Products
VALUES ('CE300', 'Miniature Train Set', 60.00, 54.00, 1, 30);

INSERT INTO Products
VALUES ('OT100', 'Dancing Bird', 10.00, 9.00, 10, 20);

INSERT INTO Products
VALUES ('LF300', 'Friendly Lion', 14.00, 12.60, 0, 30);

INSERT INTO Products
VALUES ('BN200', 'Animated Rainbow', 20.00, 18.00, 10, 20);

INSERT INTO Products
VALUES ('AN200', 'Animated Picture', 20.00, 18.00, 10, 20);

Create and Populate the Sales Table


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

INSERT INTO Sales
VALUES (1, 'BN200', 2, '3/3/08'),

INSERT INTO Sales
VALUES (2, 'CT200', 3, '2/5/08'),

INSERT INTO Sales
VALUES (3, 'ET100', 5, '2/6/07'),

INSERT INTO Sales
VALUES (4, 'PO200', 1, '7/8/08'),

INSERT INTO Sales
VALUES (5, 'TH100', 3, '2/8/08'),

INSERT INTO Sales
VALUES (6, 'RX300', 4, '2/10/07'),

INSERT INTO Sales
VALUES (7, 'CT200', 2, '2/22/08'),

INSERT INTO Sales
VALUES (8, 'ET100', 6, '2/20/08'),

INSERT INTO Sales
VALUES (9, 'LF300', 6, '2/18/08'),

INSERT INTO Sales
VALUES (10, 'BN200', 1, '2/17/08'),

Create and Populate the Tools Table


CREATE TABLE Tools
(
ToolID NUMBER CONSTRAINT ToolIDPk PRIMARY KEY,
ToolName CHAR (40) NOT NULL,
Manufacturer CHAR (40) NOT NULL,
Type CHAR (40) NOT NULL,
Location CHAR (40) NOT NULL,
Price MONEY NOT NULL
);

INSERT INTO Tools
VALUES (1, 'Jigsaw', 'Dewalt', 'Power Tool', 'A', 60.00);

INSERT INTO Tools
VALUES (2, 'Hand Drill', 'Dewalt', 'Power Tool', 'A', 30.00);

INSERT INTO Tools
VALUES (3, 'Router', 'Dewalt', 'Power Tool', 'A', 40.00);

INSERT INTO Tools
VALUES (4, 'Nail Gun', 'Bosch', 'Power Tool', 'A', 60.00);

INSERT INTO Tools
VALUES (5, 'Sandpaper', 'Bosch', 'Sanding', 'B', 4.00);

INSERT INTO Tools
VALUES (6, 'Scrapers', 'Bosch', 'Sanding', 'B', 8.00);

INSERT INTO Tools
VALUES (7, 'Hammer', 'Makita', 'Hand Tool', 'C', 14.00);

INSERT INTO Tools
VALUES (8, 'Pliers', 'Porter', 'Hand Tool', 'C', 9.00);

INSERT INTO Tools
VALUES (9, 'Screwdriver', 'Makita', 'Hand Tool', 'C', 4.00);

INSERT INTO Tools
VALUES (10, 'Tool Belt', 'Porter', 'Accessories', 'D', 15.00);

INSERT INTO Tools
VALUES (11, 'Battery Charger', 'Dewalt', 'Accessories', 'D', 20.00);

Create and Populate the Toys Table


CREATE TABLE Toys
(
ToyID INTEGER CONSTRAINT ToyPk PRIMARY KEY,
ToyName CHAR (30) NOT NULL,
Price MONEY NOT NULL,
Description CHAR (40) NULL
);

INSERT INTO Toys (ToyID, ToyName, Price, Description)
VALUES (1, 'ToyTrain1', 11.00, 'Red/blue battery powered train'),

INSERT INTO Toys (ToyID, ToyName, Price, Description)
VALUES (2, 'ToyTrain2', 11.00, 'Green/red/blue battery powered
        train'),

INSERT INTO Toys (ToyID, ToyName, Price, Description)
VALUES (3, 'ElectricTrain', 15.00, 'Red/white AC/DC powered train'),

INSERT INTO Toys (ToyID, ToyName, Price, Description)
VALUES (4, 'LivingDoll1', 12.00, 'Asian American Doll'),

INSERT INTO Toys (ToyID, ToyName, Price, Description)
VALUES (5, 'LivingDoll2', 12.00, 'African American Doll'),

INSERT INTO Toys (ToyID, ToyName, Price, Description)
VALUES (6, 'DollHouse', 17.00, 'Grand Town House'),

INSERT INTO Toys (ToyID, ToyName, Price, Description)
VALUES (7, 'Doll/TownHouse', 15.00, 'Town House'),

Create and Populate the Transactions Table


CREATE TABLE Transactions
(
TransactionID INTEGER NOT NULL PRIMARY KEY,
ProductID CHAR (7) NOT NULL,
CustomerID INTEGER NOT NULL,
DateSold DATETIME NOT NULL
);

INSERT INTO Transactions
VALUES (1, 'VR300', 2, '2/3/08'),

INSERT INTO Transactions
VALUES (2, 'CT200', 2, '2/5/08'),

INSERT INTO Transactions
VALUES (3, 'ET100', 5, '2/6/08'),

INSERT INTO Transactions
VALUES (4, 'PO200', 1, '2/8/08'),

INSERT INTO Transactions
VALUES (5, 'TH100', 3, '2/8/08'),

INSERT INTO Transactions
VALUES (6, 'RX300', 4, '2/10/08'),

INSERT INTO Transactions
VALUES (7, 'CE300', 2, '2/22/08'),

INSERT INTO Transactions
VALUES (8, 'OT100', 6, '2/20/08'),

INSERT INTO Transactions
VALUES (9, 'LF300', 6, '2/18/08'),

INSERT INTO Transactions
VALUES (10, 'BN200', 1, '2/17/08'),
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset