SQL Functions | Learn Computer Academy

Built-in functions transform or summarize values without you writing any procedural logic. This lesson covers the ones you'll reach for constantly.

COUNT()

Counts the number of rows.

SELECT COUNT(*) FROM table_name;

SELECT COUNT(*) FROM Employee;

Counts total rows.
NULL handling depends on whether you pass * or a specific column — COUNT(column) skips NULLs in that column, COUNT(*) doesn't.

Common Mistake

Using COUNT(column) expecting it to count NULLs — it doesn't.

SUM()

Adds up all values in a numeric column.

SELECT SUM(column) FROM table_name;

SELECT SUM(salary) FROM Employee;

Adds every salary value.
Ignores NULL values automatically.

Common Mistake

Applying SUM to a non-numeric column.

AVG()

Returns the mean of a numeric column.

SELECT AVG(column) FROM table_name;

SELECT AVG(salary) FROM Employee;

Returns the average salary.
Ignores NULL values in the calculation.

Common Mistake

Forgetting AVG ignores NULLs, which can skew results versus a manual calculation done elsewhere.

MIN() / MAX()

Returns the smallest or largest value in a column.

SELECT MIN(column), MAX(column) FROM table_name;

SELECT MIN(salary), MAX(salary) FROM Employee;

Works with numbers, dates, and text alike.
Useful for finding ranges at a glance.

Common Mistake

Calling MIN/MAX per row in application code instead of letting the engine aggregate it in one pass.

String Functions

Manipulate text values — casing, joining, trimming, extracting.

UPPER(col) · LOWER(col) · CONCAT(a,b) · SUBSTRING(col,start,len) · TRIM(col) · LENGTH(col)

SELECT UPPER(name), CONCAT(name, ' - ', department)
FROM Employee;

UPPER/LOWER change case; CONCAT joins strings together.
SUBSTRING extracts part of a string; TRIM removes leading/trailing spaces.

Common Mistake

Comparing strings without normalizing case first, missing matches that a human would consider identical.

Date Functions

Work with dates and times — current date, differences, arithmetic.

NOW() · CURDATE() · DATEDIFF(a,b) · DATE_ADD(date, INTERVAL n unit)

SELECT DATEDIFF(NOW(), hire_date) AS days_employed
FROM Employee;

NOW()/CURDATE() return the current timestamp or date.
DATEDIFF and DATE_ADD let you calculate durations and future/past dates.

Common Mistake

Comparing DATE and DATETIME columns directly without matching precision first.

Numeric Functions

Perform math on numeric values directly in a query.

ROUND(col, n) · ABS(col) · MOD(a, b)

SELECT ROUND(salary / 12, 2) AS monthly_pay
FROM Employee;

ROUND controls decimal precision; ABS strips a negative sign.
MOD returns the remainder of division — handy for pagination or cycling values.

Common Mistake

Rounding in application code instead of the query, causing display mismatches between the two.

Functions handle the everyday math and text work. The next lesson covers window functions — a related but more powerful tool for calculating across a set of rows without collapsing them into one.

Built-in functions transform or summarize values without you writing any procedural logic. This lesson covers the ones you'll reach for constantly.

COUNT()

Counts the number of rows.

SELECT COUNT(*) FROM table_name;

SELECT COUNT(*) FROM Employee;

Counts total rows.
NULL handling depends on whether you pass * or a specific column — COUNT(column) skips NULLs in that column, COUNT(*) doesn't.

Common Mistake

Using COUNT(column) expecting it to count NULLs — it doesn't.

SUM()

Adds up all values in a numeric column.

SELECT SUM(column) FROM table_name;

SELECT SUM(salary) FROM Employee;

Adds every salary value.
Ignores NULL values automatically.

Common Mistake

Applying SUM to a non-numeric column.

AVG()

Returns the mean of a numeric column.

SELECT AVG(column) FROM table_name;

SELECT AVG(salary) FROM Employee;

Returns the average salary.
Ignores NULL values in the calculation.

Common Mistake

Forgetting AVG ignores NULLs, which can skew results versus a manual calculation done elsewhere.

MIN() / MAX()

Returns the smallest or largest value in a column.

SELECT MIN(column), MAX(column) FROM table_name;

SELECT MIN(salary), MAX(salary) FROM Employee;

Works with numbers, dates, and text alike.
Useful for finding ranges at a glance.

Common Mistake

Calling MIN/MAX per row in application code instead of letting the engine aggregate it in one pass.

String Functions

Manipulate text values — casing, joining, trimming, extracting.

UPPER(col) · LOWER(col) · CONCAT(a,b) · SUBSTRING(col,start,len) · TRIM(col) · LENGTH(col)

SELECT UPPER(name), CONCAT(name, ' - ', department)
FROM Employee;

UPPER/LOWER change case; CONCAT joins strings together.
SUBSTRING extracts part of a string; TRIM removes leading/trailing spaces.

Common Mistake

Comparing strings without normalizing case first, missing matches that a human would consider identical.

Date Functions

Work with dates and times — current date, differences, arithmetic.

NOW() · CURDATE() · DATEDIFF(a,b) · DATE_ADD(date, INTERVAL n unit)

SELECT DATEDIFF(NOW(), hire_date) AS days_employed
FROM Employee;

NOW()/CURDATE() return the current timestamp or date.
DATEDIFF and DATE_ADD let you calculate durations and future/past dates.

Common Mistake

Comparing DATE and DATETIME columns directly without matching precision first.

Numeric Functions

Perform math on numeric values directly in a query.

ROUND(col, n) · ABS(col) · MOD(a, b)

SELECT ROUND(salary / 12, 2) AS monthly_pay
FROM Employee;

ROUND controls decimal precision; ABS strips a negative sign.
MOD returns the remainder of division — handy for pagination or cycling values.

Common Mistake

Rounding in application code instead of the query, causing display mismatches between the two.

Functions handle the everyday math and text work. The next lesson covers window functions — a related but more powerful tool for calculating across a set of rows without collapsing them into one.

Functions — Aggregate, String, Date, Numeric

COUNT()

SUM()

AVG()

MIN() / MAX()

String Functions

Date Functions

Numeric Functions

Functions — Aggregate, String, Date, Numeric

COUNT()

SUM()

AVG()

MIN() / MAX()

String Functions

Date Functions

Numeric Functions