Data Base Queries

Data base • What are Group Functions? • Types of Group Functions • Using Group Functions • Using AVG and SUM function • Using MIN and MAX function • Using COUNT function • Group functions and NULL values • Using the NVL Function with Group Function • Creating GROUP of DATA • Creating GROUP of DATA:GROUP BY Clause • Using the GROUP BY clause • Grouping by more than one column • Using the GROUP BY clause on multiple columns • Illegal Queries Using Group Functions • Excluding Group Results • Excluding Group Results: HAVING Clause • Using the HAVING clause • Nesting GROUP Function • Subqueries • Using a Subquery • Guideline for using subqueries • Types of Subqueries • Single-row subqueries • Executing Single Row Subqueries • Using Group Functions in a Subquery • HAVING clause with subqueries • Multiple Row Subqueries • Using ANY operator in Multiple-row subqueries • Using ALL operator in Multiple-row subqueries
Group Function:

"Group Functions" also known as "Multiple-Row Functions". They operates on set of rows to give one result per group. These set may be the whole table or the table split into groups.

Group Functions: • SUM • AVG • COUNT • MIN • MAX • STDDEV • VARIANCE

(These are similar to the "aggregate functions" or "Group By" functions in Access)

GROUP BY clause

To identify groups of records to be processed

HAVING clause

To restrict the groups displayed

The data types for arguments may be CHAR, VARCHAR2, NUMBER, or DATE.

The Oracle Server implicitly sorts the result set in ascending order when using a GROUP BY clause. To override the default ordering, DESC can be used in an ORDER by clause. Some simple