fungsi baris tunggal (basis data)

Fungsi Baris Tunggal

Fungsi baris tunggal atau single row functions adalah fungsi yang dapat kita gunakan

untuk setiap baris data. Fungsi-fungsi yang disediakan oleh Oracle adalah sebagai berikut:

1. Fungsi Karakter

2. Fungsi Angka

3. Fungsi Tanggal

4. Fungsi Konversi

5. Fungsi Lain

1. Fungsi Karakter

1.1. CHR(n)

Fungsi CHR() digunakan untuk menampilkan karakter dengan nilai biner n.

Perhatikan contoh berikut ini:

SELECT CHR(73)||CHR(78)||CHR(68)||CHR(82)||CHR(65) “Name”

FROM dual;

Name

INDRA

Catatan: Tabel ASCII lengkap dapat dilihat.

Tabel disebelah ASCII karakter dengan no 32-126

1.2. CONCAT(column|expression1, column|expression2)

Fungsi CONCAT() digunakan untuk menggabungkan column atau expression.

Fungsi ini sama dengan operator penggabungan (||). Perhatikan contoh berikut ini:

SELECT CONCAT(’Indra’, ‘ Armansyah’) “Name”

FROM dual;

SELECT CONCAT(first_name, CONCAT(’ ‘, last_name)) “Employee Name”

FROM employee;

1.3. INITCAP(column|expression)

Fungsi INITCAP() digunakan untuk mengkonversi huruf awal yang terdapat

pada suatu column atau expression menjadi huruf kapital (huruf besar). Perhatikan

contoh berikut ini:

SELECT INITCAP(’indra armansyah’) “Initcap”

FROM dual;

Initcap

Indra Armansyah

1.4. LENGTH(column|expression)

Fungsi LENGTH() digunakan untuk menghitung panjang dari column atau

expression. Perhatikan contoh berikut ini:

SELECT LENGTH(’Indra Armansyah’) “Length”

FROM dual;

Length

15

1.5. LOWER(column|expression)

Fungsi LOWER() digunakan untuk mengkonversi nilai-nilai yang terdapat pada

column atau expression menjadi huruf kecil. Perhatikan contoh berikut ini:

SELECT LOWER(’INDRA ARMANSYAH’) “Lowercase”

FROM dual;

Lowercase

indra armansyah

1.6. UPPER(column|expression)

Fungsi UPPER() digunakan untuk mengubah untuk mengkonversi nilai-nilai

yang terdapat pada column atau expression menjadi huruf besar. Perhatikan contoh

berikut ini:

SELECT UPPER(’indra armansyah’) “Uppercase”

FROM dual;

Uppercase

INDRA ARMANSYAH

1.7. NVL(column|expression1, column|expression2)

Fungsi NVL() digunakan untuk mengkonversi nilai null baik dari suatu column

maupun expression menjadi sebuah nilai baru yang kita inginkan. Perhatikan contoh

berikut ini:

SELECT id, last_name “Name”, NVL(commission_pct,0) “Commission”

FROM employee;

1.8. SUBSTR(column|expression, m, [n])

Fungsi SUBSTR() digunakan untuk mengambil n karakter dari column|

expression dimulai dari karakter ke-m. Perhatikan contoh berikut ini:

SELECT SUBSTR(’Indra Armansyah’,1,5) “Substr”

FROM dual;

Substr

Indra

SELECT SUBSTR(’Indra Armansyah’,-9) “Substr”

Substr

Armansyah

1.9. REPLACE(column|expression1, expression2, expression3)

Fungsi REPLACE() digunakan untuk mengganti suatu string yang terkandung

pada suatu string lain menjadi sebuah string baru yang kita inginkan. Perhatikan contoh

berikut ini:

SELECT REPLACE(’20-JUL-2002′,’2002′,’1902′) “Replace”

FROM dual;

Replace

20-JUL-1902

Pada contoh diatas kita mengganti string ‘2002′ yang terkandung di dalam ‘20-JUL-

2002′ menjadi ‘1902′ sehingga hasilnya menjadi ‘20-JUL-1902′.

2. Fungsi Angka

2.1. ROUND(column|expression[,n])

Fungsi ROUND digunakan untuk membulatkan nilai column atau expression

