---- 以DBA身份登录 Oracle 数据库
---- 请按照实际情况修改用户密码
conn system/123@&&service_name as sysdba
---- 创建一个新用户sg007、密码为sg007
create user sg007 identified by sg007 default tablespace users;
grant connect,resource to sg007;
---- 使用用户sg007连接数据库
connect sg007/sg007@&&service_name;
---- 修改日期格式
alter session set nls_date_language='american';
alter session set nls_date_format='DD-MON-RR';
---- 创建表countries
create table countries (
COUNTRY_ID CHAR(2) NOT NULL,
COUNTRY_NAME VARCHAR2(40),
REGION_ID NUMBER
);
---- 插入数据
INSERT INTO COUNTRIES
(COUNTRY_ID,COUNTRY_NAME,REGION_ID)
VALUES
('CA','Canada',2);
INSERT INTO COUNTRIES
(COUNTRY_ID,COUNTRY_NAME,REGION_ID)
VALUES
('DE','Germany',1);
INSERT INTO COUNTRIES
(COUNTRY_ID,COUNTRY_NAME,REGION_ID)
VALUES
('UK','United Kingdom',1);
INSERT INTO COUNTRIES
(COUNTRY_ID,COUNTRY_NAME,REGION_ID)
VALUES
('US','United States of America',2);
---- 创建表departments
CREATE TABLE departments
(
DEPARTMENT_ID NUMBER(4) NOT NULL,
DEPARTMENT_NAME VARCHAR2(30) NOT NULL,
MANAGER_ID NUMBER(6),
LOCATION_ID NUMBER(4)
);
---- 插入数据
INSERT INTO DEPARTMENTS
(DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID)
values
(10,'Administration',200,1700);
INSERT INTO DEPARTMENTS
(DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID)
values
(20,'Marketing',201,1800);
INSERT INTO DEPARTMENTS
(DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID)
values
(50,'Shipping',124,1500);
INSERT INTO DEPARTMENTS
(DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID)
values
(60,'IT',103,1400);
INSERT INTO DEPARTMENTS
(DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID)
values
(80,'Sales',149,2500);
INSERT INTO DEPARTMENTS
(DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID)
values
(90,'Executive',100,1700);
INSERT INTO DEPARTMENTS
(DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID)
values
(110,'Accounting',205,1700);
INSERT INTO DEPARTMENTS
(DEPARTMENT_ID,DEPARTMENT_NAME,LOCATION_ID)
values
(190,'Contracting',1700);
---- 创建表employees
CREATE TABLE employees
(
EMPLOYEE_ID NUMBER(6) NOT NULL,
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25) NOT NULL,
EMAIL VARCHAR2(25) NOT NULL,
PHONE_NUMBER VARCHAR2(20),
HIRE_DATE DATE NOT NULL,
JOB_ID VARCHAR2(10) NOT NULL,
SALARY NUMBER(8,2),
COMMISSION_PCT NUMBER(2,2),
MANAGER_ID NUMBER(6),
DEPARTMENT_ID NUMBER(4)
);
---- 插入数据
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
VALUES
(100,'Steven','King','SKING','515.123.4567','17-JUN-87','AD_PRES',24000,90);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID)
VALUES
(101,'Neena','Kochhar','NKOCHHAR','515.123.4568','21-SEP-89','AD_VP',17000,100,90);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID)
VALUES
(102,'Lex','De Haan','LDEHAAN','515.123.4569','13-JAN-93','AD_VP',17000,100,90);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID)
VALUES
(103,'Alexander','Hunold','AHUNOLD','590.123.4567','03-JAN-90','IT_PROG',9000,102,60);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID)
VALUES
(104,'Bruce','Ernst','BERNST','590.423.4568','21-MAY-91','IT_PROG',6000,103,60);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID)
VALUES
(107,'Diana','Lorentz','DLORENTZ','590.423.5567','07-FEB-99','IT_PROG',4200,103,60);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID)
VALUES
(124,'Kevin','Mourgos','KMOURGOS','650.123.5234','16-NOV-99','ST_MAN',5800,100,50);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID)
VALUES
(141,'Trenna','Rajs','TRAJS','650.121.8009','17-OCT-95','ST_CLERK',3500,124,50);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID)
VALUES
(142,'Curtis','Davies','CDAVIES','650.121.2994','29-JAN-97','ST_CLERK',3100,124,50);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID)
VALUES
(143,'Randall','Matos','RMATOS','650.121.2874','15-MAR-98','ST_CLERK',2600,124,50);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID)
VALUES
(144,'Peter','Vargas','PVARGAS','650.121.2004','09-JUL-98','ST_CLERK',2500,124,50);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
VALUES
(149,'Eleni','Zlotkey','ezlotkey','011.44.1344.429018','29-JAN-00','SA_MAN',10500,.2,100,80);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
VALUES
(174,'Ellen','Abel','EABEL','011.44.1644.429267','11-MAY-96','SA_REP',11000,.3,149,80);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
VALUES
(176,'Jonathon','Taylor','JTAYLORL','011.44.1644.429265','24-MAR-98','SA_REP',8600,.2,149,80);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID)
VALUES
(178,'Kimberely','Grant','KGRANT','011.44.1644.429263','24-MAY-99','SA_REP',7000,.15,149);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID)
VALUES
(200,'Jennifer','Whatlen','JWHALEN','515.123.4444','17-SEP-87','AD_ASST',4400,101,10);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID)
VALUES
(201,'Michael','Hartstein','MHARTSTE','515.123.5555','17-SEP-96','MK_MAN',13000,100,20);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID)
VALUES
(202,'Pat','Fay','PFAY','603.123.6666','17-AUG-97','MK_REP',6000,201,20);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID)
VALUES
(205,'Shelley','Higgins','SHIGGINS','515.123.8080','07-JUN-94','AC_MGR',12000,101,110);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID)
VALUES
(206,'William','Gietz','WGIETZ','515.123.8181','07-JUN-94','AC_ACCOUNT',8300,205,110);
---- 创建表jobs
CREATE TABLE jobs
(
JOB_ID VARCHAR2(10) NOT NULL,
JOB_TITLE VARCHAR2(35) NOT NULL,
MIN_SALARY NUMBER(6),
MAX_SALARY NUMBER(6)
);
---- 插入数据
INSERT INTO JOBS
(JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)
VALUES
('AD_PRES','President',20000,40000);
INSERT INTO JOBS
(JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)
VALUES
('AD_VP','Administration Vice President',15000,30000);
INSERT INTO JOBS
(JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)
VALUES
('AD_ASST','Administration Assistant',3000,6000);
INSERT INTO JOBS
(JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)
VALUES
('AD_MGR','Accounting Manager',8200,16000);
INSERT INTO JOBS
(JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)
VALUES
('AD_ACCOUNT','Public Accountant',4200,9000);
INSERT INTO JOBS
(JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)
VALUES
('SA_MAN','Sales Manager',10000,20000);
INSERT INTO JOBS
(JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)
VALUES
('SA_REP','Sales Representative',6000,12000);
INSERT INTO JOBS
(JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)
VALUES
('ST_MAN','Stock Manager',5500,8500);
INSERT INTO JOBS
(JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)
VALUES
('ST_CLERK','Stock Clerk',2000,5000);
INSERT INTO JOBS
(JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)
VALUES
('IT_PROG','Programmer',4000,10000);
INSERT INTO JOBS
(JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)
VALUES
('MK_MAN','Marketing Manager',9000,15000);
INSERT INTO JOBS
(JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)
VALUES
('MK_REP','Marketing Representative',4000,9000);
---- 创建表job_grades
create table job_grades
(
GRADE_LEVEL VARCHAR2(3),
LOWEST_SAL NUMBER,
HIGHEST_SAL NUMBER
);
---- 插入数据
INSERT INTO job_grades
(GRADE_LEVEL,LOWEST_SAL,HIGHEST_SAL)
VALUES
('A',1000,2999);
INSERT INTO job_grades
(GRADE_LEVEL,LOWEST_SAL,HIGHEST_SAL)
VALUES
('B',3000,5999);
INSERT INTO job_grades
(GRADE_LEVEL,LOWEST_SAL,HIGHEST_SAL)
VALUES
('C',6000,9999);
INSERT INTO job_grades
(GRADE_LEVEL,LOWEST_SAL,HIGHEST_SAL)
VALUES
('D',10000,14999);
INSERT INTO job_grades
(GRADE_LEVEL,LOWEST_SAL,HIGHEST_SAL)
VALUES
('E',15000,24999);
INSERT INTO job_grades
(GRADE_LEVEL,LOWEST_SAL,HIGHEST_SAL)
VALUES
('F',25000,40000);
---- 创建表job_history
CREATE TABLE job_history
(
EMPLOYEE_ID NUMBER(6) NOT NULL,
START_DATE DATE NOT NULL,
END_DATE DATE NOT NULL,
JOB_ID VARCHAR2(10) NOT NULL,
DEPARTMENT_ID NUMBER(4)
);
---- 插入数据
INSERT INTO job_history
(EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID)
VALUES
(102,'13-JAN-93','24-JUL-98','IT_PROG',60);
INSERT INTO job_history
(EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID)
VALUES
(101,'21-SEP-89','27-OCT-93','AC_ACCOUNT',110);
INSERT INTO job_history
(EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID)
VALUES
(101,'28-OCT-93','15-MAR-97','AC_MGR',110);
INSERT INTO job_history
(EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID)
VALUES
(201,'17-FEB-96','19-DEC-99','MK-REP',20);
INSERT INTO job_history
(EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID)
VALUES
(114,'24-MAR-98','31-DEC-99','ST_CLERK',50);
INSERT INTO job_history
(EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID)
VALUES
(122,'01-JAN-99','31-DEC-99','ST_CLERK',50);
INSERT INTO job_history
(EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID)
VALUES
(200,'17-SEP-87','17-JUN-93','AD_ASST',90);
INSERT INTO job_history
(EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID)
VALUES
(176,'24-MAR-98','31-DEC-98','SA_REP',80);
INSERT INTO job_history
(EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID)
VALUES
(176,'01-JAN-99','31-DEC-99','SA_MAN',80);
INSERT INTO job_history
(EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID)
VALUES
(200,'01-JUL-94','31-DEC-98','AC_ACCOUNT',90);
---- 创建表locations
CREATE TABLE locations
(
LOCATION_ID NUMBER(4) NOT NULL,
STREET_ADDRESS VARCHAR2(40),
POSTAL_CODE VARCHAR2(12),
CITY VARCHAR2(30) NOT NULL,
STATE_PROVINCE VARCHAR2(25),
COUNTRY_ID CHAR(2)
);
---- 插入数据
INSERT INTO LOCATIONS
(LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID)
VALUES
(1400,'2014 Jabberwocky Rd',26192,'Southlake','Texas','US');
INSERT INTO LOCATIONS
(LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID)
VALUES
(1500,'2011 Interiors Blvd',99236,'South San Francisco','California','US');
INSERT INTO LOCATIONS
(LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID)
VALUES
(1700,'2004 Charade Rd',98199,'Seattle','Washington','US');
INSERT INTO LOCATIONS
(LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID)
VALUES
(1800,'460 Bloor St. W.','ON M5S 1X8','Toronto','Ontario','CA');
INSERT INTO LOCATIONS
(LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID)
VALUES
(2500,'Magdalen Centre,The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK');
---- 创建表regions
CREATE TABLE regions
(
REGION_ID NUMBER NOT NULL,
REGION_NAME VARCHAR2(25)
);
---- 插入数据
INSERT INTO REGIONS
(REGION_ID,REGION_NAME)
VALUES
(1,'Europe');
INSERT INTO REGIONS
(REGION_ID,REGION_NAME)
VALUES
(2,'Americas');
INSERT INTO REGIONS
(REGION_ID,REGION_NAME)
VALUES
(3,'Asia');
INSERT INTO REGIONS
(REGION_ID,REGION_NAME)
VALUES
(4,'Middle East and Africa');
---- 提交
commit;
---- 提示信息
host echo "current user: sg007 , password: sg007"
