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
|
1 2 |
Select count(*) FROM library |
Output:
SQL Count
In the query above, count(*) returns the total count of the number of rows available in the table.
SQL Max
|
1 2 |
Select max(BookQuantity) from library |
Output:
SQL Max example
In the query above, max(BookQuantity) returns the max value from the column BookQuantity of Library table.
SQL Min
|
1 2 |
Select min(BookQuantity) from library |
Output:
SQL Min example
In the query above, min(BookQuantity) returns the min value from the column BookQuantity of Library table.
SQL Avg
|
1 2 |
Select avg(BookQuantity) from library |
Output:
SQL Avg example
In the query above, avg(BookQuantity) returns the average value from the column BookQuantity of Library table.
SQL Sum
|
1 2 |
Select sum(BookQuantity) from library |
Output:
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
|
1 2 |
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
|
1 2 |
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
|
1 2 |
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
|
1 2 |
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
|
1 2 |
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.