Chapter 7: Functions in SQL - Complete Guide

SQL functions predefined operations hain jo data par calculations perform

karte hain. Ye functions mainly do categories mein divide kiye jaate hain:
Aggregate Functions aur Scalar Functions. Is chapter mein hum in dono
types ke functions ko complete detail mein samjhenge.

🔹 Aggregate Functions

Aggregate functions multiple rows par calculations perform karte hain aur
single value return karte hain. Ye functions generally GROUP BY clause ke
saath use hote hain.

COUNT() - Records Count Karna

COUNT function table mein records ki total number return karta hai.

Syntax:

sql

SELECT COUNT(column_name)

FROM table_name

WHERE condition;

Examples:

Total records count:

sql

SELECT COUNT(*) AS TotalEmployees

FROM Employees;

Specific column ki count:

sql

SELECT COUNT(EmployeeID) AS TotalEmployees

FROM Employees;

NULL values exclude karte hue count:

sql
SELECT COUNT(DepartmentID) AS EmployeesWithDepartment

FROM Employees;

Condition ke saath count:

sql

SELECT COUNT(*) AS ITEmployees

FROM Employees

WHERE DepartmentID = 2;

DISTINCT values ki count:

sql

SELECT COUNT(DISTINCT DepartmentID) AS UniqueDepartments

FROM Employees;

SUM() - Values ka Sum Calculate Karna

SUM function numeric column ki sabhi values ka sum return karta hai.

Syntax:

sql

SELECT SUM(column_name)

FROM table_name

WHERE condition;

Examples:

Total salary sum:

sql

SELECT SUM(Salary) AS TotalSalary

FROM Employees;

Condition ke saath sum:

sql

SELECT SUM(Salary) AS ITSalary

FROM Employees
WHERE DepartmentID = 2;

Mathematical operations ke saath:

sql

SELECT SUM(Salary * 0.10) AS TotalBonus

FROM Employees;

AVG() - Average Calculate Karna

AVG function numeric column ki sabhi values ka average return karta hai.

Syntax:

sql

SELECT AVG(column_name)

FROM table_name

WHERE condition;

Examples:

Average salary:

sql

SELECT AVG(Salary) AS AverageSalary

FROM Employees;

Condition ke saath average:

sql

SELECT AVG(Salary) AS ITAverageSalary

FROM Employees

WHERE DepartmentID = 2;

ROUND ke saath average:

sql

SELECT ROUND(AVG(Salary), 2) AS RoundedAverage

FROM Employees;

MAX() - Maximum Value Find Karna

MAX function kisi column ki maximum value return karta hai.

Syntax:

sql

SELECT MAX(column_name)

FROM table_name

WHERE condition;

Examples:

Maximum salary:

sql

SELECT MAX(Salary) AS HighestSalary

FROM Employees;

Date column mein maximum:

sql

SELECT MAX(HireDate) AS LatestHire

FROM Employees;

String column mein maximum:

sql

SELECT MAX(FirstName) AS LastAlphabeticalName

FROM Employees;

MIN() - Minimum Value Find Karna

MIN function kisi column ki minimum value return karta hai.

Syntax:

sql

SELECT MIN(column_name)

FROM table_name

WHERE condition;

Examples:
Minimum salary:

sql

SELECT MIN(Salary) AS LowestSalary

FROM Employees;

Date column mein minimum:

sql

SELECT MIN(HireDate) AS EarliestHire

FROM Employees;

Condition ke saath minimum:

sql

SELECT MIN(Salary) AS ITLowestSalary

FROM Employees

WHERE DepartmentID = 2;

Multiple Aggregate Functions Ek Saath

Example:

sql

SELECT

COUNT(*) AS TotalEmployees,

SUM(Salary) AS TotalSalary,

AVG(Salary) AS AverageSalary,

MAX(Salary) AS HighestSalary,

MIN(Salary) AS LowestSalary

FROM Employees;

GROUP BY ke Saath Aggregate Functions

Department-wise statistics:

sql

SELECT
 DepartmentID,

COUNT(*) AS EmployeeCount,

SUM(Salary) AS TotalSalary,

AVG(Salary) AS AverageSalary,

MAX(Salary) AS HighestSalary,

