5mJQtMCy

1-- scratch database
2-- mysql version
3-- as of 2014-02-24 bw
4
5CREATE DATABASE IF NOT EXISTS scratch DEFAULT CHARSET = utf8;
6USE scratch;
7
8DROP TABLE IF EXISTS customer;
9CREATE TABLE customer (
10    id              INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, 
11    name            VARCHAR(255),
12    address         VARCHAR(255),
13    city            VARCHAR(255),
14    state           CHAR(2),
15    zip             CHAR(10)
16) ENGINE=InnoDB DEFAULT CHARSET=utf8;
17
18DROP TABLE IF EXISTS item;
19CREATE TABLE item (
20    id              INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
21    name            VARCHAR(255),
22    description     TEXT
23) ENGINE=InnoDB DEFAULT CHARSET=utf8;
24
25DROP TABLE IF EXISTS sale;
26CREATE TABLE sale (
27    id              INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
28    item_id         INTEGER,
29    customer_id     INTEGER,
30    date            DATE,
31    quantity        INTEGER,
32    price           DECIMAL(9,2)
33) ENGINE=InnoDB DEFAULT CHARSET=utf8;
34
35INSERT INTO customer ( id, name, address, city, state, zip ) VALUES ( 1, 'Bill Smith', '123 Main Street', 'Hope', 'CA', '98765' );
36INSERT INTO customer ( id, name, address, city, state, zip ) VALUES ( 2, 'Mary Smith', '123 Dorian Street', 'Harmony', 'AZ', '98765' );
37INSERT INTO customer ( id, name, address, city, state, zip ) VALUES ( 3, 'Bob Smith', '123 Laugh Street', 'Humor', 'CA', '98765' );
38
39INSERT INTO item ( id, name, description ) VALUES ( 1, 'Box of 64 Pixels', '64 RGB pixels in a decorative box' );
40INSERT INTO item ( id, name, description ) VALUES ( 2, 'Sense of Humor', 'Especially dry. Imported from England.' );
41INSERT INTO item ( id, name, description ) VALUES ( 3, 'Beauty', 'Inner beauty. No cosmetic surgery required!' );
42INSERT INTO item ( id, name, description ) VALUES ( 4, 'Bar Code', 'Unused. In original packaging.' );
43
44INSERT INTO sale ( id, item_id, customer_id, date, quantity, price ) VALUES ( 1, 1, 2, '2009-02-27', 3, 29.95 );
45INSERT INTO sale ( id, item_id, customer_id, date, quantity, price ) VALUES ( 2, 2, 2, '2009-02-27', 1, 19.95 );
46INSERT INTO sale ( id, item_id, customer_id, date, quantity, price ) VALUES ( 3, 1, 1, '2009-02-28', 1, 29.95 );
47INSERT INTO sale ( id, item_id, customer_id, date, quantity, price ) VALUES ( 4, 4, 3, '2009-02-28', 2, 9.99 );
48INSERT INTO sale ( id, item_id, customer_id, date, quantity, price ) VALUES ( 5, 1, 2, '2009-02-28', 1, 29.95 );
49
50DROP TABLE IF EXISTS numerics;
51CREATE TABLE numerics (
52    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
53    da DECIMAL(10, 2),
54    db DECIMAL(10, 2),
55    fa FLOAT,
56    fb FLOAT
57) ENGINE=InnoDB DEFAULT CHARSET=utf8;
58
59INSERT INTO numerics (da, db, fa, fb) VALUES ( 0.1, 0.2, 0.1, 0.2 );