sebanyak n desimal di belakang koma. Perhatikan contoh berikut ini:

SELECT ROUND(19.74,1) “Round 1″, ROUND(19.75,1) “Round 2″

FROM dual;

Round 1 Round 2

19.7 19.8

2.2. TRUNC(column|expression[,n])

Fungsi TRUNC() digunakan untuk membulatkan kebawah atas nilai column atau

expression sebanyak n desimal di belakang koma. Perhatikan contoh berikut ini:

SELECT TRUNC(19.74,1) “Trunc 1″, TRUNC(19.75,1) “Trunc 2″

FROM dual;

Trunc 1 Trunc 2

19.7 19.7

2.3. MOD(m,n)

Fungsi MOD() digunakan untuk menampilkan sisa pembagian antara m dan n.

Fungsi ini menghasilkan m jika n adalah 0. Perhatikan contoh berikut ini

SELECT MOD(3,2), MOD(3,0)

FROM dual;

MOD(3,2) MOD(3,0)

1 3

2.4. POWER(m,n)

Fungsi POWER() digunakan untuk menampilkan hasil m pangkat n. Perhatikan

contoh berikut ini:

SELECT POWER(3,2)

FROM dual;

POWER(3,2)

9

3. Fungsi Tanggal

3.1. MONTHS_BETWEEN(date1, date2)

Fungsi MONTHS_BETWEEN() digunakan untuk menghitung selisih bulan

antara date1 dan date2. Perhatikan contoh berikut ini:

SELECT MONTHS_BETWEEN(sysdate,’01-OCT-02′)

FROM dual;

3.2. ADD_MONTHS(date, n)

Fungsi ADD_MONTHS() digunakan untuk menambahkan n bulan terhadap date.


SELECT ADD_MONTHS(’01-OCT-02′,2)

FROM dual;

3.3. NEXT_DAY(date,’char’)

Fungsi NEXT_DAY() digunakan untuk mencari tanggal dari hari pertama (char)

setelah date. Perhatikan contoh berikut ini:

SELECT NEXT_DAY(’01-OCT-02′,2)

FROM dual;

3.4. LAST_DAY(date)

Fungsi LAST_DAY() digunakan untuk mencari tanggal terakhir dari bulan date.


SELECT LAST_DAY(’01-OCT-01′)

FROM dual;

