SQL Functions

SQL Functions are predefined operations provided by database systems to perform calculations, manipulate text, process dates, handle NULL values, and analyze data efficiently. Instead of writing complex logic manually, developers can use built-in SQL functions to simplify queries and improve productivity.

Functions are widely used in reporting systems, banking applications, e-commerce websites, educational portals, healthcare systems, and business intelligence tools. They help retrieve meaningful information from stored data while reducing query complexity.

Understanding SQL Functions is essential for database developers, administrators, analysts, and students because they are used in almost every real-world SQL query.

What are SQL Functions?

An SQL Function is a predefined routine that accepts input values, performs a specific operation, and returns a result.

Functions can be used within SELECT, WHERE, HAVING, ORDER BY, GROUP BY, and other SQL clauses.

Simple Example

SELECT UPPER('cse gyan');

Output:

CSE GYAN

The UPPER() function converts text into uppercase letters.

Why SQL Functions are Important?

Reduce query complexity.
Save development time.
Improve readability.
Perform calculations easily.
Manipulate text efficiently.
Process date and time values.
Support advanced data analysis.
Improve reporting capabilities.

Types of SQL Functions

SQL functions are generally classified into two major categories:

Function Type	Description
Single Row Functions	Operate on one row and return one result.
Aggregate Functions	Operate on multiple rows and return one result.

In Part 1, we will focus on Single Row Functions, Numeric Functions, and String Functions.

Single Row Functions

Single Row Functions process one row at a time and return a single value for each row.

Examples

UPPER()
LOWER()
LENGTH()
ROUND()
ABS()
SUBSTRING()

Numeric Functions

Numeric Functions perform mathematical calculations on numeric data. They are commonly used in financial systems, statistical reports, billing applications, and scientific calculations.

ABS() Function

The ABS() function returns the absolute value of a number by removing its negative sign.

Syntax

ABS(number)

Example

SELECT ABS(-250);

Output:

Practical Use of ABS()

Banks and accounting systems use ABS() to display positive differences between values.

ROUND() Function

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

Syntax

ROUND(number, decimal_places)

Example

SELECT ROUND(125.6789, 2);

Output:

125.68

Applications of ROUND()

Financial calculations.
Tax computation.
Scientific measurements.
Percentage calculations.

CEILING() Function

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

Example

SELECT CEILING(10.2);

Output:

Real-World Example

Shipping systems may round package weights upward to determine charges.

FLOOR() Function

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

Example

SELECT FLOOR(10.9);

Output:

Practical Applications of FLOOR()

Inventory management.
Student grading systems.
Statistical calculations.
Data analysis reports.

MOD() Function

The MOD() function returns the remainder after division.

Syntax

MOD(dividend, divisor)

Example

SELECT MOD(15,4);

Output:

Applications of MOD()

Even and odd number detection.
Scheduling algorithms.
Data partitioning.
Programming logic.

String Functions

String Functions are used to manipulate and process textual information stored in databases.

These functions are commonly used in customer management systems, educational portals, e-commerce platforms, and content management systems.

UPPER() Function

The UPPER() function converts all characters to uppercase.

Example

SELECT UPPER('cse gyan');

Output:

CSE GYAN

Applications of UPPER()

Standardizing user input.
Creating reports.
Data comparison.
Search optimization.

LOWER() Function

The LOWER() function converts text into lowercase characters.

Example

SELECT LOWER('SQL FUNCTIONS');

Output:

sql functions

LENGTH() Function

The LENGTH() function returns the number of characters in a string.

Example

SELECT LENGTH('Database');

Output:

Why LENGTH() is Useful?

Data validation.
Password checking.
Text analysis.
Input verification.

CONCAT() Function

The CONCAT() function combines multiple strings into a single string.

Example

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

Output:

CSE Gyan

Real-World Applications of CONCAT()

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

TRIM() Function

The TRIM() function removes leading and trailing spaces from text.

Example

SELECT TRIM(' SQL ');

Output:

SQL

Benefits of TRIM()

Improves data consistency.
Prevents comparison errors.
Enhances data quality.
Supports data cleaning.

