-
Notifications
You must be signed in to change notification settings - Fork 34
/
hr_quick_start_23.sql
3483 lines (3081 loc) · 73.9 KB
/
hr_quick_start_23.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
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
REM
REM HR sample schema wrapper.
REM
REM This script tries to be a "one and done" script to load the HR schema
REM by guiding the novice as best as possible to ensure a sucessfull installation
REM of the schema. Things it will endeavour to do:
REM
REM - not let you install into a root container by mistake
REM - make sure you are connected correctly
REM - if you are connected as HR, we'll try reload the schema objects and keep the user
REM - if you are connected not as HR, we'll try drop/recreate the HR schema
REM - we check for required privs in the HR schema
REM - we check for required privs for an admin to build the HR schema from scratch
REM - we check for appropriate tablespace quotas
REM - we check for default tablespaces
REM - we check for existing sessions as HR which would block a drop
REM - we check for OS file writability for spool files
REM
REM In all cases, we try to provide guidance on remedial action to follow if
REM the installation fails or cannot proceed.
REM
REM Like anything, you could come up with a bizarre set of circumstances in which
REM this can be broken, but it should work for most. If you do you break it, please
REM get in touch so I can make it even more carefree for beginners.
REM
REM
REM Standard disclaimer - anything in here can be used at your own risk.
REM
REM No warranty or liability etc etc etc. See the license file in the git repo root
REM
REM *** USE AT YOUR OWN RISK ***
REM
set echo off
set lines 200
set termout off
set markup csv off
set markup html off
set autotrace off
set timing off
set sqlprompt 'SQL> '
set echo off
set feedback on
set heading on
set define '&'
set tab off
set long 50000
set longchunksize 500
set pages 999
set verify off
undefine 1
undefine 2
undefine 3
undefine 4
set termout on
whenever sqlerror continue
clear screen
pro | 1) Preliminary checks
pro | =====================
pro |
pro | You should be connected to the database at this point.
pro | If you are, then you will see the following:
pro |
pro | >>> Connected as: YOUR_USER <<<
pro |
pro | If you are not, you're will see the following
pro |
pro | >>>> SP2-0640: Not connected <<<<
pro |
pro | If you get this error, press Ctrl-C to exit this script and
pro | connect first before running it again.
pro |
pro | Tip: For Express Edition, the command to connect is *probably*
pro |
pro | SQL> connect system/yourpassword@//localhost/XEPDB1
pro |
pro | Once you are connected OK, then press Enter to proceed
pro |
set pages 0
set feedback off
select 'Connected as: '||user from dual;
pro
set pages 999
accept dummy prompt 'Enter to proceed, Ctrl-C to stop'
pro |
pro | Checking that we can write a file to the current directory
pro | If we can't, then this script will exit here. Please make
pro | you are running the script from the directory you saved it to
pro | and this directory is writable
pro |
whenever oserror exit
spool file_write_test.out
spool off
whenever oserror continue
pro File test passed!
pro |
pro |
pro | Now checking database details. If any of these fail,
pro | the script will exit with the error that you need
pro | to resolve.
pro |
whenever sqlerror exit
set serverout on
begin
if sys_context('USERENV','CON_ID') = '0' then
dbms_output.put_line('Non-container database. No pluggable check needed...proceeding');
elsif sys_context('USERENV','CON_ID') = '1' then
dbms_output.put_line('---------------------------------------------------------');
dbms_output.put_line('ERROR TEXT:');
dbms_output.put_line('Sample data should not be loaded into the root container.');
dbms_output.put_line('You should always connect to a pluggable database.');
dbms_output.put_line('eg. connect system/yourpassword@//localhost/XEPDB1 for Express Edition.');
dbms_output.put_line('---------------------------------------------------------');
raise_application_error(-20000,'Script exiting with error');
else
dbms_output.put_line('Container database. PDB '||sys_context('USERENV','CON_NAME')||' will be used for installation...proceeding');
end if;
end;
/
whenever sqlerror continue
pro |
pro | Checking current user details
pro |
begin
if user = 'HR' then
dbms_output.put_line('You are connected as HR so assuming this is a re-install.');
dbms_output.put_line('This installation will drop any existing objects in this');
dbms_output.put_line('schema. If this was not what you wanted, then press Ctrl-C');
dbms_output.put_line('the installation, otherwise press Enter to continue');
else
--
-- this user needs DBA privs
--
dbms_output.put_line('You are connected as '||user||', ie, not the HR schema.');
dbms_output.put_line('Hence this installation will drop the HR schema entirely');
dbms_output.put_line('and recreate it. If this was not what you wanted, then press Ctrl-C');
dbms_output.put_line('the installation, otherwise press Enter to continue');
end if;
end;
/
pro
accept dummy prompt 'Enter to proceed, Ctrl-C to stop'
pro |
pro | Checking required privileges
pro |
whenever sqlerror exit
set serverout on
declare
l_priv sys.odcivarchar2list :=
sys.odcivarchar2list (
'CREATE MATERIALIZED VIEW',
'CREATE PROCEDURE',
'CREATE SEQUENCE',
'CREATE SESSION',
'CREATE SYNONYM',
'CREATE TABLE',
'CREATE TRIGGER',
'CREATE TYPE',
'CREATE VIEW'
);
l_fail boolean := false;
begin
if user = 'HR' then
for i in ( select p.column_value, s.privilege
from table(l_priv) p,
session_privs s
where p.column_value = s.privilege(+)
order by 1
)
loop
if i.privilege is not null then
dbms_output.put_line('Privilege '||rpad(i.column_value,20,'.')||'...OK');
else
dbms_output.put_line('Privilege '||rpad(i.column_value,20,'.')||'...FAIL');
l_fail := true;
end if;
end loop;
if l_fail then
dbms_output.put_line('---------------------------------------------------------');
dbms_output.put_line('ERROR TEXT:');
dbms_output.put_line('One or more privileges for the HR schema are missing.');
dbms_output.put_line('We need these to successfully create/load the schema.');
dbms_output.put_line('For each missing privilege, an admin account (eg SYSTEM) needs');
dbms_output.put_line('to issue: GRANT <privilege name> TO HR');
dbms_output.put_line('---------------------------------------------------------');
raise_application_error(-20000,'Script exiting with error');
end if;
end if;
end;
/
whenever sqlerror continue
whenever sqlerror exit
set serverout on
declare
l_priv sys.odcivarchar2list :=
sys.odcivarchar2list (
'DROP USER'
,'CREATE USER'
,'ALTER SESSION'
,'CREATE ANY TABLE'
,'CREATE ANY CLUSTER'
,'CREATE ANY SYNONYM'
,'CREATE ANY VIEW'
,'CREATE ANY SEQUENCE'
,'CREATE ANY PROCEDURE'
,'CREATE ANY TRIGGER'
,'CREATE ANY TYPE'
,'CREATE ANY OPERATOR'
,'CREATE ANY INDEXTYPE'
,'CREATE ANY INDEX'
,'ALTER USER'
,'COMMENT ANY TABLE'
,'GRANT ANY OBJECT PRIVILEGE'
--,'GRANT ANY PRIVILEGE'
,'CREATE SESSION'
,'ALTER ANY TABLE'
,'SELECT ANY TABLE'
,'INSERT ANY TABLE'
,'UPDATE ANY TABLE'
,'DELETE ANY TABLE'
,'ALTER ANY TRIGGER'
,'ANALYZE ANY'
);
l_fail boolean := false;
l_sess int;
l_priv_cnt int;
begin
if user != 'HR' then
for i in ( select p.column_value, s.privilege
from table(l_priv) p,
session_privs s
where p.column_value = s.privilege(+)
order by 1
)
loop
if i.privilege is not null then
dbms_output.put_line('Privilege '||rpad(i.column_value,30,'.')||'...OK');
else
--
-- we might be able to get away with less here (we'll try)
--
if i.column_value = 'GRANT ANY PRIVILEGE' then
begin
execute immediate q'{
with role_list as (
select distinct granted_role
from dba_role_privs
start with grantee = user
connect by prior granted_role = grantee
union all
select user from dual
union all
select 'PUBLIC' from dual
)
select count(distinct p.privilege)
from role_list r, dba_sys_privs p
where p.grantee = r.granted_role
and p.admin_option = 'YES'
and p.privilege in (
'CREATE MATERIALIZED VIEW',
'CREATE PROCEDURE',
'CREATE SEQUENCE',
'CREATE SESSION',
'CREATE SYNONYM',
'CREATE TABLE',
'CREATE TRIGGER',
'CREATE TYPE',
'CREATE VIEW'
)
}' into l_priv_cnt;
if l_priv_cnt = 12 then
dbms_output.put_line('Privilege '||rpad(i.column_value,30,'.')||'...OK (NOT NEEDED)');
else
dbms_output.put_line('Privilege '||rpad(i.column_value,30,'.')||'...FAIL');
l_fail := true;
end if;
exception
when others then
dbms_output.put_line('Privilege '||rpad(i.column_value,30,'.')||'...FAIL');
l_fail := true;
end;
else
dbms_output.put_line('Privilege '||rpad(i.column_value,30,'.')||'...FAIL');
l_fail := true;
end if;
end if;
end loop;
--
-- we also need v$session
--
begin
execute immediate 'select 1 from v$session where rownum = 1' into l_sess;
dbms_output.put_line('Privilege '||rpad('SELECT ON GV$SESSION',30,'.')||'...OK');
exception
when others then
dbms_output.put_line('Privilege '||rpad('SELECT ON GV$SESSION',30,'.')||'...FAIL');
l_fail := true;
end;
if l_fail then
dbms_output.put_line('---------------------------------------------------------');
dbms_output.put_line('ERROR TEXT:');
dbms_output.put_line('One or more privileges for the admin account you are using');
dbms_output.put_line('to build the HR schema are missing.');
dbms_output.put_line('We need these to successfully create/load the schema.');
dbms_output.put_line('For each missing privilege, an admin account (eg SYSTEM) needs');
dbms_output.put_line('to issue: GRANT <privilege name> TO '||user);
dbms_output.put_line('---------------------------------------------------------');
raise_application_error(-20000,'Script exiting with error');
end if;
end if;
end;
/
whenever sqlerror continue
pro |
pro | Checking tablespaces
pro |
whenever sqlerror exit
set serverout on
declare
l_fail boolean := false;
l_ts int;
l_unlim int;
l_ts_name varchar2(100);
begin
if user != 'HR' then
select count(trim(property_value))
into l_ts
from database_properties
where property_name in ('DEFAULT_PERMANENT_TABLESPACE','DEFAULT_TEMP_TABLESPACE' );
if l_ts < 2 then
dbms_output.put_line('---------------------------------------------------------');
dbms_output.put_line('ERROR TEXT:');
dbms_output.put_line('The HR schema builds into the default tablespace for this');
dbms_output.put_line('database so this database property needs to be set.');
dbms_output.put_line('You need to issue:');
dbms_output.put_line('SQL> alter database default tablespace <tspace>');
dbms_output.put_line('SQL> alter database default temporary tablespace <temp_tspace>');
dbms_output.put_line('---------------------------------------------------------');
raise_application_error(-20000,'Script exiting with error');
end if;
else
select count(*)
into l_unlim
from session_privs
where privilege = 'UNLIMITED TABLESPACE';
if l_unlim = 0 then
select default_tablespace
into l_ts_name
from user_users;
select sum(decode(max_bytes,-1,1e10,max_bytes))
into l_unlim
from user_ts_quotas
where tablespace_name = l_ts_name;
if l_unlim < 30*1024*1024 then
dbms_output.put_line('---------------------------------------------------------');
dbms_output.put_line('ERROR TEXT:');
dbms_output.put_line('The HR schema needs to have a tablespace quota set to that');
dbms_output.put_line('can obtain space in the database.');
dbms_output.put_line('You need to issue:');
dbms_output.put_line('SQL> alter user HR quota 50m on '||l_ts_name);
dbms_output.put_line('---------------------------------------------------------');
raise_application_error(-20000,'Script exiting with error');
end if;
end if;
end if;
dbms_output.put_line('Checks .......OK');
end;
/
whenever sqlerror continue
pro |
pro | Checking existing HR details
pro |
whenever sqlerror exit
set serverout on
declare
l_fail boolean := false;
l_sess int;
begin
if user != 'HR' then
execute immediate 'select count(*) from v$session where username = ''HR'''
into l_sess;
if l_sess > 0 then
dbms_output.put_line('---------------------------------------------------------');
dbms_output.put_line('ERROR TEXT:');
dbms_output.put_line('Someone is currently logged onto the database as user HR');
dbms_output.put_line('which means we cannot drop and replace the schema.');
dbms_output.put_line('Please get this person to log out and re-run the install.');
dbms_output.put_line('---------------------------------------------------------');
raise_application_error(-20000,'Script exiting with error');
end if;
end if;
dbms_output.put_line('Checks .......OK');
end;
/
whenever sqlerror continue
set termout off
undefine default_ts
undefine temp_ts
undefine pass
col PROPERTY_VALUE new_value default_ts
select PROPERTY_VALUE
from database_properties
where PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';
col PROPERTY_VALUE new_value temp_ts
select PROPERTY_VALUE
from database_properties
where PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';
col rndstr new_value pass
select dbms_random.string('A',10)||'$$'||trunc(dbms_random.value(100,999)) rndstr from dual;
set termout on
set termout off
set serverout on
spool nonhr_create.sql
declare
l_need_drop int;
begin
if user != 'HR' then
dbms_output.put_line('pro |');
dbms_output.put_line('pro | The new/replaced HR schema will be created now.');
dbms_output.put_line('pro | ');
dbms_output.put_line('pro | Note down this password for the HR schema. You will need it to connect');
dbms_output.put_line('pro |');
dbms_output.put_line('pro | Password (case-sensitive): &&pass');
dbms_output.put_line('pro |');
dbms_output.put_line('pro | The script will exit on any error encountered, because it should run');
dbms_output.put_line('pro | to completion with no errors at all');
dbms_output.put_line('pro | ');
dbms_output.put_line('accept dummy prompt ''Press Enter to start''');
select count(*)
into l_need_drop
from all_users
where username = 'HR';
if l_need_drop > 0 then
dbms_output.put_line('DROP USER HR CASCADE;');
end if;
dbms_output.put_line('CREATE USER hr IDENTIFIED BY &&pass;');
dbms_output.put_line('ALTER USER hr DEFAULT TABLESPACE &&default_ts QUOTA UNLIMITED ON &&default_ts;');
dbms_output.put_line('ALTER USER hr TEMPORARY TABLESPACE &&temp_ts;');
dbms_output.put_line('grant CREATE SESSION to hr;');
dbms_output.put_line('grant ALTER SESSION to hr;');
dbms_output.put_line('grant UNLIMITED TABLESPACE to hr;');
dbms_output.put_line('GRANT CREATE MATERIALIZED VIEW to hr;');
dbms_output.put_line('GRANT CREATE PROCEDURE to hr;');
dbms_output.put_line('GRANT CREATE SEQUENCE to hr;');
dbms_output.put_line('GRANT CREATE SESSION to hr;');
dbms_output.put_line('GRANT CREATE SYNONYM to hr;');
dbms_output.put_line('GRANT CREATE TABLE to hr;');
dbms_output.put_line('GRANT CREATE TRIGGER to hr;');
dbms_output.put_line('GRANT CREATE TYPE to hr;');
dbms_output.put_line('GRANT CREATE VIEW to hr;');
dbms_output.put_line('alter session set current_schema = hr;');
else
dbms_output.put_line('pro |');
dbms_output.put_line('pro | The existing HR objects will now be dropped and the fresh objects created');
dbms_output.put_line('pro | ');
dbms_output.put_line('pro | The script will exit on any error encountered, because it should run');
dbms_output.put_line('pro | to completion with no errors at all');
dbms_output.put_line('pro | ');
dbms_output.put_line('accept dummy prompt ''Press Enter to start''');
end if;
end;
/
spool off
set termout on
whenever sqlerror exit
@nonhr_create.sql
whenever sqlerror exit
set serverout on
declare
l_cnt int;
begin
if user = 'HR' then
for iter in 1 .. 10 loop
for i in ( select object_type, object_name
from user_objects
order by decode(mod(iter,2),0,-1,1)*object_id
)
loop
begin
execute immediate 'drop '||i.object_type||' '||i.object_name||case when i.object_type = 'TABLE' then ' cascade constraints purge' end;
exception
when others then null;
end;
end loop;
select count(*)
into l_cnt
from user_objects;
exit when l_cnt = 0;
end loop;
if l_cnt > 0 then
dbms_output.put_line('---------------------------------------------------------');
dbms_output.put_line('ERROR TEXT:');
dbms_output.put_line('Something went wrong dropping the objects - it could be ');
dbms_output.put_line('that someone has an uncomitted transaction on one of the ');
dbms_output.put_line('tables which means we cannot drop it.');
dbms_output.put_line('Please check on this and then re-run the install.');
dbms_output.put_line('---------------------------------------------------------');
raise_application_error(-20000,'Script exiting with error');
end if;
end if;
end;
/
whenever sqlerror continue
---------------
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(60)
, 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;
rem ********************************************************************
rem Create indexes
Prompt ****** Creating indexes ...
CREATE INDEX emp_department_ix
ON employees (department_id);
CREATE INDEX emp_job_ix
ON employees (job_id);
CREATE INDEX emp_manager_ix
ON employees (manager_id);
CREATE INDEX emp_name_ix
ON employees (last_name, first_name);
CREATE INDEX dept_location_ix
ON departments (location_id);
CREATE INDEX jhist_job_ix
ON job_history (job_id);
CREATE INDEX jhist_employee_ix
ON job_history (employee_id);
CREATE INDEX jhist_department_ix
ON job_history (department_id);
CREATE INDEX loc_city_ix
ON locations (city);
CREATE INDEX loc_state_province_ix
ON locations (state_province);
CREATE INDEX loc_country_ix
ON locations (country_id);
rem ********************************************************************
rem Add table column comments
Prompt ****** Adding table column comments ...
COMMENT ON TABLE regions
IS 'Regions table that contains region numbers and names. references with the Countries table.';
COMMENT ON COLUMN regions.region_id
IS 'Primary key of regions table.';
COMMENT ON COLUMN regions.region_name
IS 'Names of regions. Locations are in the countries of these regions.';
COMMENT ON TABLE locations
IS 'Locations table that contains specific address of a specific office,
warehouse, and/or production site of a company. Does not store addresses /
locations of customers. references with the departments and countries tables. ';
COMMENT ON COLUMN locations.location_id
IS 'Primary key of locations table';
COMMENT ON COLUMN locations.street_address
IS 'Street address of an office, warehouse, or production site of a company.
Contains building number and street name';
COMMENT ON COLUMN locations.postal_code
IS 'Postal code of the location of an office, warehouse, or production site
of a company. ';
COMMENT ON COLUMN locations.city
IS 'A not null column that shows city where an office, warehouse, or
production site of a company is located. ';
COMMENT ON COLUMN locations.state_province
IS 'State or Province where an office, warehouse, or production site of a
company is located.';
COMMENT ON COLUMN locations.country_id
IS 'Country where an office, warehouse, or production site of a company is
located. Foreign key to country_id column of the countries table.';
rem *********************************************
COMMENT ON TABLE departments
IS 'Departments table that shows details of departments where employees
work. references with locations, employees, and job_history tables.';
COMMENT ON COLUMN departments.department_id
IS 'Primary key column of departments table.';
COMMENT ON COLUMN departments.department_name
IS 'A not null column that shows name of a department. Administration,
Marketing, Purchasing, Human Resources, Shipping, IT, Executive, Public
Relations, Sales, Finance, and Accounting. ';
COMMENT ON COLUMN departments.manager_id
IS 'Manager_id of a department. Foreign key to employee_id column of employees table. The manager_id column of the employee table references this column.';
COMMENT ON COLUMN departments.location_id
IS 'Location id where a department is located. Foreign key to location_id column of locations table.';
rem *********************************************
COMMENT ON TABLE job_history
IS 'Table that stores job history of the employees. If an employee
changes departments within the job or changes jobs within the department,
new rows get inserted into this table with old job information of the
employee. Contains a complex primary key: employee_id+start_date.
References with jobs, employees, and departments tables.';
COMMENT ON COLUMN job_history.employee_id
IS 'A not null column in the complex primary key employee_id+start_date.
Foreign key to employee_id column of the employee table';
COMMENT ON COLUMN job_history.start_date
IS 'A not null column in the complex primary key employee_id+start_date.
Must be less than the end_date of the job_history table. (enforced by
constraint jhist_date_interval)';
COMMENT ON COLUMN job_history.end_date
IS 'Last day of the employee in this job role. A not null column. Must be
greater than the start_date of the job_history table.
(enforced by constraint jhist_date_interval)';
COMMENT ON COLUMN job_history.job_id
IS 'Job role in which the employee worked in the past; foreign key to
job_id column in the jobs table. A not null column.';
COMMENT ON COLUMN job_history.department_id
IS 'Department id in which the employee worked in the past; foreign key to deparment_id column in the departments table';
rem *********************************************
COMMENT ON TABLE countries
IS 'country table. References with locations table.';
COMMENT ON COLUMN countries.country_id
IS 'Primary key of countries table.';
COMMENT ON COLUMN countries.country_name
IS 'Country name';
COMMENT ON COLUMN countries.region_id
IS 'Region ID for the country. Foreign key to region_id column in the departments table.';
rem *********************************************
COMMENT ON TABLE jobs
IS 'jobs table with job titles and salary ranges.
References with employees and job_history table.';
COMMENT ON COLUMN jobs.job_id
IS 'Primary key of jobs table.';
COMMENT ON COLUMN jobs.job_title
IS 'A not null column that shows job title, e.g. AD_VP, FI_ACCOUNTANT';