Review

The following questions are designed to reinforce key information presented in the appendix. If you are unable to answer a question, review the appropriate lesson and then try the question again.

  1. An employee in the marketing department has asked you to provide summary data for product sales. She needs all breakfast cereals summarized by type (hot, cold, or low-fat), manufacturer, and size of the store where the product was sold (small, medium, or large). If a single table holds all this information, what clauses or operators might you use with the SELECT statement? Why?
  2. The GROUP BY clause with the CUBE operator provides the most complete data.

    The GROUP BY clause provides only one level of summaries (or groups).

    The ROLLUP operator provides summaries for some categories.

    The CUBE operator provides summaries for all categories.

    You also could use the COMPUTE or COMPUTE BY clauses to generate basic reports.

  3. Your manager has asked you to deliver a file that includes all the data from Question 1 to another development group that is responsible for report generation and graphing tools. Would using the COMPUTE and COMPUTE BY clauses be appropriate for this task? Why or why not?
  4. No. The COMPUTE and COMPUTE BY clauses generate extra summary rows of data in a nonrelational format. Although it is useful for viewing, the output is not well suited for generating result sets to use with other applications. You could use the GROUP BY clause and the CUBE or ROLLUP operator to provide data in a standard relational format that other clients can use easily.

  5. You are reviewing the results of a SELECT statement that used the GROUP BY clause and the CUBE operator. You see null values in the result set, and you know that null values are allowed in the tables that the SELECT statement uses. How can you distinguish between detail rows and summary rows with null values?
  6. Use the GROUPING function on the columns that allow null values. A value of 1 appears in the column generated by the GROUPING function if that row is a summary row.

  7. You need to provide a list of the top 100 products, as well as the products that are in the bottom 5 percent of sales. Can you use the SELECT TOP n [PERCENT] statement to answer each question? Are there other ways to answer the questions?
  8. Yes. You can use the SELECT TOP n [PERCENT] statement to answer each question. The first question would be answered with the SELECT TOP 100...ORDER BY...DESC statement so that the items with the highest quantity that are sold would be at the top of the list.

    The second question would be answered with the SELECT TOP 5 PERCENT...ORDER BY...ASC statement so that the items with the lowest quantity that are sold would be at the top of the list.

    Another way to answer these questions is to set the number of rows that are returned by the query using the SET ROWCOUNT option.



Microsoft Corporation - Microsoft SQL Server 7 Data Warehousing Technical Support Training Kit
Microsoft SQL Server 7 Data Warehousing Technical Support Training Kit
ISBN: 0735606706
EAN: 2147483647
Year: 1999
Pages: 114

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net