SOAL DAN JAWABAN ORACLE
� Create Table
1. buat lah barang dengan field kode_barang char(6),nama barang varchar2(25),satuan_barang varchar2(20) dan stok_barang number(4) primary key adalah kode barang
Jawab
SQL> create table barang(
kode_barang char(6),
nama_barang varchar2(25),
satuan_barang varchar2(20),
stock_barang number(4),
constraint pk_barang primary key(kode_barang)
);
Output
� Insert Table
2. Isikan lah sebuah recordke dalam tabel barang seperti tampilan berikut :
KODE_BARANG NAMA_BARANG SATUAN_BARANG STOK_BARANG
KPR-1 KULKAS BUAH 20
Jawab
SQL> insert into barang values('KPR-01','KULKAS','BUAH','20');
Output
3. Tambahkan sebuah record lagi sehingga hasil sebagai berikut
KODE_BARANG NAMA_BARANG SATUAN_BARANG STOK_BARANG
KPR-01 KULKAS BUAH 20
KPR-02 TELEVISI BUAH 15
Jawab
SQL> Insert into barang values(�KPR-02�,�TELEVISI�,�BUAH�,�15�);
Output
� Menampilkan Isi Tabel
4. Tamplikanlah seluruh field yang berada didalam tabel barang
Jawab
SQL> select * from barang;
Output
� Fungsi Update
5. Ubah lah tabel barang pada kolom nama barang dari TELEVISI menjadi MONITOR
Jawab
SQL> update barang
set nama_barang='MONITOR'
where nama_barang='TELEVISI';
output
� .Fungsi Select
6. Tampilkan lah seluruh field yang berada di tabel barang
Jawab
SQL> select * from barang
Output
7. Tampilkan kode barang,nama barang dari tabel barang yang jumalh stok nya 20
Jawab
SQL> SELECT KODE_BARANG,NAMA_BARANG FROM BARANG WHERE STOCK_BARANG=20;
Output
� Fungsi Like
8. Tampilkan nama barang,satuan barang dan stok barang dari tabel barang, dimana kode barang adalah KPR-02
Jawab
SQL> select nama_barang,satuan_barang,stock_barang from barang
Where kode_barang like 'kpr-02';
� Drop
9. Hapuslah pada field MONITOR pada kolom nama_barang pada tabel barang
Jawab
SQL> delete from barang where NAMA_BARANG='MONITOR';
10. Hapus Tabel Barang
Jawab
SQL> drop table barang;
11. Tampilkan last name,salary dari tabel employees yang salary nya lebih besar dari 12000 Jawab
SQL> select last_name,salary from employees where salary>12000;
Output
� Operator aritmatic
12. Tampilkan employee id,last name dan salary untuk karyawan yang mempunyai employee id lebih dari 100 dan salary nya ditambah 3000 dan urut kan berdasarkan salary nya
Jawab
SQL> select employee_id,last_name,salary+3000 from employees where employee_id>100;
Output
13. Tampilkan employee id,last name,salary dan salary di kalikan 10% dengan kolom alias �Gaji Baru�
Jawab
SQL> select employee_id,last_name,salary,salary*0.1 as "Gaji Baru" from employees;
Output
� Date and Time
14. Tampilkan last name dan hire date khusus untuk pegawai yang hire date nya 1993
Jawab
SQL> select last_name,hire_date from employees where hire_date like '%93';
Output
15. Tampilkan last name,job id karyawan yang hire date nya antara 20-09-1988 s/d 06-02-2000
Jawab
SQL> select last_name,job_id from employees where hire_date between '28-09-1988' and '06-02-2000';
Output
16. Hitung lah jumlah karyawan yang Tanggal masuk nya antara 20-09-1988 s/d 06-02-2000
Jawab
SQL> select count(last_name) from employees where hire_date between '20-09-1988' and '06-02-2000';
Output
17. Tampilkan last name,hire date dengan mengubah hire date nya dari angka ke karakter dengan label hire date
Jawab
� TO CHAR
18. Tampilkan last name karyawan dan tanggal masuk nya dengan mengkonversikan kedalam bentuk karakter. Khusus untuk karyawan yang department id nya lebih besar dari 100
Jawab
SQL>
select last_name,to_char(hire_date,'dd month yyyy') as "HIRE DATE" from employees where department_id>100;
� VERIFY COMMAND
19. Tampilkan last name,salary,dan panjang karakter dengan kolom alias �JML KARAKTER� berdasarkan huruf awal yang di input kan oleh user
Jawab
select last_name,salary,length(last_name) as "JML KARAKTER" from employees where last_name like '&_%';
output
20. Tampilkan last name,salary,salary di tambah 20% dengan kolom bonus nama untuk karyawan yang namanya berawan huruf H
Jawab
SQL> select last_name,salary,salary*0.2 as "BONUS NAMA" from employees where last_name like 'H%';
Output
21. Tampilkan last_name,salary untuk karyawan yang jumlah salary nya lebih besar dari data yang di input kan oleh user dan urutkan berdasarkan last_name
Jawab
SQL > select last_name,salary from employees where salary>&salary order by last_name;
Output
22. Tampilkan last_name dengan huruf kecil ,salary dari tabel employees dimana last name nya di awali dengan huruf �A�
Jawab
SQL>select lower(last_name),salary from employees where last_name like 'A%';
Output
23. Tampilkan last name dengan huruf capital dimana last name nya di awali dengan huruf �A�
Jawab
SQL> select upper(last_name),salary from employees where last_name like 'A%';
Output
24. Tampilkan last name dengan awalan huruf capital dan selebihnya huruf kecil khusu untuk karyawan yang last name nya diawali dengan huruf �H�
Jawab
SQL> select initcap(last_name),salary from employees where last_name like 'H%';
Output
� SELEKSI DAN ARITMATIC
25. Tampilkan salary tertinggi dengan kolom alias �GAJI TERTINGGI� dan untuk yang terendah dengan kolom alias �GAJI TERENDAH�
Jawab
select max(salary) as "GAJI TERTINGGI",min(salary) as "GAJI TERENDAH" from employees;
output
26. Tampilkan rata �rata gaji dari semua karyawan dengan kolom alias �RATA � RATA GAJI�
Jawab
SQL> select avg(salary) as "RATA - RATA GAJI " from employees;
Output
� NVL
27. Tampilkan last_name,salary,salary ditambah 2000 dengan label Tambahan dan dan menambahkan nilai commission pct dengan salary pada kolom nvl (commission_Pct,0) commicion pct nya 0
Jawab
SQL> select last_name,salary,nvl(commission_pct,0), (salary+2000) + (salary+2000+nvl(commission_Pct,0)) Tambahan from employees;
Output
� SORTING
28. Tampilkan rata � rata gaji tertinggi dari semua karyawan
Jawab
SQL> Select max(avg(salary)) as "RATA - RATA GAJI TERTINGGI" from employees group by salary;
Output
29.Tampilkan employee id,last name dan salary untuk karyawan yang salary nya antara 12000 s/d 24000 urutkan berdasarkan nama
Jawab
select employee_id,last_name,salary from employees where salary between '12000' and '24000' order by last_name asc;
output
� Fungsi IN dan NOT IN
30. Tampilkan last name dan salary yang salary nya ter masuk 12000,13000 dan 24000
Jawab
SQL> select last_name,salary from employees where salary in(12000,13000,24000);
Output
31. Tampilkan last name dan salary yang salary nya tidak termasuk dari 12000,13000 dan 24000 dan lakukan pengurutan dari salary terendah
Jawab
SQL> select last_name,salary from employees where salary not in(12000,13000,24000) order by salary asc;
Output
� SUBSITUTION VARIABLE
32. Tampilkan employee id,last name dan salary yang last name nya tidak berawalan huruf �H�
Jawab
SQL> select employee_id,last_name,salary from employees where last_name not like 'H%';
Output
� SUBQUERY
33. Tampilkan last name dari tabel employees yang mana employee id nya sama dengan manager id
select e.last_name from employees e where (e.employee_id) in (select m.manager_id from employees m);
output
34. Tampilkan last name,job id dan gaji terkecil dari semua karyawan yang ada di tabel employees dengan subquery
Jawab
SQL> select last_name,job_id,salary from employees where salary =(select min(salary)from employees);
Output
� COALESCE
35. Tampilkan job id,job title dan gabungkan gaji tertinggi dan terendah dan tambahkan serta dikurangi 1000 dengan label �POTONGAN� pada tabel Jobs
Jawab
SQL> select job_id,job_title,coalesce(max_salary,min_salary-1000) as " POTONGAN" from jobs;
Output
� Fungsi IF,CASE
36. Tampilkan last name dan salary dan buat kolom alias untuk karyawan yang jika panjang karakter last name karyawan = 7 maka salary ditambah 3000,jika panjang karakter last name = 6 maka salary ditambah 4000 dan selainnya nol
Jawab
SQL> select last_name,salary , case when length(last_name)='7' then salary+3000 when length(last_name)='6' then salary+4000 else salary-salary end "Bonus Karakter" from employees;
Output
� DECODE
37. Tampilkan lah job id dan last name dan buat index dengan kolom alias �INDEX� jika Job id Index
�IT_PROG� �A�
�ST_CLERK� �B�
�SA_REP� �C�
�ST_MAN� �D�
�AD_PRES� �E�
Dan kosongkan untuk job id selain dari yang diberi index
Jawab
SQL> select job_id,last_name, decode(job_id, 'IT_PROG','A',
'ST_CLERK','B',
'SA_REP','C',
'ST_MAN','D',
'AD_PRES','E') "INDEX" from employees where employee_id>130;
� CONCATING
38. Gabungkan first name,last name dengan label �Nama Lengkap�,hire date dengan label �Tanggal Masuk� dan salary dengan label gaji dari tabel employees
Jawab
SQL> select concat(first_name,last_name) as " Nama Lengkap",hire_date as "Tanggal Masuk", salary as "Gaji" from employees;
� OPERATOR ANY DAN NOT ANY
39. Tampilkan employee id,last name dan salary yang salary nya termasuk lebih besar dari 9000,6000,4200 dan job id nya adalah �IT PROG� dengan menggunakan operator any
Jawab
SQL> select employee_id,last_name,salary from employees where salary < job_id="'IT_PROG'; � INTERSECT40. Tampilkan employee id,department id dan job id dari tabel employees yang mana datanya sama dengan data yang ada di job historyJawab SQL> select employee_id,department_id,job_id from employees
intersect
select employee_id,department_id,job_id from job_history;
� FUNGSI JOIN
41. Tampilkan location id,postal code dari tabel locations dan department name dari tabel departments yang location id di tabel locations sama dengan location id yang ada di tabel departments
Jawab
SQL> select l.location_id,l.postal_code,d.department_name from locations l right outer join departments d on(l.location_id=d.location_id);
42. Tampilkan last_name,salary ditambah 2000 dengan kolom alias �Bonus� dari tabel employees dan department name dari tabel departments khusus untuk karyawan yang department id nya sama dengan department id yang ada di tabel departments
Jawab
SQL> select e.last_name,e.salary+2000 as "Bonus",d.department_name from employees e full outer join departments d on (e.department_id=d.department_id); select e.last_name,e.salary+2000 as "Bonus",d.department_name from employees e full outer join departments d on (e.department_id=d.department_id);
43. Tampilkan last name,salary dari tabel employees dan cross join dengan department name dari tabel departments
Jawab
SQL> select last_name,salary,department_name from employees cross join departments;
44. Tampilkan employee id,last name dari table employees city dari table location dan department name dari tabel departments
Jawab
SQL> select employee_id,last_name,department_name from employees e join departments d on d.department_id=e.department_id join locations l on d.location_id=l.location_id;
� Operator UNION
45. Tampilkan employee id,job id dan salary dari tabel employees dan gabungkan dengan employee id dan job id yang ada di tabel job history
Jawab
select employee_id,job_id,salary from employees
union
select employee_id,job_id,0 from job_history;
� JOIN FULL
46. Tampilkan employee id,last name dan salary yang salary nya termasuk 9000,6000,4200 dari semua salary karyawan.dengan memakai operator all
Jawab
SQL> select employee_id,last_name,salary from employees where salary <>
47. Gabungkan semua data dari tabel employees dan job history yang yang terdapat data yang sama
Jawab
SQL> select employee_id,job_id,department_id from employees
union all
select employee_id,job_id,department_id from job_history order by employee_id;
� INTERSECTION
48. Tampilkan location id dari tabel departments dan locations untuk nilai id yang sama antara tabel departments dan tabel locations
Jawab
SQL> select location_id from departments
intersect
select location_id from locations;
� COMMIT
49. Tampilkan lah last name dan salary dimana last name nya di awali dengan huruf A, dan simpan lah data tersebut
Jawab
SQL> select last_name,salary from employees where last_name like 'A%';
LAST_NAME SALARY
----------------------------------- ----------
Austin 4800
Atkinson 2800
Ande 6400
Abel 11000
commit;
� PEMBUATAN INDEX
50. Buatlah kolom NIP pada table karyawan sebagai index
Jawab
SQL> create index karyawan_nip_idx on karyawan(nip);
Output
�
� PEMBUATAN USER
51. Buat lah user baru pada table karyawan dengan user = sofyan dan password = sosa
Jawab
SQL> create user sofyan identified by sosa;
� VIEW
52. Buatlah view pada table karyawan dengan nama lihat untuk menampilkan semua data yang ada di table karyawan
Jawab
SQL> create view lihat as select * from karyawan;
� DATA CONTROL LANGUAGE (DCL)
53. Buatlah hak akses untuk user dengan nama sosa yang mana user nya hanya diperboleh kan menghapus table
Jawab
SQL> grant delete on karyawan to sosa;
54. Buatlah hak akses untuk user dengan nama sosa yang mana user tersebut hanya boleh menambah isi table
Jawab
SQL> SQL> grant insert on karyawan to sosa;
53. Hapus hak akses delete dari user sosa
Jawab
Revoke delete on karyawan from sosa;
� CONSTRAINT
54.Buatlah kolom NIP sebagai primary key pada table karyawan
Jawab
SQL> alter table karyawan add constraint pk_nip primary key (nip);
55. Buatlah kolom email pada table pegawai sebagai kolom unique
Jawab
SQL> alter table karyawan add constraint uq_email unique (email);
56. defenisikan agar kolom kode_bag dari table karyawan selalu menyamakan ke kolom kode pada table jabatan
Jawab
SQL> alter table karyawan add constraint fk_kode_bag FOREIGN KEY (kode_bag) REFERENCES jabatan(kode);
KETERANGAN
Scema yang digunakan adalah Scema HR dengan menggunakan beberapa table sebagai berikut :
1. Pada soal 1 s/d 10 disini menggunakan table barang
2. Pada soal 11 s/d 49 disini menggunakan table employees, job, histories,locations dan departments
3. Pada soal 50 s/d 56 disini menggunakan table karyawan dan jabatan
