Materi Oracle Revisi 21

RELATIONAL DATABASE MANAGEMENT SYSTEM (RDBMS)

ORACLE

PT PLN (PERSERO) UDIKLAT SEMARANG

Oleh: Agus Harya Maulana

PT PLN (PERSERO) P3B 2009

Materi Training RDBMS Oracle PT PLN (Persero) UDIKLAT UDIKLAT SEMARANG___________________________________ SEMARANG__________________________________________________ _______________

Daftar Isi 1. Perintah SQL (Structur (Structured ed Query Language)............ ........................... ............................. ............................ ............................ ........................... ................. .... 6 1.1. Menjalan Menjalankan kan Oracle.............. ............................ ............................ ............................ ............................ ............................. ............................. ....................... ............... ............ ...... 6 1.2. Cara Koneksi............. ........................... ............................. ............................. ............................ ............................ ............................ ............................. ............... ...... ............ ............ ...... 7 1.3. Mengaktif Mengaktifkan kan User HR............ ........................... ............................. ............................ ............................ ............................ ............................. ............... ...... ............ ............ ...... 7 1.4. Perintah Dasar SQL: SELECT ............. ........................... ............................ ............................ ............................ ............................ .......................... ................. ......... .... 7 1.5. Melihat Keseluruh Keseluruhan an Tabel pada User Tertentu .............. ............................. ............................. ............................ ....................... .............. ........... ...... 8 1.6. Melihat User di Database ............ ........................... ............................. ............................ ............................ ............................ ............................. .......................... .............. ... 8 1.6.1. User yang Aktif.............. ............................. ............................. ............................ ............................ ............................ ............................ ........................ ............... .......... ........... ...... 8 1.6.2. User yang Ada di dalam Database ............. ........................... ............................ ............................. ............................. ............................ ................... ........... ...... 8 1.7. Memilih Semua Kolom............. ........................... ............................ ............................ ............................ ............................. ............................. ............................ ................... ..... 9 1.8. Memilih Kolom Tertentu ............. ........................... ............................ ............................ ............................ ............................ ............................. ......................... ................ ...... 9 1.9. Persyara Persyaratan tan Penulis Penulisan an Perintah SQL.............. ............................ ............................ ............................. ........................... .................. ............ ............ ............ ...... 9 1.10. Default untuk Heading Kolom.............. ............................. ............................. ............................ ............................ ............................ ...................... ............. .......... ..... 9 2. Perbandingan Pernyataan Pernyata an SQL dengan Perintah iSQL*Plus iSQL*Plu s.............. ............................ ............................. ............................. .............. . 10 2.1. Overview tentang iSQL*Pl iSQL*Plus us............... ............................. ............................ ............................ ............................ ............................ ............................. .................... ..... 10 2.2. Menampilka Menampilkan n Struktur Tabel............ ........................... ............................. ............................ ............................ ............................ ....................... ............... ............ ........ 11 2.3. Interaksi dengan File Script .............. ............................. ............................. ............................ ............................ ............................ ................... .......... .......... .......... ....... 11 2.4. Menyimpan Script Menggunakan Command Line.......... .................... .................... ..................... .......................................... ............................... 11 2.5. Menggunakan Perintah dari File Script ke iSQL*Plus............. ............................ ............................. ........................... .................. .......... ..... 12 2.6. Menjalan Menjalankan kan Script di Command Line............. ........................... ............................ ............................ ............................. ........................ .............. .......... ....... 12 2.7. Menyimpan Output Perintah ke Dalam Sebuah File.............. ............................ ............................ ............................. ......................... ............ .. 12 3. Ekspresi Aritmatika .............. ............................. ............................. ............................ ............................ ............................ ........................... .................. ........... ............ ............ ........ 13 3.1. Mengguna Menggunakan kan Operator Aritmatika ............ ........................... ............................. ............................ ............................ .................... ............ ............ ............ ........ 13 3.2. Tingkatan Operator .......... .................... .................... ..................... ..................... ..................... ..................... .................... ..................... .................................... ......................... 13 3.3. Mengguna Menggunakan kan Paranth Parantheses eses............. ........................... ............................ ............................ ............................. ............................. ............................ ....................... ........... 13 3.4. Definisi Nilai Null............. ........................... ............................ ............................. ............................. ............................ ............................ ............................ ..................... .......... ... 14 3.5. Nilai Null pada Ekspresi Aritmatika .............. ............................ ............................. ............................. ............................ ............................ ....................... ......... 14 4. Definisi Alias Kolom............... ............................. ............................ ............................ ............................ ............................ ............................. ............................. .............. ...... ........ 14 4.1. Mengguna Menggunakan kan Alias Kolom............ .......................... ............................. ............................. ............................ ............................ ............................ ......................... ........... 14 5. Operator Concatena Concatenation tion.............. ............................ ............................ ............................ ............................ ............................. ............................. ....................... ............... ........ 15 5.1. Mengguna Menggunakan kan Operator Concatenat Concatenation ion............. ........................... ............................. ............................. ............................ ...................... ............. .......... ..... 15 6. Literal Character Strings.............. ............................ ............................ ............................. ............................. ............................ ............................ .......................... ............... ... 15 6.1. Mengguna Menggunakan kan Literal Character Strings .............. ............................ ............................ ............................ ............................ ......................... ................ ....... 15 7. Duplikasi Baris............. ........................... ............................ ............................ ............................. ............................. ............................ ............................ .................... ........... .......... ....... 16 7.1. Menghila Menghilangkan ngkan Duplikasi Baris............. ............................ ............................. ............................ ............................ ............................ ..................... ............ ......... .... 16 8. Membatasi Jumlah Baris Hasil dengan Pilihan ............. ........................... ............................. ............................. ........................... .................. .......... ..... 16 8.1. Mengguna Menggunakan kan Klausa WHERE............. ............................ ............................. ............................ ............................ ............................ ............................ .................. .... 17 9. String Karakter dan Tanggal ............. ........................... ............................. ............................. ............................ ............................ ............................ ..................... .......... ... 17 10. Kondisi Pembandi Pembanding ng.............. ............................. ............................. ............................ ............................ ............................ ............................ ............................. .................... ..... 17 10.1. Mengguna Menggunakan kan Kondisi Pembandi Pembanding ng.............. ............................ ............................ ............................ ............................. ............................. .............. ...... ........ 17 10.2. Kondisi Pembandin Pembanding g Lain............. ........................... ............................ ............................. ............................. ............................ .................... ........... .......... ........... ........ 18 10.2.1. Mengguna Menggunakan kan Kondisi BETWEEN .............. ............................ ............................ ............................ ............................. ........................ .............. .......... ....... 18 10.2.2. Mengguna Menggunakan kan Kondisi IN.............. ............................ ............................. ............................. ............................ ............................ ........................ ............... .......... ..... 18 10.2.3. Mengguna Menggunakan kan Kondisi LIKE............. ........................... ............................. ............................. ............................ ......................... ................. ............ ............ ........ 18 10.2.4. Mengguna Menggunakan kan Kondisi NULL............. ........................... ............................ ............................. ............................. ............................ ..................... ............ ........ ... 19 11. Kondisi Logika .............. ............................. ............................. ............................ ............................ ............................ ............................ ............................. ........................ .............. ..... 20 11.1. Mengguna Menggunakan kan Operator AND............. ............................ ............................. ............................ ............................ ............................ ............................ .................. .... 20 11.2. Mengguna Menggunakan kan Operator OR.............. ............................ ............................. ............................. ............................ ............................ .................... ............ ............ ........ 20 11.3. Mengguna Menggunakan kan Operator NOT.............. ............................. ............................. ............................ ............................ ............................ ......................... ................ ....... 21 12. Aturan Prioritas ............ ........................... ............................. ............................ ............................ ............................ ............................ ............................. ........................ .............. ..... 21 13. Klausa ORDER BY............ ........................... ............................. ............................ ............................ ............................ ............................ ....................... .............. .......... .......... ..... 22 13.1. Mengurutk Mengurutkan an Baris Secara Descendin Descending g............ ........................... ............................. ............................ ............................ .................... ........... .......... ....... 22 13.2. Mengurutkan Baris Berdasarkan Alias Kolom............. ............................ ............................. ............................ ............................ .................... ...... 22

RDBMS Oracle – Revisi 21 – Januari 2009

2 dari 71


Daftar Isi 1. Perintah SQL (Structur (Structured ed Query Language)............ ........................... ............................. ............................ ............................ ........................... ................. .... 6 1.1. Menjalan Menjalankan kan Oracle.............. ............................ ............................ ............................ ............................ ............................. ............................. ....................... ............... ............ ...... 6 1.2. Cara Koneksi............. ........................... ............................. ............................. ............................ ............................ ............................ ............................. ............... ...... ............ ............ ...... 7 1.3. Mengaktif Mengaktifkan kan User HR............ ........................... ............................. ............................ ............................ ............................ ............................. ............... ...... ............ ............ ...... 7 1.4. Perintah Dasar SQL: SELECT ............. ........................... ............................ ............................ ............................ ............................ .......................... ................. ......... .... 7 1.5. Melihat Keseluruh Keseluruhan an Tabel pada User Tertentu .............. ............................. ............................. ............................ ....................... .............. ........... ...... 8 1.6. Melihat User di Database ............ ........................... ............................. ............................ ............................ ............................ ............................. .......................... .............. ... 8 1.6.1. User yang Aktif.............. ............................. ............................. ............................ ............................ ............................ ............................ ........................ ............... .......... ........... ...... 8 1.6.2. User yang Ada di dalam Database ............. ........................... ............................ ............................. ............................. ............................ ................... ........... ...... 8 1.7. Memilih Semua Kolom............. ........................... ............................ ............................ ............................ ............................. ............................. ............................ ................... ..... 9 1.8. Memilih Kolom Tertentu ............. ........................... ............................ ............................ ............................ ............................ ............................. ......................... ................ ...... 9 1.9. Persyara Persyaratan tan Penulis Penulisan an Perintah SQL.............. ............................ ............................ ............................. ........................... .................. ............ ............ ............ ...... 9 1.10. Default untuk Heading Kolom.............. ............................. ............................. ............................ ............................ ............................ ...................... ............. .......... ..... 9 2. Perbandingan Pernyataan Pernyata an SQL dengan Perintah iSQL*Plus iSQL*Plu s.............. ............................ ............................. ............................. .............. . 10 2.1. Overview tentang iSQL*Pl iSQL*Plus us............... ............................. ............................ ............................ ............................ ............................ ............................. .................... ..... 10 2.2. Menampilka Menampilkan n Struktur Tabel............ ........................... ............................. ............................ ............................ ............................ ....................... ............... ............ ........ 11 2.3. Interaksi dengan File Script .............. ............................. ............................. ............................ ............................ ............................ ................... .......... .......... .......... ....... 11 2.4. Menyimpan Script Menggunakan Command Line.......... .................... .................... ..................... .......................................... ............................... 11 2.5. Menggunakan Perintah dari File Script ke iSQL*Plus............. ............................ ............................. ........................... .................. .......... ..... 12 2.6. Menjalan Menjalankan kan Script di Command Line............. ........................... ............................ ............................ ............................. ........................ .............. .......... ....... 12 2.7. Menyimpan Output Perintah ke Dalam Sebuah File.............. ............................ ............................ ............................. ......................... ............ .. 12 3. Ekspresi Aritmatika .............. ............................. ............................. ............................ ............................ ............................ ........................... .................. ........... ............ ............ ........ 13 3.1. Mengguna Menggunakan kan Operator Aritmatika ............ ........................... ............................. ............................ ............................ .................... ............ ............ ............ ........ 13 3.2. Tingkatan Operator .......... .................... .................... ..................... ..................... ..................... ..................... .................... ..................... .................................... ......................... 13 3.3. Mengguna Menggunakan kan Paranth Parantheses eses............. ........................... ............................ ............................ ............................. ............................. ............................ ....................... ........... 13 3.4. Definisi Nilai Null............. ........................... ............................ ............................. ............................. ............................ ............................ ............................ ..................... .......... ... 14 3.5. Nilai Null pada Ekspresi Aritmatika .............. ............................ ............................. ............................. ............................ ............................ ....................... ......... 14 4. Definisi Alias Kolom............... ............................. ............................ ............................ ............................ ............................ ............................. ............................. .............. ...... ........ 14 4.1. Mengguna Menggunakan kan Alias Kolom............ .......................... ............................. ............................. ............................ ............................ ............................ ......................... ........... 14 5. Operator Concatena Concatenation tion.............. ............................ ............................ ............................ ............................ ............................. ............................. ....................... ............... ........ 15 5.1. Mengguna Menggunakan kan Operator Concatenat Concatenation ion............. ........................... ............................. ............................. ............................ ...................... ............. .......... ..... 15 6. Literal Character Strings.............. ............................ ............................ ............................. ............................. ............................ ............................ .......................... ............... ... 15 6.1. Mengguna Menggunakan kan Literal Character Strings .............. ............................ ............................ ............................ ............................ ......................... ................ ....... 15 7. Duplikasi Baris............. ........................... ............................ ............................ ............................. ............................. ............................ ............................ .................... ........... .......... ....... 16 7.1. Menghila Menghilangkan ngkan Duplikasi Baris............. ............................ ............................. ............................ ............................ ............................ ..................... ............ ......... .... 16 8. Membatasi Jumlah Baris Hasil dengan Pilihan ............. ........................... ............................. ............................. ........................... .................. .......... ..... 16 8.1. Mengguna Menggunakan kan Klausa WHERE............. ............................ ............................. ............................ ............................ ............................ ............................ .................. .... 17 9. String Karakter dan Tanggal ............. ........................... ............................. ............................. ............................ ............................ ............................ ..................... .......... ... 17 10. Kondisi Pembandi Pembanding ng.............. ............................. ............................. ............................ ............................ ............................ ............................ ............................. .................... ..... 17 10.1. Mengguna Menggunakan kan Kondisi Pembandi Pembanding ng.............. ............................ ............................ ............................ ............................. ............................. .............. ...... ........ 17 10.2. Kondisi Pembandin Pembanding g Lain............. ........................... ............................ ............................. ............................. ............................ .................... ........... .......... ........... ........ 18 10.2.1. Mengguna Menggunakan kan Kondisi BETWEEN .............. ............................ ............................ ............................ ............................. ........................ .............. .......... ....... 18 10.2.2. Mengguna Menggunakan kan Kondisi IN.............. ............................ ............................. ............................. ............................ ............................ ........................ ............... .......... ..... 18 10.2.3. Mengguna Menggunakan kan Kondisi LIKE............. ........................... ............................. ............................. ............................ ......................... ................. ............ ............ ........ 18 10.2.4. Mengguna Menggunakan kan Kondisi NULL............. ........................... ............................ ............................. ............................. ............................ ..................... ............ ........ ... 19 11. Kondisi Logika .............. ............................. ............................. ............................ ............................ ............................ ............................ ............................. ........................ .............. ..... 20 11.1. Mengguna Menggunakan kan Operator AND............. ............................ ............................. ............................ ............................ ............................ ............................ .................. .... 20 11.2. Mengguna Menggunakan kan Operator OR.............. ............................ ............................. ............................. ............................ ............................ .................... ............ ............ ........ 20 11.3. Mengguna Menggunakan kan Operator NOT.............. ............................. ............................. ............................ ............................ ............................ ......................... ................ ....... 21 12. Aturan Prioritas ............ ........................... ............................. ............................ ............................ ............................ ............................ ............................. ........................ .............. ..... 21 13. Klausa ORDER BY............ ........................... ............................. ............................ ............................ ............................ ............................ ....................... .............. .......... .......... ..... 22 13.1. Mengurutk Mengurutkan an Baris Secara Descendin Descending g............ ........................... ............................. ............................ ............................ .................... ........... .......... ....... 22 13.2. Mengurutkan Baris Berdasarkan Alias Kolom............. ............................ ............................. ............................ ............................ .................... ...... 22


2 dari 71

Materi Training RDBMS Oracle PT PLN (Persero) UDIKLAT UDIKLAT SEMARANG___________________________________ SEMARANG__________________________________________________ _______________ 13.3. Mengurutkan Baris Berdasa Berdasarkan rkan Banyak Kolom............. ........................... ............................ ............................ ......................... ................ ....... 14. Fungsi Karakter ............ ........................... ............................. ............................ ............................ ............................ ............................ ............................. ........................ .............. ..... 14.1. Fungsi Case Manipula Manipulation tion............. ........................... ............................ ............................ ............................ ........................... .................. ........... ............ ............ ........ 14.1.1. Menggunakan Fungsi Case Manipula Manipulation tion............. ........................... ............................ ............................ ............................ ......................... ........... 14.2. Fungsi Characte Character-Manipula r-Manipulation tion............... ............................. ............................ ............................ ............................ ............................. ......................... ............ .. 14.2.1. Mengguna Menggunakan kan Fungsi Character Character-Manipula -Manipulation tion.............. ............................ ............................ ............................. ........................ .............. ..... 15. Fungsi Angka ............. ........................... ............................. ............................. ............................ ............................ ............................ ............................. ....................... ............. ......... .... 15.1. Mengguna Menggunakan kan Fungsi ROUND.............. ............................ ............................ ............................ ............................ ............................. ............................. .............. . 15.2. Mengguna Menggunakan kan Fungsi TRUNC.............. ............................ ............................ ............................ ............................ ....................... .............. .......... .......... .......... ....... 15.3. Mengguna Menggunakan kan Fungsi MOD.............. ............................ ............................. ............................. ............................ ............................ .................... ............ ............ ........ 16. Mengguna Menggunakan kan Tanggal ............. ........................... ............................ ............................. ............................. ............................ ............................ .................... ........... .......... ....... 16.1. Aritmatik Aritmatika a Mengguna Menggunakan kan Tanggal .............. ............................. ............................. ............................ ............................ ......................... ................ ........... ........ 16.2. Menggunakan Operator Aritmatika dengan Tanggal.......... .................... ..................... ..................... .................................. ........................ 17. Fungsi Tanggal .............. ............................ ............................ ............................ ............................. ............................. ............................ ............................ ..................... ............. ........ 17.1. Mengguna Menggunakan kan Fungsi Tangga Tanggall.............. ............................ ............................ ............................. ............................. ............................ ..................... ............ ........ ... 17.2. Menggunakan Fungsi TO_CHAR pada Tanggal.......... .................... .................... ..................... .......................................... ............................... 17.3. Elemen Model Format Tanggal .............. ............................ ............................ ............................ ............................ ............................. ........................ .............. ..... 17.4. Menggunakan Fungsi TO_CHAR pada Angka ............. ............................ ............................. ............................ ............................ .................... ...... 17.5. Menggunakan Fungsi TO_NUMBER dan TO_DATE ............. ............................ ............................. ........................... .................. .......... ..... 18. Fungsi Umum............. ........................... ............................ ............................. ............................. ............................ ............................ ............................ ............................ .................. .... 18.1. Mengguna Menggunakan kan Fungsi NVL............. ........................... ............................ ............................ ............................. ............................. ......................... ................ .......... ....... 18.2. Mengguna Menggunakan kan Fungsi NVL2............. ........................... ............................. ............................. ............................ ............................ ........................ ............... .......... ..... 18.3. Mengguna Menggunakan kan Fungsi NULLIF .............. ............................ ............................ ............................ ............................ ............................. ............................. .............. . 18.4. Mengguna Menggunakan kan Fungsi COALESCE ............ ........................... ............................. ............................ ............................ ............................ ......................... ........... 19. Pernyata Pernyataan an Kondision Kondisional al............. ............................ ............................. ............................ ............................ ............................ ............................ ................... .......... .......... ..... 19.1. Pernyata Pernyataan an CASE............. ........................... ............................. ............................. ............................ ............................ ............................ ....................... ............... ............ ........ 19.1.1. Mengguna Menggunakan kan Pernyata Pernyataan an CASE............. ........................... ............................ ............................ ............................ ....................... .............. .......... .......... ..... 19.2. Fungsi DECODE.............. ............................ ............................ ............................ ............................ ............................. ............................. ......................... ................ .......... ....... 19.2.1. Mengguna Menggunakan kan Fungsi DECODE............. ........................... ............................. ............................. ............................ ......................... ................. ............ ........ 20. Menggabungkan Tabel Mengguna Menggunakan kan Sintaks Oracle............ ........................... ............................. ............................ ...................... ............ .... 20.1. Mengambil Record Mengguna Menggunakan kan Equijoin .............. ............................ ............................. ............................. ........................... .................. .......... ..... 20.2. Menambahkan Kondisi Pencarian Mengguna kan Operator AND............... ............................. ........................... ................. .... 20.3. Mengguna Menggunakan kan Alias Tabel .............. ............................. ............................. ............................ ............................ ............................ ....................... ............... ............ ........ 20.4. Menggabungkan Lebih Dari Dua Tabel............. ........................... ............................ ............................ ............................ ......................... ................ ....... 20.5. Mengambil Record Menggunakan Non Equijoin ............. ........................... ............................ ........................... .................. ........... ............ ........ 21. Sintaks Outer Join............. ........................... ............................ ............................. ............................. ............................ ............................ ............................ ......................... ........... 21.1. Mengguna Menggunakan kan Outer Join.............. ............................ ............................ ............................ ............................ ............................ ....................... .............. .......... .......... ..... 22. Menggabungkan Tabel ke Tabel Itu Sendiri .............. ............................ ............................ ............................ ............................. ......................... ............ .. 23. Menggabungkan Tabel Menggunakan SQL: Sintaks 1999 .......... .................... ..................... ........................................ ............................. 23.1. Menggabungkan Tabel Mengguna Menggunakan kan Natural Join............... ............................. ............................ ......................... ................. ............ ........ 23.2. Menggabungkan Tabel Menggunakan Klausa USING.......... .................... .................... ..................... ................................. ...................... 23.3. Menggabungkan Menggabungka n Tabel Mengguna Menggunakan kan Klausa ON.............. ............................. ............................. ............................ .......................... .............. 23.4. LEFT OUTER JOIN.............. ............................ ............................ ............................ ............................ ............................. ......................... ............... .......... .......... .......... ....... 23.5. RIGHT OUTER JOIN.............. ............................ ............................ ............................ ............................. ............................. ............................ ..................... ............ ........ ... 23.6. FULL OUTER JOIN............. ............................ ............................. ............................ ............................ ............................ ............................. ....................... ............. ......... .... 23.7. Menggabungkan Tabel dengan Kondisi Tambahan .......... .................... .................... ..................... ..................... .......................... ................ 24. Mengguna Menggunakan kan Fungsi AVG dan SUM............... ............................. ............................ ............................ ............................ ................... .......... .......... .......... ....... 25. Menggunakan Fungsi MIN dan MAX............ ........................... ............................. ............................ ............................ ............................ ..................... .......... ... 26. Mengguna Menggunakan kan Fungsi COUNT............... ............................. ............................ ............................ ............................ ............................. ....................... ............. ......... .... 27. Mengguna Menggunakan kan Keyword DISTINCT............. ........................... ............................ ............................ ............................ ............................. ......................... ............ .. 28. Fungsi Grup dan Nilai Null.............. ............................ ............................ ............................ ............................. ............................. ........................... .................. .......... ..... 28.1. Menggunakan Fungsi NVL dengan Fungsi Grup............... ............................. ............................ ............................ .......................... ............... ... 29. Membuat Grup Data: Menggunakan Klausa GROUP BY............. ........................... ............................ ........................ ............... .......... ..... 29.1. Mengguna Menggunakan kan Klausa GROUP BY.............. ............................ ............................ ............................. ............................. ........................... .................. .......... ..... 29.2. Menggunakan Klausa GROUP BY pada Banyak Kolom........... ..................... .................... ....................................... ............................. 29.3. Query Ilegal Menggunakan Fungsi Grup.............. ............................ ............................. ............................. ............................ .......................... ..............


3 dari 71

23 23 23 24 24 24 25 25 25 26 26 26 27 27 27 28 29 29 30 30 32 32 32 33 33 33 33 34 34 34 34 35 35 35 36 36 36 36 37 37 38 38 38 39 39 39 39 40 40 40 41 41 41 41 41 42

Materi Training RDBMS Oracle PT PLN (Persero) UDIKLAT SEMARANG__________________________________________________ 29.4. Menggunakan Klausa HAVING.................................................................................................. 29.5. Gabungan Fungsi Grup............................................................................................................... 30. Menggunakan Subquery ................................................................................................................. 30.1. Subquery Satu Baris.................................................................................................................... 30.1.1. Menggunakan Subquery Satu Baris.......................................................................................... 30.1.2. Menggunakan Fungsi Grup dalam Subquery........................................................................... 30.2. Klausa HAVING dalam Subquery ............................................................................................... 30.3. Subquery Multi-Baris.................................................................................................................. . 30.4. Menggunakan Operator ANY pada Subquery Multi-Baris......................................................... 30.5. Menggunakan Operator ALL pada Subquery Multi-Baris.......................................................... 30.6. Nilai Null pada Subquery ............................................................................................................ 31. Menggunakan Variabel Pengganti &............................................................................................. 31.1. Nilai Karakter dan Waktu Menggunakan Variabel Pengganti ................................................... 31.2. Mendefinisikan Nama Kolom, Ekspresi, dan Teks...................................................................... 31.3. Perintah DEFINE dan UNDEFINE ............................................................................................ 31.4. Menggunakan Variabel Pengganti &&....................................................................................... 31.5. Menggunakan Perintah VERIFY ................................................................................................. 31.6. Menggunakan Perintah COLUMN.............................................................................................. 31.7. Model Format Kolom.................................................................................................................. 31.8. Mengatur Lebar Kolom (Hanya untuk Teks)............................................................................... 31.9. Mengubah Setting Bahasa pada Oracle...................................................................................... 31.10. Menggunakan Perintah BREAK ................................................................................................ 31.11. Menggunakan Perintah TTITLE dan BTITLE ........................................................................... 31.12. Membuat File Script untuk Menghasilkan Laporan.................................................................. 32. Membuat dan Mengatur Tabel....................................................................................................... 32.1. Aturan Penamaan ........................................................................................................................ 32.2. Perintah CREATE TABLE ........................................................................................................... 32.3. Query terhadap Data Dictionary................................................................................................. 32.4. Membuat Tabel Menggunakan Subquery .................................................................................... 32.5. Menyalin Baris dari Tabel Lain.................................................................................................. 32.6. Perintah ALTER TABLE .............................................................................................................. 32.7. Menambah Kolom........................................................................................................................ 32.8. Modifikasi Kolom......................................................................................................................... 32.9. Menghapus Kolom....................................................................................................................... 32.10. Membuat Objek Sequence .......................................................................................................... 32.10.1. Menggunakan Objek Sequence ............................................................................................... 32.11. Option SET UNUSED................................................................................................................ 32.12. Menghapus Tabel....................................................................................................................... 32.13. Mengubah Nama Objek............................................................................................................. 32.14. Truncate Table ........................................................................................................................... 32.15. Menambah Komentar pada Tabel............................................................................................. 33. Manipulasi Data............................................................................................................................. 33.1. Menambah Baris dengan Nilai Null............................................................................................ 33.2. Mengisi Nilai Waktu Tertentu...................................................................................................... 33.3. Mengisi Tabel Menggunakan Script ............................................................................................ 33.4. Update Baris dalam Tabel........................................................................................................... 33.5. Update Dua Kolom Menggunakan Subquery.............................................................................. 33.6. Menghapus Baris pada Tabel...................................................................................................... 33.7. Menghapus Baris Mengacu Pada Tabel Lain............................................................................. 33.8. Integrity Constraint Error Saat Menghapus Baris...................................................................... 33.9. Menggunakan Subquery Pada Perintah INSERT........................................................................ 34. Perintah MERGE............................................................................................................................ 35. Commit Data................................................................................................................................... 36. ROLLBACK dan Status Data Setelah ROLLBACK........................................................................ 36.1. Penggunaan SAVEPOINT dan ROLLBACK ............................................................................... 37. Konsistensi Pembacaan (Read Consistency)..................................................................................


4 dari 71

42 43 43 44 44 44 45 45 46 46 46 47 47 48 48 48 49 49 49 50 50 50 50 51 52 52 52 52 53 53 54 54 55 55 55 56 56 57 57 57 57 58 58 59 59 59 60 60 60 61 61 62 62 63 63 64

Materi Training RDBMS Oracle PT PLN (Persero) UDIKLAT SEMARANG__________________________________________________ 38. Membuat Tablespace...................................................................................................................... 39. Membuat Index............................................................................................................................... 40. Mengontrol Akses Pengguna .......................................................................................................... 40.1. Privilege Sistem........................................................................................................................... 40.2. Membuat User............................................................................................................................. 40.3. Typical User Privileges ............................................................................................................... 40.4. Memberi Grant Pada Privilege Sistem........................................................................................ 41. Role................................................................................................................................................. 41.1. Membuat dan Memberikan Privilege pada Sebuah Role............................................................ 42. Mengubah Password ...................................................................................................................... 43. Grant Object Privilege ................................................................................................................... 44. Menggunakan Keyword WITH GRANT OPTION dan PUBLIC.................................................... 45. Mencabut (Revoke) Object Privilege.............................................................................................. 46. Tabel Eksternal............................................................................................................................... 46.1. Membuat Directory ...................................................................................................................... 46.2. Contoh File Teks Dipisahkan oleh Spasi..................................................................................... 46.2.1. Membuat Tabel Eksternal .................................................................................................. ....... 46.3. Contoh File Teks Dipisahkan oleh Koma.................................................................................... 46.3.1. Membuat Tabel Eksternal .................................................................................................. .......


5 dari 71

65 65 66 66 66 66 66 67 67 67 68 68 68 69 69 69 69 70 70

Materi Training RDBMS Oracle PT PLN (Persero) UDIKLAT SEMARANG__________________________________________________

Relational Database Management System 1.

Perintah SQL (Structured Query Language)

Perintah SELECT INSERT UPDATE DELETE MERGE CREATE ALTER DROP RENAME TRUNCATE COMMIT ROLLBACK SAVEPOINT GRANT REVOKE

Istilah Data retrieval Data manipulation (DML)

language

Data definition language (DDL)

Transaction control Data control language (DCL)

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 1.1. Menjalankan Oracle Apabila kita ingin menjalankan Oracle pada komputer yang berbasiskan Windows, kita dapat melakukan salah satu langkah berikut: a. Klik Start, Program, Oracle, cari menu Sqlplus Application b. Klik Start, Run, ketikkan cmd di menu Open (running under DOS), kemudian jalankan perintah berikut: sqlplus /nolog conn / as sysdba atau conn / c. Buka Internet Explorer, kemudian masukkan address: http://localhost /isqlplus atau http:///isqlplus Apabila kita akan menjalankan Oracle pada komputer yang berbasiskan UNIX atau Linux, maka kita dapat melakukan langkah berikut: a. Buka terminal window b. Masukkan perintah: sqlplus /@ Catatan : __________________________________________________________________ __________________________________________________________________


6 dari 71


__________________________________________________________________ 1.2. Cara Koneksi Untuk melakukan koneksi ke Oracle, kita dapat melakukan langkah berikut: a. CONN / Misal: CONN SCOTT/TIGER b. CONN , kemudian masukkan password Misal: CONN SCOTT, kemudian masukkan password: TIGER 1.3. Mengaktifkan User HR Salah satu user yang telah disediakan pada saat menginstall Oracle adalah user HR. Pada kondisi awal (default) user ini dalam kondisi di-lock. Kita dapat mengaktifkan (unlock) user ini dengan langkah berikut: -

Connect sebagai DBA o

CONN / AS SYSDBA;

Unlock user HR dan berikan password ALTER USER HR ACCOUNT UNLOCK; o ALTER USER HR IDENTIFIED BY HR; o CONN HR/HR; o

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 1.4. Perintah Dasar SQL: SELECT SELECT *|{[DISTINCT] column|expression [alias],…} FROM table; - SELECT mengidentifikasikan kolom - FROM mengidentifikasikan table Dengan menggunakan perintah SELECT dapat dilakukan hal-hal sebagai berikut : Projection : menampilkan kolom tertentu pada tabel yang bersangkutan. • • •

Selection : menampilkan baris tertentu pada tabel yang bersangkutan. Joining

: menampilkan kolom atau baris tertentu pada dua atau lebih tabel yang saling berhubungan. Projection

Selection


7 dari 71


Tabel-1

Tabel-2

Joining

Dalam penulisan SQL dikenal istilah-istilah yang umum digunakan, antara lain :  Keyword : Individual SQL statement, umumnya dituliskan dengan huruf kapital. Contohnya : SELECT dan FROM : Part of SQL Statement.  Clause Contohnya : SELECT last_name,department_id  Statement : Kombinasi dua atau lebih dari clause. Contohnya : SELECT last_name,department_id FROM departments;

1.5. Melihat Keseluruhan Tabel pada User Tertentu SELECT * FROM TAB; 1.6. Melihat User di Database 1.6.1. User yang Aktif SELECT USER FROM DUAL; SHOW USER;

atau

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 1.6.2. User yang Ada di dalam Database CONN / AS SYSDBA; atau CON SYSTEM/; SELECT USERNAME FROM DBA_USERS;

Catatan : __________________________________________________________________


8 dari 71


__________________________________________________________________ __________________________________________________________________ 1.7. Memilih Semua Kolom SELECT * FROM departments; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 1.8. Memilih Kolom Tertentu SELECT FROM

department_id, location_id departments;

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 1.9. Persyaratan Penulisan Perintah SQL 1.10.

-

Perintah SQL adalah tidak case sensitive Perintah SQL dapat berupa satu atau lebih baris Keyword tidak dapat dapat disingkat atau dipisah menjadi beberapa baris Klausa ditempatkan pada baris terpisah Indent digunakan untuk meningkatkan readability Default untuk Heading Kolom i SQL*Plus: o Default heading justification : Center : Uppercase o Default heading display SQL*Plus: o Heading kolom karakter dan tanggal o Heading kolom nomor o Default heading display

: Left-justified : Right-justified : Uppercase


9 dari 71


2.

Perbandingan Pernyataan SQL dengan Perintah i SQL*Plus SQL

i SQL*Plus

Language Standar ANSI Keyword tidak dapat disingkat

Environment Milik Oracle Keyword dapat disingkat Dijalankan di browser Bersifat central, tidak diimplementasikan pada tiap komputer

2.1. Overview tentang i SQL*Plus Setelah kita log in ke i SQL*Plus, kita dapat: - Describe struktur tabel - Edit perintah SQL - Eksekusi SQL dari i SQL*Plus - Menyimpan perintah SQL menjadi sebuah file - Eksekusi perintah yang tersimpan pada saved file - Load perintah dari sebuah file teks ke edit window i SQL*Plus

Keterangan gambar : 1. Edit window 2. 3. 4. 5.

: Area dimana perintah dan statement i SQL*Plus dituliskan Execute button : Tombol untuk mengeksekusi perintah atau statement yang tercantum di menu edit window Save script button : Tombol untuk menyimpan statement yang dituliskan dalam edit window kedalam sebuah file Clear Screen button: Tombol untuk menghapus statement yang tercantum dalam edit window Cancel button : Tombol untuk membatalkan perintah terakhir


10 dari 71


6. Script locator 7. Browse button 8. Load script

: Mengidentifikasi nama dan lokasi dari file script yang akan dieksekusi : Digunakan untuk mencari file script dengan memanfaatkan dialog box Windows File Open : Tombol untuk mengeksekusi statement yang ada di file script dalam script locator

2.2. Menampilkan Struktur Tabel Dengan perintah DESCRIBE, kita dapat menampilkan struktur dari sebuah tabel DESC[RIBE] tablename DESCRIBE

employees

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 2.3. Interaksi dengan File Script Kita dapat menyimpan perintah dari edit window di i SQL*Plus menjadi sebuah file script text sebagai berikut: - Ketikkan perintah SQL pada edit window i SQL*Plus SELECT last_name, hire_date, salary FROM employees; -

Klik tombol Save Script. Kemudian akan muncul kotak dialog Windows File Save. Isikan nama file dan tentukan tujuannya.

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 2.4. Menyimpan Script Menggunakan Command Line Selain menggunakan i SQL*Plus, kita dapat pula menggunakan command line untuk menyimpan perintah atau script SQL di Orace. Caranya adalah sebagai berikut: a. SPOOL ; b. ; c. SPOOL OFF; d. Cek di lokasi file SPOOL C:\Training\Hari1.sql; SELECT * FROM departments;


11 dari 71


SPOOL OFF; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 2.5. Menggunakan Perintah dari File Script ke i SQL*Plus Kita dapat menggunakan perintah sebelumnya yang telah disimpan menjadi file ke dalam i SQL*Plus sebagai berikut: - Ketikkan nama script dan lokasinya. Atau kita dapat pula mengklik tombol Browse untuk mencari lokasi dan nama file script. D:\temp\emp_sql.htm

-

Klik tombol Load Script. Isi file akan ditampilkan pada edit window i SQL*Plus Klik tombol Execute untuk menjalankan perintah pada edit window i SQL*Plus

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 2.6. Menjalankan Script di Command Line Kita dapat menjalankan script yang telah disimpan menggunakan perintah di command line, yaitu sebagai berikut: @ Contoh: @C:\Training\Test1 Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 2.7. Menyimpan Output Perintah ke Dalam Sebuah File Kita dapat menyimpan hasil dari sebuah perintah SQL atau i SQL*Plus ke dalam sebuah file: - Ketikkan perintah SQL dan i SQL*Plus pada edit window di i SQL*Plus - Ubah output option menjadi Save

-

Klik tombol Execute untuk menjalankan perintah pada edit window iSQL*Plus. Kemudian akan muncul kotak dialog Windows File Save. Isikan nama file,


12 dari 71


dengan ekstensi default .html. Tipe file dapat diubah. Hasilnya akan dikirimkan pada file tersebut. 3.

Ekspresi Aritmatika Operator + * /

Deskripsi Tambah Kurang Kali Bagi

3.1. Menggunakan Operator Aritmatika SELECT FROM

last_name, salary, salary + 500 employees;

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 3.2. Tingkatan Operator *

/ -

+

-

Perkalian dan pembagian memiliki prioritas lebih tinggi daripada penambahan dan pengurangan Operator dengan tingkat prioritas yang sama dievaluasi dari kiri ke kanan Parantheses (dalam kurung) digunakan untuk membuat prioritas sendiri sesuai keinginan

SELECT FROM

last_name, salary, 12*salary+500 employees;

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 3.3. Menggunakan Parantheses SELECT FROM

last_name, salary, 12*(salary+500) employees;

Catatan : __________________________________________________________________


13 dari 71


__________________________________________________________________ __________________________________________________________________ 3.4. Definisi Nilai Null -

-

Null adalah sebuah nilai yang bersifat unavailable, unassigned, unknown, atau inapplicable Null tidak sama dengan nol (zero) atau blank space

SELECT last_name, job_id, salary, commission_pct FROM employees; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 3.5. Nilai Null pada Ekspresi Aritmatika SELECT last_name, 12*salary*commission_pct FROM employees; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 4. Definisi Alias Kolom Alias kolom : - Mengganti nama heading kolom - Mengikuti nama kolom, dapat berupa keyword optional AS diantara nama kolom dan alias - Membutuhkan double quotation jika berisikan space atau karakter spesial atau case sensitive 4.1. Menggunakan Alias Kolom SELECT last_name AS nama, commission_pct comm FROM employees; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________


14 dari 71


SELECT last_name “Nama”, salary*12 “Gaji Tahunan” FROM employees; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 5. Operator Concatenation Operator concatenation: - Menggabungkan kolom atau string karakter dengan kolom lain - Direpresentasikan dengan dua vertical bar (||) 5.1. Menggunakan Operator Concatenation SELECT last_name || job_id AS “Pegawai” FROM employees; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 6.

Literal Character Strings - Literal merupakan karakter, nomor, atau tanggal yang terdapat pada list SELECT - Nilai literal tanggal dan karakter harus diapit dengan tanda kutip tunggal - Setiap string karakter dihasilkan sekali untuk setiap baris

6.1. Menggunakan Literal Character Strings SELECT last_name ||’adalah seorang ‘||job_id AS “Detail Pegawai” FROM employees; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________


15 dari 71


Contoh lain: SELECT last_name ||’: 1 bulan gaji = ‘|| salary Bulanan FROM employees; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 7. Duplikasi Baris Secara default, tampilan hasil query adalah semua baris, termasuk duplikasi baris SELECT department_id FROM employees; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 7.1. Menghilangkan Duplikasi Baris Untuk menghilangkan duplikasi baris kita dapat menggunakan keyword DISTINCT pada klausa SELECT SELECT DISTINCT department_id FROM employees; Contoh lain: SELECT DISTINCT department_id, job_id FROM employees; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________

8.

Membatasi Jumlah Baris Hasil dengan Pilihan

SELECT *|{[DISTINCT] column / expression [alias],…} FROM table [WHERE condition(s];


16 dari 71


8.1. Menggunakan Klausa WHERE SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 9.

String Karakter dan Tanggal String karakter dan tanggal diapit dengan tanda kutip tunggal Karakter bersifat case sensitive, dan tanggal bersifat format sensitive - Format tanggal default adalah DD-MON-YY -

SELECT last_name, job_id, department_id FROM employees WHERE last_name = ‘Whalen’; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 10. Kondisi Pembanding Operator = > >= < <= <> 10.1.

Arti Sama dengan Lebih dari Lebih dari atau sama dengan Kurang dari Kurang dari atau sama dengan Tidak sama dengan

Menggunakan Kondisi Pembanding

SELECT last_name, salary FROM employees WHERE salary <= 3000; Catatan : __________________________________________________________________


17 dari 71


__________________________________________________________________ __________________________________________________________________ 10.2.

Kondisi Pembanding Lain

Operator BETWEEN … AND … IN(set) LIKE IS NULL

Arti Diantara dua nilai Memenuhi beberapa dari nilai dalam list Memenuhi pola karakter Sama dengan nilai null

10.2.1. Menggunakan Kondisi BETWEEN Kondisi BETWEEN digunakan untuk menampilkan baris berdasarkan range nilai yang telah ditentukan. SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 10.2.2. Menggunakan Kondisi IN Kondisi IN digunakan untuk menguji nilai yang terdapat dalam list SELECT employee_id, last_name, salary, manager_id FROM employees WHERE manager_id IN (100, 101, 201); SELECT employee_id, manager_id, department_id FROM employees WHERE last_name IN ('Hartstein', 'Vargas'); Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 10.2.3. Menggunakan Kondisi LIKE - Kondisi LIKE digunakan untuk mencari string yang valid menggunakan wildcard


18 dari 71


-

Kondisi pencarian dapat berisikan karakter literal atau nomor: o % menunjukkan zero atau banyak karakter o _ menunjukkan satu karakter

SELECT first_name FROM employees WHERE first_name LIKE 'S%'; SELECT last_name, hire_date FROM employees WHERE hire_date LIKE '%95'; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ Kita dapat pula mengkombinasikan pola-pola tersebut di atas sebagai berikut: SELECT last_name FROM employees WHERE last_name LIKE '_o%'; Selain itu kita dapat pula menggunakan identifier ESCAPE untuk mencari special karakter % dan _ yang sebenarnya SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA\_%' ESCAPE '\'; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 10.2.4. NULL

Menggunakan

Kondisi

SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL; SELECT last_name, job_id, commission_pct FROM employees WHERE commission_pct IS NULL; Catatan :


19 dari 71


__________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 11. Kondisi Logika Operator AND OR NOT

11.1.

Arti Menghasilkan TRUE jka semua kondisi komponen benar Menghasilkan TRUE jika salah satu kondisi komponen benar Menghasilkan TRUE jika kondisi bernilai salah

Menggunakan Operator AND

SELECT FROM WHERE AND

employee_id, last_name, job_id, salary employees salary >=10000 job_id LIKE '%MAN%';

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ Berikut adalah tabel kebenaran untuk kondisi AND AND TRUE FALSE TRUE TRUE FALSE FALSE FALSE FALSE NULL NULL FALSE

11.2.

NULL NULL FALSE NULL

Menggunakan Operator OR

SELECT FROM WHERE OR

employee_id, last_name, job_id, salary employees salary >= 10000 job_id LIKE '%MAN%';

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________


20 dari 71


Berikut adalah tabel kebenaran untuk kondisi OR OR TRUE FALSE TRUE TRUE TRUE FALSE TRUE FALSE NULL TRUE NULL

11.3.

NULL TRUE NULL NULL

Menggunakan Operator NOT

SELECT last_name, job_id FROM employees WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP'); Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ Berikut adalah tabel kebenaran untuk kondisi NOT NOT TRUE FALSE FALSE TRUE

NULL NULL

12. Aturan Prioritas Tingkat Evaluasi 1 2 3 4 5 6 7 8 SELECT FROM WHERE OR AND

Operator Operator aritmatika Operator concatenation Kondisi pembanding IS [NOT] NULL, LIKE, [NOT] IN [NOT] BETWEEN Kondisi logika NOT Kondisi logika AND Kondisi logika OR

last_name, job_id, salary employees job_id = 'SA_REP' job_id = 'AD_PRES' salary > 15000;

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________


21 dari 71


Kita dapat menentukan sendiri prioritas yang diinginkan dengan menambahkan tanda dalam kurung seperti berikut: SELECT last_name, job_id, salary FROM employees WHERE (job_id = 'SA_REP' OR job_id = 'AD_PRES') AND salary > 15000; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 13. Klausa ORDER BY Kita dapat mengurutkan baris menggunakan klausa ORDER BY - ASC : order secara ascending - DESC : order secara descending Klausa ORDER BY berada pada urutan terakhir pada pernyataan SELECT SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date;

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 13.1.

Mengurutkan Baris Secara Descending

SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date DESC; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 13.2.

Mengurutkan Baris Berdasarkan Alias Kolom

SELECT employee_id, last_name, salary*12 tahunan FROM employees ORDER BY tahunan;


22 dari 71


Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 13.3.

Mengurutkan Baris Berdasarkan Banyak Kolom

SELECT last_name, department_id, salary FROM employees ORDER BY department_id, salary DESC; Kita dapat mengurutkan baris berdasarkan kolom yang tidak ada pada list SELECT. SELECT last_name, salary FROM employees ORDER BY department_id, salary DESC; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 14. Fungsi Karakter Fungsi case-manipulation LOWER UPPER INITCAP

Fungsi character-manipulation CONCAT SUBSTR LENGTH INSTR LPAD | RPAD TRIM REPLACE

14.1. Fungsi Case Manipulation Fungsi ini bertujuan untuk mengubah case pada s tring karakter, sebagai berikut: Fungsi Hasil LOWER('SQL Course') sql course UPPER('SQL Course') SQL COURSE INITCAP('SQL Course') Sql Course Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________


23 dari 71


14.1.1. Case Manipulation

Menggunakan

Fungsi

SELECT employee_id, last_name, department_id FROM employees WHERE last_name = 'higgins'; No rows selected

SELECT employee_id, last_name, department_id FROM employees WHERE LOWER(last_name) = 'higgins'; Pernyataan yang terakhir menghasilkan row yang memenuhi kriteria di atas. Dengan demikian penggunaan LOWER dapat menghasilkan query yang akurat. Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 14.2.

Fungsi Character-Manipulation

Fungsi CONCAT('Selamat', 'Pagi') SUBSTR('SelamatPagi',1,5) LENGTH('SelamatPagi') INSTR('SelamatPagi', 'P') LPAD(salary,10, '*') RPAD(salary,10, '*') TRIM('S' FROM 'SelamatPagi')

Hasil SelamatPagi Selam 11 8 *****24000 24000***** elamatPagi

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 14.2.1. Character-Manipulation

Menggunakan

Fungsi

SELECT employee_id, CONCAT(first_name, last_name) NAMA, job_id, LENGTH (last_name), INSTR(last_name, 'a') “Mengandung 'a'? “ FROM employees WHERE SUBSTR(job_id, 4) = 'REP'; Catatan :


24 dari 71


__________________________________________________________________ __________________________________________________________________ __________________________________________________________________ SELECT employee_id, CONCAT(first_name, last_name) NAMA, LENGTH (last_name), INSTR(last_name, 'a') “Mengandung 'a'? “ FROM employees WHERE SUBSTR(last_name, -1, 1) = 'n'; SELECT REPLACE('JAKARTA', 'KARTA', 'CKMANIA') FROM dual; SELECT SUBSTR(last_name,3,5) FROM employees; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 15. Fungsi Angka - ROUND: Membulatkan angka sesuai dengan hukum Aritmatika o ROUND(45.926, 2)  45.93 - TRUNC: Membulatkan angka ke bawah o TRUNC(45.926, 2)  45.92 - MOD: Sisa hasil bagi o MOD(1600, 300)  100 15.1.

Menggunakan Fungsi ROUND

SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1) FROM DUAL; DUAL adalah tabel dummy yang dapat kita gunakan untuk melihat hasil sebuah fungsi atau kalkulasi. Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 15.2.

Menggunakan Fungsi TRUNC


25 dari 71


SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-2) FROM DUAL; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 15.3.

Menggunakan Fungsi MOD

SELECT last_name, salary, MOD(salary, 5000) FROM employees WHERE job_id = 'SA_REP'; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 16. Menggunakan Tanggal - Database Oracle menyimpan tanggal dalam bentuk format numerik internal yaitu: abad, tahun, bulan, hari, jam, menit, detik - Format tampilan tanggal default adalah DD-MON-YY SELECT last_name, hire_date FROM employees WHERE last_name like 'G%'; SYSDATE adalah fungsi yang dapat menghasilkan waktu dan tanggal. SELECT SYSDATE FROM DUAL; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 16.1. -

Aritmatika Menggunakan Tanggal Menambah atau mengurangi angka ke atau dari sebuah tanggal, nilai yang dihasilkan dalam bentuk tanggal Mengurangi dua tanggal menghasilkan jumlah hari antara kedua tanggal tersebut Menambah jam pada sebuah tanggal dengan membagi jumlah jam dengan 24


26 dari 71


Tanggal Tanggal Tanggal Tanggal 16.2.

Operasi + angka – angka – tanggal + angka/24

Hasil Tanggal Tanggal Jumlah hari Tanggal

Menggunakan Operator Aritmatika dengan Tanggal

SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS FROM employees WHERE department_id = 90; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ SELECT TO_DATE('20-04-04', 'DD-MM-YY') - TO_DATE('10-04-04', 'DD-MM-YY') FROM DUAL; SELECT SYSDATE + NUMTODSINTERVAL(3, 'HOUR') “3 Jam Lagi” FROM DUAL; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 17. Fungsi Tanggal Fungsi MONTHS_BETWEEN ADD_MONTHS NEXT_DAY LAST_DAY ROUND TRUNC

17.1.

Deskripsi Jumlah bulan diantara dua tanggal Menambah jumlah bulan pada tanggal Tanggal berikutnya pada hari yang ditentukan Tanggal terakhir pada bulan tertentu Round tanggal Truncate tanggal

Menggunakan Fungsi Tanggal

Fungsi MONTHS_BETWEEN ('01-SEP-95', '11-JAN-94') ADD_MONTHS ('11-JAN-94', 6) NEXT_DAY ('01-SEP-95', 'FRIDAY')

Hasil 19.6774194 ‘11-JUL-94’ ’08-SEP-95’


27 dari 71


LAST_DAY ('01-FEB-95')

’28-FEB-95’

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________

Jika diasumsikan SYSDATE = ’25-JUL-95’ maka Fungsi ROUND(SYSDATE, 'MONTH') 01-AUG-95 ROUND(SYSDATE, 'YEAR') 01-JAN-96 TRUNC(SYSDATE, 'MONTH') 01-JUL-95 TRUNC(SYSDATE, 'YEAR') 01-JAN-95

Hasil

SELECT employee_id, hire_date, ROUND(hire_date, 'MONTH'), TRUNC(hire_date, 'MONTH') FROM employees WHERE hire_date LIKE '%97'; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 17.2.

Menggunakan Fungsi TO_CHAR pada Tanggal

TO_CHAR(date, 'format_model') Format model: - Harus diapit dengan tanda kutip tunggal dan case sensitive - Dapat berisikan elemen dengan format tanggal yang valid - Memiliki elemen fm untuk menghilangkan space kosong atau menghilangkan leading zero - Dipisahkan dari tanggal menggunakan koma SELECT employee_id, TO_CHAR(hire_date, 'MM/YY') Bulan FROM employees WHERE last_name = 'Higgins'; SELECT last_name, TO_CHAR(hire_date, 'fmDD Month YYYY' ) AS TANGGAL FROM employees;


28 dari 71


Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________

17.3.

Elemen Model Format Tanggal Elemen

YYYY YEAR MM MONTH MON DY DAY DD

Makna Tahun secara lengkap dalam bentuk angka Tahun Bulan dalam bentuk angka 2 digit Bulan dalam bentuk nama lengkap Bulan dalam bentuk singkatan 3 huruf Hari dalam bentuk singkatan 3 huruf Hari dalam bentuk nama lengkap Tanggal

Format dari elemen waktu menentukan tampilan dari waktu pada Oracle. Sebagai contoh: HH24:MI:SS AM 15:45:32 PM Kita dapat pula menyelipkan karakter diantara elemen waktu dengan menambahkan tanda kutip ganda diantaranya. DD “of” MONTH 17 of AUGUST Tanggal dapat pula ditampilkan dalam bentuk kata sebagai berikut: ddspth Fourteenth Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 17.4.

Menggunakan Fungsi TO_CHAR pada Angka

TO_CHAR(number , 'format_model ' ) Berikut ini adalah beberapa elemen format yang dapat kita gunakan dengan fungsi TO_CHAR, untuk menampilkan angka sebagai karakter 9 Menunjukkan angka


29 dari 71


0 $ L . ,

Memaksakan angka 0 untuk ditampilkan Menempatkan tanda dolar Menggunakan simbl mata uang lokal Menampilkan penanda desimal Menampilkan penanda ribuan

SELECT TO_CHAR(salary, '$99,999.00' ) GAJI FROM employees WHERE last_name = 'Ernst';

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 17.5. Menggunakan Fungsi TO_NUMBER dan TO_DATE Untuk mengubah karakter menjadi format angka kita dapat menggunakan fungsi TO_NUMBER: TO_NUMBER(char[ , ‘format_model’]) Untuk mengubah karakter menjadi format tanggal kita dapat menggunakan fungsi TO_DATE: TO_DATE(char[ , ‘format_model’]) Fungsi-fungsi di atas memiliki modifier fx. Fungsi modifier ini dapat dilihat dengan menggunakan contoh berikut ini SELECT last_name, hire_date FROM employees WHERE hire_date = TO_DATE('May 24, 1999', 'fxMonth DD, YYYY'); SELECT last_name, hire_date FROM employees WHERE hire_date = TO_DATE('May24, 1999', 'fxMonth DD, YYYY'); SELECT last_name, hire_date FROM employees WHERE hire_date = TO_DATE('May 24, 1999', 'fxMonthDD, YYYY'); Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 18. Fungsi Umum Beberapa fungsi umum antara lain: - NVL (pernyataan1, pernyataan2)


30 dari 71


-

-

NVL2 (pernyataan1, pernyataan2, pernyataan3) NULLIF (pernyataan1, pernyataan2) COALESCE (pernyataan1, pernyataan2, …, pernyataann)

Deskripsi dari fungsi-fungsi di atas dapat dijelaskan sebagai berikut: Fungsi NVL NVL2

Deskripsi Mengubah nilai null menjadi nilai aktual Jika pernyataan1 tidak null, maka hasilnya adalah pernyataan2. Jika pernyataan1 null, maka hasilnya adalah pernyataan3.


31 dari 71


NULLIF

COALESCE

18.1.

Membandingkan dua pernyataan dan menghasilkan null jika kedua pernyataan sama, atau menghasilkan pernyataan pertama jika kedua pernyataan tidak sama Menghasilkan pernyataan tidak null yang pertama dalam list

Menggunakan Fungsi NVL

SELECT last_name, salary, NVL(commission_pct, 0), (salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL FROM employees; SELECT last_name, salary, commission_pct, (salary*12) + (salary*12*commission_pct) AN_SAL FROM employees; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 18.2.

Menggunakan Fungsi NVL2

SELECT last_name, salary, commission_pct, NVL2(commission_pct, 'SAL+COMM', 'SAL' ) income FROM employees WHERE department_id IN (50, 80); Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 18.3.

Menggunakan Fungsi NULLIF

SELECT first_name, LENGTH(first_name) “expr1”, last_name, LENGTH(last_name) “expr2”, NULLIF(LENGTH(first_name), LENGTH(last_name)) result FROM employees;


32 dari 71


Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 18.4.

Menggunakan Fungsi COALESCE

SELECT last_name, commission_pct, salary, COALESCE(commission_pct, salary, 10) comm FROM employees ORDER BY commission_pct; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 19. Pernyataan Kondisional Perintah SQL mengenal kondisi logika IF-THEN-ELSE dengan cara tertentu. Ada dua metode yang dapat digunakan, yaitu: - CASE - DECODE 19.1.

Pernyataan CASE

CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END 19.1.1. Pernyataan CASE

Menggunakan

SELECT last_name, job_id, salary, CASE job_id WHEN 'IT_PROG' THEN 1.10*salary WHEN 'ST_CLERK' THEN 1.15*salary WHEN 'SA_REP' THEN 1.20*salary ELSE salary END “REVISED_SALARY” FROM employees; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________


33 dari 71


19.2.

Fungsi DECODE

DECODE(col/expression, search1, result1 [, search2, result2, …,] [, default])

19.2.1. DECODE

Menggunakan

Fungsi

SELECT last_name, job_id, salary, DECODE(job_id, 'IT_PROG', 1.10*salary, 'ST_CLERK', 1.15*salary, 'SA_REP', 1.20*salary, salary) REVISED_SALARY FROM employees; SELECT last_name, trunc(salary/2000,0), salary, DECODE (TRUNC(salary/2000, 0), 0, 0.00, 1, 0.09, 2, 0.20, 3, 0.30, 4, 0.40, 5, 0.42, 6, 0.44, 0.45) TAX_RATE FROM employees WHERE department_id = 80; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 20. Menggabungkan Tabel Menggunakan Sintaks Oracle SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2;

20.1.

Mengambil Record Menggunakan Equijoin

SELECT employees.employee_id, employees.last_name, employees.department_id, departments.department_id, departments.department_name FROM employees, departments


34 dari 71


WHERE employees.department_id = departments.department_id; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________

20.2.

Menambahkan Kondisi Pencarian Menggunakan Operator AND

SELECT last_name, employees.department_id, department_name FROM employees, departments WHERE employees.department_id = departments.department_id AND last_name = ‘Matos’; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 20.3.

Menggunakan Alias Tabel

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e, departments d WHERE e.department_id = d.department_id; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 20.4.

Menggabungkan Lebih Dari Dua Tabel

SELECT FROM WHERE AND

e.last_name, d.department_name, l.city employees e, departments d, locations l e.department_id = d.department_id d.location_id = l.location_id;

Catatan : __________________________________________________________________ __________________________________________________________________


35 dari 71


__________________________________________________________________ 20.5.

Mengambil Record Menggunakan Non Equijoin

SELECT e.last_name, e.salary, j.min_salary, j.max_salary FROM employees e, jobs j WHERE e.salary BETWEEN j.min_salary AND j.max_salary; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 21. Sintaks Outer Join Kita dapat menggunakan outer join untuk melihat pula baris yang tidak sesuai dengan kondisi join. Operator untuk outer join adalah tanda (+). SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column(+) = table2.column; SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column = table2.column(+); 21.1.

Menggunakan Outer Join

SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d WHERE e.department_id(+) = d.department_id; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 22. Menggabungkan Tabel ke Tabel Itu Sendiri SELECT worker.last_name || ' bekerja untuk ' || manager.last_name FROM employees worker, employees manager WHERE worker.manager_id = manager.employee_id; Catatan : __________________________________________________________________


36 dari 71


__________________________________________________________________ __________________________________________________________________ 23. Menggabungkan Tabel Menggunakan SQL: Sintaks 1999 Menggabungkan Tabel Menggunakan Cross Join Klausa CROSS JOIN menghasilkan cross-product dari dua tabel. Hal ini sama dengan Cartesian product antara dua tabel. SELECT last_name, department_name FROM employees CROSS JOIN departments; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 23.1.

Menggabungkan Tabel Menggunakan Natural Join

SELECT department_id, department_name, location_id, city FROM departments NATURAL JOIN locations; Natural join dapat pula dituliskan sebagaimana equijoin berikut ini: SELECT department_id, department_name, departments.location_id, city FROM departments, locations WHERE departments.location_id = locations.location_id; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ Natural join dapat pula ditambah dengan klausa WHERE sebagai berikut: SELECT department_id, department_name, location_id, city FROM departments NATURAL JOIN locations WHERE department_id IN (20, 50); Catatan : __________________________________________________________________ __________________________________________________________________


37 dari 71


__________________________________________________________________ 23.2.

Menggabungkan Tabel Menggunakan Klausa USING

SELECT e.employee_id, e.last_name, d.location_id FROM employees e JOIN departments d USING (department_id); Penggabungan tabel menggunakan klausa USING seperti di atas adalah sama dengan equijoin berikut ini: SELECT e.employee_id, e.last_name, d.location_id FROM employees e, departments d WHERE e.department_id = d.department_id; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 23.3.

Menggabungkan Tabel Menggunakan Klausa ON

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e JOIN departments d ON (e.department_id = d.department_id); Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 23.4.

LEFT OUTER JOIN

SELECT e.last_name, e.department_id, d.department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id); Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________


38 dari 71


23.5.

RIGHT OUTER JOIN

SELECT e.last_name, e.department_id, d.department_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id); Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 23.6.

