5 SQL FUNCTIONS EVERY BEGINNER SHOULD KNOW

"Data is the new oil — and SQL is the refinery."

— Anonymous Data Engineer

---

Image: A developer working with databases — the everyday environment of an SQL practitioner.

---

Table of Contents

1. Introduction
2. What Is SQL?
3. Function 1 — COUNT()
4. Function 2 — SUM()
5. Function 3 — AVG()
6. Function 4 — UPPER() and LOWER()
7. Function 5 — CONCAT()
8. Combining Functions Together
9. Best Practices
10. Conclusion

---

Introduction

If you have ever opened a spreadsheet and thought, "There has to be a better way to manage this data," — you were right. That better way is SQL (Structured Query Language), the industry-standard language for talking to databases.

Whether you are a data analyst, a backend developer, a business intelligence professional, or simply a curious learner, SQL is one of the most valuable skills you can add to your toolkit. It is estimated that over 75% of companies rely on relational databases for their core operations, and every single one of them uses SQL in some form.

However, learning SQL can feel overwhelming at first. There are dozens of functions, keywords, clauses, and syntax rules to absorb. The good news? You do not need to know everything to be effective. In fact, mastering just five core SQL functions will empower you to answer real business questions, clean messy data, and produce meaningful reports.

In this article, we will explore those five essential SQL functions in depth — with clear syntax breakdowns, real-world examples, practical use cases, and tips to help you avoid common beginner mistakes.

Let us get started.

---

What Is SQL?

Before diving into the functions, it helps to have a brief refresher on what SQL actually is and how it operates.

SQL stands for Structured Query Language. It is a domain-specific language designed for managing and manipulating data stored in relational database management systems (RDBMS) such as:

• MySQL
• PostgreSQL
• Microsoft SQL Server
• SQLite
• Oracle Database

Image: A visual representation of how relational databases organize data into tables with rows and columns.

SQL operates on a simple principle: data is organized into tables (similar to spreadsheets), where each table has columns (attributes/fields) and rows (records/entries). You write queries to select, filter, sort, group, insert, update, or delete data from these tables.

SQL commands are generally categorized into:

Category	Full Name	Examples
DQL	Data Query Language	SELECT
DML	Data Manipulation Language	INSERT, UPDATE, DELETE
DDL	Data Definition Language	CREATE, ALTER, DROP
DCL	Data Control Language	GRANT, REVOKE

For this article, we will focus on SQL functions, which are built-in operations that process and return values from your data. Specifically, we will cover the five most beginner-friendly and most commonly used SQL functions.

---

Function 1 — COUNT()

What It Does

The COUNT() function returns the number of rows that match a specified condition. It is one of the most frequently used SQL functions and is essential for generating summary statistics and reports.

Syntax

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

• Use COUNT(*) to count all rows, including those with NULL values.
• Use COUNT(column_name) to count rows where that column is not NULL.

---

Real-World Example

Imagine you work at an e-commerce company and you have an orders table like this:

order_id	customer_name	product	status	amount
1	Alice Njeri	Laptop	Completed	85000
2	Brian Otieno	Headphones	Pending	4500
3	Carol Wanjiku	Phone	Completed	35000
4	David Kamau	Keyboard	Cancelled	2800
5	Eve Achieng	Monitor	Completed	22000

Question: How many orders are in the table?

SELECT COUNT(*) AS total_orders
FROM orders;

Result:

total_orders
5

Question: How many orders have the status 'Completed'?

SELECT COUNT(*) AS completed_orders
FROM orders
WHERE status = 'Completed';

Result:

completed_orders
3

---

Use Cases for COUNT()

• Counting total customers in a database
• Counting how many products are out of stock
• Counting the number of users who signed up this month
• Counting failed transactions in a payment system

Common Beginner Mistake

Beginners often confuse COUNT(*) with COUNT(column_name). Remember:

-- Counts ALL rows (even if some columns are NULL)
SELECT COUNT(*) FROM employees;

-- Counts only rows where 'email' is NOT NULL
SELECT COUNT(email) FROM employees;

---

Function 2 — SUM()

What It Does

The SUM() function calculates the total sum of a numeric column. It is indispensable for financial reporting, sales tracking, inventory management, and any scenario where you need an aggregate total.

Syntax

SELECT SUM(column_name)
FROM table_name
WHERE condition;

---

Real-World Example

Using the same orders table from above:

Question: What is the total revenue from all completed orders?

SELECT SUM(amount) AS total_revenue
FROM orders
WHERE status = 'Completed';

Result:

total_revenue
142000

This tells you that completed orders have brought in a total of KES 142,000.

