Monday, 15 February 2016

Oracle SQL-3

Single-Row Functions

Single-row functions return a single result row for every row of a queried table or view. These functions can appear in select lists, WHERE clauses, START WITH and CONNECT BY clauses, and HAVING clauses.
Oracle SQL Functions can be divided into following categories

  • Number Functions

  • Character Functions

  • Miscellaneous Single Row Functions

  • Aggregate Functions

  • Date and Time Functions

Here are the explanation and example of these functions

Number Functions (also known as Math Functions)

Number functions accept numeric input and return numeric values. Most of these functions return values that are accurate to 38 decimal digits.
The number functions available in Oracle are:
ABS  ACOS ASIN ATAN ATAN2 BITAND CEIL COS COSH EXP FLOOR LN LOG
MOD POWER ROUND (number) SIGN SIN SINH SQRT TAN TANH TRUNC (number)

ABS

ABS returns the absolute value of n.
The following example returns the absolute value of -87:
SELECT ABS(-87) "Absolute" FROM DUAL;

  Absolute
  ----------
        87

ACOS

ACOS returns the arc cosine of n. Inputs are in the range of -1 to 1, and outputs are in the range of 0 to pi and are expressed in radians.
The following example returns the arc cosine of .3:
SELECT ACOS(.3)"Arc_Cosine" FROM DUAL;

Arc_Cosine
----------
1.26610367
Similar to ACOS, you have ASIN (Arc Sine), ATAN (Arc Tangent) functions.

CIEL

Returns the lowest integer above the given number.Example:
The following function return the lowest integer above 3.456;
select ciel(3.456) “Ciel” from dual;

Ciel
---------
        4

FLOOR         

Returns the highest integer below the given number.
Example:
The following function return the highest integer below 3.456;
select floor(3.456) “Floor” from dual;

Floor
------------
        3

COS

Returns the cosine of an angle (in radians).
Example:
The following example returns the COSINE angle of 60 radians.
select  cos(60) “Cosine” from dual;

SIN

Returns the Sine of an angle (in radians).
Example:
The following example returns the SINE angle of 60 radians.
select  SIN(60) “Sine” from dual;

 

TAN

Returns the Tangent of an angle (in radians).
Example:
The following example returns the tangent angle of 60 radians.
select  Tan(60) “Tangent” from dual;
Similar to SIN, COS, TAN  functions hyperbolic functions  SINH, COSH, TANH are also available in oracle.

MOD

Returns the remainder after dividing m with n.
Example
The following example returns the remainder after dividing 30 by 4.
Select mod(30,4) “MOD” from dual;

MOD
---------
        2

POWER

Returns the power of m, raised to n.
Example
The following example returns the 2 raised to the power of 3.
select  power(2,3) “Power” from dual;

POWER
---------
        8

EXP

Returns the e raised to the power of n.
Example
The following example returns the e raised to power of 2.
select exp(2) “e raised to 2” from dual;

E RAISED TO 2
-------------
       

LN

Returns natural logarithm of n.
Example
The following example returns the natural logarithm of 2.
select ln(2) from dual;

LN
------------

LOG

Returns the logarithm, base m, of n.
Example
The following example returns the log of 100.
select log(10,100) from dual;

LOG
---------
        2

ROUND

Returns a decimal number rounded of to a given decimal positions.
Example
The following example returns the no. 3.4573 rounded to 2 decimals.
select round(3.4573,2) “Round” from dual;

Round
------------
        3.46

TRUNC

Returns a decimal number Truncated to a given decimal positions.
Example
The following example returns the no. 3.4573 truncated to 2 decimals.
select round(3.4573,2) “Round” from dual;

Round
------------
        3.45

SQRT

Returns  the square root of a given number.
Example
The following example returns the square root of  16.
select  sqrt(16) from dual;

SQRT
---------
        4 

Character Functions

Character functions operate on values of dataype  CHAR or VARCHAR.

LOWER

Returns a given string in lower case.
select LOWER(‘SAMI’) from dual;

LOWER
-------------
sami

UPPER

