SQL Functions -Single row functions & Aggregate functions

Functions that take one or more arguments and written a single value. These are classified into two types:
1.Single row functions
2.Aggregate functions

Dual: Dual is a dummy table available to the users in the database which supports data retrieved and formatting 

Example: Display current date.
sql>select sysdate from dual;

1.Single row functions: 
These functions operate on numeric data single row functions act on each row written by the query it results one result for row. These are classified into 

• Number functions
• Character functions
• Date functions
• Conversion functions

Number functions:

1. abs(<n>)
2. ceil(<n>)
3. floor(<n>)
4. cos(<n>)
5. exp(<n>)
6. log(<n1>,<n2>)
7. mod(<n1>,<n2>)
8. power(<n1>,<n2>)
9. sign(<n>)
10. sqrt(<n>)
11. round(<n1>,<n2>)
12. trunc(<n1>,<n2>)

1.ABS(<n>):This function returns an absolute value of n where n is a number

sql>select abs(-21)negative ,abs(23)positive from dual;

2.ceil(<n>):This function returns the smallest integer that is greater then or equal to 'n'. It rounds up to a whole numbers

sql>select ceil(2.41),ceil(-32.75) from dual;

Output: 3    -32

3.floor(<n>):This function returns the largest integer that is less then or equal to 'n'. It rounds up down to a whole numbers

sql>select floor(2.41),floor(-32.75) from dual;

Output: 2    -33

4.cos(<n>):This function returns the trigonometric cosine of the number 'n'.

sql>select cos(0) from dual;

Output: 1

5.exp(<n>):This function returns e raised to the nth power 

sql>select exp(1) from dual;

Output: 2.71828183

6.log(<n1>,<n2>):This function returns the logarithm base 2  

sql>select log(3,27) from dual;

Output: 3

7.mod(<n1>,<n2>):This function returns n2 modulo n1 or the remainder of n2and n1  

sql>select mod(15,7) from dual;

Output: 1 

8.power(<n1>,<n2>):This function returns n1 to the n2th power 

sql>select power(2,3) from dual;

Output: 8

9.sign(<n1>):This function returns

-1 if n is negative

0 if n is zero

1 if n is positive 

sql>select sign(-1.6),sign(0),sign(1.6) from dual;

Output: -1   0   1

10.sqrt(<n>):This function returns the square root of n  

sql>select sqrt(9) from dual;

Output: 3

11.round(<n1><n2>):This function returns n1 rounded to n2 digits of precision to the right of the decimal  

SQL> select round(275.26,1) from dual;

Output: 275.3

SQL> select round(25.67),round(22.1) from dual;

Output: 26    22

12.trunc(<n1><n2>):This function returns n1 truncated  to n2 digits of precision to the right of the decimal  

SQL> select trunc(275.26,1) from dual;

Output: 275.2

SQL> select trunc(25.67),trunc(22.1) from dual;

Output: 25    22

Character functions:

1. ascii(<c>)
2. char(<n>)
3. concat(<c1><c2>)
4. initcap(<c1>)
5. length(<c1>)
6. lower(<c1>)
7. upper(<c1>)
8. lpad(<c1>,<i>,<c2>)
9. rpad(<c1>,<i>,<c2>)
10. ltrim(<c1>,<c2>)
11. rtrim(<c1>,<c2>)
12. substr(<c1>,i,j)
13. replace(<c>,<i>,<j>)
14. translate(<c>,<i>,<j>)

1.ASCII(<c>):This function returns the ASCII equivalent of that character

SQL> select ascii('a') from dual;

Output: 97

2.chr(<n>):This function gives the result as a character corresponding to the value 'n'

SQL> select chr(97) from dual;

Output: a

3.concat(<c1><c2>):This function returns c2 appended to c1 where c1 and c2 are character strings

SQL> select concat('ram','sita') from dual;

Output: ramsita

4.initcap(<c1>):This function returns c1 with the first character of each word in uppercase and remaining character in lowercase 

SQL> select initcap('ramsita') from dual;

Output: Ramsita

5.length(<c1>):This function returns the numeric length of the character c1 

SQL> select length('ram sita') from dual;

Output: 8

6.lower(<c1>):This function returns the lowercase of the character c1 

SQL> select lower('RAMSITA') from dual;

