Built IN Functions:
SQL provides many built-in functions to perform operations on data. These functions are useful while performing mathematical calculations, string concatenations, sub-strings etc.
Scalar functions
Numeric Functions
|
Function
|
Description
|
|
abs(-10.67)
|
This returns absolute number of the given number means 10.67.
|
|
rand(10)
|
This will generate random number of 10 characters.
|
|
round(17.56719,3)
|
This will round off the given number to 3 places of decimal means
17.567
|
Character Functions
|
Function
|
Description
|
|
upper('dotnet')
|
This will returns upper case of given string means 'DOTNET'
|
|
lower('DOTNET')
|
This will returns lower case of given string means 'dotnet'
|
|
ltrim(' dotnet')
|
This will remove the spaces from left hand side of 'dotnet' string.
|
|
LEN(‘dotnet’)
|
Returns no of characters in the string given.(6)
|
|
LEFT('Dotnet',3)
|
Returns first 3 characters from
the string left side of the string.(DOT)
|
|
RIGHT('Dotnet',3)
|
Returns last 3 characters from
the string Right side of the string.(NET)
|
|
SUBSTRING('Dotnet',3,2)
|
Returns 2 characters starting
from 3 character. (tn)
|
Conversion Functions
|
Function
|
Description
|
|
SELECT CONVERT(DECIMAL(5,2),15)
|
Returns 15.00
|
|
CONVERT (INT,
15.56)
|
This will convert the given float value to integer means 15.
|
DECLARE @A varchar(2)
DECLARE @B varchar(2)
DECLARE @C varchar(2)
set @A=25
set @B=15
set @C=33
Select CAST(@A as int) + CAST(@B as int) +CAST (@C as int) as Result
-------------------------
Result
73
---------------------------
DECLARE @A1 INT
DECLARE @B1 INT
DECLARE @C1 INT
set @A1=25
set @B1=15
set @C1=33
Select @A1+@B1+@C1 as Result
-------------------------
Result
73
---------------------------
DECLARE @A2 varchar(2)
DECLARE @B2 varchar(2)
DECLARE @C2 varchar(2)
set @A2=25
set @B2=15
set @C2=33
Select @A2+@B2+@C2 as Result
-------------------------
Result
251533
---------------------------
Date Functions:
Date functions are used to convert date from one format to another.
SELECT GETDATE() - Returns current date
SELECT DATEADD(DAY,5,GETDATE()), - Adds 5 days to the current date.
SELECT DATEDIFF(yyyy,'2011/01/01','2017/01/01'). Show is difference between the given two days.
based on the iintervel given, returns difference in days, months , years etc... (check the table given) format.
Syntax :
DATEDIFF(datepart,startdate,enddate)
|
datepart
|
Abbreviation
|
|
year
|
yy, yyyy
|
|
quarter
|
qq, q
|
|
month
|
mm, m
|
|
dayofyear
|
dy, y
|
|
day
|
dd, d
|
|
week
|
wk, ww
|
|
weekday
|
dw, w
|
|
hour
|
hh
|
|
minute
|
mi, n
|
|
second
|
ss, s
|
|
millisecond
|
ms
|
|
microsecond
|
mcs
|
|
nanosecond
|
ns
|
Convert:
Convert function is to convert one date format to another date format.
Syntax:
CONVERT(data_type(length),expression,style)
SELECT CONVERT(VARCHAR(19),GETDATE())
SELECT CONVERT(VARCHAR(10),GETDATE(),10)
SELECT CONVERT(VARCHAR(10),GETDATE(),110)
SELECT CONVERT(VARCHAR(11),GETDATE(),6)
SELECT CONVERT(VARCHAR(11),GETDATE(),106)
SELECT CONVERT(VARCHAR(24),GETDATE(),113)
DATEPART
Date part function returns the required part from the date
given.
SELECT DATEPART(yyyy,GETDATE())
SELECT DATEPART(mm,GETDATE())
SELECT DATEPART(dd,GETDATE())
SELECT DATEPART(day,GETDATE())
SELECT DATEPART(dy,GETDATE())
|
Date/time part
|
Abbreviations
|
|
year
|
yy, yyyy
|
|
quarter
|
qq, q
|
|
month
|
mm, m
|
|
day of year
|
dy, y
|
|
day
|
dd, d
|
|
week
|
wk, ww
|
|
weekday
|
dw
|
|
hour
|
hh
|
|
minute
|
mi, n
|
|
second
|
ss, s
|
|
millisecond
|
ms
|
Aggregate functions:
MIN, MAX, SUM, AVG, COUNT
|
Aggregate Function
|
Description
|
|
max()
|
This returns maximum value from a collection of values.
|
|
min()
|
This returns minimum value from a collection of values.
|
|
avg()
|
This returns average of all values in a collection.
|
|
count()
|
This returns no of counts from a collection of values.
|
CREATE TABLE #tmp_Aggfun(
StudentName VARCHAR(50),
Sub VARCHAR(50),
Marks INT
)
INSERT INTO #tmp_Aggfun VALUES ('S1','Telugu',95),('S1','English',90),('S1','Maths',99),('S1','Science',85),('S1','Social',79)
INSERT INTO #tmp_Aggfun VALUES ('S2','Telugu',64),('S2','English',91),('S2','Maths',98),('S2','Science',86),('S2','Social',71)
INSERT INTO #tmp_Aggfun VALUES ('S3','Telugu',74),('S3','English',92),('S3','Maths',97),('S3','Science',87),('S3','Social',72)
INSERT INTO #tmp_Aggfun VALUES ('S4','Telugu',75),('S4','English',93),('S4','Maths',96),('S4','Science',82),('S4','Social',74)
INSERT INTO #tmp_Aggfun VALUES ('S5','Telugu',76),('S5','English',94),('S5','Maths',95),('S5','Science',81),('S5','Social',78)
SELECT * FROM #tmp_Aggfun
SELECT MAX(Marks) FROM #tmp_Aggfun WHERE
Sub='Telugu'
SELECT MIN(Marks) FROM #tmp_Aggfun WHERE
Sub='Telugu'
SELECT AVG(Marks) FROM #tmp_Aggfun WHERE
Sub='Telugu'
SELECT COUNT(Marks) FROM #tmp_Aggfun WHERE
Sub='Telugu'
SELECT SUM(Marks) FROM #tmp_Aggfun WHERE
Sub='Telugu'
-------------------------
Result
95
64
76
5
384
--------------------------------
No comments:
Post a Comment