Which action should you take?
Correct Answer for the following Question is given below
You are a database developer on an instance of SQL Server 2008. Your Transaction table has a PRIMARY KEY constraint defined on the TrxID column. YourTransaction table also contains columns named TrxType and TrxDisposition.You want to generate a list of the number of transactions by type and disposition. You want the result set to display the following data:• A total transaction count for each type• A total transaction count for each disposition• A grand total count of all transactionsWhich action should you take?The WITH CUBE clause is used with a GROUP BY clause to return rows that contain summary information. When you specify WITH CUBE, a summary row is included in the result set for each possible combination of the columns specified in the GROUP BY clause. For example, assume you executed the following statement:SELECT TrxType, TrxDisposition, COUNT(TrxID) AS TrxCount FROM dbo.Transaction GROUP BY TrxType, TrxDisposition WITH CUBE;The summary rows that represent totals for each transaction type would have a transaction type in the TrxType column, and a NULL value in the TrxDisposition column. The summary rows that represent totals for each disposition would have a disposition in the TrxDisposition column, and a NULL value in the TrxType column. The grand total summary rows would have a NULL value for both the TrxType and TrxDisposition columns.You should not include only the TrxID column in the GROUP BY clause and specify a grouping set that includes only an empty set, nor include only the TrxType column in your GROUP BY clause and create a grouping set that includes the TrxType column. Neither of these approaches would generate the desired result. You can use grouping sets to explicitly specify the groups for which aggregate information should displayed. Grouping sets are specified by including the GROUPING SETS clause with the GROUP BY clause. The GROUPING SETS clause allows you to explicitly specify the groups for which aggregate information should be displayed. This allows you to use more than one grouping within a single query. The syntax of theGROUP BY clause with a GROUPING SETS clause is as follows:GROUP BY GROUPING SETS (groupingset1 [,…groupingsetn])Each grouping set specified can contain one or more columns or an empty set, specified as (). Aggregate rows are returned in the result set for only the specified groups. Specifying an empty set indicates that a grand total row should also be returned in the result set.You should not include the TrxType and TrxDisposition columns in your GROUP BY clause and include the WITH ROLLUP option. Including the WITH ROLLUP clause would display the total number of transactions of each type and grand totals, but not the number of transactions for each disposition.
Question:
Which action should you take?
Options:
Include only the TrxID column in the GROUP BY clause and specify a grouping set that includes only an empty set.
Include only the TrxType column in your GROUP BY clause and create a grouping set that includes the TrxType column.
Include the TrxType and TrxDisposition columns in your GROUP BY clause and include the WITH ROLLUP option.
Include the TrxType and TrxDisposition columns in your GROUP BY clause and include the WITH CUBE option.
Correct Answer
The Correct Answer for this Question is
Include the TrxType and TrxDisposition columns in your GROUP BY clause and include the WITH CUBE option.
Explanation
The Question – Which action should you take? has been answered correctly and answers for the question is Include the TrxType and TrxDisposition columns in your GROUP BY clause and include the WITH CUBE option.
More about these Exams
These Exam Questions and the order of these questions keep changing. but the answers are obviously same. so if you don’t find a question after another we suggest you search it in the search box and we are sure you’ll find it. you can bookmark this site for Quick access in future.
We hope you found it helpful don’t forget to leave a comment if you feel a need to correct or ask we’re always here to help.
you can find more here at mnccertified
Feel free to contact via comment or email.
Happy Learning
Cheers, Team MNCcertified