MIN(Salary) AS LowestSalary

FROM Employees

GROUP BY DepartmentID;

HAVING Clause ke Saath Aggregate Functions

Example:

sql

SELECT

DepartmentID,

COUNT(*) AS EmployeeCount,

AVG(Salary) AS AverageSalary

FROM Employees

GROUP BY DepartmentID

HAVING COUNT(*) > 5 AND AVG(Salary) > 50000;

🔹 Scalar Functions

Scalar functions single value leti hain aur single value return karti hain. Ye
functions har row ke liye separately execute hoti hain.

String Functions

UPPER() - Text ko Uppercase Mein Convert Karna

Syntax:

sql

SELECT UPPER(column_name)
FROM table_name;

Examples:

sql

SELECT UPPER(FirstName) AS UpperFirstName

FROM Employees;

SELECT UPPER(CONCAT(FirstName, ' ', LastName)) AS FullNameUpper

FROM Employees;

LOWER() - Text ko Lowercase Mein Convert Karna

Syntax:

sql

SELECT LOWER(column_name)

FROM table_name;

Examples:

sql

SELECT LOWER(FirstName) AS LowerFirstName

FROM Employees;

SELECT LOWER(Email) AS LowerEmail

FROM Employees;

LEN() / LENGTH() - Text ki Length Check Karna

Different Database Syntax:

MySQL:

sql

SELECT FirstName, LENGTH(FirstName) AS NameLength

FROM Employees;

SQL Server:
sql

SELECT FirstName, LEN(FirstName) AS NameLength

FROM Employees;

PostgreSQL:

sql

SELECT FirstName, LENGTH(FirstName) AS NameLength

FROM Employees;

Examples:

sql

SELECT FirstName, LEN(FirstName) AS NameLength

FROM Employees

WHERE LEN(FirstName) > 5;

LEFT() - Left se Characters Extract Karna

Syntax:

sql

SELECT LEFT(column_name, number_of_chars)

FROM table_name;

Examples:

sql

SELECT FirstName, LEFT(FirstName, 3) AS FirstThreeChars

FROM Employees;

SELECT Email, LEFT(Email, 5) AS EmailPrefix

FROM Employees;

RIGHT() - Right se Characters Extract Karna

Syntax:

sql
SELECT RIGHT(column_name, number_of_chars)

FROM table_name;

Examples:

sql

SELECT Email, RIGHT(Email, 10) AS EmailDomain

FROM Employees;

SUBSTRING() / SUBSTR() - Specific Part Extract Karna

Syntax:

sql

SELECT SUBSTRING(column_name, start, length)

FROM table_name;

Examples:

sql

SELECT Email, SUBSTRING(Email, 1, 5) AS FirstPart

FROM Employees;

SELECT FirstName, SUBSTRING(FirstName, 2, 3) AS MiddlePart

FROM Employees;

REPLACE() - Text Replace Karna

Syntax:

sql

SELECT REPLACE(column_name, old_text, new_text)

FROM table_name;

Examples:

sql

SELECT Email, REPLACE(Email, '[Link]', '[Link]') AS

NewEmail
FROM Employees;

SELECT Description, REPLACE(Description, 'old', 'new') AS

UpdatedDescription

FROM Products;

TRIM() - Spaces Remove Karna

Syntax:

sql

SELECT TRIM(column_name)

FROM table_name;

Examples:

sql

SELECT TRIM(FirstName) AS TrimmedName

FROM Employees;

SELECT TRIM(BOTH ' ' FROM FirstName) AS TrimmedName

FROM Employees;

SELECT TRIM(LEADING ' ' FROM FirstName) AS LeftTrimmed

FROM Employees;

SELECT TRIM(TRAILING ' ' FROM FirstName) AS RightTrimmed

FROM Employees;

CONCAT() - Strings Join Karna

Different Database Syntax:

MySQL:

sql
SELECT CONCAT(FirstName, ' ', LastName) AS FullName

FROM Employees;

SQL Server:

sql

SELECT FirstName + ' ' + LastName AS FullName

FROM Employees;

PostgreSQL:

sql

SELECT FirstName || ' ' || LastName AS FullName

FROM Employees;

Examples:

sql

