 |
Create Table
1. Buatlah 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_BarangKpr-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. Tampilkanlah 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
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 :
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;
Vefify 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
Sseleksi 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
Jawab : select e.last_name from employees e where (e.employee_id) in (select m.manager_id from employees m); output
Jawab : 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;
Interection
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;
Viewer
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