Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • SQL Inbuilt Functions

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 695
    Comment on it


    Hi Readers,


    In this blog, we will discuss the inbuilt functions of SQL which are used to perform calculations on data. All inbuilt functions are provided to make the task easier to do and we can easily complete a complex task with the help of these functions. There are two types of functions which we can use in SQL to perform calculations which are as follows:-


    1. SQL Aggregate Functions
    2. SQL Scalar Functions

     

     SQL Aggregate Functions:-


    Now one by one we will discuss both types of functions first of all SQL aggregate functions which returns a single value from multiple values are as follows:-

        •    AVG()
        •    COUNT()
        •    FIRST()
        •    LAST()
        •    MAX()
        •    MIN()
        •    SUM()

    We will start with the first function of aggregate functions that is AVG() :-


    AVG() Function:-
    The AVG() function returns the average value of the column includes all integer values. As in mathematics also if we need to calculate average speed so we need distance and time in integer value. Syntax of the function will be used in this way as given below:-


    SQL AVG() Syntax
    SELECT AVG(column_name) FROM table_name

     

    For Example:-

    SELECT AVG(Price) AS PriceAverage FROM ProductTable;

    This will calculate the average of price from Price column in table ProductTable and return the average.

     


    COUNT() Function:-
    This  function is used to returns the number of rows that matches a specified condition.

    SQL COUNT() Syntax
    SELECT COUNT(column_name) FROM table_name;

    According to syntax, this function will return the number of values (NULL values will not be counted) of the specified column in the syntax.

    For Example:-
    SELECT COUNT(*) FROM table_name;

    According to example, this function returns the number of records in a table if used in the above way.

     

     

    FIRST() Function:-
    This function is used to returns the first value of the selected column from the table.

    SQL FIRST() Syntax
    SELECT FIRST(column_name) FROM table_name;

    Note: The FIRST() function is only supported in MS Access so for SQL SERVER we will use TOP instead of FIRST().

    SQL Server Syntax
    SELECT TOP 1 column_name FROM table_name ORDER BY column_name ASC;
    Note:ASC is for ascending order.
     

    For Example:-
    SELECT TOP 1 Employee_Name FROM Employee ORDER BY Employee_Id ASC;

     

     

    LAST() Function:-
    This function returns the last value of the selected column from the table.

    SQL LAST() Syntax
    SELECT LAST(column_name) FROM table_name;

    Note: The LAST() function is only supported in MS Access.

    SQL Server Syntax
    SELECT TOP 1 column_name FROM table_name ORDER BY column_name DESC;
    Note:DESC is for descending order.

    For Example:-
    SELECT TOP 1 Employee_Name FROM Employee ORDER BY Employee_Id DESC;

     


    MAX() Function

    This function returns the largest value of the selected column from the table.
     

    SQL MAX() Syntax
    SELECT MAX(column_name) FROM table_name;

    For Example:-
    SELECT MAX(Price) AS HighestPrice FROM ProductTable;

     


    MIN() Function:-
    This function returns the smallest value of the selected column from the table.
     

    SQL MIN() Syntax
    SELECT MIN(column_name) FROM table_name;

    For Example:-
    SELECT MIN(Price) AS SmallestOrderPrice FROM ProductTable;

     


    SUM() Function:-

    This  function returns the total sum of a numeric column from the table.


    SQL SUM() Syntax
    SELECT SUM(column_name) FROM table_name;

    For Example:-
    SELECT SUM(Price) AS TotalSum FROM ProductTable;

     


    SQL Scalar Functions:-

    This is the second type of the inbuilt functions of SQL. This includes the following functions in it :-


        •    UCASE()
        •    LCASE()
        •    LEN()
        •    ROUND()
        •    NOW()
        •    FORMAT()

     

    Now one by one we will discuss the above-mentioned functions:-


    UCASE() Function:-
    This function converts the value of a column to uppercase from the table.


    SQL UCASE() Syntax
    SELECT UCASE(column_name) FROM table_name;
    Note:-We will use UPPER instead of UCASE in SQL.

    Syntax for SQL Server
    SELECT UPPER(column_name) FROM table_name;

    For Example:-
    SELECT UPPER(Employee_Name) AS EmployeeNameUpperCase FROM Employee;

     


    LCASE() Function:-
    This function converts the value of a column to lowercase from the table.


    SQL LCASE() Syntax
    SELECT LCASE(column_name) FROM table_name;

    Syntax for SQL Server
    SELECT LOWER(column_name) FROM table_name;

    For Example:-
    SELECT LCASE(Employee_Name) AS EmployeeNameLowerCase FROM Employee;

     

     

    LEN() Function:-


    This function returns the length of the value from the given column.


    SQL LEN() Syntax
    SELECT LEN(column_name) FROM table_name;

    For Example:-
    SELECT LEN(Employee_Address) as LengthOfAddress FROM Employee;

     


    ROUND() Function:-
    This function is used to round a numeric field to the number of decimals specified in the syntax.


    SQL ROUND() Syntax
    SELECT ROUND(column_name,decimals) FROM table_name;

    For Example:-
    SELECT ROUND(Price,0) AS RoundedPrice FROM ProductTable;

     


    The NOW() Function:-
    This function returns the current system date and time.


    SQL NOW() Syntax
    SELECT NOW() FROM table_name;


    For Example:-
    SELECT  Now() AS PerDate FROM ProductTable;

     


    FORMAT() Function
    This function is used to format how a field is to be displayed and format is provided with the help of FORMAT() function.


    SQL FORMAT() Syntax
    SELECT FORMAT(column_name,format) FROM table_name;

    For Example:-
    SELECT  FORMAT(Now(),'YYYY-MM-DD') AS PerDate FROM ProductTable;


    In this way we can easily use the inbuilt functions of SQL Server and complete complex task in very less time.

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: