SQL Functions

SQL functions are built-in operations that perform calculations or manipulations on data. They can be used in various parts of SQL statements, such as the SELECT list, the WHERE clause, and the ORDER BY clause.

Categories of SQL Functions

SQL functions are broadly categorized based on the type of operation they perform:

• Aggregate Functions
• Scalar Functions (including String, Numeric, Date, and Conversion functions)
• Window Functions

Aggregate Functions

Aggregate functions operate on a set of rows and return a single value. They are often used with the GROUP BY clause.

• COUNT(): Returns the number of rows that match a specified criterion.
• SUM(): Returns the total sum of a numeric column.
• AVG(): Returns the average value of a numeric column.
• MIN(): Returns the smallest value in a column.
• MAX(): Returns the largest value in a column.

SELECT COUNT(customer_id), AVG(order_total)
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';
                

Scalar Functions

Scalar functions operate on a single value and return a single value. They can be applied to each row individually.

String Functions

• CONCAT(): Joins two or more strings together.
• LENGTH() or LEN(): Returns the length of a string.
• SUBSTRING() or SUBSTR(): Extracts a part of a string.
• UPPER(): Converts a string to uppercase.
• LOWER(): Converts a string to lowercase.
• REPLACE(): Replaces all occurrences of a substring within a string.

SELECT CONCAT(UPPER(first_name), ' ', UPPER(last_name)) AS full_name
FROM employees
WHERE department = 'Sales';
                

Numeric Functions

• ROUND(): Rounds a number to a specified number of decimal places.
• ABS(): Returns the absolute value of a number.
• CEILING(): Returns the smallest integer greater than or equal to a number.
• FLOOR(): Returns the largest integer less than or equal to a number.
• RAND(): Returns a random floating-point number.

SELECT product_name, ROUND(price, 2) AS rounded_price
FROM products;
                

Date and Time Functions

• GETDATE() or NOW(): Returns the current date and time.
• DATEPART() or EXTRACT(): Returns a specific part of a date (e.g., year, month, day).
• DATEDIFF(): Returns the difference between two dates.
• DATEADD(): Adds a specified time interval to a date.

SELECT order_id, order_date, DATEADD(day, 7, order_date) AS due_date
FROM orders;
                

Conversion Functions

• CAST(): Converts an expression from one data type to another.
• CONVERT(): Similar to CAST() but with more formatting options (SQL Server specific).

SELECT CAST(order_id AS VARCHAR(10)) + '-ORD' AS order_identifier
FROM orders;
                

Window Functions

Window functions perform calculations across a set of table rows that are somehow related to the current row. Unlike aggregate functions, window functions do not cause rows to be grouped into a single output row. They return a value for each row.

• ROW_NUMBER(): Assigns a unique sequential integer to each row within its partition.
• RANK(): Assigns a rank to each row within its partition, with gaps in the sequence.
• DENSE_RANK(): Assigns a rank to each row within its partition, with no gaps in the sequence.
• LAG(): Accesses data from a previous row in the same result set without the use of a subquery.
• LEAD(): Accesses data from a subsequent row in the same result set without the use of a subquery.
• SUM() OVER (...), AVG() OVER (...), etc.: Aggregate functions used as window functions.

SELECT
    product_name,
    price,
    ROW_NUMBER() OVER (ORDER BY price DESC) AS rank_by_price
FROM products;
                

Understanding and utilizing SQL functions is crucial for effective data manipulation and analysis in databases.