select * from ACC_TRANSACTION;
select * from ACCOUNT;
select * from EMPLOYEE;
select * from DEPARTMENT;
select * from BRANCH;
select * from OFFICER;
select * from PRODUCT;
select * from PRODUCT_TYPE;
select * from CUSTOMER;
select * from BUSINESS;
select * from INDIVIDUAL;
drop table ACC_TRANSACTION CASCADE CONSTRAINTS;
drop table ACCOUNT CASCADE CONSTRAINTS;
drop table EMPLOYEE CASCADE CONSTRAINTS;
drop table DEPARTMENT CASCADE CONSTRAINTS;
drop table BRANCH CASCADE CONSTRAINTS;
drop table OFFICER CASCADE CONSTRAINTS;
drop table PRODUCT CASCADE CONSTRAINTS;
drop table PRODUCT_TYPE CASCADE CONSTRAINTS;
drop table CUSTOMER CASCADE CONSTRAINTS;
drop table BUSINESS CASCADE CONSTRAINTS;
drop table INDIVIDUAL CASCADE CONSTRAINTS;
drop sequence hibernate_sequence;
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;