Heapwizard.com

• Join is a query that is used to combine rows from two or more tables

Types of Join :

1. Inner Join (Normal Join)
   • Default join in inner join when
2. Left Join
3. Right Join
4. Outer Join
5. Self Join
6. Cross Join
7. Equi Join (Inner join without join keyword)
8. Anti Join

Example :

reate table temp1_202103071158(
name varchar2(10)
);

create table temp2_202103071158(
name varchar2(10)
);

insert into temp1_202103071158 values('Tyson');
insert into temp1_202103071158 values('Justin');
insert into temp1_202103071158 values('Martin');


insert into temp2_202103071158 values('Jake');
insert into temp2_202103071158 values('Blake');
insert into temp2_202103071158 values('Duke');
insert into temp2_202103071158 values('Tyson');

--truncate table temp1_202103071158;
--truncate table temp2_202103071158;

select * from temp1_202103071158 t1 inner join temp2_202103071158 t2 on (t1.name=t2.name);

select * from temp1_202103071158 t1 left join temp2_202103071158 t2 on (t1.name=t2.name);

select * from temp1_202103071158 t1 right join temp2_202103071158 t2 on (t1.name=t2.name);

select * from temp1_202103071158 t1 full outer join temp2_202103071158 t2 on (t1.name=t2.name);

select * from temp1_202103071158,temp2_202103071158; --cross join

What are synonyms ?

• They are auxiliary names that relate to other database objects: tables, procedures, views, etc.
• They work like Unix hard links; a pointer to point to an object that exists somewhere else.
• Synonyms can be created as PRIVATE (by default) or PUBLIC.
• public synonyms are available to all users in the database.
• private synonyms exist only in specific user schema (they are available only to a user and to grantees for the underlying object)

When to Use Synonyms ?

• As database systems grow and applications improve, there is usually a need to change the names of tables and views to better reflect their new functionality. 

A synonym can be created on the following types of objects:

• table
• view
• stored procedure
• function
• package
• sequence
• materialised view
• synonym
• java class schema object
• user-defined object

Syntax :

CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema.] synonym_name FOR [schema.] object_name [@dblink_name];

Example :

CREATE SYNONYM emp FOR hr.employees;

Reference :

https://www.databasestar.com/oracle-synonym/

• When we are working with databases, we store data in tables. 
• Tables are a collection of the same type of record. A table has columns.
• A “key” is a field in a table that is used in the identification of that record.

Types of key in oracle

• primary keys
  • In Oracle, a primary key is a single field or combination of fields that uniquely defines a record.
  • None of the fields that are part of the primary key can contain a null value. 
  • A table can have only one primary key.
  • In Oracle, a primary key can not contain more than 32 columns.
  • A primary key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.
  • In Oracle you can create, drop, disable and enable a primary key
• Foreign keys
  • Foreign Key used to link two tables together.
  • The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.
  • Foreign key in a column(or a group of columns) of a table which maps to a primary key in another table.
  • Foreign Key may have duplicate & NULL values if it is defined to accept NULL values.
• Composite keys
  • Composite Key refers to a group of two or more columns that can be used to identify a tuple from the table uniquely. 
  • A group of the column in combination with each other can identify a row uniquely but a single column of that group doesn’t promise to identify the row uniquely.
• Unique keys
  • Unique Key can be a field or set of fields that can be used to uniquely identify the tuple from the database.
  • One or more fields can be declared as a unique Key. 
  • The unique Key column can also hold the NULL value.
  • Use of Unique Key improves the performance of data retrieval. It makes searching for records from the database much more faster & efficient.
• Candidate keys
  • Candidate Key can be a column or group of columns that can qualify for the Unique Key. 
  • A table may have one or more Candidate Key. Each Candidate Key can work as a Primary Key if required in certain scenarios.
• Super keys
  • Candidate Key is the subset of the Super Key. 
  • Candidate Key is also known as minimal Super Key.
  • If some more columns are added to a candidate key it can still uniquely identify a record in a table right? so all those possible combinations are super keys
• Alternate Keys
  • Alternate Key is that Key which can be used as a Primary Key if required. 

Reference :

• PrimaryKey
• ForeignKey

What is a Self Join?

