Function pada SQL. Aggregate Functions Scalar functions. Arithmetic functions String functions Date functions

Function pada SQL  Aggregate Functions  Scalar functions  Arithmetic functions  String functions  Date functions

Aggregate Functions Function AVG(column)

Keterangan Menghitung rata-rata nilai column

COUNT(column)

Menghitung jumlah data pada kolom

COUNT(*)

Menghitung jumlah baris pada tabel

MAX(column)

Mencari nilai terbesar pada kolom tersebut

MIN(column)

Mencari nilai terkecil dari kolom tersebut

SUM(column)

Menjumlahkan data yang ada pada kolom

Contoh Perintah Agregate Function  Pola perintahnya : SELECT function(column) FROM table

select count(nim) from mahasiswa select Max(nim) from mahasiswa select UPPER(nama) from mahasiswa

Fungsi Aritmatika

Arithmetic Functions Fungsi ABS

Kegunaan Contoh 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) Mengembalikan bilangan bulat terkecil

EXP(0) = 1 EXP(1) = 2.718

LN(2) = 0.693147181

MOD

Mengembalikan nilai logaritma natural Mengembalikan nilai logaritma Menghasilkan sisa pembagian

POWER

Mengembalikan nilai pangkat

ROUND

Mengembalikan bilangan pembulatan Mengembalikan nilai positif, negative, atau nol. Nilai balikan sign ada tiga yaitu 1 jika x>0 0 jika x=0 -1 jika x<0

FLOOR LN LOG

SIGN

FLOOR(4.2) = 4 FLOOR(-4.2) = -5

LOG(10,10) =1 MOD(4,2) = 0 MOD(7,2) = 1 POWER(2,3) = 8 ROUND(5.56,1) = 5.6 SIGN(-6) = -1 SIGN(3) = 1

SIN, COS, TAN, SINH, Fungsi trigonometri COSH,TANH

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

String Functions Fungsi

Kegunaan

Contoh

ASCII(karakter)

Memgembalikan nilai ASCII dari karakter Memgembalikan karakter dari sebuah nilai ASCII

ASCI(‘A’) = 65

CHR(nilai_ASCII) CONCAT(teks1, teks2)

Menggabungkan teks1 dan teks2

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.

CHR(65) = ‘A’

CONCAT (“sate”, ”ayam”) = “sate ayam” DECODE(field, kode_decode) Menggantikan nilai yang terdapat DECODE(bulan,1, ‘Januari’, di dalam field dengan nilai lain. 2,’Februari’, 3,’Maret’, 4,’April’, 5,’Mei’, 6,’Juni’, ‘Bulan Lain’ ) bulan GREATEST(nilai1, nilai2, nilai3, Mengembalikan nilai terbesar GREATEST …) dari sederetan nilai (3,10,15,4) = 15 INITCAP(String) Mengembalikan string yang INITCAP(‘pOLItekNik terdiri dari huruf kapital pada tElkoM’) setiap kata. = Politeknik Telkom 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,…) LENGTH(String) LOWER(String) LPAD (teks1, n , teks2) LTRIM (teks1, teks2)

REPLACE (teks1, teks2, teks3)

RPAD (teks1, n, teks2) RTRIM (teks1, teks2)

SUBSTR(Teks,i) SUBSTR(Teks, i, n)

TRANSLATE (teks1, teks2, teks3)

UPPER(String)

Mengembalikan nilai terkecil dari sederetan nilai Mengembalikan panjang String

