What is the SQL function?

Built-in SQL Functions are small programs provided by the database system that generates a single value output zero, one, or multiple-input. Most of the built-in Functions are standard among popular relational database management systems and defined clearly in the ANSI SQL standard.

The advantages of using SQL Functions:

  • It simplifies the query. Most of the time, a function can replace a long, complex query and make it easy to understand
  • It is more efficient. SQL functions are compiled and cached before use so the performance will be higher.
  • It’s reusable and you can call into a statement anytime.

Here are the lists of the most commonly used built-in functions in SQL.

SQL Aggregate Functions

SQL Aggregate Functions return a single value, calculated from values in a column.

Useful aggregate functions:

  • COUNT() - Returns the number of rows
  • MAX() - Returns the maximum value
  • MIN() - Returns the minimum value
  • SUM() - Returns the sum value
  • AVG() - Returns the average value
  • FIRST() - Returns the first value
  • LAST() - Returns the last value

Example

We have this table Course

CourseID Title Credits DepartmentID
1045 Calculus 4 7
1050 Chemistry 4 1
1061 Physics 4 1
2021 Composition 3 2
2030 Poetry 2 2
2042 Literature 4 2
3141 Tri nometry 4 7
4022 Microeconomics 3 4
4041 Macroeconomics 3 4
4061 Quantitative 2 4

Now we want to count the total number of credits of all available courses from the table. Instead of writing many plus expressions, we use the sum function:

SELECT
	SUM(credits) AS total_credits
FROM
	Course;

And it returns:

total_credits
33

SQL Scalar functions

SQL scalar functions return a single value, based on the input value.

Some useful scalar functions:

  • MID() - Extracts characters from a text field
  • LEN() - Returns the length of a text field
  • ROUND() - Rounds a numeric field to the number of decimals specified
  • NOW() - Returns the current system date and time
  • FORMAT() - Formats how a field is to be displayed
  • SQRT() - Returns the square root of a given number.
  • RAND() - Generates a random number
  • CONCAT() - Concatenates any string inside any SQL command.
  • UCASE() - Converts value of string column to Uppercase characters
  • LCASE() - Converts value of string column to Lowercase characters

Example

To get the rounded value of the square root of 6423490020 in MySQL:

SELECT ROUND(SQRT(6423490020), 0);

And the result is 80147

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL and many other databases simultaneously using an intuitive and powerful graphical interface.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

Need a quick edit on the go? Download for iOS

TablePlus in Dark mode