### Overview

### 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.