The following scripts are used to create, populate, and alter the tables belonging to the applications in this book, as described in Appendix B.
The samples are presented in the form of scripts, SQL statements saved as a source or text file using the file extension .sql. These files are available at the web site for this book: http://www.sitepoint.com/books/sql1/. Each script file is meant to be executed, and you can do this quite simply in the command line, or by importing the script into your front-end application. Also included in the code archive are scripts containing the SQL statements discussed in the book. Each script also contains commentary relating to its purpose and position in the book.
Note: TIMESTAMP and DATETIME
All the scripts in this chapter use the TIMESTAMP data type to hold date and time values. If your database system does not support this data type, you should use the DATETIME data type instead and change the scripts accordingly.
Note: Row Constructors
All the scripts in this appendix that INSERT rows into a new table use row constructors to add them in one step. If your database of choice does not support row constructors, you'll need to insert each row individually, as shown in Chapter 1.
The Teams and Games application uses three entities: teams, games, and conferences. They’re represented in two tables called teams and games.
The Teams table has three columns and three rows, and is first met in Chapter 1. Here’s the DDL to create the table:
CREATE TABLE teams
(
id INTEGER NOT NULL PRIMARY KEY
, name VARCHAR(37) NOT NULL
, conference CHAR(2) NOT NULL
CHECK ( conference IN ( 'AA','A','B','C','D','E','F','G' ) )
);
Note that the CHECK constraint appearing above is discussed in Chapter 9. If your database lacks support for CHECK constraints, you’ll need to delete it. To populate the teams table we run the following:
INSERT INTO teams ( conference , id, name ) VALUES ( 'F' , 9 , 'Riff Raff' ) , ( 'F' , 37 , 'Havoc' ) , ( 'C' , 63 , 'Brewers' )
Later in Chapter 11, the teams table is repopulated:
The games table has four columns and two rows, and is first met in Chapter 11:
CREATE TABLE games ( gamedate DATETIME NOT NULL , location VARCHAR(37) NOT NULL , hometeam INTEGER NOT NULL , awayteam INTEGER NOT NULL ); INSERT INTO games ( gamedate , location , hometeam , awayteam ) VALUES ( '2008-09-06' , 'McKenzie' , 9 , 24 ) , ( '2008-09-13' , 'Applewood' , 15 , 9 ) ;
The Content Management System application uses six entities: entries, categories, comments, contents, entrykeywords, and keywords. They’re represented in five tables, with entrykeywords and keywords being wrapped up into one table called entrykeywords. A view called entries_with_category is also created for demonstration purposes.
The entries table has six columns and five rows. It’s first introduced in Chapter 2:
CREATE TABLE entries
(
id INTEGER NOT NULL PRIMARY KEY
, title VARCHAR(99) NOT NULL
, created TIMESTAMP NOT NULL
, updated TIMESTAMP NULL
, category VARCHAR(37) NULL
, content TEXT NULL
);
INSERT INTO entries
(id, title, created, updated, category)
VALUES
(423,'What If I Get Sick and Die?',
'2008-12-30','2009-03-11','angst')
, (524,'Uncle Karl and the Gasoline',
'2009-02-28',NULL,'humor'),
, (537,'Be Nice to Everybody',
'2009-03-02',NULL,'advice'),
, (573,'Hello Statue',
'2009-03-17',NULL,'humor'),
, (598,'The Size of Our Galaxy',
'2009-04-03',NULL,'science')
;
A second script provides the extended content for one of the entries:
UPDATE
entries
SET
content =
'When I was about nine or ten, my Uncle Karl, who would''ve
been in his late teens or early twenties, once performed
what to me seemed like a magic trick.
Using a rubber hose, which he snaked down into the gas tank
of my father''s car, he siphoned some gasoline into his
mouth, lit a match, held it up a few inches in front of his
face, and then, with explosive force, sprayed the gasoline
out towards the lit match.
Of course, a huge fireball erupted, much to the delight of
the kids watching. I don''t recall if he did it more than
once.
The funny part of this story? We lived to tell it.
Karl was like that.'
WHERE
id = 524
A sixth row is added to the table to demonstrate right outer joins in Chapter 3:
INSERT INTO entries (id, title, created, updated, category) VALUES (605,'Windows Media Center Rocks','2009-04-29',NULL,'computers')
Once the contents table has been created, the content column in the entries table is removed:
Finally, in Chapter 11, the table receives an index and foreign key (if you're using MySQL, you'll need to index the foreign key first):
The categories table has two columns and initially five rows. It’s first seen in Chapter 3 and then altered in Chapter 11:
CREATE TABLE categories ( category VARCHAR(9) NOT NULL PRIMARY KEY, name VARCHAR(37) NOT NULL ); INSERT INTO categories ( category, name ) VALUES ( 'blog' , 'Log on to My Blog' ) , ( 'humor' , 'Humorous Anecdotes' ) , ( 'angst' , 'Stories from the Id' ) , ( 'advice' , 'Gentle Words of Advice' ) , ( 'science' , 'Our Spectacular Universe' ) ;
Later, in Chapter 11, a sixth category is added:
INSERT INTO categories ( category, name ) VALUES ( 'computers' , 'Information Technology' )
Finally, a new column, index, and foreign key are added to the table to demonstrate how a table can be joined to itself. (Again, if you're using MySQL, you'll need to index the foreign key first.) This creates a pseudo-hierarchy of items stored within the table:
ALTER TABLE categories
ADD COLUMN
parent VARCHAR(9) NULL
;
ALTER TABLE categories
ADD INDEX
parent_ix (parent)
;
ALTER TABLE categories
ADD CONSTRAINT
parent_fk
FOREIGN KEY (parent)
REFERENCES categories (category)
;
INSERT INTO categories
( category, name )
VALUES
( 'general' , 'Articles and Resources' )
, ( 'personal' , 'Personal Stories and Ideas' )
;
UPDATE
categories
SET
parent = 'general'
WHERE
category in ( 'computers', 'science' )
;
UPDATE
categories
SET
parent = 'personal'
WHERE
category in ( 'advice', 'angst', 'blog', 'humor' )
;
The entries_with_category view is created in Chapter 3. The script to create it will only work in versions of MySQL from version 5.0.1:
The contents table has two columns and one row initially, copied from the entries table. It’s first mentioned in Chapter 5. Note that you must create the entries table and populate it before running this script:
CREATE TABLE contents ( entry_id INTEGER NOT NULL PRIMARY KEY , content TEXT NOT NULL ); INSERT INTO contents ( entry_id , content ) SELECT id , content FROM entries WHERE NOT ( content IS NULL ) ;
Once the contents table has been created and populated, the contents column of the entries table is no longer needed:
The comments table has six columns and three rows. It’s first met in Chapter 5:
CREATE TABLE comments
(
entry_id INTEGER NOT NULL
, username VARCHAR(37) NOT NULL
, created TIMESTAMP NOT NULL
, PRIMARY KEY ( entry_id, username, created )
, revised TIMESTAMP NULL
, comment TEXT NOT NULL
);
INSERT INTO comments
( entry_id, username, created, revised, comment )
VALUES
( 524, 'Steve0', '2009-03-05', NULL ,
'Sounds like fun. Must try that.')
, ( 524, 'r937' , '2009-03-06', NULL ,
'I tasted gasoline once. Not worth the discomfort.')
, ( 524, 'J4s0n' , '2009-03-16','2009-03-17',
'You and your uncle are both idiots.')
;
The entrykeywords table has two columns and seven rows. It’s first seen in Chapter 11:
CREATE TABLE entrykeywords
(
entry_id INTEGER NOT NULL
, keyword VARCHAR(99) NOT NULL
, PRIMARY KEY ( entry_id, keyword )
, CONSTRAINT entry_fk
FOREIGN KEY (entry_id)
REFERENCES entries (id)
);
INSERT INTO entrykeywords
( entry_id, keyword )
VALUES
(524,'family')
, (524,'reckless')
, (537,'my three rules')
, (537,'family')
, (598,'astronomy')
, (605,'windows')
, (605,'television')
;
The Discussion Forums application, used in Chapter 7, has four entities: forums, members, threads, and posts; they’re represented in four tables of the same name. All four are created in the same script, but we’ve separated them here for clarity.
First, the forums table, which has two columns and three rows:
Now the threads table—it has four columns and four rows:
CREATE TABLE threads
(
id INTEGER NOT NULL PRIMARY KEY
, name VARCHAR(99) NOT NULL
, forum_id INTEGER NOT NULL
, starter INTEGER NOT NULL
, CONSTRAINT thread_name_uk
UNIQUE ( id, name )
);
INSERT INTO threads
( id, name, forum_id, starter )
VALUES
( 15 , 'Difficulty with join query', 10002 , 187 )
, ( 25 , 'How do I get listed in Yahoo?', 10001 , 9 )
, ( 35 , 'People who bought ... also bought ...' , 10002 , 99 )
, ( 45 , 'WHERE clause doesn''t work', 10002 , 187 )
;
Finally, the posts table. Note that you should remove the DEFAULT and CHECK constraints if your database does not support them. It has eight columns and seven rows, each of which has been added in its own INSERT statement for clarity:
CREATE TABLE posts
(
id INTEGER NOT NULL PRIMARY KEY
, name VARCHAR(99) NULL
, thread_id INTEGER NOT NULL
, reply_to INTEGER NULL
, posted_by INTEGER NOT NULL
, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
, revised TIMESTAMP NULL CHECK ( revised >= created )
, post TEXT NOT NULL
);
INSERT INTO posts
( id, name, thread_id, reply_to,
posted_by, created, revised, post )
VALUES
( 201 , 'Difficulty with join query' , 15, NULL , 187 ,
'2008-11-12 11:12:13', NULL, 'I''m having a lot of trouble
joining my tables. What''s a foreign key?' )
;
INSERT INTO posts
( id, name, thread_id, reply_to,
posted_by, created, revised, post )
VALUES
( 215 , 'How do I get listed in Yahoo?', 25, NULL , 9 ,
'2008-11-15 11:20:02', NULL, 'I''ve figured out how to submit
my URL to Google, but I can''t seem to find where to post it
on Yahoo! Can anyone help?' )
;
INSERT INTO posts
( id, name, thread_id, reply_to,
posted_by, created, revised, post )
VALUES
( 216 , NULL , 25, 215 , 42 , '2008-11-15 11:37:10', NULL,
'Try http://search.yahoo.com/info/submit.html ' )
;
INSERT INTO posts
( id, name, thread_id, reply_to,
posted_by, created, revised, post )
VALUES
( 218 , 'That''s it!' , 25, 216 , 9 , '2008-11-15 11:42:24',
NULL, 'That''s it! How did you find it?' )
;
INSERT INTO posts
( id, name, thread_id, reply_to,
posted_by, created, revised, post )
VALUES
( 219 , NULL , 25, 218 , 42 , '2008-11-15 11:51:45',
'2008-11-15 11:57:57', 'There''s a link at the bottom of the
homepage called "Suggest a site"' )
;
INSERT INTO posts
( id, name, thread_id, reply_to,
posted_by, created, revised, post )
VALUES
( 222 , 'People who bought ... also bought ...' , 35, NULL ,
99 , '2008-11-22 22:22:22', NULL, 'For each item in the
user''s cart, I want to show other items that people
bought who bought that item, but the SQL is too hairy
for me. HELP!' )
;
INSERT INTO posts
( id, name, thread_id, reply_to,
posted_by, created, revised, post )
VALUES
( 230 , 'WHERE clause doesn''t work' , 45, NULL , 187 ,
'2008-12-04 09:37:00', NULL, 'My query has WHERE
startdate > 2009-01-01 but I get 0 results, even though
I know there are rows for next year!' )
;
The Shopping Carts application uses four tables to represent customers, items, shopping carts, and cartitems, the items in each cart: customers, carts, cartitems, and items. All of them are introduced in Chapter 4. A fifth table, vendors, is introduced in Chapter 10 to represent those who are selling the items.
The items table has four columns and eighteen rows:
CREATE TABLE items ( id INTEGER NOT NULL PRIMARY KEY , name VARCHAR(21) NOT NULL , type VARCHAR(7) NOT NULL , price DECIMAL(5,2) NULL ); INSERT INTO items ( id, name, type, price ) VALUES (5021,'thingie' ,'widgets', 9.37 ) , (5022,'gadget' ,'doodads', 19.37 ) , (5023,'dingus' ,'gizmos' , 29.37 ) , (5041,'gewgaw' ,'widgets', 5.00 ) , (5042,'knickknack' ,'doodads', 10.00 ) , (5043,'whatnot' ,'gizmos' , 15.00 ) , (5061,'bric-a-brac' ,'widgets', 2.00 ) , (5062,'folderol' ,'doodads', 4.00 ) , (5063,'jigger' ,'gizmos' , 6.00 ) , (5901,'doohickey' ,'widgets', 12.00 ) , (5902,'gimmick' ,'doodads', 9.37 ) , (5903,'dingbat' ,'gizmos' , 9.37 ) , (5911,'thingamajig' ,'widgets', NULL ) , (5912,'thingamabob' ,'doodads', NULL ) , (5913,'thingum' ,'gizmos' , NULL ) , (5931,'contraption' ,'widgets', 49.95 ) , (5932,'whatchamacallit','doodads', 59.95 ) , (5937,'whatsis' ,'gizmos' , NULL ) ;
In Chapter 4, before we experiment with AND and OR in the WHERE clause, we have to supply prices to the items that have a price of NULL:
UPDATE items SET price = 22.22 WHERE name IN ( 'thingamajig', 'thingamabob', 'thingum' ) ; UPDATE items SET price = 93.70 WHERE name = 'whatsis' ;
In Chapter 10, the vendors table is created, and then a foreign key column and reference to that table is added to the items table linking items to their vendors.
ALTER TABLE items
ADD COLUMN
vendor_id INTEGER NULL
;
ALTER TABLE items
ADD INDEX
vendor_ix (vendor_id)
;
ALTER TABLE items
ADD CONSTRAINT
vendor_fk
FOREIGN KEY (vendor_id)
REFERENCES vendors (id)
ON DELETE CASCADE
ON UPDATE CASCADE
;
UPDATE
items
SET
vendor_id = 17
WHERE
name < 't'
;
UPDATE
items
SET
vendor_id = 19
WHERE
name > 'w'
;
INSERT INTO items
( id, name, type, price, vendor_id )
VALUES
( 9901, 'gift wrapping', 'service', 5.00, NULL )
, ( 9902, 'discount coupon', 'service', -10.00, NULL )
;
The customers table has four columns and eight rows. Note that the first seven rows use the default value of the shipaddr column, but the eighth uses its own and so is added using its own INSERT statement:
CREATE TABLE customers
(
id INTEGER NOT NULL PRIMARY KEY
, name VARCHAR(99) NOT NULL
, billaddr VARCHAR(255) NOT NULL
, shipaddr VARCHAR(255) NOT NULL DEFAULT 'See billing address.'
);
INSERT INTO customers
(id, name, billaddr)
VALUES
(710,'A. Jones','123 Sesame St., Eureka, KS')
, (730,'B. Smith','456 Sesame St., Eureka, KS')
, (750,'C. Brown','789 Sesame St., Eureka, KS')
, (770,'D. White','246 Sesame St., Eureka, KS')
, (820,'E. Baker','135 Sesame St., Eureka, KS')
, (840,'F. Black','468 Sesame St., Eureka, KS')
, (860,'G. Scott','357 Sesame St., Eureka, KS')
;
INSERT INTO customers
(id, name, billaddr, shipaddr)
VALUES
(880,'H. Clark',
'937 Sesame St., Eureka, KS', 'P.O. Box 9, Toledo, OH' )
;
The carts table has three columns and ten rows:
CREATE TABLE carts ( id INTEGER NOT NULL PRIMARY KEY , customer_id INTEGER NOT NULL , cartdate TIMESTAMP NOT NULL ); INSERT INTO carts (id, customer_id, cartdate) VALUES (2131,710,'2008-09-03 00:00:00') , (2461,820,'2008-09-16 00:00:00') , (2921,730,'2008-09-19 00:00:00') , (2937,750,'2008-09-21 00:00:00') , (3001,750,'2008-09-23 00:00:00') , (3002,730,'2008-10-07 00:00:00') , (3081,880,'2008-10-13 00:00:00') , (3197,770,'2008-10-14 00:00:00') , (3321,860,'2008-10-26 00:00:00') , (3937,750,'2008-10-28 00:00:00') ;
The cartitems table has three columns and fifteen rows. Note that you should remove the DEFAULT and CHECK constraints if your database does not support them:
CREATE TABLE cartitems ( cart_id INTEGER NOT NULL , item_id INTEGER NOT NULL , qty SMALLINT NOT NULL DEFAULT 1 CHECK ( qty <= 10 ) ); INSERT INTO cartitems (cart_id, item_id, qty) VALUES (2131,5902,3) , (2131,5913,2) , (2461,5043,3) , (2461,5901,2) , (2921,5023,3) , (2921,5937,2) , (2937,5913,1) , (3001,5912,3) , (3001,5937,2) , (3002,5901,1) , (3081,5023,3) , (3081,5913,2) , (3197,5932,1) , (3321,5932,3) , (3937,5913,3) ;
This script creates the vendors table. It has two columns and two rows. We never get around to doing much with the vendors table, other than pointing out that items don’t have to belong to a vendor.