SQL Transactions

Transactions
A transaction is a logical unit of work containing one or more steps, each of which must complete successfully in order for the transaction to commit to the database. If there are errors, then all of the data modifications are erased and the database is rolled back to its initial state at the start of the transaction

Simple Transaction
Example:
BEGIN TRANSACTION
    INSERT INTO Employees(EmpID, DeletedDate, User)
        (SELECT 123, GetDate(), CURRENT_USER);
    DELETE FROM Employees WHERE EmployeeID = 123;
COMMIT TRANSACTION

Rollback Transaction
When something fails in your transaction code and you want to undo it, you can rollback your transaction:
Example:
BEGIN TRY
    BEGIN TRANSACTION
        INSERT INTO Users(ID, Name, Age, PhNo)
        VALUES(1, 'Umesh', 24,88792916)
        DELETE FROM Users WHERE Name = 'Raj'
   COMMIT TRANSACTION
END TRY
BEGIN CATCH
   ROLLBACK TRANSACTION
END CATCH

SQL Subqueries

Subquery :
subquery is a SQL query within a query

Correlated Subqueries
Correlated (also known as Synchronized or Coordinated) Subqueries are nested queries that make references to the current row of their outer query:
Example:

SELECT EmployeeId FROM Employee AS eOuter WHERE Salary > (SELECT AVG(Salary) FROM Employee eInner WHERE eInner.DepartmentId = eOuter.DepartmentId)

Filter query results using query on different table
Example:
This query selects all employees not on the Supervisors table.
SELECT * FROM Employees WHERE EmployeeID not in (SELECT EmployeeID FROM Supervisors)
Same results can be achieved using a LEFT JOIN.

SELECT * FROM Employees AS e LEFT JOIN Supervisors AS s ON s.EmployeeID=e.EmployeeID WHERE s.EmployeeID is NULL

Subqueries in FROM clause
Example:
Use subqueries to define a temporary table and use it in the FROM clause of an "outer" query.

SELECT * FROM (SELECT city, temp_hi - temp_lo AS temp_var FROM weather) AS w WHERE temp_var > 20;

Subqueries in SELECT clause
Example:

SELECT w.*,  (SELECT c.state FROM cities AS c WHERE c.name = w.city ) AS state FROM weather AS w;

SQL Functions(Scalar/Single Row)

SQL have a rich set of functions that allow you to get information about strings and modify the contents of those strings in multiple ways. 

Functions
LOWER : This function converts alpha character values to lowercase.
Syntax: LOWER(SQL course)
Example:
SELECT LOWER('MANMA') FROM DUAL;
//Output: manma
UPPER : This function converts alpha character values to uppercase.
Syntax: UPPER(SQL course)
Example:
SELECT UPPER('mamna') FROM DUAL;
//Output: MAMNA
CONCAT : This function always appends ( concatenates ) string2 to the end of string1. If either of the string is NULL, CONCAT function returns the non-NULL argument. If both strings are NULL, CONCAT returns NULL.
Syntax:
 CONCAT('String1', 'String2')
