Course Description
Aggregate functions in SQL Server 2016 are used to summarize data in multiple rows. This course will cover various types of aggregate functions such as SUM, MIN, MAX, AVG, and COUNT, as well as the GROUP BY clause, which is used to arrange your data into subsets before summarizing it. Lastly, this course will demonstrate how to change a table-valued expression into another table using the PIVOT and UNPIVOT operators. This course is one of a series of courses that cover the objectives for 70-761: Querying Data with Transact-SQL exam.
Learning Objectives
Aggregate Functions
- start the course
- recognize the aggregate functions available using Transact-SQL
- describe the built-in aggregate function SUM in SQL Server 2016
- describe the built-in aggregate function AVG in SQL Server 2016
- list the built-in aggregate function in MIN Server 2016
- list the built-in aggregate function MAX in SQL Server 2016
- list the built-in aggregate function COUNT in SQL Server 2016
- describe how to aggregate distinct values using DISTINCT
- demonstrate how to use aggregate functions with NULL
GROUP BY Clause
- recognize when and where to use the GROUP BY clause
- demonstrate how to group and rank the results of a query using the windowing functions in SQL Server 2016
- describe how to process queries using the logical order of operations
- describe the GROUP BY workflow process
- describe how to use GROUP BY with aggregate functions in SQL Server 2016
- demonstrate using complex GROUP BY clauses using GROUPING sets
- recognize how to filter groups using the HAVING clause
Using PIVOT and UNPIVOT
- recognize how to use PIVOT and UNPIVOT relational operators in SQL Server 2016
- demonstrate how to use PIVOT in SQL Server 2016
- demonstrate how to use UNPIVOT in SQL Server 2016
- describe how to determine the impact on NULL values in PIVOT and UNPIVOT queries
Practice: Implement Aggregate Functions
- use aggregate functions in SQL Server 2016