FUNCTIONS
A function is nothing but a sub program or a module which can
written for achieving a particular task
OR
A function is a readymade formula, which takes values as input
and returns values as output.
OR
A function is a pre defined program segment that carries
specific and well-defined task.
CLASSIFICATIONS OF FUNCTIONS:
1. SCALAR OR SINGLE-VALUED FUNCTIONS
2. GROUP OR AGGREGATE FUNCTIONS
SCALAR VALUED FUNCTIONS:
These functions takes single value as input and returns single value as output
CLASSIFICATION OF SCALAR VALUED FUNCTIONS:
·
MATHEMATICAL FUNCTIONS
·
STRING FUNCTIONS
·
DATE & TIME FUNCTIONS
MATHEMATICAL FUNCTIONS:
ABSOLUTE: IT RETURN
THE ABSOLUTE VALUE OF N
|
10.65
|
à
SELECT ABS(18.69)
|
18.67
|
POWER (M, N): IT RETURN THE M POWER N VALUE
|
9
|
à
SELECT POWER(6,2)
|
36
|
SQRT(N): IT RETURN
THE SQUARE ROOT VALUE OF N
à
SELECT SQRT (2)
SQUARE (N): IT
RETURN THE SQUARE VALUE OF N
|
81
|
à
SELECT SQUARE (5)
|
25
|
ROUND (M, N): IT
WILL ROUND THE VALUE OF M TO NEAREST WHOLE NUMBER OF IT WILL AROUND.
|
15
|
à
SELECT ROUND (16.513)
|
16
|
à
SELECT ROUND (16.816)
|
17
|
à
SELECT ROUND (21.132,1)
|
21.1
|
à
SELECT ROUND (25.143)
|
25
|
CEILING: IT (CEILING) RETURNS THE
SMALLEST INTEGER GREATER THAN ‘N’
à
SELECT CEILING (15.13)
|
16
|
FLOOR (N): IT RETURN
THE LARGEST INTEGER LESS THAN ‘N’
à
SELECT FLOOR (15.13)
|
15
|
STRING FUNCTIONS:
ASCII (CH): IT
RETURNS THE ASCII VALUE OF GIVEN CHARACTER.
|
65
|
à
SELECT ASCII (‘a’)
|
97
|
à
SELECT ASCII (‘0’)
|
48
|
à
SELECT ASCII (‘’)
|
32
|
CHAR (N): IT RETURNS
THE CHARACTER FOR GIVEN ASCII VALUE
|
A
|
à
SELECT CHAR (98)
|
b
|
LOWER (STRING): IT CONVERTS THE UPPER CASE
LETTERS IN STRING INTO LOWER CASES
|
nrstt
|
UPPER (STRING): IT
CONVERTS THE LOWER CASES INTO UPPER CASES
à
SELECT UPPER (‘nrstt’)
|
NRSTT
|
à
SELECT ENAME, LOWER (ENAME) LOWER_ENAMES FROM EMP
ENAME LOWER-ENAME
SMITH SMITH
ALLEN ALLEN
LEN (STRING): IT
RETURNS THE LENGTH OF GIVEN STRING
|
9
|
à
SELECT LEN(‘SQL SERVER’)
|
10
|
SPACE IS ALSO A CHARACTER
à
SELECT ENAME, LEN (‘ENAME’) LENGTH FROM EMP
ENAME LENGTH
SMITH 5
ALLEN 5
MILLER 6
REVERSE (STRING): IT
WILL REVERSE THE GIVEN STRING
REVRESLQS
àSELECT
ENAME, REVERSE (ENAME) REV_ENAME FROM EMP
ENAME REV_ENAME
SMITH HTIMS
ALLEN NELLA
MILLER RELLIM
REPLACE (STRING,
‘SEARCHING STRING’, ‘REPLACING STRING’)
à
IT WILL REPLACE A SUB STRING IN ASTRING WITH GIVEN STRING
à
SELECT REPLACE (‘WRONG’, ‘WR’, ‘R’)
|
RONG
|
à
SELECT REPLACE (‘JACK AND JUE’, ‘J’, ‘BI’)
|
BLACK
AND BLUE
|
SUB STRING (‘STRING’, ‘STARTING POSITION’, NO
OF CHARACTERS REQUIRED);
IT WILL ENTRACT A SUBSTRING FLOW MAIN STRING
EX:à SELECT SUBSTRING (‘ORACLE’, 1, 3’)
|
ORA
|
|
RA
|
àSELECT
ENAME, SUBSTRING (ENAME,1,2) SUB_ENAME FROM EMP
ENAME SUB_ENAME
SMITH SM
ALLEN AL
DATE FUNCTIONS:
GET DATE (): IT RETURNS THE CURRENT DATE AND TIME
2005-4-5: 4:30.15.416
DATE PART ABBREVIATION
YEAR YY
QUARTER QQ
MONTH MM
WEEK WW
DAY DD
HOUR HH
MINUTE MM
SIZE SS
MILLISEC MS
DATE ADD (DATE PART,
NUMBER, DATE): IT RETURNS THE DATE ACCORDING TO DATE PART.
à
SELECT DATE (YY,1,’2005-3-15’)
|
2006-3-15
|
à
SELECT DATEADD (MM,1,’1998-4-18’)
|
1998-5-18
|
à
SELECT DATE ADD (DAY,1,’1999-04-26’)
|
1999-04-27
|
DATE DIFF (DATEPART,
DATE 1, DATE 2): IT RETURNS THE DIFFERENCE BETWEEN THE DATES ACCORDING TO
THE DATE PART
à
SELECT DATEDIFF (YEAR,'1999-11-5','2006-11-15')
|
7
|
DATEPART(PART,DATE): IT
RETURNS THE PART OF THE GIVEN DATE
SELECT DATEPART(DAY,’2007-09-13’)
OUTPUT: 13
DATENAME(PART,DATE): IT
RETURNS THE NAME OF THE GIVEN STRING
SELECT DATENAME(MONTH,’2007-10-31’)
OUTPUT: OCTOBER
AGGRIGATE OR GROUP FUNCTIONS:
These functions takes multiple values as input and returns single value as
output, these includes
MAX ( )
MIN ( )
SUM ( )
AVG ( )
COUNT ( )
COUNT_BIG ( )
EMPNO ENAME SAL DEPTNO
----------- --------------------
--------------------- -----------
11 RAM 15000.0000 10
22 RAJ 20000.0000 20
33 ABI 10000.0000 10
44 DP 5000.0000 20
55 NAAG 5000.0000 10
Based on the above table we will do
some examples for Aggregate functions
E.g.: SELECT MAX (SAL) AS HISAL FROM
EMP
HISAL
20000
E.g.: SELECT MAX (SAL) AS HISAL, MIN
(SAL) AS LOSAL FROM EMP
HISAL LOSAL
20000 5000
E.g.: SELECT MAX (SAL) AS HISAL, MIN
(SAL) AS LOSAL,
SUM (SAL) AS TOTSAL, AVG (SAL) AS
AVGSAL FROM EMP
HISAL LOSAL TOTSAL AVGSAL
20000 5000 55000 11000
E.g.: SELECT MAX
(SAL) AS HISAL, MIN (SAL) AS LOSAL,
SUM (SAL) AS
TOTSAL, AVG (SAL) AS AVGSAL FROM EMP WHERE DEPTNO=10
HISAL LOSAL TOTSAL AVGSAL
15000 5000 30000 10000
E.g.: SELECT MAX
(SAL) AS HISAL, MIN (SAL) AS LOSAL,
SUM (SAL) AS
TOTSAL, AVG (SAL) AS AVGSAL FROM EMP WHERE DEPTNO=20
HISAL LOSAL TOTSAL AVGSAL
20000 5000 25000 12500
No comments:
Post a Comment