Returns a given string in UPPER case.
select UPPER(‘Sami’) from dual;

UPPER
------------------
SAMI
                    

INITCAP

Returns a given string with Initial letter in capital.
select INITCAP(‘mohammed sami’) from dual;

INITCAP
------------------
Mohammed Sami

LENGTH

Returns the length of a given string.
select length(‘mohammed sami’) from dual;

LENGTH
------------
        13

SUBSTR

Returns a substring from a given string. Starting from position p to n characters.
For example the following query returns “sam” from the string “mohammed sami”.
select substr('mohammed sami',10,3) from dual;

Substr
--------
sam

INSTR

Tests whether a given character occurs in the given string or not. If the character occurs in the string then returns the first position of its occurrence otherwise returns 0.
Example
The following query tests whether the character “a” occurs in string “mohammed sami”
select instr('mohammed sami','a') from dual;

INSTR
--------
4

REPLACE

Replaces a given set of characters in a string with another set of characters.
Example
The following query replaces “mohd” with “mohammed” .
select replace('ali mohd khan','mohd','mohammed') from dual;

REPLACE
---------
ali mohammed khan

INSTR

Tests whether a given character occurs in the given string or not. If the character occurs in the string then returns the first position of its occurrence otherwise returns 0.
Example
The following query tests whether the character “a” occurs in string “mohammed sami”
select instr('mohammed sami','a') from dual;

INSTR
--------
4

REPLACE

Replaces a given set of characters in a string with another set of characters.
Example
The following query replaces “mohd” with “mohammed” .
select replace('ali mohd khan','mohd','mohammed') from dual;

REPLACE
---------
ali mohammed khan

 

TRANSLATE

This function is used to encrypt characters. For example you can use this function to replace characters in a given string with your coded characters.
Example
The following query replaces characters A with B, B with C, C with D, D with E,...Z with A, and a with b,b with c,c with d, d with e ....z with a.
select translate('interface','ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',
      'BCDEFGHIJKLMNOPQRSTUVWXYZAbcdefghijklmnopqrstuvwxyza') “Encrypt” from dual;

Encrypt
-----------
joufsgbdf

SOUNDEX

This function is used to check pronounciation rather than exact characters. For example many people write names as “smith” or “smyth” or “smythe” but they are pronounced as smith only.
Example
The following example compare those names which are spelled differently but are pronouced as “smith”.
Select ename from emp where soundex(ename)=soundex('smith');

ENAME
---------
Smith
Smyth
Smythe

RPAD

Right pads a given string with a given character to n number of characters.
Example
The following query rights pad ename with '*'  until it becomes 10 characters.
select rpad(ename,'*',10) from emp;

Ename
----------
Smith*****
John******
Mohammed**
Sami******

LPAD

Left pads a given string with a given character upto n number of characters.
Example
The following query left pads ename with '*'  until it becomes 10 characters.
select lpad(ename,'*',10) from emp;

Ename
----------
*****Smith
******John
**Mohammed
******Sami

LTRIM

Trims blank spaces from a given string from left.
Example
The following query returns string “       Interface        “ left trimmed.
select ltrim('       Interface       ') from dual;

Ltrim
--------------
Interface 

RTRIM

Trims blank spaces from a given string from Right.
Example
The following query returns string “       Interface        “ right trimmed.
select rtrim('       Interface       ') from dual;

Rtrim
------------
   Interface 

TRIM

Trims a given character from left or right or both from a given string.
Example
The following query removes zero from left and right of a given string.
Select trim(0 from '00003443500') from dual;

Trim
----------
34435

CONCAT

Combines a given string with another string.
Example
The following Query combines ename with literal string “ is a “ and jobid.
Select concat(concat(ename,' is a '),job) from emp;

Concat
----------------
Smith is a clerk
John is a Manager
Sami is a G.Manager

Aggregate Functions

Aggregate functions return a single value based on groups of rows, rather than single value for each row. You can use Aggregate functions in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle divides the rows of a queried table or view into groups.
The important Aggregate functions are :
Avg     Sum     Max     Min      Count     Stddev     Variance

AVG