FULL OUTER JOIN

SELECT e.last_name, e.department_id, d.department_name FROM employees e FULL OUTER JOIN departments d ON (e.department_id = d.department_id); Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 23.7.

Menggabungkan Tabel dengan Kondisi Tambahan

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e JOIN departments d ON (e.department_id = d.department_id) AND e.manager_id = 149; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 24. Menggunakan Fungsi AVG dan SUM SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary) FROM employees WHERE job_id LIKE '%REP%';


39 dari 71


Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 25. Menggunakan Fungsi MIN dan MAX SELECT MIN(hire_date), MAX(hire_date) FROM employees; SELECT MIN(last_name), MAX(last_name) FROM employees; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 26. Menggunakan Fungsi COUNT SELECT COUNT(*) FROM employees WHERE department_id = 50; SELECT COUNT(commission_pct) FROM employees WHERE department_id = 80; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 27. Menggunakan Keyword DISTINCT SELECT COUNT(DISTINCT department_id) FROM employees; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________


40 dari 71


28. Fungsi Grup dan Nilai Null SELECT AVG(commission_pct) FROM employees; 28.1.

Menggunakan Fungsi NVL dengan Fungsi Grup

SELECT AVG(NVL(commission_pct, 0)) FROM employees; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 29. Membuat Grup Data: Menggunakan Klausa GROUP BY SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column]; 29.1.

