Fungsi-Fungsi SQL dan Penyelesainya

  Fungsi fungsi sangat mendukung fitur pada SQL

·         Melakukan perhitungan perhitungan pada data

·         Memodifikasi item item data individual

·         Memanipulasi keluaran dari kelompok baris baris

·         Format untuk tampilan tanggal dan angka angka

·         Konversi tipe data tipe data kolom

  Ada dua tipe dari fungsi fungsi

·         Single rows functions (fungsi fungsi baris tunggal)

·         Multiple row functions (fungsi fungsi banyak baris)

  Single Row Functions

Fungsi fungsi ini hanya digunakan pada baris baris tunggal dan mengembalikan satu hasil per baris.

Ada tipe tipe berbeda dari singlerow functions. Single row function mencakup hal hal berikut:

·         Character (karakter)

·         Number (angka)

·         Date (tanggal)

·         Conversion (konversi)

·         General (umum)

·          

  Multiple row 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

  Single Rows Functions

Singlerow function digunakan untuk memanipulasi item item data. Singlerow functions menerima satu atau lebih argument argument 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 argument argumen

·         Dapat digunakan didalam klausa klausa SELECT, WHERE, dan ORDER BY;dapat disarangkan(nested)

  Dalam sintak :

function_name adalah nama dari fungsi arg1, arg2 adalah setiap argumen yang digunakan oleh fungsi. Hal ini bisa jadi diwakili oleh suatu kolom atau ekspresi. Cakupan single row function:

·         Character Functions : Menerima input karakter dan dapat mengembalikan baik nilai nilai karakter ataupun angka.

·         Number Functions : Menerima masukkan angka dan mengembalikan nilai nilai angka

·         Date Functions : 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.)

·         Coversion Functions : Mengkonversi suatu nilai dari satu tipe data ke tipe data lainnya.

·         General Functions (fungsi fungsi umum) :

NVL

NVL

2

NULLIF

COALESCE

CASE

DECODE

  Character Functions

Singlerow character functions menerima data karakter sebagai masukan dan dapat mengembalikan baik nilai nilai karakter maupun angka. Character functions dapat dibagi menjadi :

·         Case manipulation functions

·         Characte rmanipulation Functions

LOWER(column/expression) : Mengkonversi nilai nilai karakter huruf menjadi lowercase (huruf kecil)

UPPER(column/expression) : Mengkonversi nilai nilai karakter huruf menjadi uppercase (huruf besar)

INITCAP(column/expression) : Mengkonversi nilai nilai alpha karakter menjadi uppercase untuk huruf pertama dari tiap kata; semua huruf huruf lain lowercase

CONCAT(column1/expressio1,colum2/expression2) : Menggabungkan nilai karakter pertama ke karakter kedua : sama dengan operator penggabungan ( || )

SUBSTR (column/expression,m[,n]) : Menghasilkan karakter karakter tertentu dari nilai karakter

dimulai pada posisi karakter kem,ken panjang karakter (jika m adalah negatif, dihitung mulai dari akhir nilai karakter. Jika n dihilangkan, menghasilkan semua karakter sampai akhir dari rangkaian.)

LENGTH (column/expression) : Mengembalikan jumlah karakter dalam ekspresi

INSTR (column/expression, 'string', [,m], [n]) : Mengembalikan posisi numerik dari suatu rangkaian penamaan. Secara optional, Anda dapat menyediakan suatu posisi kem untuk memulai pencarian, dan yang terjadi din dari suatu rangkaian. m dan n defaultnya 1, artinya pencarian dimulai di awal suatu pencarian dan melaporkan kejadian yang pertama.

LPAD (column /expression, n,'string') : Mengisi nilai karakter perataan kanan (rightjustified) ke suatu lebar total n posisi karakter

RPAD (column /expression, n,'string'): Mengisi nilai karakter perataan kiri (leftjustified) ke suatu lebar total n posisi karakter

