Build_Tables.sql

Home
Skip to Content
Skip to Search

Oracle

Sign In Account
Oracle Account

Account

Help

Sign Out
Oracle Account

Manage your account and access personalized content. Sign up for an Oracle Account

Sign in to my Account

Sign in to Cloud

Access your cloud dashboard, manage orders, and more. Sign up for a free trial

Sign in to Cloud
Country/Region
Contact

-- One off master rebuild script for running against apex.oracle.com

-- All scripts found in RebuildScripts directory

----------------------------------------------------------------------------------------------

-- hr_cre script

----------------------------------------------------------------------------------------------

REM ********************************************************************

REM Create the REGIONS table to hold region information for locations

REM HR.LOCATIONS table has a foreign key to this table.

Prompt ****** Creating REGIONS table ....

CREATE TABLE regions

( region_id NUMBER

CONSTRAINT region_id_nn NOT NULL

, region_name VARCHAR2(25)

);

CREATE UNIQUE INDEX reg_id_pk

ON regions (region_id);

ALTER TABLE regions

ADD ( CONSTRAINT reg_id_pk

PRIMARY KEY (region_id)

) ;

REM ********************************************************************

REM Create the COUNTRIES table to hold country information for customers

REM and company locations.

REM OE.CUSTOMERS table and HR.LOCATIONS have a foreign key to this table.

Prompt ****** Creating COUNTRIES table ....

CREATE TABLE countries

( country_id CHAR(2)

CONSTRAINT country_id_nn NOT NULL

, country_name VARCHAR2(40)

, region_id NUMBER

, CONSTRAINT country_c_id_pk

PRIMARY KEY (country_id)

)

ORGANIZATION INDEX;

ALTER TABLE countries

ADD ( CONSTRAINT countr_reg_fk

FOREIGN KEY (region_id)

REFERENCES regions(region_id)

) ;

REM ********************************************************************

REM Create the LOCATIONS table to hold address information for company departments.

REM HR.DEPARTMENTS has a foreign key to this table.

Prompt ****** Creating LOCATIONS table ....

CREATE TABLE locations

( location_id NUMBER(4)

, street_address VARCHAR2(40)

, postal_code VARCHAR2(12)

, city VARCHAR2(30)

CONSTRAINT loc_city_nn NOT NULL

, state_province VARCHAR2(25)

, country_id CHAR(2)

) ;

CREATE UNIQUE INDEX loc_id_pk

ON locations (location_id) ;

ALTER TABLE locations

ADD ( CONSTRAINT loc_id_pk

PRIMARY KEY (location_id)

, CONSTRAINT loc_c_id_fk

FOREIGN KEY (country_id)

REFERENCES countries(country_id)

) ;

Rem Useful for any subsequent addition of rows to locations table

Rem Starts with 3300

CREATE SEQUENCE locations_seq

START WITH 3300

INCREMENT BY 100

MAXVALUE 9900

NOCACHE

NOCYCLE;

REM ********************************************************************

REM Create the DEPARTMENTS table to hold company department information.

REM HR.EMPLOYEES and HR.JOB_HISTORY have a foreign key to this table.

Prompt ****** Creating DEPARTMENTS table ....

CREATE TABLE departments

( department_id NUMBER(4)

, department_name VARCHAR2(30)

CONSTRAINT dept_name_nn NOT NULL

, manager_id NUMBER(6)

, location_id NUMBER(4)

) ;

CREATE UNIQUE INDEX dept_id_pk

ON departments (department_id) ;

ALTER TABLE departments

ADD ( CONSTRAINT dept_id_pk

PRIMARY KEY (department_id)

, CONSTRAINT dept_loc_fk

FOREIGN KEY (location_id)

REFERENCES locations (location_id)

) ;

Rem Useful for any subsequent addition of rows to departments table

Rem Starts with 280

CREATE SEQUENCE departments_seq

START WITH 280

INCREMENT BY 10

MAXVALUE 9990

NOCACHE

NOCYCLE;

REM ********************************************************************

REM Create the JOBS table to hold the different names of job roles within the company.

