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.