· 8 years ago · Jul 31, 2018, 01:38 PM
1-- *****************************************************
2-- This script creates all 3 databases (AP, EX, and OM)
3-- for Murach's MySQL by Joel Murach
4-- *****************************************************
5
6-- ********************************************
7-- CREATE THE AP DATABASE
8-- *******************************************
9
10-- create the database
11DROP DATABASE IF EXISTS ap;
12CREATE DATABASE ap;
13
14-- select the database
15USE ap;
16
17-- create the tables
18CREATE TABLE general_ledger_accounts
19(
20 account_number INT PRIMARY KEY,
21 account_description VARCHAR(50) UNIQUE
22);
23
24CREATE TABLE terms
25(
26 terms_id INT PRIMARY KEY AUTO_INCREMENT,
27 terms_description VARCHAR(50) NOT NULL,
28 terms_due_days INT NOT NULL
29);
30
31CREATE TABLE vendors
32(
33 vendor_id INT PRIMARY KEY AUTO_INCREMENT,
34 vendor_name VARCHAR(50) NOT NULL UNIQUE,
35 vendor_address1 VARCHAR(50),
36 vendor_address2 VARCHAR(50),
37 vendor_city VARCHAR(50) NOT NULL,
38 vendor_state CHAR(2) NOT NULL,
39 vendor_zip_code VARCHAR(20) NOT NULL,
40 vendor_phone VARCHAR(50),
41 vendor_contact_last_name VARCHAR(50),
42 vendor_contact_first_name VARCHAR(50),
43 default_terms_id INT NOT NULL,
44 default_account_number INT NOT NULL,
45 CONSTRAINT vendors_fk_terms
46 FOREIGN KEY (default_terms_id)
47 REFERENCES terms (terms_id),
48 CONSTRAINT vendors_fk_accounts
49 FOREIGN KEY (default_account_number)
50 REFERENCES general_ledger_accounts (account_number)
51);
52
53CREATE TABLE invoices
54(
55 invoice_id INT PRIMARY KEY AUTO_INCREMENT,
56 vendor_id INT NOT NULL,
57 invoice_number VARCHAR(50) NOT NULL,
58 invoice_date DATE NOT NULL,
59 invoice_total DECIMAL(9,2) NOT NULL,
60 payment_total DECIMAL(9,2) NOT NULL DEFAULT 0,
61 credit_total DECIMAL(9,2) NOT NULL DEFAULT 0,
62 terms_id INT NOT NULL,
63 invoice_due_date DATE NOT NULL,
64 payment_date DATE,
65 CONSTRAINT invoices_fk_vendors
66 FOREIGN KEY (vendor_id)
67 REFERENCES vendors (vendor_id),
68 CONSTRAINT invoices_fk_terms
69 FOREIGN KEY (terms_id)
70 REFERENCES terms (terms_id)
71);
72
73CREATE TABLE invoice_line_items
74(
75 invoice_id INT NOT NULL,
76 invoice_sequence INT NOT NULL,
77 account_number INT NOT NULL,
78 line_item_amount DECIMAL(9,2) NOT NULL,
79 line_item_description VARCHAR(100) NOT NULL,
80 CONSTRAINT line_items_pk
81 PRIMARY KEY (invoice_id, invoice_sequence),
82 CONSTRAINT line_items_fk_invoices
83 FOREIGN KEY (invoice_id)
84 REFERENCES invoices (invoice_id),
85 CONSTRAINT line_items_fk_acounts
86 FOREIGN KEY (account_number)
87 REFERENCES general_ledger_accounts (account_number)
88);
89
90-- create the indexes
91CREATE INDEX invoices_invoice_date_ix
92 ON invoices (invoice_date DESC);
93
94-- create some test tables that aren't explicitly
95-- related to the previous five tables
96CREATE TABLE vendor_contacts
97(
98 vendor_id INT PRIMARY KEY,
99 last_name VARCHAR(50) NOT NULL,
100 first_name VARCHAR(50) NOT NULL
101);
102
103CREATE TABLE invoice_archive
104(
105 invoice_id INT NOT NULL,
106 vendor_id INT NOT NULL,
107 invoice_number VARCHAR(50) NOT NULL,
108 invoice_date DATE NOT NULL,
109 invoice_total DECIMAL(9,2) NOT NULL,
110 payment_total DECIMAL(9,2) NOT NULL,
111 credit_total DECIMAL(9,2) NOT NULL,
112 terms_id INT NOT NULL,
113 invoice_due_date DATE NOT NULL,
114 payment_date DATE
115);
116
117-- insert rows into the tables
118INSERT INTO general_ledger_accounts VALUES
119(100,'Cash'),
120(110,'Accounts Receivable'),
121(120,'Book Inventory'),
122(150,'Furniture'),
123(160,'Computer Equipment'),
124(162,'Capitalized Lease'),
125(167,'Software'),
126(170,'Other Equipment'),
127(181,'Book Development'),
128(200,'Accounts Payable'),
129(205,'Royalties Payable'),
130(221,'401K Employee Contributions'),
131(230,'Sales Taxes Payable'),
132(234,'Medicare Taxes Payable'),
133(235,'Income Taxes Payable'),
134(237,'State Payroll Taxes Payable'),
135(238,'Employee FICA Taxes Payable'),
136(239,'Employer FICA Taxes Payable'),
137(241,'Employer FUTA Taxes Payable'),
138(242,'Employee SDI Taxes Payable'),
139(243,'Employer UCI Taxes Payable'),
140(251,'IBM Credit Corporation Payable'),
141(280,'Capital Stock'),
142(290,'Retained Earnings'),
143(300,'Retail Sales'),
144(301,'College Sales'),
145(302,'Trade Sales'),
146(306,'Consignment Sales'),
147(310,'Compositing Revenue'),
148(394,'Book Club Royalties'),
149(400,'Book Printing Costs'),
150(403,'Book Production Costs'),
151(500,'Salaries and Wages'),
152(505,'FICA'),
153(506,'FUTA'),
154(507,'UCI'),
155(508,'Medicare'),
156(510,'Group Insurance'),
157(520,'Building Lease'),
158(521,'Utilities'),
159(522,'Telephone'),
160(523,'Building Maintenance'),
161(527,'Computer Equipment Maintenance'),
162(528,'IBM Lease'),
163(532,'Equipment Rental'),
164(536,'Card Deck Advertising'),
165(540,'Direct Mail Advertising'),
166(541,'Space Advertising'),
167(546,'Exhibits and Shows'),
168(548,'Web Site Production and Fees'),
169(550,'Packaging Materials'),
170(551,'Business Forms'),
171(552,'Postage'),
172(553,'Freight'),
173(555,'Collection Agency Fees'),
174(556,'Credit Card Handling'),
175(565,'Bank Fees'),
176(568,'Auto License Fee'),
177(569,'Auto Expense'),
178(570,'Office Supplies'),
179(572,'Books, Dues, and Subscriptions'),
180(574,'Business Licenses and Taxes'),
181(576,'PC Software'),
182(580,'Meals'),
183(582,'Travel and Accomodations'),
184(589,'Outside Services'),
185(590,'Business Insurance'),
186(591,'Accounting'),
187(610,'Charitable Contributions'),
188(611,'Profit Sharing Contributions'),
189(620,'Interest Paid to Banks'),
190(621,'Other Interest'),
191(630,'Federal Corporation Income Taxes'),
192(631,'State Corporation Income Taxes'),
193(632,'Sales Tax');
194
195INSERT INTO terms VALUES
196(1,'Net due 10 days',10),
197(2,'Net due 20 days',20),
198(3,'Net due 30 days',30),
199(4,'Net due 60 days',60),
200(5,'Net due 90 days',90);
201
202INSERT INTO vendors VALUES
203(1,'US Postal Service','Attn: Supt. Window Services','PO Box 7005','Madison','WI','53707','(800) 555-1205','Alberto','Francesco',1,552),
204(2,'National Information Data Ctr','PO Box 96621',NULL,'Washington','DC','20090','(301) 555-8950','Irvin','Ania',3,540),
205(3,'Register of Copyrights','Library Of Congress',NULL,'Washington','DC','20559',NULL,'Liana','Lukas',3,403),
206(4,'Jobtrak','1990 Westwood Blvd Ste 260',NULL,'Los Angeles','CA','90025','(800) 555-8725','Quinn','Kenzie',3,572),
207(5,'Newbrige Book Clubs','3000 Cindel Drive',NULL,'Washington','NJ','07882','(800) 555-9980','Marks','Michelle',4,394),
208(6,'California Chamber Of Commerce','3255 Ramos Cir',NULL,'Sacramento','CA','95827','(916) 555-6670','Mauro','Anton',3,572),
209(7,'Towne Advertiser''s Mailing Svcs','Kevin Minder','3441 W Macarthur Blvd','Santa Ana','CA','92704',NULL,'Maegen','Ted',3,540),
210(8,'BFI Industries','PO Box 9369',NULL,'Fresno','CA','93792','(559) 555-1551','Kaleigh','Erick',3,521),
211(9,'Pacific Gas & Electric','Box 52001',NULL,'San Francisco','CA','94152','(800) 555-6081','Anthoni','Kaitlyn',3,521),
212(10,'Robbins Mobile Lock And Key','4669 N Fresno',NULL,'Fresno','CA','93726','(559) 555-9375','Leigh','Bill',2,523),
213(11,'Bill Marvin Electric Inc','4583 E Home',NULL,'Fresno','CA','93703','(559) 555-5106','Hostlery','Kaitlin',2,523),
214(12,'City Of Fresno','PO Box 2069',NULL,'Fresno','CA','93718','(559) 555-9999','Mayte','Kendall',3,574),
215(13,'Golden Eagle Insurance Co','PO Box 85826',NULL,'San Diego','CA','92186',NULL,'Blanca','Korah',3,590),
216(14,'Expedata Inc','4420 N. First Street, Suite 108',NULL,'Fresno','CA','93726','(559) 555-9586','Quintin','Marvin',3,589),
217(15,'ASC Signs','1528 N Sierra Vista',NULL,'Fresno','CA','93703',NULL,'Darien','Elisabeth',1,546),
218(16,'Internal Revenue Service',NULL,NULL,'Fresno','CA','93888',NULL,'Aileen','Joan',1,235),
219(17,'Blanchard & Johnson Associates','27371 Valderas',NULL,'Mission Viejo','CA','92691','(214) 555-3647','Keeton','Gonzalo',3,540),
220(18,'Fresno Photoengraving Company','1952 "H" Street','P.O. Box 1952','Fresno','CA','93718','(559) 555-3005','Chaddick','Derek',3,403),
221(19,'Crown Printing','1730 "H" St',NULL,'Fresno','CA','93721','(559) 555-7473','Randrup','Leann',2,400),
222(20,'Diversified Printing & Pub','2632 Saturn St',NULL,'Brea','CA','92621','(714) 555-4541','Lane','Vanesa',3,400),
223(21,'The Library Ltd','7700 Forsyth',NULL,'St Louis','MO','63105','(314) 555-8834','Marques','Malia',3,540),
224(22,'Micro Center','1555 W Lane Ave',NULL,'Columbus','OH','43221','(614) 555-4435','Evan','Emily',2,160),
225(23,'Yale Industrial Trucks-Fresno','3711 W Franklin',NULL,'Fresno','CA','93706','(559) 555-2993','Alexis','Alexandro',3,532),
226(24,'Zee Medical Service Co','4221 W Sierra Madre #104',NULL,'Washington','IA','52353',NULL,'Hallie','Juliana',3,570),
227(25,'California Data Marketing','2818 E Hamilton',NULL,'Fresno','CA','93721','(559) 555-3801','Jonessen','Moises',4,540),
228(26,'Small Press','121 E Front St - 4th Floor',NULL,'Traverse City','MI','49684',NULL,'Colette','Dusty',3,540),
229(27,'Rich Advertising','12 Daniel Road',NULL,'Fairfield','NJ','07004','(201) 555-9742','Neil','Ingrid',3,540),
230(29,'Vision Envelope & Printing','PO Box 3100',NULL,'Gardena','CA','90247','(310) 555-7062','Raven','Jamari',3,551),
231(30,'Costco','Fresno Warehouse','4500 W Shaw','Fresno','CA','93711',NULL,'Jaquan','Aaron',3,570),
232(31,'Enterprise Communications Inc','1483 Chain Bridge Rd, Ste 202',NULL,'Mclean','VA','22101','(770) 555-9558','Lawrence','Eileen',2,536),
233(32,'RR Bowker','PO Box 31',NULL,'East Brunswick','NJ','08810','(800) 555-8110','Essence','Marjorie',3,532),
234(33,'Nielson','Ohio Valley Litho Division','Location #0470','Cincinnati','OH','45264',NULL,'Brooklynn','Keely',2,541),
235(34,'IBM','PO Box 61000',NULL,'San Francisco','CA','94161','(800) 555-4426','Camron','Trentin',1,160),
236(35,'Cal State Termite','PO Box 956',NULL,'Selma','CA','93662','(559) 555-1534','Hunter','Demetrius',2,523),
237(36,'Graylift','PO Box 2808',NULL,'Fresno','CA','93745','(559) 555-6621','Sydney','Deangelo',3,532),
238(37,'Blue Cross','PO Box 9061',NULL,'Oxnard','CA','93031','(800) 555-0912','Eliana','Nikolas',3,510),
239(38,'Venture Communications Int''l','60 Madison Ave',NULL,'New York','NY','10010','(212) 555-4800','Neftaly','Thalia',3,540),
240(39,'Custom Printing Company','PO Box 7028',NULL,'St Louis','MO','63177','(301) 555-1494','Myles','Harley',3,540),
241(40,'Nat Assoc of College Stores','500 East Lorain Street',NULL,'Oberlin','OH','44074',NULL,'Bernard','Lucy',3,572),
242(41,'Shields Design','415 E Olive Ave',NULL,'Fresno','CA','93728','(559) 555-8060','Kerry','Rowan',2,403),
243(42,'Opamp Technical Books','1033 N Sycamore Ave.',NULL,'Los Angeles','CA','90038','(213) 555-4322','Paris','Gideon',3,572),
244(43,'Capital Resource Credit','PO Box 39046',NULL,'Minneapolis','MN','55439','(612) 555-0057','Maxwell','Jayda',3,589),
245(44,'Courier Companies, Inc','PO Box 5317',NULL,'Boston','MA','02206','(508) 555-6351','Antavius','Troy',4,400),
246(45,'Naylor Publications Inc','PO Box 40513',NULL,'Jacksonville','FL','32231','(800) 555-6041','Gerald','Kristofer',3,572),
247(46,'Open Horizons Publishing','Book Marketing Update','PO Box 205','Fairfield','IA','52556','(515) 555-6130','Damien','Deborah',2,540),
248(47,'Baker & Taylor Books','Five Lakepointe Plaza, Ste 500','2709 Water Ridge Parkway','Charlotte','NC','28217','(704) 555-3500','Bernardo','Brittnee',3,572),
249(48,'Fresno County Tax Collector','PO Box 1192',NULL,'Fresno','CA','93715','(559) 555-3482','Brenton','Kila',3,574),
250(49,'Mcgraw Hill Companies','PO Box 87373',NULL,'Chicago','IL','60680','(614) 555-3663','Holbrooke','Rashad',3,572),
251(50,'Publishers Weekly','Box 1979',NULL,'Marion','OH','43305','(800) 555-1669','Carrollton','Priscilla',3,572),
252(51,'Blue Shield of California','PO Box 7021',NULL,'Anaheim','CA','92850','(415) 555-5103','Smith','Kylie',3,510),
253(52,'Aztek Label','Accounts Payable','1150 N Tustin Ave','Anaheim','CA','92807','(714) 555-9000','Griffin','Brian',3,551),
254(53,'Gary McKeighan Insurance','3649 W Beechwood Ave #101',NULL,'Fresno','CA','93711','(559) 555-2420','Jair','Caitlin',3,590),
255(54,'Ph Photographic Services','2384 E Gettysburg',NULL,'Fresno','CA','93726','(559) 555-0765','Cheyenne','Kaylea',3,540),
256(55,'Quality Education Data','PO Box 95857',NULL,'Chicago','IL','60694','(800) 555-5811','Misael','Kayle',2,540),
257(56,'Springhouse Corp','PO Box 7247-7051',NULL,'Philadelphia','PA','19170','(215) 555-8700','Maeve','Clarence',3,523),
258(57,'The Windows Deck','117 W Micheltorena Top Floor',NULL,'Santa Barbara','CA','93101','(800) 555-3353','Wood','Liam',3,536),
259(58,'Fresno Rack & Shelving Inc','4718 N Bendel Ave',NULL,'Fresno','CA','93722',NULL,'Baylee','Dakota',2,523),
260(59,'Publishers Marketing Assoc','627 Aviation Way',NULL,'Manhatttan Beach','CA','90266','(310) 555-2732','Walker','Jovon',3,572),
261(60,'The Mailers Guide Co','PO Box 1550',NULL,'New Rochelle','NY','10802',NULL,'Lacy','Karina',3,540),
262(61,'American Booksellers Assoc','828 S Broadway',NULL,'Tarrytown','NY','10591','(800) 555-0037','Angelica','Nashalie',3,574),
263(62,'Cmg Information Services','PO Box 2283',NULL,'Boston','MA','02107','(508) 555-7000','Randall','Yash',3,540),
264(63,'Lou Gentile''s Flower Basket','722 E Olive Ave',NULL,'Fresno','CA','93728','(559) 555-6643','Anum','Trisha',1,570),
265(64,'Texaco','PO Box 6070',NULL,'Inglewood','CA','90312',NULL,'Oren','Grace',3,582),
266(65,'The Drawing Board','PO Box 4758',NULL,'Carol Stream','IL','60197',NULL,'Mckayla','Jeffery',2,551),
267(66,'Ascom Hasler Mailing Systems','PO Box 895',NULL,'Shelton','CT','06484',NULL,'Lewis','Darnell',3,532),
268(67,'Bill Jones','Secretary Of State','PO Box 944230','Sacramento','CA','94244',NULL,'Deasia','Tristin',3,589),
269(68,'Computer Library','3502 W Greenway #7',NULL,'Phoenix','AZ','85023','(602) 547-0331','Aryn','Leroy',3,540),
270(69,'Frank E Wilber Co','2437 N Sunnyside',NULL,'Fresno','CA','93727','(559) 555-1881','Millerton','Johnathon',3,532),
271(70,'Fresno Credit Bureau','PO Box 942',NULL,'Fresno','CA','93714','(559) 555-7900','Braydon','Anne',2,555),
272(71,'The Fresno Bee','1626 E Street',NULL,'Fresno','CA','93786','(559) 555-4442','Colton','Leah',2,572),
273(72,'Data Reproductions Corp','4545 Glenmeade Lane',NULL,'Auburn Hills','MI','48326','(810) 555-3700','Arodondo','Cesar',3,400),
274(73,'Executive Office Products','353 E Shaw Ave',NULL,'Fresno','CA','93710','(559) 555-1704','Danielson','Rachael',2,570),
275(74,'Leslie Company','PO Box 610',NULL,'Olathe','KS','66061','(800) 255-6210','Alondra','Zev',3,570),
276(75,'Retirement Plan Consultants','6435 North Palm Ave, Ste 101',NULL,'Fresno','CA','93704','(559) 555-7070','Edgardo','Salina',3,589),
277(76,'Simon Direct Inc','4 Cornwall Dr Ste 102',NULL,'East Brunswick','NJ','08816','(908) 555-7222','Bradlee','Daniel',2,540),
278(77,'State Board Of Equalization','PO Box 942808',NULL,'Sacramento','CA','94208','(916) 555-4911','Dean','Julissa',1,631),
279(78,'The Presort Center','1627 "E" Street',NULL,'Fresno','CA','93706','(559) 555-6151','Marissa','Kyle',3,540),
280(79,'Valprint','PO Box 12332',NULL,'Fresno','CA','93777','(559) 555-3112','Warren','Quentin',3,551),
281(80,'Cardinal Business Media, Inc.','P O Box 7247-7844',NULL,'Philadelphia','PA','19170','(215) 555-1500','Eulalia','Kelsey',2,540),
282(81,'Wang Laboratories, Inc.','P.O. Box 21209',NULL,'Pasadena','CA','91185','(800) 555-0344','Kapil','Robert',2,160),
283(82,'Reiter''s Scientific & Pro Books','2021 K Street Nw',NULL,'Washington','DC','20006','(202) 555-5561','Rodolfo','Carlee',2,572),
284(83,'Ingram','PO Box 845361',NULL,'Dallas','TX','75284',NULL,'Yobani','Trey',2,541),
285(84,'Boucher Communications Inc','1300 Virginia Dr. Ste 400',NULL,'Fort Washington','PA','19034','(215) 555-8000','Carson','Julian',3,540),
286(85,'Champion Printing Company','3250 Spring Grove Ave',NULL,'Cincinnati','OH','45225','(800) 555-1957','Clifford','Jillian',3,540),
287(86,'Computerworld','Department #1872','PO Box 61000','San Francisco','CA','94161','(617) 555-0700','Lloyd','Angel',1,572),
288(87,'DMV Renewal','PO Box 942894',NULL,'Sacramento','CA','94294',NULL,'Josey','Lorena',4,568),
289(88,'Edward Data Services','4775 E Miami River Rd',NULL,'Cleves','OH','45002','(513) 555-3043','Helena','Jeanette',1,540),
290(89,'Evans Executone Inc','4918 Taylor Ct',NULL,'Turlock','CA','95380',NULL,'Royce','Hannah',1,522),
291(90,'Wakefield Co','295 W Cromwell Ave Ste 106',NULL,'Fresno','CA','93711','(559) 555-4744','Rothman','Nathanael',2,170),
292(91,'McKesson Water Products','P O Box 7126',NULL,'Pasadena','CA','91109','(800) 555-7009','Destin','Luciano',2,570),
293(92,'Zip Print & Copy Center','PO Box 12332',NULL,'Fresno','CA','93777','(233) 555-6400','Javen','Justin',2,540),
294(93,'AT&T','PO Box 78225',NULL,'Phoenix','AZ','85062',NULL,'Wesley','Alisha',3,522),
295(94,'Abbey Office Furnishings','4150 W Shaw Ave',NULL,'Fresno','CA','93722','(559) 555-8300','Francis','Kyra',2,150),
296(95,'Pacific Bell',NULL,NULL,'Sacramento','CA','95887','(209) 555-7500','Nickalus','Kurt',2,522),
297(96,'Wells Fargo Bank','Business Mastercard','P.O. Box 29479','Phoenix','AZ','85038','(947) 555-3900','Damion','Mikayla',2,160),
298(97,'Compuserve','Dept L-742',NULL,'Columbus','OH','43260','(614) 555-8600','Armando','Jan',2,572),
299(98,'American Express','Box 0001',NULL,'Los Angeles','CA','90096','(800) 555-3344','Story','Kirsten',2,160),
300(99,'Bertelsmann Industry Svcs. Inc','28210 N Avenue Stanford',NULL,'Valencia','CA','91355','(805) 555-0584','Potter','Lance',3,400),
301(100,'Cahners Publishing Company','Citibank Lock Box 4026','8725 W Sahara Zone 1127','The Lake','NV','89163','(301) 555-2162','Jacobsen','Samuel',4,540),
302(101,'California Business Machines','Gallery Plz','5091 N Fresno','Fresno','CA','93710','(559) 555-5570','Rohansen','Anders',2,170),
303(102,'Coffee Break Service','PO Box 1091',NULL,'Fresno','CA','93714','(559) 555-8700','Smitzen','Jeffrey',4,570),
304(103,'Dean Witter Reynolds','9 River Pk Pl E 400',NULL,'Boston','MA','02134','(508) 555-8737','Johnson','Vance',5,589),
305(104,'Digital Dreamworks','5070 N Sixth Ste. 71',NULL,'Fresno','CA','93711',NULL,'Elmert','Ron',3,589),
306(105,'Dristas Groom & McCormick','7112 N Fresno St Ste 200',NULL,'Fresno','CA','93720','(559) 555-8484','Aaronsen','Thom',3,591),
307(106,'Ford Motor Credit Company','Dept 0419',NULL,'Los Angeles','CA','90084','(800) 555-7000','Snyder','Karen',3,582),
308(107,'Franchise Tax Board','PO Box 942857',NULL,'Sacramento','CA','94257',NULL,'Prado','Anita',4,507),
309(108,'Gostanian General Building','427 W Bedford #102',NULL,'Fresno','CA','93711','(559) 555-5100','Bragg','Walter',4,523),
310(109,'Kent H Landsberg Co','File No 72686','PO Box 61000','San Francisco','CA','94160','(916) 555-8100','Stevens','Wendy',3,540),
311(110,'Malloy Lithographing Inc','5411 Jackson Road','PO Box 1124','Ann Arbor','MI','48106','(313) 555-6113','Regging','Abe',3,400),
312(111,'Net Asset, Llc','1315 Van Ness Ave Ste. 103',NULL,'Fresno','CA','93721',NULL,'Kraggin','Laura',1,572),
313(112,'Office Depot','File No 81901',NULL,'Los Angeles','CA','90074','(800) 555-1711','Pinsippi','Val',3,570),
314(113,'Pollstar','4697 W Jacquelyn Ave',NULL,'Fresno','CA','93722','(559) 555-2631','Aranovitch','Robert',5,520),
315(114,'Postmaster','Postage Due Technician','1900 E Street','Fresno','CA','93706','(559) 555-7785','Finklestein','Fyodor',1,552),
316(115,'Roadway Package System, Inc','Dept La 21095',NULL,'Pasadena','CA','91185',NULL,'Smith','Sam',4,553),
317(116,'State of California','Employment Development Dept','PO Box 826276','Sacramento','CA','94230','(209) 555-5132','Articunia','Mercedez',1,631),
318(117,'Suburban Propane','2874 S Cherry Ave',NULL,'Fresno','CA','93706','(559) 555-2770','Spivak','Harold',3,521),
319(118,'Unocal','P.O. Box 860070',NULL,'Pasadena','CA','91186','(415) 555-7600','Bluzinski','Rachael',3,582),
320(119,'Yesmed, Inc','PO Box 2061',NULL,'Fresno','CA','93718','(559) 555-0600','Hernandez','Reba',2,589),
321(120,'Dataforms/West','1617 W. Shaw Avenue','Suite F','Fresno','CA','93711',NULL,'Church','Charlie',3,551),
322(121,'Zylka Design','3467 W Shaw Ave #103',NULL,'Fresno','CA','93711','(559) 555-8625','Ronaldsen','Jaime',3,403),
323(122,'United Parcel Service','P.O. Box 505820',NULL,'Reno','NV','88905','(800) 555-0855','Beauregard','Violet',3,553),
324(123,'Federal Express Corporation','P.O. Box 1140','Dept A','Memphis','TN','38101','(800) 555-4091','Bucket','Charlie',3,553);
325
326INSERT INTO vendor_contacts VALUES
327(5,'Davison','Michelle'),
328(12,'Mayteh','Kendall'),
329(17,'Onandonga','Bruce'),
330(44,'Antavius','Anthony'),
331(76,'Bradlee','Danny'),
332(94,'Suscipe','Reynaldo'),
333(101,'O''Sullivan','Geraldine'),
334(123,'Bucket','Charles');
335
336INSERT INTO invoices VALUES
337(1,122,'989319-457','2011-04-08','3813.33','3813.33','0.00',3,'2011-05-08','2011-05-07'),
338(2,123,'263253241','2011-04-10','40.20','40.20','0.00',3,'2011-05-10','2011-05-14'),
339(3,123,'963253234','2011-04-13','138.75','138.75','0.00',3,'2011-05-13','2011-05-09'),
340(4,123,'2-000-2993','2011-04-16','144.70','144.70','0.00',3,'2011-05-16','2011-05-12'),
341(5,123,'963253251','2011-04-16','15.50','15.50','0.00',3,'2011-05-16','2011-05-11'),
342(6,123,'963253261','2011-04-16','42.75','42.75','0.00',3,'2011-05-16','2011-05-21'),
343(7,123,'963253237','2011-04-21','172.50','172.50','0.00',3,'2011-05-21','2011-05-22'),
344(8,89,'125520-1','2011-04-24','95.00','95.00','0.00',1,'2011-05-04','2011-05-01'),
345(9,121,'97/488','2011-04-24','601.95','601.95','0.00',3,'2011-05-24','2011-05-21'),
346(10,123,'263253250','2011-04-24','42.67','42.67','0.00',3,'2011-05-24','2011-05-22'),
347(11,123,'963253262','2011-04-25','42.50','42.50','0.00',3,'2011-05-25','2011-05-20'),
348(12,96,'I77271-O01','2011-04-26','662.00','662.00','0.00',2,'2011-05-16','2011-05-13'),
349(13,95,'111-92R-10096','2011-04-30','16.33','16.33','0.00',2,'2011-05-20','2011-05-23'),
350(14,115,'25022117','2011-05-01','6.00','6.00','0.00',4,'2011-06-10','2011-06-10'),
351(15,48,'P02-88D77S7','2011-05-03','856.92','856.92','0.00',3,'2011-06-02','2011-05-30'),
352(16,97,'21-4748363','2011-05-03','9.95','9.95','0.00',2,'2011-05-23','2011-05-22'),
353(17,123,'4-321-2596','2011-05-05','10.00','10.00','0.00',3,'2011-06-04','2011-06-05'),
354(18,123,'963253242','2011-05-06','104.00','104.00','0.00',3,'2011-06-05','2011-06-05'),
355(19,34,'QP58872','2011-05-07','116.54','116.54','0.00',1,'2011-05-17','2011-05-19'),
356(20,115,'24863706','2011-05-10','6.00','6.00','0.00',4,'2011-06-19','2011-06-15'),
357(21,119,'10843','2011-05-11','4901.26','4901.26','0.00',2,'2011-05-31','2011-05-29'),
358(22,123,'963253235','2011-05-11','108.25','108.25','0.00',3,'2011-06-10','2011-06-09'),
359(23,97,'21-4923721','2011-05-13','9.95','9.95','0.00',2,'2011-06-02','2011-05-28'),
360(24,113,'77290','2011-05-13','1750.00','1750.00','0.00',5,'2011-07-02','2011-07-05'),
361(25,123,'963253246','2011-05-13','129.00','129.00','0.00',3,'2011-06-12','2011-06-09'),
362(26,123,'4-342-8069','2011-05-14','10.00','10.00','0.00',3,'2011-06-13','2011-06-13'),
363(27,88,'972110','2011-05-15','207.78','207.78','0.00',1,'2011-05-25','2011-05-27'),
364(28,123,'963253263','2011-05-16','109.50','109.50','0.00',3,'2011-06-15','2011-06-10'),
365(29,108,'121897','2011-05-19','450.00','450.00','0.00',4,'2011-06-28','2011-07-03'),
366(30,123,'1-200-5164','2011-05-20','63.40','63.40','0.00',3,'2011-06-19','2011-06-24'),
367(31,104,'P02-3772','2011-05-21','7125.34','7125.34','0.00',3,'2011-06-20','2011-06-24'),
368(32,121,'97/486','2011-05-21','953.10','953.10','0.00',3,'2011-06-20','2011-06-22'),
369(33,105,'94007005','2011-05-23','220.00','220.00','0.00',3,'2011-06-22','2011-06-26'),
370(34,123,'963253232','2011-05-23','127.75','127.75','0.00',3,'2011-06-22','2011-06-18'),
371(35,107,'RTR-72-3662-X','2011-05-25','1600.00','1600.00','0.00',4,'2011-07-04','2011-07-09'),
372(36,121,'97/465','2011-05-25','565.15','565.15','0.00',3,'2011-06-24','2011-06-24'),
373(37,123,'963253260','2011-05-25','36.00','36.00','0.00',3,'2011-06-24','2011-06-26'),
374(38,123,'963253272','2011-05-26','61.50','61.50','0.00',3,'2011-06-25','2011-06-30'),
375(39,110,'0-2058','2011-05-28','37966.19','37966.19','0.00',3,'2011-06-27','2011-06-30'),
376(40,121,'97/503','2011-05-30','639.77','639.77','0.00',3,'2011-06-29','2011-06-25'),
377(41,123,'963253255','2011-05-31','53.75','53.75','0.00',3,'2011-06-30','2011-06-27'),
378(42,123,'94007069','2011-05-31','400.00','400.00','0.00',3,'2011-06-30','2011-07-01'),
379(43,72,'40318','2011-06-01','21842.00','21842.00','0.00',3,'2011-07-01','2011-06-29'),
380(44,95,'111-92R-10094','2011-06-01','19.67','19.67','0.00',2,'2011-06-21','2011-06-24'),
381(45,122,'989319-437','2011-06-01','2765.36','2765.36','0.00',3,'2011-07-01','2011-06-28'),
382(46,37,'547481328','2011-06-03','224.00','224.00','0.00',3,'2011-07-03','2011-07-04'),
383(47,83,'31359783','2011-06-03','1575.00','1575.00','0.00',2,'2011-06-23','2011-06-21'),
384(48,123,'1-202-2978','2011-06-03','33.00','33.00','0.00',3,'2011-07-03','2011-07-05'),
385(49,95,'111-92R-10097','2011-06-04','16.33','16.33','0.00',2,'2011-06-24','2011-06-26'),
386(50,37,'547479217','2011-06-07','116.00','116.00','0.00',3,'2011-07-07','2011-07-07'),
387(51,122,'989319-477','2011-06-08','2184.11','2184.11','0.00',3,'2011-07-08','2011-07-08'),
388(52,34,'Q545443','2011-06-09','1083.58','1083.58','0.00',1,'2011-06-19','2011-06-23'),
389(53,95,'111-92R-10092','2011-06-09','46.21','46.21','0.00',2,'2011-06-29','2011-07-02'),
390(54,121,'97/553B','2011-06-10','313.55','313.55','0.00',3,'2011-07-10','2011-07-09'),
391(55,123,'963253245','2011-06-10','40.75','40.75','0.00',3,'2011-07-10','2011-07-12'),
392(56,86,'367447','2011-06-11','2433.00','2433.00','0.00',1,'2011-06-21','2011-06-17'),
393(57,103,'75C-90227','2011-06-11','1367.50','1367.50','0.00',5,'2011-07-31','2011-07-31'),
394(58,123,'963253256','2011-06-11','53.25','53.25','0.00',3,'2011-07-11','2011-07-07'),
395(59,123,'4-314-3057','2011-06-11','13.75','13.75','0.00',3,'2011-07-11','2011-07-15'),
396(60,122,'989319-497','2011-06-12','2312.20','2312.20','0.00',3,'2011-07-12','2011-07-09'),
397(61,115,'24946731','2011-06-15','25.67','25.67','0.00',4,'2011-07-25','2011-07-26'),
398(62,123,'963253269','2011-06-15','26.75','26.75','0.00',3,'2011-07-15','2011-07-11'),
399(63,122,'989319-427','2011-06-16','2115.81','2115.81','0.00',3,'2011-07-16','2011-07-19'),
400(64,123,'963253267','2011-06-17','23.50','23.50','0.00',3,'2011-07-17','2011-07-19'),
401(65,99,'509786','2011-06-18','6940.25','6940.25','0.00',3,'2011-07-18','2011-07-15'),
402(66,123,'263253253','2011-06-18','31.95','31.95','0.00',3,'2011-07-18','2011-07-21'),
403(67,122,'989319-487','2011-06-20','1927.54','1927.54','0.00',3,'2011-07-20','2011-07-18'),
404(68,81,'MABO1489','2011-06-21','936.93','936.93','0.00',2,'2011-07-11','2011-07-10'),
405(69,80,'133560','2011-06-22','175.00','175.00','0.00',2,'2011-07-12','2011-07-16'),
406(70,115,'24780512','2011-06-22','6.00','6.00','0.00',4,'2011-08-01','2011-07-29'),
407(71,123,'963253254','2011-06-22','108.50','108.50','0.00',3,'2011-07-22','2011-07-20'),
408(72,123,'43966316','2011-06-22','10.00','10.00','0.00',3,'2011-07-22','2011-07-17'),
409(73,114,'CBM9920-M-T77109','2011-06-23','290.00','290.00','0.00',1,'2011-07-03','2011-06-29'),
410(74,102,'109596','2011-06-24','41.80','41.80','0.00',4,'2011-08-03','2011-08-04'),
411(75,123,'7548906-20','2011-06-24','27.00','27.00','0.00',3,'2011-07-24','2011-07-24'),
412(76,123,'963253248','2011-06-24','241.00','241.00','0.00',3,'2011-07-24','2011-07-25'),
413(77,121,'97/553','2011-06-25','904.14','904.14','0.00',3,'2011-07-25','2011-07-25'),
414(78,121,'97/522','2011-06-28','1962.13','1762.13','200.00',3,'2011-07-28','2011-07-30'),
415(79,100,'587056','2011-06-30','2184.50','2184.50','0.00',4,'2011-08-09','2011-08-07'),
416(80,122,'989319-467','2011-07-01','2318.03','2318.03','0.00',3,'2011-07-31','2011-07-29'),
417(81,123,'263253265','2011-07-02','26.25','26.25','0.00',3,'2011-08-01','2011-07-28'),
418(82,94,'203339-13','2011-07-05','17.50','17.50','0.00',2,'2011-07-25','2011-07-27'),
419(83,95,'111-92R-10093','2011-07-06','39.77','39.77','0.00',2,'2011-07-26','2011-07-22'),
420(84,123,'963253258','2011-07-06','111.00','111.00','0.00',3,'2011-08-05','2011-08-05'),
421(85,123,'963253271','2011-07-07','158.00','158.00','0.00',3,'2011-08-06','2011-08-11'),
422(86,123,'963253230','2011-07-07','739.20','739.20','0.00',3,'2011-08-06','2011-08-06'),
423(87,123,'963253244','2011-07-08','60.00','60.00','0.00',3,'2011-08-07','2011-08-09'),
424(88,123,'963253239','2011-07-08','147.25','147.25','0.00',3,'2011-08-07','2011-08-11'),
425(89,72,'39104','2011-07-10','85.31','0.00','0.00',3,'2011-08-09',NULL),
426(90,123,'963253252','2011-07-12','38.75','38.75','0.00',3,'2011-08-11','2011-08-11'),
427(91,95,'111-92R-10095','2011-07-15','32.70','32.70','0.00',2,'2011-08-04','2011-08-06'),
428(92,117,'111897','2011-07-15','16.62','16.62','0.00',3,'2011-08-14','2011-08-14'),
429(93,123,'4-327-7357','2011-07-16','162.75','162.75','0.00',3,'2011-08-15','2011-08-11'),
430(94,123,'963253264','2011-07-18','52.25','0.00','0.00',3,'2011-08-17',NULL),
431(95,82,'C73-24','2011-07-19','600.00','600.00','0.00',2,'2011-08-08','2011-08-13'),
432(96,110,'P-0259','2011-07-19','26881.40','26881.40','0.00',3,'2011-08-18','2011-08-20'),
433(97,90,'97-1024A','2011-07-20','356.48','356.48','0.00',2,'2011-08-09','2011-08-07'),
434(98,83,'31361833','2011-07-21','579.42','0.00','0.00',2,'2011-08-10',NULL),
435(99,123,'263253268','2011-07-21','59.97','0.00','0.00',3,'2011-08-20',NULL),
436(100,123,'263253270','2011-07-22','67.92','0.00','0.00',3,'2011-08-21',NULL),
437(101,123,'263253273','2011-07-22','30.75','0.00','0.00',3,'2011-08-21',NULL),
438(102,110,'P-0608','2011-07-23','20551.18','0.00','1200.00',3,'2011-08-22',NULL),
439(103,122,'989319-417','2011-07-23','2051.59','2051.59','0.00',3,'2011-08-22','2011-08-24'),
440(104,123,'263253243','2011-07-23','44.44','44.44','0.00',3,'2011-08-22','2011-08-24'),
441(105,106,'9982771','2011-07-24','503.20','0.00','0.00',3,'2011-08-23',NULL),
442(106,110,'0-2060','2011-07-24','23517.58','21221.63','2295.95',3,'2011-08-23','2011-08-27'),
443(107,122,'989319-447','2011-07-24','3689.99','3689.99','0.00',3,'2011-08-23','2011-08-19'),
444(108,123,'963253240','2011-07-24','67.00','67.00','0.00',3,'2011-08-23','2011-08-23'),
445(109,121,'97/222','2011-07-25','1000.46','1000.46','0.00',3,'2011-08-24','2011-08-22'),
446(110,80,'134116','2011-07-28','90.36','0.00','0.00',2,'2011-08-17',NULL),
447(111,123,'263253257','2011-07-30','22.57','22.57','0.00',3,'2011-08-29','2011-09-03'),
448(112,110,'0-2436','2011-07-31','10976.06','0.00','0.00',3,'2011-08-30',NULL),
449(113,37,'547480102','2011-08-01','224.00','0.00','0.00',3,'2011-08-31',NULL),
450(114,123,'963253249','2011-08-02','127.75','127.75','0.00',3,'2011-09-01','2011-09-04');
451
452INSERT INTO invoice_line_items VALUES
453(1,1,553,'3813.33','Freight'),
454(2,1,553,'40.20','Freight'),
455(3,1,553,'138.75','Freight'),
456(4,1,553,'144.70','Int\'l shipment'),
457(5,1,553,'15.50','Freight'),
458(6,1,553,'42.75','Freight'),
459(7,1,553,'172.50','Freight'),
460(8,1,522,'95.00','Telephone service'),
461(9,1,403,'601.95','Cover design'),
462(10,1,553,'42.67','Freight'),
463(11,1,553,'42.50','Freight'),
464(12,1,580,'50.00','DiCicco\'s'),
465(12,2,570,'75.60','Kinko\'s'),
466(12,3,570,'58.40','Office Max'),
467(12,4,540,'478.00','Publishers Marketing'),
468(13,1,522,'16.33','Telephone (line 5)'),
469(14,1,553,'6.00','Freight out'),
470(15,1,574,'856.92','Property Taxes'),
471(16,1,572,'9.95','Monthly access fee'),
472(17,1,553,'10.00','Address correction'),
473(18,1,553,'104.00','Freight'),
474(19,1,160,'116.54','MVS Online Library'),
475(20,1,553,'6.00','Freight out'),
476(21,1,589,'4901.26','Office lease'),
477(22,1,553,'108.25','Freight'),
478(23,1,572,'9.95','Monthly access fee'),
479(24,1,520,'1750.00','Warehouse lease'),
480(25,1,553,'129.00','Freight'),
481(26,1,553,'10.00','Freight'),
482(27,1,540,'207.78','Prospect list'),
483(28,1,553,'109.50','Freight'),
484(29,1,523,'450.00','Back office additions'),
485(30,1,553,'63.40','Freight'),
486(31,1,589,'7125.34','Web site design'),
487(32,1,403,'953.10','Crash Course revision'),
488(33,1,591,'220.00','Form 571-L'),
489(34,1,553,'127.75','Freight'),
490(35,1,507,'1600.00','Income Tax'),
491(36,1,403,'565.15','Crash Course Ad'),
492(37,1,553,'36.00','Freight'),
493(38,1,553,'61.50','Freight'),
494(39,1,400,'37966.19','CICS Desk Reference'),
495(40,1,403,'639.77','Card deck'),
496(41,1,553,'53.75','Freight'),
497(42,1,553,'400.00','Freight'),
498(43,1,400,'21842.00','Book repro'),
499(44,1,522,'19.67','Telephone (Line 3)'),
500(45,1,553,'2765.36','Freight'),
501(46,1,510,'224.00','Health Insurance'),
502(47,1,572,'1575.00','Catalog ad'),
503(48,1,553,'33.00','Freight'),
504(49,1,522,'16.33','Telephone (line 6)'),
505(50,1,510,'116.00','Health Insurance'),
506(51,1,553,'2184.11','Freight'),
507(52,1,160,'1083.58','MSDN'),
508(53,1,522,'46.21','Telephone (Line 1)'),
509(54,1,403,'313.55','Card revision'),
510(55,1,553,'40.75','Freight'),
511(56,1,572,'2433.00','Card deck'),
512(57,1,589,'1367.50','401K Contributions'),
513(58,1,553,'53.25','Freight'),
514(59,1,553,'13.75','Freight'),
515(60,1,553,'2312.20','Freight'),
516(61,1,553,'25.67','Freight out'),
517(62,1,553,'26.75','Freight'),
518(63,1,553,'2115.81','Freight'),
519(64,1,553,'23.50','Freight'),
520(65,1,400,'6940.25','OS Utilities'),
521(66,1,553,'31.95','Freight'),
522(67,1,553,'1927.54','Freight'),
523(68,1,160,'936.93','Quarterly Maintenance'),
524(69,1,540,'175.00','Card deck advertising'),
525(70,1,553,'6.00','Freight'),
526(71,1,553,'108.50','Freight'),
527(72,1,553,'10.00','Address correction'),
528(73,1,552,'290.00','International pkg.'),
529(74,1,570,'41.80','Coffee'),
530(75,1,553,'27.00','Freight'),
531(76,1,553,'241.00','Int\'l shipment'),
532(77,1,403,'904.14','Cover design'),
533(78,1,403,'1197.00','Cover design'),
534(78,2,540,'765.13','Catalog design'),
535(79,1,540,'2184.50','PC card deck'),
536(80,1,553,'2318.03','Freight'),
537(81,1,553,'26.25','Freight'),
538(82,1,150,'17.50','Supplies'),
539(83,1,522,'39.77','Telephone (Line 2)'),
540(84,1,553,'111.00','Freight'),
541(85,1,553,'158.00','Int\'l shipment'),
542(86,1,553,'739.20','Freight'),
543(87,1,553,'60.00','Freight'),
544(88,1,553,'147.25','Freight'),
545(89,1,400,'85.31','Book copy'),
546(90,1,553,'38.75','Freight'),
547(91,1,522,'32.70','Telephone (line 4)'),
548(92,1,521,'16.62','Propane-forklift'),
549(93,1,553,'162.75','International shipment'),
550(94,1,553,'52.25','Freight'),
551(95,1,572,'600.00','Books for research'),
552(96,1,400,'26881.40','MVS JCL'),
553(97,1,170,'356.48','Network wiring'),
554(98,1,572,'579.42','Catalog ad'),
555(99,1,553,'59.97','Freight'),
556(100,1,553,'67.92','Freight'),
557(101,1,553,'30.75','Freight'),
558(102,1,400,'20551.18','CICS book printing'),
559(103,1,553,'2051.59','Freight'),
560(104,1,553,'44.44','Freight'),
561(105,1,582,'503.20','Bronco lease'),
562(106,1,400,'23517.58','DB2 book printing'),
563(107,1,553,'3689.99','Freight'),
564(108,1,553,'67.00','Freight'),
565(109,1,403,'1000.46','Crash Course covers'),
566(110,1,540,'90.36','Card deck advertising'),
567(111,1,553,'22.57','Freight'),
568(112,1,400,'10976.06','VSAM book printing'),
569(113,1,510,'224.00','Health Insurance'),
570(114,1,553,'127.75','Freight');
571
572-- ********************************************
573-- CREATE THE EX DATABASE
574-- *******************************************
575
576-- create the database
577DROP DATABASE IF EXISTS ex;
578CREATE DATABASE ex;
579
580-- select the database
581USE ex;
582
583-- example tables for chapter 3
584CREATE TABLE null_sample
585(
586 invoice_id INT NOT NULL,
587 invoice_total DECIMAL(9,2),
588 CONSTRAINT invoice_id_uq
589 UNIQUE (invoice_id)
590);
591
592INSERT INTO null_sample VALUES
593(1,125),
594(2,0),
595(3,null),
596(4,2199.99),
597(5,0);
598
599-- example tables for chapter 4
600CREATE TABLE departments
601(
602 department_number INT NOT NULL,
603 department_name VARCHAR(50) NOT NULL,
604 CONSTRAINT department_number_unq
605 UNIQUE (department_number)
606);
607
608INSERT INTO departments VALUES
609(1,'Accounting'),
610(2,'Payroll'),
611(3,'Operations'),
612(4,'Personnel'),
613(5,'Maintenance');
614
615CREATE TABLE employees
616(
617 employee_id INT NOT NULL,
618 last_name VARCHAR(35) NOT NULL,
619 first_name VARCHAR(35) NOT NULL,
620 department_number INT NOT NULL,
621 manager_id INT
622);
623
624INSERT INTO employees VALUES
625(1,'Smith','Cindy',2,null),
626(2,'Jones','Elmer',4,1),
627(3,'Simonian','Ralph',2,2),
628(4,'Hernandez','Olivia',1,9),
629(5,'Aaronsen','Robert',2,4),
630(6,'Watson','Denise',6,8),
631(7,'Hardy','Thomas',5,2),
632(8,'O''Leary','Rhea',4,9),
633(9,'Locario','Paulo',6,1);
634
635CREATE TABLE projects
636(
637 project_number VARCHAR(5) NOT NULL,
638 employee_id INT NOT NULL
639);
640
641INSERT INTO projects VALUES
642('P1011',8),
643('P1011',4),
644('P1012',3),
645('P1012',1),
646('P1012',5),
647('P1013',6),
648('P1013',9),
649('P1014',10);
650
651CREATE TABLE customers
652(
653 customer_id INT NOT NULL,
654 customer_last_name VARCHAR(30),
655 customer_first_name VARCHAR(30),
656 customer_address VARCHAR(60),
657 customer_city VARCHAR(15),
658 customer_state VARCHAR(15),
659 customer_zip VARCHAR(10),
660 customer_phone VARCHAR(24)
661);
662
663INSERT INTO customers VALUES
664(1, 'Anders', 'Maria', '345 Winchell Pl', 'Anderson', 'IN', '46014', '(765) 555-7878'),
665(2, 'Trujillo', 'Ana', '1298 E Smathers St', 'Benton', 'AR', '72018', '(501) 555-7733'),
666(3, 'Moreno', 'Antonio', '6925 N Parkland Ave', 'Puyallup', 'WA', '98373', '(253) 555-8332'),
667(4, 'Hardy', 'Thomas', '83 d''Urberville Ln', 'Casterbridge', 'GA', '31209', '(478) 555-1139'),
668(5, 'Berglund', 'Christina', '22717 E 73rd Ave', 'Dubuque', 'IA', '52004', '(319) 555-1139'),
669(6, 'Moos', 'Hanna', '1778 N Bovine Ave', 'Peoria', 'IL', '61638', '(309) 555-8755'),
670(7, 'Citeaux', 'Fred', '1234 Main St', 'Normal', 'IL', '61761', '(309) 555-1914'),
671(8, 'Summer', 'Martin', '1877 Ete Ct', 'Frogtown', 'LA', '70563', '(337) 555-9441'),
672(9, 'Lebihan', 'Laurence', '717 E Michigan Ave', 'Chicago', 'IL', '60611', '(312) 555-9441'),
673(10, 'Lincoln', 'Elizabeth', '4562 Rt 78 E', 'Vancouver', 'WA', '98684', '(360) 555-2680'),
674(11, 'Snyder', 'Howard', '2732 Baker Blvd.', 'Eugene', 'OR', '97403', '(503) 555-7555'),
675(12, 'Latimer', 'Yoshi', 'City Center Plaza 516 Main St.', 'Elgin', 'OR', '97827', '(503) 555-6874'),
676(13, 'Steel', 'John', '12 Orchestra Terrace', 'Walla Walla', 'WA', '99362', '(509) 555-7969'),
677(14, 'Yorres', 'Jaime', '87 Polk St. Suite 5', 'San Francisco', 'CA', '94117', '(415) 555-5938'),
678(15, 'Wilson', 'Fran', '89 Chiaroscuro Rd.', 'Portland', 'OR', '97219', '(503) 555-9573'),
679(16, 'Phillips', 'Rene', '2743 Bering St.', 'Anchorage', 'AK', '99508', '(907) 555-7584'),
680(17, 'Wilson', 'Paula', '2817 Milton Dr.', 'Albuquerque', 'NM', '87110', '(505) 555-5939'),
681(18, 'Pavarotti', 'Jose', '187 Suffolk Ln.', 'Boise', 'ID', '83720', '(208) 555-8097'),
682(19, 'Braunschweiger', 'Art', 'P.O. Box 555', 'Lander', 'WY', '82520', '(307) 555-4680'),
683(20, 'Nixon', 'Liz', '89 Jefferson Way Suite 2', 'Providence', 'RI', '02909', '(401) 555-3612'),
684(21, 'Wong', 'Liu', '55 Grizzly Peak Rd.', 'Butte', 'MT', '59801', '(406) 555-5834'),
685(22, 'Nagy', 'Helvetius', '722 DaVinci Blvd.', 'Concord', 'MA', '01742', '(351) 555-1219'),
686(23, 'Jablonski', 'Karl', '305 - 14th Ave. S. Suite 3B', 'Seattle', 'WA', '98128', '(206) 555-4112'),
687(24, 'Chelan', 'Donna', '2299 E Baylor Dr', 'Dallas', 'TX', '75224', '(469) 555-8828');
688
689-- example tables for chapter 7
690
691CREATE TABLE color_sample
692(
693 color_id INT NOT NULL AUTO_INCREMENT,
694 color_number INT NOT NULL DEFAULT 0,
695 color_name VARCHAR(50),
696 CONSTRAINT color_sample_pk
697 PRIMARY KEY (color_id)
698);
699
700
701INSERT INTO color_sample (color_number)
702VALUES (606);
703
704INSERT INTO color_sample (color_name)
705VALUES ('Yellow');
706
707INSERT INTO color_sample
708VALUES (3, DEFAULT, 'Orange');
709
710INSERT INTO color_sample
711VALUES (4, 808, NULL);
712
713INSERT INTO color_sample
714VALUES (5, DEFAULT, NULL);
715
716
717
718-- example tables for chapter 8
719CREATE TABLE string_sample
720(
721 emp_id VARCHAR(3),
722 emp_name VARCHAR(25)
723);
724
725INSERT INTO string_sample VALUES
726('1', 'Lizbeth Darien'),
727('2', 'Darnell O''Sullivan'),
728('17', 'Lance Pinos-Potter'),
729('20', 'Jean Paul Renard'),
730('3', 'Alisha von Strump');
731
732CREATE TABLE float_sample
733(
734 float_id INT,
735 float_value DOUBLE
736);
737
738INSERT INTO float_sample VALUES
739(1, 0.999999999999999),
740(2, 1),
741(3, 1.000000000000001),
742(4, 1234.56789012345),
743(5, 999.04440209348),
744(6, 24.04849);
745
746CREATE TABLE date_sample
747(
748 date_id INT NOT NULL,
749 start_date DATETIME
750);
751
752INSERT INTO date_sample VALUES
753(1, '1979-03-01 00:00:00'),
754(2, '1999-02-28 00:00:00'),
755(3, '2003-10-31 00:00:00'),
756(4, '2011-02-28 10:00:00'),
757(5, '2012-02-28 13:58:32'),
758(6, '2012-03-01 09:02:25');
759
760CREATE TABLE active_invoices
761(
762 invoice_id INT NOT NULL,
763 vendor_id INT NOT NULL,
764 invoice_number VARCHAR(50) NOT NULL,
765 invoice_date DATE NOT NULL,
766 invoice_total DECIMAL(9,2) NOT NULL,
767 payment_total DECIMAL(9,2) NOT NULL,
768 credit_total DECIMAL(9,2) NOT NULL,
769 terms_id INT NOT NULL,
770 invoice_due_date DATE NOT NULL,
771 payment_date DATE
772);
773
774INSERT INTO active_invoices VALUES
775(3, 110, 'P-0608', '2011-04-11', '20551.18', '0.00', '1200.00', 5, '2011-06-30', NULL),
776(6, 122, '989319-497', '2011-04-17', '2312.20', '0.00', '0.00', 4, '2011-06-26', NULL),
777(8, 122, '989319-487', '2011-04-18', '1927.54', '0.00', '0.00', 4, '2011-06-19', NULL),
778(15, 121, '97/553B', '2011-04-26', '313.55', '0.00', '0.00', 4, '2011-07-09', NULL),
779(18, 121, '97/553', '2011-04-27', '904.14', '0.00', '0.00', 4, '2011-07-09', NULL),
780(19, 121, '97/522', '2011-04-30', '1962.13', '0.00', '200.00', 4, '2011-07-10', NULL),
781(30, 94, '203339-13', '2011-05-02', '17.50', '0.00', '0.00', 3, '2011-06-13', NULL),
782(34, 110, '0-2436', '2011-05-07', '10976.06', '0.00', '0.00', 4, '2011-07-17', NULL),
783(38, 123, '963253272', '2011-05-09', '61.50', '0.00', '0.00', 4, '2011-06-29', NULL),
784(39, 123, '963253271', '2011-05-09', '158.00', '0.00', '0.00', 4, '2011-06-28', NULL),
785(40, 123, '963253269', '2011-05-09', '26.75', '0.00', '0.00', 4, '2011-06-25', NULL),
786(41, 123, '963253267', '2011-05-09', '23.50', '0.00', '0.00', 4, '2011-06-24', NULL),
787(42, 97, '21-4748363', '2011-05-09', '9.95', '0.00', '0.00', 4, '2011-06-25', NULL),
788(44, 123, '963253264', '2011-05-10', '52.25', '0.00', '0.00', 4, '2011-06-23', NULL),
789(45, 123, '963253263', '2011-05-10', '109.50', '0.00', '0.00', 4, '2011-06-22', NULL),
790(67, 123, '43966316', '2011-05-17', '10.00', '0.00', '0.00', 3, '2011-06-19', NULL),
791(68, 123, '263253273', '2011-05-17', '30.75', '0.00', '0.00', 4, '2011-06-29', NULL),
792(69, 37, '547479217', '2011-05-17', '116.00', '0.00', '0.00', 3, '2011-06-22', NULL),
793(70, 123, '263253270', '2011-05-18', '67.92', '0.00', '0.00', 3, '2011-06-25', NULL),
794(71, 123, '263253268', '2011-05-18', '59.97', '0.00', '0.00', 3, '2011-06-24', NULL),
795(72, 123, '263253265', '2011-05-18', '26.25', '0.00', '0.00', 3, '2011-06-23', NULL),
796(79, 123, '963253262', '2011-05-22', '42.50', '0.00', '0.00', 3, '2011-06-21', NULL),
797(81, 83, '31359783', '2011-05-23', '1575.00', '0.00', '0.00', 2, '2011-06-09', NULL),
798(82, 115, '25022117', '2011-05-24', '6.00', '0.00', '0.00', 3, '2011-06-21', NULL),
799(88, 86, '367447', '2011-05-31', '2433.00', '0.00', '0.00', 3, '2011-06-30', NULL),
800(91, 80, '134116', '2011-06-01', '90.36', '0.00', '0.00', 3, '2011-07-02', NULL),
801(94, 106, '9982771', '2011-06-03', '503.20', '0.00', '0.00', 2, '2011-06-18', NULL),
802(98, 95, '111-92R-10092', '2011-06-04', '46.21', '0.00', '0.00', 1, '2011-06-29', NULL),
803(99, 95, '111-92R-10093', '2011-06-05', '39.77', '0.00', '0.00', 2, '2011-06-28', NULL),
804(100, 96, 'I77271-O01', '2011-06-05', '662.00', '0.00', '0.00', 2, '2011-06-24', NULL),
805(103, 95, '111-92R-10094', '2011-06-06', '19.67', '0.00', '0.00', 1, '2011-06-27', NULL),
806(105, 95, '111-92R-10095', '2011-06-07', '32.70', '0.00', '0.00', 3, '2011-06-26', NULL),
807(106, 95, '111-92R-10096', '2011-06-08', '16.33', '0.00', '0.00', 2, '2011-06-25', NULL),
808(107, 95, '111-92R-10097', '2011-06-08', '16.33', '0.00', '0.00', 1, '2011-06-24', NULL),
809(109, 102, '109596', '2011-06-14', '41.80', '0.00', '0.00', 3, '2011-07-11', NULL),
810(110, 72, '39104', '2011-06-20', '85.31', '0.00', '0.00', 3, '2011-07-20', NULL),
811(111, 37, '547480102', '2011-05-19', '224.00', '0.00', '0.00', 3, '2011-06-24', NULL),
812(112, 37, '547481328', '2011-05-20', '224.00', '0.00', '0.00', 3, '2011-06-25', NULL),
813(113, 72, '40318', '2011-07-18', '21842.00', '0.00', '0.00', 3, '2011-07-20', NULL),
814(114, 83, '31361833', '2011-05-23', '579.42', '0.00', '0.00', 2, '2011-06-09', NULL);
815
816CREATE TABLE paid_invoices
817(
818 invoice_id INT NOT NULL,
819 vendor_id INT NOT NULL,
820 invoice_number VARCHAR(50) NOT NULL,
821 invoice_date DATE NOT NULL,
822 invoice_total DECIMAL(9,2) NOT NULL,
823 payment_total DECIMAL(9,2) NOT NULL,
824 credit_total DECIMAL(9,2) NOT NULL,
825 terms_id INT NOT NULL,
826 invoice_due_date DATE NOT NULL,
827 payment_date DATE
828);
829
830INSERT INTO paid_invoices VALUES
831(2, 34, 'Q545443', '2011-03-14', '1083.58', '1083.58', '0.00', 4, '2011-05-23', '2011-05-14'),
832(4, 110, 'P-0259', '2011-04-16', '26881.40', '26881.40', '0.00', 3, '2011-05-16', '2011-05-12'),
833(5, 81, 'MABO1489', '2011-04-16', '936.93', '936.93', '0.00', 3, '2011-05-16', '2011-05-13'),
834(7, 82, 'C73-24', '2011-04-17', '600.00', '600.00', '0.00', 2, '2011-05-10', '2011-05-05'),
835(9, 122, '989319-477', '2011-04-19', '2184.11', '2184.11', '0.00', 4, '2011-06-12', '2011-06-07'),
836(10, 122, '989319-467', '2011-04-24', '2318.03', '2318.03', '0.00', 4, '2011-06-05', '2011-05-29'),
837(11, 122, '989319-457', '2011-04-24', '3813.33', '3813.33', '0.00', 3, '2011-05-29', '2011-05-20'),
838(12, 122, '989319-447', '2011-04-24', '3689.99', '3689.99', '0.00', 3, '2011-05-22', '2011-05-12'),
839(13, 122, '989319-437', '2011-04-24', '2765.36', '2765.36', '0.00', 2, '2011-05-15', '2011-05-03'),
840(14, 122, '989319-427', '2011-04-25', '2115.81', '2115.81', '0.00', 1, '2011-05-08', '2011-05-01'),
841(16, 122, '989319-417', '2011-04-26', '2051.59', '2051.59', '0.00', 1, '2011-05-01', '2011-04-28'),
842(17, 90, '97-1024A', '2011-04-26', '356.48', '356.48', '0.00', 3, '2011-06-09', '2011-06-09'),
843(20, 121, '97/503', '2011-04-30', '639.77', '639.77', '0.00', 4, '2011-06-11', '2011-06-05'),
844(21, 121, '97/488', '2011-04-30', '601.95', '601.95', '0.00', 3, '2011-06-03', '2011-05-27'),
845(22, 121, '97/486', '2011-04-30', '953.10', '953.10', '0.00', 2, '2011-05-21', '2011-05-13'),
846(23, 121, '97/465', '2011-05-01', '565.15', '565.15', '0.00', 1, '2011-05-14', '2011-05-05'),
847(24, 121, '97/222', '2011-05-01', '1000.46', '1000.46', '0.00', 3, '2011-06-03', '2011-05-25'),
848(25, 123, '4-342-8069', '2011-05-01', '10.00', '10.00', '0.00', 4, '2011-06-10', '2011-05-27'),
849(26, 123, '4-327-7357', '2011-05-01', '162.75', '162.75', '0.00', 3, '2011-05-27', '2011-05-21'),
850(27, 123, '4-321-2596', '2011-05-01', '10.00', '10.00', '0.00', 2, '2011-05-20', '2011-05-11'),
851(28, 123, '7548906-20', '2011-05-01', '27.00', '27.00', '0.00', 3, '2011-06-06', '2011-05-26'),
852(29, 123, '4-314-3057', '2011-05-02', '13.75', '13.75', '0.00', 1, '2011-05-13', '2011-05-07'),
853(31, 123, '2-000-2993', '2011-05-03', '144.70', '144.70', '0.00', 1, '2011-05-06', '2011-05-04'),
854(32, 89, '125520-1', '2011-05-05', '95.00', '95.00', '0.00', 3, '2011-06-08', '2011-05-22'),
855(33, 123, '1-202-2978', '2011-05-06', '33.00', '33.00', '0.00', 1, '2011-05-20', '2011-05-13'),
856(35, 123, '1-200-5164', '2011-05-07', '63.40', '63.40', '0.00', 1, '2011-05-13', '2011-05-10'),
857(36, 110, '0-2060', '2011-05-08', '23517.58', '21221.63', '2295.95', 3, '2011-06-09', '2011-06-10'),
858(37, 110, '0-2058', '2011-05-08', '37966.19', '37966.19', '0.00', 3, '2011-06-09', '2011-05-31'),
859(43, 97, '21-4923721', '2011-05-09', '9.95', '9.95', '0.00', 1, '2011-05-21', '2011-05-13'),
860(46, 123, '963253261', '2011-05-10', '42.75', '42.75', '0.00', 3, '2011-06-16', '2011-06-10'),
861(47, 123, '963253260', '2011-05-10', '36.00', '36.00', '0.00', 3, '2011-06-15', '2011-06-06'),
862(48, 123, '963253258', '2011-05-10', '111.00', '111.00', '0.00', 3, '2011-06-11', '2011-05-31'),
863(49, 123, '963253256', '2011-05-10', '53.25', '53.25', '0.00', 3, '2011-06-10', '2011-05-27'),
864(50, 123, '963253255', '2011-05-11', '53.75', '53.75', '0.00', 3, '2011-06-09', '2011-06-03'),
865(51, 123, '963253254', '2011-05-11', '108.50', '108.50', '0.00', 3, '2011-06-08', '2011-05-30'),
866(52, 123, '963253252', '2011-05-11', '38.75', '38.75', '0.00', 3, '2011-06-07', '2011-05-27'),
867(53, 123, '963253251', '2011-05-11', '15.50', '15.50', '0.00', 3, '2011-06-04', '2011-05-21'),
868(54, 123, '963253249', '2011-05-12', '127.75', '127.75', '0.00', 2, '2011-06-03', '2011-05-28'),
869(55, 123, '963253248', '2011-05-13', '241.00', '241.00', '0.00', 2, '2011-06-02', '2011-05-24'),
870(56, 123, '963253246', '2011-05-13', '129.00', '129.00', '0.00', 2, '2011-05-31', '2011-05-20'),
871(57, 123, '963253245', '2011-05-13', '40.75', '40.75', '0.00', 2, '2011-05-28', '2011-05-14'),
872(58, 123, '963253244', '2011-05-13', '60.00', '60.00', '0.00', 2, '2011-05-27', '2011-05-21'),
873(59, 123, '963253242', '2011-05-13', '104.00', '104.00', '0.00', 2, '2011-05-26', '2011-05-17'),
874(60, 123, '963253240', '2011-05-23', '67.00', '67.00', '0.00', 1, '2011-06-03', '2011-05-28'),
875(61, 123, '963253239', '2011-05-23', '147.25', '147.25', '0.00', 1, '2011-06-02', '2011-05-28'),
876(62, 123, '963253237', '2011-05-23', '172.50', '172.50', '0.00', 1, '2011-05-30', '2011-05-24'),
877(63, 123, '963253235', '2011-05-14', '108.25', '108.25', '0.00', 1, '2011-05-20', '2011-05-17'),
878(64, 123, '963253234', '2011-05-14', '138.75', '138.75', '0.00', 1, '2011-05-19', '2011-05-16'),
879(65, 123, '963253232', '2011-05-14', '127.75', '127.75', '0.00', 1, '2011-05-18', '2011-05-16'),
880(66, 123, '963253230', '2011-05-15', '739.20', '739.20', '0.00', 1, '2011-05-17', '2011-05-16'),
881(73, 123, '263253257', '2011-05-18', '22.57', '22.57', '0.00', 2, '2011-06-10', '2011-05-27'),
882(74, 123, '263253253', '2011-05-18', '31.95', '31.95', '0.00', 2, '2011-06-07', '2011-06-01'),
883(75, 123, '263253250', '2011-05-19', '42.67', '42.67', '0.00', 2, '2011-06-03', '2011-05-25'),
884(76, 123, '263253243', '2011-05-20', '44.44', '44.44', '0.00', 1, '2011-05-26', '2011-05-23'),
885(77, 123, '263253241', '2011-05-20', '40.20', '40.20', '0.00', 1, '2011-05-25', '2011-05-22'),
886(78, 123, '94007069', '2011-05-22', '400.00', '400.00', '0.00', 3, '2011-07-01', '2011-06-25'),
887(80, 105, '94007005', '2011-05-23', '220.00', '220.00', '0.00', 1, '2011-05-30', '2011-05-26'),
888(83, 115, '24946731', '2011-05-25', '25.67', '25.67', '0.00', 2, '2011-06-14', '2011-05-28'),
889(84, 115, '24863706', '2011-05-27', '6.00', '6.00', '0.00', 1, '2011-06-07', '2011-06-01'),
890(85, 115, '24780512', '2011-05-29', '6.00', '6.00', '0.00', 1, '2011-05-31', '2011-05-30'),
891(86, 88, '972110', '2011-05-30', '207.78', '207.78', '0.00', 1, '2011-06-06', '2011-06-02'),
892(87, 100, '587056', '2011-05-31', '2184.50', '2184.50', '0.00', 3, '2011-06-28', '2011-06-22'),
893(89, 99, '509786', '2011-05-31', '6940.25', '6940.25', '0.00', 2, '2011-06-16', '2011-06-08'),
894(90, 108, '121897', '2011-06-01', '450.00', '450.00', '0.00', 2, '2011-06-19', '2011-06-14'),
895(92, 80, '133560', '2011-06-01', '175.00', '175.00', '0.00', 2, '2011-06-20', '2011-06-03'),
896(93, 104, 'P02-3772', '2011-06-03', '7125.34', '7125.34', '0.00', 2, '2011-06-18', '2011-06-08'),
897(95, 107, 'RTR-72-3662-X', '2011-06-04', '1600.00', '1600.00', '0.00', 2, '2011-06-18', '2011-06-11'),
898(96, 113, '77290', '2011-06-04', '1750.00', '1750.00', '0.00', 2, '2011-06-18', '2011-06-08'),
899(97, 119, '10843', '2011-06-04', '4901.26', '4901.26', '0.00', 2, '2011-06-18', '2011-06-11'),
900(101, 103, '75C-90227', '2011-06-06', '1367.50', '1367.50', '0.00', 1, '2011-06-13', '2011-06-09'),
901(102, 48, 'P02-88D77S7', '2011-06-06', '856.92', '856.92', '0.00', 1, '2011-06-13', '2011-06-09'),
902(104, 114, 'CBM9920-M-T77109', '2011-06-07', '290.00', '290.00', '0.00', 1, '2011-06-12', '2011-06-09'),
903(108, 117, '111897', '2011-06-11', '16.62', '16.62', '0.00', 1, '2011-06-14', '2011-06-12');
904
905-- ********************************************
906-- CREATE THE OM DATABASE
907-- *******************************************
908
909-- create database
910DROP DATABASE IF EXISTS om;
911CREATE DATABASE om;
912
913-- select database
914USE om;
915
916-- create tables
917CREATE TABLE customers
918(
919 customer_id INT NOT NULL,
920 customer_first_name VARCHAR(50),
921 customer_last_name VARCHAR(50) NOT NULL,
922 customer_address VARCHAR(255) NOT NULL,
923 customer_city VARCHAR(50) NOT NULL,
924 customer_state CHAR(2) NOT NULL,
925 customer_zip VARCHAR(20) NOT NULL,
926 customer_phone VARCHAR(30) NOT NULL,
927 customer_fax VARCHAR(30),
928 CONSTRAINT customers_pk
929 PRIMARY KEY (customer_id)
930);
931
932CREATE TABLE items
933(
934 item_id INT NOT NULL,
935 title VARCHAR(50) NOT NULL,
936 artist VARCHAR(50) NOT NULL,
937 unit_price DECIMAL(9,2) NOT NULL,
938 CONSTRAINT items_pk
939 PRIMARY KEY (item_id),
940 CONSTRAINT title_artist_unq
941 UNIQUE (title, artist)
942);
943
944CREATE TABLE orders
945(
946 order_id INT NOT NULL,
947 customer_id INT NOT NULL,
948 order_date DATE NOT NULL,
949 shipped_date DATE,
950 CONSTRAINT orders_pk
951 PRIMARY KEY (order_id),
952 CONSTRAINT orders_fk_customers
953 FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
954);
955
956CREATE TABLE order_details
957(
958 order_id INT NOT NULL,
959 item_id INT NOT NULL,
960 order_qty INT NOT NULL,
961 CONSTRAINT order_details_pk
962 PRIMARY KEY (order_id, item_id),
963 CONSTRAINT order_details_fk_orders
964 FOREIGN KEY (order_id)
965 REFERENCES orders (order_id),
966 CONSTRAINT order_details_fk_items
967 FOREIGN KEY (item_id)
968 REFERENCES items (item_id)
969);
970
971-- insert rows into tables
972INSERT INTO customers VALUES
973(1,'Korah','Blanca','1555 W Lane Ave','Columbus','OH','43221','6145554435','6145553928'),
974(2,'Yash','Randall','11 E Rancho Madera Rd','Madison','WI','53707','2095551205','2095552262'),
975(3,'Johnathon','Millerton','60 Madison Ave','New York','NY','10010','2125554800','NULL'),
976(4,'Mikayla','Damion','2021 K Street Nw','Washington','DC','20006','2025555561','NULL'),
977(5,'Kendall','Mayte','4775 E Miami River Rd','Cleves','OH','45002','5135553043','NULL'),
978(6,'Kaitlin','Hostlery','3250 Spring Grove Ave','Cincinnati','OH','45225','8005551957','8005552826'),
979(7,'Derek','Chaddick','9022 E Merchant Wy','Fairfield','IA','52556','5155556130','NULL'),
980(8,'Deborah','Damien','415 E Olive Ave','Fresno','CA','93728','5595558060','NULL'),
981(9,'Karina','Lacy','882 W Easton Wy','Los Angeles','CA','90084','8005557000','NULL'),
982(10,'Kurt','Nickalus','28210 N Avenue Stanford','Valencia','CA','91355','8055550584','055556689'),
983(11,'Kelsey','Eulalia','7833 N Ridge Rd','Sacramento','CA','95887','2095557500','2095551302'),
984(12,'Anders','Rohansen','12345 E 67th Ave NW','Takoma Park','MD','24512','3385556772','NULL'),
985(13,'Thalia','Neftaly','2508 W Shaw Ave','Fresno','CA','93711','5595556245','NULL'),
986(14,'Gonzalo','Keeton','12 Daniel Road','Fairfield','NJ','07004','2015559742','NULL'),
987(15,'Ania','Irvin','1099 N Farcourt St','Orange','CA','92807','7145559000','NULL'),
988(16,'Dakota','Baylee','1033 N Sycamore Ave.','Los Angeles','CA','90038','2135554322','NULL'),
989(17,'Samuel','Jacobsen','3433 E Widget Ave','Palo Alto','CA','92711','4155553434','NULL'),
990(18,'Justin','Javen','828 S Broadway','Tarrytown','NY','10591','8005550037','NULL'),
991(19,'Kyle','Marissa','789 E Mercy Ave','Phoenix','AZ','85038','9475553900','NULL'),
992(20,'Erick','Kaleigh','Five Lakepointe Plaza, Ste 500','Charlotte','NC','28217','7045553500','NULL'),
993(21,'Marvin','Quintin','2677 Industrial Circle Dr','Columbus','OH','43260','6145558600','6145557580'),
994(22,'Rashad','Holbrooke','3467 W Shaw Ave #103','Fresno','CA','93711','5595558625','5595558495'),
995(23,'Trisha','Anum','627 Aviation Way','Manhatttan Beach','CA','90266','3105552732','NULL'),
996(24,'Julian','Carson','372 San Quentin','San Francisco','CA','94161','6175550700','NULL'),
997(25,'Kirsten','Story','2401 Wisconsin Ave NW','Washington','DC','20559','2065559115','NULL');
998
999INSERT INTO items (item_id,title,artist,unit_price) VALUES
1000(1,'Umami In Concert','Umami',17.95),
1001(2,'Race Car Sounds','The Ubernerds',13),
1002(3,'No Rest For The Weary','No Rest For The Weary',16.95),
1003(4,'More Songs About Structures and Comestibles','No Rest For The Weary',17.95),
1004(5,'On The Road With Burt Ruggles','Burt Ruggles',17.5),
1005(6,'No Fixed Address','Sewed the Vest Pocket',16.95),
1006(7,'Rude Noises','Jess & Odie',13),
1007(8,'Burt Ruggles: An Intimate Portrait','Burt Ruggles',17.95),
1008(9,'Zone Out With Umami','Umami',16.95),
1009(10,'Etcetera','Onn & Onn',17);
1010
1011INSERT INTO orders VALUES
1012(19, 1, '2009-10-23', '2009-10-28'),
1013(29, 8, '2009-11-05', '2009-11-11'),
1014(32, 11, '2009-11-10', '2009-11-13'),
1015(45, 2, '2009-11-25', '2009-11-30'),
1016(70, 10, '2009-12-28', '2010-01-07'),
1017(89, 22, '2010-01-20', '2010-01-22'),
1018(97, 20, '2010-01-29', '2010-02-02'),
1019(118, 3, '2010-02-24', '2010-02-28'),
1020(144, 17, '2010-03-21', '2010-03-29'),
1021(158, 9, '2010-04-04', '2010-04-20'),
1022(165, 14, '2010-04-11', '2010-04-13'),
1023(180, 24, '2010-04-25', '2010-05-30'),
1024(231, 15, '2010-06-14', '2010-06-22'),
1025(242, 23, '2010-06-24', '2010-07-06'),
1026(264, 9, '2010-07-15', '2010-07-18'),
1027(298, 18, '2010-08-18', '2010-09-22'),
1028(321, 2, '2010-09-09', '2010-10-05'),
1029(381, 7, '2010-11-08', '2010-11-16'),
1030(392, 19, '2010-11-16', '2010-11-23'),
1031(413, 17, '2010-12-05', '2011-01-11'),
1032(442, 5, '2010-12-28', '2011-01-03'),
1033(479, 1, '2011-01-30', '2011-03-03'),
1034(491, 16, '2011-02-08', '2011-02-14'),
1035(494, 4, '2011-02-10', '2011-02-14'),
1036(523, 3, '2011-03-07', '2011-03-15'),
1037(548, 2, '2011-03-22', '2011-04-18'),
1038(550, 17, '2011-03-23', '2011-04-03'),
1039(601, 16, '2011-04-21', '2011-04-27'),
1040(606, 6, '2011-04-25', '2011-05-02'),
1041(607, 20, '2011-04-25', '2011-05-04'),
1042(624, 2, '2011-05-04', '2011-05-09'),
1043(627, 17, '2011-05-05', '2011-05-10'),
1044(630, 20, '2011-05-08', '2011-05-18'),
1045(631, 21, '2011-05-09', '2011-05-11'),
1046(651, 12, '2011-05-19', '2011-06-02'),
1047(658, 12, '2011-05-23', '2011-06-02'),
1048(687, 17, '2011-06-05', '2011-06-08'),
1049(693, 9, '2011-06-07', '2011-06-19'),
1050(703, 19, '2011-06-12', '2011-06-19'),
1051(773, 25, '2011-07-11', '2011-07-13'),
1052(778, 13, '2011-07-12', '2011-07-21'),
1053(796, 17, '2011-07-19', '2011-07-26'),
1054(800, 19, '2011-07-21', '2011-07-28'),
1055(802, 2, '2011-07-21', '2011-07-31'),
1056(824, 1, '2011-08-01', NULL),
1057(827, 18, '2011-08-02', NULL),
1058(829, 9, '2011-08-02', NULL);
1059
1060INSERT INTO order_details VALUES
1061(381,1,1),
1062(601,9,1),
1063(442,1,1),
1064(523,9,1),
1065(630,5,1),
1066(778,1,1),
1067(693,10,1),
1068(118,1,1),
1069(264,7,1),
1070(607,10,1),
1071(624,7,1),
1072(658,1,1),
1073(800,5,1),
1074(158,3,1),
1075(321,10,1),
1076(687,6,1),
1077(827,6,1),
1078(144,3,1),
1079(264,8,1),
1080(479,1,2),
1081(630,6,2),
1082(796,5,1),
1083(97,4,1),
1084(601,5,1),
1085(773,10,1),
1086(800,1,1),
1087(29,10,1),
1088(70,1,1),
1089(97,8,1),
1090(165,4,1),
1091(180,4,1),
1092(231,10,1),
1093(392,8,1),
1094(413,10,1),
1095(491,6,1),
1096(494,2,1),
1097(606,8,1),
1098(607,3,1),
1099(651,3,1),
1100(703,4,1),
1101(796,2,1),
1102(802,2,1),
1103(802,3,1),
1104(824,7,2),
1105(829,1,1),
1106(550,4,1),
1107(796,7,1),
1108(829,2,1),
1109(693,6,1),
1110(29,3,1),
1111(32,7,1),
1112(242,1,1),
1113(298,1,1),
1114(479,4,1),
1115(548,9,1),
1116(627,9,1),
1117(778,3,1),
1118(687,8,1),
1119(19,5,1),
1120(89,4,1),
1121(242,6,1),
1122(264,4,1),
1123(550,1,1),
1124(631,10,1),
1125(693,7,3),
1126(824,3,1),
1127(829,5,1),
1128(829,9,1);