SQL Functions

SQL Functions are predefined operations provided by a database management system to perform specific tasks such as mathematical calculations, text manipulation, date processing, data conversion, NULL handling, and data analysis.

Functions make SQL queries shorter, easier to understand, and more useful. Instead of performing every operation manually, we can use built-in functions to process values directly inside SQL queries.

SQL Functions are commonly used in database applications such as banking systems, e-commerce websites, educational portals, hospital management systems, business applications, reporting systems, and data analytics.

What are SQL Functions?

An SQL Function is a predefined operation that accepts one or more input values, performs a specific task, and returns a result.

A function can be used with values stored in table columns as well as constant values.

Simple Example

SELECT UPPER('cse gyan');

Output

CSE GYAN

The UPPER() function converts the supplied text into uppercase characters.

Why are SQL Functions Important?

• Reduce the complexity of SQL queries.
• Perform mathematical calculations easily.
• Manipulate and format text values.
• Process date and time values.
• Handle NULL values.
• Convert data from one type to another.
• Generate useful reports and summaries.
• Support data analysis and business intelligence.
• Reduce repetitive SQL logic.

Types of SQL Functions

SQL Functions can broadly be divided into two important categories:

In addition to these categories, SQL provides functions for numeric operations, strings, dates, type conversion, and NULL handling.

Single-Row Functions

Single-row functions process one value at a time and generally return one result for each input row.

Examples of Single-Row Functions

• UPPER()
• LOWER()
• LENGTH()
• CONCAT()
• TRIM()
• SUBSTRING()
• ABS()
• ROUND()

Numeric Functions

Numeric functions are used to perform mathematical operations on numeric values. They are useful in financial applications, billing systems, statistics, engineering calculations, and data analysis.

ABS() Function

The ABS() function returns the absolute value of a number. The result is non-negative for numeric inputs.

Syntax

ABS(number)

Example

SELECT ABS(-250);

Output

250

Practical Use

ABS() can be useful when calculating the magnitude of a difference, such as the difference between expected and actual values.

ROUND() Function

The ROUND() function rounds a numeric value to a specified number of decimal places.

Syntax

ROUND(number, decimal_places)

Example

SELECT ROUND(125.6789, 2);

Output

125.68

Applications

• Financial calculations.
• Tax calculations.
• Percentage calculations.
• Statistical reports.
• Scientific data processing.

CEILING() Function

The CEILING() function returns the smallest integer that is greater than or equal to a specified number.

Syntax

CEILING(number)

Example

SELECT CEILING(10.2);

Output

11

For example, a billing or shipping system may use a ceiling operation when a quantity or measurement must be rounded upward.

FLOOR() Function

The FLOOR() function returns the largest integer that is less than or equal to a specified number.

Syntax

FLOOR(number)

Example

SELECT FLOOR(10.9);

Output

10

MOD() Function

The MOD() function returns the remainder obtained after dividing one number by another.

Syntax

MOD(dividend, divisor)

Example

SELECT MOD(15, 4);

Output

3

Applications of MOD()

• Checking whether a number is even or odd.
• Creating repeating patterns.
• Scheduling operations.
• Partitioning calculations.
• Implementing mathematical logic.

String Functions

String functions are used to process and manipulate character data. They are commonly used for names, addresses, email IDs, product names, search values, and report formatting.

UPPER() Function

The UPPER() function converts alphabetic characters in a string to uppercase.

Syntax

UPPER(string)

Example

SELECT UPPER('cse gyan');

Output

CSE GYAN

LOWER() Function

The LOWER() function converts alphabetic characters in a string to lowercase.

Syntax

LOWER(string)

Example

SELECT LOWER('SQL FUNCTIONS');

Output

sql functions

LENGTH() Function

The LENGTH() function returns the length of a string. The exact behavior for multibyte character sets can vary between database systems.

Syntax

LENGTH(string)

Example

SELECT LENGTH('Database');

Output

8

Applications

• Data validation.
• Text analysis.
• Input checking.
• Data quality verification.

CONCAT() Function

The CONCAT() function combines two or more strings into a single string.

Syntax

CONCAT(string1, string2, ...)

Example

SELECT CONCAT('CSE', ' ', 'Gyan');

Output

CSE Gyan

Applications

• Combining first and last names.
• Creating display names.
• Building addresses.
• Generating report messages.

TRIM() Function

The TRIM() function removes unwanted leading and trailing spaces from a string.

Syntax

TRIM(string)

Example

SELECT TRIM(' SQL ');

Output

SQL

TRIM() is particularly useful when cleaning imported or user-entered data.

SUBSTRING() Function

The SUBSTRING() function extracts a portion of a string.

Common MySQL-Style Syntax

SUBSTRING(string, start, length)

Example

SELECT SUBSTRING('Database', 1, 4);

Output

Data

The exact syntax and indexing rules can vary between database systems, so always check the documentation for the DBMS being used.

Combining Multiple SQL Functions

SQL functions can be nested, meaning one function can be used as the input of another function.

Example

SELECT UPPER(
    SUBSTRING('csegyan', 1, 3)
);

Output

CSE

Here, SUBSTRING() first extracts the first three characters and UPPER() then converts them to uppercase.

SQL Functions with a Student Table

Consider the following simplified Student table:

Example

SELECT
    UPPER(StudentName) AS StudentName,
    LENGTH(StudentName) AS NameLength
FROM Student;

This query displays student names in uppercase and also calculates the length of each name.

Aggregate Functions in SQL

Aggregate functions perform calculations on a group of rows and return a single summarized value.

They are widely used in reports, dashboards, data analysis, and business intelligence applications.

Common Aggregate Functions

• COUNT()
• SUM()
• AVG()
• MAX()
• MIN()

COUNT() Function

The COUNT() function counts rows or non-NULL values, depending on the expression used.

COUNT(*)

SELECT COUNT(*)
FROM Student;

COUNT(*) counts all rows returned by the query.

COUNT(column_name)

SELECT COUNT(StudentID)
FROM Student;

COUNT(column_name) counts non-NULL values in that column.

SUM() Function

The SUM() function calculates the total of numeric values.

Example

SELECT SUM(Fees)
FROM Student;

The query calculates the total fees represented by the selected rows.

AVG() Function

The AVG() function calculates the average of numeric values.

Example

SELECT AVG(Marks)
FROM Student;

This query calculates the average marks of the students.

MAX() Function

The MAX() function returns the highest value from the selected values.

Example

SELECT MAX(Marks)
FROM Student;

MIN() Function

The MIN() function returns the smallest value from the selected values.

Example

SELECT MIN(Marks)
FROM Student;

Using Multiple Aggregate Functions

SELECT
    COUNT(*) AS TotalStudents,
    SUM(Fees) AS TotalFees,
    AVG(Fees) AS AverageFees,
    MAX(Fees) AS MaximumFees,
    MIN(Fees) AS MinimumFees
FROM Student;

This query generates a summary of student records and fee values.

SQL Functions with GROUP BY

Aggregate functions are frequently combined with GROUP BY to generate separate summaries for different groups.

Example

SELECT
    Department,
    COUNT(*) AS TotalStudents
FROM Student
GROUP BY Department;

The query calculates the number of students in each department.

SQL Functions with HAVING

The HAVING clause is used to filter grouped results after aggregate calculations have been performed.

Example

SELECT
    Department,
    AVG(Marks) AS AverageMarks
FROM Student
GROUP BY Department
HAVING AVG(Marks) > 75;

Only departments whose average marks are greater than 75 are returned.

Date and Time Functions

Date and time functions are used to work with dates, times, timestamps, deadlines, schedules, transactions, and other time-related information.

The exact functions available can differ between database systems.

NOW() Function

In systems such as MySQL, NOW() returns the current date and time.

Example

SELECT NOW();

The returned value depends on the database server's current date, time, and session settings.

CURRENT_DATE

CURRENT_DATE returns the current date.

Example

SELECT CURRENT_DATE;

CURRENT_TIME

CURRENT_TIME returns the current time.

Example

SELECT CURRENT_TIME;

DATE_ADD() Function

In MySQL, DATE_ADD() is used to add a specified time interval to a date or datetime value.

Example

SELECT DATE_ADD(
    '2026-07-21',
    INTERVAL 10 DAY
);

The result represents the date after adding 10 days.

DATEDIFF() Function

In MySQL, DATEDIFF() returns the number of days between two date values.

Example

SELECT DATEDIFF(
    '2026-12-31',
    '2026-01-01'
);

The query calculates the number of days between the two dates.

Applications of Date Functions

• Employee attendance systems.
• Student admission systems.
• Hotel reservation systems.
• Subscription management.
• Banking transactions.
• Project deadline tracking.
• Order and delivery systems.

Conversion Functions

Conversion functions are used to convert a value from one data type to another.

They are particularly useful when data from different sources needs to be processed together.

CAST() Function

The CAST() function converts an expression into a specified data type.

Syntax

CAST(expression AS data_type)

Example

SELECT CAST(125.75 AS INT);

Depending on the database system, the resulting integer value is typically 125.

Applications

• Data conversion.
• Data migration.
• Report generation.
• Application integration.
• Comparing values of compatible types.

CONVERT() Function

CONVERT() is a database-specific conversion function. For example, SQL Server provides CONVERT() with syntax and style options that differ from MySQL.

SQL Server Example

SELECT CONVERT(VARCHAR(20), 125);

The number is converted into a character value in SQL Server.

NULL Handling Functions

NULL represents a missing, unknown, or unavailable value. SQL provides functions that can be used to handle NULL values while displaying or processing data.

COALESCE() Function

The COALESCE() function returns the first non-NULL value from a list of expressions.

Syntax

COALESCE(value1, value2, value3, ...)

Example

SELECT COALESCE(
    NULL,
    NULL,
    'Available'
);

Output

Available

Applications

• Replacing missing values.
• Generating reports.
• Data cleaning.
• Providing default values.

IFNULL() Function

In MySQL, IFNULL() returns one value when the first expression is NULL and another value otherwise.

Syntax

IFNULL(expression, alternative_value)