Menggunakan Klausa GROUP BY

SELECT department_id, AVG(salary) FROM employees GROUP BY department_id; SELECT AVG(salary) FROM employees GROUP BY department_id; SELECT department_id, AVG(salary), SUM(salary), COUNT(salary) FROM employees GROUP BY department_id; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 29.2.

Menggunakan Klausa GROUP BY pada Banyak Kolom

SELECT department_id dept_id, job_id, SUM(salary) FROM employees


41 dari 71


GROUP BY department_id, job_id;

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 29.3.

Query Ilegal Menggunakan Fungsi Grup

SELECT department_id, COUNT(last_name) FROM employees; SELECT department_id, count(last_name) FROM employees GROUP BY department_id; Pada saat menggunakan fungsi grup, kita tidak boleh menggunakan klausa WHERE pada fungsi grup tersebut. Untuk maksud yang sama kita dapat menggunakan klausa HAVING. SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) > 8000 GROUP BY department_id; Untuk itu kita harus menggunakan klausa HAVING sebagaimana ditunjukkan pada contoh berikut: SELECT department_id, AVG(salary) FROM employees HAVING AVG(salary) > 8000 GROUP BY department_id; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 29.4.

Menggunakan Klausa HAVING

SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];


42 dari 71


SELECT department_id, MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary) > 10000; SELECT job_id, SUM(salary) PAYROLL FROM employees WHERE job_id NOT LIKE '%REP%' GROUP BY job_id HAVING SUM(salary) > 13000 ORDER BY SUM(salary); Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 29.5.