AVG( ALL /DISTINCT  expr)

Returns the average value of expr.
Example
The following query returns the average salary of all employees.
select avg(sal) “Average Salary” from emp;

Average Salary
------------------------
2400.40

SUM

SUM(ALL/DISTINCT  expr)

Returns the sum value of expr.
Example
The following query returns the sum salary of all employees.
select sum(sal) “Total Salary” from emp;

Total Salary
------------------------
26500

MAX

MAX(ALL/DISTINCT expr)

Returns maximum value of expr.
Example
The following query returns the max salary from the employees.
select max(sal) “Max Salary” from emp;

Maximum Salary
------------------------
4500

MIN

   MIN(ALL/DISTINCT  expr)

Returns minimum value of expr.
Example
The following query returns the minimum salary from the employees.
select min(sal) “Min Salary” from emp;

Minimum Salary
------------------------
1200

COUNT

  COUNT(*) OR COUNT(ALL/DISTINCT expr)

Returns the number of rows in the query. If you specify expr then count ignore nulls. If you specify the asterisk (*), this function returns all rows, including duplicates and nulls. COUNT never returns null.
Example
The following query returns the number of  employees.
Select count(*) from emp;

COUNT
------
14
The following query counts the number of employees whose salary is not null.
Select count(sal) from emp;

COUNT
------
12

STDDEV

STDDEV(ALL/DISTINCT  expr)

STDDEV returns sample standard deviation of expr, a set of numbers.
Example
The following query returns the standard deviation of salaries.
select stddev(sal) from emp;

Stddev
-------
 1430

VARIANCE

VARIANCE(ALL/DISTINCT  expr)

Variance returns the variance of expr.
Example
The following query returns the variance of salaries.
select variance(sal) from emp;

Variance
-------  
1430

Aggregate Functions

Aggregate functions return a single value based on groups of rows, rather than single value for each row. You can use Aggregate functions in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle divides the rows of a queried table or view into groups.
The important Aggregate functions are :
Avg     Sum     Max     Min      Count     Stddev     Variance

AVG

        AVG( ALL /DISTINCT        expr)
Returns the average value of expr.
Example
The following query returns the average salary of all employees.
select avg(sal) “Average Salary” from emp;

Average Salary
------------------------
2400.40

SUM

     SUM(ALL/DISTINCT           expr)
Returns the sum value of expr.
Example
The following query returns the sum salary of all employees.
select sum(sal) “Total Salary” from emp;

Total Salary
------------------------
26500

MAX

    MAX(ALL/DISTINCT          expr)
Returns maximum value of expr.
Example
The following query returns the max salary from the employees.
select max(sal) “Max Salary” from emp;

Maximum Salary
------------------------
4500

MIN

   MIN(ALL/DISTINCT           expr)
Returns minimum value of expr.
Example
The following query returns the minimum salary from the employees.
select min(sal) “Min Salary” from emp;

Minimum Salary
------------------------
1200

COUNT

  COUNT(*) OR COUNT(ALL/DISTINCT expr)

Returns the number of rows in the query. If you specify expr then count ignore nulls. If you specify the asterisk (*), this function returns all rows, including duplicates and nulls. COUNT never returns null.
Example
The following query returns the number of  employees.
Select count(*) from emp;

COUNT
------
14
The following query counts the number of employees whose salary is not null.
Select count(sal) from emp;

COUNT
------
12

STDDEV

 STDDEV(ALL/DISTINCT   expr)

STDDEV returns sample standard deviation of expr, a set of numbers.
Example
The following query returns the standard deviation of salaries.
select stddev(sal) from emp;

Stddev
-------
 1430

VARIANCE

VARIANCE(ALL/DISTINCT  expr)

Variance returns the variance of expr.
Example
The following query returns the variance of salaries.
select variance(sal) from emp;

Variance
-------  
1430

Date Functions and Operators.

To see the system date and time use the following functions :
CURRENT_DATE    :returns the current date in the session time zone, in a value in the Gregorian calendar of datatype
                                   DATE