Example

SELECT IFNULL(
    PhoneNumber,
    'Not Available'
)
FROM Student;

If PhoneNumber is NULL, the query displays "Not Available".

Real-World Example: Banking System

Banking systems use SQL functions to generate account summaries, calculate balances, analyze transactions, and prepare reports.

SELECT
    COUNT(AccountID) AS TotalAccounts,
    SUM(Balance) AS TotalBalance,
    AVG(Balance) AS AverageBalance
FROM Accounts;

Real-World Example: E-Commerce System

An e-commerce application can use aggregate functions to calculate orders and revenue.

SELECT
    COUNT(OrderID) AS TotalOrders,
    SUM(TotalAmount) AS TotalRevenue
FROM Orders;

Real-World Example: University Management System

A university can calculate average marks department-wise using aggregate functions with GROUP BY.

SELECT
    Department,
    AVG(Marks) AS AverageMarks
FROM Student
GROUP BY Department;

Performance Considerations When Using SQL Functions

Functions are useful, but applying functions to large amounts of data can sometimes affect query performance, particularly when a function is applied to an indexed column in a filtering condition.

• Select only the required columns.
• Filter unnecessary rows early.
• Use suitable indexes.
• Avoid unnecessary nested functions.
• Use appropriate data types.
• Check query execution plans for expensive queries.
• Test queries with realistic amounts of data.

Advantages of SQL Functions

• Simplify complex operations.
• Reduce repetitive SQL code.
• Make queries more expressive.
• Support data analysis.
• Improve report generation.
• Help clean and transform data.
• Support calculations and summaries.
• Improve database application development.

Limitations of SQL Functions

• Function syntax can differ between DBMS products.
• Excessive use of functions can affect performance.
• Complex nested functions can reduce readability.
• Some functions may prevent efficient index usage in certain queries.
• Large-scale calculations may require query optimization.

Important SQL Functions Summary

SQL Functions Interview Questions and Answers

1. What is an SQL Function?

An SQL Function is a predefined operation that performs a specific task and returns a result.

2. What are the major categories of SQL Functions?

Single-row functions and aggregate functions are two important categories.

3. What does COUNT() do?

COUNT() counts rows or non-NULL values depending on the expression.

4. What does SUM() do?

SUM() calculates the total of numeric values.

5. What does AVG() do?

AVG() calculates the average of numeric values.

6. What does MAX() return?

MAX() returns the highest value from the selected values.

7. What does MIN() return?

MIN() returns the smallest value from the selected values.

8. What is UPPER() used for?

UPPER() converts text to uppercase.

9. What is LOWER() used for?

LOWER() converts text to lowercase.

10. What does LENGTH() do?

It returns the length of a string according to the database system's definition of the function.

11. What is CONCAT()?

CONCAT() combines multiple strings.

12. What is TRIM() used for?

TRIM() removes leading and trailing spaces from text.

13. What is SUBSTRING()?

SUBSTRING() extracts a portion of a string.

14. What does ABS() return?

ABS() returns the absolute value of a number.

15. What is ROUND() used for?

ROUND() rounds a numeric value to a specified precision.

16. What does FLOOR() do?

FLOOR() returns the largest integer less than or equal to the given value.

17. What does CEILING() do?

CEILING() returns the smallest integer greater than or equal to the given value.

18. What is MOD()?

MOD() returns the remainder after division.

19. What does NOW() do?

In MySQL, NOW() returns the current date and time.

20. What is CURRENT_DATE?

CURRENT_DATE returns the current date.

21. What is CURRENT_TIME?

CURRENT_TIME returns the current time.

22. What is DATE_ADD()?

In MySQL, DATE_ADD() adds a specified interval to a date or datetime value.

23. What is DATEDIFF()?

In MySQL, DATEDIFF() calculates the difference between two dates in days.

24. What is CAST()?

CAST() converts an expression from one data type to another.

25. Is CONVERT() the same in every DBMS?

No. CONVERT() syntax and behavior are database-specific.

26. What is COALESCE()?

COALESCE() returns the first non-NULL expression from a list.

27. What is IFNULL()?

In MySQL, IFNULL() returns an alternative value when the first expression is NULL.

28. Can aggregate functions be used with GROUP BY?

Yes. Aggregate functions are frequently used with GROUP BY.

29. Can aggregate functions be used with HAVING?

Yes. HAVING can filter groups based on aggregate results.

30. Why are SQL Functions important?

SQL Functions simplify calculations, text processing, date handling, NULL handling, data conversion, reporting, and data analysis.

Conclusion

SQL Functions are an essential part of relational database programming. They allow developers to perform calculations, manipulate strings, process dates, summarize records, convert data types, and handle NULL values efficiently.

Important functions such as ABS(), ROUND(), UPPER(), LOWER(), CONCAT(), COUNT(), SUM(), AVG(), MAX(), MIN(), COALESCE(), and CAST() are useful in everyday SQL development as well as academic and interview preparation.

Students should practice these functions with real database tables rather than memorizing only their definitions. Understanding when and why a particular function is used makes SQL queries easier to write, read, debug, and optimize.