REM HR.EMPLOYEES has a foreign key to this table.

Prompt ****** Creating JOBS table ....

CREATE TABLE jobs

( job_id VARCHAR2(10)

, job_title VARCHAR2(35)

CONSTRAINT job_title_nn NOT NULL

, min_salary NUMBER(6)

, max_salary NUMBER(6)

) ;

CREATE UNIQUE INDEX job_id_pk

ON jobs (job_id) ;

ALTER TABLE jobs

ADD ( CONSTRAINT job_id_pk

PRIMARY KEY(job_id)

) ;

REM ********************************************************************

REM Create the EMPLOYEES table to hold the employee personnel

REM information for the company.

REM HR.EMPLOYEES has a self referencing foreign key to this table.

Prompt ****** Creating EMPLOYEES table ....

CREATE TABLE employees

( employee_id NUMBER(6)

, first_name VARCHAR2(20)

, last_name VARCHAR2(25)

CONSTRAINT emp_last_name_nn NOT NULL

, email VARCHAR2(25)

CONSTRAINT emp_email_nn NOT NULL

, phone_number VARCHAR2(20)

, hire_date DATE

CONSTRAINT emp_hire_date_nn NOT NULL

, job_id VARCHAR2(10)

CONSTRAINT emp_job_nn NOT NULL

, salary NUMBER(8,2)

, commission_pct NUMBER(2,2)

, manager_id NUMBER(6)

, department_id NUMBER(4)

, CONSTRAINT emp_salary_min

CHECK (salary > 0)

, CONSTRAINT emp_email_uk

UNIQUE (email)

) ;

CREATE UNIQUE INDEX emp_emp_id_pk

ON employees (employee_id) ;

ALTER TABLE employees

ADD ( CONSTRAINT emp_emp_id_pk

PRIMARY KEY (employee_id)

, CONSTRAINT emp_dept_fk

FOREIGN KEY (department_id)

REFERENCES departments

, CONSTRAINT emp_job_fk

FOREIGN KEY (job_id)

REFERENCES jobs (job_id)

, CONSTRAINT emp_manager_fk

FOREIGN KEY (manager_id)

REFERENCES employees

) ;

ALTER TABLE departments

ADD ( CONSTRAINT dept_mgr_fk

FOREIGN KEY (manager_id)

REFERENCES employees (employee_id)

) ;

Rem Useful for any subsequent addition of rows to employees table

Rem Starts with 207

CREATE SEQUENCE employees_seq

START WITH 207

INCREMENT BY 1

NOCACHE

NOCYCLE;

REM ********************************************************************

REM Create the JOB_HISTORY table to hold the history of jobs that

REM employees have held in the past.

REM HR.JOBS, HR_DEPARTMENTS, and HR.EMPLOYEES have a foreign key to this table.

Prompt ****** Creating JOB_HISTORY table ....

CREATE TABLE job_history

( employee_id NUMBER(6)

CONSTRAINT jhist_employee_nn NOT NULL

, start_date DATE

CONSTRAINT jhist_start_date_nn NOT NULL

, end_date DATE

CONSTRAINT jhist_end_date_nn NOT NULL

, job_id VARCHAR2(10)

CONSTRAINT jhist_job_nn NOT NULL

, department_id NUMBER(4)

, CONSTRAINT jhist_date_interval

CHECK (end_date > start_date)

) ;

CREATE UNIQUE INDEX jhist_emp_id_st_date_pk

ON job_history (employee_id, start_date) ;

ALTER TABLE job_history

ADD ( CONSTRAINT jhist_emp_id_st_date_pk

PRIMARY KEY (employee_id, start_date)

, CONSTRAINT jhist_job_fk

FOREIGN KEY (job_id)

REFERENCES jobs

, CONSTRAINT jhist_emp_fk

FOREIGN KEY (employee_id)

REFERENCES employees

, CONSTRAINT jhist_dept_fk

FOREIGN KEY (department_id)

REFERENCES departments

) ;

REM ********************************************************************

REM Create the EMP_DETAILS_VIEW that joins the employees, jobs,

