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.
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.
See the full benefits of our immersive learning experience with interactive courses and guided career paths.