SQL Functions
SQL functions are similar to SQL operators inward that both manipulate information items too both render a result. SQL functions differ from SQL operators inward the format inward which they appear amongst their arguments. The SQL component format enables functions to operate amongst zero, one, or to a greater extent than arguments.
function(argument1, argument2, ...) alias
SQL functions are used solely amongst SQL commands inside SQL statements. There are ii full general types of SQL functions: unmarried row (or scalar) functions too aggregate functions. These ii types differ inward the expose of database rows on which they act. H5N1 unmarried row component returns a value based on a unmarried row inward a query, whereas an aggregate component returns a value based on all the rows inward a query.
SQL arithmetics functions are :
Functions Description
abs()
This SQL ABS() returns the absolute value of a expose passed every bit argument.
ceil()
This SQL CEIL() volition rounded upward whatever positive or negative decimal value inside the component upwards.
floor()
The SQL FLOOR() rounded upward whatever positive or negative decimal value downwards to the side past times side to the lowest degree 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 operations of natural algorithm too the value of e is unopen to 2.71828183.
ln()
The SQL LN() component returns the natural logarithm of n, where n is greater than 0 too its base of operations is a expose equal to unopen to 2.71828183.
mod()
This SQL MOD() component returns the residuum from a division.
power()
This SQL POWER() component returns the value of a expose raised to another, where both of the numbers are passed every bit arguments.
sqrt()
The SQL SQRT() returns the foursquare origin of given value inward the argument.
SQL Character Functions:
Character functions that render grapheme values render values of the same datatype every bit the input argument. The length of the value returned past times the component is limited past times the maximum length of the datatype returned.
• For functions that render CHAR or VARCHAR2, if the length of the render value exceeds the limit, therefore Oracle Database truncates it too returns the effect without an mistake message.
• For functions that render CLOB values, if the length of the render values exceeds the limit, therefore Oracle raises an mistake too returns no data.
1.CHR:
Returns the grapheme amongst the binary equivalent to n inward the database grapheme set.
Syntax:
CHR (n)
Example:
SELECT CHR(68)||CHR(79)||CHR(71) "Dog" FROM DUAL;
Returns the next result:
Dog
---
DOG
2.CONCAT
Returns char1 concatenated amongst char2, where char1 too char2 are string arguments. This component is equivalent to the concatenation operator (||).
Syntax:
CONCAT(char1, char2)
Example
This instance uses nesting to concatenate iii grapheme strings:
SELECT CONCAT( CONCAT(ename, ' is a '), job) "Job"
FROM emp
WHERE empno = 7900;
3.INITCAP
Returns char, amongst the foremost missive of the alphabet of each give-and-take inward uppercase, all other letters inward lowercase. Words are delimited past times white infinite or characters that are non alphanumeric.
Syntax
INITCAP(char)
Example
SELECT INITCAP('the soap') "Capitals" FROM DUAL;
Returns the next result:
Capitals
--------
The Soap
4.LOWER
Returns a string declaration char, amongst all its letters inward lowercase. The render value has the same datatype every bit char, either CHAR or VARCHAR2.
Syntax:
LOWER(char)
Example
SELECT LOWER('LOWER') FROM DUAL;
Returns the next result:
LOWER
-----
lower
ODBC Function
{fn LCASE (char)}
5.LPAD
Returns char1, left-padded to length n amongst the sequence of characters inward char2; char2 defaults to a unmarried blank. If char1 is longer than n, this component returns the share of char1 that fits inward n.
The declaration n is the full length of the render value every bit it is displayed on your terminal screen. In nearly grapheme sets, this is besides the expose of characters inward the render value. However, inward some multi-byte grapheme sets, the display length of a grapheme string tin differ from the expose of characters inward the string.
Syntax
LPAD(char1,n [,char2])
Example
SELECT LPAD('Page1',15,'*.') "LPAD example" FROM DUAL;
Returns the next result:
LPAD example
-----------------
*.*.*.*.*.Page1
6.LTRIM
Returns the string declaration char, amongst its left-most characters removed upward to the foremost grapheme which is non inward the string declaration set, which defaults to (a unmarried space).
Syntax:
LTRIM(char [, set])
Example:
SELECT LTRIM ('xyxXxyLAST WORD','xy') "LTRIM example" FROM DUAL;
Returns the next result:
LTRIM example
---------------
XxyLAST WORD
ODBC Function
{fn LTRIM (char) } (trims leading blanks)
7.RPAD
Returns char1, right-padded to length n amongst char2 replicated every bit many times every bit necessary; char2 defaults to a unmarried blank. If char1 is longer than n, this component returns the share of char1 that fits inward n.
The declaration n is the full length of the render value every bit it is displayed on your terminal screen. In nearly grapheme sets, this is besides the expose of characters inward the render value. However, inward some multi-byte grapheme sets, the display length of a grapheme string tin differ from the expose of characters inward the string.
Syntax:
RPAD(char1,n [,char2 ])
Example:
SELECT RPAD('ename',12,'ab') "RPAD example"
FROM emp
WHERE ename = 'TURNER';
Returns the next result:
RPAD example
-------------
enameabababa
8.RTRIM
Returns the string declaration char, amongst its right-most characters removed next the in conclusion grapheme which is non inward the string declaration set. This defaults to ' ' (a unmarried space).
Syntax
RTRIM(char [,set])
Example
SELECT RTRIM ('TURNERyxXxy', 'xy') "RTRIM example" FROM DUAL;
Returns the next result:
RTRIM examp
-----------
TURNERyxX
9.SUBSTR
Returns a share of the string declaration char, kickoff amongst the grapheme at seat one thousand too n characters long.
Syntax:
SUBSTR(char, one thousand [, n ])
Notes:
If one thousand is positive, SUBSTR counts from the kickoff of char to notice the foremost character. If one thousand is negative, SUBSTR counts backwards from the halt of char. The value one thousand cannot live on 0. If n is omitted, SUBSTR returns all characters to the halt of char. The value n cannot live on less than 1.
Example
SELECT SUBSTR('ABCDEFG',3,4) "Subs" FROM DUAL;
10.TRANSLATE
Syntax:
TRANSLATE(char, from, to)
Returns char amongst all occurrences of each grapheme inward from replaced past times its corresponding grapheme inward to, where char, from, too to are string arguments.
Example
SELECT TRANSLATE('2KRW229', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "Licence" FROM DUAL;
Returns the next result:
Licence
-------
9XXX999
11. TRIM
Syntax:
TRIM( [[<trim_spec >] char ]
FROM ] string )
If <trim_spec> is omitted, therefore BOTH is implied. If char is omitted, therefore a infinite grapheme 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 next result:
TRIM('
------
MAN
12.UPPER
Syntax
UPPER(char)
Returns the string declaration char amongst all its letters converted to uppercase. The render value has the same datatype every bit char.
Example
SELECT UPPER('Carol') FROM DUAL;
Returns the next result:
UPPER
-----
CAROL
Aggregate functions inward SQL:
Aggregate functions render a unmarried effect row based on groups of rows, rather than on unmarried rows. Aggregate functions tin appear inward select lists too inward ORDER BY too HAVING clauses.
They are normally used amongst the GROUP BY clause inward a SELECT statement, where Oracle Database divides the rows of a queried tabular array or sentiment into groups. In a enquiry containing a GROUP BY clause, the elements of the select listing tin live on aggregate functions, GROUP BY expressions, constants, or expressions involving 1 of these. Oracle applies the aggregate functions to each grouping of rows too returns a unmarried effect row for each group.
This component tin produced a unmarried value for an entire grouping or table.They operate on sets of rows too render results based on groups of rows.
The full general syntax for nearly of the aggregate component is every bit 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 next result:
AVG(SAL)
---------
2073.21
COUNT:
Syntax:
COUNT([* | [DISTINCT | ALL] expr})
Returns the expose of rows inward the query.
Example :
SELECT COUNT(*) "Total" FROM emp;
Returns the next result:
Total
----------
14
MAX:
Syntax:
MAX([DISTINCT | ALL] expr)
Returns the maximum value of an appear specified past times the declaration expr.
Example
SELECT MAX(SAL) FROM EMP;
Returns the next result:
MAX(SAL)
---------
5000
MIN:
Syntax:
MIN([DISTINCT | ALL] expr)
Returns the minimum value of an appear specified past times the declaration expr.
Example:
SELECT MIN(SAL), MAX(SAL) FROM EMP;
Returns the next result:
MIN(SAL)
---------
800
SUM:
Syntax:
SUM([DISTINCT | ALL] n)
Returns the center of values of n.
Example
SELECT deptno, SUM(sal) TotalSalary FROM emp GROUP BY deptno;
Returns the next result:
DEPTNO TOTALSALARY
--------- -----------
10 8750
20 10875
30 9400
Functions
Description
SQL Count component
The SQL COUNT component returns the expose of rows inward a tabular array satisfying the criteria specified inward the WHERE clause. It sets on the expose of rows or non NULL column values.
SQL Sum component
The SQL AGGREGATE SUM() component returns the center of all selected column.
SQL Avg component
The SQL AVG component calculates the average value of a column of numeric type. It returns the average of all non NULL values
SQL Max component
The aggregate component SQL MAX() is used to notice the maximum value or highest value of a certainly column or expression. This component is useful to create upward one's take heed the largest of all selected values of a column.
SQL Min component
The aggregate component SQL MIN() is used to notice the minimum value or lowest value of a column or expression. This component is useful to create upward one's take heed the smallest of all selected values of a column.
Date Functions inward SQL:
ADD_MONTHS
LAST_DAY
MONTHS_BETWEEN:
NEXT_DAY:
SYSDATE:
ADD_MONTHS:Adds the specified expose of months to a date.
LAST_DAY:Returns the in conclusion 24-hour interval inward the calendar month of the specified date.
MONTHS_ BETWEEN:Calculates the expose of months betwixt ii dates.
NEW_TIME:Returns the date/time value, amongst the fourth dimension shifted every bit requested past times the specified fourth dimension zones.
NEXT_DAY:Returns the appointment of the foremost weekday specified that is afterwards than the date.
SYSDATE:Returns the electrical flow appointment too fourth dimension inward the Oracle Server.
Conversion Functions:
Conversion functions convert a value from 1 datatype to another. Generally, the classify of the component advert follows the convention datatype TO datatype. The foremost datatype is the input datatype; the in conclusion datatype is the output datatype. There are five conversion functions.
i.CAST
ii.TO_ CHAR
iii.TO _DATE
iv.CONVERT
v.TO _NUMBER
i.CAST:
Converts information from 1 type to some other type.
Syntax:
SELECT CAST ( <source_operand > AS <data_type > ) FROM DUAL;
ii.TO_ CHAR:
Converts a appointment or expose 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 component takes grapheme values every bit input too returns formatted appointment equivalent of the same. The TO_DATE component allows users to locomote into a appointment inward whatever format, too therefore 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 next result:
TO_CHAR(TO_DATE('JANUARY26’)
----------------------------------------
1996-01-26 12:38:00
iv.CONVERT:
Converts a grapheme string from 1 grapheme gear upward to another.
The value_exp declaration is the value to live on converted.
The data_type declaration is the advert of the grapheme gear upward to which char is converted.
Syntax:
{ fn CONVERT(value_exp, data_type) }
Example
SELECT {fn CONVERT('Groß', 'US7ASCII') }
"Conversion" FROM DUAL;
Returns the next result:
conversi
--------
Groß
v.TO _NUMBER:
The TO_NUMBER component converts a grapheme value to a numeric datatype. If the string beingness converted contains nonnumeric characters, the component 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 besides called every bit “Analytical function”
(II) This is ii types
• Rank
• Dense_rank
(III) These ii functions are used to calculate rank of a item value from the gear upward of values.
i.Rank:
Calculates the rank of a value inward a grouping of values
Syntax :-
Rank( ) over (set of values)
ii.Dense_rank:
The DENSE_RANK component acts similar the RANK component except that it assigns consecutive ranks.
Syntax:
Dense_rank() over [set of values]
General Functions:
General functions are used to grip NULL values inward database. The objective of the full general NULL treatment functions is to supersede the NULL values amongst an alternate value. We shall briefly come across through these functions below.
NVL:
The NVL component substitutes an alternate value for a NULL value.
Syntax:
NVL( Arg1, replace_with )
NVL2
As an enhancement over NVL, Oracle introduced a component to substitute value non simply for NULL columns values but besides for NOT NULL columns. NVL2 component tin live on used to substitute an alternate value for NULL every bit good every bit 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 component compares ii arguments expr1 too expr2. If expr1 too expr2 are equal, it returns NULL; else, it returns expr1. Unlike the other goose egg treatment function, foremost declaration can't live on NULL.
Syntax:
NULLIF (expr1, expr2)
COALESCE:
COALESCE function, a to a greater extent than generic classify of NVL, returns the foremost non-null appear inward the declaration list. It takes minimum ii mandatory parameters but maximum arguments has no limit.
Syntax:
COALESCE (expr1, expr2, ... expr_n )
GROUP BY clause:
The GROUP BY clause volition assemble all of the rows together that comprise information inward the specified column(s) too volition allow aggregate functions to live on performed on the 1 or to a greater extent than columns. This tin best live on explained past times an example:
GROUP BY clause
syntax:
SELECT column1,
SUM(column2)
FROM "list-of-tables"
GROUP BY "column-list";
Let's tell y'all would similar to recollect a listing of the highest paid salaries inward each dept:
SELECT max(salary), dept
FROM employee
GROUP BY dept;
This declaration volition select the maximum salary for the people inward each unique department. Basically, the salary for the mortal who makes the nearly inward each subdivision volition live on displayed. Their, salary too their subdivision volition live on returned.
The HAVING clause:
The HAVING clause enables y'all to specify weather condition that filter which grouping results appear inward the in conclusion results.
The WHERE clause places weather condition on the selected columns, whereas the HAVING clause places weather condition on groups created past times 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 tin live on a component such every bit SUM, COUNT, MIN, MAX, or AVG functions.
expression1, expression2, ... expression_n are expressions that are non encapsulated inside an aggregate component too must live on included inward the GROUP BY Clause.
condition is the status that is used to restrain the groups of returned rows. Only those groups whose status evaluates to TRUE volition live on included inward the effect set.
ORDER BY clause:
Description:
The SQL ORDER BY clause is used to form the records inward the effect gear upward for a SELECT statement.
Syntax:
The syntax for the SQL ORDER BY clause is:
SELECT expressions
FROM tables
WHERE conditions
ORDER BY appear [ ASC | DESC ];
Parameters or Arguments
expressions are the columns or calculations that y'all wishing to retrieve.
tables are the tables that y'all wishing to recollect records from. There must live on at to the lowest degree 1 tabular array listed inward the FROM clause.
conditions are weather condition that must live on met for the records to live on selected.
ASC is optional. It sorts the effect gear upward inward ascending lodge past times appear (default, if no modifier is provider).
DESC is optional. It sorts the effect gear upward inward descending lodge past times expression.
Sumber http://www.gcreddy.com/
SQL functions are similar to SQL operators inward that both manipulate information items too both render a result. SQL functions differ from SQL operators inward the format inward which they appear amongst their arguments. The SQL component format enables functions to operate amongst zero, one, or to a greater extent than arguments.
function(argument1, argument2, ...) alias
SQL functions are used solely amongst SQL commands inside SQL statements. There are ii full general types of SQL functions: unmarried row (or scalar) functions too aggregate functions. These ii types differ inward the expose of database rows on which they act. H5N1 unmarried row component returns a value based on a unmarried row inward a query, whereas an aggregate component returns a value based on all the rows inward a query.
SQL arithmetics functions are :
Functions Description
abs()
This SQL ABS() returns the absolute value of a expose passed every bit argument.
ceil()
This SQL CEIL() volition rounded upward whatever positive or negative decimal value inside the component upwards.
floor()
The SQL FLOOR() rounded upward whatever positive or negative decimal value downwards to the side past times side to the lowest degree 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 operations of natural algorithm too the value of e is unopen to 2.71828183.
ln()
The SQL LN() component returns the natural logarithm of n, where n is greater than 0 too its base of operations is a expose equal to unopen to 2.71828183.
mod()
This SQL MOD() component returns the residuum from a division.
power()
This SQL POWER() component returns the value of a expose raised to another, where both of the numbers are passed every bit arguments.
sqrt()
The SQL SQRT() returns the foursquare origin of given value inward the argument.
SQL Character Functions:
Character functions that render grapheme values render values of the same datatype every bit the input argument. The length of the value returned past times the component is limited past times the maximum length of the datatype returned.
• For functions that render CHAR or VARCHAR2, if the length of the render value exceeds the limit, therefore Oracle Database truncates it too returns the effect without an mistake message.
• For functions that render CLOB values, if the length of the render values exceeds the limit, therefore Oracle raises an mistake too returns no data.
1.CHR:
Returns the grapheme amongst the binary equivalent to n inward the database grapheme set.
Syntax:
CHR (n)
Example:
SELECT CHR(68)||CHR(79)||CHR(71) "Dog" FROM DUAL;
Returns the next result:
Dog
---
DOG
2.CONCAT
Returns char1 concatenated amongst char2, where char1 too char2 are string arguments. This component is equivalent to the concatenation operator (||).
Syntax:
CONCAT(char1, char2)
Example
This instance uses nesting to concatenate iii grapheme strings:
SELECT CONCAT( CONCAT(ename, ' is a '), job) "Job"
FROM emp
WHERE empno = 7900;
3.INITCAP
Returns char, amongst the foremost missive of the alphabet of each give-and-take inward uppercase, all other letters inward lowercase. Words are delimited past times white infinite or characters that are non alphanumeric.
Syntax
INITCAP(char)
Example
SELECT INITCAP('the soap') "Capitals" FROM DUAL;
Returns the next result:
Capitals
--------
The Soap
4.LOWER
Returns a string declaration char, amongst all its letters inward lowercase. The render value has the same datatype every bit char, either CHAR or VARCHAR2.
Syntax:
LOWER(char)
Example
SELECT LOWER('LOWER') FROM DUAL;
Returns the next result:
LOWER
-----
lower
ODBC Function
{fn LCASE (char)}
5.LPAD
Returns char1, left-padded to length n amongst the sequence of characters inward char2; char2 defaults to a unmarried blank. If char1 is longer than n, this component returns the share of char1 that fits inward n.
The declaration n is the full length of the render value every bit it is displayed on your terminal screen. In nearly grapheme sets, this is besides the expose of characters inward the render value. However, inward some multi-byte grapheme sets, the display length of a grapheme string tin differ from the expose of characters inward the string.
Syntax
LPAD(char1,n [,char2])
Example
SELECT LPAD('Page1',15,'*.') "LPAD example" FROM DUAL;
Returns the next result:
LPAD example
-----------------
*.*.*.*.*.Page1
6.LTRIM
Returns the string declaration char, amongst its left-most characters removed upward to the foremost grapheme which is non inward the string declaration set, which defaults to (a unmarried space).
Syntax:
LTRIM(char [, set])
Example:
SELECT LTRIM ('xyxXxyLAST WORD','xy') "LTRIM example" FROM DUAL;
Returns the next result:
LTRIM example
---------------
XxyLAST WORD
ODBC Function
{fn LTRIM (char) } (trims leading blanks)
7.RPAD
Returns char1, right-padded to length n amongst char2 replicated every bit many times every bit necessary; char2 defaults to a unmarried blank. If char1 is longer than n, this component returns the share of char1 that fits inward n.
The declaration n is the full length of the render value every bit it is displayed on your terminal screen. In nearly grapheme sets, this is besides the expose of characters inward the render value. However, inward some multi-byte grapheme sets, the display length of a grapheme string tin differ from the expose of characters inward the string.
Syntax:
RPAD(char1,n [,char2 ])
Example:
SELECT RPAD('ename',12,'ab') "RPAD example"
FROM emp
WHERE ename = 'TURNER';
Returns the next result:
RPAD example
-------------
enameabababa
8.RTRIM
Returns the string declaration char, amongst its right-most characters removed next the in conclusion grapheme which is non inward the string declaration set. This defaults to ' ' (a unmarried space).
Syntax
RTRIM(char [,set])
Example
SELECT RTRIM ('TURNERyxXxy', 'xy') "RTRIM example" FROM DUAL;
Returns the next result:
RTRIM examp
-----------
TURNERyxX
9.SUBSTR
Returns a share of the string declaration char, kickoff amongst the grapheme at seat one thousand too n characters long.
Syntax:
SUBSTR(char, one thousand [, n ])
Notes:
If one thousand is positive, SUBSTR counts from the kickoff of char to notice the foremost character. If one thousand is negative, SUBSTR counts backwards from the halt of char. The value one thousand cannot live on 0. If n is omitted, SUBSTR returns all characters to the halt of char. The value n cannot live on less than 1.
Example
SELECT SUBSTR('ABCDEFG',3,4) "Subs" FROM DUAL;
10.TRANSLATE
Syntax:
TRANSLATE(char, from, to)
Returns char amongst all occurrences of each grapheme inward from replaced past times its corresponding grapheme inward to, where char, from, too to are string arguments.
Example
SELECT TRANSLATE('2KRW229', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "Licence" FROM DUAL;
Returns the next result:
Licence
-------
9XXX999
11. TRIM
Syntax:
TRIM( [[<trim_spec >] char ]
FROM ] string )
If <trim_spec> is omitted, therefore BOTH is implied. If char is omitted, therefore a infinite grapheme 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 next result:
TRIM('
------
MAN
12.UPPER
Syntax
UPPER(char)
Returns the string declaration char amongst all its letters converted to uppercase. The render value has the same datatype every bit char.
Example
SELECT UPPER('Carol') FROM DUAL;
Returns the next result:
UPPER
-----
CAROL
Aggregate functions inward SQL:
Aggregate functions render a unmarried effect row based on groups of rows, rather than on unmarried rows. Aggregate functions tin appear inward select lists too inward ORDER BY too HAVING clauses.
They are normally used amongst the GROUP BY clause inward a SELECT statement, where Oracle Database divides the rows of a queried tabular array or sentiment into groups. In a enquiry containing a GROUP BY clause, the elements of the select listing tin live on aggregate functions, GROUP BY expressions, constants, or expressions involving 1 of these. Oracle applies the aggregate functions to each grouping of rows too returns a unmarried effect row for each group.
This component tin produced a unmarried value for an entire grouping or table.They operate on sets of rows too render results based on groups of rows.
The full general syntax for nearly of the aggregate component is every bit 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 next result:
AVG(SAL)
---------
2073.21
COUNT:
Syntax:
COUNT([* | [DISTINCT | ALL] expr})
Returns the expose of rows inward the query.
Example :
SELECT COUNT(*) "Total" FROM emp;
Returns the next result:
Total
----------
14
MAX:
Syntax:
MAX([DISTINCT | ALL] expr)
Returns the maximum value of an appear specified past times the declaration expr.
Example
SELECT MAX(SAL) FROM EMP;
Returns the next result:
MAX(SAL)
---------
5000
MIN:
Syntax:
MIN([DISTINCT | ALL] expr)
Returns the minimum value of an appear specified past times the declaration expr.
Example:
SELECT MIN(SAL), MAX(SAL) FROM EMP;
Returns the next result:
MIN(SAL)
---------
800
SUM:
Syntax:
SUM([DISTINCT | ALL] n)
Returns the center of values of n.
Example
SELECT deptno, SUM(sal) TotalSalary FROM emp GROUP BY deptno;
Returns the next result:
DEPTNO TOTALSALARY
--------- -----------
10 8750
20 10875
30 9400
Functions
Description
SQL Count component
The SQL COUNT component returns the expose of rows inward a tabular array satisfying the criteria specified inward the WHERE clause. It sets on the expose of rows or non NULL column values.
SQL Sum component
The SQL AGGREGATE SUM() component returns the center of all selected column.
SQL Avg component
The SQL AVG component calculates the average value of a column of numeric type. It returns the average of all non NULL values
SQL Max component
The aggregate component SQL MAX() is used to notice the maximum value or highest value of a certainly column or expression. This component is useful to create upward one's take heed the largest of all selected values of a column.
SQL Min component
The aggregate component SQL MIN() is used to notice the minimum value or lowest value of a column or expression. This component is useful to create upward one's take heed the smallest of all selected values of a column.
Date Functions inward SQL:
ADD_MONTHS
LAST_DAY
MONTHS_BETWEEN:
NEXT_DAY:
SYSDATE:
ADD_MONTHS:Adds the specified expose of months to a date.
LAST_DAY:Returns the in conclusion 24-hour interval inward the calendar month of the specified date.
MONTHS_ BETWEEN:Calculates the expose of months betwixt ii dates.
NEW_TIME:Returns the date/time value, amongst the fourth dimension shifted every bit requested past times the specified fourth dimension zones.
NEXT_DAY:Returns the appointment of the foremost weekday specified that is afterwards than the date.
SYSDATE:Returns the electrical flow appointment too fourth dimension inward the Oracle Server.
Conversion Functions:
Conversion functions convert a value from 1 datatype to another. Generally, the classify of the component advert follows the convention datatype TO datatype. The foremost datatype is the input datatype; the in conclusion datatype is the output datatype. There are five conversion functions.
i.CAST
ii.TO_ CHAR
iii.TO _DATE
iv.CONVERT
v.TO _NUMBER
i.CAST:
Converts information from 1 type to some other type.
Syntax:
SELECT CAST ( <source_operand > AS <data_type > ) FROM DUAL;
ii.TO_ CHAR:
Converts a appointment or expose 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 component takes grapheme values every bit input too returns formatted appointment equivalent of the same. The TO_DATE component allows users to locomote into a appointment inward whatever format, too therefore 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 next result:
TO_CHAR(TO_DATE('JANUARY26’)
----------------------------------------
1996-01-26 12:38:00
iv.CONVERT:
Converts a grapheme string from 1 grapheme gear upward to another.
The value_exp declaration is the value to live on converted.
The data_type declaration is the advert of the grapheme gear upward to which char is converted.
Syntax:
{ fn CONVERT(value_exp, data_type) }
Example
SELECT {fn CONVERT('Groß', 'US7ASCII') }
"Conversion" FROM DUAL;
Returns the next result:
conversi
--------
Groß
v.TO _NUMBER:
The TO_NUMBER component converts a grapheme value to a numeric datatype. If the string beingness converted contains nonnumeric characters, the component 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 besides called every bit “Analytical function”
(II) This is ii types
• Rank
• Dense_rank
(III) These ii functions are used to calculate rank of a item value from the gear upward of values.
i.Rank:
Calculates the rank of a value inward a grouping of values
Syntax :-
Rank( ) over (set of values)
ii.Dense_rank:
The DENSE_RANK component acts similar the RANK component except that it assigns consecutive ranks.
Syntax:
Dense_rank() over [set of values]
General Functions:
General functions are used to grip NULL values inward database. The objective of the full general NULL treatment functions is to supersede the NULL values amongst an alternate value. We shall briefly come across through these functions below.
NVL:
The NVL component substitutes an alternate value for a NULL value.
Syntax:
NVL( Arg1, replace_with )
NVL2
As an enhancement over NVL, Oracle introduced a component to substitute value non simply for NULL columns values but besides for NOT NULL columns. NVL2 component tin live on used to substitute an alternate value for NULL every bit good every bit 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 component compares ii arguments expr1 too expr2. If expr1 too expr2 are equal, it returns NULL; else, it returns expr1. Unlike the other goose egg treatment function, foremost declaration can't live on NULL.
Syntax:
NULLIF (expr1, expr2)
COALESCE:
COALESCE function, a to a greater extent than generic classify of NVL, returns the foremost non-null appear inward the declaration list. It takes minimum ii mandatory parameters but maximum arguments has no limit.
Syntax:
COALESCE (expr1, expr2, ... expr_n )
GROUP BY clause:
The GROUP BY clause volition assemble all of the rows together that comprise information inward the specified column(s) too volition allow aggregate functions to live on performed on the 1 or to a greater extent than columns. This tin best live on explained past times an example:
GROUP BY clause
syntax:
SELECT column1,
SUM(column2)
FROM "list-of-tables"
GROUP BY "column-list";
Let's tell y'all would similar to recollect a listing of the highest paid salaries inward each dept:
SELECT max(salary), dept
FROM employee
GROUP BY dept;
This declaration volition select the maximum salary for the people inward each unique department. Basically, the salary for the mortal who makes the nearly inward each subdivision volition live on displayed. Their, salary too their subdivision volition live on returned.
The HAVING clause:
The HAVING clause enables y'all to specify weather condition that filter which grouping results appear inward the in conclusion results.
The WHERE clause places weather condition on the selected columns, whereas the HAVING clause places weather condition on groups created past times 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 tin live on a component such every bit SUM, COUNT, MIN, MAX, or AVG functions.
expression1, expression2, ... expression_n are expressions that are non encapsulated inside an aggregate component too must live on included inward the GROUP BY Clause.
condition is the status that is used to restrain the groups of returned rows. Only those groups whose status evaluates to TRUE volition live on included inward the effect set.
ORDER BY clause:
Description:
The SQL ORDER BY clause is used to form the records inward the effect gear upward for a SELECT statement.
Syntax:
The syntax for the SQL ORDER BY clause is:
SELECT expressions
FROM tables
WHERE conditions
ORDER BY appear [ ASC | DESC ];
Parameters or Arguments
expressions are the columns or calculations that y'all wishing to retrieve.
tables are the tables that y'all wishing to recollect records from. There must live on at to the lowest degree 1 tabular array listed inward the FROM clause.
conditions are weather condition that must live on met for the records to live on selected.
ASC is optional. It sorts the effect gear upward inward ascending lodge past times appear (default, if no modifier is provider).
DESC is optional. It sorts the effect gear upward inward descending lodge past times expression.