I.

CONSTRAINT
PERCOBAAN 1.2 CREATE TABLE employees1( employee_id number(6), last_name varchar2(25) not null, salary number(8,2), commision_pct number(2,2), hire_date date constraint emp_hire_date_hn not null);

1.3

create table employees2( employee_id number(6), last_name varchar2(25) not null, email varchar2(25), salary number(8,2), commission_pct number(2,2), hire_date date not null, constraint emp_email_hn unique(email));

1.4

CREATE TABLE departments1( department_id NUMBER(4),

department_nameVARCHAR2(30) CONSTRAINT dept_name_hn NOT NULL, manager_id location_id NUMBER(6), NUMBER(4),

CONSTRAINT dept_id_hn PRIMARY KEY(department_id));

1.5

CREATE TABLE employees1( employee_id NUMBER(6), last_nameVARCHAR2(25) NOT NULL, email VARCHAR(25), salary Number(8,2), commission_pct NUMBER(2,2), hire_date DATE NOT NULL,

department_id NUMBER(4), CONSTRAINT emp_dept_hn FOREIGN KEY (department_id) REFERENCES departments(department_id), CONSTRAINT emp_email_ hnUNIQUE(email));

1.15 SELECT constraint_name, column_name FROM user_cons_columns WHERE table_name = 'employees1';

LATIHAN 1.alter table employees1 add constraint my_emp_id_pk primary key(employee_id);

2.CREATE TABLE departments2( department_id NUMBER(4),

department_name VARCHAR2(30) CONSTRAINT dept_name_hn NOT NULL,

manager_id

NUMBER(6), NUMBER(4),

location_id

CONSTRAINT dept_id_hn PRIMARY KEY(department_id));

3. alter table employees1 add department_id number(4); alter table employees1 add constraint my_dpet_id_hn foreign key(department_id) references departments2(department_id);

4.select * from user_constraints where table_name = 'EMPLOYEES1' or table_name = 'DEPARTMENTS2'

5. select * from user_objects where object_name = 'EMPLOYEES1' or object_name = 'DEPARTMENTS2'

6.alter table employees1 add COMMISSION NUMBER(2); alter table employees1 modify COMMISSION NOT NULL; alter table employees1 add constraint my_com_hn check(COMMISSION > 0);

ii. VIEW

PERCOBAAN 2.3 create view empvu80 as select employee_id, last_name, salary from employees where department_id=80;

create view salvu50 as select employee_id ID_NUMBER, last_name NAME, salary*12 ANN_SALARY from employees where department_id=50; describe salvu50;

2.4 create or replace view empvu80 (id_number, name, sal, department_id) as select employee_id, first_name ||''|| last_name, salary, department_id from employees where department_id=80; describe empvu80;

2.5 create view dept_sum_vu (name, minsal, maxsal, avgsal) as select d.department_name, MIN(e.salary), max (e.salary), AVG (e.salary) from employees e, departments d where e.department_id=d.department_id group BY d.department_name; describe dept_sum_vu;

2.7 create or replace view empvu20 as select * from employees where department_id=20 with check option constraint empvu20; describe empvu20;

2.8 create or replace view empvu10 (employee_number, employee_name, job_title) as select employee_id, last_name, job_id from employees where department_id = 10 with read only; describe empvu10;

2.1.3 select rownum as rank, last_name, salary from (select last_name,salary from employees order by salary desc) where rownum <=3;

LATIHAN 1. create view emp_vu (nomer_pegawai, nama_pegawai, nomer_department) as select employee_id,last_name,department_id from employees; describe emp_vu;

create or replace view emp_vu (nomer_pegawai, pegawai, nomer_department) as select employee_id,last_name,department_id from employees; describe emp_vu;

2. create or replace view emp_vu (EMPNO, PEGAWAI, DEPTNO) as select employee_id, last_name,department_id from employees; select* from emp_vu;

3. select view_name,text from user_views;

4. create or replace view DEPT20 (EMPLOYEE_ID,EMPLOYEE,DEPARTMENT_ID) as select employee_id,last_name,department_id from employees with read only; select* from dept20;

5. desc DEPT20;

6. create or replace view SALARY_VU (PEGAWAI,DEPARTEMEN,GAJI,JOB_TITLE) as select e.employee_id,e.first_name,e.department_id,j.job_title from employees e,jobs j where e.job_id=j.job_id; describe salary_vu;

III. PL/SQL I
PERCOBAAN
3.8 declare employee_id number(6); begin select employee_id into employee_id from employees where last_name = 'Kochhar'; END;

