SQL> --CONTOH SINGLE ROW

SQL> SELECT ename "Data",

2 initcap(ename) "Hasil"
3 FROM emp
4 WHERE deptno=20;

Data Hasil
---------- ----------
SMITH Smith
JONES Jones
SCOTT Scott
ADAMS Adams
FORD Ford

SQL> --CONTOH MULTI ROW

SQL> SELECT sal
2 FROM emp
3 WHERE deptno=20;

SAL
----------
800
2975
3000
1100
3000

SQL> SELECT SUM(sal)

2 FROM emp
3 WHERE deptno=20;

SUM(SAL)
----------
10875

SQL>
SQL>
SQL>
SQL> --CONTOH TABEL DUAL
SQL> SELECT sysdate
2 FROM dual;

SYSDATE
---------
01-AUG-17

SQL>
SQL>
SQL>
SQL> --CONTOH LOWER, INITCAP, UPPER
SQL> SELECT ename "data",
2 lower(ename) "ENAME",
3 .
SQL>
SQL> --CONTOH LOWER, INITCAP, UPPER
SQL> SELECT ename "data",
2 lower(ename) "LOWER",
3 initcap(ename) "INITCAP",
4 upper(ename) "UPPER"
 5 FROM emp;

data LOWER INITCAP UPPER

---------- ---------- ---------- ----------
SMITH smith Smith SMITH
ALLEN allen Allen ALLEN
WARD ward Ward WARD
JONES jones Jones JONES
MARTIN martin Martin MARTIN
BLAKE blake Blake BLAKE
CLARK clark Clark CLARK
SCOTT scott Scott SCOTT
KING king King KING
TURNER turner Turner TURNER
ADAMS adams Adams ADAMS

data LOWER INITCAP UPPER

---------- ---------- ---------- ----------
JAMES james James JAMES
FORD ford Ford FORD
MILLER miller Miller MILLER

14 rows selected.

SQL>
SQL> --memanfaatkan case-manipulation untuk pencarian data
SQL> SELECT ename, sal
2 FROM emp
3 WHERE ename='Adams';

no rows selected

SQL> SELECT ename, sal

2 FROM emp
3 WHERE initcap(ename)='Adams';

ENAME SAL
---------- ----------
ADAMS 1100

SQL>
SQL>
SQL> --FUNCTION CONCAT(x,y)
SQL> --MENGGABUNGKAN STRING X DAN Y
SQL> --INI SAMA SAJA DENGAN OPERATOR "||"
SQL> SELECT ename, sal, CONCAT(ename,sal) "Concat", ename||sal "Opr ||"
2 FROM emp
3 WHERE deptno=20;

ENAME SAL Concat

---------- ---------- --------------------------------------------------
Opr ||
--------------------------------------------------
SMITH 800 SMITH800
SMITH800

JONES 2975 JONES2975

JONES2975
SCOTT 3000 SCOTT3000
SCOTT3000

ENAME SAL Concat

---------- ---------- --------------------------------------------------
Opr ||
--------------------------------------------------
ADAMS 1100 ADAMS1100
ADAMS1100

FORD 3000 FORD3000

FORD3000

SQL> COLUMN "Concat" FORMAT A20

SQL> COLUMN "Opr ||" FORMAT A20
SQL> /

ENAME SAL Concat Opr ||

---------- ---------- -------------------- --------------------
SMITH 800 SMITH800 SMITH800
JONES 2975 JONES2975 JONES2975
SCOTT 3000 SCOTT3000 SCOTT3000
ADAMS 1100 ADAMS1100 ADAMS1100
FORD 3000 FORD3000 FORD3000

