-
Notifications
You must be signed in to change notification settings - Fork 8
/
db.sql
142 lines (132 loc) · 9.75 KB
/
db.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
begin
for rec in (select table_name
from user_tables
where regexp_like(table_name, '^(BADGE|DEPARTMENT|JOB_TITLE|EMPLOYEE)')
)
loop
execute immediate 'drop table '||rec.table_name||' CASCADE CONSTRAINTS PURGE';
end loop;
end;
/
CREATE TABLE "BADGE" (
"BADGE_NUMBER"
NUMBER
GENERATED ALWAYS as IDENTITY
CONSTRAINT "BADGE_PK" PRIMARY KEY
,"BADGE_STATUS"
VARCHAR2(10)
NOT NULL
,"BADGE_EXPIRY_DATE"
DATE
NOT NULL
);
CREATE TABLE "DEPARTMENT" (
"DEPARTMENT_CODE"
NUMBER
GENERATED ALWAYS as IDENTITY
CONSTRAINT "DEP_PK" PRIMARY KEY
,"DEPARTMENT_NAME"
VARCHAR2(50)
NOT NULL
);
CREATE TABLE "JOB_TITLE" (
"JOB_TITLE_CODE"
NUMBER
GENERATED ALWAYS as IDENTITY
CONSTRAINT "JOB_PK" PRIMARY KEY
,"JOB_TITLE_NAME"
VARCHAR2(50)
NOT NULL
UNIQUE
,"DEPARTMENT_CODE"
NUMBER
CONSTRAINT "DEP_FK" REFERENCES "DEPARTMENT" ("DEPARTMENT_CODE")
NOT NULL
);
CREATE TABLE "EMPLOYEE" (
"ID"
NUMBER
GENERATED ALWAYS AS IDENTITY
CONSTRAINT "EMP_PK" PRIMARY KEY
,"FIRSTNAME"
VARCHAR2(100)
NOT NULL
,"LASTNAME"
VARCHAR2(100)
NOT NULL
,"BADGE_NUMBER"
NUMBER
CONSTRAINT "BADGE_FK" REFERENCES "BADGE" ("BADGE_NUMBER")
NOT NULL
UNIQUE
,"COUNTRY_CODE"
VARCHAR2(10)
NOT NULL
,"JOB_TITLE_CODE"
NUMBER
CONSTRAINT "JOB_FK" REFERENCES "JOB_TITLE" ("JOB_TITLE_CODE")
NOT NULL
,"START_DATE"
DATE
NOT NULL
,"LEAVE_DATE"
DATE
);
INSERT INTO BADGE (BADGE_STATUS, BADGE_EXPIRY_DATE) VALUES('Active', TIMESTAMP '2021-07-12 14:53:17.000000');
INSERT INTO BADGE (BADGE_STATUS, BADGE_EXPIRY_DATE) VALUES('Active', TIMESTAMP '2021-07-12 14:53:17.000000');
INSERT INTO BADGE (BADGE_STATUS, BADGE_EXPIRY_DATE) VALUES('Active', TIMESTAMP '2021-07-12 14:53:17.000000');
INSERT INTO BADGE (BADGE_STATUS, BADGE_EXPIRY_DATE) VALUES('Active', TIMESTAMP '2021-07-12 14:53:17.000000');
INSERT INTO BADGE (BADGE_STATUS, BADGE_EXPIRY_DATE) VALUES('Active', TIMESTAMP '2021-07-12 14:53:17.000000');
INSERT INTO BADGE (BADGE_STATUS, BADGE_EXPIRY_DATE) VALUES('Active', TIMESTAMP '2021-07-12 14:53:17.000000');
INSERT INTO BADGE (BADGE_STATUS, BADGE_EXPIRY_DATE) VALUES('Active', TIMESTAMP '2021-07-12 14:53:17.000000');
INSERT INTO BADGE (BADGE_STATUS, BADGE_EXPIRY_DATE) VALUES('Active', TIMESTAMP '2021-07-12 14:53:17.000000');
INSERT INTO BADGE (BADGE_STATUS, BADGE_EXPIRY_DATE) VALUES('Active', TIMESTAMP '2021-07-12 14:53:17.000000');
INSERT INTO BADGE (BADGE_STATUS, BADGE_EXPIRY_DATE) VALUES('Disabled', TIMESTAMP '2021-07-12 14:53:18.000000');
INSERT INTO BADGE (BADGE_STATUS, BADGE_EXPIRY_DATE) VALUES('Active', TIMESTAMP '2021-07-12 14:53:18.000000');
INSERT INTO BADGE (BADGE_STATUS, BADGE_EXPIRY_DATE) VALUES('Active', TIMESTAMP '2021-07-12 14:53:18.000000');
INSERT INTO BADGE (BADGE_STATUS, BADGE_EXPIRY_DATE) VALUES('Active', TIMESTAMP '2021-07-12 14:53:18.000000');
INSERT INTO BADGE (BADGE_STATUS, BADGE_EXPIRY_DATE) VALUES('Active', TIMESTAMP '2021-07-12 14:53:18.000000');
INSERT INTO BADGE (BADGE_STATUS, BADGE_EXPIRY_DATE) VALUES('Active', TIMESTAMP '2021-07-12 14:53:18.000000');
INSERT INTO BADGE (BADGE_STATUS, BADGE_EXPIRY_DATE) VALUES('Expired', TIMESTAMP '2020-09-05 14:53:18.000000');
INSERT INTO BADGE (BADGE_STATUS, BADGE_EXPIRY_DATE) VALUES('Active', TIMESTAMP '2021-07-12 14:53:18.000000');
INSERT INTO BADGE (BADGE_STATUS, BADGE_EXPIRY_DATE) VALUES('Active', TIMESTAMP '2021-07-12 14:53:18.000000');
INSERT INTO BADGE (BADGE_STATUS, BADGE_EXPIRY_DATE) VALUES('Active', TIMESTAMP '2021-07-12 14:53:18.000000');
INSERT INTO BADGE (BADGE_STATUS, BADGE_EXPIRY_DATE) VALUES('Active', TIMESTAMP '2021-07-12 14:53:18.000000');
INSERT INTO DEPARTMENT (DEPARTMENT_NAME) VALUES('Software' );
INSERT INTO DEPARTMENT (DEPARTMENT_NAME) VALUES('Sales' );
INSERT INTO DEPARTMENT (DEPARTMENT_NAME) VALUES('Finance' );
INSERT INTO DEPARTMENT (DEPARTMENT_NAME) VALUES('Marketing');
INSERT INTO DEPARTMENT (DEPARTMENT_NAME) VALUES('HR' );
INSERT INTO DEPARTMENT (DEPARTMENT_NAME) VALUES('HCM' );
INSERT INTO JOB_TITLE (JOB_TITLE_NAME, DEPARTMENT_CODE) VALUES('Software Engineer', 1);
INSERT INTO JOB_TITLE (JOB_TITLE_NAME, DEPARTMENT_CODE) VALUES('Software Support', 1);
INSERT INTO JOB_TITLE (JOB_TITLE_NAME, DEPARTMENT_CODE) VALUES('Account Manager', 2);
INSERT INTO JOB_TITLE (JOB_TITLE_NAME, DEPARTMENT_CODE) VALUES('Customer Success', 2);
INSERT INTO JOB_TITLE (JOB_TITLE_NAME, DEPARTMENT_CODE) VALUES('Accounts Payable', 3);
INSERT INTO JOB_TITLE (JOB_TITLE_NAME, DEPARTMENT_CODE) VALUES('Accounts Receivable', 3);
INSERT INTO JOB_TITLE (JOB_TITLE_NAME, DEPARTMENT_CODE) VALUES('Marketing Consultant', 4);
INSERT INTO JOB_TITLE (JOB_TITLE_NAME, DEPARTMENT_CODE) VALUES('Marketing Manager', 4);
INSERT INTO JOB_TITLE (JOB_TITLE_NAME, DEPARTMENT_CODE) VALUES('HR Manager', 5);
INSERT INTO JOB_TITLE (JOB_TITLE_NAME, DEPARTMENT_CODE) VALUES('HR Administrator', 5);
INSERT INTO JOB_TITLE (JOB_TITLE_NAME, DEPARTMENT_CODE) VALUES('Software Manager', 1);
INSERT INTO JOB_TITLE (JOB_TITLE_NAME, DEPARTMENT_CODE) VALUES('Sales Manager', 2);
INSERT INTO EMPLOYEE (FIRSTNAME, LASTNAME, BADGE_NUMBER, COUNTRY_CODE, JOB_TITLE_CODE, START_DATE, LEAVE_DATE) VALUES('Max', 'Payne', 1, 'irl', 1, TIMESTAMP '2020-06-07 14:53:18.000000', NULL );
INSERT INTO EMPLOYEE (FIRSTNAME, LASTNAME, BADGE_NUMBER, COUNTRY_CODE, JOB_TITLE_CODE, START_DATE, LEAVE_DATE) VALUES('Jack', 'Carver', 2, 'irl', 2, TIMESTAMP '2020-06-07 14:53:18.000000', NULL );
INSERT INTO EMPLOYEE (FIRSTNAME, LASTNAME, BADGE_NUMBER, COUNTRY_CODE, JOB_TITLE_CODE, START_DATE, LEAVE_DATE) VALUES('Duke', 'Nuken', 3, 'gb', 12, TIMESTAMP '2020-06-07 14:53:18.000000', NULL );
INSERT INTO EMPLOYEE (FIRSTNAME, LASTNAME, BADGE_NUMBER, COUNTRY_CODE, JOB_TITLE_CODE, START_DATE, LEAVE_DATE) VALUES('Donkey', 'Kong', 4, 'us', 4, TIMESTAMP '2020-06-07 14:53:18.000000', NULL );
INSERT INTO EMPLOYEE (FIRSTNAME, LASTNAME, BADGE_NUMBER, COUNTRY_CODE, JOB_TITLE_CODE, START_DATE, LEAVE_DATE) VALUES('Vault', 'Boy', 5, 'br', 6, TIMESTAMP '2020-06-07 14:53:19.000000', NULL );
INSERT INTO EMPLOYEE (FIRSTNAME, LASTNAME, BADGE_NUMBER, COUNTRY_CODE, JOB_TITLE_CODE, START_DATE, LEAVE_DATE) VALUES('Marcus', 'Fenix', 6, 'ind', 6, TIMESTAMP '2020-06-07 14:53:19.000000', NULL );
INSERT INTO EMPLOYEE (FIRSTNAME, LASTNAME, BADGE_NUMBER, COUNTRY_CODE, JOB_TITLE_CODE, START_DATE, LEAVE_DATE) VALUES('Leon', 'Kennedy', 7, 'gb', 9, TIMESTAMP '2020-06-07 14:53:19.000000', NULL );
INSERT INTO EMPLOYEE (FIRSTNAME, LASTNAME, BADGE_NUMBER, COUNTRY_CODE, JOB_TITLE_CODE, START_DATE, LEAVE_DATE) VALUES('Manny', 'Cavalera', 8, 'us', 12, TIMESTAMP '2020-06-07 14:53:19.000000', NULL );
INSERT INTO EMPLOYEE (FIRSTNAME, LASTNAME, BADGE_NUMBER, COUNTRY_CODE, JOB_TITLE_CODE, START_DATE, LEAVE_DATE) VALUES('Harman', 'Smith', 9, 'irl', 3, TIMESTAMP '2020-06-07 14:53:19.000000', NULL );
INSERT INTO EMPLOYEE (FIRSTNAME, LASTNAME, BADGE_NUMBER, COUNTRY_CODE, JOB_TITLE_CODE, START_DATE, LEAVE_DATE) VALUES('Samus', 'Aran', 10, 'irl', 10, TIMESTAMP '2020-06-07 14:53:19.000000', NULL );
INSERT INTO EMPLOYEE (FIRSTNAME, LASTNAME, BADGE_NUMBER, COUNTRY_CODE, JOB_TITLE_CODE, START_DATE, LEAVE_DATE) VALUES('Nathan', 'Drake', 11, 'fr', 2, TIMESTAMP '2020-06-07 14:53:19.000000', NULL );
INSERT INTO EMPLOYEE (FIRSTNAME, LASTNAME, BADGE_NUMBER, COUNTRY_CODE, JOB_TITLE_CODE, START_DATE, LEAVE_DATE) VALUES('Lara', 'Croft', 12, 'irl', 8, TIMESTAMP '2017-12-20 14:53:19.000000', TIMESTAMP '2020-09-05 14:53:19.000000');
INSERT INTO EMPLOYEE (FIRSTNAME, LASTNAME, BADGE_NUMBER, COUNTRY_CODE, JOB_TITLE_CODE, START_DATE, LEAVE_DATE) VALUES('Gordon', 'Freeman', 13, 'gb', 8, TIMESTAMP '2020-06-07 14:53:19.000000', NULL );
INSERT INTO EMPLOYEE (FIRSTNAME, LASTNAME, BADGE_NUMBER, COUNTRY_CODE, JOB_TITLE_CODE, START_DATE, LEAVE_DATE) VALUES('Adam', 'Jensen', 14, 'us', 11, TIMESTAMP '2017-12-20 14:53:19.000000', TIMESTAMP '2020-09-05 14:53:19.000000');
INSERT INTO EMPLOYEE (FIRSTNAME, LASTNAME, BADGE_NUMBER, COUNTRY_CODE, JOB_TITLE_CODE, START_DATE, LEAVE_DATE) VALUES('CJ', 'Denton', 15, 'irl', 7, TIMESTAMP '2019-08-12 14:53:19.000000', NULL );
INSERT INTO EMPLOYEE (FIRSTNAME, LASTNAME, BADGE_NUMBER, COUNTRY_CODE, JOB_TITLE_CODE, START_DATE, LEAVE_DATE) VALUES('Max', 'Payne', 16, 'irl', 4, TIMESTAMP '2019-08-12 14:53:19.000000', NULL );
INSERT INTO EMPLOYEE (FIRSTNAME, LASTNAME, BADGE_NUMBER, COUNTRY_CODE, JOB_TITLE_CODE, START_DATE, LEAVE_DATE) VALUES('Liu', 'Kang', 17, 'irl', 6, TIMESTAMP '2019-08-12 14:53:19.000000', NULL );
INSERT INTO EMPLOYEE (FIRSTNAME, LASTNAME, BADGE_NUMBER, COUNTRY_CODE, JOB_TITLE_CODE, START_DATE, LEAVE_DATE) VALUES('Alyx', 'Vance', 18, 'ind', 5, TIMESTAMP '2019-08-12 14:53:19.000000', NULL );
INSERT INTO EMPLOYEE (FIRSTNAME, LASTNAME, BADGE_NUMBER, COUNTRY_CODE, JOB_TITLE_CODE, START_DATE, LEAVE_DATE) VALUES('Maya', 'Fey', 19, 'br', 1, TIMESTAMP '2019-08-12 14:53:19.000000', NULL );
INSERT INTO EMPLOYEE (FIRSTNAME, LASTNAME, BADGE_NUMBER, COUNTRY_CODE, JOB_TITLE_CODE, START_DATE, LEAVE_DATE) VALUES('Erica', 'Anderson', 20, 'col', 1, TIMESTAMP '2019-08-12 14:53:19.000000', NULL );