SQL Functions

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.

1. SQL Count – returns the count of rows in a database table.
2. SQL Max – returns the maximum value from a database table
3. SQL Min – returns the minimum value from a database table
4. SQL Avg – provides the average of a certain table column value
5. SQL Sum – provides the sun of a certain table column value
6. SQL sqrt – returns the square root of a number.
7. SQL rand – used to generate a random number using SQL command.
8. SQL concat – used for concatenating strings in a SQL command.
9. SQL Ucase – converts a field to upper case.
10. 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

SQL Count

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

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

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

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

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

Select sqrt(BookQuantity) from library

Output:

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:

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:

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:

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:

SQL lcase example

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