Post
Ask Question

SQL Functions and Examples.

Introduction

SQL functions in databases are routines that can accept parameters, perform actions, and return a result. They allow for modularization of code, reuse of logic, and simplify complex operations. Here are some common types of SQL functions:

Types of SQL Functions

  1. Aggregate Functions: Operate on a set of values and return a single value, such as SUM, AVG, MIN, MAX, COUNT, etc.
    Example

    SELECT SUM(salary) AS TotalSalary FROM employees;

SELECT Avg(salary) AS AvgSalary FROM employees;

SELECT MIN(salary) AS MinSalary FROM employees;

SELECT MAX(salary) AS MaxSalary FROM employees;

SELECT Count(salary) AS CountSalary FROM employees;
  2. Scalar Functions: Take one or more arguments and return a single value, such as UPPER, LOWER, CONCAT, SUBSTRING, LEN, etc.
    Example 
    SELECT UPPER(first_name) AS CapitalizedName FROM employees;

SELECT LOWER(first_name) AS LowerName FROM employees;


SELECT LEN(first_name) AS Length FROM employees;
  3. Date Functions: Perform operations on date or timestamp values, such as CURRENT_DATE, DATEADD, DATEDIFF, MONTH, YEAR, etc.
    Example 
    SELECT DATEADD(MONTH, 3, hire_date) AS ProjectedReviewDate FROM employees;
  4. Mathematical Functions: Perform mathematical operations, such as ROUND, ABS, POWER, SQRT, CEILING, FLOOR, etc.
    Example 
    SELECT ROUND(salary, 2) AS RoundedSalary FROM employees;
  5. User-Defined Functions (UDFs): Custom functions created by users to perform specific tasks. In some databases like MySQL and PostgreSQL, users can define their own functions.
    Example 
    CREATE FUNCTION GetEmployeeAge(dateOfBirth DATE) RETURNS INT
BEGIN
    DECLARE age INT;
    SET age = YEAR(CURRENT_DATE()) - YEAR(dateOfBirth);
    RETURN age;
END;

Examples of SQL Functions

  • Using COUNT: Count the number of employees in a department.

    Using COUNT: Count the number of employees in a department.

  • Using CONCAT: Concatenate first name and last name of employees.

    SELECT CONCAT(first_name, ' ', last_name) AS FullName FROM employees;
  • Using YEAR and MONTH: Extract the year and month from a date. 
    SELECT YEAR(hire_date) AS HireYear, MONTH(hire_date) AS HireMonth FROM employees;
  • Using ABS: Get the absolute value of a number. 
    SELECT ABS(salary) AS AbsoluteSalary FROM employees;
  • Using User-Defined Function (UDF): Call a user-defined function to get employee age. 
    SELECT GetEmployeeAge(date_of_birth) AS Age FROM employees;

QL functions provide powerful capabilities for data manipulation, computation, and retrieval within databases. The choice of function depends on the specific requirements of the query and the type of operation you need to perform on the data.


Similar Articles