REM departments, jobs, countries, and locations table to provide details

REM about employees.

Prompt ****** Creating EMP_DETAILS_VIEW view ...

CREATE OR REPLACE VIEW emp_details_view

(employee_id,

job_id,

manager_id,

department_id,

location_id,

country_id,

first_name,

last_name,

salary,

commission_pct,

department_name,

job_title,

city,

state_province,

country_name,

region_name)

AS SELECT

e.employee_id,

e.job_id,

e.manager_id,

e.department_id,

d.location_id,

l.country_id,

e.first_name,

e.last_name,

e.salary,

e.commission_pct,

d.department_name,

j.job_title,

l.city,

l.state_province,

c.country_name,

r.region_name

FROM

employees e,

departments d,

jobs j,

locations l,

countries c,

regions r

WHERE e.department_id = d.department_id

AND d.location_id = l.location_id

AND l.country_id = c.country_id

AND c.region_id = r.region_id

AND j.job_id = e.job_id

WITH READ ONLY;

----------------------------------------------------------------------------------------------

-- hr_popul script

----------------------------------------------------------------------------------------------

REM ***************************insert data into the REGIONS table

Prompt ****** Populating REGIONS table ....

INSERT INTO regions VALUES

( 1

, 'Europe'

);

INSERT INTO regions VALUES

( 2

, 'Americas'

);

INSERT INTO regions VALUES

( 3

, 'Asia'

);

INSERT INTO regions VALUES

( 4

, 'Middle East and Africa'

);

REM ***************************insert data into the COUNTRIES table

Prompt ****** Populating COUNTIRES table ....

INSERT INTO countries VALUES

( 'IT'

, 'Italy'

, 1

);

INSERT INTO countries VALUES

( 'JP'

, 'Japan'

, 3

);

INSERT INTO countries VALUES

( 'US'

, 'United States of America'

, 2

);

INSERT INTO countries VALUES

( 'CA'

, 'Canada'

, 2

);

INSERT INTO countries VALUES

( 'CN'

, 'China'

, 3

);

INSERT INTO countries VALUES

( 'IN'

, 'India'

, 3

);

INSERT INTO countries VALUES

( 'AU'

, 'Australia'

, 3

);

INSERT INTO countries VALUES

( 'ZW'

, 'Zimbabwe'

, 4

);

INSERT INTO countries VALUES

( 'SG'

, 'Singapore'

, 3

);

INSERT INTO countries VALUES

( 'UK'

, 'United Kingdom'

, 1

);

INSERT INTO countries VALUES

( 'FR'

, 'France'

, 1

);

INSERT INTO countries VALUES

( 'DE'

, 'Germany'

, 1

);

INSERT INTO countries VALUES

( 'ZM'

, 'Zambia'

, 4

);

INSERT INTO countries VALUES

( 'EG'

, 'Egypt'

, 4

);

INSERT INTO countries VALUES

( 'BR'

, 'Brazil'

, 2

);

INSERT INTO countries VALUES

( 'CH'

, 'Switzerland'

, 1

);

INSERT INTO countries VALUES

( 'NL'

, 'Netherlands'

, 1

);

INSERT INTO countries VALUES

( 'MX'

, 'Mexico'

, 2

);

INSERT INTO countries VALUES

( 'KW'

, 'Kuwait'

, 4

);

INSERT INTO countries VALUES

( 'IL'

, 'Israel'

, 4

);

INSERT INTO countries VALUES

( 'DK'

, 'Denmark'

, 1

);

INSERT INTO countries VALUES

( 'HK'

, 'HongKong'

, 3

);

INSERT INTO countries VALUES

( 'NG'

, 'Nigeria'

, 4

);

INSERT INTO countries VALUES

( 'AR'

, 'Argentina'

, 2

);

INSERT INTO countries VALUES

( 'BE'

, 'Belgium'

, 1

);

REM ***************************insert data into the LOCATIONS table

Prompt ****** Populating LOCATIONS table ....

INSERT INTO locations VALUES