Gabungan Fungsi Grup

SELECT MAX(AVG(salary)) FROM employees GROUP BY department_id; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 30. Menggunakan Subquery SELECT last_name FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name = ‘Abel’); Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________


43 dari 71


30.1. Subquery Satu Baris Subquery jenis ini hanya menghasilkan satu baris. menggunakan operator pembanding sebagai berikut: Operator = > >= < <= <>

Untuk

itu

kita

dapat

Arti Sama dengan Lebih dari Lebih dari atau sama dengan Kurang dari Kurang dari atau sama dengan Tidak sama dengan

SELECT last_name, job_id FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141); Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 30.1.1. Satu Baris

Menggunakan

Subquery

SELECT last_name, job_id, salary FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141) AND salary > (SELECT salary FROM employees WHERE employee_id = 143); Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________

30.1.2. Grup dalam Subquery

Menggunakan


Fungsi

44 dari 71


SELECT last_name, job_id, salary FROM employees WHERE salary = (SELECT MIN(salary) FROM employees); Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 30.2.

Klausa HAVING dalam Subquery

Oracle server akan mengeksekusi subquery terlebih dahulu. Contoh berikut dapat menggambarkan situasi tersebut: SELECT department_id, MIN(salary) FROM employees GROUP BY department_id HAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 50); SELECT job_id, AVG(salary) FROM employees GROUP BY job_id HAVING AVG(salary) = (SELECT MIN(AVG(salary)) FROM employees GROUP BY job_id); Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 30.3.

