SQL Functions Explained

SQL isn’t just about retrieving data - it also lets you transform, calculate, and analyze data using functions. Functions are built-in operations that take input, perform a task, and return a result.

Exploring SQL functions using a sample university database with students, courses, and enrollments.

Sample University Dataset

students

student_id	name	age	major
1	John Mwangi	20	Computer Sci
2	Sarah Achieng	22	Business
3	David Otieno	19	Engineering
4	Mary Njeri	21	Computer Sci

courses

course_id	course_name	credits
101	Database Systems	3
102	Accounting Basics	4
103	Physics I	3
104	Web Development	2

enrollments

enrollment_id	student_id	course_id	grade	enroll_date
1	1	101	85	2026-01-10
2	2	102	90	2026-01-12
3	3	103	70	2026-01-15
4	4	104	88	2026-01-18

i. Aggregate Functions

Aggregate functions summarize data.

Example: Count how many students are enrolled.

SELECT COUNT(*) AS total_students
FROM students;

Output:

total_students
4

COUNT() returns the number of rows

Example: Find the average grade across all enrollments.

SELECT AVG(grade) AS avg_grade
FROM enrollments;

Output:

avg_grade
83.25

AVG() calculates the mean value.

ii. String Functions

String functions manipulate text.

Example: Convert student names to uppercase.

SELECT UPPER(name) AS upper_name
FROM students;

Output:

upper_name
JOHN MWANGI
SARAH ACHIENG
DAVID OTIENO

UPPER() transforms text to uppercase.

Example: Get the first 3 letters of each course.

SELECT SUBSTRING(course_name, 1, 3) AS short_code
FROM courses;

Output:

short_code
Dat
Acc
Phy

SUBSTRING() extracts part of a string.

iii. Numeric Functions

Numeric functions perform calculations.

Example: Round grades to the nearest 10.

SELECT student_id, ROUND(grade, -1) AS rounded_grade
FROM enrollments;

Output:

student_id	rounded_grade
1	90
2	90
3	70
4	90

ROUND() adjusts numeric precision.

iv. Date/Time Functions

Date functions help analyze time-based data.

Example: Extract the month from enrollment dates.

SELECT enrollment_id, EXTRACT(MONTH FROM enroll_date) AS enroll_month
FROM enrollments;

Output:

enrollment_id	enroll_month
1	1
2	1
3	1
4	1

EXTRACT() pulls out parts of a date.

General Notes on SQL Functions
Aggregate functions → summarize data (COUNT, SUM, AVG, MIN, MAX)

String functions → manipulate text (UPPER, LOWER, SUBSTRING, LENGTH)

Numeric functions → perform math (ROUND, CEIL, FLOOR)

Date functions → handle time (NOW, EXTRACT, DATE_PART)