SELECT CONCAT(FirstName, ' ', LastName, ' - ', Email) AS EmployeeInfo

FROM Employees;

Numeric Functions

ROUND() - Numbers ko Round Karna

Syntax:

sql

SELECT ROUND(column_name, decimals)

FROM table_name;

Examples:

sql

SELECT Salary, ROUND(Salary, 0) AS RoundedSalary

FROM Employees;

SELECT AVG(Salary), ROUND(AVG(Salary), 2) AS RoundedAverage

FROM Employees;
SELECT Salary, ROUND(Salary, -3) AS RoundedToThousands

FROM Employees;

CEIL() / CEILING() - Round Up Karna

Syntax:

sql

SELECT CEIL(column_name)

FROM table_name;

Examples:

sql

SELECT Salary, CEIL(Salary) AS CeilSalary

FROM Employees;

SELECT 15.25, CEIL(15.25) AS Result; -- Returns 16

FLOOR() - Round Down Karna

Syntax:

sql

SELECT FLOOR(column_name)

FROM table_name;

Examples:

sql

SELECT Salary, FLOOR(Salary) AS FloorSalary

FROM Employees;

SELECT 15.75, FLOOR(15.75) AS Result; -- Returns 15

ABS() - Absolute Value Lena

Syntax:
sql

SELECT ABS(column_name)

FROM table_name;

Examples:

sql

SELECT Salary, ABS(Salary) AS AbsoluteSalary

FROM Employees;

SELECT -15.5, ABS(-15.5) AS Result; -- Returns 15.5

POWER() - Power Calculate Karna

Syntax:

sql

SELECT POWER(column_name, power)

FROM table_name;

Examples:

sql

SELECT Salary, POWER(Salary, 2) AS SalarySquared

FROM Employees;

SELECT 5, POWER(5, 3) AS Result; -- Returns 125

SQRT() - Square Root Calculate Karna

Syntax:

sql

SELECT SQRT(column_name)

FROM table_name;

Examples:

sql
SELECT Salary, SQRT(Salary) AS SalaryRoot

FROM Employees;

Date and Time Functions

NOW() / GETDATE() - Current Date Time Lena

Different Database Syntax:

MySQL:

sql

SELECT NOW() AS CurrentDateTime;

SQL Server:

sql

SELECT GETDATE() AS CurrentDateTime;

PostgreSQL:

sql

SELECT NOW() AS CurrentDateTime;

Examples:

sql

SELECT FirstName, HireDate, NOW() AS CurrentDate

FROM Employees;

SELECT NOW() AS CurrentTime;

CURDATE() / GETDATE() - Current Date Lena

MySQL:

sql

SELECT CURDATE() AS CurrentDate;

SQL Server:

sql

SELECT CAST(GETDATE() AS DATE) AS CurrentDate;

Examples:

sql

SELECT FirstName, HireDate, CURDATE() AS Today

FROM Employees;

CURTIME() - Current Time Lena

MySQL:

sql

SELECT CURTIME() AS CurrentTime;

Examples:

sql

SELECT FirstName, CURTIME() AS CurrentTime

FROM Employees;

DATEADD() / DATE_ADD() - Date Mein Add Karna

SQL Server:

sql

SELECT DATEADD(day, 7, HireDate) AS OneWeekLater

FROM Employees;

SELECT DATEADD(month, 1, HireDate) AS OneMonthLater

FROM Employees;

SELECT DATEADD(year, 1, HireDate) AS OneYearLater

FROM Employees;

MySQL:

sql

SELECT DATE_ADD(HireDate, INTERVAL 7 DAY) AS OneWeekLater

FROM Employees;
DATEDIFF() - Date Difference Calculate Karna

SQL Server:

sql

SELECT DATEDIFF(day, HireDate, GETDATE()) AS DaysEmployed

FROM Employees;

SELECT DATEDIFF(month, HireDate, GETDATE()) AS MonthsEmployed

FROM Employees;

SELECT DATEDIFF(year, HireDate, GETDATE()) AS YearsEmployed

FROM Employees;

MySQL:

sql

SELECT DATEDIFF(CURDATE(), HireDate) AS DaysEmployed

FROM Employees;

DATEPART() / EXTRACT() - Date ka Part Extract Karna

SQL Server:

sql

SELECT DATEPART(year, HireDate) AS HireYear

FROM Employees;

SELECT DATEPART(month, HireDate) AS HireMonth

FROM Employees;

SELECT DATEPART(day, HireDate) AS HireDay

FROM Employees;

MySQL, PostgreSQL:
sql

SELECT EXTRACT(YEAR FROM HireDate) AS HireYear

FROM Employees;

SELECT EXTRACT(MONTH FROM HireDate) AS HireMonth

FROM Employees;

DAY(), MONTH(), YEAR() - Date Parts Lena

Examples:

sql

SELECT

HireDate,

DAY(HireDate) AS HireDay,

MONTH(HireDate) AS HireMonth,

YEAR(HireDate) AS HireYear

FROM Employees;

Advanced Scalar Functions

COALESCE() - First Non-NULL Value Return Karna

Syntax:

sql

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

FROM table_name;

Examples:

sql

SELECT

FirstName,

DepartmentID,

COALESCE(DepartmentID, 0) AS DepartmentWithDefault
FROM Employees;

SELECT

FirstName,

MiddleName,

LastName,

COALESCE(MiddleName, 'No Middle Name') AS MiddleNameSafe

FROM Employees;

SELECT

FirstName,

Phone,

Mobile,

COALESCE(Phone, Mobile, 'No Contact') AS ContactNumber

FROM Employees;

ISNULL() - NULL Values Replace Karna (SQL Server)

Syntax:

sql

SELECT ISNULL(column_name, replacement_value)

FROM table_name;

Examples:

sql

SELECT

FirstName,

ISNULL(DepartmentID, 0) AS DepartmentWithDefault

FROM Employees;
SELECT

FirstName,

ISNULL(MiddleName, 'N/A') AS MiddleNameSafe

FROM Employees;

NULLIF() - Equal Values ko NULL Mein Convert Karna

Syntax:

sql

SELECT NULLIF(column1, column2)

FROM table_name;

Examples:

sql

SELECT

FirstName,

LastName,

NULLIF(FirstName, LastName) AS NameComparison

FROM Employees;

SELECT

Salary,

Bonus,

NULLIF(Salary, Bonus) AS SalaryBonusComparison

FROM Employees;

CASE Statement - Conditional Logic

Syntax:

sql

SELECT

CASE
 WHEN condition1 THEN result1

WHEN condition2 THEN result2

ELSE result

END

FROM table_name;

Examples:

sql

SELECT

FirstName,

Salary,

CASE

WHEN Salary > 70000 THEN 'High'

WHEN Salary BETWEEN 50000 AND 70000 THEN 'Medium'

ELSE 'Low'

END AS SalaryGrade

FROM Employees;

SELECT

FirstName,

DepartmentID,

CASE DepartmentID

WHEN 1 THEN 'Human Resources'

WHEN 2 THEN 'Information Technology'

WHEN 3 THEN 'Finance'

ELSE 'Other'

END AS DepartmentName

FROM Employees;
Complete Practical Examples

Example 1: Employee Data Analysis

sql

SELECT

DepartmentID,

COUNT(*) AS TotalEmployees,

SUM(Salary) AS TotalSalary,

ROUND(AVG(Salary), 2) AS AverageSalary,

MAX(Salary) AS HighestSalary,

MIN(Salary) AS LowestSalary,

MAX(Salary) - MIN(Salary) AS SalaryRange

FROM Employees

WHERE DepartmentID IS NOT NULL

GROUP BY DepartmentID

HAVING COUNT(*) > 2

ORDER BY AverageSalary DESC;

Example 2: Employee Information Formatting

sql

SELECT

EmployeeID,

UPPER(FirstName) AS FirstNameUpper,

LOWER(LastName) AS LastNameLower,

CONCAT(LEFT(FirstName, 1), LEFT(LastName, 1)) AS Initials,

CONCAT(FirstName, ' ', LastName) AS FullName,

Email,

REPLACE(Email, '@[Link]', '@[Link]') AS NewEmail,

 Salary,

ROUND(Salary, -3) AS RoundedSalary,

CASE

WHEN Salary > 75000 THEN 'Executive'

