Mastering SQL Functions: A Beginner’s Guide to Cleaner Code

Introduction

SQL functions are built-in operations that perform calculations, manipulate data, and return results in queries, making data handling simpler without writing complex code.

Common SQL Functions

1. Aggregate Functions
Aggregate functions operate on a set of values and return a single result. They are typically used in conjunction with GROUP BY to aggregate data by groups.

• COUNT() Counts the number of rows or non-NULL values in a column.

SELECT COUNT(*) FROM orders;

• SUM()

Calculates the total sum of a numeric column.

SELECT SUM(order_total) FROM orders WHERE order_date >= '2026-09-01';

• AVG()

Calculates the average value of a numeric column.

SELECT AVG(order_total) FROM orders;

• MIN() and MAX()

MIN() returns the smallest value, and MAX() returns the largest value in a column.

SELECT MIN(order_total) AS cheapest, MAX(order_total) AS priciest
FROM orders;

2. String Functions

String functions are used to manipulate and process text data.

• CONCAT()

Concatenates two or more strings into one.

select first_name,last_name,
concat(first_name,' ',last_name) as full_name
from students;

• SUBSTRING()

Extracts a substring from a string starting at a specified position.

select first_name,
substring(first_name,1,3)
from students;

• UPPER() and LOWER()

Converts a string to uppercase or lowercase.

-- syntax UPPER(text)

select first_name,
upper(first_name) as upper_fisrt_name
from students;

-- syntax LOWER(text)

select last_name,
lower(last_name) as lower_last_name
from students;

• LENGTH()

Returns the length of a string.

select subject_name,
length(subject_name)as length_subject
from subjects
order by length(subject_name) asc;

• REPLACE()

Replace text that matches a regular expression pattern.

-- Remove all non-numeric characters from phone numbers
SELECT phone_number,
       REGEXP_REPLACE(phone_number, '[^0-9]', '') AS clean_number
FROM contacts;

3. Date and Time Functions

Date and time functions are used to manipulate and calculate dates and times.

• CURRENT_TIMESTAMP() and NOW()

Returns the current date or time.

-- CURRENT_TIMESTAMP(), NOW() - current day and time 
select now();
select current_timestamp;

• EXTRACT(), DATE_PART()

Pulls a single field (year,month,etc) out of a date

select 
    first_name,
    extract(year from date_of_birth) as birth_year,
    extract(month from date_of_birth) as birth_month
from greenwood_academy.students;

3. Numeric Functions

Numeric functions perform operations on numeric data.

• ROUND()

Rounds a numeric value to a specified number of decimal places.

select round(45.456,2);

• CEIL() and FLOOR()

CEIL() always round up to the next whole number/integer while FLOOR() rounds down up to the next whole number/integer

select 
    result_id,
    marks,
    round(marks/10.0,1)as round_out_of_ten,
    ceil(marks/10.0) as ceil_out_of_ten,
    floor(marks/10.0)as floor_out_of_ten
from greenwood_academy.exam_results er;

• ABS()

Returns the absolute value of a number.

SELECT ABS(column_name) AS AbsoluteValue FROM table_name;

Conclusion

SQL functions aren't complicated once you see them as small, single-purpose helpers. Aggregate functions turn many rows into one number. String functions clean up and shape text. Date functions make "when" usable. Window functions let you rank and compare rows without losing the details.