Data Mining Lab : DBMS LAB DAY 4

Numeric Functions

SQL> select abs(-5),abs(0),abs(5) from dual;

ABS(-5) ABS(0) ABS(5)

---------- ---------- ----------

5 0 5

SQL> select sign(-5),sign(0),sign(5) from dual;

SIGN(-5) SIGN(0) SIGN(5)

---------- ---------- ----------

-1 0 1

SQL> select power(2,3),power(2.5,1.6) from dual;

POWER(2,3) POWER(2.5,1.6)

---------- --------------

8 4.33215527

SQL> select sqrt(25),sqrt(2) from dual;

SQRT(25) SQRT(2)

---------- ----------

5 1.41421356

SQL> select sin(30),sin(30*3.14/180) from dual;

SIN(30) SIN(30*3.14/180)

---------- ----------------

-.98803162 .499770103

SQL> select asin(-0.9) from dual;

ASIN(-0.9)

----------

-1.1197695

SQL> select asin(1),sinh(4) from dual;

ASIN(1) SINH(4)

---------- ----------

1.57079633 27.2899172

SQL> select mod(3,4),mod(0,0) from dual;

MOD(3,4) MOD(0,0)

---------- ----------

3 0

SQL> select remainder(3,4) from dual;

REMAINDER(3,4)

--------------

-1

SQL> select remainder(13,4) from dual;

REMAINDER(13,4)

---------------

SQL> select exp(3) from dual;

EXP(3)

----------

20.0855369

SQL> select ln(2),log(10,100) from dual;

LN(2) LOG(10,100)

---------- -----------

.693147181 2

SQL> select ceil(-5.6),ceil(0),ceil(5.6) from dual;

CEIL(-5.6) CEIL(0) CEIL(5.6)

---------- ---------- ----------

-5 0 6

SQL> select floor(-5.6),floor(0),floor(5.6) from dual;

FLOOR(-5.6) FLOOR(0) FLOOR(5.6)

----------- ---------- ----------

-6 0 5

String Functions

SQL> select length('hello') from dual;

LENGTH('HELLO')

---------------

SQL> select lower('hello'),lower('HELLO'),lower('HeLLo') from dual;

LOWER LOWER LOWER

----- ----- -----

hello hello hello

SQL> select upper('hello'),upper('HELLO'),upper('HeLLo') from dual;

UPPER UPPER UPPER

----- ----- -----

HELLO HELLO HELLO

SQL> select initcap('hello') from dual;

INITC

-----

Hello

SQL> select reverse('hello') from dual;

REVER

-----

olleh

SQL> select concat('hello','world') from dual;

