Experience the Power of SQL Function

Dear Friends

 Let's experience the Power of SQL Function.

 Oracle Functions are built into ORACLE and are available for use in Various Appropriate SQL Statement.

·         The SQL Function Can is used to

                      i.        Perform Calculation on Data

                     ii.        Modify Individual Data item

                    iii.        Manipulate output for Groups of Rows.

                   iv.        Format dates and Number for Display

                    v.        Convert Column Data Type 

·         SQL Function May Accept Argument and always Return a value and can be Nested.

·         If an SQL Function is called with a NULL Argument, Then a Null is returned.

SQL Function Types

·         SQL identifies two types of Function

1.    Single Row Function

2.    Multiple Row Function

Single Row Function

These Function Returns for every row of a queried table or view.

Multiple Row Function

These Function Manipulates Group of rows and returns one result per group of rows.

The Single Row Function is categories as...

1.    CHARACTER Function

2.    NUMBER Function

3.    DATE Function

4.    CONVERSION Function

5.    GENERAL Function

CHARACTER Function: - It Accepts Character Input and can returns Both Character and Number Values

NUMBER Function :-It Accepts  Numeric Input and returns Numeric Value.

DATE Function :-It Operate on date Data Type and Can return Both date and Number

CONVERSION Function:-It Converts a value from one data type to Another Data Type.

GENERAL Function:-

·         NVL  ->Operates on Null Values

·         NVL2:-Operates on Null Values

·         DECODE ->Operates on any data Type and Can Returns any data Type.

·         CHARACTER Function :-

Ø  It Accepts Character Input and can returns Both Character and Number Values

Ø  They Returns the data type VARCHAR2,Limilted Length of 4000 Bytes

Ø  If The Return value Length exceeds, then the return value is truncated, without an error.

            The functions are Categories as

·         Case Conversion Function

                                      I.        LOWER Function

                                    II.        UPPER Function

                                   III.        INITCAP Function

                                  IV.        CONCAT Function

                                    V.        Length Function

·         Character Manipulation function

                                      I.        Sub-String(SUBSTR)

                                    II.        INSTR

                                   III.        LPAD

                                  IV.        RPAD

                                    V.        LTRIM

                                  VI.        RTRIM

                                 VII.        TRIM

                                VIII.        REPLACE

                                  IX.        TRANSLATE

                                    X.        CHR

                                  XI.        ASCII

Lower Function :-

·         It Converts Alpha Character Values to Lower case.

·         The return Value has the same Data Type as Argument CHAR type (CHAR or VARCHR2)

SQL>SELECT LOWER(‘oracle corporation’) FROM DUAL;

Upper Function

·         It Converts Alpha Character Values to Upper case.

·         The return Value has the same Data Type as Argument CHAR type (CHAR or VARCHR2)

SQL>SELECT Upper (‘ÓRACLE CORPOARATION’) FROM DUAL;

INITCAP Function

SQL>SELECT INITCAP(‘oracle corpoaration’) FROM DUAL;

SQL> SELECT Initcap('an apple of the eye') from dual;

CONCAT Function

Syntax:-Concat(Col1/col2)

SQL>SELECT CONCAT (‘ÓRACLE’’,’’CORPOARATION’) FROM DUAL;

Length Function

SQL>SELECT LENGTH(‘ÓRACLECORPOARATION’) FROM DUAL;

SUB STRING FUNCTION

Returns Specified character from Character value ,Stating From Specified Position m to n Character Long

Syntax:- SUBSTR(Col/Expr,m,n)

POINTS TO BE REMEMBER

      ·         IF m is 0, it is treated as 1.

·         If m is positive, Oracle counts From the Beginning of String to Find the First Character.

·         If M is Negative, Oracle counts Backwards From the end of the String.

·         If n is omitted, Oracle Returns all Character to the end of String.

·         If n is less than 1 or 0,A Null is returned

·         Floating point Number passed as argument to SUBSTR is automatically converted to integers.

SQL> SELECT SUBSTR (ÁBCDEFGH’,3,4) FROM DUAL;

SQL> SELECT SUBSTR(‘ÁBCDEFGH’,-5,4) FROM DUAL;

SQL> SELECT SUBSTR (‘ÁBCDEFGH’,0,4) FROM DUAL;

SQL> SELECT SUBSTR (‘ÁBCDEFGH’,4) FROM DUAL;