Subquery Multi-Baris

Subquery jenis ini menghasilkan lebih dari satu baris. Untuk itu kita menggunakan operator pembanding multi-baris. Operator IN ANY ALL

Arti Sama dengan salah satu anggota dalam list Membandingkan nilai dengan tiap nilai hasil dari subquery Membandingkan nilai dengan semua nilai hasil dari subquery

SELECT last_name, salary, department_id


45 dari 71


FROM employees WHERE salary IN (select MIN(salary) FROM employees GROUP BY department_id);

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________

30.4.

Menggunakan Operator ANY pada Subquery Multi-Baris

SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary < ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG'; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 30.5.

Menggunakan Operator ALL pada Subquery Multi-Baris

SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary < ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG'; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 30.6.

Nilai Null pada Subquery

SELECT emp.last_name


46 dari 71


FROM employees emp WHERE emp.employee_id NOT IN (SELECT mgr.manager_id FROM employees mgr); Untuk menghindari kemungkinan dihasilkannya nilai null pada subquery, kita dapat menambahkan klausa WHERE sebagai berikut: SELECT last_name FROM employees WHERE employee_id NOT IN (select manager_id FROM employees WHERE manager_id IS NOT NULL); Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 31. Menggunakan Variabel Pengganti & Kita dapat menggunakan variabel yang dimulai dengan tanda & (ampersand) untuk mengarahkan pengguna pada suatu nilai tertentu. SELECT employee_id, last_name, salary, department_id FROM employees WHERE employee_id = &employee_num; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________

