# SQL Functions

Filed Under: SQL

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

IdLibraryBookTitleBookQuantityAuthorBookPrice
1The Chamber of Secrets10J K Rowling20.99
2One night at the call center13Chetan Bhagat100.99
3The God of Small things11Arundhati Roy120.99
4War and Peace5Leo Tolstoy80.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.

close
Generic selectors
Exact matches only
Search in title
Search in content