You can also use SUM() with GROUP BY to break totals down by category:

SELECT status, SUM(amount) AS revenue_by_status
FROM orders
GROUP BY status;

Result:

status	revenue_by_status
Completed	142000
Pending	4500
Cancelled	2800

---

Image: Data analysis and financial summaries are classic use cases for the SUM() function.

Use Cases for SUM()

• Total sales revenue for a period
• Total inventory value in a warehouse
• Total hours logged by employees in a week
• Total amount of discounts applied to orders

Common Beginner Mistake

SUM() only works with numeric columns. Attempting it on a text column will throw an error. Also, SUM() ignores NULL values automatically — which is usually the desired behavior, but good to know!

-- This will FAIL if 'product' is a text column
SELECT SUM(product) FROM orders; --  Error

-- This is CORRECT
SELECT SUM(amount) FROM orders; --  Works

---

Function 3 — AVG()

What It Does

The AVG() function calculates the arithmetic mean (average) of a set of numeric values. Rather than knowing the total, AVG() tells you the typical or central value — which is often more meaningful for decision-making.

Syntax

SELECT AVG(column_name)
FROM table_name
WHERE condition;

---

Real-World Example

Question: What is the average order amount across all orders?

SELECT AVG(amount) AS average_order_value
FROM orders;

Result:

average_order_value
29860.00

This tells the business that, on average, each order is worth approximately KES 29,860 — a useful figure for forecasting and setting minimum order targets.

Combining with GROUP BY:

SELECT status, AVG(amount) AS avg_amount_by_status
FROM orders
GROUP BY status;

Result:

status	avg_amount_by_status
Completed	47333.33
Pending	4500.00
Cancelled	2800.00

---

Use Cases for AVG()

• Average salary across a department
• Average student score in an exam
• Average delivery time for shipments
• Average customer rating for a product
• Average page views per day on a website

Common Beginner Mistake

Like SUM(), AVG() ignores NULL values. This means if some rows in your column are NULL, the average is calculated only from the non-null rows — which may or may not be what you want.

-- If 5 rows exist but only 3 have values, AVG uses 3 rows
SELECT AVG(rating) FROM products; -- Only non-NULL ratings are averaged

If you need to include NULL as zero in your average, use COALESCE():

SELECT AVG(COALESCE(rating, 0)) FROM products;

---

Function 4 — UPPER() and LOWER()

What They Do

These are string functions that convert text to either all uppercase (UPPER()) or all lowercase (LOWER()). While they seem simple, they are critical for data cleaning, standardization, and consistent formatting across databases.

Syntax

-- Convert to uppercase
SELECT UPPER(column_name) FROM table_name;

-- Convert to lowercase
SELECT LOWER(column_name) FROM table_name;

---

Real-World Example

Consider a customers table where email addresses and names have been entered inconsistently:

customer_id	name	email
1	alice njeri	[e-mail]
2	BRIAN OTIENO	[e-mail]
3	Carol Wanjiku	[e-mail]

Problem: The data is inconsistent — some names are lowercase, some uppercase, and emails are mixed.

Solution: Use LOWER() to standardize emails and UPPER() to format names properly.

-- Standardize all emails to lowercase
SELECT customer_id,
       UPPER(name) AS formatted_name,
       LOWER(email) AS standardized_email
FROM customers;

Result:

customer_id	formatted_name	standardized_email
1	ALICE NJERI	[e-mail]
2	BRIAN OTIENO	[e-mail]
3	CAROL WANJIKU	[e-mail]

You can also use LOWER() in a WHERE clause for case-insensitive searching:

-- Find customer regardless of how their email was entered
SELECT * FROM customers
WHERE LOWER(email) = '[e-mail]';

This ensures your search works whether the email is stored as [e-mail], [e-mail], or [e-mail].

---

Image: Data cleaning and standardization — exactly what UPPER() and LOWER() help accomplish.

Use Cases for UPPER() and LOWER()

• Standardizing email addresses before sending bulk emails
• Normalizing user input for case-insensitive login authentication
• Formatting customer names consistently for reports
• Ensuring uniform data before comparing or joining tables
• Cleaning imported CSV data that has inconsistent casing

Common Beginner Mistake

These functions do not modify the actual data in the database. They only transform the output in your query result. To permanently change the data, you need an UPDATE statement:

-- This only changes the display output, NOT the stored data:
SELECT LOWER(email) FROM customers; --  Display only

-- This permanently updates the stored data:
UPDATE customers
SET email = LOWER(email); --  Permanent change

---

Function 5 — CONCAT()

What It Does

