SQL Functions and Expressions
SQL functions and expressions help you work with data in a smart and flexible way. Instead of showing raw data only, they allow you to calculate values, change formats, and apply logic inside queries.
Because of this, SQL becomes more powerful and useful in real world applications.
What Are SQL Functions
SQL functions are built in tools that perform specific tasks on data. They take input, process it, and return a result.
You can use SQL functions to
calculate totals
find averages
modify text
work with numbers
handle dates
manage missing values
As a result, queries become shorter and easier to read.
Types of SQL Functions
SQL functions are mainly grouped into four types.
Aggregate Functions in SQL
Aggregate functions work on multiple rows and return a single value.
Common Aggregate Functions
- COUNT
- SUM
- AVG
- MIN
- MAX
COUNT Example
SELECT COUNT(*)
FROM students;
This query returns the total number of students.
SUM Example
SELECT SUM(marks)
FROM students;
This query calculates the total marks.
AVG Example
SELECT AVG(marks)
FROM students;
This query finds the average marks.
MIN and MAX Example
SELECT MIN(marks), MAX(marks)
FROM students;
This query shows the lowest and highest marks.
String Functions in SQL
String functions work with text data. They help format and clean text values.
Common String Functions
- UPPER
- LOWER
- LENGTH
- CONCAT
UPPER and LOWER Example
SELECT UPPER(name)
FROM students;
This converts names to uppercase.
SELECT LOWER(city)
FROM students;
This converts city names to lowercase.
LENGTH Example
SELECT name, LENGTH(name)
FROM students;
This shows each name with its length.
CONCAT Example
SELECT CONCAT(name, ' lives in ', city) AS details
FROM students;
This joins name and city into one readable sentence.
Numeric Functions in SQL
Numeric functions perform calculations on numbers.
Common Numeric Functions
- ROUND
- ABS
- MOD
ROUND Example
SELECT ROUND(AVG(marks), 2)
FROM students;
This rounds the average marks to two decimal places.
MOD Example
SELECT MOD(marks, 10)
FROM students;
This returns the remainder after dividing marks by ten.
Date Functions in SQL
Date functions help you work with dates and time.
Common Date Functions
- CURRENT_DATE
- NOW
- YEAR
- MONTH
Date Example
SELECT CURRENT_DATE;
This query returns today’s date.
What Are SQL Expressions
An SQL expression is a combination of values, operators, columns, or functions that produces a result.
Expressions can appear in
SELECT
WHERE
ORDER BY
Expression in SELECT Clause
SELECT name, marks + 5 AS updated_marks
FROM students;
This expression adds five marks to each student.
Expression in WHERE Clause
SELECT *
FROM students
WHERE marks * 2 > 150;
This filters students based on a calculated value.
Expressions with Comparison Operators
SELECT *
FROM students
WHERE age + 1 >= 21;
This query checks future age values.
Handling NULL Values with Functions
Missing values can cause problems. SQL provides functions to handle them safely.
IFNULL Example
SELECT name, IFNULL(marks, 0)
FROM students;
This replaces NULL marks with zero.
COALESCE Example
SELECT name, COALESCE(marks, 0)
FROM students;
This works across most databases and does the same job.
Why SQL Functions and Expressions Are Important
They help you
analyze data quickly
reduce manual calculations
clean and format values
write professional queries
Because of this, most real world SQL queries use functions and expressions together.
Summary
SQL functions perform ready made operations.
SQL expressions apply logic and calculations.
Both help you transform data inside queries.
Once you master them, your SQL skills become much stronger.
Check out our resources!
- Bootstrap Templates: Explore our Bootstrap Projects section.
- Free E-Books: Download your Free E-Books here.