TRIM (leading/trailing/both,trim_character FROM trim_source) : Memungkinkan Anda untuk memotong karakter karakter bagian awal atau bagian akhir atau kedua duanya dari suatu rangkaian karakter. Jika trim_character atau trim_source adalah suatu karakter literal, Anda harus mengapitnya dengan tanda petik tunggal. Ini adalah fitur yang ada di Oracle8i dan versi selanjutnya.

REPLACE (text, search_string, replacement_string): Mencari suatu ekspresi teks untuk suatu rangkaian karakter dan, jika ditemukan, digantikan dengan rangkaian yang telah ditentukan.

 Contoh:

• LOWER (‘SQL Course’)  sql course
• UPPER (‘SQL Course’)  SQL COURSE
• INITCAP (‘SQL Course’)  Sql Course
• CONCAT(‘Halo’, ‘Dunia’)  HaloDunia
• SUBSTR (‘HaloDunia’,1,4)  Halo
• LENGTH(‘HaloDunia’)  9
• INSTR(‘HaloDunia’, ‘D’)  5
• LPAD (salary,10,’*’)  *****24000
• RPAD (salary,10,’*’)  24000*****
• REPLACE (‘JACK and JUE’, ‘J’, ‘BL’)  BLACK and BLUE
• TRIM(‘H’ FROM ‘HaloDunia’)  aloDunia

ROUND (column | expression, n) : Membulatkan kolom, ekspresi, atau nilai posisi ken desimal atau, jika n dihilangkan, tidak  ada posisi desimal ( Jika n adalah negatif, angka angka di kiri dari desimal dibulatkan.)

TRUNC (column | expression, n) : Memotong kolom, ekspresi, atau nilai posisi ken desimal atau, jika n dihilangkan, n defaultnya nol

MOD (m, n) : Mengembalikan sisa dari m yang dibagi oleh n

Contoh:

ROUND (45.926, 2)  45.93

TRUNC (45.926, 2)  45.92

MOD (1600, 300)  100

  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 DD-MON-RR. Tanggal Oracle berlaku antara January 1,4712 B.C. (1 Januari 4712 S.M.) dan December 31, 9999 A.D. (31 Desember 9999 M.). Contoh:

SELECT last_name, hire_date

FROM employee

WHERE hire_date < ’01-FEB-88’;

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

  Aritmatika pada Dates

·         Menambah atau mengurangkan suatu angka ke atau dari suatu tanggal yang menghasilkan nilai date.

·         Mengurangkan dua tanggal untuk mencari angka dari hari hari antara tanggal tanggal tersebut.

·         Menambah jam ke suatu tanggal dengan membagi suatu angka dari jam dengan 24.

  Dates Functions

Date function bekerja pada tanggal –tanggal Oracle. Semua date function mengembalikan suatu nilai dengan tipe data DATE kecuali MONTHS_BETWEEN,yang mengembalikan nilai numerik.

