CS457 E HW8
1Q) What are GROUP BY with ROLLUP and CUBE operators?
As) The rollup, cube operators are extension of group by clause. The rollup, cube operators can generate the same result set as when you use UNION ALL to combine single grouping queries, however using one of the group by clause operators like rollup, cube, and grouping sets is usually more efficient. These operators don’t support the CHECK SUM _AGG function.
2Q) what is Grouping sets?
As) calculating all possible subtotals in a cube especially those with many dimensions can be quite an intensive process. If you don’t need all the subtotals, this can represent a considerable amount of wasted effort. In order to find subtotal of levels of any cube
For ex: We can use Grouping sets & specify exactly which one we need, saving us to calculate the whole cube. Grouping sets are specified in the group by clause.
3Q) what are composite columns?
As) A composite column is a collection of columns that are treated as a unit during the computation of groupings you can specify the column in parenthesis as in the following statement.
Rollup (year, (Quarter month), day).
In this statement data is not rolled up across year & quarter. (Quarter month) is a composite column which is treats as a unit.
4Q) What is concatenated grouping function is used for?
As) Concatenated grouping function are defined by putting to geher multiple grouping sets, cube or rollups separated by commas, resulting grouping are the cross-product of all groups produced by the individual grouping sets. It might be a little easier to understand
Ex: Grouping sets (a ,b), Grouping sets (c , d)
(a, c)
(a, d)
(b, c)
(b, d)
5Q) What is multiple column sub query?
As) ->It returns more than 1 column to their outer query & can be listed in the outer queries FROM, WHERE & HAVING Clause.
-> If multiple sub query is used in the FROM clause, it creates a temporary table referenced by outer clauses of outer query.
-> This temporary table is more formally called
As) The rollup, cube operators are extension of group by clause. The rollup, cube operators can generate the same result set as when you use UNION ALL to combine single grouping queries, however using one of the group by clause operators like rollup, cube, and grouping sets is usually more efficient. These operators don’t support the CHECK SUM _AGG function.
2Q) what is Grouping sets?
As) calculating all possible subtotals in a cube especially those with many dimensions can be quite an intensive process. If you don’t need all the subtotals, this can represent a considerable amount of wasted effort. In order to find subtotal of levels of any cube
For ex: We can use Grouping sets & specify exactly which one we need, saving us to calculate the whole cube. Grouping sets are specified in the group by clause.
3Q) what are composite columns?
As) A composite column is a collection of columns that are treated as a unit during the computation of groupings you can specify the column in parenthesis as in the following statement.
Rollup (year, (Quarter month), day).
In this statement data is not rolled up across year & quarter. (Quarter month) is a composite column which is treats as a unit.
4Q) What is concatenated grouping function is used for?
As) Concatenated grouping function are defined by putting to geher multiple grouping sets, cube or rollups separated by commas, resulting grouping are the cross-product of all groups produced by the individual grouping sets. It might be a little easier to understand
Ex: Grouping sets (a ,b), Grouping sets (c , d)
(a, c)
(a, d)
(b, c)
(b, d)
5Q) What is multiple column sub query?
As) ->It returns more than 1 column to their outer query & can be listed in the outer queries FROM, WHERE & HAVING Clause.
-> If multiple sub query is used in the FROM clause, it creates a temporary table referenced by outer clauses of outer query.
-> This temporary table is more formally called