( 1000

, '1297 Via Cola di Rie'

, '00989'

, 'Roma'

, NULL

, 'IT'

);

INSERT INTO locations VALUES

( 1100

, '93091 Calle della Testa'

, '10934'

, 'Venice'

, NULL

, 'IT'

);

INSERT INTO locations VALUES

( 1200

, '2017 Shinjuku-ku'

, '1689'

, 'Tokyo'

, 'Tokyo Prefecture'

, 'JP'

);

INSERT INTO locations VALUES

( 1300

, '9450 Kamiya-cho'

, '6823'

, 'Hiroshima'

, NULL

, 'JP'

);

INSERT INTO locations VALUES

( 1400

, '2014 Jabberwocky Rd'

, '26192'

, 'Southlake'

, 'Texas'

, 'US'

);

INSERT INTO locations VALUES

( 1500

, '2011 Interiors Blvd'

, '99236'

, 'South San Francisco'

, 'California'

, 'US'

);

INSERT INTO locations VALUES

( 1600

, '2007 Zagora St'

, '50090'

, 'South Brunswick'

, 'New Jersey'

, 'US'

);

INSERT INTO locations VALUES

( 1700

, '2004 Charade Rd'

, '98199'

, 'Seattle'

, 'Washington'

, 'US'

);

INSERT INTO locations VALUES

( 1800

, '147 Spadina Ave'

, 'M5V 2L7'

, 'Toronto'

, 'Ontario'

, 'CA'

);

INSERT INTO locations VALUES

( 1900

, '6092 Boxwood St'

, 'YSW 9T2'

, 'Whitehorse'

, 'Yukon'

, 'CA'

);

INSERT INTO locations VALUES

( 2000

, '40-5-12 Laogianggen'

, '190518'

, 'Beijing'

, NULL

, 'CN'

);

INSERT INTO locations VALUES

( 2100

, '1298 Vileparle (E)'

, '490231'

, 'Bombay'

, 'Maharashtra'

, 'IN'

);

INSERT INTO locations VALUES

( 2200

, '12-98 Victoria Street'

, '2901'

, 'Sydney'

, 'New South Wales'

, 'AU'

);

INSERT INTO locations VALUES

( 2300

, '198 Clementi North'

, '540198'

, 'Singapore'

, NULL

, 'SG'

);

INSERT INTO locations VALUES

( 2400

, '8204 Arthur St'

, NULL

, 'London'

, NULL

, 'UK'

);

INSERT INTO locations VALUES

( 2500

, 'Magdalen Centre, The Oxford Science Park'

, 'OX9 9ZB'

, 'Oxford'

, 'UK'

);

INSERT INTO locations VALUES

( 2600

, '9702 Chester Road'

, '09629850293'

, 'Stretford'

, 'Manchester'

, 'UK'

);

INSERT INTO locations VALUES

( 2700

, 'Schwanthalerstr. 7031'

, '80925'

, 'Munich'

, 'Bavaria'

, 'DE'

);

INSERT INTO locations VALUES

( 2800

, 'Rua Frei Caneca 1360 '

, '01307-002'

, 'Sao Paulo'

, 'BR'

);

INSERT INTO locations VALUES

( 2900

, '20 Rue des Corps-Saints'

, '1730'

, 'Geneva'

, 'Geneve'

, 'CH'

);

INSERT INTO locations VALUES

( 3000

, 'Murtenstrasse 921'

, '3095'

, 'Bern'

, 'BE'

, 'CH'

);

INSERT INTO locations VALUES

( 3100

, 'Pieter Breughelstraat 837'

, '3029SK'

, 'Utrecht'

, 'NL'

);

INSERT INTO locations VALUES

( 3200

, 'Mariano Escobedo 9991'

, '11932'

, 'Mexico City'

, 'Distrito Federal,'

, 'MX'

);

REM ****************************insert data into the DEPARTMENTS table

Prompt ****** Populating DEPARTMENTS table ....

REM disable integrity constraint to EMPLOYEES to load data

ALTER TABLE departments

DISABLE CONSTRAINT dept_mgr_fk;