SQL> SELECT CONCAT((CONCAT(ename,sal),empno) FROM emp WHERE deptno=20;

SELECT CONCAT((CONCAT(ename,sal),empno) FROM emp WHERE deptno=20
*
ERROR at line 1:
ORA-00907: missing right parenthesis

SQL> SELECT CONCAT(CONCAT(ename,sal),empno) FROM emp WHERE deptno=20;

CONCAT(CONCAT(ENAME,SAL),EMPNO)
--------------------------------------------------------------------------------
SMITH8007369
JONES29757566
SCOTT30007788
ADAMS11007876
FORD30007902

SQL> SELECT CONCAT(CONCAT(ename,sal),ename) FROM emp WHERE deptno=20;

CONCAT(CONCAT(ENAME,SAL),ENAME)
------------------------------------------------------------
SMITH800SMITH
JONES2975JONES
SCOTT3000SCOTT
ADAMS1100ADAMS
FORD3000FORD

SQL> SELECT ename||sal||ename FROM emp WHERE deptno=20;

ENAME||SAL||ENAME
------------------------------------------------------------
SMITH800SMITH
JONES2975JONES
SCOTT3000SCOTT
ADAMS1100ADAMS
FORD3000FORD

SQL>
SQL> --SUBSTRING(x,y,z)
SQL> --dari string x tampilkan posisi y sebanyak z
SQL> --jika z tidak disebutkan maka tampilkan dari posisi y sampai akhir
SQL>
SQL> SELECT ename "Data",
2 SUBSTRING(ename,2,3) "Substring"
3 FROM emp
4 WHERE deptno=20;
SUBSTRING(ename,2,3) "Substring"
*
ERROR at line 2:
ORA-00904: "SUBSTRING": invalid identifier

SQL> SELECT ename "Data",

2 SUBSTR(ename,2,3) "Substring"
3 FROM emp
4 WHERE deptno=20;

Data Sub
---------- ---
SMITH MIT
JONES ONE
SCOTT COT
ADAMS DAM
FORD ORD

SQL> SELECT ename "Data",

2 right(substr(ename,2,3),3) "Substring"
3 From emp
4 WHERE deptno=20;
right(substr(ename,2,3),3) "Substring"
*
ERROR at line 2:
ORA-00904: "RIGHT": invalid identifier

SQL> SELECT ename "Data",

2 SUBSTR(ename,-4,3) "Substring"
3 From emp
4 WHERE deptno=20;

Data Sub
---------- ---
SMITH MIT
JONES ONE
SCOTT COT
ADAMS DAM
FORD FOR

SQL>
SQL>
SQL> --LENGHT(x)
SQL> --jumlah karakter dari string x
SQL>
SQL> SELECT ename "Data",
2 LENGHT(ename) "Lenght"
3 From emp
4 WHERE deptno=20;
LENGHT(ename) "Lenght"
*
ERROR at line 2:
ORA-00904: "LENGHT": invalid identifier

SQL> SELECT ename "Data",

2 LENGth(ename) "Length"
3 From emp
4 WHERE deptno=20;

Data Length
---------- ----------
SMITH 5
JONES 5
SCOTT 5
ADAMS 5
FORD 4

SQL>
SQL>
SQL> --INSTR(x,y)
SQL> --POSISI KARAKTER Y DI STRING X
SQL>
SQL> SELECT ename "Data",
2 INSTR(ename,'A') "Posisi A"
3 From emp
4 WHERE deptno=20;

Data Posisi A
---------- ----------
SMITH 0
JONES 0
SCOTT 0
ADAMS 1
FORD 0

SQL> SELECT ename "Data",

2 INSTR(ename,'A%A') "Posisi A"
3 From emp;

Data Posisi A
---------- ----------
SMITH 0
ALLEN 0
WARD 0
JONES 0
MARTIN 0
BLAKE 0
CLARK 0
SCOTT 0
KING 0
TURNER 0
ADAMS 0

Data Posisi A
---------- ----------
JAMES 0
FORD 0
MILLER 0

14 rows selected.

SQL> SELECT ename "Data",

2 INSTR(ename,'A'%'A') "Posisi A"
3 From emp;
INSTR(ename,'A'%'A') "Posisi A"
*
ERROR at line 2:
ORA-00911: invalid character

SQL> SELECT ename "Data",

2 INSTR(ename,LIKE'A%A') "Posisi A"
3 From emp;
INSTR(ename,LIKE'A%A') "Posisi A"
*
ERROR at line 2:
ORA-00936: missing expression

SQL> SELECT ename "Data",

2 INSTR(ename,(LIKE'A%A')) "Posisi A"
3 From emp;
INSTR(ename,(LIKE'A%A')) "Posisi A"
*
ERROR at line 2:
ORA-00936: missing expression

SQL> SELECT ename "Data",

2 INSTR(ename,LIKE'%A%A%') "Posisi A"
3 From emp;
INSTR(ename,LIKE'%A%A%') "Posisi A"
*
ERROR at line 2:
ORA-00936: missing expression

SQL> SELECT ename "Data",

2 INSTR(ename,(LIKE'%A%A%')) "Posisi A"
3 From emp;
INSTR(ename,(LIKE'%A%A%')) "Posisi A"
*
ERROR at line 2:
ORA-00936: missing expression

SQL> SELECT ename "Data",

2 INSTR(ename,'A',1,2) "Posisi A"
3 From emp;
Data Posisi A
---------- ----------
SMITH 0
ALLEN 0
WARD 0
JONES 0
MARTIN 0
BLAKE 0
CLARK 0
SCOTT 0
KING 0
TURNER 0
ADAMS 3

Data Posisi A
---------- ----------
JAMES 0
FORD 0
MILLER 0

14 rows selected.

SQL>
SQL> --RPAD(x,y,z)
SQL> --UNTUK STRING X PESAN SEBANYAK Y DIGIT, JIKA MASIH ADA SISA(PADDING)
SQL> --LETAKAN DI SISI KANAN DAN GANTI KARAKTER PADDING DENGAN KARAKTER Z
SQL>
SQL> SELECT ename "Data",
2 RPAD(ename,10,'-') "RPAD",
3 LPAD(sal,10,'*') "LPAD",
4 sal
5 From emp;

Data RPAD LPAD SAL

---------- ---------- ---------- ----------
SMITH SMITH----- *******800 800
ALLEN ALLEN----- ******1600 1600
WARD WARD------ ******1250 1250
JONES JONES----- ******2975 2975
MARTIN MARTIN---- ******1250 1250
BLAKE BLAKE----- ******2850 2850
CLARK CLARK----- ******2450 2450
SCOTT SCOTT----- ******3000 3000
KING KING------ ******5000 5000
TURNER TURNER---- ******1500 1500
ADAMS ADAMS----- ******1100 1100

Data RPAD LPAD SAL

---------- ---------- ---------- ----------
JAMES JAMES----- *******950 950
FORD FORD------ ******3000 3000
MILLER MILLER---- ******1300 1300

14 rows selected.

SQL> SELECT ename "Data",

2 RPAD((ename,0,'-'),10,'-') "RPAD",
3 From emp;
RPAD((ename,0,'-'),10,'-') "RPAD",
 *
ERROR at line 2:
ORA-00907: missing right parenthesis

SQL> SELECT ename "Data",

2 RPAD((RPAD(ename,0,'-')),10,'-') "RPAD",
3 From emp;
From emp
*
ERROR at line 3:
ORA-00936: missing expression

SQL> SELECT ename "Data",

2 RPAD((ename,0,'-'),10,'-') "RPAD"
3 From emp;
RPAD((ename,0,'-'),10,'-') "RPAD"
*
ERROR at line 2:
ORA-00907: missing right parenthesis

SQL> SELECT ename "Data",

2 RPAD((RPAD(ename,0,'-')),10,'-') "RPAD"
3 From emp;

Data RPAD
---------- ----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS

Data RPAD
---------- ----------
JAMES
FORD
MILLER

14 rows selected.

SQL> SELECT ename "Data",

2 RPAD((RPAD(ename,10,'-')),10,'-') "RPAD"
3 From emp;

Data RPAD
---------- ----------
SMITH SMITH-----
ALLEN ALLEN-----
WARD WARD------
JONES JONES-----
MARTIN MARTIN----
BLAKE BLAKE-----
CLARK CLARK-----
SCOTT SCOTT-----
KING KING------
TURNER TURNER----
ADAMS ADAMS-----

Data RPAD
---------- ----------
JAMES JAMES-----
FORD FORD------
MILLER MILLER----

14 rows selected.

SQL> SELECT ename "Data",

2 RPAD((LPAD(ename,0,'-')),10,'-') "RPAD"
3 From emp;

Data RPAD
---------- ----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS

Data RPAD
---------- ----------
JAMES
FORD
MILLER

14 rows selected.

SQL> SELECT ename "Data",

2 RPAD((LPAD(ename,10,'-')),10,'-') "RPAD"
3 From emp;

Data RPAD
---------- ----------
SMITH -----SMITH
ALLEN -----ALLEN
WARD ------WARD
JONES -----JONES
MARTIN ----MARTIN
BLAKE -----BLAKE
CLARK -----CLARK
SCOTT -----SCOTT
KING ------KING
TURNER ----TURNER
ADAMS -----ADAMS
Data RPAD
---------- ----------
JAMES -----JAMES
FORD ------FORD
MILLER ----MILLER

14 rows selected.

SQL> --TRIM(Y FOM X)

SQL> --ambil karakter y dari string x
SQL> --jika y ada di depan atau di belakang
SQL>
SQL> SELECT'112121' "Data",
2 TRIM ('1' FROM '112121') "TRIM 1"
3 FROM dual;

Data TRI
------ ---
112121 212

SQL> SELECT'112121' "Data",

2 LTRIM ('1' FROM '112121') "TRIM 1"
3 FROM dual;
LTRIM ('1' FROM '112121') "TRIM 1"
*
ERROR at line 2:
ORA-00907: missing right parenthesis

SQL> SELECT'112121' "Data",

2 LTRIM ('1' '112121') "TRIM 1"
3 FROM dual;
LTRIM ('1' '112121') "TRIM 1"
*
ERROR at line 2:
ORA-00907: missing right parenthesis

SQL> SELECT'112121' "Data",

2 LTRIM ('112121''1') "TRIM 1"
3 FROM dual;

Data TRIM 1
------ --------
112121 112121'1

SQL> SELECT'112121' "Data",

2 LTRIM ('112121','1') "TRIM 1"
3 FROM dual;

Data TRIM
------ ----
112121 2121

SQL> --REPLACE(Y FROM X)

SQL> --SALAH ULANGI LAGI
SQL>
SQL> --REPLACE(X,Y,Z)
SQL> --JIKA DI STRING X ADA KARAKTER Y MAKA DIGANTI Y DENGAN Z
SQL> SELECT'112121' "Data",
2 .
SQL> SELECT ename "Data",
2 REPLACE(ename,'A','_') "Replace"
3 From emp;

Data Replace
---------- ----------
SMITH SMITH
ALLEN _LLEN
WARD W_RD
JONES JONES
MARTIN M_RTIN
BLAKE BL_KE
CLARK CL_RK
SCOTT SCOTT
KING KING
TURNER TURNER
ADAMS _D_MS

Data Replace
---------- ----------
JAMES J_MES
FORD FORD
MILLER MILLER

14 rows selected.

SQL>
SQL> --FUNCTION NUMBER
SQL>
SQL> --ROUND(A,Y)
SQL> --UNTUK NUMBER A DIJADIKAN Y DESIMAL
SQL>
SQL> SELECT 49783 "Data",
2 ROUND(49783,2) "2 DSM1",
3 ROUND(49783,0) "0 DSM1",
4 ROUND(49783,-1) "-1 DSM1",
5 ROUND(49783,-2) "-2 DSM1"
6 from EMP;

Data 2 DSM1 0 DSM1 -1 DSM1 -2 DSM1

---------- ---------- ---------- ---------- ----------
49783 49783 49783 49780 49800
49783 49783 49783 49780 49800
49783 49783 49783 49780 49800
49783 49783 49783 49780 49800
49783 49783 49783 49780 49800
49783 49783 49783 49780 49800
49783 49783 49783 49780 49800
49783 49783 49783 49780 49800
49783 49783 49783 49780 49800
49783 49783 49783 49780 49800
49783 49783 49783 49780 49800

Data 2 DSM1 0 DSM1 -1 DSM1 -2 DSM1

---------- ---------- ---------- ---------- ----------
49783 49783 49783 49780 49800
 49783 49783 49783 49780 49800
49783 49783 49783 49780 49800

14 rows selected.

SQL> SELECT 49.783 "Data",

2 ROUND(49.783,2) "2 DSM1",
3 ROUND(49.783,0) "0 DSM1",
4 ROUND(49.783,-1) "-1 DSM1",
5 ROUND(49.783,-2) "-2 DSM1"
6 from DUAL;

Data 2 DSM1 0 DSM1 -1 DSM1 -2 DSM1

---------- ---------- ---------- ---------- ----------
49.783 49.78 50 50 0

SQL> --TRUNC(x,y)
SQL> --bilangan x dijadikan y desimal tanpa pembulatan
SQL> SELECT 49.783 "Data",
2 TRUNC(49.783,2) "2 DSM1",
3 TRUNC(49.783,1) "1 DSM1",
4 TRUNC(49.783,0) "0 DSM1",
5 TRUNC(49.783,-1) "-1 DSM1",
6 TRUNC(49.783,-2) "-2 DSM1",
7 FROM dual;
FROM dual
*
ERROR at line 7:
ORA-00936: missing expression

SQL> SELECT 49.783 "Data",

2 TRUNC(49.783,2) "2 DSM1",
3 TRUNC(49.783,1) "1 DSM1",
4 TRUNC(49.783,0) "0 DSM1",
5 TRUNC(49.783,-1) "-1 DSM1",
6 TRUNC(49.783,-2) "-2 DSM1"
7 FROM dual;

Data 2 DSM1 1 DSM1 0 DSM1 -1 DSM1 -2 DSM1

---------- ---------- ---------- ---------- ---------- ----------
49.783 49.78 49.7 49 40 0

SQL>
SQL> --MOD (X,Y)
SQL> --MENAMPILKAN SISA HASIL BAGI X TERHADAP X
SQL> SELECT mod(10,3)
2 FROM dual;

MOD(10,3)
----------
1

SQL> --sysdate untuk menampilkan tanggal hari ini

SQL> jika ingin lebih lengkap gunnakan current_timetamp
SP2-0734: unknown command beginning "jika ingin..." - rest of line ignored.
SQL> --sysdate untuk menampilkan tanggal hari ini
SQL> --jika ingin lebih lengkap gunnakan current_timetamp
SQL> SELECT sysdate FROM dua';
ERROR:
ORA-01756: quoted string not properly terminated

SQL> SELECT sysdate FROM dual;

SYSDATE
---------
01-AUG-17

SQL> SELECT current_timetamp FROM dual;

SELECT current_timetamp FROM dual
*
ERROR at line 1:
ORA-00904: "CURRENT_TIMETAMP": invalid identifier

SQL> SELECT current_timestamp FROM dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
01-AUG-17 11.09.57.180000 AM +07:00

SQL> --DATE + NUMBER --> DATE

SQL> --DATE - NUMBER --> DATE
SQL> --DATE - DATE --> NUMBER
SQL> --DATE + DATE --> ERROR
SQL> SELECT sysdate "Hari ini",
2 sysdate + 1 "besok",
3 sysdate - 1 "kemarin"
4 FROM dual
5 ;

Hari ini besok kemarin

--------- --------- ---------
01-AUG-17 02-AUG-17 31-JUL-17

SQL> SELECT sysdate -(sysdate - 1)

2 FROM dual;

SYSDATE-(SYSDATE-1)
-------------------
1

SQL> SELECT to_date('01-JUL-90','DD-MON-RR') - to_date('01-JAN-50','DD-MON-RR')

"LAMA"
2 FROM dual;

LAMA
----------
14791

SQL> SELECT to_date('01-AUG-17','DD-MON-RR') - to_date('01-JAN-50','DD-MON-RR')

"LAMA"
2 FROM dual;

LAMA
----------
24684
SQL> SELECT to_date('01-JUL-90','DD-MON-RR') - to_date('17-AUG-45','DD-MON-YY')
"LAMA"
2 FROM dual;

LAMA
----------
-20136

SQL> SELECT to_date('01-AUG-17','DD-MON-RR') - to_date('17-AUG-45','DD-MON-YY')

"LAMA"
2 FROM dual;

LAMA
----------
-10243

SQL> SELECT to_date('01-AUG-17','DD-MON-RR') - to_date('17-AUG-1945','DD-MON-YYYY')

"LAMA"
2 FROM dual;

LAMA
----------
26282

SQL> --MONTHS_BETWEEN(date1,date2)
SQL> jarak antar a date1 dan date2
SP2-0734: unknown command beginning "jarak anta..." - rest of line ignored.
SQL> --MONTHS_BETWEEN(date1,date2)
SQL> --jarak antar a date1 dan date2
SQL> SELECT MONTHS_BETWEEN('01-JUL-17','01-FEB-17') FROM dual;

MONTHS_BETWEEN('01-JUL-17','01-FEB-17')
---------------------------------------
5

SQL> --ADD_MONTHS(date,number)
SQL> --menambahkan number (dalam satuan bulan) ke tgl date
SQL> SELECT add_months(sysdate,6) "6bln" FROM dual;

6bln
---------
01-FEB-18

SQL> --NEXT_DAY(date,day_)
SQL> --hari day setelah tgl date jatuh pada tgl berapa?
SQL> SELECT next_day('01-JAN-26','sunday') from dual;

NEXT_DAY(
---------
04-JAN-26

SQL> SELECT next_day(to_date('01-JAN-0001','dd-mm-yyy'),'sunday') from dual;

SELECT next_day(to_date('01-JAN-0001','dd-mm-yyy'),'sunday') from dual
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
SQL> SELECT next_day((to_date('01-JAN-0001','dd-mon-yyyy')),'sunday') from dual;

NEXT_DAY(
---------
02-JAN-01

SQL> --ROUND(DATE, 'MONTHS')

SQL> --PERHATIKAN TANGGALNYA
SQL> JIKA <= MAKA AKAN DIBULATKAN KE AWAL BULAN DATE
SP2-0734: unknown command beginning "JIKA <= MA..." - rest of line ignored.
SQL> --JIKA <= MAKA AKAN DIBULATKAN KE AWAL BULAN DATE
SQL> --JIKA <= 15 MAKA AKAN DIBULATKAN KE AWAL BULAN DATE
SQL> --JIKA > 15 MAKA AKAN DIBULATKAN KE AWAL BULAN DATE
SQL> --JIKA > 15 MAKA AKAN DIBULATKAN KE AWAL BULAN SELANJUTNYA
SQL> SELECT ROUND(to_date('15-JUL-17','DD-MON-RR'),'MONTHS')
2 FROM dual;
SELECT ROUND(to_date('15-JUL-17','DD-MON-RR'),'MONTHS')
*
ERROR at line 1:
ORA-01821: date format not recognized

SQL> SELECT ROUND(to_date('15-JUL-17','DD-MON-RR'),'MONTH')

2 FROM dual;

ROUND(TO_
---------
01-JUL-17

SQL> SELECT ROUND(to_date('16-JUL-17','DD-MON-RR'),'MONTHS')

2 FROM dual;
SELECT ROUND(to_date('16-JUL-17','DD-MON-RR'),'MONTHS')
*
ERROR at line 1:
ORA-01821: date format not recognized

SQL> SELECT ROUND(to_date('16-JUL-17','DD-MON-RR'),'MONTH')

2 FROM dual;

ROUND(TO_
---------
01-AUG-17

SQL> --SAMA SEPERTI ROUND MONTH, JIKA <= TENGAH TAHUN MAKA ROUND YEAR AKAN
DIBULATKAN KE TAHUN DATE
SQL> --SAMA SEPERTI ROUND MONTH, JIKA > TENGAH TAHUN MAKA ROUND YEAR AKAN
DIBULATKAN KE TAHUN SELANJUTNYA
SQL> SELECT ROUND(to_date('16-JUL-17','DD-MON-RR'),'year')
2 FROM dual;

ROUND(TO_
---------
01-JAN-18

SQL> --trunc(date,'month')
SQL> --trunc akan memberi tahu awal bulan, TGL TIDAK MEMPERNGARUHI
SQL> --trunc(date,'YEAR')
SQL> --trunc akan memberi tahu awal TAHUN, BULAN TIDAK MEMPERNGARUHI
SQL> -----------------------------------
SQL> --FUNGSI KONVERSI
SQL> --TO_CHAR(date-->char)
SQL> --TO_CHAR(number-->char)
SQL>
SQL> TO_DATE(char-->date)
SP2-0734: unknown command beginning "TO_DATE(ch..." - rest of line ignored.
SQL> --TO_DATE(char-->date)
SQL> --TO_NUMBER(char-->number)
SQL>
SQL> INSERT INTO emp (empno, ename, hiredate)
2 VALUES(1000,'Toni','11-MAR-90');

1 row created.

SQL> --itu adalah contoh kenversi data dengan implisit atau dilakukan otomatis
aslkan sesuai dengan default
SQL>
SQL> INSERT INTO emp (empno, ename, hiredate)
2 VALUES(1000,'Toni',to_date('110490','ddmmrr'));
INSERT INTO emp (empno, ename, hiredate)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_EMP) violated

SQL> INSERT INTO emp (empno, ename, hiredate)

2 VALUES(1001,'Tono',to_date('110490','ddmmrr'));

1 row created.

SQL> --itu adalah cara konversi data dengan eksplisit yaitu user memasukan sendiri
data yang akan diubah
SQL>
SQL>
SQL>
SQL> SELECT TO_CHAR(sysdate,'Day DD, MONTH yyyy') "Tanggal"
2 FROM dual;

Tanggal
----------------------------
Tuesday 01, AUGUST 2017

SQL> --FORMAT NUMBER

SQL> --9 : untuk menampilkan angka
SQL> --0 : untuk menampilkan 0 di depan angka
SQL>
SQL> SELECT 786,
2 to_char(786,'099999') "Ada angka nol",
3 to_char(786,'999999') "Tidak ada angka nol"
4 FROM dual;

786 Ada ang Tidak a

---------- ------- -------
786 000786 786

SQL> SELECT 786,

2 to_char(786,'099999') "Ada nol",
3 to_char(786,'999999') "Tidak nol"
 4 FROM dual;

786 Ada nol Tidak n

---------- ------- -------
786 000786 786

SQL> SELECT to_char(13456784,'999,999,999,999') angka

2 FROM dual;

ANGKA
----------------
13,456,784

SQL> SELECT to_char(13456784,'999-999-999-999') angka

2 FROM dual;
SELECT to_char(13456784,'999-999-999-999') angka
*
ERROR at line 1:
ORA-01481: invalid number format model

SQL> SELECT to_char(13456784,'Rp''999,999,999,999') angka

2 FROM dual;
SELECT to_char(13456784,'Rp''999,999,999,999') angka
*
ERROR at line 1:
ORA-01481: invalid number format model

SQL> SELECT ename, sal, to_char(sal, sal/13000) "sal(Rp)" FROM emp;

ERROR:
ORA-01481: invalid number format model

no rows selected

SQL> SELECT ename, sal, to_char(sal, '$999,999') "sal(USD)" FROM emp;

ENAME SAL sal(USD)

---------- ---------- ---------
Toni
Tono
SMITH 800 $800
ALLEN 1600 $1,600
WARD 1250 $1,250
JONES 2975 $2,975
MARTIN 1250 $1,250
BLAKE 2850 $2,850
CLARK 2450 $2,450
SCOTT 3000 $3,000
KING 5000 $5,000

ENAME SAL sal(USD)

---------- ---------- ---------
TURNER 1500 $1,500
ADAMS 1100 $1,100
JAMES 950 $950
FORD 3000 $3,000
MILLER 1300 $1,300

16 rows selected.

SQL> SELECT ename, sal, to_char(sal, '$999,999') "sal(USD)", 'sal(USD)' * 13000

FROM emp;
SELECT ename, sal, to_char(sal, '$999,999') "sal(USD)", 'sal(USD)' * 13000 FROM emp
*
ERROR at line 1:
ORA-01722: invalid number

SQL> SELECT ename, sal, to_char(sal, '$999,999') "sal(USD)", "sal(USD)" * 13000

FROM emp;
SELECT ename, sal, to_char(sal, '$999,999') "sal(USD)", "sal(USD)" * 13000 FROM emp
*
ERROR at line 1:
ORA-00904: "sal(USD)": invalid identifier

SQL> SELECT ename, sal, to_char(sal, '$999,999') "sal(USD)",

2 (to_char(sal, '$999,999')*13000) "sal(IDR)"
3 FROM emp;
ERROR:
ORA-01722: invalid number

no rows selected

SQL> SELECT ename, sal, to_char(sal, '$999,999') "sal(USD)",

2 ((to_char(sal, '$999,999'))*13000) "sal(IDR)"
3 FROM emp;
ERROR:
ORA-01722: invalid number

no rows selected

SQL> ALTER SESSION SET NLS_CURRENCY='Rp';

Session altered.

SQL> SELECT ename, sal, to_char(sal, ) "sal(Rp)" FROM emp;FROM

2 .
SQL>
SQL> ALTER SESSION SET NLS_NUMERIC_CHARACTERS=',.';

Session altered.

SQL> SELECT ename, sal, to_char(sal, 'L999g999D99') "sal(Rp)" FROM emp;

ENAME SAL sal(Rp)

---------- ---------- ---------------------
Toni
Tono
SMITH 800 Rp800,00
ALLEN 1600 Rp1.600,00
WARD 1250 Rp1.250,00
JONES 2975 Rp2.975,00
MARTIN 1250 Rp1.250,00
BLAKE 2850 Rp2.850,00
CLARK 2450 Rp2.450,00
SCOTT 3000 Rp3.000,00
KING 5000 Rp5.000,00

ENAME SAL sal(Rp)

---------- ---------- ---------------------
TURNER 1500 Rp1.500,00
ADAMS 1100 Rp1.100,00
JAMES 950 Rp950,00
FORD 3000 Rp3.000,00
MILLER 1300 Rp1.300,00

16 rows selected.

SQL> --G untuk ribuan dalam uang

SQL> --D untuk desimal dalam uang
SQL> --jika 1 dolar = rp13000
SQL> --maka buatlah gaji dalam USD dan IDR
SQL> SELECT empno, ename,
2 to_char(sal, '$999,999,999.99') "Sal(USD)",
3 to_char(sal*13000, 'L999G999G999G999D99') "Sal(IDR)"
4 FROM emp;

EMPNO ENAME Sal(USD) Sal(IDR)

---------- ---------- ---------------- -----------------------------
1000 Toni
1001 Tono
7369 SMITH $800.00 Rp10.400.000,00
7499 ALLEN $1,600.00 Rp20.800.000,00
7521 WARD $1,250.00 Rp16.250.000,00
7566 JONES $2,975.00 Rp38.675.000,00
7654 MARTIN $1,250.00 Rp16.250.000,00
7698 BLAKE $2,850.00 Rp37.050.000,00
7782 CLARK $2,450.00 Rp31.850.000,00
7788 SCOTT $3,000.00 Rp39.000.000,00
7839 KING $5,000.00 Rp65.000.000,00

EMPNO ENAME Sal(USD) Sal(IDR)

---------- ---------- ---------------- -----------------------------
7844 TURNER $1,500.00 Rp19.500.000,00
7876 ADAMS $1,100.00 Rp14.300.000,00
7900 JAMES $950.00 Rp12.350.000,00
7902 FORD $3,000.00 Rp39.000.000,00
7934 MILLER $1,300.00 Rp16.900.000,00

16 rows selected.

SQL> --nested function

SQL> --buat email
SQL> empno + 4 huruf ename + domain
SP2-0734: unknown command beginning "empno + 4 ..." - rest of line ignored.
SQL> --empno + 4 huruf ename + domain
SQL> --ditulis dalam huruf kecil
SQL>
SQL>
SQL> SELECT ename,
2 lower(empno||(substr(ename,1,4))||'.unikom.ac.id') Email
3 FROM emp;

ENAME EMAIL
---------- ---------------------------------------------------------
Toni 1000toni.unikom.ac.id
Tono 1001tono.unikom.ac.id
SMITH 7369smit.unikom.ac.id
ALLEN 7499alle.unikom.ac.id
WARD 7521ward.unikom.ac.id
JONES 7566jone.unikom.ac.id
MARTIN 7654mart.unikom.ac.id
BLAKE 7698blak.unikom.ac.id
CLARK 7782clar.unikom.ac.id
SCOTT 7788scot.unikom.ac.id
KING 7839king.unikom.ac.id

ENAME EMAIL
---------- ---------------------------------------------------------
TURNER 7844turn.unikom.ac.id
ADAMS 7876adam.unikom.ac.id
JAMES 7900jame.unikom.ac.id
FORD 7902ford.unikom.ac.id
MILLER 7934mill.unikom.ac.id

16 rows selected.

SQL> SELECT ename,

2 lower(empno||(substr(ename,1,4))||'@unikom.ac.id') Email
3 FROM emp;

ENAME EMAIL
---------- ---------------------------------------------------------
Toni 1000toni@unikom.ac.id
Tono 1001tono@unikom.ac.id
SMITH 7369smit@unikom.ac.id
ALLEN 7499alle@unikom.ac.id
WARD 7521ward@unikom.ac.id
JONES 7566jone@unikom.ac.id
MARTIN 7654mart@unikom.ac.id
BLAKE 7698blak@unikom.ac.id
CLARK 7782clar@unikom.ac.id
SCOTT 7788scot@unikom.ac.id
KING 7839king@unikom.ac.id

ENAME EMAIL
---------- ---------------------------------------------------------
TURNER 7844turn@unikom.ac.id
ADAMS 7876adam@unikom.ac.id
JAMES 7900jame@unikom.ac.id
FORD 7902ford@unikom.ac.id
MILLER 7934mill@unikom.ac.id

16 rows selected.

SQL> SELECT ename,

2 lower((substr(ename,1,4))||'.unikom.ac.id') Email
3 FROM emp;
ENAME EMAIL
---------- -----------------
Toni toni.unikom.ac.id
Tono tono.unikom.ac.id
SMITH smit.unikom.ac.id
ALLEN alle.unikom.ac.id
WARD ward.unikom.ac.id
JONES jone.unikom.ac.id
MARTIN mart.unikom.ac.id
BLAKE blak.unikom.ac.id
CLARK clar.unikom.ac.id
SCOTT scot.unikom.ac.id
KING king.unikom.ac.id

ENAME EMAIL
---------- -----------------
TURNER turn.unikom.ac.id
ADAMS adam.unikom.ac.id
JAMES jame.unikom.ac.id
FORD ford.unikom.ac.id
MILLER mill.unikom.ac.id

16 rows selected.

SQL> SELECT ename,

2 lower((substr(ename,1,4))||'@unikom.ac.id') Email
3 FROM emp;

ENAME EMAIL
---------- -----------------
Toni toni@unikom.ac.id
Tono tono@unikom.ac.id
SMITH smit@unikom.ac.id
ALLEN alle@unikom.ac.id
WARD ward@unikom.ac.id
JONES jone@unikom.ac.id
MARTIN mart@unikom.ac.id
BLAKE blak@unikom.ac.id
CLARK clar@unikom.ac.id
SCOTT scot@unikom.ac.id
KING king@unikom.ac.id

ENAME EMAIL
---------- -----------------
TURNER turn@unikom.ac.id
ADAMS adam@unikom.ac.id
JAMES jame@unikom.ac.id
FORD ford@unikom.ac.id
MILLER mill@unikom.ac.id

16 rows selected.

SQL> SELECT ename,

2 lower(ename||'@unikom.ac.id') Email
3 FROM emp;

ENAME EMAIL
---------- -----------------------
Toni toni@unikom.ac.id
Tono tono@unikom.ac.id
SMITH smith@unikom.ac.id
ALLEN allen@unikom.ac.id
WARD ward@unikom.ac.id
JONES jones@unikom.ac.id
MARTIN martin@unikom.ac.id
BLAKE blake@unikom.ac.id
CLARK clark@unikom.ac.id
SCOTT scott@unikom.ac.id
KING king@unikom.ac.id

ENAME EMAIL
---------- -----------------------
TURNER turner@unikom.ac.id
ADAMS adams@unikom.ac.id
JAMES james@unikom.ac.id
FORD ford@unikom.ac.id
MILLER miller@unikom.ac.id

16 rows selected.

SQL> --general function adalah function yang berlaku untuk semua tipe data
SQL> --NVL(x,y)
SQL> JIKA X NULL MAKA MISALKAN X DENGAN Y
SP2-0734: unknown command beginning "JIKA X NUL..." - rest of line ignored.
SQL> -JIKA X NULL MAKA MISALKAN X DENGAN Y
SP2-0734: unknown command beginning "-JIKA X NU..." - rest of line ignored.
SQL> --JIKA X NULL MAKA MISALKAN X DENGAN Y
SQL> --dengan syarat tipe data harus sama
SQL> SELECT ename, NVL(to_char(comm), tidak ada) "Komisi" FROM emp;
SELECT ename, NVL(to_char(comm), tidak ada) "Komisi" FROM emp
*
ERROR at line 1:
ORA-00907: missing right parenthesis

SQL> SELECT ename, NVL(to_char(comm), "tidak ada") "Komisi" FROM emp;

SELECT ename, NVL(to_char(comm), "tidak ada") "Komisi" FROM emp
*
ERROR at line 1:
ORA-00904: "tidak ada": invalid identifier

SQL> SELECT ename, NVL(to_char(comm), 'tidak ada') "Komisi" FROM emp;

ENAME Komisi
---------- ----------------------------------------
Toni tidak ada
Tono tidak ada
SMITH tidak ada
ALLEN 300
WARD 500
JONES tidak ada
MARTIN 1400
BLAKE tidak ada
CLARK tidak ada
SCOTT tidak ada
KING tidak ada
ENAME Komisi
---------- ----------------------------------------
TURNER 0
ADAMS tidak ada
JAMES tidak ada
FORD tidak ada
MILLER tidak ada

16 rows selected.

SQL> SELECT ename, NVL(to_char(comm), 'tidak ada') "Komisi" FROM emp;

ENAME Komisi
---------- ----------------------------------------
Toni tidak ada
Tono tidak ada
SMITH tidak ada
ALLEN 300
WARD 500
JONES tidak ada
MARTIN 1400
BLAKE tidak ada
CLARK tidak ada
SCOTT tidak ada
KING tidak ada

ENAME Komisi
---------- ----------------------------------------
TURNER 0
ADAMS tidak ada
JAMES tidak ada
FORD tidak ada
MILLER tidak ada

16 rows selected.

SQL> --NVL2(x,y,z)
SQL> --JIKA X TIDAK NULL MAKA MUNCULKAN Y
SQL> --JIKA X NULL MAKA MUNCULKAN Z
SQL> SELECT ename,comm, NVL2(comm,'ada','tidak ada') "Keterangan" FROM emp;

ENAME COMM Keteranga

---------- ---------- ---------
Toni tidak ada
Tono tidak ada
SMITH tidak ada
ALLEN 300 ada
WARD 500 ada
JONES tidak ada
MARTIN 1400 ada
BLAKE tidak ada
CLARK tidak ada
SCOTT tidak ada
KING tidak ada

ENAME COMM Keteranga

---------- ---------- ---------
TURNER 0 ada
ADAMS tidak ada
JAMES tidak ada
FORD tidak ada
MILLER tidak ada

16 rows selected.

SQL> SELECT ename, NVL2(comm,'ada','tidak ada') "Komisi" FROM emp;

ENAME Komisi
---------- ---------
Toni tidak ada
Tono tidak ada
SMITH tidak ada
ALLEN ada
WARD ada
JONES tidak ada
MARTIN ada
BLAKE tidak ada
CLARK tidak ada
SCOTT tidak ada
KING tidak ada

ENAME Komisi
---------- ---------
TURNER ada
ADAMS tidak ada
JAMES tidak ada
FORD tidak ada
MILLER tidak ada

16 rows selected.

SQL> --NULLIF(X,Y)
SQL> --JIKA X DAN Y BERNILAI SAMA MAKA AKAN MENGHASILKAN NULL
SQL> --JIKA TIDAK SAMA MAKA MUNCULKAN X
SQL> SELECT NULLIF(10,4), nullif(10,10) FROM emp;

NULLIF(10,4) NULLIF(10,10)
------------ -------------
10
10
10
10
10
10
10
10
10
10
10

NULLIF(10,4) NULLIF(10,10)
------------ -------------
10
10
10
10
10
16 rows selected.

SQL> SELECT NULLIF(10,4), nullif(10,10) FROM dual;

NULLIF(10,4) NULLIF(10,10)
------------ -------------
10

SQL> --COALESCE(X,Y,Z,.....,N)
SQL> --JIKA X TIDAK NULL MAKA MUNCULKAN X
SQL> --JIKA X NULL MAKA MUNCULKAN Y
SQL> --JIKA SEBELUMNYA NULL MAKA MUNCULKAN YG TIDAK NULL
SQL> UPDATE emp SET sal=NULL WHERE ename IN('MARTIN','BLAKE');

2 rows updated.

SQL> SELECT ename, COALESCE(sal,comm,100) "coalesce

2 "
3 FROM emp;

ENAME coalesce

---------- ----------
Toni 100
Tono 100
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1400
BLAKE 100
CLARK 2450
SCOTT 3000
KING 5000

ENAME coalesce

---------- ----------
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300

16 rows selected.

SQL> SELECT ename, sal, comm, COALESCE(sal,comm,100) "coalesce"

2 FROM emp;

ENAME SAL COMM coalesce

---------- ---------- ---------- ----------
Toni 100
Tono 100
SMITH 800 800
ALLEN 1600 300 1600
WARD 1250 500 1250
JONES 2975 2975
MARTIN 1400 1400
BLAKE 100
CLARK 2450 2450
SCOTT 3000 3000
KING 5000 5000

ENAME SAL COMM coalesce

---------- ---------- ---------- ----------
TURNER 1500 0 1500
ADAMS 1100 1100
JAMES 950 950
FORD 3000 3000
MILLER 1300 1300

16 rows selected.

SQL> --FUNGSI YANG STRUKTURNYA MIRIF IF-THEN-ELSE

SQL> --CASE
SQL> --DECODE
SQL>
SQL>
SQL> --JIKA DEPTNO 10 MAKA NAIK 5%
SQL> -- DEPTNO 20 MAKA NAIK 10%
SQL> -- DEPTNO 30 MAKA NAIK 15%
SQL>
SQL> SELECT ename, deptno, sal "Old Salary",
2 CASE deptno WHEN 10 THEN sal*1.05
3 WHEN 20 THEN sal*1.1
4 WHEN 30 THEN sal*1.15
5 ELSE
6 sal
7 END "New Salary"
8 FROM emp
9 ORDER BY deptno;

ENAME DEPTNO Old Salary New Salary

---------- ---------- ---------- ----------
KING 10 5000 5250
CLARK 10 2450 2572,5
MILLER 10 1300 1365
FORD 20 3000 3300
SCOTT 20 3000 3300
ADAMS 20 1100 1210
JONES 20 2975 3272,5
SMITH 20 800 880
TURNER 30 1500 1725
MARTIN 30
WARD 30 1250 1437,5

ENAME DEPTNO Old Salary New Salary

---------- ---------- ---------- ----------
ALLEN 30 1600 1840
BLAKE 30
JAMES 30 950 1092,5
Toni
Tono

16 rows selected.

SQL> SELECT ename, deptno, sal "Old Salary",

 2 CASE deptno WHEN 10 THEN sal*1.05
3 WHEN 20 THEN sal*1.1
4 WHEN 30 THEN sal*1.15
5 ELSE
6 sal
7 END "New Salary"
8 FROM emp
9 ORDER BY deptno, "Old Salary";

ENAME DEPTNO Old Salary New Salary

---------- ---------- ---------- ----------
MILLER 10 1300 1365
CLARK 10 2450 2572,5
KING 10 5000 5250
SMITH 20 800 880
ADAMS 20 1100 1210
JONES 20 2975 3272,5
FORD 20 3000 3300
SCOTT 20 3000 3300
JAMES 30 950 1092,5
WARD 30 1250 1437,5
TURNER 30 1500 1725

ENAME DEPTNO Old Salary New Salary

---------- ---------- ---------- ----------
ALLEN 30 1600 1840
MARTIN 30
BLAKE 30
Toni
Tono

16 rows selected.

SQL> SELECT ename, deptno, sal "Old Salary",

2 DECODE(deptno,10,sal*1.05,20,sal*1.1,30,sal*1.15,sal) "New Salary"
3 FROM emp
4 ORDER BY deptno, "Old Salary";

ENAME DEPTNO Old Salary New Salary

---------- ---------- ---------- ----------
MILLER 10 1300 1365
CLARK 10 2450 2572,5
KING 10 5000 5250
SMITH 20 800 880
ADAMS 20 1100 1210
JONES 20 2975 3272,5
FORD 20 3000 3300
SCOTT 20 3000 3300
JAMES 30 950 1092,5
WARD 30 1250 1437,5
TURNER 30 1500 1725

ENAME DEPTNO Old Salary New Salary

---------- ---------- ---------- ----------
ALLEN 30 1600 1840
MARTIN 30
BLAKE 30
Toni
Tono
16 rows selected.

SQL> rollback;

Rollback complete.

SQL> --BAB 5 AGREGASI DATA (MULTI ROW FUNCTION)

SQL>
SQL> --COUNT() : MENGHITUNG JUMLAH DATA/BARIS
SQL> --SUM() : MENHITUNG TOTAL NILAI
SQL> --AVG() : MENGHITUNG RATA-RATA
SQL> --MIN() : MENCARI NILAI PALING KECIL
SQL> --MAX() : MENCARI NILAI PALING BESAR
SQL> --STDDEV():MENCARI NILAI STANDAR DEVIASI
SQL> --VARIANCE() : MENCARI NILAI VARIANCE
SQL>
SQL> --SUM,AVG,STDDEV,VARIANCE HANYA UNTUK NUMBER
SQL> --COUNT, MIN, MAX UNTUK SEMUA TIPE DATA
SQL>
SQL> SELECT count(empno) "jml data",
2 sum(sal) "Total gaji",
3 avg(sal) "Rata-rata ",
4 min(sal) "Gaji terkecil",
5 max(sal) "Gaji terbesar"
6 FROM emp;

jml data Total gaji Rata-rata Gaji terkecil Gaji terbesar

---------- ---------- ---------- ------------- -------------
14 29025 2073,21429 800 5000

SQL> --di single row function

SQL> --nilai null tidak bisa dihitung
SQL> --tetapi di multi row function null diabaikan
SQL> SELECT count(empno), count(*) FROM emp;

COUNT(EMPNO) COUNT(*)
------------ ----------
14 14

SQL> SELECT count(empno), count(*), count(comm) FROM emp;

COUNT(EMPNO) COUNT(*) COUNT(COMM)

------------ ---------- -----------
14 14 4

SQL> SELECT count(deptno), count(distinc depno) FROM emp;

SELECT count(deptno), count(distinc depno) FROM emp
*
ERROR at line 1:
ORA-00907: missing right parenthesis

SQL> SELECT count(deptno), count(distinc deptno) FROM emp;

SELECT count(deptno), count(distinc deptno) FROM emp
*
ERROR at line 1:
ORA-00907: missing right parenthesis
SQL> SELECT count(deptno), count(distinct deptno) FROM emp;

COUNT(DEPTNO) COUNT(DISTINCTDEPTNO)
------------- ---------------------
14 3

SQL> SELECT count(empno) "HItung" FROM emp;

HItung
----------
14

SQL> SELECT count(empno) "jml peg" FROM emp GROUP BY deptno;

jml peg
----------
6
5
3

SQL> SELECT deptno, count(empno) "jml peg" FROM emp GROUP BY deptno;

DEPTNO jml peg

---------- ----------
30 6
20 5
10 3

SQL> SELECT deptno, job, count(empno) "jml peg" FROM emp GROUP BY deptno, job ORDER
BY 1,2;

DEPTNO JOB jml peg

---------- --------- ----------
10 CLERK 1
10 MANAGER 1
10 PRESIDENT 1
20 ANALYST 2
20 CLERK 2
20 MANAGER 1
30 CLERK 1
30 MANAGER 1
30 SALESMAN 4

9 rows selected.

SQL> --BAGAIMANA MENYELEKSI HASIL DARI JOB FUNCTION????

SQL> --TAMPILKAN YG JUMLAH PEGAWAINYA >= 2?
SQL>
SQL> SELECT deptno, job, count(empno) "jml peg" FROM emp HAVING count(empno) >=2
GROUP BY deptno, job ORDER BY 1,2;

DEPTNO JOB jml peg

---------- --------- ----------
20 ANALYST 2
20 CLERK 2
30 SALESMAN 4

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> SELECT deptno, job, count(empno) "jml peg" FROM emp WHERE ename <> 'SCOTT'
HAVING count(empno) >=2 GROUP BY deptno, job ORDER BY 1,2;

DEPTNO JOB jml peg

---------- --------- ----------
20 CLERK 2
30 SALESMAN 4

SQL> SPOOL OFF