SQL | Advanced Functions
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.
Conditional Functions
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 "w3wiki";
Output:
COALESCE(): It returns the first non-null expression in a list.
Query:
SELECT COALESCE(NULL,NULL,'w3wiki',NULL,'Beginner');
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:
Contact Us