31.1.

Nilai Karakter dan Waktu Menggunakan Variabel Pengganti

SELECT last_name, department_id, salary*12 FROM employees WHERE job_id = '&job_title'; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________


47 dari 71


31.2.

Mendefinisikan Nama Kolom, Ekspresi, dan Teks

SELECT employee_id, last_name, job_id, &column_name FROM employees WHERE &condition ORDER BY &order_column; Isikan -

dengan: Column_name Condition Order_column

= salary = salary > 15000 = last_name

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 31.3. Perintah DEFINE dan UNDEFINE Menggunakan Perintah DEFINE dan Variabel Pengganti & DEFINE employee_num = 200 SELECT employee_id, last_name, salary, department_id FROM employees WHERE employee_id = &employee_num; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 31.4.

Menggunakan Variabel Pengganti &&

SELECT employee_id, last_name, job_id, &&column_name FROM employees ORDER BY &column_name; Catatan : __________________________________________________________________


48 dari 71


__________________________________________________________________ __________________________________________________________________

31.5 31.5..

Meng Menggu guna naka kan n Perin Perintah tah VERI VERIFY FY

SET VERIFY ON SELECT employee_id, last_name, salary, department_id department_id FROM employees WHERE employee_id = &employee_num; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 31.6 31.6.. Meng Menggu guna naka kan n Perin Perintah tah COLU COLUMN MN - Mem Membuat buat headi eading ng kolom olom COLUMN last_name HEADING 'Employee|Name' COLUMN salary JUSTIFY LEFT FORMAT $99,990.00 COLUMN manager_id FORMAT 999999999 NULL NULL 'Tidak ada manager' -