CONCAT('HE

----------

helloworld

SQL> select lpad('hello',10,'*') from dual;

LPAD('HELL

----------

*****hello

SQL> select rpad('hello',10,'*') from dual;

RPAD('HELL

----------

hello*****

SQL> select ltrim('****hello','*') from dual;

LTRIM

-----

hello

SQL> select rtrim('hello****','*') from dual;

RTRIM

-----

hello

SQL> select substr('helloworld',6) from dual;

SUBST

-----

world

SQL> select substr('helloworld',3,5) from dual;

SUBST

-----

llowo

SQL> select substr('helloworld',-3) from dual;

SUB

---

rld

SQL> select substr('helloworld',-3,2) from dual;

SQL> select substr('helloworld',-3,3) from dual;

SUB

---

rld

SQL> select substr('helloworld',-3,4) from dual;

SUB

---

rld

SQL> select instr('hello','l') from dual;

INSTR('HELLO','L')

------------------

SQL> select instr('helloworld','l',5) from dual;

INSTR('HELLOWORLD','L',5)

-------------------------

SQL> select instr('helloworld','l',-5) from dual;

INSTR('HELLOWORLD','L',-5)

--------------------------

SQL> select replace('hello','h','x') from dual;

REPLA

-----

xello

SQL> select replace('hello','he','xy') from dual;

REPLA

-----

xyllo

SQL> select replace('hello','he','x') from dual;

REPL

----

xllo

SQL> select replace('hello','hl','xy') from dual;

REPLA

-----

hello

SQL> select translate('hello','hl','xy') from dual;

TRANS

-----

xeyyo

SQL> select translate('hello','he','xy') from dual;

TRANS

-----

xyllo

SQL> select ascii('A') from dual;

ASCII('A')

----------

SQL> select chr(65) from dual;

SQL> select ename from emp where soundex(ename)=soundex('SMITH') ;

ENAME

----------

SMITH

SQL> select ename from emp where soundex(ename)=soundex('smith') ;

ENAME

----------

SMITH

SQL> select vsize('hello'),vsize('h'),vsize(1) from dual;

VSIZE('HELLO') VSIZE('H') VSIZE(1)

-------------- ---------- ----------

5 1 2

Aggregate Functions

SQL> select count(*) ,count(empno),count(comm) from emp;

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

---------- ------------ -----------

14 14 4

SQL> select sum(sal),avg(sal),max(sal),min(sal) from emp;

SUM(SAL) AVG(SAL) MAX(SAL) MIN(SAL)

---------- ---------- ---------- ----------

29025 2073.21429 5000 800

SQL> select variance(sal) from emp;

VARIANCE(SAL)

-------------

1398313.87

SQL> select stddev(sal) from emp;

STDDEV(SAL)

-----------

1182.50322

Other Functions

SQL> select least(10,0,-9,30) from dual;

LEAST(10,0,-9,30)

-----------------

-9

SQL> select greatest(10,0,-9,30) from dual;

GREATEST(10,0,-9,30)

--------------------

SQL> select trunc(5.6789),trunc(5.6789,3),trunc(5.6789,1) from dual;

TRUNC(5.6789) TRUNC(5.6789,3) TRUNC(5.6789,1)

------------- --------------- ---------------

5 5.678 5.6

SQL> select round(5.6789),round(5.6789,3),round(5.6789,1) from dual;

ROUND(5.6789) ROUND(5.6789,3) ROUND(5.6789,1)

------------- --------------- ---------------

6 5.679 5.7

Date and Functions

SQL> select months_between('10-aug-12','15-sep-13') from dual;

MONTHS_BETWEEN('10-AUG-12','15-SEP-13')

---------------------------------------

-13.16129

SQL> select add_months('10-aug-12',3) from dual;

ADD_MONTH

---------

10-NOV-12

SQL> select next_day('10-aug-12','thu') from dual;

NEXT_DAY(

---------

16-AUG-12

SQL> select last_day('10-aug-12') from dual;

LAST_DAY(

---------

31-AUG-12

SQL> SELECT TO_CHAR(

2 ADD_MONTHS(sysdate,1),

3 'DD-MON-YYYY') from dual;

TO_CHAR(ADD_MONTHS(S

--------------------

11-SEP-2012

SQL> select to_char(to_date('15-aug-12'),'dd-mon-yyyy') from dual;

TO_CHAR(TO_DATE('15-

--------------------

15-aug-2012

SQL> ALTER SESSION SET TIME_ZONE = '-5:0';

Session altered.

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;

SESSIONTIMEZONE

---------------------------------------------------------------------------

CURRENT_DATE

--------------------

-05:00

11-AUG-2012 01:48:04

SQL> select current_date from dual;

CURRENT_DATE

--------------------

11-AUG-2012 01:48:29

SQL> ALTER SESSION SET TIME_ZONE = '-5:0';

Session altered.

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;

SESSIONTIMEZONE CURRENT_TIMESTAMP

-----------------------------------------------------

-05:00

11-AUG-12 01.49.40.738000 AM -05:00

SQL> select dbtimezone from dual;

DBTIME

------

+00:00

Conversion Functions

SQL> select to_number('123') from dual;

TO_NUMBER('123')

----------------

123

SQL> select to_char(sysdate,'day month year') from dual;

TO_CHAR(SYSDATE,'DAYMONTHYEAR')

--------------------------------------------------------------------------------

friday august twenty twelve