The CONCAT() function joins two or more strings together into a single string. It is incredibly useful for combining columns, formatting output, building dynamic messages, and creating full names or addresses from separate fields.

Syntax

-- Standard CONCAT()
SELECT CONCAT(string1, string2, string3, ...) FROM table_name;

-- Alternative using || operator (works in PostgreSQL and SQLite)
SELECT string1 || string2 FROM table_name;

---

Real-World Example

Suppose your employees table stores first and last names in separate columns:

emp_id	first_name	last_name	department	city
1	Alice	Njeri	Engineering	Nairobi
2	Brian	Otieno	Marketing	Mombasa
3	Carol	Wanjiku	Finance	Nairobi

Question: Generate a full name and a formatted label for each employee.

SELECT
    emp_id,
    CONCAT(first_name, ' ', last_name) AS full_name,
    CONCAT(first_name, ' works in ', department, ' — ', city) AS employee_summary
FROM employees;

Result:

emp_id	full_name	employee_summary
1	Alice Njeri	Alice works in Engineering — Nairobi
2	Brian Otieno	Brian works in Marketing — Mombasa
3	Carol Wanjiku	Carol works in Finance — Nairobi

You can also combine CONCAT() with UPPER() for even more powerful string formatting:

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

Result:

full_name_caps
ALICE NJERI
BRIAN OTIENO
CAROL WANJIKU

---

Use Cases for CONCAT()

• Building full names from first_name and last_name columns
• Creating a full address from street, city, and country fields
• Generating personalized email greetings like "Dear Alice Njeri,"
• Combining a product code with a category code to form a SKU
• Building dynamic SQL strings within stored procedures

Common Beginner Mistake

CONCAT() treats NULL values differently depending on the database:

-- In MySQL, CONCAT() returns NULL if ANY argument is NULL:
SELECT CONCAT('Hello', NULL, 'World'); -- Returns: NULL 

-- Use COALESCE() or CONCAT_WS() to handle NULLs safely:
SELECT CONCAT('Hello', COALESCE(NULL, ''), 'World'); -- Returns: HelloWorld 

-- CONCAT_WS (Concatenate With Separator) skips NULLs automatically:
SELECT CONCAT_WS(' ', first_name, middle_name, last_name) FROM employees; -- 

---

Combining Functions Together

The true power of SQL functions emerges when you combine them in a single query. Here is an example that uses COUNT(), SUM(), AVG(), and CONCAT() together in one query to produce a rich report:

SELECT
    department,
    COUNT(*) AS total_employees,
    SUM(salary) AS total_salary_budget,
    ROUND(AVG(salary), 2) AS average_salary,
    CONCAT('Dept: ', UPPER(department)) AS department_label
FROM employees
GROUP BY department
ORDER BY total_salary_budget DESC;

This single query tells you — for each department:

• How many employees exist
• The total salary budget
• The average salary
• A nicely formatted department label

This is the kind of query you would write to build a management dashboard or a financial summary report.

---

Best Practices

1. Always Use Aliases (AS)

Use the AS keyword to give your function outputs meaningful names. It makes your results readable and your queries professional.

-- Hard to read:
SELECT COUNT(*), SUM(amount), AVG(amount) FROM orders;

-- Much better:
SELECT 
    COUNT(*) AS total_orders,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order_value
FROM orders;

2. Handle NULL Values Proactively

Use COALESCE() or IFNULL() to deal with NULL values before passing them to aggregate functions, especially when calculating averages or concatenating strings.

3. Use GROUP BY with Aggregate Functions

When using COUNT(), SUM(), or AVG() alongside non-aggregate columns, always pair them with a GROUP BY clause.

--  This will cause an error in most databases:
SELECT department, COUNT(*) FROM employees;

--  This is correct:
SELECT department, COUNT(*) FROM employees GROUP BY department;

4. Test on Small Datasets First

Before running aggregate queries on millions of rows, test on a limited subset using LIMIT:

SELECT * FROM orders LIMIT 10;

5. Comment Your Queries

Use -- for single-line comments and /* */ for multi-line comments to document your queries:

-- Get a revenue summary by order status
SELECT 
    status,
    COUNT(*) AS order_count,     -- Number of orders per status
    SUM(amount) AS total_amount  -- Total value per status
FROM orders
GROUP BY status;

---

Conclusion

SQL does not have to be intimidating. By mastering just five core functions — COUNT(), SUM(), AVG(), UPPER()/LOWER(), and CONCAT() — you already have the tools to:

• Summarize and analyze data
• Clean and standardize messy datasets
• Build meaningful reports and dashboards
• Answer real business questions with confidence