Fungsi Single-Row. Fungsi merupakan fitur paling dibutuhkan dari SQL yang dapat digunakan untuk:

Fungsi Single-Row

Fungsi SQL Fungsi merupakan fitur paling dibutuhkan dari SQL yang dapat digunakan untuk: -

Melakukan kalkulasi pada data

-

Memodifikasi item data secara individual

-

Memanipulasi output dari sejumlah baris

-

Melakukan format terhadap tanggal dan angka untuk ditampilkan

-

Mengkonversi tipe data pada kolom

Fungsi SQL ada kalanya menggunakan argument dan selalu mengembalikan nilai. Ada dua tipe fungsi SQL: a. Fungsi Single Row (fungsi satu baris) Fungsi ini hanya bekerja pada satu baris dan mengembalikan satu hasil per baris. Ada beberapa tipe, diantaranya: character, number, date, dan konversi. b. Fungsi Multiple Row/Group Function (fungsi banyak baris) Fungsi ini dapat memanipulasi sejumlah baris dan memberikan satu hasil per sejumlah baris. Fungsi banyak baris akan dijelaskan pada modul yang lain.

Fungsi Satu Baris Fungsi ini digunakan untuk memanipulasi item data, menerima satu atau lebih argument dan mengembalikan satu nilai dari setiap baris yang dikembalikan oleh query. Sintaksnya dapat ditulis sebagai berikut. nama_fungsi [(arg1, arg2, …)]

nama_fungsi merupakan nama dari fungsi yang akan digunakan.

Danny Kriestanto, S.Kom., M.Eng

13

arg1, arg2 merupakan argument yang digunakan oleh fungsi. Bagian ini dapat direpresentasikan dalam bentuk nama kolom atau ekspresi. Isi dari argument dapat berupa: konstanta yang dideklarasikan oleh user, nilai variabel, nama kolom, ataupun ekspresi. Seperti yang telah disebutkan di atas, fungsi pada SQL ada beberapa macam, yang dapat dijabarkan sebagai berikut. 1. Fungsi umum, yakni: NVL, NVL2, NULLIF, COALESECE, CASE, dan DECODE. 2. Fungsi karakter Fungsi karakter terbagi atas dua bagian, yaitu: a. Case-manipulation functions, yaitu: -

LOWER(kolom/ekspresi), digunakan untuk mengkonversi semua huruf menjadi huruf kecil.

-

UPPER(kolom/ekspresi), untuk mengubah seluruh huruf menjadi huruf besar.

-

INITCAP(kolom/ekspresi), untuk mengubah huruf pertama dari setiap kata menjadi huruf besar.

Kerjakan praktik nomor 1 hingga 4. b. Character-manipulation functions, yaitu: -

CONCAT(kolom1/ekspresi1, kolom2/ekspresi2), untuk menggabungkan dua nilai, sama halnya seperti operator (||).

-

SUBSTR(kolom/ekspresi, m [, n] ), untuk karakter mengembalikan karakter tertentu dimulai dari posisi m sebanyak n karakter.

-

LENGTH(kolom/ekspresi), mengembalikan nilai banyaknya karakter di dalam ekpresi tersebut

-

INSTR(kolom/ekspresi, ‘string’, [, m] [, n]), mengembalikan posisi numerik dari sebuah string. Secara opsional dapat menggunakan posisi m untuk mulai mencari dan kehadiran n pada string. Secara default m dan n adalah 1, artinya pencarian dimulai dari posisi pertama dan laporan diberikan ketika pertama kali menemukan hasilnya.


14

-

LPAD(kolom/ekspresi, n, ‘string’), mengatur hasil berupa karakter rata kanan dengan total lebar n dari posisi karakter.

-

RPAD(kolom/ekspresi), mengatur hasil berupa karakter rata kiri dengan total lebar n dari posisi karakter.

-

TRIM(leading/trailing/both, trim_character FROM trim_source), memungkinkan untuk melakukan trim karakter yang berada depan (leading) maupun yang di belakang (trailing). Jika trim_character atau trim_source adalah karakter literal, keduanya harus diapit dengan tanda petik tunggal.

-

REPLACE(teks, cari_string, string_pengganti), mencari ekpresi teks dari sebuah string dan menggantinya dengan string_pengganti.

Kerjakan praktik nomor 5. 3. Fungsi numerik Fungsi numerik terdiri atas 3 macam, yaitu: -

ROUND(kolom/ekspresi, n), membulatkan nilai dari sebuah kolom, ekspresi, menjadi sepanjang n desimal, atau jika n tidak disebutkan maka tidak ada nilai pecahan.

-

TRUNC(kolom/ekspresi, n), memotong nilai dari kolom atau ekspresi sebanyak n desimal atau jika n tidak disebutkan maka nilai default n adalah 0.

-

MOD(m, n), mengembalikan sisa hasil bagi m terhadap n.

Kerjakan praktik nomor 6 hingga 8. 4. Fungsi Tanggal Sebelum fungsi tanggal dijelaskan, perlu diketahui bahwa database Oracle menyimpan tanggal dalam bentuk numerik, seperti: abad, tahun, bulan, hari, jam, menit, dan detik. Format default dari tanggal adalah DD-MON-RR, dimana DD adalah 2 digit tanggal, MON adalah 3 huruf pertama dari bulan (ditulis dengan huruf besar), dan RR adalah 2 digit tahun yang dapat mengspesifikan abad. Format yang sama dengan RR adalah format YY, namun YY tidak dapat menspesifikasikan abad. Untuk lebih jelasnya, kerjakan dahulu praktik nomor 9 dan amati.


15

Ada beberapa fungsi standar mengenai penanggalan pada Oracle yang sangat membantu, seperti: SYSDATE, yang mengembalikan tanggal dan waktu. Kerjakan praktik nomor 10.

Tanggal juga dapat dilakukan operasi aritmetika dengan pola seperti pada tabel berikut. Operasi

Hasil

Penjelasan

Tanggal + angka

Tanggal

Menambahkan banyak hari pada tanggal

Tanggal - angka

Tanggal

Mengurangi banyak hari dari tanggal

Tanggal - tanggal

Jumlah hari Mengurangi satu tanggal dari tanggal yang lain

Tanggal + angka/24

Tanggal

Menambah sejumlah jam pada tanggal

Kerjakan praktik nomor 11.

Fungsi-fungsi pada tanggal adalah sebagai berikut: -

MONTHS_BETWEEN(tgl1, tgl2), mencari jumlah bulan di antara tgl1 dan tgl2. Hasilnya dapat berupa positif atau negatif. Jika tgl1 lebih besar dari tgl2 maka hasilnya positif.

-

ADD_MONTHS(tgl, n), menambahkan sejumlah n bulan ke tgl. n harus berupa integer dan dapat berupa bilangan negatif.

-

NEXT_DAY(tgl, ‘char’), menemukan hari berikut dari sebuah tanggal yang dispesifikasikan. Nilai dari ‘char’ dapat berupa angka yang merepresentasikan hari atau string.

-

LAST_DAY(tgl), menemukan tanggal hari terakhir dari bulan dimana tgl berada.

-

ROUND(tgl[, ‘fmt’]), mengembalikan tgl yang di-round sebanyak unit yang dispesifikaskan oleh fmt. Jika fmt tidak disebutkan, tgl di-round ke hari yang terdekat.

-

TRUNC(tgl[, ‘fmt’]), mengembalikan tgl dengan porsi waktu dari hari yang ditruncate sebanyak unit yang dispesifikasikan oleh fmt. Jika fmt tidak disebutkan, tgl di-truncate ke hari yang terdekat.

Untuk lebih jelasnya, kerjakan praktik nomor 12 dan 13.

5. Konversi tipe data


16

Tipe data konversi terbagi atas dua, yaitu : implicit dan explicit. Implisit berarti dikerjakan secara otomatis oleh server Oracle, sedangkan eksplisit berarti dilakukan secara manual oleh user.

Ada beberapa tipe data konversi eksplisit, yaitu: -

