Fungsi-fungsi pada SQL
DUAL
DUAL adalah sebuah built-in tabel yang disediakan oleh Oracle untuk memproses ekspresi aritmatika, logika, tanggal dan lain sebagainya.
Contoh :
SQL > SELECT 10 * 12 – 2 FROM DUAL;
Menampilkan hasil operasi aritmatika 10 * 12 – 2
SQL > SELECT sysdate FROM DUAL;
Menampilkan tanggal hari ini
GROUP BY … HAVING ….
GROUP BY …. HAVING … digunakan untuk menyeleksi himpunan yang dihitung berdasarkan sebuah fungsi agregasi (agregate function) sesuai dengan kondisi yang didefinisikan setelah HAVING.
SELECT fungsi_agregasi, ..
FROM nama_tabel
GROUP BY nama_field
[HAVING kondisi];
Aggregate Function
AVG (Average / rata - rata)
AVG berfungsi untuk mengembalikan nilai rata – rata dari sekumpulan nilai yang terdapat pada sekumpulan nilai record pada sebuah field.
Contoh :
SQL > SELECT AVG(donasi) Donasi FROM keuangan;
Donasi : merupakan alias dari hasil fungsi AVG(donasi)
Queri diatas digunakan untuk menampilkan donasi rata – rata mahasiswa yang terdapat di dalam tabel keuangan.
MAX (Maximum)
MAX berfungsi untuk mengembalikan nilai maksimum yang terdapat pada sekumpulan nilai pada sebuah field.
Contoh :
SQL > SELECT MAX(donasi) “Donasi Terbesar”
FROM keuangan;
Donasi Terbesar : merupakan alias dari hasil fungsi MAX(donasi)
Queri diatas digunakan untuk menampilkan nilai donasi yang paling besar di dalam tabel keuangan.
MIN (Minimum)
MIN berfungsi untuk mengambil nilai minimum yang terdapat pada sekumpulan nilai pada sebuah field.
Contoh :
SQL > SELECT MIN(donasi) “Donasi Terkecil”
FROM keuangan;
Donasi Terkecil : merupakan alias dari hasil fungsi MIN(donasi)
Queri diatas digunakan untuk menampilkan nilai donasi yang paling kecil di dalam tabel keuangan.
SUM (jumlah / sigma)
SUM berfungsi untuk menghitung jumlah total nilai pada sebuah kolom tertentu.
Contoh :
SQL > SELECT SUM(donasi) “Total donasi” FROM keuangan;
Queri diatas digunakan untuk menampilkan total jumlah donasi untuk seluruh mahasiswa pada tabel keuangan.
COUNT (banyak record)
COUNT berfungsi untuk menghitung jumlah record yang memenuhi kondisi tertentu.
COUNT berfungsi untuk menghitung jumlah record yang memenuhi kondisi tertentu.
Contoh :
SQL > SELECT COUNT(*) “Donasi Manajemen Informatika”
FROM keuangan
WHERE nim like ‘3010%’;
Queri diatas digunakan untuk menampilkan jumlah mahasiswa Manajemen Informatika yang memberikan donasi.
Built-In Function Oracle
Built-In Function adalah fungsi yang disediakan oleh Oracle agar dapat digunakan di dalam SQL *Plus maupun oleh program eksternal.
Fungsi ini terdiri dari beberapa kelompok, yaitu :
Fungsi Aritmatika
Fungsi | Kegunaan | Contoh |
ABS | Mengembalikan nilai absolute | ABS(-10) = 10 |
CEIL | Mengembalikan bilangan bulat terbesar | CEIL(4.2) = 5 CEIL(-4.2) = -4 |
EXP | Mengembalikan nilai pemangkatan bilangan natural (ex) | EXP(0) = 1 EXP(1) = 2.718 |
FLOOR | Mengembalikan bilangan bulat terkecil | FLOOR(4.2) = 4 FLOOR(-4.2) = -5 |
LN | Mengembalikan nilai logaritma natural | LN(2) = 0.693147181 |
LOG | Mengembalikan nilai logaritma | LOG(10,10) =1 |
MOD | Menghasilkan sisa pembagian | MOD(4,2) = 0 MOD(7,2) = 1 |
POWER | Mengembalikan nilai pangkat | POWER(2,3) = 8 |
ROUND | Mengembalikan bilangan pembulatan | ROUND(5.56,1) = 5.6 |
SIGN | Mengembalikan nilai positif, negative, atau nol. Nilai balikan sign ada tiga yaitu 1 jika x>0 0 jika x=0 -1 jika x<0 | SIGN(-6) = -1 SIGN(3) = 1 |
SIN, COS, TAN, SINH, COSH,TANH | Fungsi trigonometri | SIN(0) = 0 COS(90) = 0 |
SQRT | Mengembalikan nilai akar | SQRT(16) = 4 |
TRUNC | Mengembalikan nilai yang telah dipotong | TRUNC(5.671,2) = 5.67 |
Fungsi Karakter / String
Fungsi | Kegunaan | Contoh |
ASCII(karakter) | Memgembalikan nilai ASCII dari karakter | ASCI(‘A’) = 65 |
CHR(nilai_ASCII) | Memgembalikan karakter dari sebuah nilai ASCII | CHR(65) = ‘A’ |
CONCAT(teks1, teks2) | Menggabungkan teks1 dan teks2 | CONCAT (“sate”, ”ayam”) = “sate ayam” |
DECODE(field, kode_decode) | Menggantikan nilai yang terdapat di dalam field dengan nilai lain. | DECODE(bulan,1, ‘Januari’, 2,’Februari’, 3,’Maret’, 4,’April’, 5,’Mei’, 6,’Juni’, ‘Bulan Lain’ ) bulan |
GREATEST(nilai1, nilai2, nilai3, …) | Mengembalikan nilai terbesar dari sederetan nilai | GREATEST (3,10,15,4) = 15 |
INITCAP(String) | Mengembalikan string yang terdiri dari huruf kapital pada setiap kata. | INITCAP(‘pOLItekNik tElkoM’) = Politeknik Telkom |
INSTR (teks1, teks2) INSTR(teks1, teks2, i) INSTR(teks1, teks2, 1, n) | Mencari posisi teks2 yang terdapat di dalam teks1. Pencarian dapat dimulai dari posisi ke-i. n menyatakan pengulangan yang ke-n kali dari teks2 yang terdapat di dalam teks1. | INSTR (’SQL n PL/SQL’, ’SQL’) = 1 INSTR (’SQL n PL/SQL’, ’SQL’, 4) = 10 INSTR (’SQL n PL/SQL’, ’SQL’,1,2) = 10 |
LEAST(nila1, nilai2, nilai3,…) | Mengembalikan nilai terkecil dari sederetan nilai | LEAST(2,2,5,1,6) = 1 |
LENGTH(String) | Mengembalikan panjang String | LENGTH(“1233”) = 4 |
LOWER(String) | Mengubah String menjadi huruf kecil semuanya | LOWER (“PoLTeK”) = poltek |
LPAD (teks1, n , teks2) | Menyisipkan karakter teks2 ke dalam teks1 untuk karakter kosong sepanjang n disebelah kiri teks1 | LPAD (‘Oracle’, 10 , ‘/’) = ////Oracle |
LTRIM (teks1, teks2) | Menghapus karakter-karakter pada bagian kiri teks1 sehingga tidak diawali dengan sembarang karakter pada teks2. Default teks2 adalah spasi | LTRIM (‘xxxOracle’,’x’) = Oracle |
REPLACE (teks1, teks2, teks3) | Menggantikan kemunculan karakter teks2 di dalam teks1 dengan teks3. Bila teks3 tidak disebutkan maka teks2 pada teks1 akan dihapus karena default teks3 null | REPLACE (‘siswa’,’a’,’i’) = siswi |
RPAD (teks1, n, teks2) | Menyisipkan karakter teks2 ke dalam teks1 untuk karakter kosong sepanjang n disebelah kanan teks1 | RPAD (‘Oracle’, 10 , ‘/’) = Oracle//// |
RTRIM (teks1, teks2) | Menghapus karakter-karakter pada bagian kanan teks1 sehingga tidak diakhiri dengan sembarang karakter pada teks2. Default teks2 adalah spasi | RTRIM ('123000', '0') = 123 |
SUBSTR(Teks,i) SUBSTR(Teks, i, n) | Mengambil karakter pada string teks dimulai dari posisi ke-i (dari kiri ke kanan) sebanyak n buah. Jika i bernilai negatif, maka posisi ke-i dimulai dari kanan ke kiri | SUBSTR(“ABCD”, 2) = “BCD” SUBSTR (“ABCD”, -3, 2 ) = “BC” |
TRANSLATE (teks1, teks2, teks3) | Menggantikan kemunculan karakter teks2 di dalam teks1 dengan teks3. perbedaannya dengan REPLACE adalah karakter yang digantikan dilakukan secara individual | |
UPER(String) | Mengubah String menjadi huruf besar semuanya | UPER(“PoLTeK”) = POLTEK |
Fungsi Tanggal
Fungsi | Kegunaan | Contoh |
ADD_MONTHS (tanggal, n) | Menambah atau mengurangi tanggal terhadap n | sysdate =06-JUL-05 ADD_MONTHS (sysdate, 2) = 08-JUL-05 ADD_MONTHS (sysdate, -2) = 04-JUL-05 |
LAST_DAY (tanggal) | Menghasilkan tanggal terakhir pada sebuah bulan | LAST_DAY (sysdate) = 31-JUL-05 |
MONTHS_BETWEEN (tanggal 2, tanggal1) | Mengahsilkan selisih tanggal2 dan tanggal1 dalam satuan bulan | MONTHS_BETWEEN (‘01-JUL-05’, ‘14-MAR-05’) = 3.58064516129032 |
NEXT_DAY() | Mengembalikan tanggal berikutnya | NEXT_DAY() = 07-JUL-05 |
TO_CHAR (tanggal, format) | Mengubah tanggal menjadi bentuk karakter sesuai dengan format. Sehingga dapat ditampilkan sebagai string | TO_CHAR (sysdate, ‘DD-MM-YYYY’) = 06-07-2006 |
Join Tabel
JOIN adalah menggabungkan beberapa tabel dangan cara mengakses setiap tabel secara individu berdasarkan kondisi yang diberikan, kemudian hasilnya digabungkan.
Syarat penggunaan JOIN adalah harus terdapat hubungan / keterkaitan diantara tabel – tabel yang dijadikan sumber dari kolom – kolom yang hendak di join dan ditampilkan. Keterkaitan diatara tabel – tabel berupa kolom – kolom yang memiliki nilai dan tipe data yang sama.
Join dimungkinkan dikarenakan oleh model relasional, begitu juga sebaliknya, join dibutuhkan dikarenakan model relational. Join sebetulnya secara umum terbagi dalam 3 jenis: cartesian product, join condition dan outer join.
Cartessian Product
Cartessian product merupakan himpunan dari hasil kombinasi yang memungkinkan dari baris-baris data dari 2 tabel atau lebih. Cartessian product merupakan join tanpa menggunakan join condition. Dengan demikian, sebuah baris pada tabel yang satu akan digabungkan dengan semua baris pada tabel yang lain, seterusnya sehingga jumlah baris hasil query menjadi ( n1* n2 *...*nn ) dengan ni adalah jumlah baris pada tabel ke-i. Jumlah baris hasil query yang merupakan hasil perkalian dari jumlah baris dari tabel-tabel inilah yang disebut dengan produk kartesian (cartesian product).
Contoh : Misalkan ilustrasi sebagai berikut
Tabel A Tabel B
A1 | A2 |
1 | 10 |
2 | 100 |
B1 | B2 |
2 | 4 |
3 | 9 |
Jika kita melakukan operasi seperti ini:
SELECT * FROM A, B;
maka akan dihasilkan Cartesian Product.
Tabel Produk Cartesian
A1 | A2 | B1 | B2 |
1 | 10 | 2 | 4 |
1 | 10 | 3 | 9 |
2 | 100 | 2 | 4 |
2 | 100 | 3 | 9 |
Untuk menghindari hal seperti ini, paling tidak terdapat sebuah join condition pada query yang melakukan join terhadap kedua tabel tersebut dengan catatan join condition tersebut valid.
Join Condition
Join condition menspesifikasikan kondisi join dari beberapa tabel. Seperti telah disinggung sebelumnya, join biasanya selalu melibatkan kolom-kolom yang terdapat pada tabel yang terlibat join yang memiliki kesamaan dan keserupaan maksud representasi dari kolom tersebut.
Equjoin/simple join/inner join adalah join yang menggunakan operator sama dengan (=) pada join condition-nya.
Contoh : Misalnya jika kita melakukan operasi
SELECT * FROM A, B WHERE A.A1 = B.B1
Maka akan dihasilkan sbb:
A1 | A2 | B1 | B2 |
2 | 100 | 2 | 4 |
Dari hasil operasi join tersebut dapat terlihat bahwa hanya nilai-nilai dari field A1 pada tabel A dan nilai-nilai field B1 pada tabel B yang bersesuaian yang ditampilkan. Dalam skenario ini adalah A1=B1=2.
Non-Equjoin adalah sebuah join yang menggunakan join condition yang berisi operator selain sama dengan (=) misalnya operator BETWEEN...AND
Self Join adalah query yang menggabungkan sebuah tabel dengan dirinya sendiri. Tabel tersebut muncul dua kali pada klausa from dan masing-masing harus diikuti dengan nama aliasnya. Penggunaan tabel alias ini wajib dilakukan untuk menghindari ambiguous karena semua nama kolom pada tabel pertama ada juga pada tabel kedua.
Contoh:
Misalkan terdapat tabel C sebagai berikut
Tabel C
C1 | C2 | C3 | C4 |
1 | 10 | 2 | 4 |
1 | 10 | 3 | 9 |
2 | 100 | 2 | 4 |
2 | 100 | 3 | 9 |
Jika dilakukan perintah:
SELECT a.C1,a.C2,a.C3,a.C4 FROM C a, C b WHERE a.C1=b.C3
Dari skenario ini dapat dibayangkan kita memiliki dua buah tabel yang identik yaitu tabel alias a dan tabel alias b. Dimana operasi yang dilakukan dapat digambarkan sebagai berikut:
Tabel C
C1 | C2 | C3 | C4 |
1 | 10 | 2 | 4 |
1 | 10 | 3 | 9 |
2 | 100 | 2 | 4 |
2 | 100 | 3 | 9 |
|
|
C1 | C2 | C3 | C4 |
1 | 10 | 2 | 4 |
1 | 10 | 3 | 9 |
2 | 100 | 2 | 4 |
2 | 100 | 3 | 9 |
C1 | C2 | C3 | C4 |
  1 | 10 | 2 | 4 |