//Example:
SELECT CONCAT('computer' ,'science') FROM DUAL;
//Output: computerscience
SELECT CONCAT( NULL ,NULL ) FROM DUAL;
//Output: - 
CAST and CONVERT
These functions convert an expression of one data type to another.
Example: Change the input datatype
Cast
Example
SELECT 9.5 AS Original,CAST(9.5 AS INT) AS [int], CAST(9.5 AS DECIMAL(6, 4)) AS [decimal];
//Output
Original              int             decimal
9.5                      9               9.50000
Convert
SELECT 9.5 AS Original,CONVERT(INT, 9.5) AS [int],CONVERT(DECIMAL(6, 4), 9.5) AS [decimal];
//Output
Original              int             decimal
9.5                      9               9.50000
TRY_CONVERT 
This function tries to convert an expression from one datatype to another datatype. If the conversion fails, the function will return NULL. Otherwise, it will return the converted value.
Syntax
The syntax for the TRY_CONVERT function in SQL Server (Transact-SQL) is:
TRY_CONVERT( type [ (length) ], expression [ , style ] )
type
The datatype that you wish to convert expression to. It can be one of the following: bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, or image.
length
Optional. The length of the resulting data type for char, varchar, nchar, nvarchar, binary and varbinary.
expression
The value to convert to another datatype.
style
Optional. The format used to convert between datatypes, such as a date format or string format. It can be one of the following values:
Converting datetime to character
Value (without century)  Value (with century) Explanation
0                                  100                                  mon dd yyyy hh:miAM/PM (Default)
1                                  101                                  mm/dd/yyyy (US standard)
2                                  102                                  yy.mm.dd (ANSI standard)
3                                  103                                  dd/mm/yy (British/French standard)
4                                  104                                  dd.mm.yy (German standard)
5                                  105                                  dd-mm-yy (Italian standard)
6                                  106                                  dd mon yy
7                                  107                                  Mon dd, yy
8                                  108                                  hh:mi:ss
9                                  109                                  mon dd yyyy hh:mi:ss:mmmAM/PM
10                                  110                                  mm-dd-yy (USA standard)
11                                  111                                  yy/mm/dd (Japan standard)
12                                  112                                  yymmdd (ISO standard)
13                                  113                                  dd mon yyyy hh:mi:ss:mmm (Europe standard - 24 hour clock)
14                                  114                                  hh:mi:ss:mmm (24 hour clock)
20                                  120                                  yyyy-mm-dd hh:mi:ss (ODBC canonical - 24 hour clock)
21                                  121                                  yyyy-mm-dd hh:mi:ss:mmm (ODBC canonical - 24 hour clock)
                                   126                                  yyyy-mm-ddThh:mi:ss:mmm (ISO8601 standard)
                                   127                                  yyyy-mm-ddThh:mi:ss:mmmZ (ISO8601 standard)
                                   130                                  dd mon yyyy hh:mi:ss:mmmAM/PM (Hijri standard)
                                 131                                  dd/mm/yy hh:mi:ss:mmmAM/PM (Hijri standard)

//Example
SELECT TRY_CONVERT(varchar, '2018-09-13', 101);
//Output: '09/13/2018'
TRY_CAST 
This function tries to convert an expression from one datatype to another datatype. If the conversion fails, the function will return NULL. Otherwise, it will return the converted value.
Syntax
TRY_CAST( expression AS type [ (length) ] )
expression
The value to convert to another datatype.
type
The datatype that you wish to convert expression to. It can be one of the following: bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, or image.
length
Optional. The length of the resulting data type for char, varchar, nchar, nvarchar, binary and varbinary.
Note : When casting from a float or numeric to an integer, the TRY_CAST function will truncate the result. For other conversions, the TRY_CAST function will round the result.
//Example
SELECT TRY_CAST(14.85 AS int);
Result: 14
SELECT TRY_CAST('14 Main St.' AS float);
Result: NULL
(result is NULL because conversion failed since this string value can not be converted to a float)
Date and Time Data Types and Functions

Data type	Format	Range	Storage size (bytes)	User-defined fractional 2nd  precision	Time zone offset
time	hh:mm:ss[.nnnnnnn]	00:00:00.0000000 through 23:59:59.9999999	3 to 5	Yes	No
date	YYYY-MM-DD	0001-01-01 through 9999-12-31	3	No	No
smalldatetime	YYYY-MM-DD hh:mm:ss	1900-01-01 through 2079-06-06	4	No	No
datetime	YYYY-MM-DD hh:mm:ss[.nnn]	1753-01-01 through 9999-12-31	8	No	No
datetime2	YYYY-MM-DD hh:mm:ss[.nnnnnnn]	0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999	6 to 8	Yes	No
datetimeoffset	YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm	0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC)	8 to 10	Yes	Yes

Example:

SELECT CAST('2007-05-08 12:35:29. 1234567 +12:15' AS time(7)) AS 'time',CAST('2007-05-08 12:35:29. 1234567 +12:15' AS date) AS 'date' ,CAST('2007-05-08 12:35:29.123' AS smalldatetime) AS 'smalldatetime',CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime' ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(7)) AS   'datetime2',CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS 'datetimeoffset'; 

//Output:

Higher-Precision System Date and Time Functions

SQL Server 2017 derives the date and time values through use of the GetSystemTimeAsFileTime() Windows API. The accuracy depends on the computer hardware and version of Windows on which the instance of SQL Server running. This API has a precision fixed at 100 nanoseconds. Use the GetSystemTimeAdjustment() Windows API to determine the accuracy.

Function	Syntax	Return value	Return data type
SYSDATETIME	SYSDATETIME ()	Returns a datetime2(7) value containing the date and time of the computer on which the instance of SQL Server runs. The returned value does not include the time zone offset.	datetime2(7)
SYSDATETIMEOFFSET	SYSDATETIMEOFFSET ( )	Returns a datetimeoffset(7) value containing the date and time of the computer on which the instance of SQL Server runs. The returned value includes the time zone offset.	datetimeoffset(7)
SYSUTCDATETIME	SYSUTCDATETIME ( )	Returns a datetime2(7) value containing the date and time of the computer on which the instance of SQL Server is running. The function returns the date and time values as UTC time (Coordinated Universal Time).	datetime2(7)

Lower-Precision System Date and Time Functions

Function	Syntax	Return value	Return data type
CURRENT_TIMESTAMP	CURRENT_TIMESTAMP	Returns a datetime value containing the date and time of the computer on which the instance of SQL Server runs. The returned value does not include the time zone offset.	datetime
GETDATE	GETDATE ( )	Returns a datetime value containing the date and time of the computer on which the instance of SQL Server runs. The returned value does not include the time zone offset.	datetime
GETUTCDATE	GETUTCDATE ( )	Returns a datetime value containing the date and time of the computer on which the instance of SQL Server runs. The function returns the date and time values as UTC time (Coordinated Universal Time).	datetime

 SELECT SYSDATETIME(), SYSDATETIMEOFFSET(), SYSUTCDATETIME(), CURRENT_TIMESTAMP, GETDATE(), GETUTCDATE();  

/* Returned:  

SYSDATETIME()      2007-04-30 13:10:02.0474381  

SYSDATETIMEOFFSET()2007-04-30 13:10:02.0474381 -07:00  

SYSUTCDATETIME()   2007-04-30 20:10:02.0474381  

CURRENT_TIMESTAMP  2007-04-30 13:10:02.047  

GETDATE()          2007-04-30 13:10:02.047  

GETUTCDATE()       2007-04-30 20:10:02.047  

*/

DATENAME

This function returns a character string representing the specified datepart of the specified date.

Syntax

DATENAME ( datepart , date )

//Example

SELECT DATENAME(year, '12:10:30.123'),DATENAME(month, '12:10:30.123'),DATENAME(day, '12:10:30.123'),DATENAME(dayofyear, '12:10:30.123'),DATENAME(weekday, '12:10:30.123'); 

DATEPART (Transact-SQL)

This function returns an integer representing the specified datepart of the specified date.

Syntax

DATEPART ( datepart , date ) 

Example:

SELECT DATEPART(week, '2007-04-21 '), DATEPART(weekday, '2007-04-21 ')

//Output :

30     1

datepart	datepart	datepart	datepart	datepart
year	quarter	month	dayofyear	nanosecond
day	week	weekday	hour	TZoffset
minute	second	millisecond	microsecond

DAY

This function returns an integer that represents the day (day of the month) of the specified date.

//Syntax

DAY(date) 

//Example

SELECT DAY('2015-04-30 01:01:01.1234567');

//Output

30

MONTH

This function Returns an integer that represents the month of the specified date.

//Syntax

MONTH(date) 

//Example

SELECT MONTH('2015-04-30 01:01:01.1234567');

//Output

04