TO_CHAR(nomor/tanggal, [ fmt], [nlsparam]), mengkonversikan angka atau tanggal ke tipe data VARCHAR2. Konversi NUMBER: nlsparam menspesifikasikan karakter desimal, pemisah grup, simbol mata uang lokal, atau simbol mata uang internasional. Jika nslparam tidak disebutkan, maka parameter default yang akan digunakan. Konversi DATE: nslparam menspesifikasikan format yang digunakan dalam tanggal. Jika tidak disebutkan maka yang digunakan adalah parameter default. Kerjakan praktik nomor 14 dan 15.

-

TO_NUMBER(char, [ fmt], [ nslparam]), mengkonversi string yang mengandung angka menjadi NUMBER yang dispesifikasikan dengan model format fmt. nslparam untuk konversi ini sama seperti konversi TO_CHAR dari NUMBER.

-

TO_DATE(char, [ fmt], [ nslparam]), mengkonversi string yang menunjukkan tanggal ke dalam bentuk DATE berdasarkan format yang ditentukan pada fmt. jika fmt tidak disebutkan, maka format yang digunakan adalah DD-MON-YY.

Format Elemen tanggal dapat dilihat pada tabel berikut. YYYY

4 digit tahun

YEAR

Tahun disebutkan

MM MONTH MON DY DAY DD

2 digit bulan Bulan disebutkan secara penuh 3 huruf singkatan dari bulan 3 huruf singkatan dari hari Hari disebutkan secara penuh 2 digit tanggal

Untuk lebih jelas, kerjakan praktik nomor 16 hingga 19.


17

PRAKTIK Kerjakan, amati hasilnya, dan analisa apa yang dikerjakan perintah berikut ini: 1. SELECT ‘Pekerjaan IT untuk ‘ || UPPER(last_name) || ‘ adalah ‘ || LOWER(job_id) AS “Detail Pegawai” FROM employees;

2. SELECT employee_id, last_name, department_id FROM employees WHERE last_name = ‘higgins’;

3. SELECT employee_id, last_name, department_id FROM employees WHERE LOWER(last_name) = ‘higgins’;

4. SELECT employee_id, UPPER(last_name), department_id FROM employees WHERE INITCAP(last_name) = ‘higgins’;

5. SELECT employee_id, CONCAT(first_name, last_name) NAME, job_id, LENGTH(last_name), INSTR(last_name, ‘a’) “Mengandung ‘a’?” FROM employees WHERE SUBSTR(job_id, 4) = ‘REP’;

6. SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1) FROM DUAL;

7. SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-2) FROM DUAL;

8. SELECT last_name, salary, MOD(salary, 5000) FROM employees WHERE job_id = ‘SA_REP’;


18

9. SELECT last_name, hire_date FROM employees WHERE last_name LIKE ‘G%’;

10. SELECT SYSDATE FROM DUAL;

11. SELECT last_name, (SYSDATE - hire_date)/7 AS MINGGU FROM employees WHERE department_id = 90;

12. SELECT employee_id, hire_date, MONTHS_BETWEEN(SYSDATE, hire_date) masajabatan, ADD_MONTHS(hire_date, 6) review, NEXT_DAY(hire_date, ‘FRIDAY’), LAST_DAY(hire_date) FROM employees WHERE MONTHS_BETWEEN(SYSDATE, hire_date) < 36;

13. SELECT employee_id, hire_date, ROUND(hire_date, ‘MONTH’), TRUNC(hire_date, ‘MONTH’) FROM employees WHERE hire_date LIKE ‘%97’;

14. SELECT employee_id, TO_CHAR(hire_date, ‘MM/YY’) bulan_dipekerjakan FROM employees WHERE last_name = ‘Higgins’;

15. SELECT TO_CHAR(salary, ‘$99,999.00’) GAJI FROM employees WHERE last_name = ‘Ernst’;

16. SELECT last_name, TO_CHAR(hire_date, ‘fmDD Month YYYY’) as tanggalmasuk FROM employees;

17. SELECT last_name, TO_CHAR(hire_date, ‘fmDD Month YYYY fmHH:MI:SS AM’) as tanggalmasuk FROM employees;


19

18. SELECT last_name, TO_CHAR(hire_date, ‘DD-Mon-YYYY’) FROM employees WHERE hire_date < TO_DATE(’01-Jan-90’, ‘DD-Mon-RR’);