Menamp Menampilk ilkan an sett setting ing tertent tertentu u untu untuk k kolo kolom m LAST_ LAST_NAM NAME E COLUMN last_name

-

Menghi Menghilang langkan kan settin setting g untu untuk k kolom kolom LAST_NA LAST_NAME ME COLUMN last_name CLEAR

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 31.7 31.7..

Mode Modell For Forma matt Kol Kolom om Elemen 9 0 $ L . ,

Contoh 999999 099999 $9999 L9999 9999.99 9,999

Hasil 1234 001234 $1234 L12 34 1234.00 1,234


49 dari 71


31.8. 31.8. Mengat Mengatur ur Leba Lebar r Kolom Kolom (Hanya (Hanya untu untuk k Teks) Teks) Pada saat menggunakan command line, adakalanya hasil query yang ditampilkan tidak sesuai dengan keinginan. Kita dapat mengatur lebar kolom hasil query tersebut menggunakan perintah berikut: COL FOR COL last_name FOR A25 Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 31.9. 31.9. Mengub Mengubah ah Sett Setting ing Bahasa Bahasa pada pada Oracl Oracle e Oracle mengenal beberapa setting bahasa, diantaranya bahasa Indonesia, Inggris, Belanda, Malaysia, dan lain-lain. Perintah yang dapat digunakan adalah: ALTER SESSION SET NLS_LANGUAGE=''; ALTER SESSION SET NLS_LANGUAGE='DUTCH'; SELECT TO_CHAR(sysdate, 'DAY DD-MM-YYYY') Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 31.10. 31.10. Menggunak Menggunakan an Perintah BREAK BREAK BREAK ON job_id Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 31.11. 31.11. Menggunak Menggunakan an Perintah TTITLE dan BTITLE - Mena Menamp mpil ilka kan n hea heade derr dan dan foot footer er TTI[TLE] [text|OFF|ON] -

Men Mengese gesett hea heade derr lap lapor oran an TTITLE 'Salary|Report'


50 dari 71


-

Men Mengese gesett foo foote terr lap lapor oran an BTITLE 'Confidential'

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 31.12. Membuat File Script untuk untuk Menghasilkan Menghasilkan Laporan -

-

Buat Buat perin erinttah SQL SQL SE SELECT LECT Simp Simpan an per perin inta tah h SELE SELECT CT ke ke dala dalam m file file scri script pt Load Load file file scr scrip iptt ke ke dal dalam am edi edito torr Tambah Tambah perinta perintah h forma formatti tting ng sebe sebelum lum perint perintah ah SELE SELECT CT Hapus Hapus perinta perintah h form formatt atting ing setelah setelah perinta perintah h SELE SELECT CT Simpan file scri scrip pt Load file script ke dalam text window i SQL*Plus, SQL*Plus, dan klik tombol Execute

Contoh: SET FEEDBACK OFF TTITLE 'Employee|Report' BTITLE 'Confidential' BREAK ON job_id COLUMN job_id HEADING 'Job|Category' COLUMN last_name HEADING 'Employee' COLUMN salary HEADING 'Salary' FORMAT $99,999.99 REM ** Insert SELECT statement SELECT job_id, last_name, salary FROM employees WHERE salary < 15000 ORDER BY job_id, last_name / REM clear all formatting commands … SET FEEDBACK ON COLUMN job_id CLEAR COLUMN last_name CLEAR COLUMN salary CLEAR CLEAR BREAK Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________


51 dari 71


32. Membuat dan Mengatur Tabel 32.1. Aturan Penamaan Penamaan tabel dalam Oracle memiliki aturan yang harus diikuti yaitu sebagai berikut: - Harus dimulai dengan huruf - Panjang karakter antara 1-30 karakter - Hanya dapat berisikan A-Z, a-z, 0-9, _ , $, dan # - Tidak boleh duplikasi nama dari objek lain yang dimiliki oleh pengguna yang sama - Tidak boleh berupa reserved word server Oracle 32.2. Perintah CREATE TABLE - Membuat tabel CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13)); -

Konfirmasi pembuatan tabel DESCRIBE dept

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 32.3. Query terhadap Data Dictionary - Melihat nama tabel yang dimiliki oleh pengguna SELECT table_name FROM user_tables;

-

Melihat tipe objek yang dimiliki oleh pengguna SELECT DISTINCT object_type FROM user_objects;

-

Melihat tabel, view, synonym, dan sequences yang dimiliki oleh pengguna SELECT * FROM user_catalog;

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________


52 dari 71


32.4.

Membuat Tabel Menggunakan Subquery

CREATE TABLE dept80 AS SELECT employee_id, last_name, salary*12, hire_date FROM employees WHERE department_id = 80;

CREATE TABLE dept80 AS SELECT employee_id, last_name, salary*12 ANNSAL, hire_date FROM employees WHERE department_id = 80;

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ DESCRIBE dept80 CREATE TABLE sales_reps(id, name, salary, commission_pct) AS SELECT employee_id, last_name, salary, commission_pct FROM employees WHERE 1=2; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 32.5.

Menyalin Baris dari Tabel Lain

INSERT INTO sales_reps(id, name, salary, commission_pct)


53 dari 71


SELECT employee_id, last_name, salary, commission_pct FROM employees WHERE job_id LIKE '%REP%';

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 32.6. Perintah ALTER TABLE Perintah ALTER TABLE digunakan untuk: - Menambah kolom baru - Memodifikasi kolom yang ada - Mendefinisikan nilai default untuk kolom baru - Menghapus kolom ALTER TABLE table ADD (column datatype [DEFAULT expr ] [, column datatype] …); ALTER TABLE table MODIFY (column datatype [DEFAULT expr ] [, column datatype] …); ALTER TABLE table DROP (column); Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 32.7. Menambah Kolom Kita dapat menggunakan klausa ADD untuk menambah kolom ALTER TABLE dept80 ADD (job_id VARCHAR2(9));

Kolom yang baru dibuat akan berada pada kolom terakhir. Catatan : __________________________________________________________________


54 dari 71


__________________________________________________________________ __________________________________________________________________ 32.8. Modifikasi Kolom Kita dapat pula mengubah tipe data pada kolom, ukuran maupun nilai default. ALTER TABLE dept80 MODIFY (last_name VARCHAR2(30));

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 32.9. Menghapus Kolom Kita dapat menggunakan klausa DROP COLUMN untuk menghapus kolom yang tidak lagi dibutuhkan pada sebuah tabel. ALTER TABLE dept80 DROP COLUMN job_id;

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 32.10. Membuat Objek Sequence Adakalanya kita ingin memasukkan suatu nomor pada sebuah kolom secara otomatis dengan pola tertentu. Biasanya nomor ini digunakan sebagai ID pada tabel. Untuk memperoleh hasil tersebut kita dapat menggunakan objek sequence pada Oracle dengan script sebagai berikut: CREATE SEQUENCE MYSQ INCREMENT BY 2 START WITH 10 MAXVALUE 20 NOCACHE NOCYCLE; Catatan : __________________________________________________________________ __________________________________________________________________


55 dari 71


__________________________________________________________________ 32.10.1. Menggunakan Objek Sequence Setelah kita membuat objek sequence, maka kita dapat mempergunakannya pada saat memasukkan data ke tabel. Kita dapat melihat contoh berikut ini:

CREATE TABLE TEST ( KOL1 NUMBER, KOL2 VARCHAR2(25)); INSERT INTO TEST VALUES(MYSQ.NEXTVAL, 'Agus'); INSERT INTO TEST VALUES(MYSQ.NEXTVAL, 'Budi'); INSERT INTO TEST VALUES(MYSQ.NEXTVAL, 'Chintami'); Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 32.11. Option SET UNUSED Kita dapat pula menghapus sebuah kolom melalui tahapan-tahapan untuk menghindari kesalahan yang mungkin dapat terjadi. Tahap pertama adalah untuk menandai kolom mana yang akan dihapus, sedangkan tahap kedua adalah proses penghapusan kolom itu sendiri. ALTER TABLE table SET UNUSED (column); Atau ALTER TABLE table SET UNUSED COLUMN column; ALTER TABLE table DROP UNUSED COLUMNS; Contoh: ALTER TABLE dept80 SET UNUSED (last_name);

ALTER TABLE dept80 DROP UNUSED COLUMNS; Catatan : __________________________________________________________________ __________________________________________________________________


56 dari 71


__________________________________________________________________ 32.12. Menghapus Tabel Apabila kita melakukan penghapusan tabel, maka seluruh data dan struktur tabel tersebut akan terhapus. Disamping itu semua transaksi yang tertunda (pending) akan di-commit. Kita tidak dapat melakukan roll back setelah kita memberikan perintah DROP TABLE. DROP TABLE dept80; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 32.13. Mengubah Nama Objek Nama objek seperti tabel, view, dan sequence dapat diubah menggunakan perintah RENAME RENAME dept TO detail_dept;

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 32.14. Truncate Table Perintah TRUNCATE TABLE bermakna sebagai berikut: - Menghapus semua baris pada tabel - Struktur tabel dimaksud masih tersisa TRUNCATE TABLE detail_dept;

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 32.15. Menambah Komentar pada Tabel COMMENT ON TABLE employees IS ‘Employee Information’;


57 dari 71


Komentar-komentar pada tabel dapat dilihat melalui data dictionary berikut: - ALL_COL_COMMENTS - USER_COL_COMMENTS - ALL_TAB_COMMENTS - USER_TAB_COMMENTS

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 33. Manipulasi Data Menambah Baris Baru CREATE TABLE department_ojt AS SELECT * FROM departments; INSERT INTO department_ojt (department_id, department_name, manager_id, location_id) VALUES (70, 'Public Relations', 100, 1700); Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 33.1. Menambah Baris dengan Nilai Null - Metode implisit INSERT INTO department_ojt (department_id, department_name) VALUES (30, 'Purchasing'); -

Metode eksplisit INSERT INTO department_ojt VALUES (100, 'Finance', NULL, NULL);

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________


58 dari 71


33.2.

Mengisi Nilai Waktu Tertentu

CREATE TABLE employee_ojt AS SELECT * FROM employees;

INSERT INTO employee_ojt VALUES (114, 'Den', 'Raphealy', 'DRAPHEAL', '515.127.4561', TO_DATE('FEB 3, 1999', 'MON DD, YYYY'), 'AC_ACCOUNT', 11000, NULL, 100, 30); Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 33.3.

Mengisi Tabel Menggunakan Script

INSERT INTO department_ojt (department_id, department_name, location_id) VALUES (&department_id, '&department_name', &location); Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 33.4.

Update Baris dalam Tabel

UPDATE employee_ojt SET department_id = 70 WHERE employee_id = 113; UPDATE employee_ojt SET department_id = 110; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________


59 dari 71


33.5.

Update Dua Kolom Menggunakan Subquery

UPDATE employee_ojt SET job_id = (SELECT job_id FROM employees WHERE employee_id = 205), salary = (SELECT salary FROM employees WHERE employee_id = 205) WHERE employee_id = 114; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 33.6. Menghapus Baris pada Tabel Perintah DELETE DELETE [FROM] [WHERE

table condition];

DELETE FROM department_ojt WHERE department_name = 'Finance'; Jika kita tidak menuliskan klausa WHERE, maka seluruh baris pada tabel akan terhapus. DELETE FROM employee_ojt; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 33.7.

Menghapus Baris Mengacu Pada Tabel Lain

