SQL Functions

on


SQL Functions( Introduction):

 Functions are used to solve/perform particular task and also functions must return a value.
All database systems having two types of functions.

1. Pre-Defined functions
2.User-Defined functions.

1. Pre-Defined functions: Pre-defined functions are categorized into 4 types.

  • Number Functions
  • Character Functions
  • Date Functions
  • Group Functions (or) Aggregate functions.
Number functions:  Number functions are operate over number data and performs specific task.

  • ABS()  - Return the +ve value(converts -ve to +ve)
  • MOD() - Returns remainder value.
  • GREATEST - Return maximum value among passed value.
  • ROUND() - Rounds the given numeric value based on the specified decimal value.
  • TRUNK()  - It truncate the given numeric value based on the specified decimal value.
  • CEIL() - Returns nearest greater number based on the decimal value.
  • FLOOR() - Returns nearest lowest value based on the decimal value.
Character Functions: Character functions are used to perform the certain operations on Character values.
  • UPPER () - Converts a field to upper case.
  • LOWER() - Converts a field to lower case.
  • INITCAP() - Returns Initial letter as Capital and remaining small. 
  • LENGTH() - Returns length of specified field.
  • SUBSTR() - Returns portion of a string based on specified value.
  • LPAD() - It fills Left side spaces of letter/string with specified character.
  • RPAD() - It fills Right side spaces of letter/string with specified character.
  • TRIM() - It removes specified character in a specified string.
Date Functions: Date functions are used to perform the operations on specified date.
  • SYSDATE - Returns the current Date.
  • ADD_MONTHS() - It Returns the specified date based on the passed parameter.
  • LAST_DAY() - It reruns the last day of the month within the specified date.
  • NEXT_DAY() - It Returns next occurrence day from the specified date.
  • MONTHS_BETWEEN - Returns number of months between specified two dates.
Group Functions (or) Aggregate Functions: 

SQL aggregate functions returns a single value based on the given input value.
  • MIN() - Returns the smallest value
  • MAX() - Returns the largest value
  • AVG() - Returns the Average value
  • SUM() - Returns the sum of the specified values.
  • COUNT(*) - Returns the number of rows.
  • COUNT(Column name) - Returns the number rows of a column.
Note: All the above functions will be explained in detail in my next chapters. 

If you have any doubts, Please drop a comment below.


                                           Thank You




Comments

Post a Comment