·         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_DAY (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. Jika model format fmt dihilangkan , date dipotong ke hari terdekat.

  Conversion Functions

Disamping tipe data tipe data Oracle, kolom kolom dari table table di dalam Oracle dapat

didefinisikan menggunakan tipe data tipe data ANSI, DB2, dan SQL/DS. Meskipun demikian, server Oracle secara internal merubah tipe data tipe data yang sama dengan tipe data tipe data Oracle.

Dalam beberapa kasus, server Oracle menggunakan data dari suatu tipe data dimana server Oracle memperkirakan data dari suatu tipe data yang lain. Ketika ini terjadi, server Oracle dapat secara otomatis mengubah suatu data ke tipe data yang diperkirakan. Perubahan tipe data ini bisa jadi dilakukan secara implisit (implicitly) oleh server Oracle atau secara eksplisit (explicitly) oleh user.

Konversi konversi tipe data implisit bekerja menurut aturan aturan yang dijelaskan dalam slide berikutnya.

Konversi konversi tipe data eksplisit dilakukan dengan menggunakan conversion functions.

Conversion functions merubah suatu nilai dari suatu tipe data ke tipe data lain. Umumnya, bentuk dari nama nama fungsi mengikuti konvensi (kesepakatan) data type TO data type. Tipe data yang pertama adalah tipe data input;tipe data yang kedua adalah output.

Catatan : Meskipun disediakan konversi tipe data implisit, Anda diijinkan melakukan konversi tipe data eksplisit untuk memastikan kebenaran pernyataan SQL Anda.

  Konversi Tipe Data Implisit

Untuk penugasan penugasan, server Oracle dapat secara otomatis mengkonversi berikut ini :

Dari varchar2 ke number

Dari varchar2 ke date

Dari number ke varchar2

Dari date ke varchar2

PERMASALAHAN

1.      Tampilkan lastname, firstname, tanggalbirthdate, bulanbirthdate, dan tahunbirthdate untuk employee yang memiliki bulan lahir lebih dari Maret. 

2.      Tampilkan lastname, firstname, birthdate, usiatahunini, dan nextusia dari tabel employee.

3.      Tampilkan productid, productname dan unitprice, dimana unitprice adalah diantara 2 buah inputan user. (batas awal, dan batas akhir).

4.      Tampilkan productid, productname, unitprice, dan unitprice after discount. Dengan syarat sebagai berikut,

unitprice 1 - 5, maka discount 10%.

unitprice 6 - 10, maka discount 15%.

unitprice 11 - 15, maka discount 20%.

unitprice 16 - 20, maka discount 25%.

unitprice 21 - 25, maka discount 30%.

Gunakan (case).

contoh unitprice = 18, maka harga setelah diskon adalah 13 

5.      Tampilkan contactname customers, hanya nama depannya saja.

Misal : Maria Anders, menjadi : Maria. 

6.      Menampilkan firstname, lastname, bulanbirthdate dimana bulan birthdatenya adalah bulan saat ini (april).

BAB III

PENYELESAIAAN

1.         SELECT LASTNAME, FIRSNAME, TO_CHAR(BIRTHDATE,’DD’), TO_CHAR(BIRTHDATE,’MON’), TO_CHAR(BIRTHDATE,’YYYY’)

            FROM EMPLOYEES           

            WHERE TO_CHAR(BIRTHDATE,’MON’) NOT IN(‘JAN’,’PEB’,’MAR’);

2.         SELECT  LASTNAME, FIRSTNAME, BIRTHDATE, ROUND(TO_CHAR(SYSDATE-BIRTHDATE)/365,0) AS “USIA TAHUN INI”, ROUND(TO_CHAR(SYSDATE-BIRTHDATE)/365,0)+1 AS “NEX USIA”

            FROM EMPLOYEES;

3.         SELECT PRODUCTID, PRODUCTNAME, UNITPRICE

            FROM EMPLOYEES

            WHERE UNITPRICE BETWEEN &UNITPRICE1 AND &UNITPRICE2;

4.         SELECT PRODUCTID, PRODUCTNAME, UNITPRICE

            CASE WHEN UNITPRICE <=5 THEN 0.10*UNITPRICE

            WHEN UNITPRICE <=5 THEN 0.15*UNITPRICE

            WHEN UNITPRICE <=5 THEN 0.20*UNITPRICE

            WHEN UNITPRICE <=5 THEN 0.25*UNITPRICE

            WHEN UNITPRICE <=5 THEN 0.30*UNITPRICE

            ELSE UNITPRICE END “UNITPRICE AFTER DISCOUNT”

FROM PRODUCTS;

5.         SELECT SUBSTR(CONTACTNAME,1, INSTR(CONTCATNAME,’ ‘))

            FROM CUSTOMERS;

6.         SELECT FIRSTNAME, LASTNAME, TO_CHAR(BIRTHDATE,’MONTH’)

FROM EMPLOYEES

WHERE TO_CHAR(BIRTHDATE, ‘MON’) = TO_CHAR(SYSDATE,’MON’);