A  self-join is simply a normal SQL join that joins one table to itself. Joining a table to itself can be useful when you want to compare values in a column to other values in the same column.

Is Self Join Inner Join or Outer Join?

A self-join can be an inner join or an outer join or even a cross join. A table is joined to itself based upon a column that have duplicate data in different rows.

What is a practical use of the Self Join in the real world?

The best example of self join in the real world is when we have a table with Employee data and each row contains information about employee and his/her manager. You can use self join in this scenario and retrieve relevant information. Let us see an example, over here.

Question In below example : List the employee name followed by manager name

create table emp_temp_20220307_0629(
emp_id number,
emp_name varchar2(20),
manager_id number
);

insert into emp_temp_20220307_0629 values (1,'Mike',3);
insert into emp_temp_20220307_0629 values (2,'David',3);
insert into emp_temp_20220307_0629 values (3,'Roger',NULL);
insert into emp_temp_20220307_0629 values (4,'Marry',2);
insert into emp_temp_20220307_0629 values (5,'Joseph',2);
insert into emp_temp_20220307_0629 values (7,'Ben',2);

Answer

SELECT
    t1.emp_name    AS emp_name,
    t2.emp_name    AS mgr_name
FROM
    emp_temp_20220307_0629  t1
    left JOIN emp_temp_20220307_0629  t2 ON t1.manager_id = t2.emp_id;

Question : Showing the students who have enrolled in two courses using self join

create table student_temp_202103060804(
sid VARCHAR2(10),
cid VARCHAR2(10),
year number
);

insert into student_temp_202103060804 values ('s1','c1','2016');
insert into student_temp_202103060804 values ('s2','c2','2017');
insert into student_temp_202103060804 values ('s1','c2','2018');


select st1.*,st2.* from student_temp_202103060804 st1 join student_temp_202103060804 st2 on (st1.sid = st2.sid and st1.cid <> st2.cid);

Reference :

create table names_table(
name varchar2(100)
);

truncate table names_table;

insert into names_table values ('Bane');
insert into names_table values ('Tyson');
insert into names_table values ('Justin');
insert into names_table values ('Martin');
insert into names_table values ('Jake');
insert into names_table values ('Linda');
insert into names_table values ('Duke');
insert into names_table values ('Fade');
insert into names_table values ('Rick');
insert into names_table values ('Rae');
insert into names_table values ('Cidney');
insert into names_table values ('Jhon');
insert into names_table values ('Simp');
insert into names_table values ('Andrew');
insert into names_table values ('Norman');
insert into names_table values ('Dom');
insert into names_table values ('Blanke');
insert into names_table values ('Blade');
insert into names_table values ('Ted');
insert into names_table values ('Megan');
insert into names_table values ('King');
insert into names_table values ('Laura');

• Distinct
• Unique
• Group by of all columns
• Union of same columns of same table
• Union of null columns from dual table
• Intersect the same table query
• Minus the query with null records of dual

Reference :

Example 1

SQL : Structure Query Language is a means of communicating of Database via set of instructions.

Types of SQL Statement :

• DDL – Data Definition Language
  • Create, Alter, Drop, Truncate
  • These are autocommit commands
• DML – Data Manipulation Language
  • Insert, Update, Delete, Merge
• DCL – Data Control Language
  • Grant, Remove
• TCL – Transaction Control Language
  • Commit, Rollback, Savepoint
• DRL – Data Retrieval Language
  • Select statement

Other variant

• Session Control Statement
  • Alter session (for NLS etc)
• System Control Statement
  • Alter system

• Rank – If two values are exactly same then both the person is assigned same rank but the next rank is skipped
• Dense rank – If two values are exactly same then both the person is assigned same rank but the next rank is not skipped.

Example

select rank() over(order by AVAIL_BALANCE desc),
dense_rank() over(order by AVAIL_BALANCE desc),
ACCOUNT_ID, AVAIL_BALANCE, CLOSE_DATE, LAST_ACTIVITY_DATE, OPEN_DATE, PENDING_BALANCE, STATUS, CUST_ID, OPEN_BRANCH_ID, OPEN_EMP_ID, PRODUCT_CD 
from ACCOUNT;

• Names dump

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;