SQL> SELECT SUBSTR(‘ÁBCDEFGH’,4,0) FROM DUAL;

SQL> SELECT SUBSTR(‘ÁBCDEFGH’,4,-4) FROM DUAL;

SQL> SELECT INITCAP(ENAME),Job FROM EMP WHERE

SUBSTR(Job,4,3)=UPPER('age’);                                                                                  

INSTRING Function

It returns the Numeric position of a Named Character

Syntax :- INSTR(col/expr,char,m,n)

·         The instr function searches a string of substring that is supplied

·         The function returns an integer indicating the position of the character in String that is the first character of this occurrence

·         Searches for the colm or exprn beginning with its Mth character for the Nth Occurrence of Char2, and Returns the position of the character in char1,That is the first character of this occurrence.

·         M can be positive or Negative; If Negative searches Backwards From the End of Column Or Expn.

·         The value of n should be Positive.

·         The default value of Both M and N are 1

·         The return value is relative to the Beginning of Char1 regardless of the Value of m and is expressed in Character

·         If the Searches are Unsuccessful, The return value is Zero.

SQL> SELECT INSTR(‘CORPORATE FLOOR’,’OR’,1,1) FROM DUAL;

SQL> SELECT INSTR(‘CORPORATE FLOOR’,’OR’,3,2) FROM DUAL;

SQL> SELECT INSTR(‘GHANSHYAM,’A’,1,2) FROM DUAL;

Lpad Function

·         Pads the character value right justified to a total width of n Character Position

·         The Default Padding Character is Space

Syntax:-LPAD(Char1,n,Char2)

SQL> SELECT LPAD (‘’Page1’’,15,’’*”’) FROM DUAL;

RPAD FUNCTION

·         Pads the character value left justified to a total width of n Character Position

·         The Default Padding Character is Space

Syntax:-RPAD(Char1,n,Char2)

SQL> SELECT RPAD (‘’Page1’’,15,’’*”’) FROM DUAL;

select Rpad(

Substr(Ename,1,

 (Length(Ename)-3)

 )

 ,

Length(ename),'*')

 FROM EMP;

O/P:-RPAD(SUBSTR(ENAME,1,(LENGTH(ENAME)-3)),LENGTH(ENAME),'*')

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

SM***

AL***

W***

JO***

MAR***

BL***

CL***

SC***

K***

TUR***

LTRIM Function

·         It enables to trim Heading Character From a Character String

·         All The Leftmost Character that appears in the set are removed. 

Syntax:-LTRIM(Char,SET)

SQL> SELECT LTRIM (‘xyxyxyxyLAST WORD’,’’xy’’) FROM DUAL;

RTRIM Function

·         It enables to trim of Trailing Character From a Character String

·         All The Rightmost Character that appears in the set are removed. 

Syntax:-RTRIM(Char,SET)

SQL> SELECT LTRIM (‘LAST WORDxyxyxyxy’,’’xy’’) FROM DUAL;

TRIM Function

Syntax:-TRIM(BOTH,Trimchar from Trim source)

SQL> SELECT TRIM (‘S’ FROM ‘SSMITHSS’) FROM DUAL;

REPLACE FUNCTION

·         It returns the every occurrence of search string replaced by the replacement string

·         If the replacement string is omitted or null, all occurrence   of Search String are removed

·         It substitutes one string for another as well as removes character string’s

SYNTAX:-REPALCE(CHAR,Search_str,Replace_str)

SELECT REPLACE(‘JACL and JUE’,’J’,’BL’) from Dual;

TranslateFunction

·         Used to translate Character by charcter in a string

·         It returns CHAR with all occurrence each character in FROM Replaced by its corresponding character in TO.

·         Character in Char That are not in FROM are not replaced

·         The argument FROM can contain more character than TO

·         If the Extra character appears in CHAR They are removed from Return Value.

SYNTAX:-TRANSLATE(CHAR,FROM,TO)

SQL>SELECT TRANSLATE(‘JACL and JUE’,’JA’,’BL’) from Dual;

SQL>SELECT JOB, TRANSLATE(JOB,,’MN’,’DM’) from EMP WHERE JOB=’MANAGER’;

CHAR FUNCTION

It returns a Character having the ASCII to n

Syntax:- CHR(n)

SQL>SELECT CHR(65)||CHR(67)||CHR(84) FROM DUAL;

ASCII FUNCTION

Syntax:- ASCII (CHAR)

SQL>SELECT ASCII (‘A’)||ASCII (‘APPLE’)||ASCII (‘a’) FROM DUAL;

NUMBER Function :-

It Accepts  NumericInput and returns Numeric Value.

Many Functions Returns Values That are accurate to 38 Decimal digits

                              I.        ROUND

                            II.        TRUNCATE

                           III.        CEIL

                          IV.        FLOOR

                            V.        MODULUS(MOD)

                          VI.        POWER

                         VII.        SQRT

                        VIII.        ABS

                          IX.        SIGN

Round function

Syntax:- Round(n,m)

·         It Returns n Rounded to m Places Right of the Decimal Point.

·         If m is Omitted n is Rounded to 0,Places

·         M  can be negative and Rounds off The Digits to the Left of the Decimal Point

·         M must be an integer.

SQL> SELECT ROUND(15.193,1)  FROM DUAL;

SQL> SELECT ROUND(15.193,-1)  FROM DUAL;

SQL> SELECT ROUND(45.923,2)  FROM DUAL;

SQL> SELECT ROUND(45.923,0)  FROM DUAL;

SQL> SELECT ROUND(45.923,-1)  FROM DUAL;

 Truncate  function

Syntax:-Trunc(n,m)

·         It Returns n Truncated to m Places Decimal Point.

·         If m is Omitted n is Truncated to 0Decimal Places

·         n  can be negative to Truncated m Digits to the Left of the Decimal Point

SQL> SELECT TRUNC(15.193,1)  FROM DUAL;

SQL> SELECT TRUNC (15.193,-1)  FROM DUAL;

SQL> SELECT TRUNC (45.923,2)  FROM DUAL;

SQL> SELECT TRUNC (45.923,0)  FROM DUAL;

SQL> SELECT TRUNC (45.923,-2)  FROM DUAL;

CEIL FUNCTION

SYNTAX:-CEIL(n)

  Returns the smallest Interger Greater Than or equal t to n

·         The adjecscentment is done to the highest nearest Decimal value

SQL> SELECT CEIL (45.7)  FROM DUAL;

SQL> SELECT CEIL (14.27)  FROM DUAL;

SQL> SELECT CEIL (14)  FROM DUAL;

FLOOR FUNCTION

SYNTAX:- FLOOR (n)

·         Returns the Largest Interger Less Than or equal t to n

·         The adjecscentment is done to the Lowest nearest Decimal value

SQL> SELECT FLOOR (45.7)  FROM DUAL;

SQL> SELECT FLOOR (14.27)  FROM DUAL;

SQL> SELECT FLOOR (14)  FROM DUAL;

MOD FUNCTION

SYNTAX:-MOD(m,n)

·         It returns Remainder of m Divided by n

·         It returns m if n is 0

SQL> SELECT MOD (11,3)  FROM DUAL;

POWER FUNCTION

SYNTAX:-POWER(m,n)

·         It returns m raised to the n th Power

·         The Base m and The exponent n can be any Number

·         If m is Negative ,n must be an Integer

SQL> SELECT POWER (2,3)  FROM DUAL;

SQL> SELECT POWER (-2,3)  FROM DUAL;

SQL> SELECT POWER (-2,-3)  FROM DUAL;

SQUARE ROOT FUNCTION

SYNTAX:-Sqrt(n)

·         It returns square root n as Real value

·         The value of n cannot be negative

SQL> SELECT Sqrt (25)  FROM DUAL;

ABSOLUTE FUNCTION

SYNTAX:-ABS(n)

·         It returns the absolute value of n

SQL> SELECT ABS (-25)  FROM DUAL;

SIGN FUNCTION

SYNTAX:-SIGN(n)

·         It returns The sign Specification of a number

·         IF n<0 ,then returns -1

·         IF n=0 ,then returns 0

·         IF n>0 ,then returns 1

SQL> SELECT SIGN (25)  ,SIGN (0)  ,SIGN (-25)  FROM DUAL;

DATE Function :-It Operate on date Data Type and Can return Both date and Number

·         Oracle Stores date in an Internal Numeric format

·         The date in Oracle Range From JANUARY 1,4712 BC to DECEMBER 31,9999 AD

·         The Default Display  and Input Format for any date is DD-MON-YY.

·         The Internal Date format Represents

Century<=YEAR<=MONTH<= DAY<=HOURS<=MINUTES<=SECONDS

SYSDATE

·         It is the date Function That Returns Current DATE and TIME

·         SYSDATE is generally selected on Dummy table

Date Arithmetic

·         As Database Stores Date as NUMBER, Arithmetic Operation can be Implemented.

·         Number Constant can be added or Subtracted Upon dates

·         Date +Number=Return Date

Add Number of Days to a Date

·          Date -Number=Return Date

Subtracts Number of days from a Date

·         Date -Dater=Return No of days

       I.        MONTHS_BETWEEN

     II.        NEXT_DAY

    III.        LAST_DAY

   IV.        ROUND

     V.        TRUNCATE(TRUNC)

   VI.        ADD MONTHS

ADD_MONTHS FUNCTION

SYNTAX:- Add_months(D,n)

·         It Returns the DATE D plus or Minus n Months

·         The Argument n can be any positive or negative Integer.

SQL> SELECT SYSDATE,ADD_MONTHS(HIREDATE,2) FROM EMP;

SQL> SELECT ENAME,SAL HIREDATE,ADD_MONTHS(HIREDATE,2) FROM EMP WHERE DEPTNO=20;

MONTHS_BETWEEN  FUNCTION

SYNTAX:-MONTHS_BETWEEN(D1,D2)

·         It returns number of months between date D1 and D2.

·         If D1 is later than D2 ,The Result is positive else negative.

·         If D1 and D2 are either the same days of the moths or Both last days of the months ,The result is always an Integer.

SQL>SELECT ENAME,HIREDATE,SYSDATE,MONTHS_BETWEEN(SYSDATE,HIREDATE) FROM EMP;

SQL>SELECT ENAME,HIREDATE,SYSDATE,MONTHS_BETWEEN(SYSDATE,HIREDATE) FROM EMP WHERE MONTHS_BETWEEN(SYSDATE,HIREDATE)<200;

NEXT_DAY FUNCTION

Syntax  NEXT_DAY(d,CHAR)

·         It Returns the date of the First week day Named By CHAR,That is later than the Date d

·         The CHAR must be a day of the Week in the session Date language.

·         The day of the Week can be full name  or the Abbreviation,

SQL> SELECT SYSDATE,NEXT_DAY(SYSDATE,’SUN’) from Dual;

SQL> SELECT Sal,Hiredate,Next_day(Hiredate,’’MONDAY”) From emp;

LAST_DAY FUNCTION

Syntax :-LAST_DAY(D)

·         It returns the date of the Last day of the months that contain D

·         Mostly used to Determine How many days are left in the current Month

SQL> SELECT SYSDATE,LAST_DAY(SYSDATE) from Dual;

SQL> SELECT SYSDATE,LAST_DAY(SYSDATE)-SYSDATE from Dual;

ROUND Function

Syntax :-Round(date,Format)

·         Returns Date rounded to the unit specified by the format

·         If Format is omitted ,date is Rounded to the Nearest day.

SQL>SELECT SYSDATE,ROUND(SYSDATE,’DAY’’) from Dual;

SQL>SELECT SYSDATE,ROUND(SYSDATE,’MONTH’’) from Dual;

SQL>SELECT SYSDATE,ROUND(SYSDATE,’YEAR’’) from Dual;

Truncate Date

Syntax:-Trunc(date, ’Format”)

·         Date is truncated t the nearest Date with the time portion of the day truncated to the specified Unit.

·         If Format is Omitted date is truncated to the Nearest Day.

SQL>SELECT SYSDATE,TRUNC(SYSDATE,’DAY’) from Dual;

SQL>SELECT SYSDATE,TRUNC (SYSDATE,’MONTH’) from Dual;

SQL>SELECT SYSDATE,TRUNC (SYSDATE,’YEAR’) from Dual;

CONVERSION Function:-It Converts a value from one data type to Another Data Type.The Conversion Function Converts a value From One data Type to Another.

Types of Conversion Function

o   Implicit Conversion

o   Explicit Conversion

Implicit data Type Conversion

·         Implicit Conversion Works According to the Conversion Specified by Oracle.

·         The Assignment Succeeds if  the Oracle server can Converts the data type of the value

·         Char to Number Conversion succeeds only if the Character String Represents a Valid Number.

·         Char to date Conversion succeeds only if the Character String Represents the default Format of date DD-MON-YY.

In Assignment Operation

·         Varchar2/Char                        -> NUMBER

·         Varchar2/Char                        -> DATE

·         NUMBER                    ->Varchar2

·         DATE                          ->Varchar2

Explicit data Type Conversion

SQL Provides three Function to Converts a value from one data type to another

The Explicit Conversion Function are..

1.    TO_CHAR

2.    TO_NUMBER

3.    TO_DATE

TO_CHAR Conversion Function

This conversion function can be used in two Different Flavour.

TO_CHAR      (NUMBER Conversion)

TO_CHAR      (DATE Conversion)

TO_CHAR      (NUMBER Conversion)

Syntax :-TO_CHAR(NUMBER,fmt,nlsparams)

·         Converts NUMBER of NUMBER data type to a value of VARCHAR2 data Type in the format specified.

·         Fmt is the optional Number Format

·         Nlsparams specifies the character returned by the Number Format element

Select to_char(12345)||6 from dual;

TO_CHAR      (Date Conversion)

Syntax  :-TO_CHAR(Date,fmt,nlsparams)

·         Converts Date of Date  data type to a value of VARCHAR2 data Type in the format specified

·         Fmt is the optional Number Format

·         Nlsparams specifies the character returned by the Number Format element

Examples - Dates

The following is a list of valid parameters when the to_char function is used to convert a date to a string. These parameters can be used in many combinations.

Parameter	Explanation
YEAR	Year, spelled out
YYYY	4-digit year
YYY YY Y	Last 3, 2, or 1 digit(s) of year.
IYY IY I	Last 3, 2, or 1 digit(s) of ISO year.
IYYY	4-digit year based on the ISO standard
Q	Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM	Month (01-12; JAN = 01).
MON	Abbreviated name of month.
MONTH	Name of month, padded with blanks to length of 9 characters.
RM	Roman numeral month (I-XII; JAN = I).
WW	Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW	Week of year (1-52 or 1-53) based on the ISO standard.
D	Day of week (1-7).
DAY	Name of day.
DD	Day of month (1-31).
DDD	Day of year (1-366).
DY	Abbreviated name of day.
J	Julian day; the number of days since January 1, 4712 BC.
HH	Hour of day (1-12).
HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
MI	Minute (0-59).
SS	Second (0-59).
SSSSS	Seconds past midnight (0-86399).
FF	Fractional seconds.

The following are date examples for the to_char function.

to_char(sysdate, 'yyyy/mm/dd');	would return '2003/07/09'
to_char(sysdate, 'Month DD, YYYY');	would return 'July 09, 2003'
to_char(sysdate, 'FMMonth DD, YYYY');	would return 'July 9, 2003'
to_char(sysdate, 'MON DDth, YYYY');	would return 'JUL 09TH, 2003'
to_char(sysdate, 'FMMON DDth, YYYY');	would return 'JUL 9TH, 2003'
to_char(sysdate, 'FMMon ddth, YYYY');	would return 'Jul 9th, 2003'

You will notice that in some to_char function examples, the format_mask parameter begins with "FM". This means that zeros and blanks are suppressed. This can be seen in the examples below.

to_char(sysdate, 'FMMonth DD, YYYY');	would return 'July 9, 2003'
to_char(sysdate, 'FMMON DDth, YYYY');	would return 'JUL 9TH, 2003'
to_char(sysdate, 'FMMon ddth, YYYY');	would return 'Jul 9th, 2003'

The zeros have been suppressed so that the day component shows as "9" as opposed to "09".

SQL> Select to_char(sysdate, 'yyyy/mm/dd') "Date Today" FROM dual;

 '2010/12/24'

SQL> Select to_char(sysdate, 'FMMonth DD, YYYY') FROM dual;

 'June 9, 2005'

SQL> select to_char(sysdate,'HH24:MI:SS') "Time Now" from dual;

 '14:35:56'

TO_NUMBER Function

Syntax:-To_NUMBER(CHAR,fmt,’nlsparam’)

It converts a char,value of CHAR or Varchar2 Data Type containing a Number in the Format Specified By the optional Format Model ‘fmt’,To a value of Number Data Type.

SQL> SELECT ‘$1000.00’,TO_NUMBER(‘$1000.00’,’L99,999.99’)FROM DUAL;

SQL> SELECT ‘$1000.00’,TO_NUMBER(‘$1000.00’,’L99,999.99’)+500 FROM DUAL;

select to_number('1000.10','9999.99')

from dual;

TO_DATE Function

Syntax:-To_DATE(CHAR,,fmt,’nlsparam’)

CONVERTS given Char of CHAR or Varchar2 data type to given of DATE data type

SQL>SELECT ENAME,Hiredate,ADD_MONTHS(TO_DATE(’17-DEC-1980’,’DD-MON-YY’),3) FROM EMP WHERE HIREDATE=’17-DEC-1980’;

SQL>SELECT ENAME,Hiredate,ADD_MONTHS(TO_DATE(’1980-DECEMBER-17’,’YYYY-MONTH-DD’),3) FROM EMP WHERE HIREDATE=’17-DEC-1980’;

DATE Function:-

Sysdate

Select sysdate from dual;

ADD_MONTHS(D1,n)

select ADD_MONTHS(TO_DATE('17-DEC-1980','DD-MON-YY'),12)from dual;

Months_betweens

 select MONTHS_BETWEEN(TO_DATE('17-DEC-1985','DD-MON-YY'),TO_DATE('17-DEC-1980','DD-MON-YY'))from dual;

 NEXT_DAY(D,DAY)

SELECT NEXT_DAY('05-DEC-2012','SUN')from dual;

SELECT NEXT_DAY(TO_DATE('05-DEC-2012','DD-MON-YY'),'SUN')from dual;

LAST_DAY

SELECT LAST_DAY('05-DEC-2012')from dual;

General Function

NVL

select a.* ,NVL(comm,1000)from emp a where comm is null;

NVL2

select a.* ,NVL2(comm,1000,2000)fromempa;

DECODE

SELECT ENAME,SAL,JOB,

decode(JOB,'CLERK','YOU ARE NOT ELIGIBLE',

'ANALYST','YOU NEED PERMISSION',

'MANAGER','YOU ARE  ELIGIBLE',

'NOT APPLICABLE') result

FROM EMP;

 For Example

You could use the decode function in an SQL statement as follows:

SELECT supplier_name,

decode(supplier_id, 10000, 'IBM',

                    10001, 'Microsoft',

                    10002, 'Hewlett Packard',

                    'Gateway') result

FROM suppliers;

The above decode statement is equivalent to the following IF-THEN-ELSE statement:

IF supplier_id = 10000 THEN

result := 'IBM';

ELSIF supplier_id = 10001 THEN

result := 'Microsoft';

ELSIF supplier_id = 10002 THEN

result := 'Hewlett Packard';

ELSE

result := 'Gateway';

END IF;

SELECT ENAME,SAL,JOB,

decode(JOB,'CLERK','YOU ARE NOT ELIGIBLE',

'ANALYST','YOU NEED PERMISSION',

'MANAGER','YOU ARE  ELIGIBLE',

'NOT APPLICABLE') result

FROMEMP;

Multiple Row Function

These Function Manipulates Group of rows and returns one result per group of rows.

GROUP BY CLUASE:-The Group By clause is used to decide the the rows in a table into Group.

HAVING CLAUSE:-It is used to specify Which Group are to be Dispalyed        

The clause is used to Filter Data that is associated with Group Function

Average Function

AVG(DISTINCT/ALL COL)

SUM FUNCTION

Syntax SUM(DISTINCT/ALL COL)

SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO

SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING MAX(SAL)>1500

SELECT DEPTNO,MIN(SAL), MAX(SAL) FROM EMP WHERE JOB=’’ÇLERK’’GROUP BY DEPTNO HAVING MIN(SAL)<1500

 Miscellaneous Function

·         Greatest

Select greatest(‘’Harry’’,’’HARRIOT’’) form Dual

Select greatest(1000,2000,3000) from dual;

·         Least

Select Least(‘’Harry’’,’’HARRIOT’’) form Dual

Select Least(1000,2000,3000) from dual;

·         USER

SELECT USER from Dual;

·         UID

SELECT UID from Dual;

 ·         Vsize(Expn):- It returns number of bytes

SELECT VSIZE (ENAME) FROM DUAL

Thanks for reading my blog .You are welcome to share your thoughts.

Thanks
Ghanshyam M