Best SQL Functions With Examples

What are sql functions?

A SQL function is a predefined, reusable piece of code that performs a specific operation in sql (Structured Query Language). Functions in SQL are used to manipulate data, perform calculations, and return a single value or a table result.

SQL functions can be categorized into two main types:
Aggregate functions:
Scalar functions:

Scalar Functions:
Definition: Scalar functions operate on a single value and return a single result.
Examples:
LEN() – To returns the length of a string.
LOWER() – To convert a string to lowercase.
UPPER() – To convert a string to uppercase.
CONCAT() – To concatenate two or more strings.
Aggregate Functions:
Aggregate functions perform a calculation on a set of values and return a single value summarizing the entire set.
Examples:
SUM() – To calculate the sum of numeric values.
AVG() – To calculate the average of numeric values.
COUNT() – To count the number of rows in a set.
MAX() – To return the maximum value in a set.
MIN() – To return the minimum value in a set.

Usage of SQL Functions:
Data Manipulation:
Functions are used to manipulate data stored in the database tables, such as converting data types, changing cases, and performing string manipulations.

Data Aggregation:
Aggregate functions are used to summarize and analyze data, providing insights into the overall characteristics of a dataset.

Date and Time Operations:
SQL functions assist in working with date and time values, such as extracting parts of a date, performing date arithmetic, and formatting dates.

Mathematical Operations:
Mathematical functions are used for performing calculations on numeric values, such as addition, subtraction, multiplication, and division.

Custom Functions:
Some database systems allow users to create their own custom functions to perform specific operations tailored to their needs.

Example of a SQL Functions:
Scalar function example
SELECT UPPER(‘hello’) AS UppercaseString;

Aggregate function example
SELECT AVG(salary) AS AverageSalary FROM employees;

UPPER() is a scalar function that converts a string to uppercase.
AVG() is an aggregate function that calculates the average salary from the employee table.

How do I use sql functions?

Using SQL functions involves defining functions and then invoking or applying them in queries.

Below are the general steps for creating and using functions in SQL:

Create a function:
Scalar Function Example:
Creating a simple scalar function
CREATE FUNCTION dbo.AddTwoNumbers(@num1 INT, @num2 INT)
RETURNS INT
AS
BEGIN
RETURN @num1 + @num2;
END;
Table-Valued Function Example:

Creating a table-valued function
CREATE FUNCTION dbo.GetEmployeesByDepartment(@dept_id INT)
RETURNS TABLE
AS
RETURN
(
SELECT * FROM employees WHERE department_id = @dept_id
);

Call the function:
Using Scalar Functions:
Calling the scalar function
DECLARE @result INT;
SET @result = dbo.AddTwoNumbers(5, 7);
SELECT @result AS SumResult;

Using Table-Valued Function:
Calling the table-valued function
SELECT * FROM dbo.GetEmployeesByDepartment(3);

Integrate Functions in Queries:
Functions can be used within queries to perform calculations, filter data, or manipulate results.

Example with a Scalar Function:
Using a scalar function in a SELECT statement
SELECT employee_id, salary, dbo.AddTwoNumbers(salary, 1000) AS NewSalary
FROM employees;

Example with a Table-Valued Function:
Using a table-valued function in a SELECT statement
SELECT * FROM dbo.GetEmployees(2);

Important Points:
Ensure that you have the necessary permissions to create functions in the database.
Pay attention to the data types of input parameters and return values.

Types of sql functions with examples?

SQL functions are categorized into different types based on their purpose and behavior. The main types of functions in SQL include:

Scalar Functions:
It is operating on a single value and returning a single value. And it’s used to manipulate data values and perform calculations on individual columns.

Examples:
UPPER(str): To convert a string to uppercase.
LOWER(str): To convert a string to lowercase.
ROUND(num, decimals): to round a numeric value to the specified number of decimals.
LENGTH(str): To return the length of a string.

Aggregate sql Functions:
Aggregate sql functions perform calculations on a set of values and return a single value summarizing the entire set. They are frequently used in conjunction with the GROUP BY clause.

Examples:
COUNT(column): To count the number of rows in a result set.
SUM(column): To calculate the sum of numeric values in a column.
AVG(column): To calculate the average value of numeric values in a column.
MIN(column): To return the minimum value in a set of values.
MAX(column): To return the maximum value in a set of values.

Date and Time Functions:
Date and time functions are used for working with date and time values. They provide operations such as extracting parts of a date, performing date arithmetic, and formatting dates.

Examples:
CURRENT_DATE(): Returns the current date.
CURRENT_TIME(): Returns the current time.
DATEDIFF(date1, date2): Returns the difference between two dates.

String Functions:
String sql functions are used to manipulate and analyze text data. They perform operations on character strings, such as concatenation, case conversion, and substring extraction.

Examples:
CONCAT(str1, str2, …): Concatenates two or more strings.
LEFT(str, length): Returns the left part of a string with the specified length.
SUBSTRING(str, start, length): Extracts a substring from a string.

Window Functions:
Window sql functions perform calculations across a specified range of rows related to the current row.
Examples:
ROW_NUMBER(): To assign a unique number to each row within a partition.
RANK(): To assign a rank to each row within a partition based on the specified column’s values.
LEAD(column): To return the value of the next row within the partition.
LAG(column): To return the value of the previous row within the partition.

Understanding these types of functions is essential for writing effective SQL queries and performing various operations on the database.

What are SQL general functions with examples?

The SQL General function offers a wide range of general-purpose functions that can be applied to many database queries.

SQL general functions with examples:

String Functions:
CONCAT: To concatenate values for two or more strings.
SELECT CONCAT(first_name, ‘ ‘, last_name) AS full_name FROM emp;

SUBSTRING: It’s extracting a substring from a string.
SELECT SUBSTRING(product_name, 1, 10) AS short_name FROM product;

LENGTH/LEN: It’s returns the length of a string.
SELECT product_name, LENGTH(product_name) AS name_length FROM products;

UPPER/LOWER: Converts a string to uppercase or lowercase.
SELECT UPPER(category) AS uppercase_category FROM products;

Numeric Functions:
ROUND: Rounds a numeric value which specified number of decimal.
SELECT ROUND(price, 2) AS rounded_price FROM products;

ABS: Returns the absolute value of a number.
SELECT ABS(-50) AS absolute_value;

RAND/RANDOM: Generates a random number.
SELECT RAND() AS random_number;

Date and Time Functions:
NOW/CURRENT_TIMESTAMP: It’s returning the current date and time.
SELECT NOW() AS current_datetime;

DATE_FORMAT: Formats a date or timestamp.
SELECT DATE_FORMAT(order_date, ‘%Y-%m-%d’) AS formatted_date FROM orders;

DATEDIFF: Calculates the number of days between two dates.
SELECT DATEDIFF(end_date, start_date) AS days_between FROM events;

Mathematical Functions:
SQRT: It calculates the square root of a number.
SELECT SQRT(25) AS square_root;

POWER: It’s a number to a specified power.
SELECT POWER(2, 3) AS result; — 2^3 = 8

MOD: Calculates the remainder of a division operation.
SELECT MOD(17, 5) AS remainder; — 17 % 5 = 2

Conditional Functions:
IFNULL/COALESCE: Return a specified value if a column is NULL.
SELECT COALESCE(discounted_price, regular_price) AS final_price FROM products;

SQL CASE: conditional logic within a query.
SELECT product_name,
CASE
WHEN price < 500 THEN ‘Low’
WHEN price < 1000 THEN ‘Moderate’
ELSE ‘High’
END AS price_category
FROM products;

Basic SQL Functions:
Basic sql functions that are commonly used for data manipulation, retrieval, and analysis.

Scalar Functions:
CONCAT(str1, str2, …):

Concatenates two or more strings.
SELECT CONCAT(first_name, ‘ ‘, last_name) AS full_name FROM emp;

UPPER(str):
It will convert a string to uppercase.
SELECT UPPER(product_name) AS capitalized_name FROM products;

LOWER(str):
It converts a string to lowercase.
SELECT LOWER(city) AS lowercase_city FROM customers;

LENGTH(str):
It will returns the length of a string.
SELECT LENGTH(email) AS email_length FROM users;

Mathematical Functions:
ROUND(num, decimals):
Rounds a numeric value to the specified number of decimals.
SELECT ROUND(price, 2) AS rounded_price FROM products;

ABS(num):
Returns the absolute value of a numeric expression.
SELECT ABS(balance) AS absolute_balance FROM accounts;

SQRT(num):
Returns the square root of a numeric expression.
SELECT SQRT(area) AS square_root_area FROM geometry;

Aggregate Functions:
COUNT(column):
It will counts the number of rows in a result set.
SELECT COUNT(*) AS total_records FROM orders;

SUM(column):
It calculates the sum of numeric values in a column.
SELECT SUM(quantity) AS total_quantity FROM order_details;

AVG(column):
It calculates the average value of numeric values in a column.
SELECT AVG(price) AS average_price FROM products;

MIN(column):
It will return the minimum value in a set of values.
SELECT MIN(salary) AS lowest_salary FROM employees;

MAX(column):
It’s a function that returns the maximum value in a set of values.

SELECT MAX(age) AS oldest_age FROM customers;

Main SQL Functions:
The main SQL functions that are commonly used for data manipulation, retrieval, and analysis are:

String Functions:
CONCAT(str1, str2, …):

Concatenates two or more strings.
SELECT CONCAT(first_name, ‘ ‘, last_name) AS full_name FROM employees;

UPPER(str):
Converts a string to uppercase.
SELECT UPPER(product_name) AS capitalized_name FROM products;

LOWER(str):
Converts a string to lowercase.
SELECT LOWER(city) AS lowercase_city FROM customers;

LENGTH(str):
Returns the length of a string.
SELECT LENGTH(email) AS email_length FROM users;

Numeric Functions:
ROUND(num, decimals):
Round a numeric value to the specified number of decimals.
SELECT ROUND(price, 2) AS rounded_price FROM products;

ABS(num):
Returns the absolute value of a numeric expression.
SELECT ABS(balance) AS absolute_balance FROM accounts;

SQRT(num):
Returns the square root of a numeric expression.
SELECT SQRT(area) AS square_root_area FROM geometry;

Date and Time Functions:
CURRENT_DATE():
Returns the current date.
SELECT CURRENT_DATE() AS current_date;

CURRENT_TIME():
Returns the current time.
SELECT CURRENT_TIME() AS current_time;

DATEDIFF(date1, date2):
Returns the difference between two dates.
SELECT DATEDIFF(end_date, start_date) AS date_difference FROM tasks;

Aggregate Functions:
COUNT(column):
Counts the number of rows in a result set.
SELECT COUNT(*) AS total_records FROM orders;

SUM(column):
Calculates the sum of numeric values in a column.
SELECT SUM(quantity) AS total_quantity FROM order_details;

AVG(column):
Calculates the average value of numeric values in a column.
SELECT AVG(price) AS average_price FROM products;

MIN(column):
Returns the minimum value in a set of values.
SELECT MIN(salary) AS lowest_salary FROM employees;

MAX(column):
Returns the maximum value in a set of values.
SELECT MAX(age) AS oldest_age FROM customers;

These main SQL functions play a crucial role in querying and manipulating data within a relational database.

Common SQL Functions:
Common SQL functions that are frequently used for data manipulation and analysis:

String Functions:
CONCAT(str1, str2, …):

Concatenates two or more strings.
SELECT CONCAT(first_name, ‘ ‘, last_name) AS full_name FROM employees;

UPPER(str):
It converts a string to uppercase.
SELECT UPPER(product_name) AS capitalized_name FROM products;