SUBSTRING() Function

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

Syntax

SUBSTRING(text,start,length)

Example

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

Output:

Data

Applications of SUBSTRING()

Extracting codes.
Parsing text.
Generating reports.
Data transformation.

Combining Multiple Functions

SQL functions can be used together in a single query.

Example

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

Output:

CSE

Real-World Example: Student Database

SELECT
UPPER(StudentName),
LENGTH(StudentName)

FROM Student;

The query converts student names to uppercase and calculates their lengths.

Aggregate Functions in SQL

Aggregate Functions are special SQL functions that perform calculations on multiple rows and return a single result. These functions are widely used in reporting, analytics, dashboards, and business intelligence systems.

Unlike single-row functions that work on one value at a time, aggregate functions analyze a group of records together.

COUNT() Function

The COUNT() function returns the total number of records that match a specified condition.

Syntax

SELECT COUNT(column_name)
FROM table_name;

Example

SELECT COUNT(StudentID)
FROM Student;

This query returns the total number of students stored in the Student table.

COUNT(*) Example

SELECT COUNT(*)
FROM Student;

COUNT(*) counts all rows in a table regardless of NULL values.

SUM() Function

The SUM() function calculates the total of a numeric column.

Syntax

SELECT SUM(column_name)
FROM table_name;

Example

SELECT SUM(Fees)
FROM Student;

The query returns the total fees collected from all students.

Applications of SUM()

Sales reporting.
Financial analysis.
Inventory calculations.
Budget management.
Business dashboards.

AVG() Function

The AVG() function calculates the average value of a numeric column.

Example

SELECT AVG(Marks)
FROM Student;

The query returns the average marks of all students.

Practical Uses of AVG()

Student performance analysis.
Salary calculations.
Product rating systems.
Business forecasting.

MAX() Function

The MAX() function returns the highest value from a column.

Example

SELECT MAX(Salary)
FROM Employee;

The query returns the highest salary in the Employee table.

MIN() Function

The MIN() function returns the smallest value from a column.

Example

SELECT MIN(Price)
FROM Product;

The query returns the lowest product price.

Combining Aggregate Functions

Multiple aggregate functions can be used within a single query.

SELECT
COUNT(*),
SUM(Fees),
AVG(Fees),
MAX(Fees),
MIN(Fees)

FROM Student;

This query generates a complete summary report.

Date and Time Functions

Date and Time Functions help manage temporal data such as dates, timestamps, schedules, and logs.

These functions are heavily used in attendance systems, booking applications, banking systems, and reporting platforms.

NOW() Function

The NOW() function returns the current date and time of the database server.

Example

SELECT NOW();

Sample Output:

2026-07-21 10:45:30

CURRENT_DATE Function

CURRENT_DATE returns the current system date.

SELECT CURRENT_DATE;

CURRENT_TIME Function

CURRENT_TIME returns the current system time.

SELECT CURRENT_TIME;

DATE_ADD() Function

DATE_ADD() adds a specific interval to a date value.

Example

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

The result will be a date 10 days later.

DATEDIFF() Function

DATEDIFF() calculates the difference between two dates.

Example

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

This query returns the number of days between the two dates.

Real-World Date Function Applications

Employee attendance systems.
Student admission tracking.
Hotel reservation systems.
Subscription management.
Project deadline calculations.

Conversion Functions

Conversion functions change data from one type into another type.

These functions improve compatibility between different data formats.

CAST() Function

CAST() converts a value into a specified data type.

Syntax

CAST(expression AS datatype)

Example

SELECT CAST(
125.75 AS INT
);

Output:

Why CAST() is Important?

Data conversion.
Reporting systems.
Data migration.
Application integration.

CONVERT() Function

CONVERT() is another conversion function available in many database systems.

Example

SELECT CONVERT(
VARCHAR,
125
);

The number is converted into text format.

NULL Handling Functions

NULL values represent missing or unknown information. SQL provides special functions to handle NULL values effectively.

COALESCE() Function

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

Example

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

Output:

Available