DELETE FROM employee_ojt WHERE department_id = (SELECT department_id


60 dari 71


FROM departments WHERE department_name LIKE '%Public%');

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 33.8.

Integrity Constraint Error Saat Menghapus Baris

DELETE FROM department_ojt WHERE department_id = 60; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 33.9.

Menggunakan Subquery Pada Perintah INSERT

INSERT INTO (SELECT employee_id, last_name, email, hire_date, job_id, salary, department_id FROM employee_ojt WHERE department_id = 50) VALUES (99999, 'Taylor', 'DTAYLOR', TO_DATE('07-JUN-99', 'DD-MON-YY'), 'ST_CLERK', 5000, 50); Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________


61 dari 71


34. Perintah MERGE Sintaks Perintah MERGE MERGE INTO table_name table_alias USING (table/view/sub_query ) alias ON ( join condition) WHEN MATCHED THEN UPDATE SET col1 = col_val1, col2 = col2_val WHEN NOT MATCHED THEN INSERT (column_list ) VALUES (column_values);

MERGE INTO copy_emp c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.first_name = e.first_name, c.last_name = e.last_name, c.email = e.email, c.phone_number = e.phone_number, c.hire_date = e.hire_date, c.job_id = e.job_id, c.salary = e.salary, c.commission_pct = e.commission_pct, c.manager_id = e.manager_id, c.department_id = e.department_id WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name, e.last_name, e.email, e.phone_number, e.hire_date, e.job_id, e.salary, e.commission_pct, e.manager_id, e.department_id); Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 35. Commit Data - Membuat perubahan data


62 dari 71


DELETE FROM employee_ojt WHERE employee_id = 99999; 1 row deleted INSERT INTO department_ojt VALUES (290, 'Corporate Tax', NULL, 1700); 1 row inserted -

Commit perubahan data COMMIT; Commit complete

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 36. ROLLBACK dan Status Data Setelah ROLLBACK Kita dapat membatalkan perubahan yang dilakukan dengan menggunakan perintah ROLLBACK: - Perubahan data tidak jadi dilaksanakan - Status data sebelumnya dikembalikan - Lock pada baris yang dilakukan perubahan dilepaskan DELETE FROM employee_ojt; 22 rows deleted. ROLLBACK; Rollback complete Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 36.1. Penggunaan SAVEPOINT dan ROLLBACK Apabila selama melakukan transaksi, kita ingin melalui tahapan yang aman sehingga bisa kembali ke kondisi sebelumnya, kita dapat menggunakan perintah SAVEPOINT. Penggunaan perintah SAVEPOINT dimaksud adalah sebagai berikut: CONN HR/HR; INSERT INTO department_ojt (department_id, department_name) VALUES (86, 'A'); SAVEPOINT A; INSERT INTO department_ojt (department_id, department_name) VALUES (97, 'B'); SAVEPOINT B;


63 dari 71


INSERT INTO department_ojt (department_id, department_name) VALUES (98, 'C'); SAVEPOINT C; INSERT INTO department_ojt (department_id, department_name) VALUES (99, 'D'); SAVEPOINT D; INSERT INTO department_ojt (department_id, department_name) VALUES (101, 'E'); SAVEPOINT E;

Kemudian kita lakukan perintah berikut: SELECT * FROM department_ojt; ROLLBACK TO SAVEPOINT D; SELECT * FROM department_ojt; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 37. Konsistensi Pembacaan (Read Consistency) Read consistency menjamin konsistensi terhadap data yang ada setiap saat. Perubahan yang dilakukan oleh seorang pengguna tidak akan bertentangan dengan perubahan yang dilakukan oeh pengguna lainnya. Read consistency dapat ditunjukkan melalui contoh berikut ini: Sesi 1

Sesi 2

SELECT salary FROM employee_ojt

Waktu

Output

t1

24000

WHERE last_name= 'King';

UPDATE employee_ojt SET salary=salary+10000 WHERE last_name= 'King';

SELECT salary FROM employee_ojt WHERE last_name= 'King';

t2

t3

COMMIT;

SELECT salary FROM employee_ojt

24000

t4

t5

34000

WHERE last_name= 'King';

Catatan :


64 dari 71


__________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 38. Membuat Tablespace Pada saat kita membuat sebuah tabel, secara default tabel tersebut akan masuk ke dalam tablespace system. Jika tabel yang dibuat ternyata dalam jumlah yang banyak, tentu akan menambah beban tablespace system tersebut. Oleh karena itu kita dapat mengarahkan sebuah tabel pada tablespace yang kita inginkan. Sebelumnya kita harus membuat tablespace dimaksud sebagaimana dicontohkan berikut ini: Create tablespace kinerja datafile 'd:\rencana_scadatel\my_kinerja01.dbf ' size 5M;

Sedangkan untuk mengarahkan sebuah tabel pada tablespace yang kita inginkan dicontohkan sebagai berikut: create table kinerja( id number, station varchar2(5), bay varchar2(7), awal varchar2(25), akhir varchar2(25) ) tablespace kinerja Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 39. Membuat Index Pada saat kita melakukan query terhadap suatu tabel yang sangat besar (jumlah data melebihi 1 juta baris), kita dapat mempercepat proses query dengan menambahkan index pada tabel dimaksud. Berikut adalah contoh penggunaan index. Create index nama_index on nama_tabel (nama_kolom); Create index station_idx on kinerja(station); Agar kita dapat melihat perbedaan kecepatan query, kita dapat menggunakan perintah berikut:


65 dari 71


SET timing on; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 40. Mengontrol Akses Pengguna 40.1. Privilege Sistem Privilege Sistem CREATE USER DROP USER DROP ANY TABLE BACKUP ANY TABLE SELECT ANY TABLE CREATE ANY TABLE

40.2.

Otorisasi Operasi Diijinkan untuk membuat user Oracle Diijinkan untuk menghapus atau menghilangkan user lain Diijinkan untuk menghapus tabel dalam setiap skema Diijinkan untuk melakukan back up setiap tabel dalam setiap skema menggunakan export utility Diijinkan untuk melakukan query tabel, view, atau snapshot dalam setiap skema Diijinkan untuk membuat tabel dalam setiap skema

Membuat User

CREATE USER scott IDENTIFIED BY tiger; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 40.3.

Typical User Privileges

Privilege Sistem CREATE SESSION CREATE TABLE CREATE SEQUENCE CREATE VIEW

40.4.

Otorisasi Operasi Diijinkan untuk terhubung (connect) ke database Diijinkan untuk membuat tabel dalam skema user Diijinkan untuk membuat sequence dalam skema user Diijinkan untuk membuat view dalam skema user

Memberi Grant Pada Privilege Sistem

GRANT resource TO agus;


66 dari 71


Grant resource bertujuan agar user dapat membuat tabel menggunakan tablespace system. GRANT create session, create table, create sequence TO scott; Grant create session bertujuan agar user dapat terhubung (connect ) ke usernya masing-masing. Grant create table bertujuan agar user dapat membuat table sendiri. Grant create sequence bertujuan agar user dapat membuat sequence sendiri. Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 41. Role Role adalah nama dari grup privilege yang dapat diberikan pada pengguna. Metode ini memudahkan kita untuk menghapus maupun memelihara privilege. 41.1. Membuat dan Memberikan Privilege pada Sebuah Role - Membuat role CREATE ROLE manager; -

Memberikan privilege pada role GRANT create table, create view TO manager;

-

Memberikan role pada user GRANT manager TO DEHAAN, KOCHHAR;

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 42. Mengubah Password Kita dapat mengubah password user dengan menggunakan perintah ALTER USER ALTER USER scott IDENTIFIED BY lion; Catatan : __________________________________________________________________


67 dari 71


__________________________________________________________________ __________________________________________________________________ 43. Grant Object Privilege Grant privilege dapat diberikan pada objek tertentu, misalkan pada sebuah tabel bahkan pada sebuah kolom tertentu. GRANT select ON hr.employees TO yudi,ichal GRANT update (department_name, location_id) ON hr.departments TO yudi,ichal Kemudian coba buka di user yudi atau ichal. SELECT * FROM hr.departments Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 44. Menggunakan Keyword WITH GRANT OPTION dan PUBLIC GRANT select, insert ON hr.departments TO scott WITH GRANT OPTION; GRANT select ON hr.departments TO PUBLIC; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 45. Mencabut (Revoke) Object Privilege Sebagai contoh, connect sebagai user Alice. Kemudian lakukan statement berikut untuk mencabut privilege SELECT dan INSERT terhadap user Scott pada tabel DEPARTMENTS REVOKE select, insert ON hr.departments


68 dari 71


FROM

scott;

Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 46. Tabel Eksternal 46.1. Membuat Directory Tabel eksternal bertujuan untuk memasukkan data ke tabel Oracle berdasarkan file teks di operating system (Windows, Linux, Unix, dsb). Sebelum membuat tabel eksternal untuk Oracle, kita harus membuat terlebih dahulu direktori yang menjadi acuan dari file teks tersebut. Untuk membuat direktori dimaksud, kita dapat menjalankan perintah SQL berikut: create directory kinerja_operasi as 'd:\kinerja_operasi'; Catatan : __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ 46.2.

Contoh File Teks Dipisahkan oleh Spasi

0008 0009 0010 0011 0012

BEKS7 CRTA7 CRTA7 CRTA7 CRTA7

5IBT7-2 5IBT7-2 5IBT7-2 5IBT7-2 5IBT7-2

Simpan file di atas 'd:\kinerja_operasi'.

dengan

46.2.1.

08/01/2005 08/01/2005 08/01/2005 08/01/2005 08/01/2005

nama

07:14:51 08:03:15 09:04:34 12:54:40 12:58:51

08/01/2005 08/01/2005 08/01/2005 08/01/2005 08/01/2005

'gangguan.txt’

pada

13:32:35 08:51:33 12:53:57 12:57:18 12:59:48

directory

Windows

Membuat Tabel Eksternal

drop table kinerja; create table kinerja( id number, station varchar2(5), bay varchar2(7), awal varchar2(25), akhir varchar2(25) ) organization external (type oracle_loader default directory kinerja_operasi access parameters


69 dari 71


(records delimited by newline characterset WE8MSWIN1252 badfile 'kinerja_jelek' logfile 'kinerja_cakep' readsize 1048576 fields ldrtrim reject rows with all null fields ( id (1:4) char, station (9:13) char, bay (18:24) char, awal (29:47) char, akhir (52:70) char ) ) location('gangguan.txt') ) tablespace kinerja parallel 500 reject limit unlimited; Catatan : __________________________________________________________________ __________________________________________________________________ 46.3.

Contoh File Teks Dipisahkan oleh Koma

0008,BEKS7,5IBT7-2,08/01/2005 0009,CRTA7,5IBT7-2,08/01/2005 0010,CRTA7,5IBT7-2,08/01/2005 0011,CRTA7,5IBT7-2,08/01/2005 0012,CRTA7,5IBT7-2,08/01/2005

Simpan file di atas 'd:\kinerja_operasi'.

dengan

07:14:51,08/01/2005 08:03:15,08/01/2005 09:04:34,08/01/2005 12:54:40,08/01/2005 12:58:51,08/01/2005

nama

46.3.1.

'gangguan.txt’

13:32:35 08:51:33 12:53:57 12:57:18 12:59:48

pada

directory

Windows

Membuat Tabel Eksternal

drop table kinerja; create table kinerja( id number, station varchar2(5), bay varchar2(7), awal varchar2(25), akhir varchar2(25) ) organization external (type oracle_loader default directory kinerja_operasi access parameters (RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL


70 dari 71

Materi Oracle Revisi 21

Recommend Documents