Bab 1 : Retrieving Data Using SQL SELECT Statement


Single-row Function
——————-
Berdasarkan tipe data yang diproses, sngle-row function dibagi :
– Fungsi Karakter
– Fungsi Number
– Fungsi Date
– Fungsi Konversi
– Fungsi General (Karakter, number, date)

Tabel DUAL

SELECT ….
FROM nama_tabel;

Jika suatu data tidak diambil dari tabel manapun maka gunakan tabel DUAL untuk melengkapi perintah SELECT

SELECT ….
FROM DUAL;

SQL> desc DUAL;
Name Null? Type
—————————————– ——– ————–
DUMMY VARCHAR2(1)

SQL> SELECT * FROM dual;

D

X

Contoh Pemakaian tabel DUAL

SELECT lower(‘LaB kOMPUTER’)
FROM dual;

SELECT ‘LaB kOMPUTER’ as “asli”,
lower(‘LaB kOMPUTER’) as “Lower”,
upper(‘LaB kOMPUTER’) as “Upper”,
initcap(‘LaB kOMPUTER’) as “Initcap”
FROM dual;

asli Lower Upper Initcap
———— ———— ———— ————
LaB kOMPUTER lab komputer LAB KOMPUTER Lab Komputer

SELECT ename,
lower(ename),
initcap(ename)
FROM EMP
WHERE DEPTNO=30;

ENAME LOWER(ENAM INITCAP(EN
———- ———- ———-
ALLEN allen Allen
WARD ward Ward
MARTIN martin Martin
BLAKE blake Blake
TURNER turner Turner
JAMES james James

CONCAT
——
Concatination ||
Untuk menggabungkan karakter.

SELECT ename, sal,
concat(ename,sal),
ename||sal,
FROM emp
WHERE deptno=30;

ENAME SAL CONCAT(ENAME,SAL)
———- ———- ———————-
ALLEN 1600 ALLEN1600
WARD 1250 WARD1250
MARTIN 1250 MARTIN1250
BLAKE 2850 BLAKE2850
TURNER 1500 TURNER1500
JAMES 950 JAMES950

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s