3.13 variable g_salary number; begin select salary into :g_salary from employees where employee_id=178; END; / PRINT g_salary

variable g_monthly_sal number;

define p_annual_sal = 50000 set verify off declare v_sal number(9,2):=&p_annual_sal; begin :g_monthly_sal:=v_sal/12; END; / print g_monthly_sal

3.14 set serveroutput on define p_annual_sal:= 50000 declare v_sal number(9,2):=&p_annual_sal; begin v_sal:=v_sal/12; dbms_output.put_line('The monthly salary is ' || to_char(v_sal)); end; /

LATIHAN
1. a. DECLARE v_id NUMBER(4); BEGIN NULL; END;

( LEGAL EXPRESSION ) b. DECLARE v_x, v_y, v_z VARCHAR2(10); BEGIN

NULL; END;

( ILEGAL EXPRESSION )

c. DECLARE v_birthdate DATE NOT NULL; BEGIN NULL; END;

( ILEGAL EXPRESSION) d. DECLARE V_in_stock BOOLEAN:=1; BEGIN NULL; END;

( ILEGAL EXPRESSION )

2. . ERROR DECLARE v_days_to_go DATE; v_due_date DATE;

begin

v_days_to_go : = v_due_date - SYSDATE; end;

b. VA ID DECLARE v_senderVARCHAR2(20); v_dept_noVARCHAR2(20);

begin v_sender := USER || ':' || TO_CHAR(v_dept_no); end;

c. ERROR DECLARE v_sum NUMBER(7); begin v_sum := $100,000 + $250,000 ; end;

d. VA ID DECLARE v_flag BOOLEAN; begin v_flag := TRUE; end;

e. VA ID DECLARE v_n1 BOOLEAN ; v_n2 NUMBER(10) ; v_n3 NUMBER(10) ; begin v_n1 := v_n2 > (2*v_n3); end;

f. DECLARE v_value NUMBER(2); begin v_value := NULL; end;

3. set serveroutput on declare bilang varchar2(15); begin bilang:= 'i love oracle'; dbms_output.put_line(bilang); end; /

set serveroutput on variable bilang varchar2(15); begin bilang:= 'i love oracle'; end; / PRINT bilang;

4. set serveroutput on declare v_char varchar2(42); v_num number(5); begin v_char:='42 adalah angka'; dbms_output.put_line(v_char); v_num:=substr(v_char,0,2); dbms_output.put_line(v_num); end; /

IV. PENULISAN STRUKTUR KONTROL IF-THEN-ELSE-CASE

PERCOBAAN 4.4 DECLARE sales NUMBER(8,2):=10100; quota NUMBER(8,2):=10000; bonus number(6,2); emp_id number(6):=120; BEGIN if sales > (quota +200) then bonus:=(sales - quota)/4; update employees set salary = salary +bonus whereemployee_id = emp_id; end if; end; /

4.5 DECLARE sales NUMBER(8,2):=12100; quota NUMBER(8,2):=10000; bonus number(6,2); emp_id number(6):=120; BEGIN if sales > (quota +200) then bonus:=(sales - quota)/4; else bonus:=50; end if; update employees set salary = salary +bonus where employee_id = emp_id; end; /

4.6 DECLARE sales NUMBER(8,2):=12100; quota NUMBER(8,2):=10000; bonus number(6,2); emp_id number(6):=120; BEGIN if sales > (quota +200) then bonus:=(sales - quota)/4; else if sales > quota then bonus:=50; else bonus:=0; end if; end if; update employees set salary = salary +bonus where employee_id = emp_id; end; /

4.7 DECLARE sales NUMBER(8,2):=20000; bonus number(6,2); emp_id number(6):=120; BEGIN if sales > 50000 then bonus:=1500; elsif sales > 35000 then bonus:=500; else bonus:=100; end if; update employees set salary = salary +bonus where employee_id = emp_id; end; /

4.8 DECLARE grade char(1); BEGIN grade:='B'; if grade = 'A' then DBMS_OUTPUT.PUT_LINE('Excellent'); ELSIF grade='B' then DBMS_OUTPUT.PUT_LINE('Very Good'); ELSIF grade='C' then DBMS_OUTPUT.PUT_LINE('Good'); ELSIF grade='D' then DBMS_OUTPUT.PUT_LINE('Fair'); ELSIF grade='F' then DBMS_OUTPUT.PUT_LINE('Poor'); ELSE DBMS_OUTPUT.PUT_LINE('No Such Grade'); END IF; END; /

