Belajar SQL

A.     Fungsi-Fungsi SQL (lanjutan)
Ada dua tipe dari fungsifungsi
1.      SingleRow Functions
Fungsi-fungsi ini hanya digunakan pada baris-baris
tunggal dan mengembalikan satu hasil per baris.
Ada tipe-tipe berbeda dari singlerow functions. Pelajaran ini mencakup hal-hal berikut :

• Character (karakter) : Menerima input karakter dan dapat mengembalikan baik nilai-nilai karakter ataupun angka.
• Number (angka) : Menerima masukkan angka dan mengembalikan nilai-nilai angka
• Date (tanggal) : Bekerja pada nilai-nilai dari tipe data DATE (semua date functions mengembalikan suatu nilai dari tipe data DATE kecuali fungsi MONTHS_BETWEEN, yang mengembalikan suatu angka.)
• Conversion (konversi) : Mengkonversi suatu nilai dari satu tipe data ke tipe data lainnya.
• General (umum) : (fungsi-fungsi umum) :
• NVL
• NVL
• 2
• NULLIF
• COALESCE
• CASE
• DECODE
• Single Rows Functions

Singlerow function digunakan untuk memanipulasi itemi-tem data. Singlerow functions menerima satu atau lebih argumenargumen dan mengembalikan satu nilai untuk setiap baris yang dihasilkan oleh suatu query. Suatu argumen dapat berupa berikut ini :

• Usersupplied
• constant (Konstanta yang disediakan oleh user)
• Nilai variable
• Nama Kolom
• Ekspresi

Fitur-fitur dari singlerow functions mencakup :

• Aksi pada setiap baris yang dikembalikan di dalam query
• Mengembalikan satu hasil per baris
• Memungkinkan pengembalian suatu nilai data dari suatu tipe berbeda daripada satu yang direferensikan.
• Mungkin menerima satu atau lebih argumentargumen
• Dapat digunakan didalam klausaklausa

SELECT, WHERE, dan ORDER BY;dapat disarangkan (nested)

2.      Multiplerow Functions
Fungsi-fungsi dapat memanipulasi kelompok dari baris-baris untuk memberi suatu hasil baris-baris per kelompok. Fungsi-fungsi ini dikenal juga sebagai group functions (dibahas pada pelajaran  selanjutnya).

• CaseManipulation Functions

• ·LOWER : Konversi mixedcase (bentuk campuran) atau karakterkarakter string huruf besar ke huruf kecil
• ·UPPER : Konversi mixedcase atau karakterkarakter string huruf kecil ke huruf besar
• ·INITCAP : Konversi huruf pertama setiap kata ke huruf besar dan hurufhuruf selanjunya ke huruf kecil

SELECT ‘the job id for ‘||UPPER(last_name)|| ’ is ‘||LOWER(job_id) AS “EMPLOYEE DETAILS”
FROM employees;

• Menggunakan Fungsi-Fungsi CaseManipulation

Klausa WHERE dapat ditulis ulang dengan cara berikut untuk menghasilkan hasil yang sama :
. . . WHERE last_name= ‘ Higgins’
Nama pada output muncul seperti nama itu disimpan di dalam database. Untuk menampilkan nama dimana hanya huruf pertama dalam bentuk huruf besar, gunakan fungsi UPPER pada pernyataan SELECT.
SELECT employee_id, UPPER (last_name), department_id
FROM employees
WHERE INITCAP(last_name) = ‘Higgins’;

• Fungsi-Fungsi CharacterManipulation

• ·CONCAT : Menggabungkan bersama nilai-nilai ( Anda dibatasi untuk menggunakan dua

parameter pada CONCAT.)

• ·SUBSTR : Memotong suatu rangkaian dari panjang tertentu

LENGTH : Menampilkan panjang dari suatu rangkaian sebagai suatu nilai numeric
INSTR : Menemukan posisi numerik dari suatu karakter nama
LPAD : Mengisi nilai karakter rightjustified (perataan kanan)
RPAD : Mengisi nilai karakter leftjustified(perataan kiri)
TRIM : Memotong karakter-karakter bagian awal atau bagian akhir (atau keduaduanya) dari suatu rangkaian karakter ( jika trim_character atau trim_source adalah suatu karakter literal, Anda harus mengapitnya didalam tanda petik tunggal.)

• Menggunakan Fungsi-Fungsi CharacterManipulation.

SELECT employee_id, CONCAT (first_name, last_name) NAME,
LENGTH (last_name), INSTR (last_name, ‘a’) “Contains ‘a’?”
FROM employees
WHERE SUBSTR (last_name, 1,1) = ‘n’;

• Fungsi ROUND

Fungsi ROUND membulatkan kolom, ekspresi, atau nilai posisi ken decimal dan dapat juga digunakan dengan date function (fungsi-fungsi tanggal).

• DUAL Table

DUAL table secara umum digunakan untuk melengkapi sintak klausa SELECT, sebab baik klausa SELECT dan klausa FROM mandatory (bersifat perintah) , dan beberapa perhitungan-perhitungan tidak perlu memilih dari
Table-tabel aktual.

• Fungsi TRUNC

Fungsi TRUNC memotong kolom, ekspresi, atau nilai posisi ken Decimal dan bekerja dengan argumentargumen yang sama untuk fungsi ROUND.

