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

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 example

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

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

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

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

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

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

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

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

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

SQL lcase example

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

Leave a Reply

Your email address will not be published. Required fields are marked *

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