【Oracle】SELECT結果を同一テーブルへINSERTする

1
-- テーブル作成: 部署テーブル
2
CREATE TABLE departments (
3
    dept_id    NUMBER PRIMARY KEY,
4
    dept_name  VARCHAR2(100),
5
    base_salary     NUMBER(10,2)
6
);
7

8
-- テーブル作成: 従業員テーブル
9
CREATE TABLE employees (
10
    emp_id     NUMBER PRIMARY KEY,
11
    emp_name   VARCHAR2(100),
12
    salary     NUMBER(10,2),
13
    dept_id    NUMBER REFERENCES departments(dept_id),
14
    created_at DATE DEFAULT SYSDATE
15
);
16

17
-- テーブル作成: usersテーブル
18
CREATE TABLE users (
19
  user_id NUMBER PRIMARY KEY,
20
  name    VARCHAR2(50),
21
  status  VARCHAR2(20)
22
);
23

24
-- テーブル作成: audit_logテーブル
25
CREATE TABLE audit_log (
26
  user_id NUMBER,
27
  action  VARCHAR2(20),
28
  logtime DATE
29
);
30

31
-- テーブル作成: report_logテーブル
32
CREATE TABLE report_log (
33
  user_id NUMBER,
34
  result  VARCHAR2(20),
35
  logtime DATE
36
);
37

38
-- テーブル作成: high_salaryテーブル
39
CREATE TABLE high_salary (
40
id NUMBER,
41
name VARCHAR2(50)
42
);
43

44
-- テーブル作成: low_salaryテーブル
45
CREATE TABLE low_salary (
46
id NUMBER,
47
name VARCHAR2(50)
48
);
49

50
-- サンプルデータ投入: 部署
51
INSERT INTO departments (dept_id, dept_name, base_salary) VALUES (10, 'Sales', '3000');
52
INSERT INTO departments (dept_id, dept_name, base_salary) VALUES (20, 'HR', '4000');
53
INSERT INTO departments (dept_id, dept_name, base_salary) VALUES (30, 'IT', '5000');
54

55
-- サンプルデータ投入: 従業員
56
INSERT INTO employees (emp_id, emp_name, salary, dept_id, created_at)
57
VALUES (1, 'Alice', 5000, 10, TO_DATE('2023-06-01','YYYY-MM-DD'));
58

59
INSERT INTO employees (emp_id, emp_name, salary, dept_id, created_at)
60
VALUES (2, 'Bob', 4500, 10, TO_DATE('2023-09-15','YYYY-MM-DD'));
61

62
INSERT INTO employees (emp_id, emp_name, salary, dept_id, created_at)
63
VALUES (3, 'Charlie', 6000, 20, TO_DATE('2024-02-20','YYYY-MM-DD'));
64

65
INSERT INTO employees (emp_id, emp_name, salary, dept_id, created_at)
66
VALUES (4, 'Diana', 7000, 30, TO_DATE('2024-05-10','YYYY-MM-DD'));
67

68
-- サンプルデータ投入:users
69
INSERT INTO users VALUES (101, 'User_A', 'ACTIVE');
70
INSERT INTO users VALUES (102, 'User_B', 'INACTIVE');
71
INSERT INTO users VALUES (103, 'User_C', 'ACTIVE');
72
INSERT INTO users VALUES (104, 'User_D', 'ACTIVE');
73

74
COMMIT;