Functions in SQL

SQL functions are built-in, reusable blocks of code that accept input values, manipulate or perform calculations on them, and return a specific output. They make writing queries significantly easier by handling complex formatting, transformations, and math directly inside the database engine.SQL functions are broadly divided into two major execution types: Single-Row (Scalar) Functions and Multi-Row (Aggregate/Window) Functions

Multi-Row (Aggregate) Functions
These functions look at a set of values across multiple rows and condense them down into a single, summarized output. They are frequently paired with the GROUP BY clause.

SUM(): Adds all numeric values in a column together.

AVG(): Calculates the average value of a set.

COUNT(): Returns the total number of rows or non-null values.

MAX() / MIN(): Returns the highest or lowest value in a group

SELECT Department, COUNT(EmployeeID), AVG(Salary)
FROM Employees
GROUP BY Department;

Single-Row (Scalar) Functions

These functions operate on an individual value in a single row and return exactly one result per row. They are widely used for data cleaning and formatting.

LOWER() / UPPER(): Converts text to lowercase or uppercase.

CONCAT(): Joins two or more strings together.

SUBSTRING() / SUBSTR(): Extracts a specific section of text.

LENGTH() / LEN(): Returns the total character count of a string.

Mathematical / Numeric Functions

Used to handle arithmetic operations

ROUND(): Rounds a number to a designated decimal place.

ABS(): Returns the absolute (positive) value of a number.

CEILING() / FLOOR(): Rounds a number up or down to the nearest integer.

Date and Time Functions

Used to manage calendar inputs, timelines, and intervals:

NOW() / CURRENT_TIMESTAMP: Pulls the database server's exact current date and time.

DATEDIFF(): Calculates the difference in days/months/years between two dates.

DATE_ADD(): Adds a time interval (e.g., 30 days) to an existing date.

SELECT UPPER(FirstName), ROUND(Bonus, 2), NOW() 
FROM Employees;