SQL Functions (Aggregate and Scalar Functions)

SQL Functions are built-in programs that are used to perform different operations on the database.

There are two types of functions in SQL:

• Aggregate Functions
• Scalar Functions

SQL Aggregate Functions

SQL Aggregate Functions operate on a data group and return a singular output. They are mostly used with the GROUP BY clause to summarize data. 

Some common Aggregate functions with Syntax and description are shown in the table below.

SQL Scalar functions

SQL Scalar Functions are built-in functions that operate on a single value and return a single value.

Scalar functions in SQL helps in efficient data manipulation and simplification of complex calculations in SQL queries.

SQL Functions Examples

Let's look at some examples of SQL Functions. We will cover examples of SQL aggregate functions and scalar functions.

We will perform queries on the given SQL table:

Aggregate Functions Examples

Let's look at the examples of each aggregate function in SQL.

AVG() Function Example

Computing average marks of students. 

Query:

SELECT AVG(MARKS) AS AvgMarks FROM Students; 

Output: 

COUNT() Function Example

Computing total number of students. 

Query: 

SELECT COUNT(*) AS NumStudents FROM Students;

Output: 

FIRST() Function Example

Fetching marks of first student from the Students table. 

Query:

SELECT FIRST(MARKS) AS MarksFirst FROM Students;

Output: 

LAST() Function Example

Fetching marks of last student from the Students table. 

Query:

SELECT LAST(MARKS) AS MarksLast FROM Students;

Output: 

MAX() Function Example

Fetching maximum marks among students from the Students table. 

Query:

SELECT MAX(MARKS) AS MaxMarks FROM Students;

Output: 

MIN() Function Example

Fetching minimum marks among students from the Students table. 

Query:

SELECT MIN(MARKS) AS MinMarks FROM Students;

Output: 

SUM() Function Example

Fetching summation of total marks among students from the Students table.

Query: 

SELECT SUM(MARKS) AS TotalMarks FROM Students;

Output: 

Scalar Functions Examples

Let's look at some examples of each Scalar Function in SQL.

UCASE() Function Example

Converting names of students from the table Students to uppercase. 

Query:

SELECT UCASE(NAME) FROM Students;

Output: 

LCASE() Function Example 

Converting names of students from the table Students to lowercase. 

Query:

SELECT LCASE(NAME) FROM Students;

Output: 

MID() Function Example

Fetching first four characters of names of students from the Students table. 

Query:

SELECT MID(NAME,1,4) FROM Students; 

Output: 

LEN() Function Example 

Fetching length of names of students from Students table. 

Query:

SELECT LENGTH(NAME) FROM Students;

Output: 

ROUND() Function Example 

Fetching maximum marks among students from the Students table. 

Query:

SELECT ROUND(MARKS,0) FROM Students; 

Output: 

NOW() Function Example 

Fetching current system time. 

Query:

SELECT NAME, NOW() AS DateTime FROM Students; 

Output: 

FORMAT() Function Example 

Formatting current date as 'YYYY-MM-DD'. 

Query:

SELECT NAME, FORMAT(Now(),'YYYY-MM-DD') AS Date FROM Students; 

Output: 

 Important Points About SQL Functions

• SQL functions are built-in programs that are used to manipulate data in various ways.
• There are different types of SQL functions - Aggregate functions and Scalar functions.
• Aggregate functions perform calculations on a group of values and return a single value. Example SUM, AVG, COUNT.
• Scalar functions operate on a single value and return a single value. Example UPPER, LOWER, SUBSTRING.
• SQL functions can be used in different SQL statements, such as SELECT, WHERE, GROUP BY, and ORDER BY, to improve data processing and analysis.