Output: ramsita

7.upper(<c1>):This function returns the uppercase of the character c1 

SQL> select upper('ramsita') from dual;

Output: RAMSITA

8.lpad(<c1>,<i>,<c2>):This function returns the character string c1 expanded in length to i characters using c2 to fill the spaces as need on left side of c1. 

SQL> select lpad('ramsita',4,'*') from dual;

Output: rams

SQL> select lpad('ramsita',10,'*') from dual;

Output: ***ramsita

9.rpad(<c1>,<i>,<c2>):This function returns the character string c1 expanded in length to i characters using c2 to fill the spaces as need on right side of c1. 

SQL> select rpad('ramsita',4,'*') from dual;

Output: rams

SQL> select rpad('ramsita',10,'*') from dual;

Output: ramsita***

10.ltrim(<c1>,<c2>):This function returns c1 without any leading characters that appear in c2 

SQL> select ltrim('hai','h') from dual;

Output: ai

11.rtrim(<c1>,<c2>):This function returns c1 without any trailing characters that appear in c2 

SQL> select rtrim('hai','i') from dual;

Output: ha

12.substr(<c1>,i,j):This function returns the position of c1 which is j character long beginning at position i 

SQL> select substr('ram is a boy',2,5) from dual;

SUBST
-----
am is

13.replace(<c>,<i>,<j>):This function replace single character with multiple characters  

SQL> select replace('ramsita','sita','ya') from dual;

REPLA
-----
ramya

14.translate(<c>,<i>,<j>):This function translates single character with single character only  

SQL> select translate('vijaya','a','w') from dual;

TRANSL
------
vijwyw

SQL> select translate('vijaya','ya','w') from dual;

TRAN
----
vijw

Date functions:

1. sysdate
2. add_month
3. last_day
4. next_day
5. months_between
6. sysdate+1
7. sysdate-1

1.sysdate: This function returns system date 

SQL> select sysdate from dual;

SYSDATE
---------
17-SEP-21

2.add_month:This function is used to add a month to the specified  system date 

SQL> select sysdate , add_months (sysdate,2) from dual;

SYSDATE   ADD_MONTH
--------- ---------
17-SEP-21 17-NOV-21

3.last_day:This function returns the last day of the system date 

SQL> select sysdate , last_day (sysdate) from dual;

