Home

Boolean Constants

 

Logical Comparisons

 

Introduction

Databases and other programming environments provide operators you can use to perform data analysis. The operators used are called logical operators because they are used to perform comparisons that produce a result of true or false (there is no middle result; in other words, something is not half true or half false or "Don't Know": either it is true or it is false).

           

The TRUE and FALSE Constants

Like C#, in SQL, a Boolean variable can hold a TRUE value. The value is also considered as 1. By contrast, if something doesn't hold a value, it is considered non-existent and non-worthy of consideration. Such a thing has a value of FALSE, 0, or No. To retrieve such a value, you can just find out if the value of a field is existent or not.

The comparison for a True or False value is mostly performed on Boolean fields, such a case is the SPHome (which specifies whether a student lives in a single parent home) field of the Students table of the HighSchool database. If a record has a value of 1, the table considers that such a field is True. If the field has a 0 value, then it holds a FALSE value.

The NULL Constant

After you have declared a variable, the SQL interpreter reserves a space in the computer memory for it but does not put anything in that memory space. At that time, that area of memory does not hold a significant value. Also at that time, the variable is considered null.

Here is note to be careful about: when a variable is said to hold a null value, it does not mean its value is 0. It does not even mean that the variable's memory space is empty. It actually means that we cannot clearly determine the current value that the variable is holding.

To support the null value, Transact-SQL provides a constant named NULL. The NULL constant is mostly used for comparison purposes. For example, you can use an IF statement to check the nullity of a variable.

The IS Operator

To validate something as being possible, you can use the IS operator. For example, to acknowledge that something is NULL, you can use the IS NULL expression. Here is an example:

// Square Calculation
private void btnConditions_Click(object sender, EventArgs e)
{
    SqlConnection connection =
	new SqlConnection("Data Source=(local);" +
			  "Database='Exercise4';" +
			  "Integrated Security=yes;");
    SqlCommand command =
 	 new SqlCommand("DECLARE @Side As Decimal(10,3)," +
			" 	 @Perimeter As Decimal(10,3)," +
			" 	 @Area As Decimal(10,3);" +
			"SET @Perimeter = @Side * 4;" +
			"SET @Area = @Side * @Side;" +
			"IF @Side IS NULL" +
			"    PRINT 'A null value is not welcome'" +
			"ELSE IF @Side > 0" +
			"    BEGIN" +
			"        SELECT @Side AS Side;" +
			" 	 SELECT @Perimeter AS Perimeter ;" +
			"        SELECT @Area AS Area;" +
			"    END;" +
			"ELSE" +
			"    PRINT 'You must provide a positive value';",
			connection);
    connection.Open();
    SqlDataReader rdr = command.ExecuteReader();

    while (rdr.Read())
    {
	MessageBox.Show(rdr[0].ToString());
    }

    rdr.Close();
    connection.Close();
}

To avoid having a NULL value, you can either initialize the variable or you can assign it a value. Here is an example:

NULL

// Square Calculation
private void btnCalculate_Click(object sender, EventArgs e)
{
    SqlConnection connection =
	new SqlConnection("Data Source=(local);" +
			  "Database='Exercise4';" +
			  "Integrated Security=yes;");
    SqlCommand command =
	 new SqlCommand("DECLARE @Side As Decimal(10,3)," +
	 	        " 	 @Perimeter As Decimal(10,3)," +
		        " 	 @Area As Decimal(10,3);" +
			"SET @Side = 48.126;" +
			"SET @Perimeter = @Side * 4;" +
			"SET @Area = @Side * @Side;" +
			"IF @Side IS NULL" +
			"    PRINT 'A null value is not welcome'" +
			"ELSE IF @Side > 0" +
			"    BEGIN" +
			" 	SELECT @Side, @Perimeter, @Area;" +
			"    END;" +
			"ELSE" +
			"    PRINT 'You must provide a positive value';",
			connection);

    connection.Open();
    SqlDataReader rdr = command.ExecuteReader();

    while (rdr.Read())
    {
	txtSide.Text = rdr[0].ToString();
	txtPerimeter.Text = rdr[1].ToString();
	txtArea.Text = rdr[2].ToString();
    }

    rdr.Close();
    connection.Close();
}

This would produce:

The NOT Operator

To deny the presence, the availability, or the existence of a value, you can use the NOT operator. This operator is primarily used to reverse a Boolean value. For example, we have learned that FALSE is the opposite of TRUE. In the same way, TRUE is the opposite of FALSE. If you want to compare a value as not being TRUE, the NOT TRUE would produce the same result as the FALSE value. For the same reason, the expression NOT FALSE is the same as TRUE.

Lesson Summary

 

Exercises

  1. Create an Windows Application that gets the length and the width of a rectangle, then it calculates the perimeter and the area. The calculation must be performed by the SQL interpreter and not the C# compiler
  2. Create an Windows Application that gets the length, the width, and the height of a box, then it calculates the area and the volume. The calculation must be performed by the SQL interpreter and not the C# compiler
  3. Create an Windows Application that gets the radius of a circle, then it calculates the perimeter and the area. The calculation must be performed by the SQL interpreter and not the C# compiler
  4. Create an Windows Application that gets the radius of a sphere, then it calculates the diameter, the circumference, the total area, and the volume. The calculation must be performed by the SQL interpreter and not the C# compiler
  5. Create an Windows Application that gets the radius and the height of a cylinder, then it calculates the diameter of the base, the circumference of the base, the area of the base, the lateral area of the cylinder, the total area of the cylinder, and the volume. The calculation must be performed by the SQL interpreter and not the C# compiler
 

Published on Monday 27 December 2007

 

Previous Copyright © 2007 FunctionX, Inc. Home