The Grouping and Pivoting Data module provide you with the instructions and devices to develop your hands-on skills in the following topics.
- Working with GROUP BY clause
- Using HAVING clause with GROUP BY
- Demonstrating the use of ranking functions
- Using PIVOT and UNPIVOT operators
Lab time: It will take approximately 60 minutes to complete this lab.
The following exam objectives are covered in this lab:
- Demonstrate the use of GROUP BY clause
- Use a simple GROUP BY clause
- Use a GROUP BY clause with multiple tables
- Use GROUP BY with ROLLUP, CUBE and GROUPING SETS
- Demonstrate the use of HAVING clause that specifies a search condition for a group or an aggregate
- Demonstrate the use of Ranking functions that rank the results of a query
- Use PIVOT and UNPIVOT operators to change a table-valued expression into another table to summarize data
Exercise 1 - Working with GROUP BY Clause
A GROUP BY clause is used in a SELECT statement. It divides the results into separate groups and displays the aggregate value for each group.
The GROUP BY clause can be used with the following operators to display varied results:
- ROLLUP: It creates groups for each combination of column expressions provided in the clause. In addition, it also calculates the subtotal and the grand total for each group of the column expression. The number of rows in a group depends on the number of unique combinations of values in the column expressions.
- CUBE: It creates a group for all the possible combinations of column expressions in the GROUP BY clause. For example, using GROUP BY CUBE (x, y) will display the list of unique values for each of the group that is (NULL, y), (x, NULL), or (NULL, NULL).
- GROUPING SETS: It combines the different GROUP BY clauses into a single result set. For example, the GROUP BY ROLLUP and GROUP BY CUBE can be combined using GROUPING SETS and displayed as a single result set.
In this exercise, you will learn to use GROUP BY clause with operators in SELECT statement.
Exercise 2 - Using HAVING Clause with GROUP BY
The HAVING clause filters the rows according to the condition specified for a group. It is generally used with the GROUP BY clause.
In this exercise, you will learn to use the HAVING clause along with GROUP BY.
Exercise 3 - Demonstrating the Use of Ranking Functions
Ranking functions are used with OVER, PARTITION BY, and ORDER BY clauses to assign numeric values to the result set.
The OVER clause orders or partitions the result set according to the associated PARTITION BY or ORDER BY clauses. The PARTITION BY clause creates separate partitions or groups of the result set. For example, a result set can be partitioned based on the department of an employee in the organization. The ORDER BY clause arranges the result set in the ascending or descending order of the rows.
Ranking functions are used to assign a numeric value to each row in a partition. Assigning a value to each partitioned row depends on the function used in the partition. Some of the ranking functions are as follows:
- ROW_NUMBER (): It assigns sequential numbers to the entire output of the result set within a partition. The ROW_NUMBER () function can be used with the PARTITION BY clause to assign the values sequentially for each ordered partition.
The syntax for the ROW_NUMBER () function is as follows:
ROWNUMBER ( ) OVER ( [ PARTITION BY valueexpression , ... [ n ] ] orderbyclause ) RANK (): It is similar to ROW_NUMBER () function. However, the RANK () function assigns same value to repetitive fields in the partition. RANK () function can be used with the PARTITION BY clause.
The syntax for RANK () function is as follows:
- RANK ( ) OVER ( [ PARTITION BY valueexpression , ... [ n ] ] ORDER BYclause ) NTILE (): It divides the number of rows in the partition into specified number of groups. The NTILE () function returns the number of the group to which a row belongs.
The syntax for the NTILE () function is as follows:
- NTILE (integer_expression) OVER ( [
] < ORDER BY clause>)
In this exercise, you will learn to rank the rows in a partition using the ranking functions.
Exercise 4 - Using PIVOT and UNPIVOT Operators
PIVOT and UNPIVOT are the relational operators used to change a table-valued expression into another table. For example, PIVOT operator can be used to convert unique rows from one table to column headers in another table. PIVOT operator also performs aggregations on the remaining columns of the query that are required in the final output. UNPIVOT operator performs the reverse operation of PIVOT operator.
See the full benefits of our immersive learning experience with interactive courses and guided career paths.