In SQL, functions are a set of predefined or user-defined operations that we can use to perform specific tasks on data within a database.
Like many programming languages, SQL functions can be broadly categorized into two types:
These functions are provided by the database management system and are readily available for use. Examples include SUM, CONCAT, and GETDATE. The code below uses the built-in SUM function to calculate the total sales from a table, sales_table, already set up in the database.
SELECT SUM(amount) AS total_sales FROM sales_table;
The built-in functions can then further be categorized into two types:
Aggregate functions: These functions operate on sets of values and return a single value that summarizes the data. They are used to perform calculations across multiple rows of a table. These include: AVG ,COUNT, FIRST, and LAST. The code below uses the AVG function to calculate the average of the sales number from the same table we used earlier, sales_table.
SELECT AVG(amount) AS avg_sales FROM sales_table;
Scalar functions: These functions operate on a single value and return a single value. They are applied to each row individually in a query. They don’t perform aggregation or summarization of data. Some examples of the scalar functions include UCASE, LCASE, and MID. The code below uses the scalar function LENGTH on the sales_table.
SELECT sale_id, LENGTH(sale_id) AS id_length FROM sales_table;
These functions are created by users to perform custom operations that are not available in built-in functions. These functions can be written in SQL, PL/SQL (for Oracle databases), T-SQL (for Microsoft SQL Server), or other procedural languages supported by the DBMS. Users can define parameters, specify logic, and define the return type of the function. The code below defines a function that calculate the highest sales from the sales_table we’ve used in all other codes.
USE sales_database;DELIMITER //CREATE FUNCTION calculate_highest_sales()RETURNS DECIMAL(10,2)BEGINDECLARE highest_sale DECIMAL(10,2);SELECT MAX(amount) INTO highest_sale FROM sales_table;RETURN highest_sale;END //DELIMITER ;SELECT calculate_highest_sales() AS highest_sales;
In the code above:
Lines 5–6: We declare the calculate_highest_sales function and specify that the function will return a decimal number with up to 10 digits in total, including 2 digits after the decimal point.
Lines 7 and 11: We define the body of the function. BEGIN and END mark the start and end of the function body.
Line 8: We declare a variable, highest_sale, to hold the highest sale amount.
Line 9: We use the built-in function, MAX, to find the maximum value of the amount column in sales_table and store it in the highest_sale variable.
Line 10: We return the value stored in highest_sale as the output of the function.
User-defined functions can also be categorized into the same two types as the built-in functions.
In conclusion, SQL functions are powerful tools that enable us to perform specific tasks and operations on data within a database efficiently. By understanding and utilizing both built-in and user-defined functions, we can enhance our data manipulation and analysis capabilities, making our SQL queries more powerful and efficient.
Free Resources