create table ACCOUNT ( ACCOUNT_ID number(10,0) not null, AVAIL_BALANCE float, CLOSE_DATE date, LAST_ACTIVITY_DATE date, OPEN_DATE date not null, PENDING_BALANCE float, STATUS varchar2(10 char), CUST_ID number(10,0), OPEN_BRANCH_ID number(10,0) not null, OPEN_EMP_ID number(10,0) not null, PRODUCT_CD varchar2(10 char) not null, primary key (ACCOUNT_ID) ); create table ACC_TRANSACTION ( TXN_ID number(19,0) not null, AMOUNT float not null, FUNDS_AVAIL_DATE timestamp not null, TXN_DATE timestamp not null, TXN_TYPE_CD varchar2(10 char), ACCOUNT_ID number(10,0), EXECUTION_BRANCH_ID number(10,0), TELLER_EMP_ID number(10,0), primary key (TXN_ID) ); create table BRANCH ( BRANCH_ID number(10,0) not null, ADDRESS varchar2(30 char), CITY varchar2(20 char), NAME varchar2(20 char) not null, STATE varchar2(10 char), ZIP_CODE varchar2(12 char), primary key (BRANCH_ID) ); create table BUSINESS ( INCORP_DATE date, NAME varchar2(255 char) not null, STATE_ID varchar2(10 char) not null, CUST_ID number(10,0) not null, primary key (CUST_ID) ); create table CUSTOMER ( CUST_ID number(10,0) not null, ADDRESS varchar2(30 char), CITY varchar2(20 char), CUST_TYPE_CD varchar2(1 char) not null, FED_ID varchar2(12 char) not null, POSTAL_CODE varchar2(10 char), STATE varchar2(20 char), primary key (CUST_ID) ); create table DEPARTMENT ( DEPT_ID number(10,0) not null, NAME varchar2(20 char) not null, primary key (DEPT_ID) ); create table EMPLOYEE ( EMP_ID number(10,0) not null, END_DATE date, FIRST_NAME varchar2(20 char) not null, LAST_NAME varchar2(20 char) not null, START_DATE date not null, TITLE varchar2(20 char), ASSIGNED_BRANCH_ID number(10,0), DEPT_ID number(10,0), SUPERIOR_EMP_ID number(10,0), primary key (EMP_ID) ); create table INDIVIDUAL ( BIRTH_DATE date, FIRST_NAME varchar2(30 char) not null, LAST_NAME varchar2(30 char) not null, CUST_ID number(10,0) not null, primary key (CUST_ID) ); create table OFFICER ( OFFICER_ID number(10,0) not null, END_DATE date, FIRST_NAME varchar2(30 char) not null, LAST_NAME varchar2(30 char) not null, START_DATE date not null, TITLE varchar2(20 char), CUST_ID number(10,0), primary key (OFFICER_ID) ); create table PRODUCT ( PRODUCT_CD varchar2(10 char) not null, DATE_OFFERED date, DATE_RETIRED date, NAME varchar2(50 char) not null, PRODUCT_TYPE_CD varchar2(255 char), primary key (PRODUCT_CD) ); create table PRODUCT_TYPE ( PRODUCT_TYPE_CD varchar2(255 char) not null, NAME varchar2(50 char), primary key (PRODUCT_TYPE_CD) ); alter table ACCOUNT add constraint ACCOUNT_CUSTOMER_FK foreign key (CUST_ID) references CUSTOMER; alter table ACCOUNT add constraint ACCOUNT_BRANCH_FK foreign key (OPEN_BRANCH_ID) references BRANCH; alter table ACCOUNT add constraint ACCOUNT_EMPLOYEE_FK foreign key (OPEN_EMP_ID) references EMPLOYEE; alter table ACCOUNT add constraint ACCOUNT_PRODUCT_FK foreign key (PRODUCT_CD) references PRODUCT; alter table ACC_TRANSACTION add constraint ACC_TRANSACTION_ACCOUNT_FK foreign key (ACCOUNT_ID) references ACCOUNT; alter table ACC_TRANSACTION add constraint ACC_TRANSACTION_BRANCH_FK foreign key (EXECUTION_BRANCH_ID) references BRANCH; alter table ACC_TRANSACTION add constraint ACC_TRANSACTION_EMPLOYEE_FK foreign key (TELLER_EMP_ID) references EMPLOYEE; alter table BUSINESS add constraint BUSINESS_EMPLOYEE_FK foreign key (CUST_ID) references CUSTOMER; alter table EMPLOYEE add constraint EMPLOYEE_BRANCH_FK foreign key (ASSIGNED_BRANCH_ID) references BRANCH; alter table EMPLOYEE add constraint EMPLOYEE_DEPARTMENT_FK foreign key (DEPT_ID) references DEPARTMENT; alter table EMPLOYEE add constraint EMPLOYEE_EMPLOYEE_FK foreign key (SUPERIOR_EMP_ID) references EMPLOYEE; alter table INDIVIDUAL add constraint INDIVIDUAL_CUSTOMER_FK foreign key (CUST_ID) references CUSTOMER; alter table OFFICER add constraint OFFICER_CUSTOMER_FK foreign key (CUST_ID) references CUSTOMER; alter table PRODUCT add constraint PRODUCT_PRODUCT_TYPE_FK foreign key (PRODUCT_TYPE_CD) references PRODUCT_TYPE; create sequence hibernate_sequence; -- ======================================================================== -- ======================================================================== -- ======================================================================== -- begin data population -- department data --------------------- insert into department (dept_id, name) values (1, 'Operations'); --------------------- insert into department (dept_id, name) values (2, 'Loans'); --------------------- insert into department (dept_id, name) values (3, 'Administration'); insert into department (dept_id, name) values (4, 'IT'); /* branch data */ --------------------- insert into branch (branch_id, name, address, city, state, Zip_Code) values (1, 'Headquarters', '3882 Main St.', 'Waltham', 'MA', '02451'); --------------------- insert into branch (branch_id, name, address, city, state, Zip_Code) values (2, 'Woburn Branch', '422 Maple St.', 'Woburn', 'MA', '01801'); --------------------- insert into branch (branch_id, name, address, city, state, Zip_Code) values (3, 'Quincy Branch', '125 Presidential Way', 'Quincy', 'MA', '02169'); --------------------- insert into branch (branch_id, name, address, city, state, Zip_Code) values (4, 'So. NH Branch', '378 Maynard Ln.', 'Salem', 'NH', '03079'); /* employee data */ --------------------- insert into employee (emp_id, First_Name, Last_Name, start_date, dept_id, title, assigned_branch_id) values (1, 'Michael', 'Smith', to_date('2001-06-22','yyyy-MM-dd'), (select dept_id from department where name = 'Administration'), 'President', (select branch_id from branch where name = 'Headquarters')); --------------------- insert into employee (emp_id, First_Name, Last_Name, start_date, dept_id, title, assigned_branch_id) values (2, 'Susan', 'Barker',to_date( '2002-09-12','yyyy-MM-dd'), (select dept_id from department where name = 'Administration'), 'Vice President', (select branch_id from branch where name = 'Headquarters')); --------------------- insert into employee (emp_id, First_Name, Last_Name, start_date, dept_id, title, assigned_branch_id) values (3, 'Robert', 'Tyler',to_date( '2000-02-09','yyyy-MM-dd'), (select dept_id from department where name = 'Administration'), 'Treasurer', (select branch_id from branch where name = 'Headquarters')); --------------------- insert into employee (emp_id, First_Name, Last_Name, start_date, dept_id, title, assigned_branch_id) values (4, 'Susan', 'Hawthorne',to_date( '2002-04-24','yyyy-MM-dd'), (select dept_id from department where name = 'Operations'), 'Operations Manager', (select branch_id from branch where name = 'Headquarters')); --------------------- insert into employee (emp_id, First_Name, Last_Name, start_date, dept_id, title, assigned_branch_id) values (5, 'John', 'Gooding',to_date( '2003-11-14','yyyy-MM-dd'), (select dept_id from department where name = 'Loans'), 'Loan Manager', (select branch_id from branch where name = 'Headquarters')); --------------------- insert into employee (emp_id, First_Name, Last_Name, start_date, dept_id, title, assigned_branch_id) values (6, 'Helen', 'Fleming',to_date( '2004-03-17','yyyy-MM-dd'), (select dept_id from department where name = 'Operations'), 'Head Teller', (select branch_id from branch where name = 'Headquarters')); --------------------- insert into employee (emp_id, First_Name, Last_Name, start_date, dept_id, title, assigned_branch_id) values (7, 'Chris', 'Tucker',to_date( '2004-09-15','yyyy-MM-dd'), (select dept_id from department where name = 'Operations'), 'Teller', (select branch_id from branch where name = 'Headquarters')); --------------------- insert into employee (emp_id, First_Name, Last_Name, start_date, dept_id, title, assigned_branch_id) values (8, 'Sarah', 'Parker',to_date( '2002-12-02','yyyy-MM-dd'), (select dept_id from department where name = 'Operations'), 'Teller', (select branch_id from branch where name = 'Headquarters')); --------------------- insert into employee (emp_id, First_Name, Last_Name, start_date, dept_id, title, assigned_branch_id) values (9, 'Jane', 'Grossman',to_date( '2002-05-03','yyyy-MM-dd'), (select dept_id from department where name = 'Operations'), 'Teller', (select branch_id from branch where name = 'Headquarters')); --------------------- insert into employee (emp_id, First_Name, Last_Name, start_date, dept_id, title, assigned_branch_id) values (10, 'Paula', 'Roberts',to_date( '2002-07-27','yyyy-MM-dd'), (select dept_id from department where name = 'Operations'), 'Head Teller', (select branch_id from branch where name = 'Woburn Branch')); --------------------- insert into employee (emp_id, First_Name, Last_Name, start_date, dept_id, title, assigned_branch_id) values (11, 'Thomas', 'Ziegler',to_date( '2000-10-23','yyyy-MM-dd'), (select dept_id from department where name = 'Operations'), 'Teller', (select branch_id from branch where name = 'Woburn Branch')); --------------------- insert into employee (emp_id, First_Name, Last_Name, start_date, dept_id, title, assigned_branch_id) values (12, 'Samantha', 'Jameson',to_date( '2003-01-08','yyyy-MM-dd'), (select dept_id from department where name = 'Operations'), 'Teller', (select branch_id from branch where name = 'Woburn Branch')); --------------------- insert into employee (emp_id, First_Name, Last_Name, start_date, dept_id, title, assigned_branch_id) values (13, 'John', 'Blake',to_date( '2000-05-11','yyyy-MM-dd'), (select dept_id from department where name = 'Operations'), 'Head Teller', (select branch_id from branch where name = 'Quincy Branch')); --------------------- insert into employee (emp_id, First_Name, Last_Name, start_date, dept_id, title, assigned_branch_id) values (14, 'Cindy', 'Mason',to_date( '2002-08-09','yyyy-MM-dd'), (select dept_id from department where name = 'Operations'), 'Teller', (select branch_id from branch where name = 'Quincy Branch')); --------------------- insert into employee (emp_id, First_Name, Last_Name, start_date, dept_id, title, assigned_branch_id) values (15, 'Frank', 'Portman',to_date( '2003-04-01','yyyy-MM-dd'), (select dept_id from department where name = 'Operations'), 'Teller', (select branch_id from branch where name = 'Quincy Branch')); --------------------- insert into employee (emp_id, First_Name, Last_Name, start_date, dept_id, title, assigned_branch_id) values (16, 'Theresa', 'Markham',to_date( '2001-03-15','yyyy-MM-dd'), (select dept_id from department where name = 'Operations'), 'Head Teller', (select branch_id from branch where name = 'So. NH Branch')); --------------------- insert into employee (emp_id, First_Name, Last_Name, start_date, dept_id, title, assigned_branch_id) values (17, 'Beth', 'Fowler',to_date( '2002-06-29','yyyy-MM-dd'), (select dept_id from department where name = 'Operations'), 'Teller', (select branch_id from branch where name = 'So. NH Branch')); --------------------- insert into employee (emp_id, First_Name, Last_Name, start_date, dept_id, title, assigned_branch_id) values (18, 'Rick', 'Tulman',to_date( '2002-12-12','yyyy-MM-dd'), (select dept_id from department where name = 'Operations'), 'Teller', (select branch_id from branch where name = 'So. NH Branch')); /* create data for self-referencing foreign key 'superior_emp_id' */ create table emp_tmp as select emp_id, First_Name, Last_Name from employee; --------------------- update employee set superior_emp_id = (select emp_id from emp_tmp where Last_Name = 'Smith' and First_Name = 'Michael') where ((Last_Name = 'Barker' and First_Name = 'Susan') or (Last_Name = 'Tyler' and First_Name = 'Robert')); --------------------- update employee set superior_emp_id = (select emp_id from emp_tmp where Last_Name = 'Tyler' and First_Name = 'Robert') where Last_Name = 'Hawthorne' and First_Name = 'Susan'; --------------------- update employee set superior_emp_id = (select emp_id from emp_tmp where Last_Name = 'Hawthorne' and First_Name = 'Susan') where ((Last_Name = 'Gooding' and First_Name = 'John') or (Last_Name = 'Fleming' and First_Name = 'Helen') or (Last_Name = 'Roberts' and First_Name = 'Paula') or (Last_Name = 'Blake' and First_Name = 'John') or (Last_Name = 'Markham' and First_Name = 'Theresa')); --------------------- update employee set superior_emp_id = (select emp_id from emp_tmp where Last_Name = 'Fleming' and First_Name = 'Helen') where ((Last_Name = 'Tucker' and First_Name = 'Chris') or (Last_Name = 'Parker' and First_Name = 'Sarah') or (Last_Name = 'Grossman' and First_Name = 'Jane')); --------------------- update employee set superior_emp_id = (select emp_id from emp_tmp where Last_Name = 'Roberts' and First_Name = 'Paula') where ((Last_Name = 'Ziegler' and First_Name = 'Thomas') or (Last_Name = 'Jameson' and First_Name = 'Samantha')); --------------------- update employee set superior_emp_id = (select emp_id from emp_tmp where Last_Name = 'Blake' and First_Name = 'John') where ((Last_Name = 'Mason' and First_Name = 'Cindy') or (Last_Name = 'Portman' and First_Name = 'Frank')); --------------------- update employee set superior_emp_id = (select emp_id from emp_tmp where Last_Name = 'Markham' and First_Name = 'Theresa') where ((Last_Name = 'Fowler' and First_Name = 'Beth') or (Last_Name = 'Tulman' and First_Name = 'Rick')); drop table emp_tmp; /* product type data */ --------------------- insert into product_type (product_type_cd, name) values ('ACCOUNT','Customer Accounts'); --------------------- insert into product_type (product_type_cd, name) values ('LOAN','Individual and Business Loans'); --------------------- insert into product_type (product_type_cd, name) values ('INSURANCE','Insurance Offerings'); /* product data */ --------------------- insert into product (product_cd, name, product_type_cd, date_offered) values ('CHK','checking account','ACCOUNT',to_date('2000-01-01','yyyy-MM-dd')); --------------------- insert into product (product_cd, name, product_type_cd, date_offered) values ('SAV','savings account','ACCOUNT',to_date('2000-01-01','yyyy-MM-dd')); --------------------- insert into product (product_cd, name, product_type_cd, date_offered) values ('MM','money market account','ACCOUNT',to_date('2000-01-01','yyyy-MM-dd')); --------------------- insert into product (product_cd, name, product_type_cd, date_offered) values ('CD','certificate of deposit','ACCOUNT',to_date('2000-01-01','yyyy-MM-dd')); --------------------- insert into product (product_cd, name, product_type_cd, date_offered) values ('MRT','home mortgage','LOAN',to_date('2000-01-01','yyyy-MM-dd')); --------------------- insert into product (product_cd, name, product_type_cd, date_offered) values ('AUT','auto loan','LOAN',to_date('2000-01-01','yyyy-MM-dd')); --------------------- insert into product (product_cd, name, product_type_cd, date_offered) values ('BUS','business line of credit','LOAN',to_date('2000-01-01','yyyy-MM-dd')); --------------------- insert into product (product_cd, name, product_type_cd, date_offered) values ('SBL','small business loan','LOAN',to_date('2000-01-01','yyyy-MM-dd')); /* residential customer data */ --------------------- insert into customer (cust_id, fed_id, cust_type_cd, address, city, state, postal_code) values (1, '111-11-1111', 'I', '47 Mockingbird Ln', 'Lynnfield', 'MA', '01940'); --------------------- insert into individual (cust_id, First_Name, Last_Name, birth_date) select cust_id, 'James', 'Hadley', to_date('1972-04-22','yyyy-MM-dd') from customer where fed_id = '111-11-1111'; --------------------- insert into customer (cust_id, fed_id, cust_type_cd, address, city, state, postal_code) values (2, '222-22-2222', 'I', '372 Clearwater Blvd', 'Woburn', 'MA', '01801'); --------------------- insert into individual (cust_id, First_Name, Last_Name, birth_date) select cust_id, 'Susan', 'Tingley', to_date('1968-08-15','yyyy-MM-dd') from customer where fed_id = '222-22-2222'; --------------------- insert into customer (cust_id, fed_id, cust_type_cd, address, city, state, postal_code) values (3, '333-33-3333', 'I', '18 Jessup Rd', 'Quincy', 'MA', '02169'); --------------------- insert into individual (cust_id, First_Name, Last_Name, birth_date) select cust_id, 'Frank', 'Tucker',to_date( '1958-02-06','yyyy-MM-dd') from customer where fed_id = '333-33-3333'; --------------------- insert into customer (cust_id, fed_id, cust_type_cd, address, city, state, postal_code) values (4, '444-44-4444', 'I', '12 Buchanan Ln', 'Waltham', 'MA', '02451'); --------------------- insert into individual (cust_id, First_Name, Last_Name, birth_date) select cust_id, 'John', 'Hayward',to_date('1966-12-22','yyyy-MM-dd') from customer where fed_id = '444-44-4444'; --------------------- insert into customer (cust_id, fed_id, cust_type_cd, address, city, state, postal_code) values (5, '555-55-5555', 'I', '2341 Main St', 'Salem', 'NH', '03079'); --------------------- insert into individual (cust_id, First_Name, Last_Name, birth_date) select cust_id, 'Charles', 'Frasier',to_date( '1971-08-25','yyyy-MM-dd') from customer where fed_id = '555-55-5555'; --------------------- insert into customer (cust_id, fed_id, cust_type_cd, address, city, state, postal_code) values (6, '666-66-6666', 'I', '12 Blaylock Ln', 'Waltham', 'MA', '02451'); --------------------- insert into individual (cust_id, First_Name, Last_Name, birth_date) select cust_id, 'John', 'Spencer',to_date( '1962-09-14','yyyy-MM-dd')from customer where fed_id = '666-66-6666'; --------------------- insert into customer (cust_id, fed_id, cust_type_cd, address, city, state, postal_code) values (7, '777-77-7777', 'I', '29 Admiral Ln', 'Wilmington', 'MA', '01887'); --------------------- insert into individual (cust_id, First_Name, Last_Name, birth_date) select cust_id, 'Margaret', 'Young',to_date( '1947-03-19' ,'yyyy-MM-dd')from customer where fed_id = '777-77-7777'; --------------------- insert into customer (cust_id, fed_id, cust_type_cd, address, city, state, postal_code) values (8, '888-88-8888', 'I', '472 Freedom Rd', 'Salem', 'NH', '03079'); --------------------- insert into individual (cust_id, First_Name, Last_Name, birth_date) select cust_id, 'Louis', 'Blake',to_date( '1977-07-01' ,'yyyy-MM-dd')from customer where fed_id = '888-88-8888'; --------------------- insert into customer (cust_id, fed_id, cust_type_cd, address, city, state, postal_code) values (9, '999-99-9999', 'I', '29 Maple St', 'Newton', 'MA', '02458'); --------------------- insert into individual (cust_id, First_Name, Last_Name, birth_date) select cust_id, 'Richard', 'Farley',to_date( '1968-06-16','yyyy-MM-dd') from customer where fed_id = '999-99-9999'; /* corporate customer data */ --------------------- insert into customer (cust_id, fed_id, cust_type_cd, address, city, state, postal_code) values (10, '04-1111111', 'B', '7 Industrial Way', 'Salem', 'NH', '03079'); --------------------- insert into business (cust_id, name, state_id, incorp_date) select cust_id, 'Chilton Engineering', '12-345-678',to_date( '1995-05-01','yyyy-MM-dd') from customer where fed_id = '04-1111111'; --------------------- insert into officer (officer_id, cust_id, First_Name, Last_Name, title, start_date) select 1, cust_id, 'John', 'Chilton', 'President', to_date('1995-05-01','yyyy-MM-dd') from customer where fed_id = '04-1111111'; --------------------- insert into customer (cust_id, fed_id, cust_type_cd, address, city, state, postal_code) values (11, '04-2222222', 'B', '287A Corporate Ave', 'Wilmington', 'MA', '01887'); --------------------- insert into business (cust_id, name, state_id, incorp_date) select cust_id, 'Northeast Cooling Inc.', '23-456-789',to_date( '2001-01-01' ,'yyyy-MM-dd')from customer where fed_id = '04-2222222'; --------------------- insert into officer (officer_id, cust_id, First_Name, Last_Name, title, start_date) select 2, cust_id, 'Paul', 'Hardy', 'President',to_date( '2001-01-01','yyyy-MM-dd') from customer where fed_id = '04-2222222'; --------------------- insert into customer (cust_id, fed_id, cust_type_cd, address, city, state, postal_code) values (12, '04-3333333', 'B', '789 Main St', 'Salem', 'NH', '03079'); --------------------- insert into business (cust_id, name, state_id, incorp_date) select cust_id, 'Superior Auto Body', '34-567-890',to_date( '2002-06-30','yyyy-MM-dd') from customer where fed_id = '04-3333333'; --------------------- insert into officer (officer_id, cust_id, First_Name, Last_Name, title, start_date) select 3, cust_id, 'Carl', 'Lutz', 'President',to_date( '2002-06-30','yyyy-MM-dd') from customer where fed_id = '04-3333333'; --------------------- insert into customer (cust_id, fed_id, cust_type_cd, address, city, state, postal_code) values (13, '04-4444444', 'B', '4772 Presidential Way', 'Quincy', 'MA', '02169'); --------------------- insert into business (cust_id, name, state_id, incorp_date) select cust_id, 'AAA Insurance Inc.', '45-678-901',to_date( '1999-05-01','yyyy-MM-dd') from customer where fed_id = '04-4444444'; --------------------- insert into officer (officer_id, cust_id, First_Name, Last_Name, title, start_date) select 4, cust_id, 'Stanley', 'Cheswick', 'President',to_date( '1999-05-01','yyyy-MM-dd') from customer where fed_id = '04-4444444'; /* residential account data */ --------------------- insert into account (account_id, product_cd, cust_id, open_date, last_activity_date, status, open_branch_id, open_emp_id, avail_balance, pending_balance) select hibernate_sequence.nextval, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE', e.branch_id, e.emp_id, a.avail, a.pend from customer c cross join (select b.branch_id, e.emp_id from branch b inner join employee e on e.assigned_branch_id = b.branch_id where b.city = 'Woburn' and rownum= 1) e cross join (select 'CHK' prod_cd,to_date( '2000-01-15','yyyy-MM-dd') open_date,to_date( '2005-01-04','yyyy-MM-dd') last_date, 1057.75 avail, 1057.75 pend from dual union all select 'SAV' prod_cd,to_date( '2000-01-15','yyyy-MM-dd') open_date,to_date( '2004-12-19','yyyy-MM-dd') last_date, 500.00 avail, 500.00 pend from dual union all select 'CD' prod_cd,to_date( '2004-06-30','yyyy-MM-dd') open_date,to_date( '2004-06-30' ,'yyyy-MM-dd')last_date, 3000.00 avail, 3000.00 pend from dual ) a where c.fed_id = '111-11-1111'; --------------------- insert into account (account_id, product_cd, cust_id, open_date, last_activity_date, status, open_branch_id, open_emp_id, avail_balance, pending_balance) select hibernate_sequence.nextval, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE', e.branch_id, e.emp_id, a.avail, a.pend from customer c cross join (select b.branch_id, e.emp_id from branch b inner join employee e on e.assigned_branch_id = b.branch_id where b.city = 'Woburn' and rownum = 1) e cross join (select 'CHK' prod_cd,to_date( '2001-03-12','yyyy-MM-dd') open_date,to_date( '2004-12-27' ,'yyyy-MM-dd')last_date, 2258.02 avail, 2258.02 pend from dual union all select 'SAV' prod_cd,to_date( '2001-03-12','yyyy-MM-dd') open_date,to_date( '2004-12-11' ,'yyyy-MM-dd')last_date, 200.00 avail, 200.00 pend from dual ) a where c.fed_id = '222-22-2222'; --------------------- insert into account (account_id, product_cd, cust_id, open_date, last_activity_date, status, open_branch_id, open_emp_id, avail_balance, pending_balance) select hibernate_sequence.nextval, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE', e.branch_id, e.emp_id, a.avail, a.pend from customer c cross join (select b.branch_id, e.emp_id from branch b inner join employee e on e.assigned_branch_id = b.branch_id where b.city = 'Quincy' and rownum = 1) e cross join (select 'CHK' prod_cd,to_date( '2002-11-23' ,'yyyy-MM-dd')open_date,to_date( '2004-11-30' ,'yyyy-MM-dd')last_date, 1057.75 avail, 1057.75 pend from dual union all select 'MM' prod_cd,to_date( '2002-12-15','yyyy-MM-dd') open_date,to_date( '2004-12-05' ,'yyyy-MM-dd')last_date, 2212.50 avail, 2212.50 pend from dual ) a where c.fed_id = '333-33-3333'; --------------------- insert into account (account_id, product_cd, cust_id, open_date, last_activity_date, status, open_branch_id, open_emp_id, avail_balance, pending_balance) select hibernate_sequence.nextval, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE', e.branch_id, e.emp_id, a.avail, a.pend from customer c cross join (select b.branch_id, e.emp_id from branch b inner join employee e on e.assigned_branch_id = b.branch_id where b.city = 'Waltham' and rownum = 1) e cross join (select 'CHK' prod_cd,to_date( '2003-09-12' ,'yyyy-MM-dd')open_date,to_date( '2005-01-03' ,'yyyy-MM-dd')last_date, 534.12 avail, 534.12 pend from dual union all select 'SAV' prod_cd,to_date( '2000-01-15' ,'yyyy-MM-dd')open_date,to_date( '2004-10-24','yyyy-MM-dd') last_date, 767.77 avail, 767.77 pend from dual union all select 'MM' prod_cd,to_date( '2004-09-30','yyyy-MM-dd') open_date,to_date( '2004-11-11' ,'yyyy-MM-dd')last_date, 5487.09 avail, 5487.09 pend from dual) a where c.fed_id = '444-44-4444'; --------------------- insert into account (account_id, product_cd, cust_id, open_date, last_activity_date, status, open_branch_id, open_emp_id, avail_balance, pending_balance) select hibernate_sequence.nextval, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE', e.branch_id, e.emp_id, a.avail, a.pend from customer c cross join (select b.branch_id, e.emp_id from branch b inner join employee e on e.assigned_branch_id = b.branch_id where b.city = 'Salem' and rownum = 1) e cross join (select 'CHK' prod_cd, to_date('2004-01-27' ,'yyyy-MM-dd')open_date,to_date( '2005-01-05' ,'yyyy-MM-dd')last_date, 2237.97 avail, 2897.97 pend from dual) a where c.fed_id = '555-55-5555'; --------------------- insert into account (account_id, product_cd, cust_id, open_date, last_activity_date, status, open_branch_id, open_emp_id, avail_balance, pending_balance) select hibernate_sequence.nextval, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE', e.branch_id, e.emp_id, a.avail, a.pend from customer c cross join (select b.branch_id, e.emp_id from branch b inner join employee e on e.assigned_branch_id = b.branch_id where b.city = 'Waltham' and rownum = 1) e cross join (select 'CHK' prod_cd,to_date( '2002-08-24' ,'yyyy-MM-dd')open_date,to_date( '2004-11-29','yyyy-MM-dd') last_date, 122.37 avail, 122.37 pend from dual union all select 'CD' prod_cd,to_date( '2004-12-28' ,'yyyy-MM-dd')open_date,to_date( '2004-12-28','yyyy-MM-dd') last_date, 10000.00 avail, 10000.00 pend from dual ) a where c.fed_id = '666-66-6666'; --------------------- insert into account (account_id, product_cd, cust_id, open_date, last_activity_date, status, open_branch_id, open_emp_id, avail_balance, pending_balance) select hibernate_sequence.nextval, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE', e.branch_id, e.emp_id, a.avail, a.pend from customer c cross join (select b.branch_id, e.emp_id from branch b inner join employee e on e.assigned_branch_id = b.branch_id where b.city = 'Woburn' and rownum = 1) e cross join (select 'CD' prod_cd,to_date( '2004-01-12' ,'yyyy-MM-dd')open_date, to_date('2004-01-12' ,'yyyy-MM-dd')last_date, 5000.00 avail, 5000.00 pend from dual) a where c.fed_id = '777-77-7777'; --------------------- insert into account (account_id, product_cd, cust_id, open_date, last_activity_date, status, open_branch_id, open_emp_id, avail_balance, pending_balance) select hibernate_sequence.nextval, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE', e.branch_id, e.emp_id, a.avail, a.pend from customer c cross join (select b.branch_id, e.emp_id from branch b inner join employee e on e.assigned_branch_id = b.branch_id where b.city = 'Salem' and rownum = 1) e cross join (select 'CHK' prod_cd,to_date( '2001-05-23' ,'yyyy-MM-dd')open_date,to_date( '2005-01-03' ,'yyyy-MM-dd')last_date, 3487.19 avail, 3487.19 pend from dual union all select 'SAV' prod_cd,to_date( '2001-05-23' ,'yyyy-MM-dd')open_date,to_date( '2004-10-12' ,'yyyy-MM-dd')last_date, 387.99 avail, 387.99 pend from dual ) a where c.fed_id = '888-88-8888'; --------------------- insert into account (account_id, product_cd, cust_id, open_date, last_activity_date, status, open_branch_id, open_emp_id, avail_balance, pending_balance) select hibernate_sequence.nextval, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE', e.branch_id, e.emp_id, a.avail, a.pend from customer c cross join (select b.branch_id, e.emp_id from branch b inner join employee e on e.assigned_branch_id = b.branch_id where b.city = 'Waltham' and rownum = 1) e cross join (select 'CHK' prod_cd,to_date( '2003-07-30','yyyy-MM-dd') open_date,to_date( '2004-12-15' ,'yyyy-MM-dd')last_date, 125.67 avail, 125.67 pend from dual union all select 'MM' prod_cd,to_date( '2004-10-28' ,'yyyy-MM-dd')open_date,to_date( '2004-10-28' ,'yyyy-MM-dd')last_date, 9345.55 avail, 9845.55 pend from dual union all select 'CD' prod_cd,to_date( '2004-06-30' ,'yyyy-MM-dd')open_date,to_date( '2004-06-30' ,'yyyy-MM-dd')last_date, 1500.00 avail, 1500.00 pend from dual ) a where c.fed_id = '999-99-9999'; /* corporate account data */ --------------------- insert into account (account_id, product_cd, cust_id, open_date, last_activity_date, status, open_branch_id, open_emp_id, avail_balance, pending_balance) select hibernate_sequence.nextval, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE', e.branch_id, e.emp_id, a.avail, a.pend from customer c cross join (select b.branch_id, e.emp_id from branch b inner join employee e on e.assigned_branch_id = b.branch_id where b.city = 'Salem' and rownum = 1) e cross join (select 'CHK' prod_cd,to_date( '2002-09-30' ,'yyyy-MM-dd')open_date, to_date('2004-12-15' ,'yyyy-MM-dd')last_date, 23575.12 avail, 23575.12 pend from dual union all select 'BUS' prod_cd,to_date( '2002-10-01' ,'yyyy-MM-dd')open_date,to_date( '2004-08-28' ,'yyyy-MM-dd')last_date, 0 avail, 0 pend from dual ) a where c.fed_id = '04-1111111'; --------------------- insert into account (account_id, product_cd, cust_id, open_date, last_activity_date, status, open_branch_id, open_emp_id, avail_balance, pending_balance) select hibernate_sequence.nextval, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE', e.branch_id, e.emp_id, a.avail, a.pend from customer c cross join (select b.branch_id, e.emp_id from branch b inner join employee e on e.assigned_branch_id = b.branch_id where b.city = 'Woburn' and rownum = 1) e cross join (select 'BUS' prod_cd,to_date( '2004-03-22','yyyy-MM-dd') open_date,to_date( '2004-11-14','yyyy-MM-dd') last_date, 9345.55 avail, 9345.55 pend from dual) a where c.fed_id = '04-2222222'; --------------------- insert into account (account_id, product_cd, cust_id, open_date, last_activity_date, status, open_branch_id, open_emp_id, avail_balance, pending_balance) select hibernate_sequence.nextval, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE', e.branch_id, e.emp_id, a.avail, a.pend from customer c cross join (select b.branch_id, e.emp_id from branch b inner join employee e on e.assigned_branch_id = b.branch_id where b.city = 'Salem' and rownum = 1) e cross join (select 'CHK' prod_cd,to_date( '2003-07-30','yyyy-MM-dd') open_date,to_date( '2004-12-15' ,'yyyy-MM-dd')last_date, 38552.05 avail, 38552.05 pend from dual) a where c.fed_id = '04-3333333'; --------------------- insert into account (account_id, product_cd, cust_id, open_date, last_activity_date, status, open_branch_id, open_emp_id, avail_balance, pending_balance) select hibernate_sequence.nextval, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE', e.branch_id, e.emp_id, a.avail, a.pend from customer c cross join (select b.branch_id, e.emp_id from branch b inner join employee e on e.assigned_branch_id = b.branch_id where b.city = 'Quincy' and rownum = 1) e cross join (select 'SBL' prod_cd,to_date( '2004-02-22','yyyy-MM-dd') open_date,to_date( '2004-12-17','yyyy-MM-dd') last_date, 50000.00 avail, 50000.00 pend from dual) a where c.fed_id = '04-4444444'; -- put $100 in all checking/savings accounts on date account opened --------------------- insert into acc_transaction (txn_id, txn_date, account_id, txn_type_cd, amount, funds_avail_date) select hibernate_sequence.nextval, a.open_date, a.account_id, 'CDT', 100, a.open_date from account a where a.product_cd IN ('CHK','SAV','CD','MM'); -- end data population Commit;