Module3 - Info Management

oakyu

Group Functions

Operate on sets of rows to give one result per group

AVG and SUM

Used for numeric datas

AVG

COUNT

MAX

MIN

STDDEV

SUM

VARIANCE

Types of group functions

MIN and MAX

Use for numeric, char, and date data types

Count (*)

This return the number of rows in a table

COUNT (EXPR)

Returns the number of rows w non values fo

COOUT (DISTINCT expr)

Returns the number of distinct non-values of the expression

Group functions

This ignores null values in a column

NVL Function

This forces group functions to include null values

GROUP BY

Used to divide rows in a table into smaller groups

True

True or False:

All columns in the SELECT list that are not in a group functions must be in the GROUP BY clause.

SELECT

All columns in the ____ list that are not in a group functions must be in the GROUP BY clause.

TRUE

True or False:

The GROUP BY column does not have to be in the SELECT list

SELECT

The GROUP BY column does not have to be in the ______ list

Grouping by more than one column

Using the GROUP BY clause on multiple columns

True

True or false:

Any column or expression in the SELECT list that is not an aggregate function must be in the GROUP BY clause

SELECT

Any column or expression in the _____ list that is not an aggregate function must be in the GROUP BY clause

GROUP BY

Any column or expression in the SELECT list that is not an aggregate function must be in the ____ clause

True

True or False

You cannot use the WHERE clause to restrict groups.

WHERE

You cannot use the ___ clause to restrict groups.

True

True or False:

You cannot use group functions in the WHERE clause.

WHERE

You cannot use group functions in the ____ clause.

RESRICTING GROUP RESULTS

• Rows are grouped

• GROUP function is applied

• GROUPS matching the HAVING clause are displayed

When using the HAVING clause it restricts?

When using the HAVING clause

ROLLUP

creates subtotals that roll up from the most

detailed

level to a grand total, using the grouping list

specified in the GROUP BY clause.

CUBE

is often used in queries that use columns from separate tables rather than separate columns from a single table.

Check how cube works