Pertemuan 5

INSERT INTO departments(department_id, department_name, manager_id, location_id)
VALUES (70, 'Public Relation', 100,1700);

INSERT INTO departments(department_id, department_name)
VALUES (30, 'Purchasing');

INSERT INTO departments
VALUES (100, 'Finance', NULL,NULL);

INSERT INTO employess (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
VALUES (133, 'Louis', ' Popp', 'LPOPP', '515.124.4567', SYSDATE, 'AC_ACCOUNT', 6900, NULL, 205, 100);

INSERT INTO employees
VALUES (144, 'Den', 'Raphealy', 'DRAPHEAL', '515.127.4561', TO_DATE('FEB 3, 1999', 'MON DD, YYYY'), 'AC_ACCOUNT', 11000, NULL, 100, 30);

INSERT INTO department612(department_id, department_name, location_id)
VALUES (&department_id, '&department_name', &location_id);

UPDATE employees
SET department_id = 70
WHERE employee_id = 133;

UPDATE employees
SET        job_id       = (SELECT job_id
                                  FROM   employees
                                  WHERE employee_id = 205),
              salary       = (SELECT salary
                                  FROM employees
                                  WHERE employee_id = 205)
WHERE employee_id = 144;

DELETE FROM departments
WHERE department_name = ‘Finance’;

LATIHAN

CREATE TABLE DEPARTMENT612
AS SELECT * FROM DEPARMENTS

1. INSERT DATA BARU KE DALAM TABLE DEPARTMENT + NIM(TIGA DIGIT TERAKHIR)

DEPARTMENT_ID – DEPARTMENT_NAME – MANAGER_ID – LOCATION_ID

1135 MARKETING 100 110

1175 ACCOUNTING 80 90

188 ADMINISTRASI 90 70

2. TAMPILKAN DATA DEPARTMENTS YANG DEPARTMENT_IDNYA ADALAH 1135, 1175, 1188

CREATE TABLE department612
AS SELECT * FROM DEPARTMENTS

INSERT INTO department612(department_id, department_name, manager_id, location_id)
VALUES (&department_id, '&department_name', &manager_id, &location_id);

SELECT department_id AS "ID Departmen ", department_name AS "Nama Department", manager_id AS "ID Manager", location_id AS "ID Lokasi"
FROM department612
WHERE department_id = 1135
OR department_id = 1175
OR department_id = 1188;