Applications of COALESCE()

Replacing missing values.
Data cleaning.
Generating reports.
Improving query reliability.

IFNULL() Function

IFNULL() replaces NULL values with an alternative value.

Example

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

Functions with GROUP BY

Aggregate functions are often used with GROUP BY to summarize data.

Example

SELECT Department,
COUNT(*)

FROM Student

GROUP BY Department;

The query counts students in each department.

Functions with HAVING

HAVING filters grouped data after aggregate calculations are performed.

Example

SELECT Department,
AVG(Marks)

FROM Student

GROUP BY Department

HAVING AVG(Marks) > 75;

Only departments with average marks greater than 75 are displayed.

Real-World Example: Banking System

SELECT
COUNT(AccountID),
SUM(Balance),
AVG(Balance)

FROM Accounts;

Banks use aggregate functions to generate financial summaries.

Real-World Example: E-Commerce Website

SELECT
COUNT(OrderID),
SUM(TotalAmount)

FROM Orders;

This query calculates total orders and revenue.

Real-World Example: University Management System

SELECT Department,
AVG(Marks)

FROM Student

GROUP BY Department;

Universities use such reports to evaluate academic performance.

Performance Considerations

Functions are powerful but should be used carefully to maintain performance.

Use indexes effectively.
Avoid unnecessary calculations.
Filter data before aggregation.
Use proper data types.
Optimize large queries.
Analyze execution plans.

Advantages of SQL Functions

Simplify complex calculations.
Improve productivity.
Reduce coding effort.
Enhance reporting capabilities.
Support business analytics.
Improve data quality.
Increase query flexibility.

Limitations of SQL Functions

Excessive use may affect performance.
Database-specific differences exist.
Complex nesting can reduce readability.
Large-scale calculations may require optimization.

SQL Functions Interview Questions and Answers

1. What is an SQL Function?

A predefined routine that performs a specific task and returns a value.

2. What are the main categories of SQL Functions?

Single Row Functions and Aggregate Functions.

3. What does COUNT() do?

Counts rows or values.

4. What does SUM() do?

Adds numeric values.

5. What does AVG() do?

Calculates average values.

6. What does MAX() return?

The highest value.

7. What does MIN() return?

The smallest value.

8. What is the purpose of UPPER()?

Converts text to uppercase.

9. What is the purpose of LOWER()?

Converts text to lowercase.

10. What does LENGTH() return?

The number of characters in a string.

11. What does CONCAT() do?

Combines strings.

12. What is TRIM() used for?

Removing extra spaces.

13. What is SUBSTRING()?

Extracts part of a string.

14. What does ABS() return?

The absolute value of a number.

15. What is ROUND() used for?

Rounding decimal values.

16. What does FLOOR() do?

Returns the lower integer value.

17. What does CEILING() do?

Returns the higher integer value.

18. What is MOD()?

Returns the remainder after division.

19. What does NOW() return?

Current date and time.

20. What does CURRENT_DATE return?

Current system date.

21. What does CURRENT_TIME return?

Current system time.

22. What is DATE_ADD()?

Adds an interval to a date.

23. What is DATEDIFF()?

Calculates difference between dates.

24. What is CAST()?

Converts one data type into another.

25. What is CONVERT()?

A data conversion function.

26. What is COALESCE()?

Returns the first non-NULL value.

27. What is IFNULL()?

Replaces NULL values.

28. Can functions be used with GROUP BY?

Yes.

29. Can functions be used with HAVING?

Yes.

30. Why are SQL Functions important?

They simplify data processing, calculations, and reporting.

Conclusion

SQL Functions are among the most powerful tools available in relational database systems. They help developers perform calculations, manipulate text, manage dates, handle NULL values, and generate meaningful reports without writing complicated logic.

By mastering Numeric Functions, String Functions, Aggregate Functions, Date Functions, Conversion Functions, and NULL Functions, students and developers can write efficient SQL queries and build professional database applications. SQL Functions are essential for database development, data analysis, reporting systems, and interview preparation.

← Previous: SQL Joins Next: Subqueries →