.::: Create CDB & Sample Database in Oracle 19C :::.

 

1. Login sebagai SYS atau SYSTEM

Masuk ke CDB lalu kasih privilege ke HRADMIN:

-- Masuk ke CDB sebagai SYS
sqlplus sys@//localhost:1521/TGHDB as sysdba

-- Pindah ke PDB TGHDBHR
ALTER SESSION SET CONTAINER=TGHDBHR;

-- Beri hak penuh

CREATE USER hradmin IDENTIFIED BY hrpass;
GRANT CONNECT, RESOURCE TO hradmin;
ALTER USER hradmin QUOTA UNLIMITED ON USERS;

GRANT CONNECT, RESOURCE TO hradmin;
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE TO hradmin;

-- Kalau mau full DBA di PDB
GRANT DBA TO hradmin;

sqlplus hradmin/hrpass@//localhost:1521/TGHDBHR
 
2. Buat PDB Baru (sudah Anda coba)

Misalnya PDB bernama TGHDBHR sudah berhasil dibuat.
Login SYS ke PDB itu:

ALTER SESSION SET CONTAINER=TGHDBHR;

3. Buat Tabel HR Sederhana
a. Departments
CREATE TABLE departments (
    department_id   NUMBER(4) PRIMARY KEY,
    department_name VARCHAR2(30) NOT NULL,
    location        VARCHAR2(50)
);

b. Employees
CREATE TABLE employees (
    employee_id     NUMBER(6) PRIMARY KEY,
    first_name      VARCHAR2(20),
    last_name       VARCHAR2(25) NOT NULL,
    email           VARCHAR2(25) UNIQUE 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),
    CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
);

c. Jobs
CREATE TABLE jobs (
    job_id      VARCHAR2(10) PRIMARY KEY,
    job_title   VARCHAR2(35) NOT NULL,
    min_salary  NUMBER(6),
    max_salary  NUMBER(6)
);

4. Insert Sample Data
Departments
INSERT INTO departments VALUES (10, 'Administration', 'Jakarta');
INSERT INTO departments VALUES (20, 'Finance', 'Bandung');
INSERT INTO departments VALUES (30, 'Human Resources', 'Surabaya');
INSERT INTO departments VALUES (40, 'IT', 'Jakarta');
INSERT INTO departments VALUES (50, 'Marketing', 'Medan');


Jobs
INSERT INTO jobs VALUES ('AD_PRES', 'President', 15000, 25000);
INSERT INTO jobs VALUES ('FI_MGR', 'Finance Manager', 9000, 15000);
INSERT INTO jobs VALUES ('HR_REP', 'HR Representative', 4000, 9000);
INSERT INTO jobs VALUES ('IT_PROG', 'Programmer', 4000, 10000);
INSERT INTO jobs VALUES ('MK_REP', 'Marketing Rep', 3000, 7000);

Employees
INSERT INTO employees VALUES 
(100, 'Steven', 'King', 'SKING', '515.123.4567', DATE '2003-06-17', 'AD_PRES', 24000, NULL, NULL, 10);

INSERT INTO employees VALUES 
(101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', DATE '2005-09-21', 'FI_MGR', 17000, NULL, 100, 20);

INSERT INTO employees VALUES 
(102, 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', DATE '2001-01-13', 'HR_REP', 9000, NULL, 101, 30);

INSERT INTO employees VALUES 
(103, 'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567', DATE '2006-01-03', 'IT_PROG', 6000, NULL, 102, 40);

INSERT INTO employees VALUES 
(104, 'Bruce', 'Ernst', 'BERNST', '590.423.4568', DATE '2007-05-21', 'IT_PROG', 7000, NULL, 103, 40);

INSERT INTO employees VALUES 
(105, 'David', 'Austin', 'DAUSTIN', '590.423.4569', DATE '2005-06-25', 'MK_REP', 5000, NULL, 102, 50);

INSERT INTO employees VALUES 
(106, 'Valli', 'Pataballa', 'VPATABAL', '590.423.4560', DATE '2006-02-05', 'IT_PROG', 4800, NULL, 103, 40);

INSERT INTO employees VALUES 
(107, 'Diana', 'Lorentz', 'DLORENTZ', '590.423.5567', DATE '2007-02-07', 'MK_REP', 4500, NULL, 105, 50);

INSERT INTO employees VALUES 
(108, 'Nancy', 'Greenberg', 'NGREENBE', '515.124.4569', DATE '2002-08-17', 'FI_MGR', 12000, NULL, 101, 20);

INSERT INTO employees VALUES 
(109, 'Daniel', 'Faviet', 'DFAVIET', '515.124.4169', DATE '2006-08-16', 'HR_REP', 6000, NULL, 102, 30);

Lalu commit:

COMMIT;

5. Uji Query
SELECT e.first_name, e.last_name, j.job_title, d.department_name, e.salary
FROM employees e
JOIN jobs j ON e.job_id = j.job_id
JOIN departments d ON e.department_id = d.department_id;
 

Contoh hasil:

FIRST_NAME  LAST_NAME  JOB_TITLE            DEPARTMENT_NAME   SALARY
----------  ---------  -------------------  ----------------  ------
Steven      King       President            Administration    24000
Neena       Kochhar    Finance Manager      Finance           17000
Lex         De Haan    HR Representative    Human Resources    9000


6. other query 

1. Join sederhana (Employees ? Departments)

Cari nama karyawan beserta nama departemen:

SELECT e.employee_id, e.first_name || ' ' || e.last_name AS employee_name,
       d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
 

2. Join 3 tabel (Employees ? Departments ? Jobs)

Cari nama karyawan, departemen, dan nama pekerjaannya:

SELECT e.first_name || ' ' || e.last_name AS employee_name,
       d.department_name,
       j.job_title,
       e.salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON e.job_id = j.job_id;
 

3. Subquery (cari gaji di atas rata-rata)

Cari karyawan yang gajinya di atas rata-rata seluruh karyawan:

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
 

4. Aggregasi dengan GROUP BY

Jumlah karyawan per departemen:

SELECT d.department_name,
       COUNT(e.employee_id) AS total_employees
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name
ORDER BY total_employees DESC;
 

5. Self-Join (relasi manager ? bawahan)

Tampilkan karyawan beserta nama manajernya:

SELECT e.employee_id,
       e.first_name || ' ' || e.last_name AS employee_name,
       m.first_name || ' ' || m.last_name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
ORDER BY manager_name NULLS FIRST;