先上图：
找这几个文件时几经波折，百度在这种基础问题上也掉链子了，老铁们且看且珍惜。
有些文件可以在官网下载，但是老外的网站你捉摸不透，时隔一天我已经找不到了。
CSDN现在不能免费分享文件，笔者贴上代码，老铁们可自行搬运：
一、新建文本文档命名为“del_data.sql”，后缀名别漏了改，分割线内的代码复制粘贴保存：
———————————————————“del_data.sql”的分割线———————————————————
ALTER TABLE departments
DISABLE CONSTRAINT DEPT_MGR_FK;
ALTER TABLE job_history
DISABLE CONSTRAINT JHIST_EMP_FK;
DROP TRIGGER secure_employees;
DROP TRIGGER update_job_history;
DROP PROCEDURE add_job_history;
DROP PROCEDURE secure_dml;
DELETE FROM employees
WHERE manager_id IN (108, 114, 120, 121, 122, 123, 145, 146, 147, 148);
DELETE FROM employees
WHERE employee_id IN (114, 120, 121, 122, 123, 145, 146, 147, 148,
196, 197, 198, 199, 105, 106, 108, 175, 177,
179, 203, 204);
DELETE FROM locations
WHERE location_id NOT IN
(SELECT DISTINCT location_id
FROM departments);
DELETE FROM countries
WHERE country_id NOT IN
(SELECT country_id
FROM locations);
DELETE FROM jobs
WHERE job_id NOT IN
(SELECT job_id
FROM employees);
DELETE FROM departments
WHERE department_id NOT IN
(SELECT DISTINCT department_id
FROM employees WHERE department_id IS NOT NULL);
UPDATE departments
SET manager_id = 124
WHERE department_id = 50;
UPDATE departments
SET manager_id = 149
WHERE department_id = 80;
DELETE FROM locations
WHERE location_id IN (2700, 2400);
UPDATE locations
SET street_address = '460 Bloor St. W.',
postal_code = 'ON M5S 1X8'
WHERE location_id = 1800;
ALTER TABLE departments
ENABLE CONSTRAINT DEPT_MGR_FK;
CREATE TABLE job_grades(grade_level VARCHAR2(3), lowest_sal NUMBER, highest_sal NUMBER);
INSERT INTO job_grades
VALUES ('A', 1000, 2999);
INSERT INTO job_grades
VALUES ('B', 3000, 5999);
INSERT INTO job_grades
VALUES('C', 6000, 9999);
INSERT INTO job_grades
VALUES('D', 10000, 14999);
INSERT INTO job_grades
VALUES('E', 15000, 24999);
INSERT INTO job_grades
VALUES('F', 25000, 40000);
INSERT INTO departments VALUES
( 190
, 'Contracting'
, NULL
, 1700
);
COMMIT;
———————————————————“del_data.sql”的分割线———————————————————
二、“hr_cre.sql”，方法同上。
———————————————————“hr_cre.sql”的分割线———————————————————
Rem
Rem $Header: hr_cre.sql 29-aug-2002.11:44:03 hyeh Exp $
Rem
Rem hr_cre.sql
Rem
Rem Copyright (c) 2001, 2002, Oracle Corporation. All rights reserved.
Rem
Rem NAME
Rem hr_cre.sql - Create data objects for HR schema
Rem
Rem DESCRIPTION
Rem This script creates six tables, associated constraints
Rem and indexes in the human resources (HR) schema.
Rem
Rem NOTES
Rem
Rem CREATED by Nancy Greenberg, Nagavalli Pataballa - 06/01/00
Rem
Rem MODIFIED (MM/DD/YY)
Rem hyeh 08/29/02 - hyeh_mv_comschema_to_rdbms
Rem ahunold 09/14/00 - Added emp_details_view
Rem ahunold 02/20/01 - New header
Rem vpatabal 03/02/01 - Added regions table, modified regions
Rem column in countries table to NUMBER.
Rem Added foreign key from countries table
Rem to regions table on region_id.
Rem Removed currency name, currency symbol
Rem columns from the countries table.
Rem Removed dn columns from employees and
Rem departments tables.
Rem Added sequences.
Rem Removed not null constraint from
Rem salary column of the employees table.
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
SET ECHO OFF
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;
COMMIT;
———————————————————“hr_cre.sql”的分割线———————————————————
三、“hr_popul.sql”，方法同上。
———————————————————“hr_popul.sql ”的分割线———————————————————
rem
rem Header: hr_popul.sql 09-jan-01
rem
rem Copyright (c) 2001, 2002, Oracle Corporation. All rights reserved.
rem
rem Owner : ahunold
rem
rem NAME
rem hr_popul.sql - Populate script for HR schema
rem
rem DESCRIPTON
rem
rem
rem NOTES
rem There is a circular foreign key reference between
rem EMPLOYESS and DEPARTMENTS. That's why we disable
rem the FK constraints here
rem
rem CREATED
rem Nancy Greenberg, Nagavalli Pataballa - 06/01/00
rem
rem MODIFIED (MM/DD/YY)
rem hyeh 08/29/02 - hyeh_mv_comschema_to_rdbms
rem ahunold 03/07/01 - small data errors corrected
rem - Modified region values of countries table
rem - Replaced ID sequence values for employees
rem and departments tables with numbers
rem - Moved create sequence statements to hr_cre
rem - Removed dn values for employees and
rem departments tables
rem - Removed currency columns values from
rem countries table
rem ngreenbe - Updated employee 178 for no department
rem pnathan - Insert new rows to job_history table
rem ahunold 02/20/01 - NLS_LANGUAGE, replacing non American
rem ahunold 01/09/01 - checkin ADE
SET VERIFY OFF
ALTER SESSION SET NLS_LANGUAGE=American;
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
, 'A
最低0.47元/天 解锁文章
847
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
