SQL Functions

SQL Functions
        
SQL functions are similar to SQL operators in that both manipulate data items and both return a result. SQL functions differ from SQL operators in the format in which they appear with their arguments. The SQL function format enables functions to operate with zero, one, or more arguments.
function(argument1, argument2, ...) alias

SQL functions are used exclusively with SQL commands within SQL statements. There are two general types of SQL functions: single row (or scalar) functions and aggregate functions. These two types differ in the number of database rows on which they act. A single row function returns a value based on a single row in a query, whereas an aggregate function returns a value based on all the rows in a query.

SQL arithmetic functions are :
Functions     Description

abs()

This SQL ABS() returns the absolute value of a number passed as argument.
ceil()
This SQL CEIL() will rounded up any positive or negative decimal value within the function upwards.
floor()
The SQL FLOOR() rounded up any positive or negative decimal value down to the next least integer value.
exp()
The SQL EXP() returns e raised to the n-th power(n is the numeric expression), where e is the base of natural algorithm and the value of e is approximately 2.71828183.
ln()
The SQL LN() function returns the natural logarithm of n, where n is greater than 0 and its base is a number equal to approximately 2.71828183.
mod()
This SQL MOD() function returns the remainder from a division.
power()
This SQL POWER() function returns the value of a number raised to another, where both of the numbers are passed as arguments.
sqrt()
The SQL SQRT() returns the square root of given value in the argument.

SQL Character Functions:

  Character functions that return character values return values of the same datatype as the input argument. The length of the value returned by the function is limited by the maximum length of the datatype returned.
•    For functions that return CHAR or VARCHAR2, if the length of the return value exceeds the limit, then Oracle Database truncates it and returns the result without an error message.
•    For functions that return CLOB values, if the length of the return values exceeds the limit, then Oracle raises an error and returns no data.
1.CHR:
Returns the character with the binary equivalent to n in the database character set.
Syntax:
CHR (n)
Example:
SELECT CHR(68)||CHR(79)||CHR(71) "Dog" FROM DUAL;
Returns the following result:
Dog
---
DOG
2.CONCAT
  Returns char1 concatenated with char2, where char1 and char2 are string arguments. This function is equivalent to the concatenation operator (||).
Syntax:
CONCAT(char1, char2)
Example
This example uses nesting to concatenate three character strings:
SELECT CONCAT( CONCAT(ename, ' is a '), job) "Job"
FROM emp
WHERE empno = 7900;

3.INITCAP

  Returns char, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric.

 Syntax
INITCAP(char)

Example
SELECT INITCAP('the soap') "Capitals" FROM DUAL;
Returns the following result:
Capitals
--------
The Soap

4.LOWER

Returns a string argument char, with all its letters in lowercase. The return value has the same datatype as char, either CHAR or VARCHAR2.
Syntax:
LOWER(char)
 Example
SELECT LOWER('LOWER') FROM DUAL;
Returns the following result:
LOWER
-----
lower
ODBC Function
{fn LCASE (char)}
5.LPAD
   Returns char1, left-padded to length n with the sequence of characters in char2; char2 defaults to a single blank. If char1 is longer than n, this function returns the portion of char1 that fits in n.
The argument n is the total length of the return value as it is displayed on your terminal screen. In most character sets, this is also the number of characters in the return value. However, in some multi-byte character sets, the display length of a character string can differ from the number of characters in the string.
Syntax
LPAD(char1,n [,char2])
 Example
SELECT LPAD('Page1',15,'*.') "LPAD example" FROM DUAL;


Returns the following result:
LPAD example
-----------------
*.*.*.*.*.Page1
6.LTRIM
Returns the string argument char, with its left-most characters removed up to the first character which is not in the string argument set, which defaults to (a single space).
Syntax:
LTRIM(char [, set])
Example:
SELECT LTRIM ('xyxXxyLAST WORD','xy') "LTRIM example" FROM DUAL;

Returns the following result:
LTRIM example
---------------
XxyLAST WORD
ODBC Function
{fn LTRIM (char) }      (trims leading blanks)

7.RPAD
   Returns char1, right-padded to length n with char2 replicated as many times as necessary; char2 defaults to a single blank. If char1 is longer than n, this function returns the portion of char1 that fits in n.
The argument n is the total length of the return value as it is displayed on your terminal screen. In most character sets, this is also the number of characters in the return value. However, in some multi-byte character sets, the display length of a character string can differ from the number of characters in the string.
Syntax:
RPAD(char1,n [,char2 ])
Example:
SELECT RPAD('ename',12,'ab') "RPAD example"
FROM emp
WHERE ename = 'TURNER';

Returns the following result:
RPAD example
-------------
enameabababa
8.RTRIM
Returns the string argument char, with its right-most characters removed following the last character which is not in the string argument set. This defaults to ' ' (a single space).
Syntax
RTRIM(char [,set])
Example
SELECT RTRIM ('TURNERyxXxy', 'xy') "RTRIM example" FROM DUAL;
Returns the following result:
RTRIM examp
-----------
TURNERyxX
9.SUBSTR
Returns a portion of the string argument char, beginning with the character at position m and n characters long.
Syntax:
SUBSTR(char, m [, n ])
 Notes:
