Appendix B. Sample Table Scripts


As you follow each lesson in this book, you are strongly encouraged to try out each example on your own MySQL server.

The tables used by the examples throughout the book are created using the following SQL script. The script contains CREATE TABLE statements that define each table and some INSERT statements to populate the tables with their initial values.

Sample Database

You can download the SQL file printed here from the Sams Publishing website at http://www.samspublishing.com.


 DROP TABLE IF EXISTS customers; CREATE TABLE customers (   customer_code VARCHAR(10)    NOT NULL,   name          VARCHAR(40)    NOT NULL ); INSERT INTO customers (customer_code, name) VALUES ('PRESINC', 'Presidents Incorporated'); INSERT INTO customers (customer_code, name) VALUES ('SCICORP', 'Science Corporation'); INSERT INTO customers (customer_code, name) VALUES ('MUSGRP', 'Musicians of America'); DROP TABLE IF EXISTS customer_contacts; CREATE TABLE customer_contacts (   contact_id    INT             PRIMARY KEY AUTO_INCREMENT,   customer_code VARCHAR(10)     NOT NULL,   first_name    VARCHAR(30)     NOT NULL,   last_name     VARCHAR(30)     NOT NULL,   email         TEXT,   telephone     TEXT ); INSERT INTO customer_contacts   (customer_code, first_name, last_name, email) VALUES ('PRESINC', 'Abraham', 'Lincoln', 'lincoln@presidentsinc.com'); INSERT INTO customer_contacts   (customer_code, first_name, last_name, email) VALUES ('PRESINC', 'Richard', 'Nixon', 'nixon@presidentsinc.com'); INSERT INTO customer_contacts   (customer_code, first_name, last_name, email) VALUES ('PRESINC', 'Franklin', 'Roosevelt', 'fdr@presidentsinc.com'); INSERT INTO customer_contacts   (customer_code, first_name, last_name, email) VALUES ('PRESINC', 'Theodore', 'Roosevelt', 'roosevelt@presidentsinc.com'); INSERT INTO customer_contacts   (customer_code, first_name, last_name, email) VALUES ('SCICORP', 'Albert', 'Einstein', 'einstein@sciencecorp.com'); INSERT INTO customer_contacts   (customer_code, first_name, last_name, email) VALUES ('SCICORP', 'Charles', 'Darwin', 'darwin@sciencecorp.com'); INSERT INTO customer_contacts   (customer_code, first_name, last_name, email) VALUES ('SCICORP', 'Marie', 'Curie', 'curie@sciencecorp.com'); INSERT INTO customer_contacts   (customer_code, first_name, last_name, email) VALUES ('SCICORP', 'Benjamin', 'Franklin', 'franklin@sciencecorp.com'); INSERT INTO customer_contacts   (customer_code, first_name, last_name, email) VALUES ('MUSGRP', 'George', 'Gershwin', 'hawking@musgrp.com'); INSERT INTO customer_contacts   (customer_code, first_name, last_name, email) VALUES ('MUSGRP', 'Benjamin', 'Britten', 'britten@musgrp.com'); INSERT INTO customer_contacts   (customer_code, first_name, last_name, email) VALUES ('MUSGRP', 'John', 'Lennon', 'lennon@musgrp.com'); DROP TABLE IF EXISTS orders; CREATE TABLE orders (   order_id      INT          PRIMARY KEY AUTO_INCREMENT,   customer_code VARCHAR(10)  NOT NULL,   order_date    DATE         NOT NULL ); DROP TABLE IF EXISTS order_lines; CREATE TABLE order_lines (   line_id       INT          PRIMARY KEY AUTO_INCREMENT,   order_id      INT          NOT NULL,   product_code  VARCHAR(10)  NOT NULL,   quantity   TINYINT         NOT NULL ); INSERT INTO orders (order_id, customer_code, order_date) VALUES (1, 'PRESINC', '2006-01-23');   INSERT INTO order_lines (order_id, product_code, quantity)   VALUES (1, 'MINI', 4);   INSERT INTO order_lines (order_id, product_code, quantity)   VALUES (1, 'MAXI', 2); INSERT INTO orders (order_id, customer_code, order_date) VALUES (2, 'PRESINC', '2006-01-26');   INSERT INTO order_lines (order_id, product_code, quantity)   VALUES (2, 'MAXI', 12); INSERT INTO orders (order_id, customer_code, order_date) VALUES (3, 'SCICORP', '2006-01-23');   INSERT INTO order_lines (order_id, product_code, quantity)   VALUES (3, 'MINI', 16); INSERT INTO orders (order_id, customer_code, order_date) VALUES (4, 'SCICORP', '2006-02-02');   INSERT INTO order_lines (order_id, product_code, quantity)   VALUES (4, 'MINI', 16);   INSERT INTO order_lines (order_id, product_code, quantity)   VALUES (4, 'MAXI', 10); INSERT INTO orders (order_id, customer_code, order_date) VALUES (5, 'SCICORP', '2006-02-05');   INSERT INTO order_lines (order_id, product_code, quantity)   VALUES (5, 'MIDI', 10);   INSERT INTO order_lines (order_id, product_code, quantity)   VALUES (5, 'MAXI', 10); INSERT INTO orders (order_id, customer_code, order_date) VALUES (6, 'MUSGRP', '2006-02-01');   INSERT INTO order_lines (order_id, product_code, quantity)   VALUES (6, 'MAXI', 6); INSERT INTO orders (order_id, customer_code, order_date) VALUES (7, 'MUSGRP', '2006-02-02');   INSERT INTO order_lines (order_id, product_code, quantity)   VALUES (7, 'MAXI', 8); DROP TABLE IF EXISTS t1; CREATE TABLE t1 (   id INT PRIMARY KEY,   letter VARCHAR(1) NOT NULL ); INSERT INTO t1 (id, letter) VALUES (1, 'A'); INSERT INTO t1 (id, letter) VALUES (2, 'B'); INSERT INTO t1 (id, letter) VALUES (3, 'C'); DROP TABLE IF EXISTS t2; CREATE TABLE t2 (   id INT PRIMARY KEY,   letter VARCHAR(1) NOT NULL ); INSERT INTO t2 (id, letter) VALUES (1, 'X'); INSERT INTO t2 (id, letter) VALUES (2, 'Y'); INSERT INTO t2 (id, letter) VALUES (3, 'Z'); 





Sams Teach Yourself MySQL in 10 Minutes
Sams Teach Yourself MySQL in 10 Minutes
ISBN: 0672328631
EAN: 2147483647
Year: 2006
Pages: 165
Authors: Chris Newman

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net