Categories

# SQL Functions – JournalDev

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

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:

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:

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.