· 7 years ago · Feb 14, 2019, 11:02 AM
1CREATE DATABASE IF NOT EXISTS book DEFAULT CHARSET = utf8;
2USE book;
3
4/* begin table creation */
5
6create table department
7 (dept_id smallint unsigned not null auto_increment,
8 name varchar(20) not null,
9 constraint pk_department primary key (dept_id)
10 );
11
12create table branch
13 (branch_id smallint unsigned not null auto_increment,
14 name varchar(20) not null,
15 address varchar(30),
16 city varchar(20),
17 state varchar(2),
18 zip varchar(12),
19 constraint pk_branch primary key (branch_id)
20 );
21
22create table employee
23 (emp_id smallint unsigned not null auto_increment,
24 fname varchar(20) not null,
25 lname varchar(20) not null,
26 start_date date not null,
27 end_date date,
28 superior_emp_id smallint unsigned,
29 dept_id smallint unsigned,
30 title varchar(20),
31 assigned_branch_id smallint unsigned,
32 constraint fk_e_emp_id
33 foreign key (superior_emp_id) references employee (emp_id),
34 constraint fk_dept_id
35 foreign key (dept_id) references department (dept_id),
36 constraint fk_e_branch_id
37 foreign key (assigned_branch_id) references branch (branch_id),
38 constraint pk_employee primary key (emp_id)
39 );
40
41create table product_type
42 (product_type_cd varchar(10) not null,
43 name varchar(50) not null,
44 constraint pk_product_type primary key (product_type_cd)
45 );
46
47create table product
48 (product_cd varchar(10) not null,
49 name varchar(50) not null,
50 product_type_cd varchar(10) not null,
51 date_offered date,
52 date_retired date,
53 constraint fk_product_type_cd foreign key (product_type_cd)
54 references product_type (product_type_cd),
55 constraint pk_product primary key (product_cd)
56 );
57
58create table customer
59 (cust_id integer unsigned not null auto_increment,
60 fed_id varchar(12) not null,
61 cust_type_cd enum('I','B') not null,
62 address varchar(30),
63 city varchar(20),
64 state varchar(20),
65 postal_code varchar(10),
66 constraint pk_customer primary key (cust_id)
67 );
68
69create table individual
70 (cust_id integer unsigned not null,
71 fname varchar(30) not null,
72 lname varchar(30) not null,
73 birth_date date,
74 constraint fk_i_cust_id foreign key (cust_id)
75 references customer (cust_id),
76 constraint pk_individual primary key (cust_id)
77 );
78
79create table business
80 (cust_id integer unsigned not null,
81 name varchar(40) not null,
82 state_id varchar(10) not null,
83 incorp_date date,
84 constraint fk_b_cust_id foreign key (cust_id)
85 references customer (cust_id),
86 constraint pk_business primary key (cust_id)
87 );
88
89create table officer
90 (officer_id smallint unsigned not null auto_increment,
91 cust_id integer unsigned not null,
92 fname varchar(30) not null,
93 lname varchar(30) not null,
94 title varchar(20),
95 start_date date not null,
96 end_date date,
97 constraint fk_o_cust_id foreign key (cust_id)
98 references business (cust_id),
99 constraint pk_officer primary key (officer_id)
100 );
101
102create table account
103 (account_id integer unsigned not null auto_increment,
104 product_cd varchar(10) not null,
105 cust_id integer unsigned not null,
106 open_date date not null,
107 close_date date,
108 last_activity_date date,
109 status enum('ACTIVE','CLOSED','FROZEN'),
110 open_branch_id smallint unsigned,
111 open_emp_id smallint unsigned,
112 avail_balance float(10,2),
113 pending_balance float(10,2),
114 constraint fk_product_cd foreign key (product_cd)
115 references product (product_cd),
116 constraint fk_a_cust_id foreign key (cust_id)
117 references customer (cust_id),
118 constraint fk_a_branch_id foreign key (open_branch_id)
119 references branch (branch_id),
120 constraint fk_a_emp_id foreign key (open_emp_id)
121 references employee (emp_id),
122 constraint pk_account primary key (account_id)
123 );
124
125create table transaction
126 (txn_id integer unsigned not null auto_increment,
127 txn_date datetime not null,
128 account_id integer unsigned not null,
129 txn_type_cd enum('DBT','CDT'),
130 amount double(10,2) not null,
131 teller_emp_id smallint unsigned,
132 execution_branch_id smallint unsigned,
133 funds_avail_date datetime,
134 constraint fk_t_account_id foreign key (account_id)
135 references account (account_id),
136 constraint fk_teller_emp_id foreign key (teller_emp_id)
137 references employee (emp_id),
138 constraint fk_exec_branch_id foreign key (execution_branch_id)
139 references branch (branch_id),
140 constraint pk_transaction primary key (txn_id)
141 );
142
143/* end table creation */
144
145/* begin data population */
146
147/* department data */
148insert into department (dept_id, name)
149values (null, 'Operations');
150insert into department (dept_id, name)
151values (null, 'Loans');
152insert into department (dept_id, name)
153values (null, 'Administration');
154
155/* branch data */
156insert into branch (branch_id, name, address, city, state, zip)
157values (null, 'Headquarters', '3882 Main St.', 'Waltham', 'MA', '02451');
158insert into branch (branch_id, name, address, city, state, zip)
159values (null, 'Woburn Branch', '422 Maple St.', 'Woburn', 'MA', '01801');
160insert into branch (branch_id, name, address, city, state, zip)
161values (null, 'Quincy Branch', '125 Presidential Way', 'Quincy', 'MA', '02169');
162insert into branch (branch_id, name, address, city, state, zip)
163values (null, 'So. NH Branch', '378 Maynard Ln.', 'Salem', 'NH', '03079');
164
165/* employee data */
166insert into employee (emp_id, fname, lname, start_date,
167 dept_id, title, assigned_branch_id)
168values (null, 'Michael', 'Smith', '2001-06-22',
169 (select dept_id from department where name = 'Administration'),
170 'President',
171 (select branch_id from branch where name = 'Headquarters'));
172insert into employee (emp_id, fname, lname, start_date,
173 dept_id, title, assigned_branch_id)
174values (null, 'Susan', 'Barker', '2002-09-12',
175 (select dept_id from department where name = 'Administration'),
176 'Vice President',
177 (select branch_id from branch where name = 'Headquarters'));
178insert into employee (emp_id, fname, lname, start_date,
179 dept_id, title, assigned_branch_id)
180values (null, 'Robert', 'Tyler', '2000-02-09',
181 (select dept_id from department where name = 'Administration'),
182 'Treasurer',
183 (select branch_id from branch where name = 'Headquarters'));
184insert into employee (emp_id, fname, lname, start_date,
185 dept_id, title, assigned_branch_id)
186values (null, 'Susan', 'Hawthorne', '2002-04-24',
187 (select dept_id from department where name = 'Operations'),
188 'Operations Manager',
189 (select branch_id from branch where name = 'Headquarters'));
190insert into employee (emp_id, fname, lname, start_date,
191 dept_id, title, assigned_branch_id)
192values (null, 'John', 'Gooding', '2003-11-14',
193 (select dept_id from department where name = 'Loans'),
194 'Loan Manager',
195 (select branch_id from branch where name = 'Headquarters'));
196insert into employee (emp_id, fname, lname, start_date,
197 dept_id, title, assigned_branch_id)
198values (null, 'Helen', 'Fleming', '2004-03-17',
199 (select dept_id from department where name = 'Operations'),
200 'Head Teller',
201 (select branch_id from branch where name = 'Headquarters'));
202insert into employee (emp_id, fname, lname, start_date,
203 dept_id, title, assigned_branch_id)
204values (null, 'Chris', 'Tucker', '2004-09-15',
205 (select dept_id from department where name = 'Operations'),
206 'Teller',
207 (select branch_id from branch where name = 'Headquarters'));
208insert into employee (emp_id, fname, lname, start_date,
209 dept_id, title, assigned_branch_id)
210values (null, 'Sarah', 'Parker', '2002-12-02',
211 (select dept_id from department where name = 'Operations'),
212 'Teller',
213 (select branch_id from branch where name = 'Headquarters'));
214insert into employee (emp_id, fname, lname, start_date,
215 dept_id, title, assigned_branch_id)
216values (null, 'Jane', 'Grossman', '2002-05-03',
217 (select dept_id from department where name = 'Operations'),
218 'Teller',
219 (select branch_id from branch where name = 'Headquarters'));
220insert into employee (emp_id, fname, lname, start_date,
221 dept_id, title, assigned_branch_id)
222values (null, 'Paula', 'Roberts', '2002-07-27',
223 (select dept_id from department where name = 'Operations'),
224 'Head Teller',
225 (select branch_id from branch where name = 'Woburn Branch'));
226insert into employee (emp_id, fname, lname, start_date,
227 dept_id, title, assigned_branch_id)
228values (null, 'Thomas', 'Ziegler', '2000-10-23',
229 (select dept_id from department where name = 'Operations'),
230 'Teller',
231 (select branch_id from branch where name = 'Woburn Branch'));
232insert into employee (emp_id, fname, lname, start_date,
233 dept_id, title, assigned_branch_id)
234values (null, 'Samantha', 'Jameson', '2003-01-08',
235 (select dept_id from department where name = 'Operations'),
236 'Teller',
237 (select branch_id from branch where name = 'Woburn Branch'));
238insert into employee (emp_id, fname, lname, start_date,
239 dept_id, title, assigned_branch_id)
240values (null, 'John', 'Blake', '2000-05-11',
241 (select dept_id from department where name = 'Operations'),
242 'Head Teller',
243 (select branch_id from branch where name = 'Quincy Branch'));
244insert into employee (emp_id, fname, lname, start_date,
245 dept_id, title, assigned_branch_id)
246values (null, 'Cindy', 'Mason', '2002-08-09',
247 (select dept_id from department where name = 'Operations'),
248 'Teller',
249 (select branch_id from branch where name = 'Quincy Branch'));
250insert into employee (emp_id, fname, lname, start_date,
251 dept_id, title, assigned_branch_id)
252values (null, 'Frank', 'Portman', '2003-04-01',
253 (select dept_id from department where name = 'Operations'),
254 'Teller',
255 (select branch_id from branch where name = 'Quincy Branch'));
256insert into employee (emp_id, fname, lname, start_date,
257 dept_id, title, assigned_branch_id)
258values (null, 'Theresa', 'Markham', '2001-03-15',
259 (select dept_id from department where name = 'Operations'),
260 'Head Teller',
261 (select branch_id from branch where name = 'So. NH Branch'));
262insert into employee (emp_id, fname, lname, start_date,
263 dept_id, title, assigned_branch_id)
264values (null, 'Beth', 'Fowler', '2002-06-29',
265 (select dept_id from department where name = 'Operations'),
266 'Teller',
267 (select branch_id from branch where name = 'So. NH Branch'));
268insert into employee (emp_id, fname, lname, start_date,
269 dept_id, title, assigned_branch_id)
270values (null, 'Rick', 'Tulman', '2002-12-12',
271 (select dept_id from department where name = 'Operations'),
272 'Teller',
273 (select branch_id from branch where name = 'So. NH Branch'));
274
275/* create data for self-referencing foreign key 'superior_emp_id' */
276create temporary table emp_tmp as
277select emp_id, fname, lname from employee;
278
279update employee set superior_emp_id =
280 (select emp_id from emp_tmp where lname = 'Smith' and fname = 'Michael')
281where ((lname = 'Barker' and fname = 'Susan')
282 or (lname = 'Tyler' and fname = 'Robert'));
283update employee set superior_emp_id =
284 (select emp_id from emp_tmp where lname = 'Tyler' and fname = 'Robert')
285where lname = 'Hawthorne' and fname = 'Susan';
286update employee set superior_emp_id =
287 (select emp_id from emp_tmp where lname = 'Hawthorne' and fname = 'Susan')
288where ((lname = 'Gooding' and fname = 'John')
289 or (lname = 'Fleming' and fname = 'Helen')
290 or (lname = 'Roberts' and fname = 'Paula')
291 or (lname = 'Blake' and fname = 'John')
292 or (lname = 'Markham' and fname = 'Theresa'));
293update employee set superior_emp_id =
294 (select emp_id from emp_tmp where lname = 'Fleming' and fname = 'Helen')
295where ((lname = 'Tucker' and fname = 'Chris')
296 or (lname = 'Parker' and fname = 'Sarah')
297 or (lname = 'Grossman' and fname = 'Jane'));
298update employee set superior_emp_id =
299 (select emp_id from emp_tmp where lname = 'Roberts' and fname = 'Paula')
300where ((lname = 'Ziegler' and fname = 'Thomas')
301 or (lname = 'Jameson' and fname = 'Samantha'));
302update employee set superior_emp_id =
303 (select emp_id from emp_tmp where lname = 'Blake' and fname = 'John')
304where ((lname = 'Mason' and fname = 'Cindy')
305 or (lname = 'Portman' and fname = 'Frank'));
306update employee set superior_emp_id =
307 (select emp_id from emp_tmp where lname = 'Markham' and fname = 'Theresa')
308where ((lname = 'Fowler' and fname = 'Beth')
309 or (lname = 'Tulman' and fname = 'Rick'));
310
311drop table emp_tmp;
312
313/* product type data */
314insert into product_type (product_type_cd, name)
315values ('ACCOUNT','Customer Accounts');
316insert into product_type (product_type_cd, name)
317values ('LOAN','Individual and Business Loans');
318insert into product_type (product_type_cd, name)
319values ('INSURANCE','Insurance Offerings');
320
321/* product data */
322insert into product (product_cd, name, product_type_cd, date_offered)
323values ('CHK','checking account','ACCOUNT','2000-01-01');
324insert into product (product_cd, name, product_type_cd, date_offered)
325values ('SAV','savings account','ACCOUNT','2000-01-01');
326insert into product (product_cd, name, product_type_cd, date_offered)
327values ('MM','money market account','ACCOUNT','2000-01-01');
328insert into product (product_cd, name, product_type_cd, date_offered)
329values ('CD','certificate of deposit','ACCOUNT','2000-01-01');
330insert into product (product_cd, name, product_type_cd, date_offered)
331values ('MRT','home mortgage','LOAN','2000-01-01');
332insert into product (product_cd, name, product_type_cd, date_offered)
333values ('AUT','auto loan','LOAN','2000-01-01');
334insert into product (product_cd, name, product_type_cd, date_offered)
335values ('BUS','business line of credit','LOAN','2000-01-01');
336insert into product (product_cd, name, product_type_cd, date_offered)
337values ('SBL','small business loan','LOAN','2000-01-01');
338
339/* residential customer data */
340insert into customer (cust_id, fed_id, cust_type_cd,
341 address, city, state, postal_code)
342values (null, '111-11-1111', 'I', '47 Mockingbird Ln', 'Lynnfield', 'MA', '01940');
343insert into individual (cust_id, fname, lname, birth_date)
344select cust_id, 'James', 'Hadley', '1972-04-22' from customer
345where fed_id = '111-11-1111';
346insert into customer (cust_id, fed_id, cust_type_cd,
347 address, city, state, postal_code)
348values (null, '222-22-2222', 'I', '372 Clearwater Blvd', 'Woburn', 'MA', '01801');
349insert into individual (cust_id, fname, lname, birth_date)
350select cust_id, 'Susan', 'Tingley', '1968-08-15' from customer
351where fed_id = '222-22-2222';
352insert into customer (cust_id, fed_id, cust_type_cd,
353 address, city, state, postal_code)
354values (null, '333-33-3333', 'I', '18 Jessup Rd', 'Quincy', 'MA', '02169');
355insert into individual (cust_id, fname, lname, birth_date)
356select cust_id, 'Frank', 'Tucker', '1958-02-06' from customer
357where fed_id = '333-33-3333';
358insert into customer (cust_id, fed_id, cust_type_cd,
359 address, city, state, postal_code)
360values (null, '444-44-4444', 'I', '12 Buchanan Ln', 'Waltham', 'MA', '02451');
361insert into individual (cust_id, fname, lname, birth_date)
362select cust_id, 'John', 'Hayward', '1966-12-22' from customer
363where fed_id = '444-44-4444';
364insert into customer (cust_id, fed_id, cust_type_cd,
365 address, city, state, postal_code)
366values (null, '555-55-5555', 'I', '2341 Main St', 'Salem', 'NH', '03079');
367insert into individual (cust_id, fname, lname, birth_date)
368select cust_id, 'Charles', 'Frasier', '1971-08-25' from customer
369where fed_id = '555-55-5555';
370insert into customer (cust_id, fed_id, cust_type_cd,
371 address, city, state, postal_code)
372values (null, '666-66-6666', 'I', '12 Blaylock Ln', 'Waltham', 'MA', '02451');
373insert into individual (cust_id, fname, lname, birth_date)
374select cust_id, 'John', 'Spencer', '1962-09-14' from customer
375where fed_id = '666-66-6666';
376insert into customer (cust_id, fed_id, cust_type_cd,
377 address, city, state, postal_code)
378values (null, '777-77-7777', 'I', '29 Admiral Ln', 'Wilmington', 'MA', '01887');
379insert into individual (cust_id, fname, lname, birth_date)
380select cust_id, 'Margaret', 'Young', '1947-03-19' from customer
381where fed_id = '777-77-7777';
382insert into customer (cust_id, fed_id, cust_type_cd,
383 address, city, state, postal_code)
384values (null, '888-88-8888', 'I', '472 Freedom Rd', 'Salem', 'NH', '03079');
385insert into individual (cust_id, fname, lname, birth_date)
386select cust_id, 'Louis', 'Blake', '1977-07-01' from customer
387where fed_id = '888-88-8888';
388insert into customer (cust_id, fed_id, cust_type_cd,
389 address, city, state, postal_code)
390values (null, '999-99-9999', 'I', '29 Maple St', 'Newton', 'MA', '02458');
391insert into individual (cust_id, fname, lname, birth_date)
392select cust_id, 'Richard', 'Farley', '1968-06-16' from customer
393where fed_id = '999-99-9999';
394
395/* corporate customer data */
396insert into customer (cust_id, fed_id, cust_type_cd,
397 address, city, state, postal_code)
398values (null, '04-1111111', 'B', '7 Industrial Way', 'Salem', 'NH', '03079');
399insert into business (cust_id, name, state_id, incorp_date)
400select cust_id, 'Chilton Engineering', '12-345-678', '1995-05-01' from customer
401where fed_id = '04-1111111';
402insert into officer (officer_id, cust_id, fname, lname,
403 title, start_date)
404select null, cust_id, 'John', 'Chilton', 'President', '1995-05-01'
405from customer
406where fed_id = '04-1111111';
407insert into customer (cust_id, fed_id, cust_type_cd,
408 address, city, state, postal_code)
409values (null, '04-2222222', 'B', '287A Corporate Ave', 'Wilmington', 'MA', '01887');
410insert into business (cust_id, name, state_id, incorp_date)
411select cust_id, 'Northeast Cooling Inc.', '23-456-789', '2001-01-01' from customer
412where fed_id = '04-2222222';
413insert into officer (officer_id, cust_id, fname, lname,
414 title, start_date)
415select null, cust_id, 'Paul', 'Hardy', 'President', '2001-01-01'
416from customer
417where fed_id = '04-2222222';
418insert into customer (cust_id, fed_id, cust_type_cd,
419 address, city, state, postal_code)
420values (null, '04-3333333', 'B', '789 Main St', 'Salem', 'NH', '03079');
421insert into business (cust_id, name, state_id, incorp_date)
422select cust_id, 'Superior Auto Body', '34-567-890', '2002-06-30' from customer
423where fed_id = '04-3333333';
424insert into officer (officer_id, cust_id, fname, lname,
425 title, start_date)
426select null, cust_id, 'Carl', 'Lutz', 'President', '2002-06-30'
427from customer
428where fed_id = '04-3333333';
429insert into customer (cust_id, fed_id, cust_type_cd,
430 address, city, state, postal_code)
431values (null, '04-4444444', 'B', '4772 Presidential Way', 'Quincy', 'MA', '02169');
432insert into business (cust_id, name, state_id, incorp_date)
433select cust_id, 'AAA Insurance Inc.', '45-678-901', '1999-05-01' from customer
434where fed_id = '04-4444444';
435insert into officer (officer_id, cust_id, fname, lname,
436 title, start_date)
437select null, cust_id, 'Stanley', 'Cheswick', 'President', '1999-05-01'
438from customer
439where fed_id = '04-4444444';
440
441/* residential account data */
442insert into account (account_id, product_cd, cust_id, open_date,
443 last_activity_date, status, open_branch_id,
444 open_emp_id, avail_balance, pending_balance)
445select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',
446 e.branch_id, e.emp_id, a.avail, a.pend
447from customer c cross join
448 (select b.branch_id, e.emp_id
449 from branch b inner join employee e on e.assigned_branch_id = b.branch_id
450 where b.city = 'Woburn' limit 1) e
451 cross join
452 (select 'CHK' prod_cd, '2000-01-15' open_date, '2005-01-04' last_date,
453 1057.75 avail, 1057.75 pend union all
454 select 'SAV' prod_cd, '2000-01-15' open_date, '2004-12-19' last_date,
455 500.00 avail, 500.00 pend union all
456 select 'CD' prod_cd, '2004-06-30' open_date, '2004-06-30' last_date,
457 3000.00 avail, 3000.00 pend) a
458where c.fed_id = '111-11-1111';
459insert into account (account_id, product_cd, cust_id, open_date,
460 last_activity_date, status, open_branch_id,
461 open_emp_id, avail_balance, pending_balance)
462select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',
463 e.branch_id, e.emp_id, a.avail, a.pend
464from customer c cross join
465 (select b.branch_id, e.emp_id
466 from branch b inner join employee e on e.assigned_branch_id = b.branch_id
467 where b.city = 'Woburn' limit 1) e
468 cross join
469 (select 'CHK' prod_cd, '2001-03-12' open_date, '2004-12-27' last_date,
470 2258.02 avail, 2258.02 pend union all
471 select 'SAV' prod_cd, '2001-03-12' open_date, '2004-12-11' last_date,
472 200.00 avail, 200.00 pend) a
473where c.fed_id = '222-22-2222';
474insert into account (account_id, product_cd, cust_id, open_date,
475 last_activity_date, status, open_branch_id,
476 open_emp_id, avail_balance, pending_balance)
477select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',
478 e.branch_id, e.emp_id, a.avail, a.pend
479from customer c cross join
480 (select b.branch_id, e.emp_id
481 from branch b inner join employee e on e.assigned_branch_id = b.branch_id
482 where b.city = 'Quincy' limit 1) e
483 cross join
484 (select 'CHK' prod_cd, '2002-11-23' open_date, '2004-11-30' last_date,
485 1057.75 avail, 1057.75 pend union all
486 select 'MM' prod_cd, '2002-12-15' open_date, '2004-12-05' last_date,
487 2212.50 avail, 2212.50 pend) a
488where c.fed_id = '333-33-3333';
489insert into account (account_id, product_cd, cust_id, open_date,
490 last_activity_date, status, open_branch_id,
491 open_emp_id, avail_balance, pending_balance)
492select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',
493 e.branch_id, e.emp_id, a.avail, a.pend
494from customer c cross join
495 (select b.branch_id, e.emp_id
496 from branch b inner join employee e on e.assigned_branch_id = b.branch_id
497 where b.city = 'Waltham' limit 1) e
498 cross join
499 (select 'CHK' prod_cd, '2003-09-12' open_date, '2005-01-03' last_date,
500 534.12 avail, 534.12 pend union all
501 select 'SAV' prod_cd, '2000-01-15' open_date, '2004-10-24' last_date,
502 767.77 avail, 767.77 pend union all
503 select 'MM' prod_cd, '2004-09-30' open_date, '2004-11-11' last_date,
504 5487.09 avail, 5487.09 pend) a
505where c.fed_id = '444-44-4444';
506insert into account (account_id, product_cd, cust_id, open_date,
507 last_activity_date, status, open_branch_id,
508 open_emp_id, avail_balance, pending_balance)
509select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',
510 e.branch_id, e.emp_id, a.avail, a.pend
511from customer c cross join
512 (select b.branch_id, e.emp_id
513 from branch b inner join employee e on e.assigned_branch_id = b.branch_id
514 where b.city = 'Salem' limit 1) e
515 cross join
516 (select 'CHK' prod_cd, '2004-01-27' open_date, '2005-01-05' last_date,
517 2237.97 avail, 2897.97 pend) a
518where c.fed_id = '555-55-5555';
519insert into account (account_id, product_cd, cust_id, open_date,
520 last_activity_date, status, open_branch_id,
521 open_emp_id, avail_balance, pending_balance)
522select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',
523 e.branch_id, e.emp_id, a.avail, a.pend
524from customer c cross join
525 (select b.branch_id, e.emp_id
526 from branch b inner join employee e on e.assigned_branch_id = b.branch_id
527 where b.city = 'Waltham' limit 1) e
528 cross join
529 (select 'CHK' prod_cd, '2002-08-24' open_date, '2004-11-29' last_date,
530 122.37 avail, 122.37 pend union all
531 select 'CD' prod_cd, '2004-12-28' open_date, '2004-12-28' last_date,
532 10000.00 avail, 10000.00 pend) a
533where c.fed_id = '666-66-6666';
534insert into account (account_id, product_cd, cust_id, open_date,
535 last_activity_date, status, open_branch_id,
536 open_emp_id, avail_balance, pending_balance)
537select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',
538 e.branch_id, e.emp_id, a.avail, a.pend
539from customer c cross join
540 (select b.branch_id, e.emp_id
541 from branch b inner join employee e on e.assigned_branch_id = b.branch_id
542 where b.city = 'Woburn' limit 1) e
543 cross join
544 (select 'CD' prod_cd, '2004-01-12' open_date, '2004-01-12' last_date,
545 5000.00 avail, 5000.00 pend) a
546where c.fed_id = '777-77-7777';
547insert into account (account_id, product_cd, cust_id, open_date,
548 last_activity_date, status, open_branch_id,
549 open_emp_id, avail_balance, pending_balance)
550select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',
551 e.branch_id, e.emp_id, a.avail, a.pend
552from customer c cross join
553 (select b.branch_id, e.emp_id
554 from branch b inner join employee e on e.assigned_branch_id = b.branch_id
555 where b.city = 'Salem' limit 1) e
556 cross join
557 (select 'CHK' prod_cd, '2001-05-23' open_date, '2005-01-03' last_date,
558 3487.19 avail, 3487.19 pend union all
559 select 'SAV' prod_cd, '2001-05-23' open_date, '2004-10-12' last_date,
560 387.99 avail, 387.99 pend) a
561where c.fed_id = '888-88-8888';
562insert into account (account_id, product_cd, cust_id, open_date,
563 last_activity_date, status, open_branch_id,
564 open_emp_id, avail_balance, pending_balance)
565select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',
566 e.branch_id, e.emp_id, a.avail, a.pend
567from customer c cross join
568 (select b.branch_id, e.emp_id
569 from branch b inner join employee e on e.assigned_branch_id = b.branch_id
570 where b.city = 'Waltham' limit 1) e
571 cross join
572 (select 'CHK' prod_cd, '2003-07-30' open_date, '2004-12-15' last_date,
573 125.67 avail, 125.67 pend union all
574 select 'MM' prod_cd, '2004-10-28' open_date, '2004-10-28' last_date,
575 9345.55 avail, 9845.55 pend union all
576 select 'CD' prod_cd, '2004-06-30' open_date, '2004-06-30' last_date,
577 1500.00 avail, 1500.00 pend) a
578where c.fed_id = '999-99-9999';
579
580/* corporate account data */
581insert into account (account_id, product_cd, cust_id, open_date,
582 last_activity_date, status, open_branch_id,
583 open_emp_id, avail_balance, pending_balance)
584select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',
585 e.branch_id, e.emp_id, a.avail, a.pend
586from customer c cross join
587 (select b.branch_id, e.emp_id
588 from branch b inner join employee e on e.assigned_branch_id = b.branch_id
589 where b.city = 'Salem' limit 1) e
590 cross join
591 (select 'CHK' prod_cd, '2002-09-30' open_date, '2004-12-15' last_date,
592 23575.12 avail, 23575.12 pend union all
593 select 'BUS' prod_cd, '2002-10-01' open_date, '2004-08-28' last_date,
594 0 avail, 0 pend) a
595where c.fed_id = '04-1111111';
596insert into account (account_id, product_cd, cust_id, open_date,
597 last_activity_date, status, open_branch_id,
598 open_emp_id, avail_balance, pending_balance)
599select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',
600 e.branch_id, e.emp_id, a.avail, a.pend
601from customer c cross join
602 (select b.branch_id, e.emp_id
603 from branch b inner join employee e on e.assigned_branch_id = b.branch_id
604 where b.city = 'Woburn' limit 1) e
605 cross join
606 (select 'BUS' prod_cd, '2004-03-22' open_date, '2004-11-14' last_date,
607 9345.55 avail, 9345.55 pend) a
608where c.fed_id = '04-2222222';
609insert into account (account_id, product_cd, cust_id, open_date,
610 last_activity_date, status, open_branch_id,
611 open_emp_id, avail_balance, pending_balance)
612select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',
613 e.branch_id, e.emp_id, a.avail, a.pend
614from customer c cross join
615 (select b.branch_id, e.emp_id
616 from branch b inner join employee e on e.assigned_branch_id = b.branch_id
617 where b.city = 'Salem' limit 1) e
618 cross join
619 (select 'CHK' prod_cd, '2003-07-30' open_date, '2004-12-15' last_date,
620 38552.05 avail, 38552.05 pend) a
621where c.fed_id = '04-3333333';
622insert into account (account_id, product_cd, cust_id, open_date,
623 last_activity_date, status, open_branch_id,
624 open_emp_id, avail_balance, pending_balance)
625select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',
626 e.branch_id, e.emp_id, a.avail, a.pend
627from customer c cross join
628 (select b.branch_id, e.emp_id
629 from branch b inner join employee e on e.assigned_branch_id = b.branch_id
630 where b.city = 'Quincy' limit 1) e
631 cross join
632 (select 'SBL' prod_cd, '2004-02-22' open_date, '2004-12-17' last_date,
633 50000.00 avail, 50000.00 pend) a
634where c.fed_id = '04-4444444';
635
636/* put $100 in all checking/savings accounts on date account opened */
637insert into transaction (txn_id, txn_date, account_id, txn_type_cd,
638 amount, funds_avail_date)
639select null, a.open_date, a.account_id, 'CDT', 100, a.open_date
640from account a
641where a.product_cd IN ('CHK','SAV','CD','MM');
642
643/* end data population */