SYSDATE   LAST_DAY(
--------- ---------
17-SEP-21 30-SEP-21

SQL> select sysdate , last_day (sysdate)+1 from dual;

SYSDATE   LAST_DAY(
--------- ---------
17-SEP-21 01-OCT-21

4.next_day:This function returns the next day of the system date

SQL> select sysdate , next_day (sysdate,'tue') from dual;

SYSDATE   NEXT_DAY(
--------- ---------
17-SEP-21 21-SEP-21

5.months_between:This function returns the months in between 

SQL> select months_between ('22-mar-2021' , '22-sep-2020') from dual;

MONTHS_BETWEEN('22-MAR-2021','22-SEP-2020')
-------------------------------------------
                                          6

6.sysdate+1:This function adds one day to the system date 

SQL> select sysdate+1 from dual;

SYSDATE+1
---------
18-SEP-21

7.sysdate-1:This function removes one day to the system date 

SQL> select sysdate-1 from dual;

SYSDATE-1
---------
16-SEP-21

conversion function:

to_char: This keyword is used for date conversion functions 

SQL> select to_char(sysdate,'dd') from dual;

TO
--
17

SQL> select to_char(sysdate,'mm') from dual;

TO
--
09

SQL> select to_char(sysdate,'yyyy') from dual;

TO_C
----
2021

SQL> select to_char(sysdate,'mon') from dual;

TO_CHAR(SYSD
------------
sep

SQL> select to_char(sysdate,'month') from dual;

TO_CHAR(SYSDATE,'MONTH')
------------------------------------
september

SQL> select to_char(sysdate,'year') from dual;

TO_CHAR(SYSDATE,'YEAR')
------------------------------------------
twenty twenty-one

SQL> select to_char(sysdate,'hh:mm:ss') from dual;

TO_CHAR(
--------
06:09:24

SQL> select to_char(sysdate,'hh:mm:ssPM') from dual;

TO_CHAR(SY
----------
06:09:50AM

2.Aggregate or Group Functions :

SQL Aggregation function or Group functions is used to perform the calculations on multiple rows of a single column of a table. It returns a single value. It is also used to summarize the data.

Types of SQL Aggregation Function :

These functions return a value based on number of inputs. It does not process null values and does not return null values.

The different functions are:-

1.Count(*)

2.Sum

3.Avg

4.Max

5.Min

SQL> create table sagg(sno number,sname varchar(11),marks number(2),age number(2));
Table created.
SQL> insert into sagg values(1,'raju',90,19);

1 row created.
SQL> insert into sagg values(2,'rani',50,20);
1 row created.
SQL> insert into sagg values(3,'vani',80,19);
1 row created.
SQL> insert into sagg values(4,'ramesh',95,21);
1 row created.
SQL> insert into sagg values(5,'suresh',85,18);
1 row created.
SQL> select * from sagg;
       SNO SNAME            MARKS        AGE
---------- ----------- ---------- ----------
         1 raju                90         19
         2 rani                50         20
         3 vani                80         19
         4 ramesh           95         21
         5 suresh            85         18

1.count (* / [distinct /all] colname]:
count function determines no of tuples (or) no.of attributes values.
If * is passed then the total no.of tuples will be return.

count(*):

Counts the number of all rows of the table including null.
Syntax :

select COUNT(*) from <TABLE_NAME>;

SQL> select count(*) from sagg;

COUNT(*)

----------

         5

COUNT( DISTINCT COLUMN_NAME):

Count number of distinct values in a column.

Syntax :

select COUNT(DISTINCT COLUMN_NAME) from <TABLE_NAME>;

SQL> select count(distinct marks) from sagg;

COUNT(DISTINCTMARKS)

--------------------

                   5

SQL> select count(distinct marks) as samemark from sagg;

SAMEMARK

----------

         5

COUNT( COLUMN_NAME):

Count number of non-null values in column.

Syntax :

select COUNT(COLUMN_NAME) from <TABLE_NAME>;

SQL> select count(sname) from sagg;

COUNT(SNAME)

------------

           5

2.  Sum ([distinct  / all ] Colname):

Sum function is used to calculate the sum of all selected columns.

It works on numeric fields only.

SUM(colname)

Syntax :
select sum(COLUMN_NAME) from <TABLE_NAME>;

SQL> select sum(marks) from sagg;

SUM(MARKS)

----------

       400

SUM([ALL/DISTINCT] colname)
Syntax :

select sum(DISTINCT COLUMN_NAME) from <TABLE_NAME>;

SQL> select sum(distinct age) from sagg;

SUM(DISTINCTAGE)

----------------

              78

SQL> select sum(age) from sagg;

  SUM(AGE)

----------

        97

3.  AVG(colname):
AVG function is used to calculate the average value of the numeric type.
AVG function returns the average of all non-Null values.

AVG(colname)
Syntax :

select avg(COLUMN_NAME) from <TABLE_NAME>;

SQL> select avg(marks) from sagg;
AVG(MARKS)
----------
        80

AVG([ALL|DISTINCT] colname)
Syntax :

select avg(DISTINCT COLUMN_NAME) from <TABLE_NAME>;

SQL> select avg(distinct marks) from sagg group by age;
AVG(DISTINCTMARKS)
------------------
                85
                85
                50
                95

4.  MAX(colname):

MAXfunction  is  used  to  find  the  maximum  value  of a certain column. 

This function determines the largest value of all selected values of a column.

MAX(colname)
Syntax :

select max(COLUMN_NAME) from <TABLE_NAME>; 

SQL> select max(marks) from sagg;

MAX(MARKS)

----------

        95

MAX([ALL/DISTINCT] colname)

Syntax :

select max(DISTINCT COLUMN_NAME) from <TABLE_NAME>; 

5.  MIN(colname):

MIN function is used to find the minimum value of a certain column.

This function determines the smallest value of all selected values of  a column.

MIN(colname)
Syntax :select min(COLUMN_NAME) from <TABLE_NAME>; 

SQL> select min(marks) from sagg;

MIN(MARKS)

----------

        50

MIN([ALL/DISTINCT] colname)
Syntax :select min(DISTINCT COLUMN_NAME) from <TABLE_NAME>; 
Ex:    select min(distinct marks) from std;