sql_functions

SQL Functions

SQL provides a rich set of built-in functions to perform operations on data. These functions allow for calculations, data manipulation, and formatting directly within your SQL queries, enhancing data retrieval and transformation capabilities.

Functions in SQL can be broadly categorized into aggregate functions and scalar functions. Some databases also support advanced functions like window functions.

SQL Aggregate Functions

Aggregate functions perform a calculation on a set of values (e.g., a column) and return a single summary value. They are commonly used with the GROUP BY clause to calculate metrics for different groups of rows.

Commonly used aggregate functions:

• AVG(column) - Returns the average value of a numeric column.
• COUNT(column_or_*) - Returns the number of rows. COUNT(column) counts non-NULL values in the specified column, while COUNT(*) counts all rows.
• MAX(column) - Returns the largest value in a column.
• MIN(column) - Returns the smallest value in a column.
• SUM(column) - Returns the sum of values in a numeric column.

Note: Functions like FIRST() and LAST() are not standard SQL aggregate functions. To get the first or last value within a group, you typically use MIN() or MAX() on an ordering column (like a date or ID) or employ window functions in more complex scenarios.

SQL Scalar Functions (Single-Row Functions)

Scalar functions operate on a single input value (from a single row) and return a single output value per row. They can be used in SELECT lists, WHERE clauses, and ORDER BY clauses.

The availability and syntax of scalar functions can vary significantly between different database systems (e.g., MySQL, PostgreSQL, SQL Server, Oracle). Always consult your RDBMS documentation for specific function names and parameters. Here are some common categories and examples of what you might find (standard functions are preferred):

• String Functions:
• • `UPPER(string)` - Converts a string to upper case. (Standard: `UPPER`)
  • `LOWER(string)` - Converts a string to lower case. (Standard: `LOWER`)
  • `SUBSTRING(string FROM start FOR length)` - Extracts a substring from a string. (Standard: `SUBSTRING`. Variations: `SUBSTR`, `MID`)
  • `LENGTH(string)` - Returns the length of a string. (Standard: `LENGTH` or `CHAR_LENGTH`. Variations: `LEN`)
  • `CONCAT(string1, string2, ...)` - Concatenates two or more strings. (Standard-ish: `CONCAT`. Operator: `||` in many systems)
  • `TRIM([LEADING|TRAILING|BOTH] [character] FROM string)` - Removes leading/trailing/both specified characters (or spaces) from a string. (Standard: `TRIM`)
• Numeric Functions:
• • `ROUND(number, decimals)` - Rounds a numeric field to a specified number of decimals. (Standard: `ROUND`)
  • `ABS(number)` - Returns the absolute value of a number.
  • `CEIL(number)` / `CEILING(number)` - Returns the smallest integer greater than or equal to the number.
  • `FLOOR(number)` - Returns the largest integer less than or equal to the number.
• Date and Time Functions:
• • `CURRENT_TIMESTAMP` - Returns the current system date and time. (Standard. Variations: `NOW()`, `GETDATE()`, `SYSDATE`)
  • `CURRENT_DATE` - Returns the current system date. (Standard)
  • `CURRENT_TIME` - Returns the current system time. (Standard)
  • `EXTRACT(part FROM date)` - Extracts a part (e.g., YEAR, MONTH, DAY) from a date/time value. (Standard)
  • Date formatting functions (e.g., `TO_CHAR` in Oracle/PostgreSQL, `FORMAT` in SQL Server, `DATE_FORMAT` in MySQL) are highly database-specific. Often, date formatting is best handled at the application layer for portability.
• Type Conversion Functions:
• • `CAST(expression AS datatype)` - Converts an expression to a specified data type. (Standard: `CAST`)
  • `CONVERT(...)` - Similar to CAST, syntax varies by RDBMS.

SQL Window Functions (Advanced)

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 become grouped into a single output row; they retain the original rows and add a new column with the result of the function for each row.

Examples include ROW_NUMBER(), RANK(), LAG(), LEAD(), and using aggregate functions like SUM() or AVG() over a window.

Tip: The aggregate functions and common scalar functions will be explained in more detail in subsequent chapters. For specific scalar functions or window functions, refer to your database system’s documentation, as their implementation can vary.