Functions in Oracle SQL

The database functions search a specified database for records of the particular condition. When the condition is true, some functions are performed in a particular column of that database.

There are has many functions to operate the Oracle SQL. They are categorized into two categories and further sub-categorized as follows:

  • Aggregate functions
    • COUNT()
    • AVG()
    • SUM()
    • MIN()
    • MAX()
  • Scalar functions
    • String functions
    • Numeric functions
    • Date functions
    • Conversion function

AGGREGATE functions in Oracle SQL

The function where all the multiple rows are grouped together to form a single value is known as Aggregate functions. Aggregate functions also deal with GROUP BY and HAVING functions.

We commonly use the aggregate functions with the GROUP BY statement. If you use aggregate functions without a GROUP BY statement, then the aggregate functions apply to all rows of the tables.

We also use the aggregate functions in the HAVING statement to filter groups based on the retrieved data of the aggregate functions.

Now let us understand some aggregate functions in SQL. Some of the commonly used aggregate functions in the oracle SQL are as follows,

  • COUNT()
  • AVG()
  • SUM()
  • MIN()
  • MAX()

COUNT() function in Oracle SQL:

This function is used to count the number of records that match the specified condition.

Syntax for the COUNT() function :

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

Example for the COUNT() function :

SELECT COUNT(city)
FROM Persons_table;

data-of-table
In the above query, all the details are retrieved from the Persons_table.
aggregate-func-sql
In the above image, the number of times Bangalore present in the city column is displayed. And is applied to the all rows of the table.

COUNT() function with GROUP BY and HAVING functions:

Example:

SELECT COUNT(city), Gender
FROM Persons_table
GROUP BY gender
HAVING gender ='m';

aggregate-func-sql-group-having
In the above query, the GROUP BY and the HAVING functions are used. and grouped by the gender which belongs to 'm'(males).

AVG()

function in Oracle SQL:

This function is used to return the average value of the numeric column.

Syntax for AVG() function :

SELECT AVG(column_name)
FROM table_name
WHERE condition;

Example for AVG() function :

SELECT AVG(age)
FROM persons_table
WHERE gender='f';

data-of-table
In the above query, all the details are retrieved from the Persons_table.
avg-func
In the above image, the average of the age column is calculated where the gender value is 'f' (female).

SUM()

function in Oracle SQL:

This function is used to return the total sum of the numeric column.

Syntax of SUM() function :

SELECT SUM(column_name)
FROM table_name
WHERE condition;

Example of SUM() function :

SELECT SUM(age)
FROM persons_table
WHERE gender='f';

data-of-table
In the above query, all the details are retrieved from the Persons_table.
sum()-function
In the above query, the age of the two females is added.

MIN()

function in Oracle SQL:

This function retrieves the smallest value of the selected column.

Syntax of the MIN() function:

SELECT MIN(column_name)
FROM table_name;

Example of MIN() function to retrieve the smallest age of a person in the given table.

SELECT MIN(age)
FROM Persons_table;

data-of-table
In the above query, all the details are retrieved from the Persons_table.
min-function
In the above image, the smallest value in the age column is shown as output.

MAX()

function in Oracle SQL:

This function retrieves the highest value of the selected column.

Syntax of MAX() function :

SELECT MAX(column_name)
FROM table_name;

Example of MAX() function to retrieve the highest age of a person in the given table.

SELECT MAX(age)
FROM Persons_table;

data-of-table
In the above query, all the details are retrieved from the Persons_table.

max-function
In the above image, the highest value in the age column is shown as output.

In the next article, let us study the SCALAR FUNCTIONS.

0 results
Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions