Execute the following Multiple row functions (Aggregate Functions) on Relation • Group functions(AVG, COUNT, MAX, MIN, SUM) • DISTINCT Keyword in Count Function • Null Values in Group Functions • NVL Function with Group Functions.

 EXERCISE : 5

AIM : Execute the following Multiple row functions (Aggregate Functions) on
Relation                                                                                                                      

• Group functions(AVG, COUNT, MAX, MIN, SUM)
• DISTINCT Keyword in Count Function
• Null Values in Group Functions
• NVL Function with Group Functions.

Description :

AGGREGATE FUNCTIONS

            In data base management system ,an aggregate function is a function where the values of multiple rows are  grouped together as input on certain criteria to form a single value of more significant meaning.

The aggregate functions are:

1) MAX():   It returns the max value in the given column.

2) MIN():    It returns the max value in the given column.

3) SUM():    It returns the sum of all numeric  values in the given column.

4) AVG():   It returns the average of all  values in the given column.

5) COUNT():It returns the total number of all  values in the given column(excluding null values).

6) COUNT(*):It returns the  number of all  rows in the given table(including null values).

Group Functions(AVG , COUNT , MAX , MIN , SUM) :

Example :

         SELECT * FROM sailors;

AVERAGE (AVG):

    Example :

select avg(s.age) from sailors s;

  select avg(s.age) from sailors s where s.rating=10;

MAXIMUN (MAX):

Example:

           select max(s.age) from sailors s;

MINIMUM (MIN):

Example:

            select min(s.age) from sailors s;

SUM:

     Example:

            select sum(distinct s.rating) from sailors s;

COUNT: 

     Example:

            select count(*) from sailors;

 

 

 

DISTINCT keyword in count function :

EXAMPLE :

select count(Distinct sname) from sailors;

Null values in group functions :

EXAMPLE :

Create table table1 (id int, col int);

          Select * from table1;

select count(*) from table1;

select count(id) from table1;

NVL function with group functions :

EXAMPLE :

create table gg(fname varchar2(20),lname varchar2(20),country varchar2(10));

SELECT * FROM GG;

select fname,NVL(fname,'noname') from gg;

  select lname,NVL(lname,'empty') from gg;

select country,NVL(country,'no country') from gg;

select country,NVL(country,'0') from gg;

select * from gg;

select lname,NVL(lname,fname) from gg;