LEAST(2,2,5,1,6) =1 LENGTH(“1233”) =4 Mengubah String menjadi huruf kecil LOWER semuanya (“PoLTeK”) = poltek Menyisipkan karakter teks2 ke LPAD dalam teks1 untuk karakter kosong (‘Oracle’, 10 , ‘/’) sepanjang n disebelah kiri teks1 = ////Oracle Menghapus karakter-karakter pada LTRIM bagian kiri teks1 sehingga tidak (‘xxxOracle’,’x’) diawali dengan sembarang karakter = Oracle pada teks2. Default teks2 adalah spasi Menggantikan kemunculan karakter REPLACE teks2 di dalam teks1 dengan teks3. (‘siswa’,’a’,’i’) Bila teks3 tidak disebutkan maka = siswi teks2 pada teks1 akan dihapus karena default teks3 null Menyisipkan karakter teks2 ke RPAD dalam teks1 untuk karakter kosong (‘Oracle’, 10 , ‘/’) sepanjang n disebelah kanan teks1 = Oracle//// Menghapus karakter-karakter pada bagian kanan teks1 sehingga tidak diakhiri dengan sembarang karakter pada teks2. Default teks2 adalah spasi 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 Menggantikan kemunculan karakter teks2 di dalam teks1 dengan teks3. perbedaannya dengan REPLACE adalah karakter yang digantikan dilakukan secara individual Mengubah String menjadi huruf besar semuanya

RTRIM ('123000', '0') = 123 SUBSTR(“ABCD”, 2) = “BCD” SUBSTR (“ABCD”, -3, 2 ) = “BC”

UPPER(“PoLTeK”) = POLTEK

Date Functions Fungsi ADD_MONTHS (tanggal, n)

Kegunaan Menambah atau mengurangi tanggal terhadap n

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

Cartesian Product

Jika kita melakukan operasi seperti ini: SELECT * FROM A, B; maka akan dihasilkan??

Join Operation  Untuk menghindari fenomena cartesian product maka operasi tersebut harus memiliki kondisi pada klausa “where” untuk melibatkan kolom-kolom yang terdapat pada tabel yang terlibat join yang memiliki kesamaan dan keserupaan maksud representasi dari kolom tersebut.  Operasi ini disebut sebagai join.

 Jika kita melakukan operasi:

SELECT * FROM A, B WHERE A.A1 = B.B1;

 Jenis operasi join:  Equjoin/simple join/inner join adalah join yang menggunakan operator sama dengan (=) pada join condition-nya.  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.  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.

Join dengan perintah From  select pegawai.nip, pegawai.nama, pegawai.kd_unit, unit.nama_unit from pegawai, unit where pegawai.kd_unit=unit.kd_unit;  select a.nip, a.nama, a.kd_unit, b.nama_unit from pegawai a, unit b where a.kd_unit=b.kd_unit;

Join dengan perintah Inner Join  select pegawai.nip, pegawai.nama, pegawai.kd_unit, unit.nama_unit from pegawai inner join unit on pegawai.kd_unit=unit.kd_unit;  Biasanya join dilakukan pada tabel yang mempunyai foreign-key pada suatu tabel dihubungkan dengan primary-key pada tabel yang lainnya

Outer Join  Kebalikan dari Inner Join, dimana inner join akan mencari data yang sama pada kedua belah tabel, sementara Outer Join adalah memungkinkan untuk menampilkan data walaupun data tidak ditemukan pada sebelah tabel. Data yang tidak ketemu akan diberikan nilai null.  Misalkan tabel A dijoinkan dengan tabel B. semua baris data pada tabel A ingin ditampikan walaupun tidak memenuhi join condition. Untuk itu operator outer join (+) pada join condition harus disertakan setelah kolomkolom tabel B. Penulisannya adalah sbb: a.col_name=b.col_name(+) atau dengan menggunakan a left outer join b on a.col_name=b.col_name;

Outer Join  Penulisan query untuk OUTER JOIN ini dibentuk menjadi 3 bagian sbb:  LEFT JOIN  RIGHT JOIN  FULL JOIN

SELECT nama_kolom FROM nama_tabel_1 (LEFT|RIGHT|FULL) [OUTER] JOIN nama_tabel_2 ON relasi_tabel

Outer Join  Misalkan ditampilkan semua data pegawai meskipun pegawai tersebut tidak memiliki unit kerja:  select pegawai.nip, pegawai.nama, pegawai.kd_unit, unit.nama_unit from pegawai, unit where pegawai.kd_unit=unit.kd_unit(+);

 select pegawai.nip, pegawai.nama, pegawai.kd_unit, unit.nama_unit from pegawai left outer join unit on pegawai.kd_unit=unit.kd_unit;

