In day to day usage of SQL, there is a time when we need to manipulate data based on the type of data. For example, in a table which comprises prices we need to get the average or sum of all the prices. In these cases, SQL functions come very handily as they provide a built-in mechanism for such functions.
SQL Functions
SQL functions are the set of built-in functions to perform a calculation over data that are stored in the table. Let us have a look at the list of most useful SQL functions.
- SQL Count – returns the count of rows in a database table.
- SQL Max – returns the maximum value from a database table
- SQL Min – returns the minimum value from a database table
- SQL Avg – provides the average of a certain table column value
- SQL Sum – provides the sun of a certain table column value
- SQL sqrt – returns the square root of a number.
- SQL rand – used to generate a random number using SQL command.
- SQL concat – used for concatenating strings in a SQL command.
- SQL Ucase – converts a field to upper case.
- SQL Lcase – converts a field to lower case.
Now let’s try to understand all the above-mentioned SQL functions one by one in more detail.
We will consider the following table to understand the functions in a better way.
Library
IdLibrary | BookTitle | BookQuantity | Author | BookPrice |
---|---|---|---|---|
1 | The Chamber of Secrets | 10 | J K Rowling | 20.99 |
2 | One night at the call center | 13 | Chetan Bhagat | 100.99 |
3 | The God of Small things | 11 | Arundhati Roy | 120.99 |
4 | War and Peace | 5 | Leo Tolstoy | 80.00 |
SQL Count
Select count(*) FROM library
Output:
Count(*) |
---|
4 |

SQL Count
In the query above, count(*) returns the total count of the number of rows available in the table.
SQL Max
Select max(BookQuantity) from library
Output:
max(BookQuantity) |
---|
13 |

SQL Max example
In the query above, max(BookQuantity) returns the max value from the column BookQuantity of Library table.
SQL Min
Select min(BookQuantity) from library
Output:
min(BookQuantity) |
---|
5 |

SQL Min example
In the query above, min(BookQuantity) returns the min value from the column BookQuantity of Library table.
SQL Avg
Select avg(BookQuantity) from library
Output:
avg(BookQuantity) |
---|
9.7500 |

SQL Avg example
In the query above, avg(BookQuantity) returns the average value from the column BookQuantity of Library table.
SQL Sum
Select sum(BookQuantity) from library
Output:
sum(BookQuantity) |
---|
39 |

SQL Sum example
In the query above, sum(BookQuantity) returns the sum of all the values from the column BookQuantity of Library table.
SQL Sqrt
Select sqrt(BookQuantity) from library
Output:
sqrt(BookQuantity) |
---|
3.1622 |
3.6055 |
3.3166 |
2.2306 |

SQL square root example
In the query above, sqrt(BookQuantity) returns the square root for all the values from the column BookQuantity of Library table.
SQL Rand
Select rand(BookQuantity) from library
Output:
rand(BookQuantity) |
---|
0.6570 |
0.4076 |
0.9072 |
0.4061 |

SQL rand example
In the query above, rand(BookQuantity) returns the random number for values corresponding to the rows for column BookQuantity of Library table.
SQL Concat
Select concat(BookTitle,'-',Author)from library
Output:
concat(BookTitle,’-‘,Author) |
---|
The Chamber of Secrets-J K Rowling |
One night at the call center-Chetan Bhagat |
The God of Small things-Arundhati Roy |
War and Peace-Leo Tolstoy |

SQL concat example
In the query above, concat(BookTitle,’-‘,Author) returns the concatenated values corresponding to the rows for column BookTitle and Author of Library table.
SQL Ucase
Select ucase(Author)from library
Output:
ucase(Author) |
---|
J K ROWLING |
CHETAN BHAGAT |
ARUNDHATI ROY |
LEO TOLSTOY |

SQL ucase example
In the query above, ucase(Author) returns the values in upper case for Author column of Library table.
SQL Lcase
Select lcase(Author)from library
Output:
lcase(Author) |
---|
j k rowling |
chetan bhagat |
arundhati roy |
leo tolstoy |

SQL lcase example
In the query above, lcase(Author) returns the values in lower case for Author column of Library table.