SQL Functions

SQL (Structured Query Language) provides a rich set of built-in functions that allow you to perform operations on data within your database. These functions can manipulate strings, perform mathematical calculations, work with dates and times, and much more.

Categories of SQL Functions

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

1. 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.
SUM(): Returns the total sum of a numeric column.
AVG(): Returns the average value of a numeric column.
MIN(): Returns the minimum value in a column.
MAX(): Returns the maximum value in a column.

2. Scalar Functions

Scalar functions operate on a single value and return a single value. They can be used in almost any part of a SQL statement where an expression is allowed.

String Functions

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

Numeric Functions

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

Date and Time Functions

GETDATE() / NOW(): Returns the current date and time.
DATEPART() / EXTRACT(): Returns a 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.

3. Window Functions

Window functions perform calculations across a set of table rows that are related to the current row. Unlike aggregate functions, window functions do not collapse rows into a single output row. The rows are referred to as the "window frame".

ROW_NUMBER(): Assigns a unique sequential integer to each row.
RANK(): Assigns a rank to each row within its partition.
DENSE_RANK(): Assigns a rank to each row without gaps.
LAG(): Accesses data from a previous row.
LEAD(): Accesses data from a subsequent row.
SUM() OVER (...), AVG() OVER (...), etc.: Aggregate functions used as window functions.

Using SQL Functions

Functions are typically used in the SELECT clause, WHERE clause, ORDER BY clause, and even within other functions.

Example: Using String and Aggregate Functions

Let's say we have a table named Products with columns ProductName and Price. We want to find the average price and the number of products whose names start with 'A'.


SELECT
    COUNT(*) AS NumberOfProducts,
    AVG(Price) AS AveragePrice,
    SUM(CASE WHEN UPPER(ProductName) LIKE 'A%' THEN 1 ELSE 0 END) AS ProductsStartingWithA
FROM
    Products;

Example: Using Date Functions

Retrieve orders placed in the last 30 days.


SELECT
    OrderID,
    OrderDate,
    CustomerID
FROM
    Orders
WHERE
    OrderDate >= DATEADD(day, -30, GETDATE());

Custom Functions

Most database systems also allow you to create your own user-defined functions (UDFs) to encapsulate complex logic or reusable computations.

Understanding and utilizing SQL functions is crucial for effective data manipulation, analysis, and reporting. Explore the specific functions available in your database system (e.g., SQL Server, PostgreSQL, MySQL) for a comprehensive list.