### Introduction

The Working with Aggregate Data module provides you with the instructions and devices to develop your hands-on skills in the following topics.

• Using aggregate functions in SELECT statement
• Working with mathematical and datetime functions
• Working with system functions

Lab time: It will take approximately 40 minutes to complete this lab.

### Exam Objectives

The following exam objectives are covered in this lab:

• Demonstrate the use of built-in aggregate functions in a SELECT clause
• Demonstrate the use of SQL Server mathematical functions and datetime functions in Transact-SQL code
• Demonstrate the use of SQL Server system functions in Transact-SQL code

### Exercise 1 - Using Aggregate Functions in SELECT Statement

Aggregate functions are used with a SELECT statement to perform calculations on values from table fields. They return a single value as a result. Some of the aggregate functions in Transact-SQL are as follows:

• MIN(): It returns the minimum value in the given field.
• MAX(): It returns the maximum value in the given field.
• SUM(): It returns the sum of the numeric values of the field.
• AVG(): It returns the average of the numeric values in the field.
• COUNT(): It returns the total number of values in the field.
• COUNT (*): It returns the number of rows in the table.

In this exercise, you will learn to use aggregate functions in a SELECT statement.

### Exercise 2 - Working with Mathematical and DateTime Functions

Transact-SQL offers various mathematical functions. Some of the mathematical functions are as follows:

• ROUND(): It rounds the value to the specified number of decimal places.
• CEILING(): It displays the next highest integer value.
• FLOOR(): It displays the lowest integer value.
• RAND(): It returns a random number.
• ABS(): It returns an absolute value.

Transact-SQL provides various datetime functions. Some of the datetime functions are as follows:

• GETDATE(): It returns the current date and time.
• DATEPART(): It returns the single part of the date or time.
• DATEADD(): It adds or subtracts the specified time interval from the given date.
• DATEDIFF(): It returns the time difference in between the two dates.

In this exercise, you will learn how to use the mathematical and datetime functions in Transact-SQL.

### Exercise 3 - Working with System Functions

Transact-SQL provides various system functions. Some of them are as follows:

• @@ERROR: It returns the error number of the last SQL statement executed
• @@ROWCOUNT: It returns the number of rows affected by the last SQL statement
• ISNULL: It replaces NULL values with the specified values

In this exercise, you will learn to use the system functions in Transact-SQL.

### Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.