4.11 DECLARE grade char(1); BEGIN grade:='B'; CASE grade WHEN 'A' then WHEN 'B' then WHEN 'C' then WHEN 'D' then WHEN 'F' then

DBMS_OUTPUT.PUT_LINE('Excellent'); DBMS_OUTPUT.PUT_LINE('Very Good'); DBMS_OUTPUT.PUT_LINE('Good'); DBMS_OUTPUT.PUT_LINE('Fair'); DBMS_OUTPUT.PUT_LINE('Poor');

ELSE DBMS_OUTPUT.PUT_LINE('No Such Grade'); END CASE; END;/

LATIHAN 1. declare emp_id number(6):=103; dept_id number(6); begin select d.department_id into dept_id from departments d,employees e where d.department_id=e.department_id and e.employee_id=emp_id; if dept_id=10 then DBMS_OUTPUT.PUT_LINE('Administration'); ELSIF dept_id=20 then DBMS_OUTPUT.PUT_LINE('Marketing'); ELSIF dept_id=30 then DBMS_OUTPUT.PUT_LINE('Purchasing'); ELSIF dept_id=40 then DBMS_OUTPUT.PUT_LINE('Human Resources'); ELSIF dept_id=50 then DBMS_OUTPUT.PUT_LINE('Shipping'); ELSIF dept_id=60 then DBMS_OUTPUT.PUT_LINE('IT'); ELSIF dept_id=70 then DBMS_OUTPUT.PUT_LINE('Public Relations'); ELSIF dept_id=80 then DBMS_OUTPUT.PUT_LINE('80 Sales'); ELSIF dept_id=90 then DBMS_OUTPUT.PUT_LINE('Executive'); ELSE DBMS_OUTPUT.PUT_LINE('Finance'); END IF; END; /

2. declare emp_id number(6):=103; dept_id number(6); begin select d.department_id into dept_id from departments d,employees e where d.department_id=e.department_id and e.employee_id=emp_id; CASE dept_id WHEN 10 then DBMS_OUTPUT.PUT_LINE('Administration'); WHEN 20 then DBMS_OUTPUT.PUT_LINE('Marketing'); WHEN 30 then DBMS_OUTPUT.PUT_LINE('Purchasing'); WHEN 40 then DBMS_OUTPUT.PUT_LINE('Human Resources'); WHEN 50 then DBMS_OUTPUT.PUT_LINE('Shipping'); WHEN 60 then DBMS_OUTPUT.PUT_LINE('IT'); WHEN 70 then DBMS_OUTPUT.PUT_LINE('Public Relations'); WHEN 80 then DBMS_OUTPUT.PUT_LINE('80 Sales'); WHEN 90 then DBMS_OUTPUT.PUT_LINE('Executive'); WHEN 100 then DBMS_OUTPUT.PUT_LINE('Finance'); ELSE DBMS_OUTPUT.PUT_LINE('Tidak Terdaftar'); END CASE; END; /

V. PenulisanStrukturKontrol LOOPING
5.2 declare

credit_rating number :=0; begin loop credit_rating:=credit_rating +1; if credit_rating > 3 then exit; --exit loop immediately end if; end loop; --control resumes here dbms_output.put_line('credit rating: '||to_char(credit_rating)); if credit_rating > 3 then return; end if; dbms_output.put_line('credit rating: '||to_char(credit_rating)); end; /

declare credit_rating number :=0; begin loop credit_rating:=credit_rating +1; exit when credit_rating > 3; end loop; --control resume here dbms_output.put_line('credit rating: '||to_char(credit_rating)); if credit_rating > 3 then return; end if; dbms_output.put_line('credit rating: '||to_char(credit_rating)); end; /

5.3 declare v_country_id locations.country_id%type:='CA'; v_location_id locations.location_id%type; v_city locations.city%type:='Montreal'; v_counter number:=1; begin select max(location_id) into v_location_id FROM locations where country_id = v_country_id; while v_counter <= 3 loop insert into locations(location_id,city,country_id) values((v_location_id+v_counter),v_city,v_country_id); v_counter:=v_counter+1; end loop; end; /

5.4 declare p number:=0; begin for k in 1..500 loop --calculate pi with 500 term p:=p +(((-1)**(k+1))/((2*k)-1)); end loop;

end; /

begin for i in reverse 1..3 loop --assign the values 1,2,3 to i dbms_output.put_line(TO_CHAR(i)); end loop; end; /

5.6 declare