WHEN Salary BETWEEN 50000 AND 75000 THEN 'Senior'

ELSE 'Junior'

END AS EmployeeLevel,

HireDate,

DATEDIFF(year, HireDate, GETDATE()) AS YearsWithCompany,

COALESCE(DepartmentID, 0) AS DepartmentSafe

FROM Employees

ORDER BY YearsWithCompany DESC, Salary DESC;

Example 3: Department-wise Comprehensive Report

sql

SELECT

DepartmentID,

COUNT(*) AS EmployeeCount,

COUNT(DISTINCT JobTitle) AS UniqueJobTitles,

SUM(Salary) AS TotalSalaryExpenditure,

ROUND(AVG(Salary), 2) AS AverageSalary,

MAX(Salary) AS HighestSalary,

MIN(Salary) AS LowestSalary,

ROUND(SUM(Salary) / COUNT(*), 2) AS CostPerEmployee,

COUNT(CASE WHEN YEAR(HireDate) = 2023 THEN 1 END) AS

NewHires2023,

ROUND((COUNT(CASE WHEN YEAR(HireDate) = 2023 THEN 1 END) * 100.0

/ COUNT(*)), 2) AS NewHirePercentage
FROM Employees

WHERE DepartmentID IS NOT NULL

GROUP BY DepartmentID

HAVING COUNT(*) >= 3

ORDER BY TotalSalaryExpenditure DESC;

Example 4: Data Cleaning and Transformation

sql

SELECT

EmployeeID,

TRIM(FirstName) AS CleanedFirstName,

TRIM(LastName) AS CleanedLastName,

LOWER(TRIM(Email)) AS StandardizedEmail,

COALESCE(Phone, 'Not Provided') AS ContactNumber,

CASE

WHEN LEN(FirstName) < 3 THEN 'Short Name'

WHEN LEN(FirstName) BETWEEN 3 AND 6 THEN 'Medium Name'

ELSE 'Long Name'

END AS NameLengthCategory,

ROUND(Salary / 12, 2) AS MonthlySalary,

FLOOR(DATEDIFF(day, HireDate, GETDATE()) / 365.25) AS CompleteYears,

ISNULL(ManagerID, 0) AS ManagerSafe

FROM Employees

WHERE Email LIKE '%@[Link]'

ORDER BY CompleteYears DESC, MonthlySalary DESC;

Performance Considerations

1. Function Usage in WHERE Clause

sql

-- ❌ SLOW: Function on indexed column

SELECT * FROM Employees WHERE UPPER(FirstName) = 'AMIT';

-- ✅ FAST: Direct comparison

SELECT * FROM Employees WHERE FirstName = 'Amit';

2. Computed Columns Indexing

sql

-- ✅ Computed column par index bana sakte hain

CREATE INDEX idx_employee_fullname ON Employees(CONCAT(FirstName, '

', LastName));

3. Avoid Multiple Function Calls

sql

-- ❌ INEFFICIENT

SELECT UPPER(FirstName), LOWER(LastName), LEN(FirstName)

FROM Employees;

-- ✅ EFFICIENT

SELECT FirstName, LastName,

UPPER(FirstName) AS UpperFirst,

LOWER(LastName) AS LowerLast,

LEN(FirstName) AS NameLength

FROM Employees;

Chapter Summary
Function
Purpose Common Functions
Type

Aggregat Multiple rows par

COUNT, SUM, AVG, MAX, MIN
e calculations

Text data UPPER, LOWER, LEN, SUBSTRING,

String
manipulation REPLACE, TRIM, CONCAT

ROUND, CEIL, FLOOR, ABS, POWER,

Numeric Number operations
SQRT

Date/ NOW, CURDATE, DATEADD,

Date operations
Time DATEDIFF, DATEPART

Advanced Special operations COALESCE, ISNULL, NULLIF, CASE

Key Points:

1. Aggregate Functions - Multiple rows ko summarize karte hain

2. Scalar Functions - Single row par operations perform karte hain

3. String Functions - Text data ko manipulate karne ke liye

4. Numeric Functions - Mathematical operations ke liye

5. Date Functions - Date and time operations ke liye

6. Conditional Functions - Logic implement karne ke liye