19. SELECT last_name, TO_CHAR(hire_date, ‘DD-Mon-YYYY’) FROM employees WHERE TO_DATE(hire_date, ‘DD-Mon-YY’) < ’01-Jan-1990’;


20

Fungsi Agregasi Data

Group functions atau disebut juga fungsi banyak baris (multiple-row function) adalah fungsi yang bekerja pada sekelompok baris untuk memberikan satu hasil untuk setiap satu grup, tidak seperti halnya fungsi satu baris (single-row function). Ada beberapa tipe group functions: 1. AVG([DISTINCT|ALL] n), menghitung rata-rata dari semua data yang diquery (n), tidak termasuk yang bernilai null. 2. COUNT({*|[DISTINCT|ALL] ekspr}), mencacah jumlah baris, dimana ekspr merupakan sesuatu nilai bukan null. Jika hendak digunakan untuk mencacah semua baris gunakan tanda *, maka yang terhitung adalah semua baris yang muncul lebih dari 1 kali dan juga baris yang null. 3. MAX([DISTINCT|ALL] ekspr), nilai maksimum dari ekpr, tidak termasuk nilai null. 4. MIN([DISTINCT|ALL] ekspr), nilai minimum dari ekpr, tidak termasuk nilai null. 5. STDDEV([DISTINCT|ALL] n), standar deviasi dari n, tidak termasuk nilai null. 6. SUM([DISTINCT|ALL] n), menghitung jumlah dari n, tidak termasuk nilai null. 7. VARIANCE([DISTINCT|ALL] n), variansi dari n, tidak termasuk nilai null. Dari ketujuh tipe ini, STDDEV dan VARIANCE tidak akan dijelaskan. DISTINCT digunakan untuk menghilangkan duplikasi baris, sedangkan argument ekspr bisa berarti tipe data CHAR, VARCHAR2, NUMBER, ataupun DATE.

Perhatikan sintaks cara penggunaan group functions seperti yang tertulis di bawah ini. SELECT [kolom, ] group_function(kolom), … FROM nama_tabel [WHERE kondisi] [GROUP BY ekspresi_group_by] [ORDER BY kolom]; Contoh penggunaan tipe-tipe di atas dapat dilihat pada praktik nomor 1 sampai nomor 5. Penggunaan DISTINCT dapat dilihat pada praktik nomor 6.


21

Mengelompokkan Data Hingga saat ini, semua group function memperlakukan tabel sebagai satu kumpulan informasi yang sangat besar. Ada saatnya informasi tersebut dipecah atas kelompok-kelompok yang lebih kecil.

Syarat untuk melakukan group by adalah semua kolom yang terdapat pada klausa SELECT harus ditulis dibagian klausa GROUP BY, KECUALI kolom yang dikenakan group function. Akan tetapi, yang terdapat pada klausa GROUP BY tidak perlu berada di klausa SELECT. Untuk lebih jelas, kerjakan dan perhatikan contoh pada praktik nomor 7 hingga 9. Selain itu, GROUP BY digunakan pada saat user ingin mencampurkan antara individual item dengan group function dalam satu perintah SELECT. Jika hal ini terjadi namun klausa GROUP BY tidak ditulis, maka akan muncul error. Error ini disebut juga dengan illegal queries. Perhatikan contoh error yang terjadi pada praktik nomor 10. Contoh lain pada praktik 11 adalah error karena salah memasukkan perintah group function pada klausa WHERE. Masalah seperti ini dapat diselesaikan dengan memberikan klausa HAVING, seperti yang tercantum pada praktik 12. Klausa HAVING digunakan untuk memberikan batasan query. Syarat dari penggunaan HAVING adalah group function harus diaplikasikan. Secara sintaks, penempatan HAVING pada SQL dapat ditulis sebagai berikut. SELECT kolom, group_function FROM nama_tabel [WHERE kondisi] [GROUP BY ekspresi_group_by] [HAVING kondisi_group] [ORDER BY kolom]; Kerjakan praktik nomor 13 dan 14 agar lebih jelas.


22

Selain digunakan secara individual, group function juga dapat dibuat berkalang (nested). Perhatikan contoh praktik nomor 15. PRAKTIK Kerjakan, amati hasilnya, dan analisa apa yang dikerjakan perintah berikut ini: 1. SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary) FROM employees WHERE job_id LIKE ‘%REP%’;

