SQL

SQL Functions

🧮 SQL Functions are built-in routines provided by a Database Management System (DBMS) to perform specific operations on data. Functions can manipulate values, perform calculations, format text, work with dates and times, and generate summarized results. Using SQL functions makes queries more concise, readable, and efficient.

📖 What are SQL Functions?

An SQL function accepts one or more input values (called arguments), processes them, and returns a single result. Functions can be used in SELECT, WHERE, ORDER BY, GROUP BY, HAVING, and many other SQL statements.

Information

SQL functions are built into most relational database systems. While many functions are standardized, some databases provide additional vendor-specific functions and syntax.

🗂️ Types of SQL Functions

SQL functions are generally classified into two main categories:

🔹 Single-Row (Scalar) Functions
🔹 Aggregate Functions

🔹 Single-Row (Scalar) Functions

Single-row functions operate on one value at a time and return one result for each row.

🔤 String Functions

String functions manipulate and format text values.

Function	Description	Example
UPPER()	Converts text to uppercase.	UPPER('sql') → SQL
LOWER()	Converts text to lowercase.	LOWER('SQL') → sql
LENGTH()	Returns the number of characters in a string.	LENGTH('Database') → 8
TRIM()	Removes leading and trailing spaces.	TRIM(' SQL ')
CONCAT()	Combines multiple strings.	CONCAT('John',' Doe')

Using String Functions

SELECT
    UPPER(Name) AS UpperName,
    LOWER(Name) AS LowerName,
    LENGTH(Name) AS NameLength
FROM Students;

🔢 Numeric Functions

Numeric functions perform mathematical calculations.

Function	Description
ABS()	Returns the absolute value.
ROUND()	Rounds a number to a specified number of decimal places.
CEILING()	Rounds a number up to the nearest integer.
FLOOR()	Rounds a number down to the nearest integer.
MOD()	Returns the remainder after division.

Using Numeric Functions

SELECT
    Price,
    ROUND(Price, 2) AS RoundedPrice,
    ABS(-25) AS AbsoluteValue
FROM Products;

📅 Date and Time Functions

Date and time functions retrieve or manipulate date and time values.

Function	Description
CURRENT_DATE	Returns the current date.
CURRENT_TIME	Returns the current time.
CURRENT_TIMESTAMP	Returns the current date and time.
EXTRACT()	Extracts a specific part of a date.

Using Date Functions

SELECT
    CURRENT_DATE,
    CURRENT_TIMESTAMP;

📊 Aggregate Functions

Aggregate functions operate on multiple rows and return a single summarized result. They are frequently used with the GROUP BY clause.

Function	Description
COUNT()	Counts the number of rows.
SUM()	Calculates the total of numeric values.
AVG()	Calculates the average value.
MIN()	Returns the smallest value.
MAX()	Returns the largest value.

Aggregate Function Example

SELECT
    COUNT(*) AS TotalEmployees,
    AVG(Salary) AS AverageSalary,
    MIN(Salary) AS MinimumSalary,
    MAX(Salary) AS MaximumSalary
FROM Employees;

📌 Using Aggregate Functions with GROUP BY

The GROUP BY clause groups rows with the same values, allowing aggregate functions to calculate results for each group.

GROUP BY Example

SELECT Department,
       COUNT(*) AS EmployeeCount,
       AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department;

🎯 Using Aggregate Functions with HAVING

The HAVING clause filters grouped results after aggregation.

HAVING Example

SELECT Department,
       COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 5;

📊 Comparison: Scalar vs Aggregate Functions

Feature	Scalar Functions	Aggregate Functions
Works On	One row at a time	Multiple rows
Returns	One result per row	One summarized result
Examples	UPPER(), ROUND()	COUNT(), SUM()
Common Usage	Formatting and calculations	Reporting and analysis

💼 Real-World Example

Sales Report

SELECT Category,
       COUNT(*) AS ProductCount,
       SUM(Price) AS TotalValue,
       AVG(Price) AS AveragePrice
FROM Products
GROUP BY Category
ORDER BY TotalValue DESC;

This query groups products by category, counts the number of products, calculates the total value of products in each category, computes the average price, and sorts the results from highest to lowest total value.

⚠️ Best Practices

Best Practice

Use scalar functions only when necessary, avoid applying functions to indexed columns in filtering conditions unless required, combine aggregate functions with GROUP BY for meaningful summaries, and use descriptive aliases to improve the readability of query results.

🚀 Key Points to Remember

📌 SQL functions simplify calculations and data manipulation.
📌 Scalar functions process one row at a time.
📌 Aggregate functions summarize data across multiple rows.
📌 Functions can be used in many SQL clauses, including SELECT, WHERE, and HAVING.
📌 Some functions vary slightly between database systems.

>>"SQL functions transform raw data into meaningful information with just a few lines of code."

Summary

✅ SQL functions are powerful built-in tools that perform calculations, manipulate text, process dates, and summarize data. By mastering both scalar and aggregate functions, you can write efficient queries, generate insightful reports, and solve complex database problems with ease.