1 | 10 | 3 | 9 |
  2 | 100 | 2 | 4 |
2 | 100 | 3 | 9 |
Maka akan dihasilkan seperti ini
C1 | C2 | C3 | C4 |
2 | 100 | 2 | 4 |
2 | 100 | 3 | 9 |
2 | 100 | 2 | 4 |
2 | 100 | 3 | 9 |
Outer Join
Outer join biasanya digunakan ketika kita ingin baris data dari tabel yang pertama, atau dari tabel yang kedua atau dari keduanya tetap ditampilkan meskipun tidak terdapat pasangan baris data tersebut dari tabel lain pada kondisi join-nya. Misalkan tabel A di-join dengan tabel B. Semua baris data pada tabel A ingin ditampilkan walaupun tidak memenuhi join condition. Untuk itu, operator outer join (+) pada join condition harus disertakan setelah kolom-kolom tabel B. Penulisan operator join dapat ditulis sebagai berikut.
A.col_name = B.col_name(+)
Dalam hal ini, tabel B akan memberikan nilai NULL untuk setiap baris pada tabel A jika join condition tidak terpenuhi.
Contoh :
Misalnya jika kita melakukan operasi SELECT * FROM A, B WHERE A.A1 = B.B1(+) maka akan dihasilkan
A1 | A2 | B1 | B2 |
1 | 10 | NULL | NULL |
2 | 100 | 2 | 4 |
Pada outer join ada beberapa aturan yang perlu diperhatikan :
· Operator “+” hanya boleh ada pada klausa WHERE dan hanya dapat dikenakan pada kolom dari tabel atau view.
· Jika terdapat lebih dari satu join condition, operator “+” harus dikenakan pada semua join condition tersebut. Jika tidak, hasilnya dengan inner join.
· Jika ada kondisi yang mengandung kolom tabel B dan kolom tersebut dibandingkan dengan sebuah nilai konstan, operator “+” harus dikenakan pada kolom tersebut. Misalkan:
SELECT * FROM A, B WHERE A.A1 = B.B1(+) and B.B1(+) > 0;
· Kondisi yang mengandung operator “+” tidak boleh dikombinasikan dengan logika OR dan IN.
· Kolom dengan operator “+” tidak boleh dibandingkan dengan hasil subquery. Misalkan:
SELECT * FROM A, B WHERE A.A1 = B.B1(+) and B.B1(+) > (SELECT C4 FROM C WHERE C4=2);
· Untuk query yang menggunakan outer join dan melibatkan lebih dari dua tabel, misalnya tabel A, B, dan C, maka tabel yang satu hanya dapat men-generate tabel NULL untuk satu tabel. Dengan demikian, operator “+” tidak dapat dikenakan pada kolom tabel B untuk dua buah join condition: A join B dan C join B. Misalkan:
SELECT * FROM A, B WHERE A.A1 = B.B1(+) and C.C1=B.B1(+) ;
Kesimpulan: Operasi join digunakan untuk menampilkan data dari beberapa tabel. Ada beberapa macam operasi join, antara lain :
Operator Join | Deskripsi | Contoh |
Cartesian Product | Menampilkan data dari beberapa tabel tanpa kondisi tertentu. | SELECT NAMA_PEGAWAI, NAMA_DEPARTEMEN FROM PEGAWAI P, DEPARTEMEN D |
Join Condition | Menampilkan data dari beberapa tabel dengan kondisi tertentu | SELECT NAMA_PEGAWAI, NAMA_DEPARTEMEN FROM PEGAWAI P, DEPARTEMEN D WHERE P.NO_DEP= D.NO_DEPARTEMEN; |
Outer Join | Menampilkan data dari beberapa tabel dimana kolom yang diacu tidak memiliki anggota | SELECT P. NAMA_PEGAWAI, M. NAMA_PEGAWAI AS MANAJER FROM PEGAWAI P, PEGAWAI M WHERE P.NO_MANAJER = M.NO_PEGAWAI(+); |
Query Bersarang
Pada kondisi tertentu, terkadang beberapa query membutuhkan nilai yang dihasilkannya dan digunakan sebagai kondisi pembandingan (dalam klausa WHERE sebagai contoh). Query tersebut dapat diformulasikan menggunakan nested query (query bersarang). Query bersarang akan melibatkan query dan subquery.
Subquery merupakan statement SELECT yang bersarang didalam klausa WHERE dari statement SELECT yang lain. Meskipun jarang, tetapi subquery juga bisa terdapat pada statement DML yang lain seperti INSERT, UPDATE ataupun DELETE.
Secara umum, sintaks dari NESTED QUERY adalah:
SELECT [DISTINCT] select_list FROM table1, table_2 [,table_3 …] WHERE {expression {[NOT] IN | comparison operator} | [NOT] EXIST } ( SELECT [DISTINCT] subquery_select_list FROM table_list WHERE search_conditions ) |
Yang dilakukan oleh subquery adalah menetapkan kondisi pencarian pada klausa WHERE dalam beberapa cara:
Menghasilkan list untuk klausa IN
Mengembalikan sebuah nilai yang dapat digunakan oleh operator
Mengembalikan nilai boolean (true atau false)
Didalam sebuah SELECT statement, subquery boleh berada pada klausa-klausa berikut ini:
· Klausa WHERE
· Klausa HAVING
· Klausa FROM
· Klausa START WITH (pada query hirarki)
Sebagai catatan, subquery pada klausa WHERE sering disebut sebagai nested subquery. Sedangkan subquery pada klausa FROM sering disebut dengan istilah inline view.
Banyak permasalahan yang dapat dipecahkan dengan menggunakan subquery. Daftar berikut ini menunjukkan beberapa kegunaan subquery:
· Memberikan nilai sebagai kondisi di dalam klausa WHERE, HAVING, dan START WITH (subquery pada SELECT, UPDATE, dan INSERT statement).
· Menentukan sekumpulan baris data untuk kebutuhan membuat view (subquery pada CREATE VIEW statement).
· Mendefinisikan sebuah tabel (inline view) yang digunakan oleh query utama (subquery pada klausa FROM dari sebuah SELECT statement).
· Menentukan sekumpulan baris data yang dimasukkan ke dalam tabel tujuan, baik ke dalam tabel yang sudah ada (subquery pada INSERT statement) maupun pada saat yang bersamaan dengan pembuatan tabel (subquery pada CREATE TABLE statement).
· Mendapatkan satu nilai atau lebhih yang digunakan untuk mengubah data yang sudah ada (subquery pada UPDATE statement).
Klausa IN
Subquery yang dilakukan menggunakan klausa IN akan dilakukan pengecekan apakah suatu nilai ada atau tidak dalam hasil dari subquery.
Contoh :
SELECT A1 FROM A WHERE A1 IN (SELECT C3 FROM C)
Hasilnya sbb:
A1 | A2 |
2 | 100 |
Klausa ALL
Membandingkan nilai dengan setiap nilai yang dikembalikan oleh query tersarang. Kondisi perbandingan akan bernilai benar jika semua nilai yang dikembalikan oleh subquery memenuhi kondisi tersebut.
Contoh :
SELECT * FROM A WHERE A1 < ALL(SELECT C3 FROM C)
Hasilnya sbb:
A1 | A2 |
1 | 10 |
Klausa ANY/SOME
Membandingkan nilai dengan salah satu nilai yang dikembalikan oleh query tersarang. Kondisi perbandingan akan bernilai benar jika salah satu nilai yang dikembalikan oleh subquery memenuhi kondisi tersebut dan akan bernilai salah atau tidak terpenuhi jika subquery tidak mengembalikan nilai apapun (tabel kosong).
Contoh :
SELECT * FROM A WHERE A1 < ANY(SELECT C3 FROM C)
Hasilnya sbb:
A1 | A2 |
1 | 10 |
2 | 100 |
Klausa [NOT] EXISTS
Klausa EXISTS digunakan untuk melakukan pengecekan apakah hasil dari nested query yang berkorelasi menghasilkan baris data atau tidak. Operator exists akan menghasilkan nilai “TRUE” jika subquery yang mengikutinya menghasilkan paling tidak satu baris data.
Contoh :
SELECT * FROM A WHERE EXISTS (SELECT C3 FROM C)
Hasilnya sbb:
A1 | A2 |
1 | 10 |
2 | 100 |
sangat membantu
BalasHapus