Modul Praktikum Oracle Introduction SQL dan PL/SQL
Disusun oleh :
Humisar Hasugian, M.Kom
UNIVERSITAS BUDI LUHUR JAKARTA 2013
Kerangka Modul
Kode Matakuliah
: KP301
Nama Matakuliah
: Oracle : Introduction SQL dan PL/SQL PL/SQL
Bobot
: 3 SKS
Nama Modul
: Modul Pratikum Oracle : Introduction SQL dan PL/SQL
Daftar isi modul
:
Bab I
: Pratikum
Pengenalan
Oracle
Database
10g,
iSQLPlus
Environment, dan Instalasi Oracle Database 10g. Bab II
: Pratikum Menampilkan, Membatasi dan Mengurutkan Data Menggunakan Perintah Select.
Bab III
: Pratikum Menggunakan Single Row Function.
Bab IV
: Pratikum Menggunakan Single Row Function Lanjutan.
Bab V
: Pratikum Menggunakan Group Function.
Bab VI
: Pratikum Menampilkan Data dari Banyak Tabel.
Bab VII
: Pratikum Menggunakan Subqueries.
Bab VIII
: Pratikum Menggunakan Perintah DDL dan DML.
Bab IX
: Pratikum Pengolahan Object.
Bab X
: Pratikum Membuat Program Sederhana Menggunakan PL/SQL.
Bab XI
: Pratikum Membuat Program untuk Mengakses Oracle Database 10g,
Dengan
Struktur
Kondisi
dan
Struktur
Perulangan
menggunakan PL/SQL. Bab XII
: Pratikum Membuat Program Menggunakan Explicit Cursor.
Bab XIII
: Pratikum Membuat Stored Procedured.
Bab XIV
: Pratikum Membuat Stored Function dengan Parameter.
Bab I Pratikum Pengenalan Oracle Database 10g, iSQLPlus Environtment, dan Instalasi Oracle Database 10g
I.
Tujuan Pratikum
Agar mahasiswa dapat mengetahui apa itu oracle database 10g, mempunyai gambaran infrasturktur jaringan Oracle 10g, dapat mengakses iSQLPlus melalui salah satu browser, mengenal lingkungan kerja iSQLPlus dan dapat memasukkan username, password dan connect identifier database oracle, mengetahui daftar table-tabel database HR yang akan digunakan selama perkuliahan berlangsung dan bisa melakukan instalasi oracle database 10g pada komputer masing-masing.
II.
Bahan & Alat
Hardware berupa PC/laptop, software Oracle Database 10g, dan modul pratikum
III.
Metode yang digunakan
Untuk melaksanakan pratikum ini, anda harus menyediakan hardware dan software yang dibutuhkan.
IV.
Landasan Teori
Oracle database 10g merupakan relational database management system (RDBMS) yang dibuat oleh perusahaan Oracle yang terdiri atas kumpulan data-data dalam suatu sistem manajemen basis data. Oracle database terdiri atas obyek-obyek seperti obyek tabel, obyek view, obyek sequence, obyek index dan obyek synonym. Adapun infrasturktur yang dimiliki oracle 10g adalah sebagai berikut :
Grid Infrastruktur Oracle 10g
Oracle Database
Oracle Application Server
Oracle Enterprise Manager
Class Diagram Human Resources
V.
Bahan Pratikum
A. Install Oracle Database 10g Server pada komputer masing-masing. Ikuti langkah-langkah berikut ini untuk menginstall oracle database 10g pada komputer atau laptop yang anda gunakan. 1. Klik file setup.exe
Terdapat dua pilihan metode instalasi, yaitu Basic Installation dan Advanced Installation. 2. Pilih Basic Installation, pada kolom Oracle Home Location secara otomatis akan terisi. Home location adalah lokasi folder penyimpanan software oracle 10g, biasa disebut dengan Oracle_home. Oracle_home secara otomatis akan memilih partisi yang mempunyai free-space yang terbanyak. Installation type dan Global Database Name mengikuti nilai default, dilanjutkan dengan mengisi database password dan confirm password. Kemudian pilih tombol Next. 3. Silahkan menunggu proses persiapan penginstalan selesai.
4. Instaler akan melakukan verifikasi untuk memastikan spesifikasi hardware sesuai dengan kebutuhan minimal setting dan konfigurasi database oracle. Perhatikan dan sesuaikan spesifikasi hardware jika terdapat pesan error, dan kemudian klik Next.
5. Berikutnya akan tampil Summary, untuk verifikasi komponen-komponen yang akan diinstall, kemudian klik Install.
6. Tunggu proses penginstalan berhasil dengan sempurna
7. Jika sudah selesai, klik Password Management untuk membuka lock user HR
8. Buka lock user HR dan ganti password sesuai dengan yang diinginkan. Contoh user HR dengan New Password :hr dan Confirm Password : hr, kemudian klik OK.
9. Klik OK.
10. Perhatikan konfigurasi End of Installation, dan klik Exit.
11. Klik Yes untuk konfirmasi Exit.
B. Mengakses iSQLPlus Environtment. 1. Buka salah satu browser, misalkan Mozilla Firefox. 2. Pada Address ketik http://192.168.8.1:5560/isqlplus (sesuaikan dengan lokasi server database anda) atau gunakan http://localhost:5560/isqlplus jika anda bekerja pada komputer server oracle database. Username : hr Password : hr Connect Identifier : Kemudian klik Login
3. Maka akan tampil workspace area iSQLPlus seperti gambar dibawah ini :
4. Workspace area adalah tempat untuk menuliskan perintah-perintah SQL maupun perintah-perintah blok PL/SQL. 5. Klik tombol Execute untuk menjalankan perintah SQL atau PL/SQL. 6. Klik Load Script untuk membuka file script SQL dan PL/SQL yang sudah pernah tersimpan sebelumnya. 7. Klik tombol Save Script untuk menyimpan perintah SQL ata u PL/SQL. 8. Klik tombol Cancel untuk membatalkan penulisan perintah. 9. Klik tombol Clear untuk membersihkan workspace area. 10. Klik Tab History, untuk melihat histori perintah yang pernah di eksekusi. 11. Klik menu Preferences untuk melakukan pengaturan ulang terhadap editor iSQLPlus. 12. Klik menu Logout untuk keluar dari lingkungan li ngkungan iSQLPlus.
VI.
Daftar Pustaka
Intro, Oracle Database 10g. SQL Fundamentals I (D17108GC21),Edition 2.1, December 2006.
Bab II Pratikum Menampilkan, Membatasi dan Mengurutkan Data Menggunakan Perintah Select I.
Tujuan Pratikum
Agar mahasiswa dapat menggunakan perintah SELECT untuk menampilkan data yang diambil dari database, mengetahui kemampuan yang dimiliki perintah SELECT, dapat memberikan limit atau batasan terhadap data, mengurutkan data sesuai dengan kebutuhan, dan pengenalan variable subtitusi. II.
Bahan & Alat
Hardware berupa PC/laptop, software Oracle Database 10g, dan modul pratikum III.
Metode yang digunakan
Untuk melaksanakan pratikum ini, anda harus sudah menginstall oracle database 10g, membuka lock user HR, mengerti tentang class diagram Human Resources, dan bisa melakukan koneksi ke database oracle menggunakan iSQLPlus. IV.
Landasan Teori
Perintah Select digunakan untuk menampilkan data dari database, adapun kemampuan yang dimiliki perintah select adalah sebagai berikut :
Projection, kemampuan untuk menampilkan kolom-kolom tertentu sesuai dengan kebutuhan. Selection, kemampuan untuk menampilkan per baris (record), dengan memberikan limit atau batasan pada query dengan menambahkan perintah where. Join, kemampuan untuk menampilkan data dari dua atau banyak tabel secara bersamaan dengan melakukan join antar tabel-tabel yang berkaitan. Perhatikan primary key dan foreign key tabel-tabel tersebut.
Capabilities of Select Statement
Sintaks perintah select :
Ketentuan penulisan perintah SQL :
Perintah SQL tidak case sensitive (pengunaan huruf besar dan kecil bebas)
Penulisan perintah SQL bisa ditulis dalam satu atau banyak baris.
Penulisan keyword tidak bisa disingkat ataupun di pisah.
Perintah SQL pada iSQLPlus diakhiri dengan karakter ; (titik koma)
Penamaan kolom heading pada iSQLPlus selalu cetak tengah dan dalam bentuk hurup besar semua. Penggunaan operator Aritmatika dalam perintah SQL meliputi penjumlahan, pengurangan, perkalian dan pembagian. pembagian.
Nilai null merupakan nilai default yang menempati sebuah field dalam suatu tabel apabila field tersebut belum diberi nilai. Penanganan nilai null sangat diperlukan
untuk menghasilkan perintah dan aplikasi yang baik, beberapa hal yang perlu diketahui mengenai nilai null :
Nilai null tidak sama dengan 0 (nol) dan dan tidak sama dengan spasi kosong.
Nilai null akan mengakibatkan error jika tidak dikelola dengan dengan baik.
Untuk mengganti nilai null dengan nilai lain menggunakan fungsi NVL(paramater)
Pemberian nama kolom alias dengan memperhatikan ketentuan berikut ini :
Nama kolom alias digunakan untuk mengubah nama judul kolom pada saat data ditampilkan. Nama kolom alias bisa digunakan untuk keperluan keperluan kalkulasi Penulisan nama kolom alias langsung dituliskan setelah nama kolom, atau bisa juga dengan menggunakan menggunakan keyword AS Penggunaan keyword AS bersifat optional menggunakan kutip ganda untuk nama alias yang mengandung spasi atau karakter khusus
Operator concat (||) digunakan untuk menggabungkan dua karakter/varchar atau lebih kedalam satu kolom. Aturan penulisan tipe data karakter, number dan date yaitu :
Tipe data karakter dan date harus diapit dengan petik tunggal
Menampilkan karakter petik tunggal harus menggunakan perintah quote (q)
Nilai dengan tipe data karakter bersifat case sensitive
Nilai tipe data date bersifat format sensitive
Format default tipe data date DD-MON-RR
Perintah distinct digunakan untuk menghilangkan data yang sama (duplicate data) Untuk membatasi data yang ditampilkan sesuai dengan yang diinginkan menggunakan keyword WHERE
Sintaks penulisan WHERE :
Perbandingan kondisi menggunakan operator sebagai berikut :
Perintah like digunakan untuk pencarian data karakter didalam sebuah tabel, penulisan perintah like menggunakan karakter % untuk mewakili 0 karakter atau banyak karakter dan karakter _ (garis bawah) untuk mewakilik 1 karakter. Logical kondisi menggunakan operator sebagai berikut :
Untuk mengurutkan data agar lebih mudah untuk dibawa menggunakan perintah ORDER BY, terdiri atas urut dari kecil ke besar (ASC) dan urut dari besar ke kecil (DESC). Variabel subtitusi digunakan untuk menginput nilai pada saat proses run-time berlangsung. Dengan menambahkan karakter & didepan nama variabel. Variabel
subtitusi bisa disertakan pada SELECT, FROM, WHERE, ORDER BY, atau di keseluruhan perintah SELECT. V.
Bahan Pratikum
1.
Perintah SQL untuk menampilkan semua kolom yang terdapat pada tabel departments.
2.
Perintah SQL untuk menampilkan data pada kolom tertentu.
3.
Perintah SQL dengan menggunakan operator aritmatika
4.
Perintah SQL menggunakan operator aritmatika dengan level penugasan yang berbeda.
5.
Perintah untuk menampilkan data dengan nilai null
6.
Perintah untuk mengganti nama kolom alias.
7.
Perintah menggunakan operator concat
8.
Perintah penulisan karakter string
9.
Perintah untuk menampilkan karakter petik tunggal
10. Perintah untuk menghilangkan data duplicate.
11. Perintah SQL dengan menggunakan keyword WHERE
12. Perintah untuk mencari data dengan tipe data karakter.
13. Perintah menampilkan data employees dengan salary lebih kecil atau sama dengan 3000.
14. Perintah mencari data employees dengan range salary antara 2500 – 3500 menggunakan perintah between.
15. Perintah menampilkan data employees yang mempunyai manager id 100,101 dan 201, dengan menggunakan perintah IN.
16. Perintah untuk mencari first_name employees dengan huruf pertama S
17. Perintah untuk menampilkan last_name employees dengan huruf kedua dari last_name = o
18. Perintah untuk mencari data employees yang masih diisi dengan nilai null
19. Perintah untuk menampilkan data employees yang mempunyai salary lebih besar atau sama dengan 10000 dan job_id mengandung kata MAN
20. Perintah untuk menampilkan data employees yang mempunyai salary lebih besar atau sama dengan 10000 atau job_id j ob_id mengandung kata MAN
21. Perintah menampilkan data employees yang job_id nya bukan IT_PROG, ST_CLERK, SA_REP dengan perintah NOT IN
22. Perintah untuk menampilkan data employees urut berdasarkan hire_date (tanggal masuk)
23. Perintah untuk menampilkan data employees urut berdasarkan hire_date dari terbesar ke terkecil.
24. Perintah untuk menampilkan data employees urut berdasarkan nama alias
25. Perintah untuk menampilkan data employees urut berdasarkan banyak kolom
26. Perintah untuk menampilkan data employees menggunakan variabel subtitusi pada saat pembatasan keluaran (WHERE) untuk tipe data number
27. Perintah untuk menampilkan data employees menggunakan variabel subtitusi pada saat pembatasan keluaran (WHERE) untuk tipe data karakter atau date.
28. Perintah untuk menampilkan data employees menggunakan variabel subtitusi pada keseluruhan perintah.
29. Perintah untuk mengaktifkan verifikasi pada saat menggunakan variabel subtitusi.
VI.
Daftar Pustaka
Retrieving Data Using The SQL Select Statement, Oracle Database 10g. SQL Fundamentals I (D17108GC21),Edition 2.1, December 2006. Restricting and Sorting Data, Oracle Database 10g. SQL Fundamentals I (D17108GC21),Edition (D17108GC21),Edition 2.1, December 2006.
Bab III Pratikum Menggunakan Single Row Function I.
Tujuan Pratikum
Agar mahasiswa dapat menggunakan single row function dalam perintah SQL untuk menampilkan data, mengetahui berbagai jenis type single row function yang tersedia dalam perintah SQL, bisa menggunakan fungsi pengolahan karakter, fungsi pengolahan number dan fungsi pengolahan pen golahan date dat e dalam perintah SQL, dan mengetahui mengetah ui berbagai jenis fungsi untuk un tuk konversi antar tipe ti pe data. II.
Bahan & Alat
Hardware berupa PC/laptop, software Oracle Database 10g, dan modul pratikum
III.
Metode yang digunakan
Untuk melaksanakan pratikum ini, anda harus sudah menginstall oracle database 10g, membuka lock user HR, mengerti tentang class diagram Human Resources, melakukan koneksi ke database oracle menggunakan iSQLPlus, dan mengerti penggunaan perintah select, s elect, from, where dan order by by
IV.
Landasan Teori
Dengan menggunakan Single Row Function akan membuat query lebih powerfull, karena dapat digunakan untuk memanipulasi data. Function dapat digunakan untuk keperluan : Melakukan kalkulasi terhadap data Modifikasi data
Manipulasi keluaran untuk kumpulan baris data Mengatur format keluaran tipe data date dan tipe data number Konversi antar tipe data
Fungsi kadang-kadang menggunakan parameter dan selalu menghasilkan sebuah nilai (return value).
Fungsi terbagi atas 2 tipe yaitu : single row functions dan multiple row functions. Single Row Function, fungsi ini hanya beroperasi per baris dan menghasilkan hanya satu keluaran per baris. Jenis-jenis tipe Single Row Function yaitu : Character Number Date Convertion General
Multiple Row Functions, fungsi dapat memanipulasi sekumpulan data untuk menghasilkan satu keluaran per grup baris, yang sering disebut juga dengan group functions.
Kemampuan Single Row Function : Memanipulasi data item Menerima argumen dan menghasilan satu nilai Beroperasi untuk setiap baris Selalu menghasilkan satu nilai per baris Dapat mengubah tipe data Dapat bersarang (nested) Menerima argumen bisa berupa nama kolom atau dalam bentuk ekspresi.
Sintaks penulisan Single Row Function :
Sysdate merupakan fungsi yang digunakan untuk menampilkan tanggal sekarang dari sistem, dimana sysdate dapat mengolah nilai date dan nilai time. Operator aritmatika yang bisa dikenakan terhadap tipe data date antara lain : penjumlahan dan
tanggal,
antara dua tipe data date untuk
menghasilkan selisih jumlah hari antara tanggal tersebut, dan menambahkan jam ke dalam tipe data date.
Sintaks penulisan :
Harus diapit dengan petik tunggal
Bersifat case sensitive
Nama hari dan nama bulan pada keluaran secara otomatis diisi dengan spasi kosong.
Untuk menghilangkan spasi kosong pada nama hari dan nama bulan menggunakan tambahan fm
Koversi tipe data Date menjadi tipe data Karakter :
Menggunakan fungsi TO_CHAR
Sintaks penulisan konversi date menjadi karakter :
Elemen-elemen format model
Koversi tipe data Number menjadi tipe data Karakter :
Menggunakan fungsi TO_CHAR
Sintaks penulisan konversi number menjadi karakter :
Elemen-elemen format model
Koversi tipe data Karakter menjadi tipe data Number :
Menggunakan fungsi TO_NUMBER
Sintaks penulisan konversi karakter menjadi number :
Koversi tipe data Karakter menjadi tipe data Date :
V.
Menggunakan fungsi TO_DATE
Sintaks penulisan konversi karakter menjadi date :
Bahan Pratikum
1.
Perintah SQL untuk menampilkan data employees yang mempunyai last_name = higgins.
2.
Perintah SQL untuk menampilkan data employees menggabungkan first_name dan last_name dengan nama alias NAME, menghitung jumlah karakter last_name, mencari posisi karakter a pada kolom last_name dengan nama alias Contains ‘a’? dengan 3 karakter terakhir job_id mengandung kata REP.
3.
Modifikasi perintah SQL diatas untuk menampilkan data employees dengan karakter terakhir dari last_name mengandung hurup n, sehingga keluaran seperti gambar dibawah.
4.
Perintah SQL untuk menghitung nilai 45.923 desimal 2, 45.923 desimal 0, dan 45.923 desimal -1 dengan menggunakan fungsi pembulatan keatas.
5.
Perintah SQL untuk menghitung nilai 45.923 desimal 2, 45.923 desimal 0, dan 45.923 desimal -1 dengan menggunakan fungsi pembulatan kebawah.
6.
Perintah SQL untuk menampilkan last_name, salary dan sisa bagi salary/5000 untuk semua employees dengan job_id = SA_REP.
7.
Perintah SQL untuk menampilkan last_name dan hire_date untuk semua employees dengan tanggal masuk dibawah 01 februari 1988
8.
Perintah SQL untuk menampilkan last_name, lama masa kerja dalam minggu untuk employees dengan department_id = 90
9.
Tulislah perintah SQL untuk melengkapi tabel dibawah sehingga menghasilkan keluaran seperti yang ada pada kolom Result
10. Tampilkan employee number, lama kerja, lama kerja employees dalam satuan bulan, gambaran lama kerja pegawai 6 bulan kedepan, hari jumat pertama pada saat masuk kerja, dan hari terakhir berdasarkan bulan masuk untuk semua
employee yang sudah bekerja sedikitnya 80 bulan, sehingga akan menghasilkan keluaran seperti dibawah :
11. Tulislah perintah SQL untuk melengkapi tabel dibawah sehingga menghasilkan keluaran seperti yang ada pada kolom Result
12. Tuliskan perintah untuk menampilkan employee_id, hire_date, nama bulan mulai kerja dengan menggunakan fungsi round dan fungsi trunc, untuk semua employees yang bekerja pada tahun 1997. Sehingga menghasilkan keluaran seperti dibawah.
13. Tuliskan perintah untuk menampilkan employee id dan hire_date dalam format MM/YY untuk employees dengan nama belakang Higgins. Sehingga keluaran seperti dibawah
14. Tuliskan perintah untuk menampilkan last_name dan hire_date dalam format DD Month YYYY dan beri nama alias HIREDATE dan hilangkan semua spasi kosong pada hire_date yang tidak dibutuhkan, untuk semua employees.
15. Modifikasi perintah diatas untuk mengubah format keluaran tanggal (hire_date) dari 17 June 1987 menjadi “Seventeenth of June 1987 12:00:00 AM” untuk keseluruhan employees. Sehingga keluaran akan menjadi seperti dibawah ini.
Perhatikan format keluaran tanggal karakter pertama dimulai dengan huruf besan dan berikutnya huruf kecil 16. Tuliskan perintah untuk menampilkan gaji employees dalam bentuk mata uang Dolar untuk employees yang bernama belakang Ernst.
17. Tuliskan perintah SQL untuk menampilkan tanggal hari ini dan beri nama alias Date.
18. Bagian HR membutuhkan laporan untuk menampilkan employee number, last name, salary dan kenaikan salary 15.5% untuk semua employees. Dan beri nama alias New Salary. Sehingga menghasilkan keluaran berikut ini.
19. Modifikasi perintah diatas dengan menambah kolom baru yang merupakan pengurangan antara salary sala ry baru dengan salary sal ary lama, dan beri nama alias Increase, sehingga menampilkan keluaran berikut.
20. Tuliskan perintah SQL untuk menampilkan last name dengan karakter pertama huruf besar dan semua karakter berikutnya huruf kecil dan hitung panjang digit dari last name untuk semua employees yang last name nya dimulai dengan huruf J, A atau M, dan urutkan berdasarkan last name.
21. HR departemen membutuhkan data masa kerja employee untuk masing-masing employee, dengan menampilkan last name dan perhitungan jumlah bulan antara hari ini dengan tanggal masuk kerja employees (hire_date), dan beri nama alias MONTHS_WORKED, urutkan berdasarkan jumlah bulan kerja. Sehingga menampilkan keluaran berikut.
22. Buat laporan untuk menghasilkan laporan sebagai berikut : earns monthly but wants <3 * salary> dan beri nama alias Dream Salaries. Sehingga menghasilkan keluaran berikut.
23. Buat query untuk menampilkan last name dan salary untuk keseluruhan employees, dengan format tampilan salary 15 karakter rata kiri dengan menggunakan simbol $ dan beri nama alias SALARY, dengan keluaran sebagai berikut.
24. Tampilkan last name, hire_date, salary dan gambaran tanggal gaji pada hari senin pertama dalam 6 bulan kedepan, dengan nama alias REVIEW. Format tanggal yang tampil sesuaikan dengan “Monday, the Thirty-First od July, 2000”, sehingga keluaran seperti dibawah.
25. Tampilkan last name, hire date, dan nama hari dari employees pertama kali berkerja dan beri nama alias DAY, urut berdasarkan nama hari dimulai dengan hari Monday. Seperti keluaran berikut.
VI.
Daftar Pustaka
Using Single Row Function to Customize Output, Oracle Database 10g. SQL Fundamentals I (D17108GC21),Edition 2.1, December 2006.
Bab IV Pratikum Menggunakan Single Row Function Lanjutan I.
Tujuan Pratikum
Agar mahasiswa dapat menggunakan single row function lanjutan dalam perintah SQL untuk menampilkan data, mengetahui RR date format, dapat membuat fungsi bersarang (nested), mengetahui fungsi-fungsi umum oracle seperti NVL, NVL2, DECODE, NULLIF, COALESCE. II.
Bahan & Alat
Hardware berupa PC/laptop, Software Oracle Database 10g, dan modul pratikum
III.
Metode yang digunakan
Untuk melaksanakan pratikum ini, anda harus sudah menginstall oracle database 10g, membuka lock user HR, mengerti tentang class diagram Human Resources, melakukan koneksi ke database oracle menggunakan iSQLPlus, dan mengerti penggunaan perintah select, from, where dan order order by.
IV.
Landasan Teori
RR date format sama seperti YY date format, tapi bisa digunakan untuk menampilkan tahun pada abad (century) yang berbeda. Sepert i gambaran dibawah.
Fungsi Bersarang (Nested)
Single row functions bisa dijadikan bersarang menjadi beberapa level.
Fungsi bersarang dikerjakan mulai dari fungsi terdalam sampai dengan fungsi terluar.
General Function :
NVL (expr1, expr2) Digunakan untuk mengkonversi nilai null menjadi nilai baru sesuai kebutuhan. Konversi untuk berbagai tipe data :
Contoh : NVL (commission_pct,0) NVL (hire_date, ’01-JAN’01-JAN-97’) 97’) NVL (job_id, ‘No Job Yet’) Yet’)
NVL2 (expr1, expr2, expr3) expr3) Digunakan untuk mengecek nilai null atau tidak dan mengganti dengan nilai yang lain, misalnya jika expr1 tidak null maka diberi nilai expr2 tapi jika null maka diberi nilai expr3.
NULLIF (expr1, expr2)
Membandingkan dua ekspresi jika nilai ekspresinya sama maka diberi nilai null dan jika nilai ekspresinya tidak sama maka diberi di beri nilai expr1.
COALESCE (expr1, expr2, ...., exprn) Memberikan nilai dari ekspresi pertama yang bukan bernilai null. Keunggulan fungsi COALESCE dibanding dengan fungsi NVL adalah bisa memberikan banyak alternatif nilai. Memberikan nilai expr1 jika expr 1 tidak bernilai null. Memberikan nilai expr2 jika nilai expr1 bernilai null dan nilai expr2 tidak bernilai null. Memberikan nilai exprn jika nilai ekspresi sebelumnya semuanya bernilai null. Semua ekspresi yang dituliskan harus mempunyai tipe data yang sama.
Ekspresi Kondisi
Ekspresi kondisi digunakan untuk menggantikan perintah kondisi if then else dalam perintah SQL. Menggunakan dua metode antara lain :
CASE Ekspresi (berdasarkan ANSI SQL)
Fungsi DECODE, bisa digunakan spesifik untuk oracle sintaks
V.
Bahan Pratikum
1.
Perintah SQL untuk menampilkan last_name dan tanggal masuk dengan format DD-Mon-YYYY untuk semua employees yang masuk sebelum tanggal 01-Jan-90 dengan menggunakan format century (RR)
2.
Berdasarkan soal diatas ubah pencarian data untuk menampilkan last_name dan tanggal masuk dengan format DD-Mon-YYYY untuk semua employees yang masuk sebelum tanggal 01-Jan-90 dengan menggunakan format YY. Pastikan keluaran sebagai berikut : no rows selected Jawab :
3.
Perintah SQL untuk menampilkan last_name, 8 karakter pertama last_name dan gabungkan dengan karakter _US dan kemudian konversi menjadi guruf besar semua, untuk semua employees yang bekerja pada department_id 60.
4.
Tuliskan perintah SQL untuk menampilkan last_name, salary, commission_pct nilai null ganti menjadi 0 (nol), (salary * 12) + (salary * 12 * commission_pct nilai null ganti dengan 0 (nol)) dengan nama allias AN_SAL untuk keseluruhan employees.
5.
Tuliskan perintah SQL untuk menampilkan last_name, salary, commission_pct tanpa fungsi NVL, (salary * 12) + (salary * 12 * commission_pct tanpa fungsi NVL) dengan nama allias AN_SAL untuk keseluruhan employees, sehingga menghasilkan keluaran seperti gambar dibawah.
6.
Jelaskan perbedaan antara perintah SQL pada soal nomor 4 dan perintah SQL pada soal nomor 5 diatas. Jawab :
7.
Tuliskan perintah SQL untuk menampilkan last_name, salary, commission_pct, keterangan tambahan jika commission_pct tidak null maka beri keterangan ‘SAL+COM’ ‘SAL+COM’ dan jika bernilai null beri keterangan ‘SAL’ dengan nama alias income untuk semua employees yang mempunyai department_id 50 dan 80.
8.
Tuliskan perintah SQL untuk menampilkan first_name, jumlah digit dari first_name dengan nama alias expr1, last_name, jumlah digit dari last_name dengan nama alias expr2, dan bandingkan jumlah digit first_name dengan jumlah digit last_name jika sama beri nilai null dan jika berbeda tampilkan nilai jumlah digit first_name dan beri nama alias RESULT.
9.
Tuliskan perintah SQL untuk menampilkan last_name, dan data manager_id jika ternyata null tampilkan besar commission_pct dan jika nilainya null maka beri nilai -1 dengan nama alias comm untuk semua employees dan urutkan berdasarkan commission_pct dari terkecil ke terbesar.
10. Tuliskan perintah SQL untuk menampilkan last_name, job_id, salary dan informasi revisi perubahan besar gaji dengan ketentuan sebagai berikut :
Jika job_id = IT_PROG maka besar gaji 1.10 * salary
Jika job_id = ST_CLERK maka besar gaji 1.50 * salary
Jika job_id = SA_REP maka besar gaji 1.20 * salary
Dan beri nama alias REVISED_SALARY untuk keseluruhan employees dengan menggunakan perintah CASE.
11. Tampilkan hasil keluaran dari perintah SQL berikut ini, sesuaikan keluaran dengan soal sebelumnya.
12. Tuliskan perintah SQL untuk menampilkan last_name, job_id, salary dan informasi revisi perubahan besar gaji dengan ketentuan sebagai berikut :
Jika job_id = IT_PROG maka besar gaji 1.10 * salary
Jika job_id = ST_CLERK maka besar gaji 1.50 * salary
Jika job_id = SA_REP maka besar gaji 1.20 * salary
Dan beri nama alias REVISED_SALARY untuk keseluruhan employees dengan menggunakan perintah DECODE,
13. Tuliskan perintah SQL untuk menampilkan last_name, salary dan besar persentase pajak dengen nama alias TAX_RATE untuk semua employees yang mempunyai department_id = 80, dan gambarkan hasil keluarannya.
14. Buat query untuk menampilkan last name, besar commission, dan jika seorang employee tidak punya commission maka tampilkan “No Commission” dan beri nama alias COMM, seperti keluaran berikut.
15. Buat query untuk menampilkan 8 karakter pertama dari last name employees dan perkiraan besar nilai gajinya dengan tanda karakter *, urutkan data berdasarkan besar gaji dari terbesar ke terkecil, dan beri nama alias EMPLOYEES_AND THEIR_SALARIES. Sehingga Sehingga keluaran seperti dibawah.
16. Menggunakan fungsi DECODE, buat queri untuk menampilkan grade dari semua employees berdasarkan nilai dari JOB_ID, sesuai ketentuan berikut :
Sehingga akan menampilkan keluaran sebagai berikut :
17. Tulis ulang perintah diatas dengan menggunakan perintah CASE. Jawab :
VI.
Daftar Pustaka
Using Single Row Function to Customize Output, Oracle Database 10g. SQL Fundamentals I (D17108GC21),Edition 2.1, December 2006.
Bab V Pratikum Menggunakan Group Function I.
Tujuan Pratikum
Agar mahasiswa dapat menggunakan group function dalam perintah SQL untuk menampilkan data, dapat mengidentifikasi group function yang tersedia. Memahami kegunaan group function, memahami kegunaan GROUP BY, dan mengerti kegunaan perintah HAVING.
II.
Bahan & Alat
Hardware berupa PC/laptop, Software Oracle Database 10g, dan modul pratikum
III.
Metode yang digunakan
Untuk melaksanakan pratikum ini, anda harus sudah menginstall oracle database 10g, membuka lock user HR, mengerti tentang class diagram Human Resources, melakukan koneksi ke database oracle menggunakan iSQLPlus, dan mengerti penggunaan perintah select, from, where, order by dan sudah memahami kegunaan single row functions.
IV.
Landasan Teori
Group Functions bekerja pada sejumlah baris (data) dan selalu menghasilkan hanya satu nilai per group.
Jenis-jenis group Functions :
AVG Digunakan untuk mencari nilai rata-rata dari sekumpulan data. COUNT Digunakan untuk menghitung jumlah baris dalam sebuah tabel. MAX Digunakan untuk mencari nilai terbesar dari sekumpulan data. MIN Digunakan untuk mencari nilai terkecil dari sekumpulan data STDDEV SUM Digunakan untuk menjumlahkan keseluruhan nilai. VARIANCE
Group Function digunakan untuk pengolahan data dengan jumlah yang banyak dan menghasilkan hanya satu keluaran. Sintaks Group Functions :
SELECT : untuk mengambil data yang diinginkan, bisa terdiri atas nama field ataupun nama group function.
FROM : untuk menyebutkan nama tabel pencarian
WHERE : untuk memberi batasan terhadap data yang akan dicari
GROUP BY : pengelompokan data menjadi bagian-bagian yang lebih kecil, biasanya selalu sama dengan nama field yang diselect
ORDER BY : mengurutkan data sesuai dengan yang diinginkan.
Fungsi AVG dan SUM digunakan hanya untuk tipe data number
Fungsi MIN dan MAX digunakan untuk tipe data number, karakter dan date.
COUNT(*) akan menghitung jumlah baris yang terdapat dalam sebuah tabel, tanpa melihat isinya bernilai null atau tidak. COUNT(expr) digunakan untuk menghitung jumlah record yang tidak berupa nilai null. COUNT(DISTINCT expr) digunakan untuk menghitung jumlah record yang unik saja, jika terdapat data yang sama maka akan dihitung sebagai satu record.
Membuat Pengelompokan Data
Sintaks penulisan :
Mengelompokkan data menjadi kelompok yang lebih kecil menggunakan perintah GROUP BY. Pengelompokan Lebih dari Satu Kolom
Contoh kesalahan yang terjadi pada pengelompokan data
Beberapa hal yang diperhatikan pada saat penggunaan group by :
Klausa Where tidak bisa digunakan untuk membatasi group
Klausa having digunakan untuk membatasi group
Tidak bisa menggunakan group function dalam klausa where
Ketika terdapat klausa HAVING pada sebuah queri maka Oracle Server akan melakukan tahap-tahapan berikut :
Baris atau record akan dikelompokkan terlebih dahulu
Kemudian group function yang digunakan akan bekerja
Data yang ditampilkan adalah data yang sesuai dengan klausa HAVING.
Sintak penulisan group functions dengan klausa having :
V.
Bahan Pratikum
1.
Tuliskan perintah SQL untuk menghitung rata-rata gaji pegawai, gaji terbesar, gaji terkecil, dan total keseluruhan gaji untuk semua employees yang mempunyai job id mengandung karakter REP.
2.
Tuliskan perintah SQL untuk menghitung tanggal kerja terlama dan tanggal kerja terbaru dari semua employees.
3.
Tuliskan perintah SQL untuk mengitung jumlah pegawai yang bekerja pada department_id = 50.
4.
Tuliskan perintah SQL untuk mengitung jumlah pegawai yang bekerja pada department_id = 80, khusus bagi pegawai yang mempunyai komisi.
5.
Menghitung jumlah departmet id yang terdaftar pada tabel employees, jika terdapat deparment id dalam jumlah banyak maka akan dihitung 1.
6.
Menghitung komisi rata-rata dari keseluruhan employees, jika tidak mempunyai komisi atau null maka tidak diikutkan untuk mendapatkan nilai-rata-rata.
7.
Menghitung komisi rata-rata dari keseluruhan employees, termasuk pegawai yang tidak mempunyai commission_pct (bernilai null).
8.
Menampilkan department_id dan Menghitung gaji rata-rata employees per department_id.
9.
Menghitung gaji rata-rata employees per department_id, tanpa menampilkan department_id yang bersangkutan.
10. Menampilkan data department id dengan nama alias dept_id, job id, menjumlahkan
semua
department_id dan job_id.
gaji
employees
dan
dikelompokkan
berdasarkan
11. Menampilkan department_id dan gaji terbesar per grup department_id dan yang ditampilkan hanya gaji terbesar diatas 10000 saja.
12. Menampilkan job_id dan total salary per group job_id dengan memberi nama alias PAYROLL, total yang dicari untuk job_id yang tidak mengandung karakter REP, data yang ditampilkan hanya untuk total gaji diatas 13000 saja dan urutkan berdasarkan total gaji dari terkecil ke terbesar.
13. Menampilkan gaji rata-rata terbesar dari tabel employees kelompokkan berdasarkan department_id.
14. Terdapat Struktur tabel sebagai berikut :
a.
Tuliskan queri untuk menampilkan jumlah FPP terbesar, jumah FPP terkecil dan jumlah FPP Rata-rata yang pernah diajukan oleh anggota koperasi. Jawab :
b.
Tuliskan queri untuk untuk menampilkan nama anggota koperasi yang pernah meminjam dengan jumlah FPP terbesar. Jawab :
c.
Tuliskan queri untuk menghitung jumlah anggota yang pernah meminjam dikelompokkan berdasarkan bulan permohonan peminjaman selama tahun 2013, urutkan berdasarkan jumlah anggota dari terbesar ke terkecil. Jawab :
d.
Tuliskan queri untuk menghitung total FPP yang pernah diajukan anggota dikelompokkan berdasarkan bulan permohonan peminjaman selama tahun 2013, dan yang ditampilkan hanya total FPP yang diatas 100.000.000,urutkan berdasarkan total FPP dari terkecil ke terbesar. Jawab :
VI.
Daftar Pustaka
Reporting Aggregated Data Using the Group Functions, Oracle Database 10g. SQL Fundamentals I (D17108GC21),Edition 2.1, December 2006.
Bab VI Pratikum Menampilkan Data dari Banyak Tabel I.
Tujuan Pratikum
Agar mahasiswa dapat menggunakan perintah SELECT untuk menampilkan data dari banyak tabel menggunakan perintah equijoins dan non equijoins, bisa melakukan self join terhadap tabel itu sendiri, dan dapat memahami penggunaan penggunaan outer join.
II.
Bahan & Alat
Hardware berupa PC/laptop, Software Oracle Database 10g, dan modul pratikum. III.
Metode yang digunakan
Untuk melaksanakan pratikum ini, anda harus sudah menginstall oracle database 10g, membuka lock user HR, mengerti tentang class diagram Human Resources, melakukan koneksi ke database oracle menggunakan iSQLPlus, dan mengerti penggunaan perintah select, from, where, order by dan sudah memahami kegunaan single row functions dan group functions.
IV.
Landasan Teori
Memperoleh data dari banyak tabel.
Berdasarkan gambar diatas laporan yang terbentuk menampilkan data yang diambil dari dua tabel yaitu :
Employee_id terdapat pada tabel employees
Department_id terdapat pada tabel employees dan tabel departments
Deparment_name terdapat pada tabel departments Untuk menghasilkan keluaran perlu menghubungkan tabel employees dengan tabel departments sehingga dapat mengakses data dari kedua tabel ter sebut.
Jenis-jenis JOINS tabel berdasarkan SQL:1999 SQL:1999 standard :
Cross Joins
Natural Joins
Using klausa
Full (or two-sided) outer joins
Arbitrary join conditions for outer joins
Sintaks untuk menampilkan data dari banyak tabel :
Menampilkan data dengan Natural Joins
Klausa natural join akan menampilkan data berdasarkan semua kolom yang sama yang ada dalam dua tabel. Menampilkan baris dari dua tabel yang mempunyai nilai yang sesuai.
Jika mempunyai nama kolom yang sama tetapi dengan tipe data yang berbeda akan menimbulkan error Menampilkan data dengan USING Klausa
Sama dengan natural join dapat menampilkan data dari dua tabel dengan menggunakan nama kolom yang sama meskipun dengan tipe data yang berbeda. Jangan menggunakan nama tabel atau nama alias untuk penyebutan nama kolom.
Pemberian Nama Alias
Gunakan tabel prefix untuk menyebutkan nama kolom yang berasal dari banyak tabel. Menggunakan tabel prefix untuk meningkatkan performa queri Gunakan nama kolom alias untuk membedakan kolom yang sama yang terdapat dalam tabel berbeda.
Jangan menggunakan nama kolom alias pada klausa USING.
Kolom alias digunakan untuk menyederhanakan queri
Menampilkan data dengan Klausa ON
Klausa ON membuat perintah lebih mudah di pahami.
Menggunakan klausa ON untuk penulisan kondisi yang lebih spesifik.
Bisa menggunakan klausa ON meskipun nama kolom berbeda.
Self Joins menggunakan klausa ON
Nonequijoin Merupakan sebuah kondisi join yang menggunakan operator perbandingan yang tidak biasa. Seperti pada gambar dibawah dimana relationship antara employees dan job_grades berdasarkan kolom salary dan kolom lowest_sal dan kolom highest_sal. Biasanya menggunakan operator BETWEEN untuk mempermudah penulisan queri.
Outer Join Dengan menggunakan simple join maka akan menampilkan data yang terdapat pada semua tabel yang dijoinkan. Kadang kala dibutuhkan kemampuan untuk menampilkan semua data yang terdapat pada beberapa tabel meskipun data-data tersebut tidak
terhubung satu dengan yang lain. Seperti pada gambar dibawah, tabel departments terdapat record Contracting dengan id 190, dan pada tabel employees tidak terdapat employee yang mempunyai department_id 190. Agar bisa menampilkan semua department beserta dengan daftar pegawainya (ada ataupun tidak ada) maka kita bisa menggunakan queri outer join.
Inner Versus Outer Join
V.
SQL:1999, join dari dua tabel yang menampilkan data dengan baris yang sesuai saja disebut dengan inner join. Join antara dua tabel yang menghasilkan keluaran sama seperti inner join dan juga menampilkan baris yang tidak sesuai (unmatched) dari da ri tabel sebelah kiri disebut dengan left outer join. Join antara dua tabel yang menghasilkan keluaran sama seperti inner join dan juga menampilkan baris yang tidak sesuai (unmatched) dari tabel sebelah kanan disebut dengan right outer join. Join antara dua tabel yang menghasilkan keluaran seperti inner join dan juga menampilkan baris yang tidak sesuai dari tabel sebelah kiri dan jug amenampilkan yang tidak sesuai dari tabel sebelah kanan disebut dengan full outer join.
Bahan Pratikum
1.
Menampilkan data department id, department name, location id dan city dari tabel departments dan tabel locations locati ons menggunakan NATURAL JOIN.
2.
Berdasarkan perintah diatas tuliskan perintah SQL untuk Menampilkan data department id, department name, location id dan city dari tabel departments dan tabel locations menggunakan NATURAL JOIN tapi khusus untuk employees yang mempunyai department_id 20 dan 50 saja.
3.
Menampilkan data employee_id, last_name, location_id dan department_id untuk keseluruhan employees dengan menggunakan klausa JOIN.
4.
Ubah perintah pada soal diatas dengan mengganti penyebutan nama tabel dengan nama alias.
5.
Menampilkan data employee_id, last_name, location_id dan department_id untuk keseluruhan employees dengan menggunakan klausa ON
6.
Menampilkan last_name employee, last_name manager untuk keseluruhan employees menggunakan klausa ON.
7.
Menampilkan data employee_id, last_name, location_id dan department_id untuk keseluruhan employees dengan menggunakan klausa ON dan untuk employee yang kode manager = 149.
8.
Menampilkan data employee_id, city, dan department_name untuk keseluruhan employees dengan menggunakan klausa ON, join tiga tabel yaitu employees, departments, dan locations.
9.
Menampilkan last_name, salary, grade_level dari semua employees dimana grade_level ditentukan berdasarkan salary masing-masing employee dibadingkan dengan kolom lowest_sal dan kolom highest_sal.
10. Menampilkan last_name, department_id, dan department_name untuk semua employees yang mempunyai department_id maupun employees yang tidak mempunyai department_id.
11. Menampilkan last_name, department_id, dan department_name untuk semua departments yang mempunyai employees maupun departments yang tidak mempunyai employees.
12. Menampilkan last_name, department_id, dan department_name untuk semua employees yang mempunyai department_id maupun employees yang tidak mempunyai department_id dan menampilkan departments yang mempunyai employees maupun departments yang tidak mempunyai employees.
13. Tulis perintah SQL untuk mencetak alamat semua departments, dengan melibatkan
tabel
locations
dan
tabel
countries,
tampilkan
location_id,
street_address, city, state or province, dan country_name, dengan menggunakan NATURAL JOIN. Sehingga Sehingga akan menampilkan keluaran seperti berikut.
14. Tulis perintah SQL untuk menampilkan laporan keseluruhan employees yang terdiri
atas
last_name,
department_id,
dan
department_name
dengan
menggunakan salah satu join yang anda pahami. Sehingga akan menampilkan keluaran seperti berikut.
15. Tulis perintah SQL untuk menampilkan laporan employees yang tinggal di Toronto,
yang
terdiri
atas
last_name,
job_id,
department_id,
dan
department_name dengan menggunakan salah satu join yang anda pahami. Sehingga akan menampilkan keluaran seperti berikut.
16. Tulis perintah SQL untuk menampilkan last_name, employee_id, last_name managernya, dan employee_id managernya dan beri nama alias Employee, Emp#, Manager, and Mgr# dengan menggunakan salah satu join yang anda pahami. Sehingga akan menampilkan keluaran seperti berikut.
17. Ubah perintah SQL pada soal diatas untuk menampilkan semua employees termasuk King, yang tidak mempunyai manager. Sehingga akan menampilkan keluaran seperti berikut.
18. Tuliskan perintah SQL untuk menampilkan last_name, department_id, dan last_name employee lain yang mempunyai department yang sama dengan employee tersebut. Beri nama alias yang sesuai, Sehingga akan menampilkan keluaran seperti berikut.
19. Tuliskan
perintah
SQL
untuk
menampilkan
struktur
tabel
dari
tabel
JOB_GRADES, dan tuliskan perintah untuk menampilkan last_name, job_id, department_name, salary dan grade untuk keseluruhan employees, Sehingga akan menampilkan keluaran seperti berikut.
20. Tuliskan perintah SQL untuk menampilkan last_name dan hire_date semua employees yang diperkerjakan setelah tanggal masuknya Davies, Sehingga akan menampilkan keluaran seperti berikut.
21. Tuliskan perintah SQL untuk menampilkan last_name dan hire_date semua employees yang dipekerjakan sebelum managernya masing-masing. Dan tampilkan juga last_name dan hire_date dari managernya tersebut, seperti keluaran berikut.
VI.
Daftar Pustaka
Displaying Data from Multiple Tables, Oracle Database 10g. SQL Fundamentals I (D17108GC21),Edition (D17108GC21),Edition 2.1, December 2006.
Bab VII Pratikum Menggunakan Subqueries I.
TujuanPratikum
Agar mahasiswa dapat mengerti fungsi dan cara menggunakan subqueri, dapat memecahkan masalah dengan menggunakan subqueri, mengerti jenis-jenis subqueri, dan dapat menulis single-row dan multiple-row subqueri. II.
Bahan&Alat
Hardware berupa PC/laptop, Software Oracle Database 10g, dan modul pratikum. III.
Metode yang digunakan
Untuk melaksanakan pratikum ini, anda harus sudah menginstall oracle database 10g, membuka lock user HR, mengerti tentang class diagram Human Resources, melakukan koneksi ke database oracle menggunakan iSQLPlus, dan mengerti penggunaan perintah select, from, where, order by dan sudah memahami kegunaan single row functions, group functions, dan mampu menampilkan data dari banyak tabel
IV.
LandasanTeori
Employee mana yang mempunyai salary lebih besar dari pada salar y Abel?
Untuk memecahkan permasalah diatas, maka perlu dibuatkan dua queri, queri pertama untuk mendapatkan besar gaji Abel, dan queri kedua untuk mendapatkan gaji pegawai lain yang lebih besar dari pada gaji Abel. Untuk memecahkan masalah ini perlu mengkombinasikan kedua queri diatas, dengan menempatkan satu queri didalam queri yang lain. Queri yang didalam (subqueri) menghasilkan nilai yang akan digunakan oleh queri bagian luar (queri utama).
Sintaks penulisan subqueri
Subqueri (inner queri) di eksekusi terlebih dahulu kemudian dilanjutkan dengan queri utama (outer queri). Hasil dari subqueri akan digunakan oleh queri utama. Subqueri bisa di tempatkan pada posisi : o Klausa WHERE o Klausa HAVING dan o Klausa FROM Penggunaan operator yang diperbolehkan seperti >, =, or IN Perbandingan kondisi dibagi menjadi 2 : o Single row operator ( >, =, >=, <, <>, <=) o Multiple row operator (IN, (I N, ANY, ALL)
Subqueri harus diapit dengan tanda buka dan t utup kurung.
Tempatkan subqueri di posisi sebelah kanan dari kondisi perbandingan.
Penggunaan ORDER BY dalam subqueri tidak diperlukan
Gunakan single-row operator dengan single-row subqueri dan gunakan muliple-row operator dengan multiple-row subqueri.
Jenis - Jenis Subqueri
Single-row subqueri : queri manghasilkan hanya satu baris dari perintah inner select. dan operator perbandingan yang bisa digunakan :
Subqueri tidak hanya didalam klausa WHERE tapi bisa juga didalam klausa HAVING. o o
Oracle server akan eksekusi subqueri pertama kali Kemudian hasilnya akan dibandingkan dengan klausa HAVING yang ada di queri utama.
Multiple-row subqueri : queri menghasilkan lebih dari satu baris dari perintah inner select.dan operator perbandingan yang bisa digunakan :
V.
BahanPratikum
1.
Menampilkan data last_name, dan salary pegawai, yang gajinya lebih besar dari pada gaji Abel.
2.
Menampilkan last_name dan job_id pegawai dimana job_id nya sama dengan job_id pegawai dengan employee_id employee_id 141.
3.
Menampilkan last_name, job_id, dan salary untuk semua pegawai dengan job_id sama seperti job_id pegawai pegawai nomor 141, dan gaji harus lebih besar dari pegawai pegawai nomor 143
4.
Menampilkan last_name, job_id dan salary dari pegawai yang mempunyai gaji terkecil yng terdaftar pada tabel employees.
5.
Menampilkan department_id dan gaji pegawai terkecil dari departement tersebut, dimana gaji pegawai tersebut harus lebih besar dari gaji pegawai terkecil department_id 50.
6.
Jelaskan kegunaan dari perintah SQL dibawah ini.
7.
Jelaskan sebab timbulnya error pada perintah dibawah ini :
Karena penggunaan operator single-row (=) terhadap multiple-row subqueri. Untuk memperbaikinya maka operator = ganti dengan operator IN. 8.
Jelaskan kenapa perintah SQL dibawah tidak menampilkan keluaran?
Karena pada subqueri (inner queri) tidak ada pegawai dengan last_name Haas sehingga tidak terdapat data job_id yang akan dibandingkan dengan job_id queri utama. 9.
Menampilkan nama belakang, besar gaji dan kode departemen untuk semua pegawai yang mempunyai gaji yang sama dengan gaji terkecil pegawai untuk masing-masing departemen.
10. Menampilkan employee_id, last_name, job_id, dan salary untuk semua pegawai yang bukan job_id IT_PROG dan gajinya lebih kecil dari gaji terbesar pegawai dengan job_id = IT_PROG, menggunakan perintah ANY
11. Menampilkan employee_id, last_name, job_id, dan salary untuk semua pegawai yang bukan job_id IT_PROG dan gajinya lebih kecil dari semua gaji pegawai dengan job_id = IT_PROG, menggunakan perintah ALL
12. Jelaskan kenapa perintah SQL dibawah tidak menampilkan keluaran? Dan tuliskan perintah perbaikannya.
Karena didalam subqueri terdapat nilai null, sehingga mengakibatkan tidak ada nilai yang bisa dibandingkan dengan queri utama. Dan queri perbaikannya adalah:
13. Departemen HR membutuhkan queri yang dapat menginput nama belakang dari pegawai, berdasarkan inputan tersebut tampilkan nama belakang dan tanggal masuk untuk setiap pegawai yang satu departemen dengan pegawai yang diinput . sebagai contoh user yang diinput adalah Zlotkey, kemudian cari semua pegawai yang satu departemen dengan Zlotkey.
Jawab :
14. Buat sebuah laporan untuk menampilkan employee number, last name dan besar gaji semua pegawai yang memperoleh gaji lebih besar dari rata-rata gaji, urutkan berdasarkan besar gaji dari terkecil ke terbesar.
Jawab :
15. Buat sebuah queri untuk menampilkan employee number dan last name dari semua pegawai yang bekerja dalam departemen yang sama dengan last name pegawai yang mengandung hurup a.
Jawab :
16. Departemen HR membutuhkan lapaoran untuk menampilkan last name, department number dan job ID dari semua pegawai yang location id departmentnya 1700.
Jawab :
17. Buat laporan untuk menampilkan last name dan salary untuk setiap pegawai yang mempunyai atasan King.
Jawab :
18. Buat laporan untuk menampilkan department number, last name dan job ID untuk setiap pegawai yang bekerja sebagai Executive.
Jawab :
VI.
DaftarPustaka
Using Subqueries to Solve Queries, Oracle Database 10g. SQL Fundamentals I (D17108GC21),Edition (D17108GC21),Edition 2.1, December 2006.
Bab VIII Pratikum Menggunakan Menggunakan Perintah DDL dan DML I.
Tujuan Pratikum
Agar mahasiswa dapat mengerti perintah-perintah data manipulation language (DML) seperti perintah insert, update, delete dan transaksi control, mengerti penggunaan perintah-perintah data definition language (DDL).
II.
Bahan&Alat
Hardware berupa PC/laptop, Software Oracle Database 10g, dan modul pratikum. III.
Metode yang digunakan
Untuk melaksanakan pratikum ini, anda harus sudah menginstall oracle database 10g, membuka lock user HR, mengerti tentang class diagram Human Resources, melakukan koneksi ke database oracle menggunakan iSQLPlus, dan mengerti penggunaan perintah select, from, where, order by dan sudah memahami kegunaan single row functions, group functions, dan mampu menampilkan data dari banyak tabel
IV.
LandasanTeori
Data manipulation language (DML) di eksekusi ketika :
Menambah record baru kedalam table
Mengubah record yang sudah ada dalam sebuah table
Menghapus record yang sudah ada dalam sebuah table.
Pada gambar diatas terdapat data baru 70 Public Relation 100 1700, akan dimasukkan kedalam table departments.
Sintaks penulisan perintah INSERT :
Perintah untuk menambah record baru ke dalam table menggunakan perintah INSERT.
Menggunakan perintah ini hanya satu record yang bias diinsert.
Insert record baru harus mempunyai nilai (values) untuk setiap kolom.
Tipe data date dan karakter harus diapit karakter petik tunggal.
Insert record baru dengan nilai null dengan cara : o
Implicit method : menghilangkan kolom dari kolom list,
o
Explicit method : secara spesifik menuliskan NULL pada klausa VALUES.
Copy data dari table lain tidak boleh menggunakan klausa VALUES
Pada gambar diatas department_id 60 akan diubah menjadi department_id 30 untuk semua employees. Sintaks penulisan perintah UPDATE
Perintah untuk mengubah record dalam suatu table menggunakan perintah UPDATE.
Bisa melakukan perubahan terhadap banyak record sekaligus (jika di perlukan)
Mengubah record dengan data dari tabel lain
Pada gambar diatas akan dilakukan penghapusan record 100 Finance yang terdapat pada table departments. Sintaks penulisan perintah DELETE
Perintah untuk menghapus data dari sebuah table menggunakan perintah DELETE Bisa melakukan penghapusan data per satu record atau banyak record sekaligus.
Perintah TRUNCATE
Menghapus semua record yang terdapat dalam sebuah table. sehingga table menjadi kosong dengan struktur table tetap utuh. Sintaks penulisan perintah TRUNCATE
Database Obyek
Syarat pemberian nama table dan nama kolom :
Harus dimulai dengan huruf
Terdiri atas 1-30 karakter
Terdiri atas karakter A-Z, a-z, 0-9, _, & dan #
Tidak boleh menggunakan nama yang sama
Dan bukan reserved word
Sintaks untuk membuat table :
Jenis-jenis tipe data untuk pembentukan kolom (field)
Constraint Constraint adalah aturan-aturan tambahan yang akan dikenakan pada level table dan bias juga pada field level. Constraint digunakan untuk menghindari terjadinya keselasahan penghapusan data. Jenis-jenis constraint :
Not Null
Unique
Primary Key
Foreign Key
Check
Sintaks penulisan constraint :
Not Null Constraint
Not null constraint digunakan untuk memastikan bahwa kolom tersebut tidak boleh diisi dengan nilai null. Unique Constraint
Unique constraint digunakan untuk memastikan bahwa data yang akan diinput harus unik tidak boleh sama dengan data pada record lain. V.
Bahan Pratikum
1.
Perintah untuk menginsert data baru (70, Public Relations, 100, 1700) kedalam table departments .
2.
Perintah untuk insert data baru berupa nilai NULL secara implicit methode.
3.
Perintah untuk insert data baru berupa nilai NULL secara explicit methode.
4.
Perintah untuk insert special nilai seperti nilai tanggal dan jam sekarang kedalam table menggunakan fungsi SYSDATE
5.
Perintah insert data baru kedalam table tanpa menyebutkan spesifik nama-nama kolomnya.
Pastikan data terinsert seperti gambar dibawah ini
6.
Perintah untuk insert data ke table menggunakan variable subtitusi.
7.
Insert data kedalam sebuah table dengan data yang diambil dari table lain.
8.
Perintah untuk melakukan perubahan terhadap data tertentu dalam sebuah table.
Semua record akan berubah jika ji ka menghilangkan perintah WHERE 9.
Perintah untuk mengubah job dan salary employee 114 disamakan dengan job dan salary employee 205
10. Perintah untuk mengubah data berdasarkan table yang lain.
11. Perintah untuk menghapus data secara spesifikke record tertentu.
12. Perintah untuk menghapus data secara keseluruhan
13. Perintah untuk menghapus data berdasarkan table lain
14. Perintah untuk menghapus keseluruhan record tanpa menggunakan perintah delete.
15. Perintah untuk membuat table dept
16. Perintah untuk melihat struktur table.
17. Perintah untuk pemberian constraint primary key pada level kolom
18. Perintah untuk pemberian constraint primary key pada level tabel
19. Perintah untuk pemberian constraint unique pada level kolom
20. Perintah untuk pemberian constraint foreign key.
21. Perintah untuk pemberian constraint primary key, not null, unique, check, foregin key dalam satu perintah.
VI.
Daftar Pustaka
Manipulating
Data,
Oracle
Database
10g.
SQL
Fundamentals
I
(D17108GC21),Edition (D17108GC21),Edition 2.1, December 2006. Using DDL Statements to Create and Manage Tables, Oracle Database 10g. SQL Fundamentals I (D17108GC21),Edition 2.1, December 2006.
Bab IX Pratikum Pengolahan Object I.
Tujuan Pratikum
Agar mahasiswa dapat membuat simple dan complex views, dapat menerima data dari views, dapat membuat, mengelola dan menggunakan sequence, dapat membuat dan mengelola index, dan dapat membuat private dan public synonyms. II.
Bahan & Alat
Hardware berupa PC/laptop, Software Oracle Database 10g, dan modul pratikum. III.
Metode yang digunakan
Untuk melaksanakan pratikum ini, anda harus sudah menginstall oracle database 10g, membuka lock user HR, mengerti tentang class diagram Human Resources, melakukan koneksi ke database oracle menggunakan iSQLPlus.
IV.
Landasan Teori
Apa itu VIEW?
view digunakan untuk menampilkan atau bahkan menyembunyikan data dari suatu tabel atau dari view yang lainnya. View tidak mempunyai data sendiri tapi diambil dari suatu tabel yang disebut dengan base tabel, dengan view data bisa ditampilkan dan bisa juga diubah. Keuntungan menggunakan VIEWS
Untuk mebatasi pengaksesan data.
Untuk menyederhanakan queri yang kompleks
Menampilkan data dengan view yang berbeda-beda dengan menggunakan data yang sama. Independence data
Perbedaan Simple Views dan Complex Views
Sintaks Perintah membuat VIEWS
OR REPLACE
: mengupdate view yang sudah ada
FORCE
: membuat view tanpa memperhatikan ada atau tidaknya base table
NOFORCE
: membuat view jika base table ada. (default)
View
: nama dari view
Alias
: nama ekspresi yang akan diolah oleh view
Subquery
: perintah select untuk mengambil data dari base table
WITH CHECK OPTION
: menentukan hanya baris tersebut yang dapat diakses oleh view untuk melakukan insert dan update.
Constraint
: nama dari CHECK OPTION
WITH READ ONLY
: memastikan tidak ada perintah DML yang dapat digunakan pada view.
Aturan penggunaan perintah DML pada VIEWS
Pada simple views perintah DML bisa digunakan setiap saat Tidak bisa menghapus baris jika sebuah views be risi : Group functions o Klausa group by o
o o
Keyword Distinct Keyword rownum
Tidak bisa mengubah data jika sebuah views berisi : o Group functions Klausa group by o Keyword Distinct o o Keyword rownum o Nama kolom yang berupa ekspresi (ex: salary * 12) Tidak bisa menambah data jika sebuah views berisi : Group functions o Klausa group by o o Keyword Distinct Keyword rownum o o Nama kolom yang berupa ekspresi (ex: salary * 12) o Not null kolom dalam base tabel tidak dikutkan dalam views
SEQUENCES
Secara otomatis membentuk angka unik
Dapat digunakan sebagai primary key
Mempercepat pengaksesan.
Sintaks Perintah membuat Sequences
Sequence
: nama dari sequence generator
INCREMENT BY n
: nilai interval antar bilangan dimana n berupa bilangan bulat, jika tidak disebutkan berarti intervalnya 1
START WITH n
: menentukan nilai awal dari sequence, jika tidak disebutkan maka nilai awalnya 1
MAXVALUE n
: menentukan nilai paling maksimal dari sequnece
MINVALUE n
: menentukan nilai paling minimal.
INDEX Indeks adalah database objek yang dapat digunakan untuk meningkatkan performa dari suatu queri. Indeks dapat terbentuk secara otomatis pada saat membuat primary key atau unik constraint. Ketika menghapus sebuah tabel maka semua indeks yang pernah dibuat akan ikut terhapus, tapi views dan sequence masih tetap ada. Cara membuat indeks :
Otomatis : unique indeks terbentuk secara otomatis ketika menentukan primary key atau unique constraint pada suatu tabel. Manual : dapat membuat nonunique indeks terhadap kolom (field) untuk meningkatkan kecepatan akses terhadap baris (record).
Sintaks Perintah membuat Indeks
Indeks tidak bisa diubah, untuk mengubah indeks maka harus menghapus terlebih dahulu baru kemudian membuat ulang. Sintaks Perintah menghapus Indeks
Untuk menghapus indeks harus owner dari indeks tersebut atau user mempunyai hak akses atau privilege DROP ANY INDEX. SYNONYMS Synonyms adalah database objek yang dapat digunakan untuk memanggil suatu tabel menggunakan nama lain. Membuat synonyms untuk memberi alternative nama terhadap sebuah tabel. Sintaks Perintah membuat Synonyms
V.
Bahan Pratikum
1.
Perintah untuk membuat views yang menampilkan data employees yang mempunyai department_id = 80 saja.
2.
Perintah untuk melihat struktur views
3.
Perintah membuat views dengan menggunakan nama alias.
4.
Perintah untuk menampilkan data dari Views
5.
Perintah untuk mengubah views yang sudah pernah dibuat.
6.
Perintah untuk
membuat kompleks views yang berisi group functions untuk
menampilkan data dari dua tabel.
7.
Hasil view diatas dengan menggunakan perintah select * from dept_sum_vu;
8.
Perintah untuk membatasi pengubahan data dengan menggunakan WITH CHECK OPTION CONSTRAINT.
9.
Perintah untuk testing view diatas.
10. Perintah untuk memastikan tidak bisa menggunakan perintah DML dalam suatu views
11. Perintah untuk menghapus views
12. Buat views dengan menggunakan nama EMPLOYEES_VU dengan menampilkan employee_id, employees name, dan department number, dari tabel employees, dan beri nama alias EMPLOYEE untuk emp;loyees name. 13. Pastikan views diatas menampilkan keluaran seperti dibawah ini.
14. Menggunakan EMPLOYEES_VU views, tuliskan perintah untuk menampilkan employees names dan department number.
15. Department 50 membutuhkan hak akses terhadap data employees. Buat sebuah view dengan nama DEPT50 yang berisi employee number, last names, dan department numbers untuk semua pegawai yang mempunyai department 50, dan beri nama label untuk kolom view EMPNO, EMPLOYEE, dan DEPTNO. Untuk tujuan keamanan, tidak diperbolehkan mengubah data employee menjadi departemen lain. 16. Tampilkan struktur dari view DEPT50
17. Tampilkan data dari view DEPT50
18. Test view yang sudah dibuat, untuk mencoba mengganti employee Matos ke department 80. 19. Buat sequence dengan nama DEPT_DEPTID_SEQ untuk dijadikan sebagai primary key pada tabel departments, tidak menggunakan CYCLE option.
20. Tambahkan record baru pada tabel departments dengan nama “Support” dan location ID 2500.
21. Tampilkan nilai current dari sequence DEPT_DEPTDID_SEQ
22. Ubah increment value, maximum value, minimum valaue, cycle option atau cache option dari sequence dept_deptid_seq.
23. Perintah untuk menghapus sequence dept_deptid_seq.
24. Buat sebuah indeks untuk meningkatkan kecepatan akses queri terhadap kolom last_name pada tabel employees.
25. Hapus indek UPPER_LAST_NAME dari data dictionary
26. Perintah untuk memperpendek nama view DEPT_SUM_VU menjadi D_SUM
27. Perintah untuk menghapus Synonym D_SUM
VI.
Daftar Pustaka
Creating Other Schema Objects, Oracle Database 10g. SQL Fundamentals I (D17108GC21),Edition 2.1, December 2006.
Bab X Pratikum Membuat Program Sederhana Menggunakan PL/SQL.
I.
Tujuan Pratikum
Agar mahasiswa dapat
mengerti kegunaan dan manfaat pengunaan PL/SQL,
mempelajari jenis-jenis blok PL/SQL, bisa menggunakan iSQL*Plus editor, dan dapat membuat program sederhana untuk menampilkan data keluaran. II.
Bahan & Alat
Hardware berupa PC/laptop, Software Oracle Database 10g, Browser Mozilla Firefox / Internet Explorer dan modul pratikum. III.
Metode yang digunakan
Untuk melaksanakan pratikum ini, anda harus sudah menginstall oracle database 10g, membuka lock user HR, mengerti tentang class diagram Human Resources, melakukan koneksi ke database oracle menggunakan iSQLPlus.
IV.
Landasan Teori PL/SQL : Singkatan dari Procedural Language to SQL
Merupakan bahasa standar akses data untuk relasi database.
Lingkungan Kerja PL/SQL
Struktur blok PL/SQL
Terdiri atas : DECLARE (optional) Bersifat pilihan jika terdapat variabel, cursor, user-defined exceptions o pada blok program yang dibuat maka harus di deklarasikan terlebih dahulu pada bagian ini. BEGIN (mandatory) Terdiri atas perintah-perintah SQL o Dan perintah-perintah PL/SQL yang di kombinasi sesuai dengan logika o pemrograman.
Bersifat keharusan dalam pembuatan blok PL/SQL EXCEPTION (optional) Mengalihkan tindakan jika terjadi terjadi error yang berhubungan dengan data, o dan bersifat pilihan. END; (mandatory) Untuk mengakhiri blok PL/SQL menggunakan perintah END; dan bersifat o keharusan. o
Tipe Blok PL/SQL
Terdiri ada 3 tipe : Tipe Anonymous Tipe anonymous adalah blok PL/SQL yang tidak mempunyai nama spesifik, dibuat untuk mengerjakan tugas tertentu. Umumnya jika terdapat perintah DECLARE maka blok tersebut pasti anonymous. Tipe Procedure Disebut dengan sub program dan mempunyai nama yang spesifik, dan dibuat untuk mengerjakan tugas tertentu yang bisa dipanggil dari blok PL/SQL yang lain. Tipe Function Disebut dengan sub program dan mempunyai nama yang spesifik, dan dibuat untuk mengerjakan tugahs tertentu dan harus ada nilai yang dihasilkan dan bisa dipanggil dari blok PL/SQL yang lain.
Untuk bisa menampilkan keluaran pada iSQL*Plus harus mengaktifkan
SET SERVEROUTPUT ON
Untuk menampilkan data kelayar menggunakan perintah
DBMS_OUTPUT.PUT_LINE
Variabel digunakan untuk :
Menyimpan nilai semantara Mengolah nilai yang akan disimpan Reusability Deklarasi dan pemberian nilai awal pada bagian DECLARATION SECTION Menggunakan dan memberi nilai baru pada bagian EXECUTABLE SECTION Bisa digunakan untuk melewatkan parameter ke subprogram Digunakan untuk menyimpan keluaran yang didapat dari subprogram.
IDENTIFIERS Identifiers digunakan untuk :
penamaan suatu variabel ketentuan penamaan variabel : harus dimulai dengan huruf o o bisa mengandung huruf dan angka o bisa mengandung spesial karakter seperti $, _, #
o o
maksimal 30 karakter dan bukan termasuk reserved words.
Sintaks Deklarasi Variabel
VARIABEL SUBTITUSI
digunakan untuk menerima masukan dari user pada saat run time. Digunakan dalam blok PL/SQL diawali dengan karakter & Digunakan untuk menghindari pembuatan program yang rumit.
Kode Komentar
Karakter – untuk untuk memberi komentar per bari Karakter /* and */ untuk memberi komentar per blok
Blok Bersarang (Nested)
Executable section (BEGIN .... END) bisa mengandung nested blocks Exception section juga bisa mengandung nested block
V.
Bahan Pratikum
1.
Cara mengakses iSQL*Plus Ketik pada browser : http://192.168.8.1:5560/isqlplus : http://192.168.8.1:5560/isqlplus *Username
: hr
*Password
: hr
Connect Identifier
: orcl (jika ada)
*harus diisi
Maka akan tampil workspace seperti gambar dibawah
2.
Perintah sederhana untuk mengambil data employee id 100 dari tabel employee menggunakan PL/SQL.
3.
Untuk menjalankan blok PL/SQL diatas, klik pada tombol Execute
4.
Buat PL/SQL untuk menampilkan nama First_name dari employees yang mempunyai employee_id 100 ke layar dengan keluaran “The First Name of o f the Employee is Steven”
5.
Perintah untuk mendeklarasikan variabel dalam PL/SQL
6.
Buat program untuk menampilkan proses deklarasi variabel dan menggunakan variabel tersebut didalam program.
7.
Buat program program untuk menampilkan proses deklarasi deklarasi variabel dan pemberian nilai awal dan mengubah nilai variabel tersebut didalam program.
8.
Menampilkan data berupa karakter kutip tunggal menggunakan PL/SQL.
9.
Program sederhana menggunakan 1 variabel subtitusi.
10. PL/SQL untuk mengganti nama prompt pada saat menggunakan variabel subtitusi.
11. Contoh penulisan komentar pada blok PL/SQL
12. Perintah penggunaan nested blok pada PL/SQL
13. Perintah penggunaan nested blok pada PL/SQL menggunakan nama variabel yang sama.
VI.
Daftar Pustaka
Introduction
to
PL/SQL,
Oracle
Database
10g.
PL/SQL
Fundamentals
(D17112GC21),Edition 2.1, December 2006. Declaring
PL/SQL
Variables,
Oracle
Database
10g.
PL/SQL
Fundamentals
10g.
PL/SQL
Fundamentals
(D17112GC21),Edition 2.1, December 2006. Writing
Executable
Statements,
Oracle
(D17112GC21),Edition 2.1, December 2006.
Database
Bab XI Pratikum Membuat Program Program untuk Mengakses Oracle Database 10g, Dengan Struktur Kondisi dan Struktur Perulangan menggunakan PL/SQL.
I.
Tujuan Pratikum
Agar mahasiswa dapat mengakses data dari dari oracle database database server menggunakan blok PL/SQL, memanipulasi data menggunakan DML pada blok PL/SQL, dapat memahami penggunaan struktur kondisi dan struktur perulangan dalam blok PL/SQL. II.
Bahan & Alat
Hardware berupa PC/laptop, Software Oracle Database 10g, Browser Mozilla Firefox / Internet Explorer dan modul pratikum. III.
Metode yang digunakan
Untuk melaksanakan pratikum ini, anda harus sudah menginstall oracle database 10g, membuka lock user HR, mengerti tentang class diagram Human Resources, melakukan koneksi ke database oracle menggunakan iSQLPlus.
IV.
Landasan Teori Perintah SQL dalam PL/SQL
Menampilkan data dari database menggunakan perintah SELECT Melakukan perubahan terhadap data dalam database dengan menggunakan perintah-perintah Data Manipulation Language (DML). Kontrol transaksi SAVEPOINT.l
menggunakan
perintah
Sintaks Perintah Select dalam PL/SQL
Membutuhkan klausa INTO
Queri hanya boleh menghasilkan satu baris
COMMIT,
ROLLBACK
dan
Manipulasi Data menggunakan PL/SQL
INSERT
UPDATE
DELETE
MERGE
SQL Cursor
Cursor adalah pointer yang mempunyai area penyimpanan pribadi yang disediakan oleh Oracle Server. Ada dua tipe cursor : Implicit : dibuat dan dikelola secara internal oleh oracle server untuk o memproses perintah-perintah SQL o Explicit : dibuat dan dikelola secara langsung oleh programmer yang membutuhkan.
SQL Cursor Attribut untuk Implicit Cursors
Controlling Flow od Execution
Sintaks Perintah IF
Sintaks perintah CASE
Perbandingan Boolean
Perintah Loop
Loops akan mengerjakan sekumpulan perintah secara berulang-ulang Ada tiga tipe loop : o Basic Loop Sintaks :
o
o
V.
FOR Loop Sintaks :
WHILE Loop Sintaks :
Bahan Pratikum
1.
Blok PL/SQL untuk menampilkan first_name yang diambil dari tabel employees untuk employee_id 200
2.
Blok PL/SQL untuk menampilkan hire_date dan salary employees tertentu, sebagai contoh employee_id 100
3.
4.
Berdasarkan soal diatas jelaskan fungsi dari : a.
Employees.hire_date%type;
b.
Employees.salary%type;
c.
Apakah blok PL/SQL diatas menghasilkan keluaran?
Blok PL/SQL untuk menampilkan jumlah keseluruhan gaji pegawai yang terdapat dalam department tertentu.
5.
6.
Berdasarkan soal diatas jelaskan fungsi dari : a.
Sum_sal NUMBER(10,2);
b.
Deptno NUMBER NOT NULL := 60;
c.
- - group function
d.
Tuliskan keluaran dari blok PL/SQL diatas.
Blok PL/SQL untuk menambahkan data employee baru kedalam tabel employees sebagai berikut :
7.
Blok PL/SQL untuk mengubah data gaji employees yang mempunyai job_id stock clerks (ST_CLERK)
8.
Blok PL/SQL untuk menghapus semua employee yang mempunai department_id 10 pada tabel employees :
9.
Blok PL/SQL untuk menggabungkan baris kedalam tabel copy_emp, jika belum ada maka disimpan dan jika sudah ada maka akan di update.
10. Blok PL/SQL untuk menghapus data tertrentu dari tabel employees dan menampilkan jumlah record yang berhasil dihapus ke layar.
11. Blok PL/SQL menggunakan perintah IF sederhana
12. Blok PL/SQL menggunakan perintah IF then Else
13. Blok PL/SQL menggunakan perintah IF then ElsIf
14. Blok PL/SQL yang berisi perbandingan nilai null, perhatikan keluaran yang dihasilkan.
15. Blok PL/SQL menggunakan perbandingan CASE tanpa variabel
16. Blok PL/SQL menggunakan perbandingan CASE dengan variabel
17. Blok PL/SQL menggunakan perbandingan CASE melibatkan queri
18. Contoh Blok PL/SQL menggunakan Basic Loop
19. Contoh Blok PL/SQL menggunakan FOR Loop
20. Contoh Blok PL/SQL menggunakan WHILE Loop
VI.
Daftar Pustaka
Interacting with the Oracle Server, Oracle Database 10g. PL/SQL Fundamentals (D17112GC21),Edition 2.1, December 2006. Writing
Control
Structures
Oracle
Database
(D17112GC21),Edition 2.1, December 2006.
10g.
PL/SQL
Fundamentals
Bab XII Pratikum Membuat Program Menggunakan Explicit Cursor
I.
Tujuan Pratikum
Agar mahasiswa mahasiswa dapat mengerti penggunaan composite data types, bisa membedakan implicit dan explicit cursor, menggunakan perulangan dalam cursor, dan menggunakan parameter dalam cursor. II.
Bahan & Alat
Hardware berupa PC/laptop, Software Oracle Database 10g, Browser Mozilla Firefox / Internet Explorer dan modul pratikum. III.
Metode yang digunakan
Untuk melaksanakan pratikum ini, anda harus sudah menginstall oracle database 10g, membuka lock user HR, mengerti tentang class diagram Human Resources, melakukan koneksi ke database oracle menggunakan iSQLPlus.
IV.
Landasan Teori Tipe Data Composite
Menyimpan nilai dengan tipe data berbeda
Terdiri atas dua tipe : PL/SQL Records dan PL/SQL Collections
Sintaks :
Deklarasi Field :
%Rowtype Attribute
Deklarasi variable berdasarkan sekumpulan kolom yang ada di dalam database tabel atau view Menggunakan prefix %ROWTYPE Nama dan tipe data field dalam record akan mengambil nama dan tipe data dari kolom yang ada dalam tabel database tabel atau view.
Sintaks :
Cursors
Perbedaan Implicit cursors dan Explicit Cursors :
Implicit Cursors : deklarasi dan pengaturan dibuat oleh PL/SQL untuk semua perintah DML dan perintah select. Explicit Cursors : deklarasi dan pengaturan dibuat oleh programmer.
Explicit Cursor Operation :
Explicit Cursors Kontrol
Sintaks Deklarasi Cursor :
Contoh Deklarasi Cursor :
Loop For dalam Cursor Sintaks :
Perulangan FOR merupakan shortcut untuk proses p roses explicit cursor Dengan perulangan cursor secara otomatis dibuka (open), diambil datanya(fetch), dan ditutup (close) Record secara otomatis terbentuk (declared).
Explicit Cursor Attribut
Cursor dengan Parameter Sintaks :
V.
Bahan Pratikum
1.
Membuat blok PL/SQL untuk deklarasi varibel yang bisa menyimpan nama, job dan gaji dari pegawai baru.
2.
Membuat blok PL/SQL untuk memasukkan data pegawai 124 dari tabel employees ke tabel retired_emps dengan menggunakan %rowtype attribute.
3.
Membuat blok PL/SQL untuk memasukkan data pegawai 124 dari tabel employees ke tabel retired_emps dengan menggunakan %rowtype attribute tanpa menyebutkan nama fieldnya.
4.
Membuat blok PL/SQL untuk mengubah record data pegawai 124 pada tabel retired_emps dengan menggunakan %rowtype attribute tanpa menyebutkan nama fieldnya.
5.
Tahap-tahapan
pembuatan
blok
PL/SQL
yang
menggunakan
menampung data selama program di eksekusi.
Tahap membuka cursor
Tahap mengambil data dari cursor tanpa perulangan
cursor
untuk
Tahap mengambil data dari cursor dengan perulangan
Tahap menutup cursor
6.
Blok PL/SQL menggunakan variable record (%rowtype) untuk menyederhakan penulisan program.
7.
Membuat blok PL/SQL yang berisi cursor untuk menyimpan data dan pengolahan data menggunakan perulangan FOR.
8.
Membuat blok PL/SQL yang berisi cursor untuk menyimpan data dan pengolahan data dengan perulangan FOR menggunakan subqueri.
9.
Blok PL/SQL menggunakan attribute %ISOPEN, untuk cek apakah cursor sudah terbuka atau belum.
10. Blok PL/SQL menggunakan attribute %ROWCOUNT, dan %NOTFOUND
11. Blok PL/SQL yang berisi cursor menggunakan parameter.
12. Blok PL/SQL yang berisi cursor menggunakan subqueri.
VI.
Daftar Pustaka
Working with Composite Data types, Oracle Database 10g. PL/SQL Fundamentals (D17112GC21), Edition 2.1, December 2006. Using Explicit Cursors. PL/SQL Fundamentals (D17112GC21), Edition 2.1, December 2006.
Bab XIII Pratikum Membuat Stored Procedured. I.
Tujuan Pratikum
Agar mahasiswa dapat mengetahui perbedaan blok aninomus dan subprogram, dapat mengetahui perbedaan antara prosedur dan fungsi,
mengerti penggunaan stored stored
procedure, dapat memanggil stored prosedur dari blok PL/SQL yang lain. II.
Bahan & Alat
Hardware berupa PC/laptop, Software Oracle Database 10g, Browser Mozilla Firefox / Internet Explorer dan modul pratikum. III.
Metode yang digunakan
Untuk melaksanakan pratikum ini, anda harus sudah menginstall oracle database 10g, membuka lock user HR, mengerti tentang class diagram Human Resources, melakukan koneksi ke database oracle menggunakan iSQLPlus.
IV.
Landasan Teori Procedure dan Function
Merupakan nama dari blok PL/SQL
Disebut juga subprogram PL/SQL
Struktur blok sama dengan aninomus blok : Declarative Section bersifat opsional o Executable Section bersifat keharusan o (BEGIN …… END;) o Handle Exception bersifat opstional. Perbedaan antara blok aninomus dengan subprogram :
Sintaks membuat Stored Procedure :
V.
Bahan Pratikum
1.
Tuliskan blok PL/SQL untuk membuat stored procedure dengan nama tampil_data, yang dapat digunakan untuk menampilkan keluaran sebagai berikut :
2.
Buat blok PL/SQL untuk memanggil procedure tampil_data
3.
Ubah procedure tampil_data diatas menjadi keluaran sebagai berikut :
4.
Tuliskan blok PL/SQL untuk membuat stored procedure dengan nama data_pegawai, yang dapat digunakan untuk menampilkan nama lengkap dan gaji dari pegawai 100.
5.
Buat blok PL/SQL untuk memanggil procedure tampil_data dan data_pegawai secara bersamaan.
6.
Tuliskan blok PL/SQL untuk membuat stored procedure dengan nama cari_min, yang dapat digunakan untuk menampilkan gaji terkecil keseluruhan pegawai.
7.
Tuliskan blok PL/SQL untuk membuat stored procedure dengan nama cari_max, yang dapat digunakan untuk menampilkan gaji terbesar keseluruhan pegawai.
8.
Tuliskan blok PL/SQL untuk membuat stored procedure dengan nama cari_rata, yang dapat digunakan untuk menampilkan gaji rata-rata keseluruhan pegawai.
9.
Buat blok PL/SQL untuk memanggil procedure cari_min, cari_max dan cari_rata secara bersamaan.
10. Tuliskan blok PL/SQL untuk membuat stored procedure dengan nama cari_staff, yang dapat digunakan untuk menampilkan jumlah staff berdasarkan jumlah staff dari terbesar ke terkecil.
per Manager ID urut
11. Tuliskan PL/SQL untuk menampilkan keluaran procedu re cari_staff diatas.
12. Ubah procedure cari_staff untuk menampilkan nama manager beserta dengan jumlah staffnya.
13. Tuliskan PL/SQL untuk menampilkan keluaran cari_staff hasil modifikasi procedure diatas.
14. Tuliskan perintah untuk mencopy
tabel departments menjadi tabel dept
menggunakan perintah SQL, pastikan tabel yang anda buat unik dan tidak sama dengan mahasiswa lain. (dept_nim_anda)
15. Tuliskan perintah SQL untuk menampilkan isi tabel dept hasil copy dari tabel departments.
16. Tuliskan blok PL/SQL untuk membuat stored procedure dengan nama add_dept, yang dapat digunakan untuk menambahkan record baru ke dalam tabel dept dengan dept_id 280 dan dept_name ST-Curriculum, dan tampilkan jumlah record yang berhasil terinsert, dengan keluaran sebagai berikut.
Inserted 1 row
\ 17. Buat blok PL/SQL sederhana untuk memanggil procedure yang sudah pernah dibuat dan aktifkan set serveroutput on, kemudian tuliskan perintah select untuk menampilkan isi dari tabel dept yang terbaru.
VI.
Daftar Pustaka
Creating Stored Procedure and Functions, Oracle Database 10g. PL/SQL Fundamentals (D17112GC21), Edition 2.1, December 2006.
Bab XIV Pratikum Membuat Stored Function dengan Parameter
I.
Tujuan Pratikum
Agar mahasiswa dapat mengetahui perbedaan blok aninomus dan subprogram, dapat mengetahui perbedaan antara prosedur dan fungsi, mengerti penggunaan stored function, membuat function dengan parameter, dapat memanggil stored function dari blok PL/SQL yang lain maupun dari perintah Select. II.
Bahan & Alat
Hardware berupa PC/laptop, Software Oracle Database 10g, Browser Mozilla Firefox / Internet Explorer dan modul pratikum. III.
Metode yang digunakan
Untuk melaksanakan pratikum ini, anda harus sudah menginstall oracle database 10g, membuka lock user HR, mengerti tentang class diagram Human Resources, melakukan koneksi ke database oracle menggunakan iSQLPlus.
IV.
Landasan Teori Procedure dan Function
Merupakan nama dari blok PL/SQL
Disebut juga subprogram PL/SQL
Struktur blok sama dengan aninomus blok : Declarative Section bersifat opsional o Executable Section bersifat keharusan o (BEGIN …… END;) Handle Exception bersifat opstional. o Perbedaan antara blok aninomus dengan subprogram :
Sintaks membuat Stored Function :
V.
Bahan Pratikum
1.
Tuliskan blok PL/SQL untuk membuat function dengan nama tanggal_sekarang, yang dapat digunakan untuk menampilkan tanggal hari ini dengan format nama Hari, tanggal, nama bulan dan 4 digit tahun.
Buat function untuk menampilkan tanggal sekarang dengan format default menggunakan fungsi SYSDATE, seperti dibawah ini.
Keluaran sebagai berikut :
Modifikasi function diatas untuk mengubah format default (DD-MON-YY) menjadi format Day, DD Month YYYY.
2.
Sehingga Keluaran menjadi seperti dibawah ini :
Tuliskan blok PL/SQL untuk menampilkan nama pegawai dari pegawai 200 dengan menggunakan fungsi.
Tentukan nama fungsi dan tipe data output dari fungsi tersebut. Nama fungsi
: nama_pegawai
Tipe data output
: varchar
Ketik perintah untuk membuat fungsi
Deklarasikan variable yang dibutuhkan (jika ada)
Pada program utama (executable section) buat PL/SQL untuk mencari nama lengkap dari employee 200.
Tambahkan return value
3.
Tuliskan blok PL/SQL untuk menampilkan hasil function diatas.
4.
Tuliskan blok PL/SQL untuk menampilkan nama pegawai menggunakan fungsi dengan parameter NIP.
5.
Tuliskan perintah SQL untuk menampilkan keluaran fungsi nama_pegawai2 yang terbentuk diatas dengan mengirimkan nilai parameter 190.
6.
Tuliskan perintah perintah SQL untuk menampilkan keluaran fungsi nama_pegawai2 yang terbentuk diatas dengan mengirimkan nilai parameter Employee_Id.
7.
Tuliskan blok PL/SQL untuk menampilkan nama departemen, nama kota dan nama negara dengan membuat 3 fungsi yang sesuai beserta dengan parameter yang dibutuhkan.
Membuat fungsi nama_dept
8.
Membuat fungsi nama_kota
Membuat fungsi nama_negara
Tuliskan blok PL/SQL untuk menampilkan nama pegawai, nama departemen, nama kota dan nama Negara dengan memanfaatkan fungsi-fungsi diatas.
Dengan keluaran sebagai berikut :
9.
Tuliskan blok PL/SQL untuk membandingkan gaji pegawai 205 dengan gaji rata-rata pegawai yang satu departemen dengannyadengan nama fungsi check_sal dan keluar berupa TRUE dan FALSE.
10. Buat blok PL/SQL dengan menggunakan fungsi check_sal untuk mengetahui status gaji pegawai 205.
11. Perintah untuk menghapus function tanggal_sekarang
12. Peritah untuk melihat error karena fungsi tidak ditemukan
VI.
Daftar Pustaka
Creating Stored Procedure and Functions, Oracle Database 10g. PL/SQL Fundamentals (D17112GC21), Edition 2.1, December 2006.
