|
| |
| |
|
|
|
Da Notes -
70-029 - Q
Personal notes on the MCDBA tests. These notes are from the various resources I used to pass the MCDBA exams including the MCDBA sample exams from Transcender. |
Passing Score: 693, Questions: 49, Time: 150 Min.
|
Da Notes Index
|
|
|
Query
|
Query Order in SQL7.0:
Select
From
Where
Group
Having
Order by
Compute By
Query Clause - Where :
Aggregate functions can not be used in Where clauses because aggregate functions are calculated from the result set and the Where clause produces the result set.
Query Clause - Group:
Aliases can not be used. All columns in the Select list must occur in the Group clause, but can have additional columns. Returns an aggregate value for every row in the result set. Will only return one summary row for every specified group when used without ROLLUP or CUBE.
Query Clause - Having:
All the columns in the having clause must be listed in the group by clause or used in aggregate functions. When using the HAVING clause all columns that appear in the SELECT list or in the HAVING clause must either be listed in the GROUP BY clause or be used in an aggregate function.
Query Clause - Order By:
ORDER BY - must use since SQL 7.0 does NOT guarantee any order. Ascending is the default order by. All columns in the Compute by clause must appear in the order by clause. The sort order of each column is independent of each other.
Query Clause - Compute By:
When compute is used ORDER BY must also be used. Cannot contain aliases. All columns that are present in the compute by clause must be listed in order by. Compute returns both detail information and sum values as separate result sets. Without the BY, you will get one row for every column that is specified in the compute clause. When BY is used then you will receive a sum row for every group that is specified after BY word.
To summarize the differences between COMPUTE and GROUP BY:
GROUP BY produces a single result set. There is one row for each group containing only the grouping columns and aggregate functions showing the subaggregate for that group. The select list can contain only the grouping columns and aggregate functions.
COMPUTE produces multiple result sets. One type of result set contains the detail rows for each group containing the expressions from the select list. The other type of result set contains the subaggregate for a group, or the total aggregate for the SELECT statement. The select list can contain expressions other than the grouping columns or aggregate functions. The aggregate functions are specified in the COMPUTE clause, not in the select list.
|
|
|
|
|