YEAR

Returns an integer that represents the year of the specified date.

//Syntax

YEAR(date) 

//Example

SELECT YEAR('2015-04-30 01:01:01.1234567');

//Output

2015

Function	Syntax
DATEFROMPARTS	DATEFROMPARTS ( year, month, day )
Example: SELECT DATEFROMPARTS ( 2010, 12, 31 ) AS Result; Result 2010-12-31
DATETIME2FROMPARTS	DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision)
Example: SELECT DATETIME2FROMPARTS ( 2010, 12, 31, 23, 59, 59, 0, 0 ) AS Result;   Result: 2010-12-31 23:59:59.0000000
DATETIMEFROMPARTS	DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds)
Example: SELECT DATETIMEFROMPARTS ( 2010, 12, 31, 23, 59, 59, 0 ) AS Result;   Result: 2010-12-31 23:59:59.000
DATETIMEOFFSETFROMPARTS	DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)
Example: SELECT DATETIMEOFFSETFROMPARTS ( 2010, 12, 31, 14, 23, 23, 0, 12, 0, 7 ) AS Result;   Result : 2010-12-31 14:23:23.0000000 +12:00
SMALLDATETIMEFROMPARTS	SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )
Example: SELECT SMALLDATETIMEFROMPARTS ( 2010, 12, 31, 23, 59 ) AS Result Result: 2010-12-31 23:59:00
TIMEFROMPARTS	TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )
Example: SELECT TIMEFROMPARTS ( 23, 59, 59, 0, 0 ) AS Result; Result: 23:59:59.000000

DATEDIFF

This function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate

Syntax

DATEDIFF ( datepart , startdate , enddate )  

Note : DATEDIFF will not accept datepart values from user-defined variables or as quoted strings.

//Example:

SELECT DATEDIFF(year,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(quarter,     '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(month,       '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(dayofyear,   '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(day,         '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(week,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(hour,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(minute,      '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(second,      '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(millisecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(microsecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

//Result:

1(for all).

Note: DATEDIFF_BIG 

Syntax:

DATEDIFF_BIG ( datepart , startdate , enddate )

Example:

Returns the number of date or time datepart boundaries, crossed between two specified dates.

DATEADD

This function adds a specified number value (as a signed integer) to a specified datepart of an input date value, and then returns that modified value.

Syntax:

DATEADD (datepart , number , date )

//Example

DECLARE @datetime2 datetime2 = '2007-01-01 13:10:10.1111111';  

SELECT 'year', DATEADD(year,1,@datetime2)  

UNION ALL  

SELECT 'quarter',DATEADD(quarter,1,@datetime2)  

UNION ALL  

SELECT 'month',DATEADD(month,1,@datetime2)  

UNION ALL  

SELECT 'dayofyear',DATEADD(dayofyear,1,@datetime2)  

UNION ALL  

SELECT 'day',DATEADD(day,1,@datetime2)  

UNION ALL  

SELECT 'week',DATEADD(week,1,@datetime2)  

UNION ALL  

SELECT 'weekday',DATEADD(weekday,1,@datetime2)  

UNION ALL  

SELECT 'hour',DATEADD(hour,1,@datetime2)  

UNION ALL  

SELECT 'minute',DATEADD(minute,1,@datetime2)  

UNION ALL  

SELECT 'second',DATEADD(second,1,@datetime2)  

UNION ALL  

SELECT 'millisecond',DATEADD(millisecond,1,@datetime2)  

UNION ALL  

SELECT 'microsecond',DATEADD(microsecond,1,@datetime2)  

UNION ALL  

SELECT 'nanosecond',DATEADD(nanosecond,1,@datetime2); 

//Output

Year         2008-01-01 13:10:10.1111111  

quarter      2007-04-01 13:10:10.1111111  

month        2007-02-01 13:10:10.1111111  

dayofyear    2007-01-02 13:10:10.1111111  

day          2007-01-02 13:10:10.1111111  

week         2007-01-08 13:10:10.1111111  

weekday      2007-01-02 13:10:10.1111111  

hour         2007-01-01 14:10:10.1111111  

minute       2007-01-01 13:11:10.1111111  

second       2007-01-01 13:10:11.1111111  

millisecond  2007-01-01 13:10:10.1121111  

microsecond  2007-01-01 13:10:10.1111121  

nanosecond   2007-01-01 13:10:10.1111111   

ISDATE 

Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.

ISDATE returns 0 if the expression is a datetime2 value.

Syntax

ISDATE(expression)

//Example

IF ISDATE('2009-05-12 10:19:41.177') = 1  

    PRINT 'VALID'  

ELSE  

    PRINT 'INVALID';  

Logical Function

SQL has two logical functions CHOOSE and IIF.

CHOOSE function returns an item from a list of values, based on its position in the list. This position is specified by the index.

In the syntax, the index parameter specifies the item and is a whole number, or integer. The val_1 … val_n parameter identifies the list of values.

SELECT CHOOSE(2, 'Human Resources', 'Sales', 'Admin', 'Marketing' ) AS Result;

//Output:

Sales

IIF function returns one of two values, based on a particular condition. If the condition is true, it will return true value. Otherwise it will return a false value.

In the syntax, the boolean_expression parameter specifies the Boolean expression. The true_value parameter specifies the value that should be returned if the boolean_expression evaluates to true and the false_value parameter specifies the value that should be returned if the boolean_expression evaluates to false.

SELECT BusinessEntityID, SalesYTD, IIF(SalesYTD > 200000, 'Bonus', 'No Bonus') AS 'Bonus?' FROM Sales.SalesPerson

GO

//Example :

BusinessEntityID    SalesYTD         Bonus?

274                            559697.5639 Bonus

275                            3763178.1787 Bonus

285                            172524.4512 No Bonus

Mathematical Function

For a better understanding, let's see all the preceding functions with easy Example to executes.

Abs(): The Abs() function returns the absolute value. The Abs() function doesn't work for the bit data type.

Syntax

ABS ( numeric_expression )

Example to execute to execute

SELECT ABS(-25) AS A1, ABS(25) AS A2 ,ABS(-25.50) A3 ,ABS(25.25) AS A4

ACOS(): A mathematical function that returns the angle, in radians, whose cosine is the specified float expression, also called arccosine.

Syntax

ACOS ( float_expression )

The return type of the function is float.

Float_expression 

Is an expression of the type float or of a type that can be implicitly converted to float, with a value from -1 through 1. Values outside this range return NULL and report a domain error.

Example to execute

SELECT ACOS(-0.5) A1 ,ACOS(0.5) A2 , ACOS(1) A3 

ASIN(): ASIN() function returns the angle, in radians, whose sine is the specified float expression. This is also called arcsine. The return type is Float.

Syntax

ASIN ( float_expression )

float_expression 

Is an expression of the type float or of a type that can be implicitly converted to float, with a value from -1 through 1. Values outside this range return NULL and report a domain error.

Example to execute

SELECT ASIN(0.5) A1 , ASIN(0.8) A2 ,ASIN(1) A3 

ATAN(): The ATAN() function returns the angle in radians whose tangent is a specified float expression. This is also called arctangent. The return type of the function is float.

Syntax

ATAN ( float_expression )

float_expression: Is an expression of the type float or of a type that can be implicitly converted to float.

Example to execute

SELECT ATAN(45) A1, ATAN(55) A2, ATAN(-25) A3 

ATN2(): the ATN2() function returns the angle, in radians, between the positive x-axis and the ray from the origin to the point (y, x), where x and y are the values of the two specified float expressions. The return type of the function is float.

Syntax

ATN2 ( float_expression , float_expression )

Float_expression: Is an expression of the float data type.

Example to execute

SELECT ATN2(45,45) A1 , ATN2(35,148) A2 ,ATN2(88,345) A3

CEILING(): The CEILING() function returns the smallest integer greater than, or equal to, the specified numeric expression. The return type of the function is float.

Syntax

CEILING ( numeric_expression )

numeric_expression 

Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. 

Example to execute

SELECT CEILING(124.45) A1, CEILING(-124.15) A2, CEILING(124) A3

COS(): The COS() function returns the trigonometric cosine of the specified angle, in radians, in the specified expression. In other words, it returns the cosine of an angle that is provided in radians. The return type of the function is float.

Syntax

COS ( float_expression )

Float_expression 

Example to execute

SELECT COS(1.046) A1 , COS(0) A2 , COS(0.785) A3  

Now we take the same Example to execute for an angle of 60, 0 and 45 degrees.

SELECT COS(60*3.14/180) A1 , COS(0) A2 , COS(45*3.14/180) A3  

COT(): The COT() function returns the trigonometric cotangent of the specified angle, in radians, in the specified float expression. In other words it returns the cotangent of an angle in radians. The return type of the function is float.

Syntax

COT ( float_expression )

Float_expression 

Is an expression of type float or of a type that can be implicitly converted to float.

Example to execute

SELECT COT(124) A1 ,COT(-45) A2 ,COT(78.45) A3 

DEGREES(): The DEGREES() function returns the corresponding angle in degrees for an angle specified in radians. The return type of DEGREES() is the same type as numeric_expression.

Syntax

DEGREES ( numeric_expression )

numeric_expression 

Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Example to execute

SELECT DEGREES(PI()) A1, DEGREES(PI()/2) A2,DEGREES(PI()/4) A3

EXP(): The EXP() function returns the exponential value of the specified float expression. The return type of the function is float. The EXP() function uses the constant e (2.718281…) as the base.

Syntax

EXP ( float_expression )

Example to execute

SELECT EXP(2) A1,EXP(3) A2,EXP(10) A3

FLOOR(): The FLOOR() function returns the largest integer less than or equal to the specified numeric expression. The FLOOR() function returns the same type as numeric_expression.

Syntax

FLOOR ( numeric_expression )

numeric_expression 

Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Example to execute

SELECT FLOOR(-124.14) A1, FLOOR(124) A2 ,FLOOR(124.15) A3

LOG(): The LOG() function returns the natural logarithm of the specified float expression in SQL Server. the return type of the function is float.

Syntax

LOG ( float_expression [, base ] )

By default the value of the base is "e", in other words 22/7.

float_expression : Is an expression of type float or of a type that can be implicitly converted to float.

base: Optional integer argument that sets the base for the logarithm.

Example to execute

SELECT LOG(12) A1, LOG(25) A2, LOG(100) A3

LOG10( ): the LOG10 function returns the base-10 logarithm of the specified float expression. The return type of the function is float.

Syntax

LOG10 ( float_expression )

float_expression 

Is an expression of type float or of a type that can be implicitly converted to float.

Example to execute

SELECT LOG10(1000) A1, LOG10(10000) A2, LOG10(10050) A3

PI(): The PI function returns the constant value of PI. The return type is a float that is a constant value.

Syntax

PI ( )

Example to execute

SELECT PI() A1 ,PI()*2 A2 , PI()*3 A3

POWER(): The POWER function returns the value of the specified expression to the specified power. The return type is float.

Syntax

POWER ( float_expression , y )

float_expression: Is an expression of type float or of a type that can be implicitly converted to float.

y: Is the power to which to raise float_expression. y can be an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Example to execute

SELECT POWER(2,3) A1, POWER(5,5) A2, POWER(4,4) A3

RADIANS(): The RADIANS function returns radians when a numeric expression, in degrees, is entered. The return type of the function is float.

Syntax

RADIANS ( numeric_expression )

numeric_expression 

Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Example to execute

SELECT RADIANS(180) Approximate, RADIANS(180.0) Exact , RADIANS(60) Approximate, RADIANS(60.0) Exact

RAND(): The RAND function returns a pseudo-random float value from 0 through 1, exclusive. The return type of the Rand function is float.

Syntax

RAND ( [ seed ] )

Example to execute

SELECT RAND() A1,RAND() A2, RAND() A3  

ROUND(): The ROUND function returns a numeric value, rounded to the specified length or precision. The return type of the function is the same as the numeric expression.

Syntax

ROUND ( numeric_expression , length [ ,function ] )

numeric_expression: Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

length: The number of decimal places rounded to. This value must be a positive or negative integer. If this parameter is omitted, the ROUND function will round the number to 0 decimal places. If it is positive then the decimal part of the number is rounded and if it is negative then the number is rounded on the left side of the decimal point.

function (Optional): The operation can be either 0 or any other numeric value. When it is 0 (or this parameter is omitted), the ROUND function will round the result to the number of decimal_places. If operation is any value other than 0, the ROUND function will truncate the result to the number of decimal_places.

Example to execute

SELECT ROUND(225.715, 2) Round_ UNION all /* result is rounded because 3rd parameter is omitted */  

SELECT ROUND(225.715, 2, 0) Round_ UNION ALL /* result is rounded because 3rd parameter is 0 */  

SELECT ROUND(225.715, 2, 1) Round_ UNION ALL /* result is truncated because 3rd parameter is non-zero */  

SELECT ROUND(225.715, 1) Round_ UNION ALL /* result is rounded because 3rd parameter is omitted */  

SELECT ROUND(225.715, 0) Round_ UNION ALL /* result is rounded because 3rd parameter is omitted */  

SELECT ROUND(225.715, -1) Round_ UNION ALL /* result is rounded because 3rd parameter is omitted */  

SELECT ROUND(225.715, -2) Round_ UNION ALL /* result is rounded because 3rd parameter is omitted */  

SELECT ROUND(567.655,-2) Round_ UNION ALL /* result is rounded because 3rd parameter is omitted */   

SELECT ROUND(512.566,0) Round_ UNION ALL /* result is rounded because 3rd parameter is omitted */  

SELECT ROUND(512.566,-1 ,0) Round_ UNION ALL /* result is truncated because 3rd parameter is non-zero */  

SELECT ROUND(519.566,-1 ,2) Round_ UNION ALL /* result is truncated because 3rd parameter is non-zero */  

SELECT ROUND(519.566,-4 ,2) Round_ /* result will be zero because value of Round Place is greater than total length of number */

SIGN(): The SIGN function returns the positive (+1), zero (0), or negative (-1) sign of the specified expression. The return type of the function is the same as the numeric expression.

Syntax: 

SIGN ( numeric_expression )

numeric_expression 

Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Example to execute

SELECT SIGN(-32.12) A1, SIGN(123.12) A2, SIGN(0.0) A3 

SIN( ): The SIN function returns the trigonometric sine of the specified angle, in radians and in an approximate numeric, float, expression. The return type of the function is float.

Syntax

SIN ( float_expression )

float_expression: Is an expression of type float or of a type that can be implicitly converted to float.

Example to execute

SELECT SIN(2.5) A1, SIN(-1.5) A2, SIN(3.0) A3

SQRT(): The SQRT function returns the square root of the specified float value. The return type of the function is float.

Syntax

SQRT ( float_expression )

float_expression: Is an expression of type float or of a type that can be implicitly converted to float.

Example to execute

SELECT SQRT(9) A1,SQRT(25) A2,SQRT(45) A3

SQUARE(): The SQUARE function returns the square of the specified float value. The return type of the function is float.

Syntax

SQUARE ( float_expression )

float_expression 

Is an expression of type float or of a type that can be implicitly converted to float.

Example to execute

SELECT SQUARE(4) A1,SQUARE(6.3) A2, SQUARE(-9) A3

TAN(): The TAN function returns the tangent of the input expression. The return type of the function is float.

Syntax

TAN ( float_expression )

float_expression: Is an expression of type float or of a type that can be implicitly converted to float, interpreted as the number of radians.

Example to execute

SELECT TAN(124) A1,TAN(3.12) A2, TAN(-12.12) A3