In this class you will learn to use the GROUP BY clause as well as learn about the different functions nd some stuff about nested an

In this class you will learn to use the GROUP BY clause as well as learn about the different functions nd some stuff about nested and corelated queries.

Let us first start of with GROUP BY clause:

The whole table when considered alone can be thought of as a group of rows. Sometimes it so happens that we can identify sub groups in the whole group. For example consider, the students table. In that there may be several students with age 18 and several more with age 20 etc. So the students table as a whole is one big group and it contains several sub groups of students with different age groups. You can seperate out the goups using the GROUP BY command in SQL. For example if you want to know the average marks of all students with age 18 you can write the query as

>SELECT age, AVG(marks)

FROM students

GROUP BY age

HAVING age=18;

There are several things new in this query. Let us see each one in detail.

First is the use of AVG( ). this is a new function. There are many functions like these and these are known as group functions a they work on a group. AVG( ) calculates the average of the group specified by the group by clause. If no group is specified, the it is applied to the whole table. For a list of other aggregate or group functions please look at your manual.

Next you see the GROUP BY clause which specifies the columns on which we need to group. Once we use a GROUP BY clause in the query then we get one row per group as answer. So if there are 5 different age groups in students then we get 5 rows as the result..

Finally we have the HAVING clause which applies a condition on the whole group rather than on a row as the WHERE clause does.

So, the above query is executed as follows:

First all the rows are grouped together into groups such that every row is part of some group.

Then the group with age=18 is selected

And finally the average age of that group is calculated.

Run the GROUP BY clause in several queries and understand how it works.

Note: The above query could have been written without GROUP BY clause as follows:

>SELECT age,avg(marks)

FROM students

WHERE age=18;

But if the query was "Find the average marks of different age groups in students", you will have to use GROUP BY clause.

Help for exercise 3.5:

DECODE Function

The decode function executes just like the case structure in any programming language. The syntax of the decode function is as follows:

Syntax:

Decode(expression, case1, value_to_returned, case2, value_to_returned, case3 , value_to_returned, ..,default value)

where,

expression is a valid expression which may be evaluated to any integer value specified in the abive list i.e. (case1,case2,case3, ...) and the action against each value can be specified.

for example, to find the employees category if the categories are specified as follows:

i) Low income group : salary less than 5000

ii) high income group : salary greater than or equal to 5000

subtract 5000 from the salary , we will get a value. Depending on the value after subtraction we have to devide the list of employees in to 2 groups. we can use the sign function to get three possible values depending on the resultant after sub traction.

The employee will be :

1) In category 1, i.e, Low income group, if sign is negative, .

2) in category 2, i.e, High income group, if sign is positive or equal to zero.

The query is as follows:

select decode(sign(sal-5000),-1,'Low income group', 'High income group') from emp;

select empno,ename,sal,

decode(sign(sal-3000),-1,'Low income group', 'High income group') as "Income group" from emp;