SQL Functions: Practical Examples I Actually Use

SQL Functions: Practical Examples That Finally Made Sense

When I started learning SQL functions, I knew what some of them were called, but I wasn't always sure when to use them.

The easiest way for me to understand them was to connect each function to a real problem.

What are SQL functions?

SQL functions are built-in tools that perform a specific operation on data.

For example, instead of manually calculating the average salary of employees, I can ask SQL to do it using AVG().

Here are some functions I find useful.

COUNT()

Let's say a hotel wants to know how many bookings it has received.

SELECT COUNT(*) AS total_bookings
FROM bookings;

This counts all the rows in the bookings table.

The result might be:

560 total_bookings

This is useful when I want to know how many records I have.

SUM()

Now the hotel wants to know how much revenue it generated from all bookings.

SELECT SUM(total_amount) AS total_revenue
FROM bookings;

Instead of adding hundreds of booking amounts manually, SQL calculates the total.

The result might be:

KSh 4,850,000

AVG()

Suppose I'm analysing guest ratings and want to find the average rating.

SELECT AVG(guest_rating) AS average_rating
FROM bookings;

The result could be:

4.2

This gives the hotel a quick idea of its overall guest satisfaction.

MAX()

Now I want to find the most expensive booking in the dataset.

SELECT MAX(total_amount) AS highest_booking
FROM bookings;

The result could be:

KSh 185,000

This can help identify the largest booking or highest-value customer transaction.

MIN()

Let's find the cheapest room rate in a hotel dataset.

SELECT MIN(room_rate_per_night) AS cheapest_room
FROM bookings;

The result could be:

KSh 3,500

This is useful when analysing the hotel's pricing range.

UPPER()

Imagine guest names were entered in different formats:

feddy mwanjumwa

Feddy Mwanjumwa

FEDDY MWANJUMWA

I can standardize them to uppercase:

SELECT UPPER(guest_name) AS guest_name
FROM bookings;

Now the names are displayed consistently.

LOWER()

Suppose I'm working with email addresses and want them all in lowercase.

SELECT LOWER(email) AS email
FROM customers;

This is useful because text entered by different people can have inconsistent capitalization.

TRIM()

Imagine a guest name was accidentally stored as:

' Feddy Mwanjumwa '

I can remove the unnecessary spaces with:

SELECT TRIM(guest_name) AS cleaned_name
FROM bookings;

This is especially useful when cleaning imported or manually entered data.

ROUND()

Suppose I'm calculating revenue and get a value with many decimal places.

SELECT ROUND(SUM(total_amount), 2) AS total_revenue
FROM bookings;

Instead of something like:

4850000.67891

I get:

4850000.68

This is useful when working with money.

COALESCE()

Missing data is very common.

Suppose some guests don't have a phone number:

SELECT
    guest_name,
    COALESCE(guest_phone, 'No phone number') AS phone
FROM bookings;

Instead of displaying NULL, SQL will show:

No phone number

This makes the result much easier to understand.

CONCAT()

Suppose a table stores first and last names separately.

SELECT
    CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

This combines the two columns into one:

Feddy Mwanjumwa

Useful when I need to display or export a complete name.

CASE

CASE isn't technically a function, but it's too useful to leave out.

Suppose I want to classify hotel bookings based on their total value:

SELECT
    booking_id,
    total_amount,
    CASE
        WHEN total_amount >= 100000 THEN 'High Value'
        WHEN total_amount >= 50000 THEN 'Medium Value'
        ELSE 'Low Value'
    END AS booking_category
FROM bookings;

Now each booking gets a category based on its amount.

This becomes very useful when turning raw data into something easier to analyse.

When would I use these functions?

I don't think of SQL functions as things I just need to memorize anymore.

I think about the question first.

How many bookings are there?

COUNT()

How much revenue was generated?

SUM()

What's the average rating?

AVG()

What's the highest booking?

MAX()

Are there unnecessary spaces in my data?

TRIM()

Are some values missing?

COALESCE()

Do I need to create categories?

CASE

That's what made SQL functions much easier for me to understand.