IMPLEMENTATION OF BUILT-IN FUNCTIONS

DATE FUNCTIONS:

SQL> select sysdate from dual;
SYSDATE
---------
22-DEC-11
SQL> select last_day(sysdate) from dual;
LAST_DAY(
---------
31-DEC-11
SQL> select next_day(sysdate,'thursday') from dual;
NEXT_DAY(
---------
29-DEC-11
SQL> select greatest(sysdate,'15-DEC-11') from dual;
GREATEST(
---------
22-DEC-11
SQL> select greatest('20-DEC-11','10-DEC-11') from dual;
GREATEST(
---------
20-DEC-11
SQL> select greatest('Monday','Friday') from dual;
GREATE
------
Monday
SQL> select add_months(sysdate,3) from dual;
ADD_MONTH
---------
22-MAR-12
SQL> select last_day('12-FEB-11') from dual;
LAST_DAY(
---------
28-FEB-11
SQL> select sysdate,round(sysdate,'month')from dual;
SYSDATE ROUND(SYS
--------- ---------
22-DEC-11 01-JAN-12
SQL> select sysdate,round(sysdate,'year')from dual;
SYSDATE ROUND(SYS
--------- ---------
22-DEC-11 01-JAN-12

AGGREGATE FUNCTIONS:

SQL> create table table1(name char(20),roll int,mark int);
Table created.
SQL> select * from table1;
NAME
ROLL
MARK
-------------------- ---------- ----------
Abi
1
98
Pooja
2
89
Venu
3
91
3 rows selected.
SQL> select sum(mark) from table1;
SUM(MARK)
----------
278
SQL> select max(mark) from table1;
MAX(MARK)
----------
98
SQL> select min(mark) from table1;
MIN(MARK)
----------
89
SQL> select avg(mark) from table1;
AVG(MARK)
----------
92.6666667
SQL> select count(*) from table1;
COUNT(*)
----------
3
CHARACTER FUNCTIONS:

SQL> select initcap('good') from dual;
INIT
----
Good
SQL> select lower('GOOD') from dual;
LOWE
----
good

SQL> select upper('good')from dual;
UPPE
----
GOOD
SQL> select translate('good','g','h') from dual;
TRAN
----
hood
SQL> select replace('good','go','g') from dual;
REP
---
god
SQL> select substr('good',2,2) from dual;
SU
--
oo
SQL> select soundex('good')from dual;
SOUN
----
G300
SQL> select trim(' good ') from dual;
TRIM
----
good
SQL> select lpad('good','10','*')from dual;
LPAD('GOOD
----------
******good
SQL> select rpad('good','10','*')from dual;
RPAD('GOOD
----------
good******
SQL> select length('good')from dual;
LENGTH('GOOD')
--------------
4
NUMERIC FUNCTIONS

SQL> select abs(-15) from dual;
ABS(-15)
----------
15

SQL> select cos(90) from dual;
COS(90)
----------
-.44807362
SQL> select ceil(10.33) from dual;
CEIL(10.33)
-----------
11
SQL> select floor(10.57) from dual;
FLOOR(10.57)
------------
10
SQL> select exp(0) from dual;
EXP(0)
----------
1
SQL> select sqrt(4) from dual;
SQRT(4)
----------
2
MISCELLANEOUS FUNCTIONS:

SQL> select uid from dual;
UID
----------
462
SQL> select user from dual;
USER
------------------------------
10ITR062