ORACLE JOIN

Oracle Join


> Join Demo Tables

CREATE TABLE person (
person_id NUMBER(10),
first_name VARCHAR2(25) NOT NULL,
last_name VARCHAR2(25) NOT NULL,
title_1 VARCHAR2(5),
title_2 VARCHAR2(5))
PCTFREE 20;

ALTER TABLE person
ADD CONSTRAINT pk_person
PRIMARY KEY (person_id)
USING INDEX
PCTFREE 5;

CREATE TABLE person_role (
role_id VARCHAR2(1),
role_name VARCHAR2(20) NOT NULL);

ALTER TABLE person_role
ADD CONSTRAINT pk_role
PRIMARY KEY (role_id)
USING INDEX
PCTFREE 5;

CREATE TABLE person_role_ie (
person_role_id NUMBER(10),
person_id NUMBER(10) NOT NULL,
role_id VARCHAR2(1) NOT NULL);

ALTER TABLE person_role_ie
ADD CONSTRAINT pk_person_role_ie
PRIMARY KEY (person_role_id)
USING INDEX
PCTFREE 5;

CREATE TABLE title (
title_abbrev VARCHAR2(5),
title_name VARCHAR2(20))
PCTFREE 0;

ALTER TABLE title
ADD CONSTRAINT pk_title
PRIMARY KEY (title_abbrev)
USING INDEX
PCTFREE 0;

ALTER TABLE person_role_ie
ADD CONSTRAINT fk_person_role_ie_person
FOREIGN KEY (person_id)
REFERENCES person(person_id);

ALTER TABLE person_role_ie
ADD CONSTRAINT fk_person_role_ie_role
FOREIGN KEY (role_id)
REFERENCES person_role(role_id);

ALTER TABLE person
ADD CONSTRAINT fk_person_title1
FOREIGN KEY (title_1)
REFERENCES title(title_abbrev);

ALTER TABLE person
ADD CONSTRAINT fk_person_title2
FOREIGN KEY (title_2)
REFERENCES title(title_abbrev);


Need more on : http://psoug.org/reference/joins.html