• Fungsi MOD

MOD function akan menemukan suatu sisa dari argumen pertama dibagi dengan argumen kedua.

• Format Tanggal Oracle

Database Oracle menyimpan tanggal-tanggal dalam suatu format angka sendiri, yang menunjukkan abad, tahun, bulan, hari, jam, menit, dan detik. Tampilan default dan format inputan untuk setiap tanggal adalah DDMONRR.

• Fungsi SYSDATE

SYSDATE adalah fungsi tanggal yang mengembalikan tanggal dan waktu server database saat ini.
Contoh
Menampilkan tanggal saat ini menggunakan tabel DUAL.
SELECT SYSDATE
FROM DUAL;

• Dates Functions

• ·MONTHS_BETWEEN (date1, date2) : Mencari jumlah bulan diantara date1 dan date2. Hasilnya bisa jadi positif atau negatif. Jika date1 lebih awal daripada date2, hasilnya adalah positif ; jika date1 lebih awal daripada date2, hasilnya adalah negatif . Sebagian dari hasil bukan bilangan bulat (noninteger) menunjukkan suatu bagian dari bulan.

ADD_MONTHS (date, n) : Menambahkan n jumlah suatu bulan kalender ke date. Nilai dari n harus bilangan bulat (integer) dan bisa negatif.
NEXT_DATE (date, ’char’) : (‘char’) setelah date menemukan suatu tanggal dari suatu hari tertentu pada suatu minggu. Nilai dari char bisa angka yang mewakili suatu hari atau suatu karakter string.
LAST_DAY (date) : Mencari hari terakhir dari suatu tanggal dalam suatu bulan yang berisi date.
ROUND (date[,’fmt’] ) : Mengembalikan pembulatan date ke suatu unit yang ditentukan oleh model format fmt. Jika model format fmt dihilangkan, date dibulatkan ke hari terdekat.
TRUNC (date[,’fmt’] ) : Mengembalikan date dengan bagian suatu waktu dari suatu hari yang dipotong ke unit yang ditentukan oleh model format fmt.

• Menggunakan Fungsi NVL

Untuk menghitung kompensasi tahunan dari semua pegawai, Anda membutuhkan perkalian antara
penghasilan bulanan dengan 12 kemudian menambah prosentase komisi kepada hasil :
SELECT last_name, salary, commission_pct,
(salary*12) + (salary*12*commission_pct) AN_SAL
FROM employees;

• Menggunakan Fungsi NVL2

Fungsi NVL2 memeriksa ekspresi pertama. Jika ekspresi pertama bukan null, maka fungsi NVL2
mengembalikan ekspresi kedua. Jika ekspresi pertama null, maka ekspresi ketiga yang akan
dikembalikan.
Sintak
NVL (expr1, expr2, expr3)
Dalam sintak :
·expr1 adalah nilai atau ekspresi asal yang mungkin berisi null.
·expr2 adalah nilai yang dikembalikan jika expr1 bukan null.
·expr3 adalah nilai yang dikembalikan jika expr1 adalah null.

• Penggunaan Fungsi NULLIF

Fungsi NULLIF membandingkan dua ekspresi. Jika sama, fungsi akan mengembalikan null. Jika tidak sama, fungsi akan mengembalikan ekspresi pertama. Anda tidak dapat menyebutkan literal NULL sebagai ekspresi pertama.
Sintak
NULLIF(expr1, expr2)
Dalam sintak :
·expr1 adalah nilai asal yang dibandingkan dengan expr2.
·expr2 adalah nilai asal yang dibandingkan dengan expr1 (Jika tidak sama dengan expr1,
maka expr1 yang dikembalikan).

• Menggunakan Fungsi COALESCE

Fungsi COALESCE mengembalikan ekspresi bukan null (nonnull) yang pertama yang terdapat dalam daftar.
Sintak
COALESCE (epr1, expr2,…exprn)
Dalam sintak:
expr1 mengembalikan ekspresi expr1 jika ia bukan null.

• ·expr2 mengembalikan ekspresi expr2 jika ekspresi pertama null dan ekspresi expr2 bukan null.
• ·exprn mengembalikan ekspresi exprn jika ekspresiekspresi sebelumnya null.

Semua ekspresi harus bertipe data sama.

• Ekspresi-Ekspresi Kondisional

Dua metode yang digunakan untuk implementasi pemrosesan kondisi (logika IF_THEN_ELSE)
dalam pernyataan SQL adalah ekspresi CASE dan fungsi DECODE.

• Ekspresi CASE

Ekspresi CASE mengijinkan Anda untuk menggunakan logika IFTHENELSE dalam pernyataan
SQL tanpa meminta prosedurprosedur. Dalam ekspresi CASE sederhana, server Oracle mencari pasangan WHEN . . . THEN pertama dimana expr adalah sama dengan comparison_expr dan mengembalikan return_expr.

• Fungsi DECODE

Fungsi DECODE mengkodekan suatu ekspresi dalam suatu cara yang sama dengan logika IFTHENELSE
yang digunakan dalam beragam bahasa. Fungsi DECODE mengkodekan expression setelah membandingkannya dengan setiap nilai search. Jika ekspresi sama dengan search, result akan dikembalikan. Jika nilai default