Site Loader
Get a Quote

Functions SQL Server

Functions SQL Server :- There are many built-in function that are used to perform calculation on data.

Different types of functions in SQL are:

SQL Aggregate Function:

Aggregate function are used to perform operation on group of values and returns single value.Below is the list of some built-in Aggregate Function.

Useful aggregate functions:

 

  • AVG() – This will Returns the average value.
  • COUNT() – This will Returns the number of rows.
  • MAX() – This will Returns the largest value.
  • MIN() – This will Returns the smallest value.
  • SUM() – This will Returns the sum.

 

SQL Scalar Functions in SQL server:

Scalar function are used to perform operation on single value and returns single value.Below is the list of some built-in Scalar Functions.

 

  • UPPER() This will Converts a field to upper case.
  • LOWER()   This will Converts a field to lower case.
  • SUBSTRING() – This will Extract characters from a text field.
  • LEN() – This will Returns the length of a text field.
  • ROUND() – This will Rounds a numeric field to the number of decimals specified.
  • GETDATE() –  This will Returns the current system date.

 

Let’s take a look at the above functions in details.Before starting i will create a table with the name Employee below is the script for that.

 CREATE TABLE [dbo].[Employee]
 (
 ID BIGINT IDENTITY,
 EmployeeName NVARCHAR(100),
 City NVARCHAR(100),
 Salary FLOAT
 )

Let’s have some values into Employee table.

INSERT INTO [dbo].[Employee] (EmployeeName,City,Salary) VALUES ('Anurag Gupta','New Delhi',10000)
 INSERT INTO [dbo].[Employee] (EmployeeName,City,Salary) VALUES ('Vivek Nagar','Allahabad',30000)
 INSERT INTO [dbo].[Employee] (EmployeeName,City,Salary) VALUES ('Abhishek Pandey','Lucknow',24000)
 INSERT INTO [dbo].[Employee] (EmployeeName,City,Salary) VALUES ('Nitin Diwedi','Lucknow',78000)
 INSERT INTO [dbo].[Employee] (EmployeeName,City,Salary) VALUES ('Ritesh Singh','Lucknow',78000)

 

 

Now the result set of Employee table will be:Functions SQL Server

Now it’s time to get in touch with the Functions we have discussed.

AVG() – Syntax for AVG() function is as

SELECT AVG(column_name) FROM table_name

Let’s have some example using AVG()  function.Run the following query

SELECT AVG(Salary) AS 'Average Salary' FROM [dbo].[Employee]

You will get the following result after executing above query

 

Functions SQL Server

COUNT() – Syntax for COUNT() function is as

SELECT COUNT(column_name) FROM table_name

Run the following query

SELECT COUNT(*) AS ‘Total Records’ FROM [dbo].[Employee]

You will get the following result after executing above query.

 

Functions SQL Server

 

MAX()-  Syntax for MAX() function is as

SELECT MAX(column_name) FROM table_name:

Run the following query

SELECT MAX(Salary) AS ‘Max Salary’ FROM [dbo].[Employee]

You will get the following result after executing above query.

 

Functions SQL Server

MIN()-  Syntax for MIN() function is as

SELECT MIN(column_name) FROM table_name:

Run the following query

SELECT MIN(Salary) AS 'Max Salary' FROM [dbo].[Employee]

You will get the following result after executing above query.

Functions SQL Server

SUM()-  Syntax for SUM() function in sql sever is as

SELECT SUM(column_name) FROM table_name:

Run the following query

SELECT SUM(Salary) AS 'Max Salary' FROM [dbo].[Employee]

You will get the following result after executing above query.

 

Functions SQL Server

UPPER()-  Syntax for UPPER() function in sql sever is as

SELECT UPPER(column_name) FROM table_name:

Run the following query

SELECT UPPER(EmployeeName) FROM [dbo].[Employee];

Functions SQL Server

 

LOWER()-  Syntax for LOWER() function is as

SELECT LOWER(column_name) FROM table_name:

Run the following query

SELECT LOWER(EmployeeName) FROM [dbo].[Employee];

Functions SQL Server

SUBSTRING()-  Syntax for SUBSTRING() function is as

SELECT SUBSTRING(column_name,start,length) AS some_name FROM table_name;

Run the following query

SELECT SUBSTRING(City,1,4) FROM [dbo].[Employee];

SUBSTRING

LEN()-  Syntax for LEN() function is as

SELECT LEN(column_name,start,length) AS some_name FROM table_name;

Run the following query

SELECT LEN(EmployeeName) FROM [dbo].[Employee]

Functions SQL Server

GETDATE()- 

Run the following query
SELECT GETDATE() 'Current Date'
GETDATE

Session Ends Functions SQL Server

Post Author: TechieFlair

Leave a Reply

Your email address will not be published.