Oracle-Script.sql

    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;