+91 9717203377

Home » Web » useful sql function

useful sql function

SQL stands for Structured Query Language and it allows to access and manipulate databases.
SQL has many built-in functions which process on string or numeric data of database table. We can define built-in functions in two categories

  1. SQL Aggregate Functions
    These functions return a single value after calculation from column. These functions are
    COUNT(), AVG(), MAX(), MIN(), SUM()
  2. SQL Scalar functions
    SQL scalar functions return a single value, based on the input value. These functions are
    UCASE(), LCASE(), MID(), LEN(), ROUND(), NOW(), FORMAT()

COUNT()
COUNT function is the simplest function of SQL and it counts the number of records in the SELECT statement. Here is an example to show this

SELECT COUNT(*) FROM employee;
SELECT COUNT(*) FROM employee WHERE salary>10000;

In this example first query returns total number of records in employee table and second query returns total number of records from employee table whose salary is above 10000.

AVG()
This sql function is used for selects the average value for certain table column. Here is an example

SELECT AVG(salary) FROM employee;

This query will return a single row which is average of salary for all the records.

MAX() and MIN()
MAX function returns the record of a column which is maximum value among the record set and MIN function returns the record with minimum value among the record set. Here is example

SELECT MAX(salary) FROM employee;
SELECT MIN(salary) FROM employee;

SUM()
This function is used for sum of all the records of a field according to query from database table. To perform this function data type of column should be numeric. Here is an example.

SELECT SUM(salary) FROM employee;

This query will return sum of all employee’s salary

UCASE()
This function returns string value in uppercase from the database table.

SELECT UCASE( emp_name ) AS fname FROM  `employees`

LCASE()
This function converts the value of a record field to lowercase.

SELECT LCASE( emp_name ) AS fname FROM  `employees`

MID()
This function is used to extract characters from a string of field from database table.

SELECT MID(emp_name,1,2) AS fname FROM `employees`

This function will display first two characters from the emp_name.

ROUND()
This function rounds a number specified as an argument up to a number specified as another argument.

 NOW()
This function returns the current date and time. e.g. SELECT NOW()