Overview

Introduction

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

  • Comparing two values with different data types
  • Working with COUNT function on columns with NULL values
  • Working with AVG function on columns with NULL values

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

Exam Objectives

The following lab objectives are covered in this lab.

  • Write and execute a query to compare two values with different data types and observe the implicit conversion that takes place before the comparison
  • Write and execute a query with COUNT function on a column with NULL values
  • Write and execute a query with AVG function on a column with NULL values

Exercise 1 - Comparing Two Values with Different Data Types

In SQL Server databases, the columns of a table store specific data or values in the form of numbers, dates, or text. These values are referred to as data types. Some of the most common data types are as follows:

  • INT
  • VARCHAR, NVARCHAR
  • DATETIME
  • DECIMAL, FLOAT
  • BIT

A particular data type defines the type of data that can be stored in a table. For example, the data type INT can only store whole numbers and does not accept any decimal values. Similarly, the data type VARCHAR stores characters (letters and numbers) of any length.

To compare values of different data types, it is important to convert the value of one data type to another data type. Transact-SQL implicitly (automatically) converts a data type to another data type when comparing the values, if none of the two data types has been specified for conversion. For example, when CHAR is compared to a FLOAT, the CHAR is implicitly converted to FLOAT before starting with comparison.

In this exercise, you will learn to compare two values assigned to different data types after a specific data type converts to another data type implicitly (automatically).

Exercise 2 - Working with COUNT Function on Columns with NULL Values

In Transact-SQL, a column with a NULL does not represent any value, but it signifies that a value has been provided in that particular column when creating the database. Thus, the data is not available in that column of the specific row. A NULL value should not be taken as zero or an empty space; it means the value might not be applicable or not known.

When working with large databases, there arises a need to analyze the number of rows with NULL and non-NULL values in the tables. For example, some of the listed products do not have a standard cost, whereas some of the products have a standard cost. The StandardCost column of the table listing the products with unknown cost is set to NULL.

The COUNT function can be used to obtain the total number of records having NULL or non-NULL values, as specified in the query. It is also possible to get the total number of records by ignoring the columns with NULL value by marking it as ‘none’ and hence excluded in the counting.

In this exercise, you will learn how to use the COUNT function in Transact-SQL to test for non-NULL and NULL values in table columns.

Exercise 3 - Working with AVG Function on Columns with NULL Values

Transact-SQL provides the AVG function to calculate the average of an expression or column given in the SELECT statement. It is possible to calculate the average of the records irrespective of having NULL values.

In this exercise, you will learn to use the AVG function in Transact-SQL to calculate the average of columns by ignoring the NULL values.

Comprehensive Learning

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