---
--- Oracle Database 10g 入門
--- SQL基礎I　演習用テーブル作成スクリプト
--- Since 2005-4-22 LastUpdate 2006-10-21
---

--- REGIONS表：領域（米国、アジアなど）を示す行が表示されます
CREATE TABLE regions (
region_id NUMBER NOT NULL,
region_name VARCHAR2(25)
);
INSERT ALL
INTO regions VALUES (1, 'Europe')
INTO regions VALUES (2, 'Americas')
INTO regions VALUES (3, 'Asia')
INTO regions VALUES (4, 'Middle East and Africa')
SELECT 'X' FROM dual;
COMMIT;
--- COUNTRIES表：領域に関係する国の行が表示されます
CREATE TABLE countries (
country_id CHAR(2) NOT NULL,
country_name VARCHAR2(40),
region_id NUMBER
);
INSERT ALL
INTO countries VALUES ('CA', 'Canada', 2)
INTO countries VALUES ('DE', 'Germany', 1)
INTO countries VALUES ('UK', 'United Kingdom', 1)
INTO countries VALUES ('US', 'United States of America', 2)
SELECT 'X' FROM dual;
COMMIT;
--- 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 ALL
INTO locations VALUES (1400, '2014 Jabberwocky Rd', '26192', 'Southlake', 'Texas', 'US')
INTO locations VALUES (1500, '2011 Interiors Blvd', '99236', 'South SanFrancisco', 'California', 'US')
INTO locations VALUES (1700, '2004 Charade Rd', '98199', 'Seattle', 'Washinton', 'US')
INTO locations VALUES (1800, '460 Bloor St. VV.', 'ON S5S 1X8', 'Tronto', 'Ontario', 'CA')
INTO locations VALUES (2500, 'Magdalen Centre, The Oxford Science Park', 'OX9 9ZB', 'Oxford', 'Oxford', 'UK')
SELECT 'X' FROM dual;
COMMIT;
--- DEPARTMENTS表：従業員が勤務している部門の詳細を示します。各部門は、EMPLOYEES表と関連を持ち、部門マネージャーを表すことができます
CREATE TABLE departments (
department_id NUMBER(4) NOT NULL,
department_name VARCHAR2(30) NOT NULL,
manager_id NUMBER(6),
location_id NUMBER(4)
);
INSERT ALL
INTO departments VALUES (10, 'Administration', 200, 1700)
INTO departments VALUES (20, 'Marketing', 201, 1800)
INTO departments VALUES (50, 'Shipping', 124, 1500)
INTO departments VALUES (60, 'IT', 103, 1400)
INTO departments VALUES (80, 'Sales', 149, 2500)
INTO departments VALUES (90, 'Executive', 100, 1700)
INTO departments VALUES (110, 'Accounting', 205, 1700)
INTO departments VALUES (190, 'Contracting', NULL, 1700)
SELECT 'X' FROM dual;
COMMIT;
--- 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 ALL
INTO employees VALUES (100, 'Steven', 'King', 'SKING', '515.123.4567', '87-06-17', 'AD_PRES', 24000, NULL, NULL, 90)
INTO employees VALUES (101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', '89-09-21', 'AD_VP', 17000, NULL, 100, 90)
INTO employees VALUES (102, 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', '93-01-13', 'AD_VP', 17000, NULL, 100, 90)
INTO employees VALUES (103, 'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567', '90-01-03', 'IT_PROG', 9000, NULL, 102, 60)
INTO employees VALUES (104, 'Bruce', 'Emst', 'BERNST', '590.423.4568', '91-05-21', 'IT_PROG', 6000, NULL, 103, 60)
INTO employees VALUES (107, 'Diana', 'Lorentz', 'DLORENTZ', '590.423.5567', '99-02-07', 'IT_PROG', 4200, NULL, 103, 60)
INTO employees VALUES (124, 'Kevin', 'Mourgos', 'KMOURGOS', '650.123.5234', '99-11-16', 'ST_MAN', 5800, NULL, 100, 50)
INTO employees VALUES (141, 'Trenna', 'Rajs', 'TRAJS', '650.121.8009', '95-10-17', 'ST_CLERK', 3500, NULL, 124, 50)
INTO employees VALUES (142, 'Curtis', 'Davies', 'CDAVIES', '650.121.2994', '97-01-29', 'ST_CLERK', 3100, NULL, 124, 50)
INTO employees VALUES (143, 'Randall', 'Matos', 'RMATOS', '650.121.2874', '98-03-15', 'ST_CLERK', 2600, NULL, 124, 50)
INTO employees VALUES (144, 'Peter', 'Vargas', 'PVARGAS', '650.121.2004', '98-07-09', 'ST_CLERK', 2500, NULL, 124, 50)
INTO employees VALUES (149, 'Eleni', 'Zlotkey', 'EZLOTKEY', '011.44.1344.429018', '00-01-29', 'SA_MAN', 10500, .2, 100, 80)
INTO employees VALUES (174, 'Ellen', 'Abel', 'EABEL', '011.44.1644.429267', '96-05-11', 'SA_REP', 11000, .3, 149, 80)
INTO employees VALUES (176, 'Jonathon', 'Taylor', 'JTAYLOR', '011.44.1644.429265', '98-03-24', 'SA_REP', 8600, .2, 149, 80)
INTO employees VALUES (178, 'Kimberely', 'Grant', 'KGRANT', '011.44.1644.429263', '99-05-24', 'SA_REP', 7000, .15, 149, NULL)
INTO employees VALUES (200, 'Jennifer', 'Whalen', 'JWHALEN', '515.123.4444', '87-09-17', 'AD_ASST', 4400, NULL, 101, 10)
INTO employees VALUES (201, 'Michael', 'Hartstein', 'MHARTSTE', '515.123.5555', '96-02-17', 'MK_MAN', 13000, NULL, 100, 20)
INTO employees VALUES (202, 'Pat', 'Fay', 'PFAY', '603.123.6666', '97-08-17', 'MK_REP', 6000, NULL, 201, 20)
INTO employees VALUES (205, 'Shelley', 'Higgins', 'SHIGGINS', '515.123.8080', '94-06-07', 'AC_MGR', 12000, NULL, 101, 110)
INTO employees VALUES (206, 'William', 'Gietz', 'WGIETZ', '515.123.8181', '94-06-07', 'AC_ACCOUNT', 8300, NULL, 205, 110)
SELECT 'X' FROM dual;
COMMIT;
--- 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 ALL
INTO jobs VALUES ('AD_PRES', 'President', 20000, 40000)
INTO jobs VALUES ('AD_VP', 'Administration Vice President', 15000, 30000)
INTO jobs VALUES ('AD_ASST', 'Administration Assistant', 3000, 6000)
INTO jobs VALUES ('AC_MGR', 'Accounting Manager', 8200, 16000)
INTO jobs VALUES ('AC_ACCOUNT', 'Public Accountant', 4200, 9000)
INTO jobs VALUES ('SA_MAN', 'Sales Manager', 10000, 20000)
INTO jobs VALUES ('SA_REP', 'Sales Representative', 6000, 12000)
INTO jobs VALUES ('ST_MAN', 'Stock Manager', 5500, 8500)
INTO jobs VALUES ('ST_CLERK', 'Stock Clerk', 2000, 5000)
INTO jobs VALUES ('IT_PROG', 'Programmer', 4000, 10000)
INTO jobs VALUES ('MK_MAN', 'Marketing Manager', 9000, 15000)
INTO jobs VALUES ('MK_REP', 'Marketing Representative', 4000, 9000)
SELECT 'X' FROM dual;
COMMIT;
--- 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 ALL
INTO job_history VALUES (102, '93-01-13', '98-07-24', 'IT_PROG', 60)
INTO job_history VALUES (101, '89-09-21', '93-10-27', 'AC_ACCOUNT', 110)
INTO job_history VALUES (101, '93-10-28', '97-03-15', 'AC_MGR', 110)
INTO job_history VALUES (201, '96-02-17', '99-12-19', 'MK_REP', 20)
INTO job_history VALUES (114, '98-03-24', '99-12-31', 'ST_CLERK', 50)
INTO job_history VALUES (122, '99-01-01', '99-12-31', 'ST_CLERK', 50)
INTO job_history VALUES (200, '87-09-17', '93-06-17', 'AD_ASST', 90)
INTO job_history VALUES (176, '98-03-24', '98-12-31', 'SA_REP', 80)
INTO job_history VALUES (176, '99-01-01', '99-12-31', 'SA_MAN', 80)
INTO job_history VALUES (200, '97-07-01', '98-12-31', 'AC_ACCOUNT', 90)
SELECT 'X' FROM dual;
COMMIT;
--- JOB_GRADES表：職種の等級別に給与範囲を識別します。給与範囲は重複しません
CREATE TABLE job_grades (
grade_level VARCHAR2(3),
lowest_sal NUMBER,
highest_sal NUMBER
);
INSERT ALL
INTO job_grades VALUES ('A', 1000, 2999)
INTO job_grades VALUES ('B', 3000, 5999)
INTO job_grades VALUES ('C', 6000, 9999)
INTO job_grades VALUES ('D', 10000, 14999)
INTO job_grades VALUES ('E', 15000, 24999)
INTO job_grades VALUES ('F', 25000, 40000)
SELECT 'X' FROM dual;
COMMIT;
---
--- ここまで
---