Bradley Shedd
12/14/12
SQL Report
CREATE TABLE
passes (
first VARCHAR (25) NOT NULL,
last VARCHAR(25) NOT NULL,
street VARCHAR (25) NOT NULL,
city VARCHAR (15) NOT NULL,
zip CHAR (5) NOT NULL,
phone NUMERIC (10),
email VARCHAR (25) NOT NULL
CHECK (email LIKE ‘%@%.%’),
validuntil VARCHAR (10) NOT NULL
CHECK (validuntil LIKE ‘%/%/%’),
plu CHAR (8)
DEFAULT ‘ADAP2011’ CHECK (plu =
‘ADAP2011’ OR ‘CHAP2011’ OR ‘SNAP2011’),
);
CREATE TABLE orders (
ordnum NUMERIC (3),
quan NUMERIC (1),
plu CHAR (8)
DEFAULT ‘ADAP2011’ CHECK (plu =
‘ADAP2011’ OR ‘CHAP2011’ OR ‘SNAP2011’),
price NUMERIC (6) NOT NULL CHECK (price LIKE ‘$%%.%%’),
orderdate VARCHAR (10) NOT NULL CHECK (orderdate LIKE ‘%/%/%’),
);
CREATE TABLE contact (
name VARCHAR (40) NOT NULL,
street VARCHAR (25) NOT NULL,
city VARCHAR (15) NOT NULL,
zip CHAR (5) NOT NULL,
phone NUMERIC (10),
email VARCHAR (25) NOT NULL
CHECK (email LIKE ‘%@%.%’),
);
INSERT INTO passes (first, last, street, city, zip, phone, email,
validuntil, plu)
VALUES (‘Joe’, ‘Bishop’,
‘123 Brook Avenue’, 30252, 1234567890, jbishop@aol.com, 12/12/12, ‘CHAP2011’);
INSERT INTO passes (first, last, street, city, zip, phone, email,
validuntil, plu)
VALUES (‘Barak’, ‘Obama’,
‘1 White House Road’, 55555, 1234528934, bobama@prez.com, 05/11/12, ‘ADAP2011’);
INSERT INTO passes (first, last, street, city, zip, phone, email,
validuntil, plu)
VALUES (‘Homer’, ‘Iliad’,
‘222 Ulysses Street’, 35552, 2893097890, homerilliad@novels.com, 01/12/12,
‘SNAP2011’);
INSERT INTO passes (first, last, street, city, zip, phone, email,
validuntil, plu)
VALUES (‘Joey’,
‘Kangaroo’, ‘414 Outback Avenue’, 33352, 4444567890, jkang@out.com, 04/11/12,
‘CHAP2011’);
INSERT INTO passes (first, last, street, city, zip, phone, email,
validuntil, plu)
VALUES (‘Chip’, ‘Douglas’,
’33 Aspen Road’, 32252, 1229847290, cdoug@bellsouth.com, 10/21/12, ‘ADAP2011’);
INSERT INTO passes (first, last, street, city, zip, phone, email,
validuntil, plu)
VALUES (‘Jim’, ‘Kirk’, ‘1
Enterprise Lane’, 00001, 1228301390, jkirk@space.com, 09/13/12, ‘ADAP2011’);
INSERT INTO passes (first, last, street, city, zip, phone, email,
validuntil, plu)
VALUES (‘Joseph’,
‘Castle’, ’10 Noah Avenue’, 76452, 1927347890, jcastle@jj.com, 11/09/12,
‘SNAP2011’);
INSERT INTO passes (first, last, street, city, zip, phone, email,
validuntil, plu)
VALUES (‘John’, ‘Bosh’,
‘777 Lucky Avenue’, 36548, 0927384017, jbosh@aol.com, 12/12/12, ‘CHAP2011’);
INSERT INTO passes (first, last, street, city, zip, phone, email,
validuntil, plu)
VALUES (‘Bear’, ‘Grills’,
‘123 Eat Fish Road’, 30388, 9093567890, bgrills@tnt.com, 10/16/12, ‘ADAP2011’);
INSERT INTO passes (first, last, street, city, zip, phone, email,
validuntil, plu)
VALUES (‘Rambo’,
‘Commando’, ‘888 Jungle Lane’, 34322, 1239027490, rcommando@fx.com, 11/19/12,
‘SNAP2011’);
INSERT INTO passes (first, last, street, city, zip, phone, email,
validuntil, plu)
VALUES (‘Peter’,
‘Griffen’, ’12 Cartoon Road’, 30024, 1292847890, pgriffen@aol.com, 12/12/12,
‘ADAP2011’);
INSERT INTO passes (first, last, street, city, zip, phone, email,
validuntil, plu)
VALUES (‘Bryan’,
‘Griffen’, ’76 Tail Spin Road’, 30442, 1232237890, bgriffen@tbs.com, 09/23/12,
‘ADAP2011’);
INSERT INTO passes (first, last, street, city, zip, phone, email,
validuntil, plu)
VALUES (‘Jeff’, ‘Wess’,
‘55 Short Street’, 30255, 1232257890, jwess@aol.com, 12/12/12, ‘SNAP2011’);
INSERT INTO passes (first, last, street, city, zip, phone, email,
validuntil, plu)
VALUES (‘Jake’, ‘Snake’,
‘303 Wrestle Lane’, 00011, 0293841846, jsnake@aol.com, 10/21/12, ‘SNAP2011’);
INSERT INTO passes (first, last, street, city, zip, phone, email,
validuntil, plu)
VALUES (‘John’, ‘Cena’, ‘5
No View Street’, 00011, 0384756389, jcena@wwe.com, 11/22/12, ‘ADAP2011’);
INSERT INTO passes (first, last, street, city, zip, phone, email,
validuntil, plu)
VALUES (‘Jake’, ‘Roberts’,
’44 Wrestle Lane’, 33344, 9837264984, jroberts@wwe.com, 12/12/12, ‘ADAP2011’);
INSERT INTO passes (first, last, street, city, zip, phone, email,
validuntil, plu)
VALUES (‘Jim’, ‘Fields’,
’55 Sesame Street’, 12345, 1234567890, jfields@sesame.com, 09/24/12,
‘SNAP2011’);
INSERT INTO passes (first, last, street, city, zip, phone, email,
validuntil, plu)
VALUES (‘Joe’, ‘James’,
‘’, 30252, 1235983029, jjames@comcast.com, 06/21/12, ‘CHAP2011’);
INSERT INTO passes (first, last, street, city, zip, phone, email,
validuntil, plu)
VALUES (‘Peter’, ‘Tyrone’,
’65 South Street’, 30161, 7067665555, ptyrone@aol.com, 03/03/12, ‘CHAP2011’);
INSERT INTO passes (first, last, street, city, zip, phone, email,
validuntil, plu)
VALUES (‘John’, ‘Wayne’,
‘5 Wild Wild West Road’, 30909, 0993234765, jwayne@tombstone.com, 05/05/12,
‘ADAP2011’);
INSERT INTO passes (first, last, street, city, zip, phone, email,
validuntil, plu)
VALUES (‘Bruce’, ‘Lee’,
‘501 China Town Street’, 30252, 5556754242, blee@pages.com, 07/20/12,
‘ADAP2011’);
INSERT INTO passes (first, last, street, city, zip, phone, email,
validuntil, plu)
VALUES (‘Peter’, ‘Parker’,
‘5 Daily Planet’, 30505, 6365867833, spiderman@web.com, 02/03/12, ‘CHAP2011’);
INSERT INTO passes (first, last, street, city, zip, phone, email,
validuntil, plu)
VALUES (‘Bob’, ‘Brawny’,
‘Rock Avenue’, 30161, 7061238272, bbrawny@comcast.com, 08/21/12, ‘SNAP2011’);
INSERT INTO passes (first, last, street, city, zip, phone, email,
validuntil, plu)
VALUES (‘Jeff’, ‘Smith’,
‘333 Broad Street’, 30001, 1234999999, jsmith@aol.com, 12/12/12, ‘SNAP2011’);
INSERT INTO passes (first, last, street, city, zip, phone, email,
validuntil, plu)
VALUES (‘Blake’,
‘McWhorts’, ‘314 Cubicle Circle’, 30252, 1234567890, bmcwhorts@comcast.com,
12/12/12, ‘ADAP2011’);
INSERT INTO passes (first, last, street, city, zip, phone, email,
validuntil, plu)
VALUES (‘Bradley’,
‘Smith’, ‘225 Bakersville Road’, 30060, 4041234321, bsmith@bellsouth.com,
09/01/12, ‘CHAP2011’);
INSERT INTO passes (first, last, street, city, zip, phone, email,
validuntil, plu)
VALUES (‘Bruce’, ‘Wayne’,
‘123 Wayne Manor’, 30252, 4045433456, batman@arkham.com, 02/10/12, ‘SNAP2011’);
INSERT INTO passes (first, last, street, city, zip, phone, email,
validuntil, plu)
VALUES (‘Dirty’, ‘Bird’,
‘3 Georgia Dome Street’, 30313, 4045555555, dbird@atlanta.com, 09/15/12,
‘ADAP2011’);
INSERT INTO passes (first, last, street, city, zip, phone, email,
validuntil, plu)
VALUES (‘Rome’, ‘Romey’,
‘Mutual Stadium Street’, 30165, 7062346543, rromey@gntc.edu, 10/10/12,
‘SNAP2011’);
INSERT INTO passes (first, last, street, city, zip, phone, email,
validuntil, plu)
Zippy’, ‘Cardinal’, ‘555
Acorn Street’, 30163, 5555555555, zcardinal@gmail.com, 11/11/12, ‘CHAP2011’);
INSERT INTO orders (ordernum, quan, plu, price, orderdate)
VALUES (100, 7, CHAP2011,
$59.95, 12/07/11);
INSERT INTO orders (ordernum, quan, plu, price, orderdate)
VALUES (101, 8, ADAP2011,
$79.95, 12/07/11);
INSERT INTO orders (ordernum, quan, plu, price, orderdate)
VALUES (102, 6, SNAP2011,
$69.95, 12/07/11);
INSERT INTO orders (ordernum, quan, plu, price, orderdate)
VALUES (103, 5, ADAP2011,
$79.95, 12/07/11);
INSERT INTO orders (ordernum, quan, plu, price, orderdate)
VALUES (104, 5, ADAP2011,
$79.95, 12/07/11);
INSERT INTO orders (ordernum, quan, plu, price, orderdate)
VALUES (105, 3, ADAP2011,
$79.95, 12/08/11);
INSERT INTO orders (ordernum, quan, plu, price, orderdate)
VALUES (106, 4, CHAP2011,
$59.95, 12/08/11);
INSERT INTO orders (ordernum, quan, plu, price, orderdate)
VALUES (107, 7, ADAP2011,
$79.95, 12/08/11);
INSERT INTO orders (ordernum, quan, plu, price, orderdate)
VALUES (108, 2, SNAP2011,
$69.95, 12/08/11);
INSERT INTO orders (ordernum, quan, plu, price, orderdate)
VALUES (109, 5, CHAP2011,
$59.95, 12/09/11);
INSERT INTO orders (ordernum, quan, plu, price, orderdate)
VALUES (110, 1, ADAP2011,
$79.95, 12/09/11);
INSERT INTO orders (ordernum, quan, plu, price, orderdate)
VALUES (111, 6, ADAP2011,
$79.95, 12/10/11);
INSERT INTO orders (ordernum, quan, plu, price, orderdate)
VALUES (112, 4, SNAP2011,
$69.95, 12/10/11);
INSERT INTO orders (ordernum, quan, plu, price, orderdate)
VALUES (113, 9, CHAP2011,
$59.95, 12/10/11);
INSERT INTO orders (ordernum, quan, plu, price, orderdate)
VALUES (114, 9, CHAP2011,
$59.95, 12/10/11);
INSERT INTO orders (ordernum, quan, plu, price, orderdate)
VALUES (115, 8, ADAP2011,
$79.95, 12/11/11);
INSERT INTO orders (ordernum, quan, plu, price, orderdate)
VALUES (116, 4, SNAP2011,
$69.95, 12/11/11);
INSERT INTO orders (ordernum, quan, plu, price, orderdate)
VALUES (117, 7, ADAP2011,
$79.95, 12/11/11);
INSERT INTO orders (ordernum, quan, plu, price, orderdate)
VALUES (118, 1, ADAP2011,
$79.95, 12/11/11);
INSERT INTO orders (ordernum, quan, plu, price, orderdate)
VALUES (119, 5, SNAP2011,
$69.95, 12/12/11);
INSERT INTO orders (ordernum, quan, plu, price, orderdate)
VALUES (120, 3, CHAP2011,
$59.95, 12/12/11);
INSERT INTO orders (ordernum, quan, plu, price, orderdate)
VALUES (121, 2, ADAP2011,
$79.95, 12/12/11);
INSERT INTO contacts (name, city, street, zip, phone, email)
VALUES (‘Bob Grills’,
‘Rome’, ‘333 Broad Street’, 1234567890, bgrills@rome.com);
INSERT INTO contacts (name, city, street, zip, phone, email)
VALUES (‘Adam Hall’,
‘Atlanta’, ‘232 Lucky Street’, 1234283940, ahall@atlanta.com);
INSERT INTO contacts (name, city, street, zip, phone, email)
VALUES (‘Adam Erikson’,
‘Rome’, ‘333 Taco Street’, 1902357890, aerikson@rome.com);
INSERT INTO contacts (name, city, street, zip, phone, email)
VALUES (‘Aj McStay’,
‘Atlanta’, ‘342 Ponce deLeon Street’, 1189203790, amcstay@atlanta.com);
INSERT INTO contacts (name, city, street, zip, phone, email)
VALUES (‘Ah Ron Choi’,
‘Athens’, ’15 Milledge Avenue’, 9093567890, achoy@uga.edu);
INSERT INTO contacts (name, city, street, zip, phone, email)
VALUES (‘Al Gonzales’,
‘Rome’, ‘333 Lawaway Street’, 1289764390, agonzales@rome.com);
INSERT INTO contacts (name, city, street, zip, phone, email)
VALUES (‘Alan Clark’,
‘Walker’, ’76 Football Road’, 1234892390, aclark@spsu.edu);
INSERT INTO contacts (name, city, street, zip, phone, email)
VALUES (‘Alyssa Slovinac’,
‘Savannah’, ’43 Tybee Beach Road’, 8084567890, aslovinac@savannah.com);
INSERT INTO contacts (name, city, street, zip, phone, email)
VALUES (‘Amad Abdellatif’,
‘New Orleans’, ’35 Workout Way’, 7066567890, aabdellatif@health.com);
INSERT INTO contacts (name, city, street, zip, phone, email)
VALUES (‘Brandon Hindmon’,
‘New York’, ‘1 New Baby Street’, 7064558890, bhindmon@prosper.com);
INSERT INTO contacts (name, city, street, zip, phone, email)
VALUES (‘Branon Hight’,
‘Las Vegas’, ‘2 New Baby Street’, 7065557990, bhight@living.com);
INSERT INTO contacts (name, city, street, zip, phone, email)
VALUES (‘Brent Beard’,
‘Rome’, ‘3 Race Way Road’, 7064558720, bbeard@race.com);
INSERT INTO contacts (name, city, street, zip, phone, email)
VALUES (‘Caleb Moore’,
‘Houston’, ‘101 Marine Road’, 9909067890, cmoore@shootemup.com);
INSERT INTO contacts (name, city, street, zip, phone, email)
VALUES (‘Candice Harwell’,
‘Sugar Land’, ’55 Singit Road’, 2219023790, bgrills@rome.com);
INSERT INTO contacts (name, city, street, zip, phone, email)
VALUES (‘Casey Harris’,
‘Marietta’, ‘333 Cobb Parkway’, 7704549890, charris@life.edu);
INSERT INTO contacts (name, city, street, zip, phone, email)
VALUES (‘Caty Pendley’,
‘Alpharetta’, ’55 Lazy Lane’, 4044541890, cpendley@alpha.com);
INSERT INTO contacts (name, city, street, zip, phone, email)
VALUES (‘Chad Johnson’,
‘Rome’, ‘187 Cop Patrol Road’, 7064598590, cjohnson@rome.com);
INSERT INTO contacts (name, city, street, zip, phone, email)
VALUES (‘Charlene Talley’,
‘Acworth’, ’57 Heinz Street’, 3334097890, ctalley@spsu.edu);
INSERT INTO contacts (name, city, street, zip, phone, email)
VALUES (‘Cher Agan’,
‘Rome’, ‘333 Miss Model’, 2244467890, cagan@rome.com);
INSERT INTO contacts (name, city, street, zip, phone, email)
VALUES (‘Chris Chambers’,
‘Rome’, ‘122 Broad Street’, 4444567890, cchambers@rome.com);