modul oracle introduction sql dan plsql

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

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 : <dikosongkan>

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 atau 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 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



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.

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 tidak sama dengan spasi kosong.

Nilai null akan mengakibatkan error jika tidak dikelola 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 kalkulasi

Penulisan nama kolom alias langsung dituliskan setelah nama kolom, atau bisa

juga dengan 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 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


pada saat pembatasan keluaran (WHERE) untuk tipe data karakter atau date.


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 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 date dalam perintah SQL, dan mengetahui

berbagai jenis fungsi untuk konversi antar tipe data.

II. Bahan & Alat




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 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 pengurangan tanggal, pengurangan 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 :

Menggunakan fungsi TO_DATE

Sintaks penulisan konversi karakter menjadi date :

V. 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 baru dengan salary 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 :

<employee last name> earns <salary> 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


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





penggunaan perintah select, from, where dan order by.

IV. Landasan Teori

RR date format sama seperti YY date format, tapi bisa digunakan untuk menampilkan

tahun pada abad (century) yang berbeda. Seperti 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-97’)

NVL (job_id, ‘No Job Yet’)

NVL2 (expr1, expr2, 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 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’ 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 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


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





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 gaji employees dan dikelompokkan berdasarkan

department_id dan job_id.

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


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 outer join.

II. Bahan & Alat

Hardware berupa PC/laptop, Software Oracle Database 10g, dan modul pratikum.






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 tersebut.

Jenis-jenis JOINS tabel berdasarkan 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

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 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.

V. Bahan Pratikum

1. Menampilkan data department id, department name, location id dan city dari tabel

departments dan tabel locations 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.


keseluruhan employees dengan menggunakan klausa ON

6. Menampilkan last_name employee, last_name manager untuk keseluruhan

employees menggunakan klausa ON.


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.


departments yang mempunyai employees maupun departments yang tidak

mempunyai employees.


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 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


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


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


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







single row functions, group functions, dan mampu menampilkan data dari banyak

tabel

IV. LandasanTeori

Employee mana yang mempunyai salary lebih besar dari pada salary 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, ANY, ALL)

Subqueri harus diapit dengan tanda buka dan tutup 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 Oracle server akan eksekusi subqueri pertama kali

o 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 141.

3. Menampilkan last_name, job_id, dan salary untuk semua pegawai dengan job_id

sama seperti job_id pegawai nomor 141, dan gaji harus lebih besar dari 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


Bab VIII

Pratikum 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







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 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


Using DDL Statements to Create and Manage Tables, Oracle Database 10g. SQL


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




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 berisi :

o Group functions

o Klausa group by

o Keyword Distinct

o Keyword rownum

Tidak bisa mengubah data jika sebuah views berisi :

o Group functions

o Klausa group by

o Keyword Distinct

o Keyword rownum

o Nama kolom yang berupa ekspresi (ex: salary * 12)

Tidak bisa menambah data jika sebuah views berisi :

o Group functions

o Klausa group by

o Keyword Distinct

o Keyword rownum

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


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.





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)

o Bersifat pilihan jika terdapat variabel, cursor, user-defined exceptions

pada blok program yang dibuat maka harus di deklarasikan terlebih dahulu

pada bagian ini.

BEGIN (mandatory)

o Terdiri atas perintah-perintah SQL

o Dan perintah-perintah PL/SQL yang di kombinasi sesuai dengan logika

pemrograman.

o Bersifat keharusan dalam pembuatan blok PL/SQL

EXCEPTION (optional)

o Mengalihkan tindakan jika terjadi error yang berhubungan dengan data,

dan bersifat pilihan.

END; (mandatory)

o Untuk mengakhiri blok PL/SQL menggunakan perintah END; dan bersifat

keharusan.

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 :

o harus dimulai dengan huruf

o bisa mengandung huruf dan angka

o bisa mengandung spesial karakter seperti $, _, #

o maksimal 30 karakter

o 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 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

*Username : hr

*Password : hr

Connect Identifier : orcl (jika ada)

*harus diisi

Maka akan tampil workspace seperti gambar dibawah

http://192.168.8.1:5560/isqlplus

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 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 untuk menampilkan proses 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


Declaring PL/SQL Variables, Oracle Database 10g. PL/SQL Fundamentals


Writing Executable Statements, Oracle Database 10g. PL/SQL Fundamentals


Bab XI

Pratikum Membuat Program untuk Mengakses Oracle Database 10g, Dengan Struktur

Kondisi dan Struktur Perulangan menggunakan PL/SQL.

I. Tujuan Pratikum

Agar mahasiswa dapat mengakses data dari oracle 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







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 menggunakan perintah COMMIT, ROLLBACK dan

SAVEPOINT.l

Sintaks Perintah Select dalam PL/SQL

Membutuhkan klausa INTO

Queri hanya boleh menghasilkan satu baris

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 :

o Implicit : dibuat dan dikelola secara internal oleh oracle server untuk

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 FOR Loop

Sintaks :

o WHILE Loop

Sintaks :

V. 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. Berdasarkan soal diatas jelaskan fungsi dari :

a. Employees.hire_date%type;

b. Employees.salary%type;

c. Apakah blok PL/SQL diatas menghasilkan keluaran?

4. Blok PL/SQL untuk menampilkan jumlah keseluruhan gaji pegawai yang terdapat

dalam department tertentu.

5. 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.

6. 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


Writing Control Structures Oracle Database 10g. PL/SQL Fundamentals


Bab XII

Pratikum Membuat Program Menggunakan Explicit Cursor

I. Tujuan Pratikum

Agar 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







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 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 cursor untuk

menampung data selama program di eksekusi.

Tahap membuka cursor

Tahap mengambil data dari cursor tanpa perulangan

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

procedure, dapat memanggil stored prosedur dari blok PL/SQL yang lain.

II. Bahan & Alat







IV. Landasan Teori

Procedure dan Function

Merupakan nama dari blok PL/SQL

Disebut juga subprogram PL/SQL

Struktur blok sama dengan aninomus blok :

o Declarative Section bersifat opsional

o Executable Section bersifat keharusan

(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 per Manager ID urut

berdasarkan jumlah staff dari terbesar ke terkecil.

11. Tuliskan PL/SQL untuk menampilkan keluaran procedure 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


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







IV. Landasan Teori

Procedure dan Function

Merupakan nama dari blok PL/SQL

Disebut juga subprogram PL/SQL

Struktur blok sama dengan aninomus blok :

o Declarative Section bersifat opsional

o Executable Section bersifat keharusan

(BEGIN …… END;)

o Handle Exception bersifat opstional.

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.

Sehingga Keluaran menjadi seperti dibawah ini :

2. 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 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

Membuat fungsi nama_kota

Membuat fungsi nama_negara

8. 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


modul oracle introduction sql dan plsql

Documents