SYSDATE                 :Returns the current date and time.
SYSTIMESTAMP    :The SYSTIMESTAMP function returns the system date, including fractional seconds and time zone
                                    of the database. The return type is TIMESTAMP WITH TIME ZONE.

SYSDATE Example

To see the current system date and time give the following query.
select sysdate from dual;

SYSDATE
-------
8-AUG-03
The format in which the date is displayed depends on NLS_DATE_FORMAT parameter.
For example set the NLS_DATE_FORMAT to the following format
alter session set NLS_DATE_FORMAT=’DD-MON-YYYY HH:MIpm’;
Then give the give the following statement
select sysdate from dual;

SYSDATE
------------------
8-AUG-2003 03:05pm
The default setting of NLS_DATE_FORMAT is DD-MON-YY


CURRENT_DATE Example

To see the current system date and time with  time zone use CURRENT_DATE function
ALTER SESSION SET TIME_ZONE = '-4:0';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;

SESSIONTIMEZONE CURRENT_DATE
--------------- --------------------
-04:00          22-APR-2003 14:15:03

ALTER SESSION SET TIME_ZONE = '-7:0';
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;

SESSIONTIMEZONE CURRENT_DATE
--------------- --------------------
-07:00          22-APR-2003 09:15:33

SYSTIMESTAMP Example

To see the current system date and time with fractional seconds with time zone give the following statement
select systimestamp from dual;

SYSTIMESTAMP
-------------------------------
22-APR-03 08.38.55.538741 AM -07:00

DATE FORMAT MODELS

To translate the date into a different format string  you can use TO_CHAR function with date format. For example to see the current day you can give the following query
Select to_char(sysdate,’DAY’)”Today” FROM DUAL;

TODAY
-------
THURSDAY
To translate a character value, which is in format other than the default date format, into a date value you can use TO_DATE function with date format to date.

TO_DATE Example

To_Date function is used to convert strings into date values. For example you want to see what was the day on 15-aug-1947. The use the to_date function to first convert the string into date value and then pass on this value to to_char function to extract day.
select to_char(to_date(’15-aug-1947’,’dd-mon-yyyy’),’Day’)
                                         from dual;

TO_CHAR(
--------
Friday
To see how many days have passed since 15-aug-1947 then give the following query
select sysdate-to_date(’15-aug-1947’,’dd-mon-yyyy’) from dual;
Now we want to see which date will occur after 45 days from now
select sysdate+45 from dual;

SYSDATE
-------
06-JUN-2003


ADD_MONTHS

To see which date will occur after 6 months from now, we can use ADD_MONTHS function
Select ADD_MONTHS(SYSDATE,6) from dual;

ADD_MONTHS
----------
22-OCT-2003

MONTHS_BETWEEN

To see how many months have passed since  a particular date, use the MONTHS_BETWEEN function.

For Example, to see how many months have passed since 15-aug-1947, give the following query.
select months_between(sysdate,to_date(’15-aug-1947’))
                             from dual;

Months
------
616.553
To eliminate the decimal value use truncate function
select trunc(months_between(sysdate,to_date(’15-aug-1947’)))
                             from dual;

Months
------
616

LAST_DAY

To see the last date of the month of a given date, Use LAST_DAY function.
select LAST_DAY(sysdate) from dual;

LAST_DAY
--------
31-AUG-2003

NEXT_DAY

To see when a particular day is coming next ,  use the NEXT_DAY function.

For Example to view when next Saturday is coming, give the following query
select next_day(sysdate) from dual;

NEXT_DAY
-----------
09-AUG-2003

EXTRACT

An EXTRACT datetime function extracts and returns the value of a specified datetime field from a datetime or interval value expression. When you extract a TIMEZONE_REGION or TIMEZONE_ABBR (abbreviation), the value returned is a string containing the appropriate time zone name or abbreviation
The syntax of EXTRACT function is
EXTRACT ( YEAR / MONTH / WEEK / DAY / HOUR / MINUTE / TIMEZONE  FROM DATE)

Example
The following demonstrate the usage of EXTRACT function to extract year from current date.

select extract(year from sysdate) from dual;

EXTRACT
-------
2003




Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)