2. SELECT MIN(hire_date), MAX(hire_date) FROM employees;

3. SELECT MIN(last_name), MAX(last_name) FROM employees;

4. SELECT COUNT(*) FROM employees WHERE department_id = 50;

5. SELECT COUNT(commission_pct) FROM employees WHERE department_id = 80;

6. SELECT COUNT(DISTINCT department_id) FROM employees;

7. SELECT department_id, AVG(salary) FROM employees GROUP BY department_id; 8. SELECT department_id id_departemen, job_id, SUM(salary) FROM employees GROUP BY department_id, job_id;

9. SELECT AVG(salary) FROM employees GROUP BY department_id;

10. SELECT department_id, COUNT(last_name) FROM employees; 11. SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) > 8000 GROUP BY department_id;

12. SELECT department_id, AVG(salary) FROM employees GROUP BY department_id


23

HAVING AVG(salary) > 8000;

13. SELECT department_id, MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary) > 10000;

14. SELECT job_id, SUM(salary) upah FROM employees WHERE job_id NOT LIKE ‘%REP%’ GROUP BY job_id HAVING SUM(salary) > 13000 ORDER BY SUM(salary);

15. SELECT MAX(AVG(salary)) FROM employees GROUP BY department_id;


24

Subquery

Subquery digunakan ketika dibutuhkan lebih dari satu query. Query tersebut digabungkan sehingga menjadi query di dalam query. Perintah dasar subquery. SELECT daftar_select FROM nama_tabel WHERE ekspresi operator (SELECT daftar_select FROM nama_tabel);

Operator yang digunakan dapat berupa >, >=, <, <=, =, dan <> dan operator lain seperti IN, ANY, dan ALL. Kerjakan Praktek 1. Aturan penulisan subquery: -

Subquery ditulis di dalam tanda kurung.

-

Letakkan subquery pada sisi kanan dari perbandingan

-

Perintah ORDER BY pada subquery tidak perlu ada

-

Gunakan operator single-row pada single-row subquery dan operator multiple-row dengan subquery multiple-row. Yang termasuk operator single-row adalah >, >=, <, <=, =, dan <>. Sedangkan operator multiple-row adalah IN, ANY dan ALL.

Kerjakan praktek nomor 2 dan 3 untuk single-row dan praktek nomor 5 hingga 8. Pada kasus penggunaan klausa HAVING, setelah server Oracle mengeksekusi subquery, hasilnya dikembalikan ke klausa HAVING dari query utama. Kerjakan praktek nomor 4.


25

PRAKTEK Kerjakan dan amati hasilnya. 1. SELECT last_name FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name = ‘Abel’);

2. SELECT last_name, job_id, salary FROM employees WHERE job_id = (SELECT job_id FROm employees WHERE employee_id = 141) AND salary > (SELECT salary FROM employees WHERE employee_id = 143);

3. SELECT last_name, job_id, salary, FROM employees WHERE salary = (SELECT MIN(salary) FROM employees);

4. SELECT department_id, MIN(salary) FROM employees GROUP BY department_id HAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 50);

5. SELECT last_name, salary, department_id FROM employees WHERE salary IN (2500, 4200, 4400, 6000, 7000, 8300, 8600, 17000);

6. SELECT employee_id, last_name FROM employees WHERE salary IN (SELECT MIN(salary) FROM employees GROUP BY department_id);

7. SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary < ANY(SELECT salary FROM employees WHERE job_id = ‘IT_PROG’) AND job_id <> ‘IT_PROG’;

8. SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary < ALL (SELECT salary FROM employees WHERE job_id = ‘IT_PROG’) AND job_id <> ‘IT_PROG’;


26

9. SELECT e.last_name FROM employees e WHERE e.employee_id IN (SELECT m.manager_id FROM employees m);

10. SELECT last_name FROM employees WHERE employee_id NOT IN (SELECT m.manager_id FROM employees m WHERE manager_id IS NOT NULL);


27

Fungsi Single-Row. Fungsi merupakan fitur paling dibutuhkan dari SQL yang dapat digunakan untuk:

Recommend Documents