LAST_DAY(

31-OCT-01

Perhatikan bahwa tanggal terakhir dari bulan October adalah 31-OCT-01.

3.5. ROUND(date[,’fmt’])

Fungsi ROUND() digunakan untuk membulatkan date. Jika fmt tidak disebutkan

maka akan dibulatkan ke hari terdekat. Perhatikan contoh berikut ini:

SELECT ROUND(TO_DATE(’27-OCT-92′),’YEAR’) “Round Date”

FROM dual;

Round Date

01-JAN-93

3.6. SYSDATE

Fungsi SYSDATE digunakan untuk menampilkan tanggal dan jam sistem.


SELECT SYSDATE “Today”

FROM dual;

3.7. TRUNC(date[,’fmt’])

Fungsi TRUNC() digunakan untuk membulatkan date. Jika fmt tidak disebutkan

maka akan dibulatkan ke hari terdekat. Perhatikan contoh berikut ini:

SELECT TRUNC(TO_DATE(’27-OCT-92′),’YEAR’) “Trunc Date”

FROM dual;

Trunc Date

01-JAN-92

4. Fungsi Konversi

4.1. TO_CHAR(number|date[,’fmt’])

Fungsi TO_CHAR() digunakan untuk mengkonversi data bertipe number atau

date menjadi karakter. Perhatikan contoh berikut ini:

SELECT TO_CHAR(sysdate,’DD MONTH YYYY’)

FROM dual;

Jika kita asumsikan bahwa tanggal sistem adalah 01-OCT-01 maka hasil query

adalah sebagai berikut:

TO_CHAR(SYSDATE,’

01 OCTOBER 2001

4.2. TO_DATE(char[,’fmt’])

Fungsi TO_DATE() digunakan untuk mengkonversi data bertipe karakter

menjadi tanggal. Perhatikan contoh berikut ini:

SELECT TO_DATE(’01-OCT-02′,’DD-MON-YY’)

FROM dual;

TO_DATE(’

01-OCT-02

Fungsi TO_DATE berguna jika kita ingin menginputkan data atau mencari

berdasarkan data bertipe tanggal, namun format tanggal yang kita miliki berbeda dengan

format default yang ada (DD-MON-YY). Penjelasan lengkap dapat diikuti pada bagian

Interpretasi Tanggal.

4.3. TO_NUMBER(char)

Fungsi TO_NUMBER() digunakan untuk mengkonversi data bertipe karakter

menjadi angka. Perhatikan contoh berikut ini:

SELECT TO_NUMBER(’5′) * 5

FROM dual;

TO_NUMBER(’5′)*5

25

5. Fungsi Lain

5.1. GREATEST(expr1[,expr2]…)

Fungsi GREATEST() digunakan untuk mencari nilai terbesar dari beberapa


SELECT GREATEST(’HARRY’,'HARRIOT’,'HAROLD’) “Greatest”

FROM dual;

GREAT

HARRY

5.2. LEAST(expr1[,expr2]…)

Fungsi LEAST() digunakan untuk mencari nilai terkecil dari beberapa


SELECT LEAST(’HARRY’,'HARRIOT’,'HAROLD’) “Least”

FROM dual;

LEAST

HAROLD

5.3. USER

Fungsi USER digunakan untuk menampilkan nama user yang sedang terkoneksi

dengan database Oracle. Perhatikan contoh berikut ini:

SELECT USER, UID

FROM dual;

USER UID

INDRA 2367

5.4. USERENV(option)

Fungsi USERENV() digunakan untuk menampilkan environment pengguna.


SELECT USERENV(’LANGUAGE’) “Language”, USERENV(’TERMINAL’)

“Terminal”

FROM dual;

Language Terminal

AMERICAN_AMERICA.WE8MSWIN1252 HASTINAPURA

5.5. VSIZE()

Fungsi VSIZE(expr) digunakan untuk menampilkan jumlah byte dalam

representasi internal dari expr.

SELECT last_name “Name”, VSIZE(last_name) “Bytes”

FROM employee;

Name Bytes

Velasquez 9

Ngao 4

Nagayama 8

Interpretasi Tanggal

Kita perlu berhati-hati jika bekerja dengan data bertipe tanggal, karena Oracle

menginterpretasikan tahun sesuai dengan tahun pada tanggal sistem.

Data

Tahun

Siste

m

Interpreta

si

01-

OC

T-01

>=200

0

01-OCT-

2001

01-

OC

T-01

<

2000

01-OCT-

1901

Perhatikan contoh berikut:

CREATE TABLE tanggal( tanggal DATE );

Pastikan berapa sesungguhnya tahun sistem sekarang dengan menggunakan fungsi

TO_CHAR. Kemudian satu data ke dalam tabel TANGGAL, tahun yang kita maksud

disini adalah 1974, bukan 2074.

INSERT INTO tanggal VALUES(‘01-OCT-74′);

SELECT TO_CHAR(TANGGAL,’DD-MON-YYYY’)

FROM coba;

TANGGAL

01-OCT-2074

Oracle menginterpretasikan tahun 74 yang kita inputkan menjadi 2074

bukannya 1974. Mengapa demikian? Perhatikan kembali penjelasan pada tabel

mengenai interpretasi tanggal.

Kita dapat menggunakan dua alternatif pemecahan untuk mengatasi permasalahan ini:

1. Mengubah tanggal sistem dari sistem operasi sehingga memiliki tahun < 2000

(tidak disarankan)

2. Menggunakan fungsi TO_DATE

Pada dasarnya kita harus memberitahukan kepada Oracle dengan jelas berapa

sesungguhnya tahun yang kita maksud, 1974 atau 2074, agar data dapat

diinterpretasikan dengan benar. Perhatikan perintah insert yang sudah dimodifikasi

berikut:

INSERT INTO tanggal VALUES(TO_DATE(‘01-OCT-1974′,’DD-MON-YYYY’);

SELECT TO_CHAR(TANGGAL,’DD-MON-YYYY’)

FROM tanggal;

TANGGAL

01-OCT-1974

Format Tanggal

SCC atau CC : Abad.

YYYY atau SYYYY : Tahun dalam empat digit.

YYY atau YY atau Y : Tiga, dua, atau satu digit terakhir dari tahun.

Y,YYY : Tahun dengan koma pada posisi tersebut.

SYEAR atau YEAR : Tahun dalam ejaan.

Q : Kwartal dari tahun.

MM : Bulan dalam dua digit.

MONTH : Bulan dalam ejaan.

MON : Bulan dalam ejaan tiga huruf.

RM : Bulan dalam angka romawi.

WW atau W : Minggu ke sekian dalam tahun yang bersangkutan, dalam dua atau satu

digit.

DDD atau DD atau D : Hari ke sekian dalam tahun, bulan atau minggu yang

bersangkutan.

DAY : Hari dalam ejaan.

DY : Hari dalam ejaan tiga huruf.

Format Waktu

AM atau PM (A.M. atau P.M.) : Indikator meridian.

HH atau HH12 atau HH24 : Jam kesekian dalam hari yang bersangkutan, jam(1 - 12),

atau jam (0 - 23)

MI : Menit (0 - 59).

SS : Detik (0 - 59).

SSSSS : Detik ke sekian setelah tengah malam (0 - 86399).

Format Lain

/ . , : Memisahkan hasil konversi sesuai tampilan yang diinginkan.

” of the ” : Menambahkan karakter string ke dalam tampilan hasil konversi.

Akhiran

TH : Menampilkan urutan nomor (contoh, DDTH untuk 4 TH).

SP : Nomor dalam ejaan (contoh, DDSP untuk FOUR)

SPTH atau THSP : Menampilkan urutan nomor dalam ejaan (contoh, DDSPTH untuk

FOURTH).

Format Angka

9 : Posisi numerik (jumlah angka 9 menunjukkan maksimal panjang data yang dapat

ditampilkan). Contoh 999999 untuk hasil 1234

0 : Menambahkan awalan 0. Contoh 099999 untuk hasil 01234

$ : Menambahkan awalan dollar. Contoh $999999 untuk hasil $1234

L : Menambahkan simbol local currency. Contoh L999999 untuk hasil L1234

. : Menambahkan titik pada posisi tertentu. Contoh 999999.99 untuk hasil 1234.00

, : Menambahkan koma pada posisi tertentu. Contoh 999,999 untuk hasil 1,234

MI : Menambahkan tanda negatif di sebelah kanan angka. Contoh 999999MI

untuk hasil 1234-

PR : Contoh 999999PR untuk hasil <1234>

EEEE : Notasi untuk operasi scientific. Contoh 99.999EEEE untuk hasil 1,234E+03

V : Mengalikan dengan 10 n kali (n menunjukkanjumlah angka 9 setelah V). Contoh

9999V99 untuk hasil 123400

B : Menampilkan nilai nol sebagai blank. Contoh B9999,99 untuk hasil 1234,00

6. Latihan

Tampilkan nama lengkap pegawai dengan huruf kecil, inisial pegawai dengan huruf

depan kapital, dan jabatan pegawai dengan huruf kapital, khusus untuk pegawai dengan

jabatan vice president. Tampilkan nama lengkap pegawai dengan judul VP, inisial pegawai

dengan judul USERID, dan jagatan pegawai dengan judul TITLE.

SELECT LOWER(first_name | | ‘ ‘ | | last_name) VP, INITCAP(userid) USERID,

UPPER(title) TITLE

FROM employee

WHERE title LIKE ‘VP%’;

Tampilkan nama depan dan nama belakang pegawai yang memiliki nama belakang PATEL.

SELECT first_name, last_name

FROM employee

WHERE UPPER(last_name) = ‘PATEL’;

Tampilkan nama dan negara pelanggan dengan nilai kredit GOOD. Tampilkan nama

dan negara pelanggan dalam satu kolom dipisahkan spasi, beri judul CUSTOMER.

SELECT CONCAT(name, country) CUSTOMER

FROM customer;

Tampilkan nama produk dan panjang dari nama produk tersebut untuk produk yang

tiga huruf pertamanya adalah “Ace”.

SELECT name, LENGTH(name)

FROM product

WHERE SUBSTR(name,1,3) = ‘Ace’;

Tampilkan nama produk dan panjang dari nama produk tersebut untuk produk yang

tiga huruf terakhirnya adalah “Bar”.

SELECT name, LENGTH(name)

FROM product

WHERE SUBSTR(name,-3) = ‘Bar’

Ketikkan perintah berikut ini dan amatilah hasilnya.

SELECT ROUND(45.923,2), ROUND(45.923,1), ROUND(45.923,0), ROUND(45.923,-1),

ROUND(45.923,-2)

FROM SYS. DUAL;


SELECT TRUNC(45.923,2), TRUNC(45.923,1), TRUNC(45.923,0), TRUNC(45.923,-1),

TRUNC(45.923,-2)

FROM SYS. DUAL;

Tampilkan nama belakang, jabatan pegawai dan kode departemen dimana pegawai

tersebut bekerja khusus untuk pegawai yang bekerja pada departemen dengan kode genap.

SELECT last_name, title, dept_id

FROM employee

WHERE MOD(dept_id,2) = 0;

Tampilkan tanggal sistem.

SELECT sysdate

FROM sys.dual;

Tampilkan nama belakang dan masa kerja pegawai yang bekerja pada departemen

dengan kode 43, dengan ketentuan bahwa masa kerja adalah tanggal sejak pegawai tersebut

bekerja hingga sekarang. Tampilkan masa kerja tersebut dalam hitungan minggu dan beri

judul dengan WEEKS. Tampilkan urut berdasarkan nama belakang pegawai.

SELECT last_name, (SYSDATE - start_date) / 7 WEEKS

FROM employee

WHERE dept_id = 43;

Tampilkan kode pegawai, tanggal pegawai tersebut mulai bekerja, lama pegawai

tersebut bekerja hingga sekarang dalam hitungan bulan dengan judul TENURE, dan enam

bulan berikut sejak pegawai tersebut bekerja dengan judul REVIEW, khusus untuk pegawai

dengan masa kerja hingga sekarang lebih dari 100 bulan.

SELECT id, start_date, MONTHS_BETWEEN(SYSDATE, start_date)

TENURE, ADD_MONTHS(start_date,6) REVIEW

FROM employee

WHERE MONTHS_BETWEEN(SYSDATE, start_date) > 100;

Tampilkan kode pegawai, tanggal pegawai tersebut mulai bekerja, hari Jum’at

pertama setelah tanggal pegawai tersebut mulai bekerja, dan tanggal terakhir dari bulan

pegawai tersebut mulai bekerja, khusus untuk pegawai yang memiliki kode genap.

SELECT id, start_date, NEXT_DAY(start_date,’FRIDAY’), LAST_DAY(start_date)

FROM employee

WHERE MOD(id,2) = 0;


SELECT id, start_date,ROUND(start_date,’MONTH’) RM, TRUNC(start_date,’MONTH’)

TM,ROUND(start_date,’YEAR’) RY, TRUNC(start_date,’YEAR’) TY

FROM employee

WHERE start_date LIKE ‘%91′;

Tampilkan nomor order dan tanggal order untuk semua order yang ditangani oleh

sales representative dengan kode 11. Tampilkan tanggal order dengan format MM/YY dan

beri judul ORDERED.

SELECT ID, TO_CHAR(date_ordered,’MM/YY’) ORDERED

FROM orders

WHERE sales_rep_id = 11;

Tampilkan nama belakang pegawai dan tanggal pegawai tersebut mulai bekerja.

Untuk tanggal pegawai mulai bekerja tampilkan dengan format sehingga akan tampak seperti

7 of February 1991 dan beri judul HIREDATE.

COLUMN HIREDATE FORMAT A50

SELECT last_name, TO_CHAR(start_date,’fmDD “of” Month YYYY’)

HIREDATE

FROM employee


Tampilkan kembali soal diatas dimana tanggal pegawai mulai bekerja akan tampak

seperti Sevententh of February 1991 08:00:00 AM dan beri judul HIREDATE.

SELECT last_name, TO_CHAR(start_date,’fmDdspth “of” Month YYYY

fmHH:MI:SS AM’) HIREDATE

FROM employee


Tampilkan pesan yang mengindikasikan nomor dan total order, khusus untuk order

yang dikirimkan pada tanggal 21 September 1992. Tampilkan dengan judul NOTE.

SELECT ‘Order ‘ | | TO_CHAR(id) | | ‘ was filled for a total of ‘ | |

TO_CHAR(total,’fm$99,999,999′) NOTE

FROM orders

WHERE date_shipped = ‘21-SEP-92′;

Tampilkan nomor, total dan tanggal untuk order dengan tanggal September 7, 1992.

Konversilah string tanggal pada soal ini menjadi format tanggal, sehingga dapat dilakukan

pencarian terhadap data yang dimaksud.

SELECT id, total, date_ordered

FROM orders

WHERE date_ordered = TO_DATE(’7 September 1992′,’dd Month YYYY’);

Tampilkan gabungan nama belakang dan jabatan pegawai dengan judul Vice

President, khusus untuk pegawai dengan jabatan vice president, dengan ketentuan nama

belakang ditampilkan dengan huruf kapital. Hilangkan karakter VP pada jabatan.

SELECT CONCAT(UPPER(last_name), SUBSTR(title,3)) “Vice President”

FROM employee

WHERE title LIKE ‘VP%’;

Tampilkan nama belakang dari pimpinan perusahaan, yaitu pegawai yang tidak

memiliki atasan, sehingga akan tampak seperti:

BOSS

Velasquez has no manager

SELECT last_name | | ‘ ‘ | | ‘has no manager’ BOSS

FROM employee

WHERE manager_id IS NULL;

Tampilkan tanggal dari hari Jum’at enam bulan berikutnya setelah tanggal order.

Hasilnya akan tampak seperti : Friday, March 12th, 1993. Tampilkan dengan judul New 6

Month Review, dan urutkan berdasarkan tanggal order.

SELECT TO_CHAR(NEXT_DAY(ADD_MONTHS(date_ordered,6), ‘FRIDAY’), ‘fmDay,

Month ddth, YYYY’) “New 6 Month Review”

FROM orders

ORDER BY date_ordered;

Tampilkan nama belakang dan jabatan pegawai, sehingga tampak seperti berikut:

EMPLOYEE

Biri(Warehouse Manager)

Catchpole(Warehouse Manager)

……………………

SELECT last_name | | ‘(’ | | title | | ‘)’ EMPLOYEE

FROM employee

ORDER BY last_name;

Tampilkan nama belakang pegawai, tanggal pegawai tersebut mulai bekerja, tanggal

peninjauan gaji pegawai yang merupakan hari Senin pertama setelah enam bulan seorang

pegawai bekerja. Tampilkan tanggal peninjauan gaji dengan judul REVIEW dan dengan

format seperti Eight of May 1992.

COLUMN REVIEW FORMAT A50

SELECT last_name,start_date, TO_CHAR(NEXT_DAY(ADD_MONTHS(start_date,6),

‘MONDAY’),’fmDdspth “of” Month YYYY’) REVIEW

FROM employee

ORDER BY start_date;

Tampilkan nama belakang pegawai, tanggal dan hari pegawai tersebut mulai bekerja.

COLUMN DAY FORMAT A10

SELECT last_name, start_date, TO_CHAR(start_date,’fmDAY’) DAY

FROM employee;

Tampilkan gabungan nama belakang, gaji bulanan, dan gaji impian pegawai dengan

judul Dream Salaries, dengan catatan nama belakang pegawai ditampilkan dalam huruf

kapital, sedangkan gaji bulanan dan gaji impian ditampilkan dalam format $99,999. Gunakan

fungsi-fungsi pada latihan-latihan sebelumnya, dan tampilkan hasil query tersebut dengan

format:

<last_name> earns <salary> monthly but wants <3 * salary>

Contoh : ALLEN earns $1,100 monthly but wants $3,300.

SELECT upper(last_name) | | ‘ earns ‘ | | TO_CHAR(salary,’fm$9,999′) | |

‘ but wants ‘ | | TO_CHAR(salary*3,’fm$9,999′) “Dream Salaries”

FROM employee;

fungsi baris tunggal (basis data)

Documents