Function In SQL

Function In SQL

For doing operations on data SQL has many built-in Function In SQL, they are categorized in two categories and further sub-categorized in different seven functions under each category.

  1. Functions are pre define implementation provided by SQL.
  2. There are different categories of function
    1. Aggregate Function
    2. String Function
    3. Date Function

 

Aggregate Function

In this type values are group together and the single value as an output

  1. Count()

  2. mysql> select count(id) from employee;

    +———–+

    | count(id) |

    +———–+

    |         7 |

    +———–+

    1 row in set (0.01 sec)

     

    mysql> select count(id) from employee where isActive = 1;

    +———–+

    | count(id) |

    +———–+

    |         5 |

    +———–+

    1 row in set (0.01 sec)

     

    mysql> select count(id) as totalEmp from employee where isActive = 1;

    +———-+

    | totalEmp |

    +———-+

    |        5 |

    +———-+

    1 row in set (0.00 sec)

  3. Sum()

    mysql> select sum(salary) from employee;

    +————–+

    | sum(salary)  |

    +————–+

    | 14488915.111 |

    +————–+

    1 row in set (0.00 sec)

     

    mysql> select sum(salary) as totalSalary from employee;

    +————–+

    | totalSalary  |

    +————–+

    | 14488915.111 |

    +————–+

    1 row in set (0.00 sec)

     

    mysql> select sum(salary) as totalSalary from employee where isActive=0;

    +————-+

    | totalSalary |

    +————-+

    | 4783975.746 |

    +————-+

    1 row in set (0.00 sec)

  4. Avg()

    mysql> select avg(salary) as averageSalary from employee where isActive=1;

    +——————–+

    | averageSalary      |

    +——————–+

    | 1940987.8730000001 |

    +——————–+

    1 row in set (0.00 sec)

     

    mysql> select avg(salary) as averageSalary from employee;

    +——————–+

    | averageSalary      |

    +——————–+

    | 2069845.0158571429 |

    +——————–+

    1 row in set (0.00 sec)

  5. Min()

    mysql> select min(salary) as minimumSalary from employee;

    +—————+

    | minimumSalary |

    +—————+

    |   1236987.873 |

    +—————+

    1 row in set (0.01 sec)

     

    mysql> select min(salary) as minimumSalary from employee where isActive=1;

    +—————+

    | minimumSalary |

    +—————+

    |   1236987.873 |

    +—————+

    1 row in set (0.00 sec)

  6. Max()

    mysql> select max(salary) as maximumSalary from employee where isActive=1;

    mysql> select max(salary) as maximumSalary from employee;

  • String function

    1. Upper()

    Is use to convert string value in to Capital case

    mysql> select id, upper(name) as name, contact from employee;

    1. Lower()

    Is use to convert string into small case.

    mysql> select id, lower(name) as name, contact from employee;

    1. Concat()

    Is use to combine the multiple column into a single column

    select id, concat(name,’-‘ ,contact) from employee;

    1. length()

    Is use to get the total count of character in the string

    mysql> select id, contact, length(contact) from employee;

    1. trim()

    Is use to remove the spaces from both the side (left and right) of the string. It will not remove the space which is in between the word.

    select id,trim(name) from employee;

    1. ltrim() and rtrim()

    is use to remove the spaces from left side (ltrim) and right side (rtrim) of the string

    mysql> select id,rtrim(name) from employee;

    mysql> select id,ltrim(name) from employee;

  • Date Function

    1. now()

    To get the system data and time

    mysql> select now();

    +———————+

    | now()               |

    +———————+

    | 2022-04-07 08:02:07 |

    +———————+

    1 row in set (0.01 sec)

     

    1. curdate()

    to get the current system date

    mysql> select curdate();

    +————+

    | curdate()  |

    +————+

    | 2022-04-07 |

    +————+

    1 row in set (0.00 sec)

    1. curtime()

    can get the current system time

    mysql> select curtime();

    +———–+

    | curtime() |

    +———–+

    | 08:02:56  |

    +———–+

    1 row in set (0.00 sec)

     

    1. year()

    Is use to get the year from the given date

    mysql> select id,name,year(doj) from employee;

     

    1. month()

    Is use to get the month from the given date

    mysql> select id,name,month(doj) from employee;

     

    1. monthname()

    Is use to get the month in word from the given date

    mysql> select id,name,monthname(doj) from employee;

     

    1. day()

    is use to print the day in the given date

    mysql> select id,name,day(doj) from employee;

     

    1. datediff()

    to Get the difference in two dates in day.

    mysql> select id,name,doj, datediff(now(), doj) from employee;

     

    1. date_format()

    is use to fomrat the date

    mysql> select id,name,date_format(doj,’%d %M %Y’) from employee;

 

Join Telegram : Click Here

 

All Full Stack Java Study Material

 

Structured Query Language

 

Job’s For Fresher

 

Share This Information To Your Friends and Your College Group’s, To Help Them !