s pls_integer:=0; i pls_integer:=0; j pls_integer; begin <<outer_loop>> loop i:=i+1; j:=0; <<inner_loop>> loop j:=j+1; s:=s+1*j; --sum a bunch of products exit inner_loop when (j>5); exit outer_loop when ((i*j)>15); end loop inner_loop; end loop outer_loop; dbms_output.put_line('The sum of products equals: ' || to_char(s)); end; /

5.7 LATIHAN

1.create table departments_0910683069( DEPARTMENT_ID number, DEPARTMENT_NAME varchar2(30), MANAGER_ID number, LOCATION_ID number);

2. declare id_dept departments.department_id%type:=10; nama_dept departments.department_name%type; id_man departments.manager_id%type; id_lok departments.location_id%type; begin while id_dept <101 loop select department_id,department_name,manager_id,location_id into id_dept,nama_dept,id_man,id_lok from departments where department_id = id_dept; insert into departments_0910683069(department_id,department_name,manager_id,loca tion_id) values(id_dept,nama_dept,id_man,id_lok); id_dept:=id_dept+10; end loop; end;

3. create table EMP_DETP_0910683050( EMPLOYEE_ID NUMBER, FIRST_NAME VARCHAR2(30), DEPARTMENT_NAME VARCHAR2(30));

4. declare id_emp employees.employee_id%type:=100; nama_emp employees.first_name%type; nama_dept departments.department_name%type; begin while id_emp <121 loop select e.employee_id,e.first_name,d.department_name into id_emp,nama_emp,nama_dept

from employees e, departments d where e.department_id = d.department_id and e.employee_id=id_emp; insert into EMP_DETP_0910683050(EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_NAME) values(id_emp,nama_emp,nama_dept); id_emp:=id_emp+5; end loop; end;

VI. CURSOR
6.7 declare emp_id employees.job_id%type; l_name employees.last_name%type; id_job employees.job_id%type; sal employees.salary%type; cursor c1 is select employee_id,last_name,job_id,salary from employees where salary > 2000; begin open c1; loop fetch c1 into emp_id,l_name,id_job,sal; dbms_output.put_line(emp_id||''||l_name||''||id_job||''||sal); exit when c1%notfound; end loop; close c1; end;

LATIHAN

1. declare dept_id departments.department_id%type; d_name departments.department_name%type; city locations.city%type; state locations.state_province%type; country countries.country_name%type; region regions.region_name%type; cursor data is select d.department_id,d.department_name,l.city,l.state_province,c.country_ name,r.region_name from departments d, locations l, countries c, regions r where d.location_id=l.location_id and l.country_id=c.country_id and c.region_id=r.region_id; begin open data; loop fetch data into dept_id,d_name,city,state,country,region; dbms_output.put_line(dept_id||' '||d_name||' '||city||' '||state||' '||country||' '||region); exit when data%notfound; end loop; close data; end;

2. DECLARE

job jobs.job_title%type; jml_emp number(4); CURSOR emp IS SELECT j.job_title, count(e.employee_id) as jml_emp FROM jobs j, employees e WHERE j.job_id=e.job_id group by j.job_title; BEGIN OPEN emp; LOOP FETCH emp into job, jml_emp; DBMS_OUTPUT.PUT_LINE(job ||' '|| jml_emp); EXIT WHEN emp%NOTFOUND; END LOOP; CLOSE emp; END;

3. declare

emp_id employees.employee_id%type; f_name employees.first_name%type; d_name departments.department_name%type; man_id employees.manager_id%type; man_nama employees.last_name%type; cursor emp is select distinct e.employee_id,e.first_name,d.department_name,f.manager_id,e.last_nam e as manager_name from employees e,departments d,employees f where e.employee_id = f.manager_id and e.department_id=d.department_id; begin open emp; loop fetch emp into emp_id,f_name,d_name,man_id,man_nama; dbms_output.put_line(emp_id||' '||f_name||' '||d_name||' '||man_id||' '||man_nama); exit when emp%notfound; end loop; close emp; end;

VII. STORED PROCEDURE

7.6 CREATE OR REPLACE PROCEDURE raise_salary( id IN employees.employee_id%TYPE, percent IN NUMBER) is begin update employees set salary = salary * (1+percent/100) whereemployee_id = id; END raise_salary; /

execute raise_salary(176,10)

7.7 create or replace procedure query_emp( id in employees.employee_id%type, name out employees.last_name%type, salary out employees.salary%type) is begin selectlast_name, salary into name, salary from employees whereemployee_id = id; endquery_emp;

declare emp_nameemployees.last_name%type; emp_salemployees.salary%type;

begin query_emp(171, emp_name, emp_sal); end;

7.8 create or replace procedure format_phone (phone_no IN OUT varchar2) is begin phone_no := '(' || SUBSTR(phone_no,1,3)|| ')' || SUBSTR(phone_no,4,3)|| '-' || SUBSTR(phone_no,7); endformat_phone; /

7.9 declare emp_nameemployees.last_name%type; emp_salemployees.salary%type; begin query_emp(171, emp_name, emp_sal); DBMS_OUTPUT.PUT_LINE('Name: '||emp_name); DBMS_OUTPUT.PUT_LINE('Salary: '||emp_sal); end;

variable name varchar2(25) variablesal number executequery_emp(171, :name, :sal); print name sal

LATIHAN 1. create table emp_0910683069 as select * from employees

2. create or replace procedure backup_emp_0910683050( jobid in jobs.job_id%type ) as begin insert into emp_0910683069 select * from employees where job_id=jobid; end backup_emp_0910683050;

3. create or replace procedure get_emp_0910683069( dept_id in departments.department_id%type ) as id employees.employee_id%type; fname employees.first_name%type; jtitle jobs.job_title%type; cursor curr is select e.employee_id, e.first_name, j.job_title from employees e, jobs j where e.job_id= j.job_id and e.department_id=dept_id; begin open curr; loop fetch curr into id, fname,jtitle; dbms_output.put_line(id||' '||fname||' '||jtitle); exit when curr%notfound; end loop; close curr; end get_emp_0910683069;

VIII. STORED FUNCTION

1. create or replace function jum_emp(id in departments.department_id%type) return number is jumlah_emp number :=0; BEGIN select count (employee_id) into jumlah_emp from hr.employees where department_id = id; return jumlah_emp; end jum_emp

variable jumlah_emp NUMBER executive :jumlah_emp :=jum_emp(10)

declare jumlah_emp number:=0;

begin jumlah_emp:=jum_emp(10); end;

2. select distinct d.department_id, d.department_name, jum_emp(e.department_id) from hr.employees e, hr.departments d;

3. create or replace function salx (sala in number)

return number is begin return (sala*25/100+sala); end salx;

Update hr.employees Set salary=salx(salary) Where employee_id=100

IX. TRIGGER
9.3 create or replace trigger audit_emp_values after delete or insert or update on employees for each row begin insert into audit_emp(user_name,time_stamp,id,old_last_name,new_last_name,old_ title,new_title,old_salary,new_salary)

values(USER,SYSDATE,:OLD.employee_id,:OLD.last_name,:NEW.last_name ,:OLD.job_id,:NEW.job_id,:OLD.salary,:NEW.salary); END; / 9.4 BEFORE INSERT OR UPDATE OF salary ON employees FOR EACH ROW WHEN(NEW.job_id='SA_REP') BEGIN IF INSERTING THEN :NEW.commission_pct:=0; ELSIF :OLD.commission_pct IS NULL THEN :NEW.commission_pct:=0; ELSE :NEW.commission_pct:=:OLD.commission_pct+0.05; END IF; END; /

ScreenShoot:

LATIHAN 1.create table emp_0910683069 as select * from employees

2. create table bkp_emp_0910683069 ( user_name varchar(20)not null, time_stamp date not null, id number(6) primary key not null, old_first_name varchar2(20), new_first_name varchar2(20), old_last_name varchar2(25) not null, new_last_name varchar2(25)not null, old_email varchar2(25) not null,

new_email varchar2(25) not null, old_phone varchar2(20), new_phone varchar2(20), old_hire_date date not null, new_hire_date date not null, old_job_id varchar2(10) not null, new_job_id varchar2(10) not null, old_salary number(8,2), new_salary number(8,2), old_commission number(2,2), new_commission number(2,2), old_manager_id number(6), new_manager_id number(6), old_department_id number(4), new_department_id number(4))

X. OBJEK DATABASE YANG LAIN

1. create sequence DEPT_ID_SEQ INCREMENT BY 10 START WITH 60 MAXVALUE 200 NOCACHE NOCYCLE;

2. SELECT SEQUENCE_NAME,MAX_VALUE,INCREMENT_BY,LAST_NUMBER FROM USER_SEQUENCES

3.

CREATE INDEX EMP_IDX_FRG ON EMPLOYEES(JOB_ID,MANAGER_ID,DEPARTMENT_ID);

4. SELECT ic.index_name,ic.column_name,ix.uniqueness FROM user_indexes ix, user_ind_columns ic WHERE ic.index_name = ix.index_name AND ic.table_name='EMPLOYEES';