Contoh kasus  untuk Inner/simple Join  Menampilkan data pegawai yang sudah memiliki unit kerja.

 Untuk outer Join  Menampilkan data karyawan yang maupun belum memiliki unit kerja.

Self Join  Adalah query yang menggabungkan sebuah tabel dengan dirinya sendiri.  Contoh penggunaan self join adalah mencari atasan atau bawahan yang dimiliki oleh seorang pegawai.  Query: select b.nip, b.nama, a.nip as "nip atasan", a.nama "nama_atasan" from pegawai a, pegawai b where a.nip=b.atasan;

Self Join

Tabel Pegawai b

Tabel Pegawai a

Hasil Join

Query Bersarang  Merupakan query yang berada didalam query lainnya.  Subquery sering disebut sebagai inner query, sedangkan query induknya disebut sebagai outer query.  Query bersarang atau nested query dapat digunakan insert, update, delete, maupun select statement.

 Didalam statement SELECT, subquery boleh ada pada klausa-klausa berikut ini:  Klausa select ….  Klausa from ….  Klausa where ….  Klausa having ….

• Subquery pada klausa where sering disebut sebagai nested subquery. • Sedangkan subquery pada klausa from disebut sebagai inline view.

 Subquery pada klausa select (menampilkan nama unit tempai pegawai bekerja):  select nip, nama, kd_unit, (select nama_unit from unit b where a.kd_unit=b.kd_unit) from pegawai a;

 Subquery pada klausa from (menampilkan data pegawai dari tabel yang telah difilter dari nilai null):  select nip, nama, kd_unit from (select * from pegawai where kd_unit is not null);

 Subquery pada klausa where (menampilkan data pegawai yang se-unit dengan pegawai dengan nip 54):  select nip, nama, kd_unit from pegawai where kd_unit=(select kd_unit from pegawai where nip='54');

 Subquery pada klausa having(menampilkan data unit yang gaji pegawainya diatas rata-rata seluruh pegawai):  select kd_unit, min(gaji), max(gaji), avg(gaji) from pegawai group by kd_unit having avg(gaji) > (select avg(gaji) from pegawai);

Klausa pada Query bersarang  Klausa IN Subquery yang dilakukan menggunakan klausa IN akan dilakukan pengecekan apakah suatu nilai ada atau tidak dalam hasil dari subquery. Contoh: select * from pegawai where kd_unit in(select kd_unit from unit);  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 pegawai where gaji > all(select gaji from pegawai);

Klausa pada Query bersarang  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 pegawai where gaji > any(select gaji from pegawai);  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 pegawai a where exists(select * from unit b where a.kd_unit=b.kd_unit);

Rangkuman  Perintah-perintah query dilengkapi dengan berbagai fungsi untuk memperoses datanya.  Terdapat dua kelompok fungsi, yaitu:  Aggregate Functions

 Scalar functions

 Join operation digunakan untuk menampilkan data yang berasal dari dua atau lebih tabel yang terkait  Keyword yang digunakan dapat menggunakan Inner Join dan Outer Join

Rangkuman  inner join akan mencari data yang sama pada kedua belah tabel, sementara Outer Join adalah memungkinkan untuk menampilkan data walaupun data tidak ditemukan pada sebelah tabel. Data yang tidak ketemu akan diberikan nilai null.  Query bersarang menampilkan data

digunakan

sebagai

kondisi

pembanding

untuk

 Beberapa kalusa yang dapat digunakan adalah: IN, ANY/SOME, EXISTS or NOT EXISTS

Tugas e -learning  dari contoh kasus pada UTS anda buatlah contoh query dan hasil screenshotnya untuk  1. kasus agregasi menggunakan count,max,min,adv  2. join dengan dua table dan 3 table  3. inner join  4. self join  5. query bersarang