If m is positive, SUBSTR counts from the beginning of char to find the first character. If m is negative, SUBSTR counts backwards from the end of char. The value m cannot be 0. If n is omitted, SUBSTR returns all characters to the end of char. The value n cannot be less than 1.
Example
SELECT SUBSTR('ABCDEFG',3,4) "Subs" FROM DUAL;


10.TRANSLATE
Syntax:
TRANSLATE(char, from, to)
   Returns char with all occurrences of each character in from replaced by its corresponding character in to, where char, from, and to are string arguments.
Example
SELECT TRANSLATE('2KRW229', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "Licence" FROM DUAL;
Returns the following result:
Licence
-------
9XXX999
11. TRIM
Syntax:
TRIM( [[<trim_spec >] char ]
    FROM ] string )

If <trim_spec> is omitted, then BOTH is implied. If char is omitted, then a space character is implied.
Purpose
Removes leading and/or trailing blanks (or other characters) from a string.
Example
SELECT TRIM ('OLD' FROM 'OLDMAN') FROM DUAL;

Returns the following result:
TRIM('
------
MAN
12.UPPER
Syntax
UPPER(char)
  Returns the string argument char with all its letters converted to uppercase. The return value has the same datatype as char.
Example
SELECT UPPER('Carol') FROM DUAL;
Returns the following result:
UPPER
-----
CAROL
Aggregate functions in SQL:
   Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear 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 Database divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.
This function can produced a single value for an entire group or table.They operate on sets of rows and return results based on groups of rows.
The general syntax for most of the aggregate function is as follows:
         aggregate_function( [ DISTINCT | ALL ] expression)
List of SQL Aggregate functions are :
AVG:
Syntax
AVG([DISTINCT | ALL] n)
 Returns the average value of a column n.
Example :

SELECT AVG(SAL) FROM EMP;

Returns the following result:
 AVG(SAL)
---------
  2073.21
COUNT:
Syntax:

COUNT([* | [DISTINCT | ALL] expr})
Returns the number of rows in the query.
Example :
SELECT COUNT(*) "Total" FROM emp;
Returns the following result:
Total
----------
14
 MAX:
Syntax:
MAX([DISTINCT | ALL] expr)
Returns the maximum value of an expression specified by the argument expr.
Example
SELECT MAX(SAL) FROM EMP;
Returns the following result:
MAX(SAL)
---------
5000
 MIN:
Syntax:
MIN([DISTINCT | ALL] expr)
Returns the minimum value of an expression specified by the argument expr.
Example:
SELECT MIN(SAL), MAX(SAL) FROM EMP;
Returns the following result:
 MIN(SAL)
---------
      800
SUM:
Syntax:
SUM([DISTINCT | ALL] n)

Returns the sum of values of n.
Example
SELECT deptno, SUM(sal) TotalSalary FROM emp GROUP BY deptno;

Returns the following result:
   DEPTNO TOTALSALARY
--------- -----------
       10        8750
       20       10875
       30        9400




Functions    

Description
SQL Count function
The SQL COUNT function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. It sets on the number of rows or non NULL column values.
SQL Sum function
The SQL AGGREGATE SUM() function returns the sum of all selected column.
SQL Avg function
The SQL AVG function calculates the average value of a column of numeric type. It returns the average of all non NULL values
SQL Max function
The aggregate function SQL MAX() is used to find the maximum value or highest value of a certain column or expression. This function is useful to determine the largest of all selected values of a column.
SQL Min function
The aggregate function SQL MIN() is used to find the minimum value or lowest value of a column or expression. This function is useful to determine the smallest of all selected values of a column.

Date Functions in SQL:

ADD_MONTHS
LAST_DAY
MONTHS_BETWEEN:
NEXT_DAY:
SYSDATE:
ADD_MONTHS:Adds the specified number of months to a date.
LAST_DAY:Returns the last day in the month of the specified date.
MONTHS_ BETWEEN:Calculates the number of months between two dates.
NEW_TIME:Returns the date/time value, with the time shifted as requested by the specified time zones.
NEXT_DAY:Returns the date of the first weekday specified that is later than the date.
SYSDATE:Returns the current date and time in the Oracle Server.
Conversion Functions:
    Conversion functions convert a value from one datatype to another. Generally, the form of the function name follows the convention datatype TO datatype. The first datatype is the input datatype; the last datatype is the output datatype. There are 5 conversion functions.
       
i.CAST       
ii.TO_ CHAR
iii.TO _DATE
iv.CONVERT
v.TO _NUMBER

i.CAST:
Converts data from one type to another type.

Syntax:
SELECT CAST ( <source_operand > AS <data_type > ) FROM DUAL;
ii.TO_ CHAR:
 Converts a date or number to a value of the VARCHAR2 datatype, using the optional format fmt.
Syntax for Dates:
TO_CHAR(d [, fmt])
Syntax for Numbers:
TO_CHAR(n [, fmt])


iii.TO _DATE:
     The function takes character values as input and returns formatted date equivalent of the same. The TO_DATE function allows users to enter a date in any format, and then it converts the entry into the default format .
Syntax:
TO_DATE( string1, [ format_mask ], [ nls_language ] )
Example:

 SELECT TO_DATE('January 26, 1996, 12:38 A.M.', 'Month dd YYYY HH:MI A.M.') FROM DUAL;
Returns the following result:
TO_CHAR(TO_DATE('JANUARY26’)
----------------------------------------
1996-01-26 12:38:00
iv.CONVERT:
Converts a character string from one character set to another.
The value_exp argument is the value to be converted.
The data_type argument is the name of the character set to which char is converted.
Syntax:
{ fn CONVERT(value_exp, data_type) }


Example
SELECT {fn CONVERT('Groß', 'US7ASCII') }
"Conversion" FROM DUAL;

Returns the following result:
conversi
--------
Groß

v.TO _NUMBER:
  The TO_NUMBER function converts a character value to a numeric datatype. If the string being converted contains nonnumeric characters, the function returns an error.
Syntax
TO_NUMBER (string1, [format], [nls_parameter])

Example:

 SELECT  TO_NUMBER('121.23', '9G999D99')
FROM DUAL

TO_NUMBER('121.23','9G999D99')
------------------------------
                        121.23

OLAP  FUNCTIONS  :-
(I) It is also called as “Analytical function”
(II) This is two types
•    Rank
•    Dense_rank
(III) These two functions are used to calculate rank of a particular value from the set of values.
i.Rank:
Calculates the rank of a value in a group of values
Syntax  :-
                Rank( ) over (set of values)

ii.Dense_rank:
          The DENSE_RANK function acts like the RANK function except that it assigns consecutive ranks.
      Syntax:
      Dense_rank() over [set of values]
General Functions:
  General functions are used to handle NULL values in database. The objective of the general NULL handling functions is to replace the NULL values with an alternate value. We shall briefly see through these functions below.
NVL:
The NVL function substitutes an alternate value for a NULL value.
Syntax:
NVL( Arg1, replace_with )
NVL2
As an enhancement over NVL, Oracle introduced a function to substitute value not only for NULL columns values but also for NOT NULL columns. NVL2 function can be used to substitute an alternate value for NULL as well as non NULL value.
Syntax:
NVL2( string1, value_if_NOT_null, value_if_null )

Ex:

 SQL> SELECT NVL2(JOB_CODE, 'Job Assigned', 'Bench')
FROM employees;
NULLIF:
The NULLIF function compares two arguments expr1 and expr2. If expr1 and expr2 are equal, it returns NULL; else, it returns expr1. Unlike the other null handling function, first argument can't be NULL.

Syntax:
NULLIF (expr1, expr2)
COALESCE:
COALESCE function, a more generic form of NVL, returns the first non-null expression in the argument list. It takes minimum two mandatory parameters but maximum arguments has no limit.
Syntax:
COALESCE (expr1, expr2, ... expr_n )
GROUP BY clause:
The GROUP BY clause will gather all of the rows together that contain data in the specified column(s) and will allow aggregate functions to be performed on the one or more columns. This can best be explained by an example:
GROUP BY clause
syntax:

SELECT column1,
SUM(column2)

FROM "list-of-tables"

GROUP BY "column-list";
Let's say you would like to retrieve a list of the highest paid salaries in each dept:

SELECT max(salary), dept

FROM employee

GROUP BY dept;
This statement will select the maximum salary for the people in each unique department. Basically, the salary for the person who makes the most in each department will be displayed. Their, salary and their department will be returned.
The HAVING clause:
The HAVING clause enables you to specify conditions that filter which group results appear in the final results.
The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause.
Syntax
The syntax for the SQL HAVING Clause is:
SELECT expression1, expression2, ... expression_n,
       aggregate_function (expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n
HAVING condition;
Parameters or Arguments
aggregate_function can be a function such as SUM, COUNT, MIN, MAX, or AVG functions.
expression1, expression2, ... expression_n are expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY Clause.
condition is the condition that is used to restrict the groups of returned rows. Only those groups whose condition evaluates to TRUE will be included in the result set.
 ORDER BY clause:
Description:
The SQL ORDER BY clause is used to sort the records in the result set for a SELECT statement.
Syntax:
The syntax for the SQL ORDER BY clause is:
SELECT expressions
FROM tables
WHERE conditions
ORDER BY expression [ ASC | DESC ];
Parameters or Arguments
expressions are the columns or calculations that you wish to retrieve.
tables are the tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.
conditions are conditions that must be met for the records to be selected.
ASC is optional. It sorts the result set in ascending order by expression (default, if no modifier is provider).
DESC is optional. It sorts the result set in descending order by expression.

0 comments:

Post a Comment