SQL (Structured Query Language) offers a wide range of advanced functions that allow you to perform complex calculations, transformations, and aggregations on your data.
Aggregate Functions
In database management an aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning.
- SUM(): Calculates the sum of values in a column.
- AVG(): Computes the average of values in a column.
- COUNT(): Returns the number of rows or non-null values in a column.
- MIN(): Finds the minimum value in a column.
- MAX(): Retrieves the maximum value in a column.
Conditional Functions
- CASE WHEN: Allows conditional logic to be applied in the SELECT statement.
- COALESCE(): Returns the first non-null value in a list.
- NULLIF(): Compares two expressions and returns null if they are equal; otherwise, returns the first expression.
Mathematical Functions
Mathematical functions are present in SQL which can be used to perform mathematical calculations. Some commonly used mathematical functions are given below:
- ABS(): Returns the absolute value of a number.
- ROUND(): Rounds a number to a specified number of decimal places.
- POWER(): Raises a number to a specified power.
- SQRT(): Calculates the square root of a number.
Advanced Functions in SQL
BIN(): It converts a decimal number to a binary number.
Query:
SELECT BIN(18);
Output:
BINARY(): It converts a value to a binary string.
Query:
SELECT BINARY "GeeksforGeeks";
Output:
COALESCE(): It returns the first non-null expression in a list.
Query:
SELECT COALESCE(NULL,NULL,'GeeksforGeeks',NULL,'Geeks');
Output:
CONNECTION_ID(): It returns the unique connection ID for the current connection.
Query:
SELECT CONNECTION_ID();
Output:
CURRENT_USER(): It returns the user name and hostname for the MySQL account used by the server to authenticate the current client.
Query:
SELECT CURRENT_USER();
Output:
DATABASE(): It returns the name of the default database.
Query:
SELECT DATABASE();
Output:
IF(): It returns one value if a condition is TRUE, or another value if a condition is FALSE.
Query:
SELECT IF(200<500, "YES", "NO");
Output:
LAST_INSERT_ID(): It returns the first AUTO_INCREMENT value that was set by the most recent INSERT or UPDATE statement.
Query:
SELECT LAST_INSERT_ID();
Output:
Query:
SELECT NULLIF(25.11, 25);
Output:
Query:
SELECT NULLIF(115, 115);
Output:
SESSION_USER(): It returns the user name and host name for the current MySQL user.
Query:
SELECT SESSION_USER();
Output:
SYSTEM_USER(): It returns the user name and host name for the current MySQL user.
Query:
SELECT SYSTEM_USER();
Output:
USER(): It returns the user name and host name for the current MySQL user.
Query:
SELECT USER();
Output:
VERSION(): It returns the version of the MySQL database.
Query:
SELECT VERSION();
Output:
Similar Reads
SQL Aggregate functions SQL Aggregate Functions are used to perform calculations on a set of rows and return a single value. These functions are particularly useful when we need to summarize, analyze, or group large datasets in SQL databases. Whether you are working with sales data, employee records or product inventories,
4 min read
SQL | Date Functions (Set-2) SQL Date Functions are powerful tools that allow users to manipulate, extract , and format date and time values within SQL databases. These functions simplify handling temporal data, making them indispensable for tasks like calculating intervals, extracting year or month values, and formatting dates
5 min read
SQL | Date Functions (Set-1) SQL Date Functions are essential for managing and manipulating date and time values in SQL databases. They provide tools to perform operations such as calculating date differences, retrieving current dates and times and formatting dates. From tracking sales trends to calculating project deadlines, w
5 min read
PLSQL | BITAND Function The BITAND is an inbuilt function in PLSQL which is used to returns an integer value which is calculated with AND operation of two given input decimal number. Internally these input decimal numbers get converted into binary numbers and then AND operation is performed and results are returned as outp
2 min read
SQL MIN() and MAX() Functions The SQL MIN() and MAX() functions are essential aggregate functions in SQL used for data analysis. They allow you to extract the minimum and maximum values from a specified column, respectively, making them invaluable when working with numerical, string, or date-based data. In this article, we will
4 min read