 SQL Operators and Operands

Expressions

Introduction

An expression is value combined with a symbol to produce a new value. To make this possible, an expression may involve at least one value, the name of a column or else with one of the operators we have used in the previous lesson or one of those we will review here. There is no specific formula to follow: The formulation of an expression depends on the operator(s) and the operand(s) involved.

To use an expression in your Windows Forms Application, you can write one of the expressions we will studying, pass it to a SqlCommand object, and execute it.

PRINT Something

If you are working in the SQL Query Analyzer and you want to display something in plain text as a result of a statement, type PRINT followed by what to display. Therefore, PRINT uses the following formula:

`PRINT WhatToPrint`

The item to display can be anything that is allowed and it is provided on the right side of PRINT. If it is a normal (called a constant) number, simply type it on the right side of PRINT. Here is an example: The item to display can also be an operation or the result of an operation as we will learn in this lesson. If you want to display a character, a word, or a sentence, include it between single-quotes. You can also display an expression as a combination of number(s) and sentences as we will learn later.

Here is another example: Unary Operators

The Positive Operator +

Algebra uses a type of ruler to classify numbers. This ruler has a middle position of zero. The numbers on the left side of the 0 are referred to as negative while the numbers on the right side of the rulers are considered positive:

 -∞ -6 -5 -4 -3 -2 -1 1 2 3 4 5 6 +∞ 0 -∞ -6 -5 -4 -3 -2 -1 1 2 3 4 5 6 +∞

A value on the right side of 0 is considered positive. To express that a number is positive, you can write a + sign on its left. Examples are +4, +228, +90335. In this case the + symbol is called a unary operator because it acts on only one operand.

The positive unary operator, when used, must be positioned on the left side of its operand, never on the right side.

As a mathematical convention, when a value is positive, you do not need to express it with the + operator. Just writing the number without any symbol signifies that the number is positive. Therefore, the numbers +4, +228, and +90335 can be, and are better, expressed as 4, 228, 90335. Because the value does not display a sign, it is referred as unsigned as we learned in the previous lesson.

To express a variable as positive or unsigned, you can just type it. here is an example:

`SELECT +1250`

The Negative Operator -

As you can see on the above ruler, in order to express any number on the left side of 0, it must be appended with a sign, namely the - symbol. Examples are -12, -448, -32706. A value accompanied by - is referred to as negative.

The - sign must be typed on the left side of the number it is used to negate.

Remember that if a number does not have a sign, it is considered positive. Therefore, whenever a number is negative, it MUST have a - sign. In the same way, if you want to change a value from positive to negative, you can just add a - sign to its left.

Here is an example that uses two variables. One has a positive value while the other has a negative value:

`SELECT -1250`

Arithmetic Operators Introduction

We have seen that the SELECT keyword can be used to create a list of isolated columns. These columns are rendered separate of each other. Instead of having separate columns, you can combine them to create a string or a value that is in fact an expression. For example, you can combine a first name and a last name to create a full name.

An expression that combines columns can be performed on text-based columns. such as a first name being added to a last name to get a full name. Another expression can use a date on the table, add a number to it to get a date on another day. An expression can also be used to perform a calculation on two or more columns such as employees weekly hours multiplied by their hourly salary to get their weekly salary.

The addition can be used to add one value to another or to add a constant to the values of a column. Imagine you have create a table that allows employees to enter their daily hours as a time sheet as follows: Imagine that, at the end of a certain week, some employees submitted their times as follows: To calculate the total time for each employee, you can add the values of the weekdays using the addition operator. Here is an example: The order you use to add two or more values doesn't matter. This means Value1 + Value2 is the same as Value2 + Value1. In the same way a + b + c is the same as a + c + b the same as b + a + c and the same as c + b + a. This means that the addition is associative.

The Subtraction

The subtraction operation, sometimes called the difference, is used to take out or subtract one value from another value. It is essentially the opposite of the addition. The subtraction is performed with the - sign. Here is an example:

`SELECT 1240 - 608`

Unlike the addition, the subtraction operation is not associative. This means that a - b - c is not necessarily equal to c - b - a. This is illustrated in the following statements:

```SELECT 128 - 42 - 5

SELECT 5 - 42 - 128```

Notice that both operations of the addition convey the same result. In the subtraction section, the numbers follow the same order but a different operation; and the last two operations render different results.

The Multiplication *

The multiplication can be used to multiply each value of a column to a constant number. You can also use it to multiply the value of one column to the corresponding value of another column. Imagine you have created a table used to hold payroll information for employees as follows: Imagine a few employees have submitted their times as follows: To get the weekly salary of each employee, you can multiply the WeeklyHours column by the HourlySalary and get the result. Here is an example: The Division

The division operation is similar to cutting an item in pieces or fractions of a set value. Therefore, the division is used to get the fraction of one number in terms of another. The division is performed with the forward slash /. Here is an example:

`SELECT 128 / 42`

This would produce 3

When performing the division, be aware of its many rules. Never divide by zero (0). Make sure that you know the relationship(s) between the numbers involved in the operation.

The Modulo
 In the above division, 128/42, the result is 3. When you multiply 42 by 3, as in 42*3, you get 126. In some cases, you may be interested in knowing the amount that was left out after the operation. The modulo operation is used to get the remainder of a division as a natural number. The remainder operation is performed with the percent sign (%). Here is an example: `PRINT 128 % 42` This would produce 2

 Parentheses
 Like most computer languages, Transact-SQL uses parentheses to isolate a group of items that must be considered as belonging to one entity. For example, as we will learn soon, parentheses allow a function to delimit the list of its arguments. Parentheses can also be used to isolate an operation or an expression with regards to another operation or expression. For example, when studying the algebraic operations, we saw that the subtraction is not associative and can lead to unpredictable results. In the same way, if your operation involves various operators such as a mix of addition(s) and subtraction(s), you can use parentheses to specify how to proceed with the operations, that is, what operation should (must) be performed first. Here is an example:
 ```PRINT (154 - 12) + 8 PRINT 154 - (12 + 8)```

This would produce:

 ```150 134```
 As you can see, using the parentheses controls how the whole operation would proceed. This difference can be even more accentuated if your operation includes 3 or more operators and 4 or more operands.

Bit Manipulations

 Introduction
 When you use a value in your database or application, the value must be stored somewhere in the computer memory using a certain amount of space. As we review in our study of bytes and words, a value occupies space that resembles a group of small boxes. In our human understanding, it is not always easy to figure out how a letter such as as B is stored in 7 seven small boxes when we know that B is only one letter. Bit manipulation or a bit related operation allows you to control how values are stored in bits. This is not an operation you will need to perform very often, especially not in the early stages of your database. Nevertheless, bit operations (and related overloaded operators) are present in all or most programming environments, so much that you should be aware of what they do or what they offer.
 Bits Operators: The Bitwise NOT Operator ~
 One of the operations you can perform on a bit consists of reversing its value. That is, if a bit holds a value of 1, you may want to change it to 0 and vice-versa. This operation can be taken care of by the bitwise NOT operator that is represented with the tilde symbol ~ The bitwise NOT is a unary operator that must be placed on the left side of its operand as in `~Value` Here is an example: `PRINT ~158` To perform this operation, the Transact-SQL interpreter considers each bit that is part of the operand and inverts the value of each bit from 1 to 0 or from 0 to 1 depending on the value the bit is holding. This operation can be resumed in the following table:
 Bit ~Bit 1 0 0 1
 Consider a number with a byte value such as 248. In our study of numeric systems, we define how to convert numbers from one system to another. Based on this, the binary value of decimal 248 is 1111 1000 (and its hexadecimal value is 0xF8). If you apply the bitwise NOT operator on it to reverse the values of its bits, you would get the following result:
 Value 1 1 1 1 1 0 0 0 ~Value 0 0 0 0 0 1 1 1
 Bits Comparison: The Bitwise AND Operator &
 The bitwise & is a binary operator that uses the following syntax `Operand1 & Operand2` This operator considers two values and compares the bit of each with the corresponding bit of the other value. If both corresponding bits are 1, the comparison produces 1. Otherwise, that is, if either bit is 0, the comparison produces 0. This comparison is resumed as follows:
 Bit1 Bit2 Bit1 & Bit2 0 0 0 1 0 0 0 1 0 1 1 1
 Imagine you have two byte values represented as 187 and 242. Based on our study of numeric systems, the binary value of decimal 187 is 1011 1011 (and its hexadecimal value is 0xBB). The binary value of decimal 242 is 1111 0010 (and its hexadecimal value is 0xF2). Let�s compare these two values bit by bit, using the bitwise AND operator:
 Binary Decimal N1 1 0 1 1 1 0 1 1 187 N2 1 1 1 1 0 0 1 0 242 N1 & N2 1 0 1 1 0 0 1 0 178

Most of the times, you will want the interpreter to perform this operation and use the result in your program. This means that you can get the result of this operation and possibly display it to the user. The above operation can be performed by the following program:

`PRINT 187 & 242`

This would produce 178

Bits Comparison: The Bitwise OR Operator |

You can perform another type of comparison on bits using the bitwise OR operator that is represented by |. Its syntax is:

`Value1 | Value2`

Once again, the interpreter compares the corresponding bits of each operand. If at least one of the equivalent bits is 1, the comparison produces 1. The comparison produces 0 only if both bits are 0. This operation is resumed as follows:

 Bit1 Bit2 Bit1 | Bit2 0 0 0 1 0 1 0 1 1 1 1 1

Once again, let�s consider decimals 187 and 242. Their bitwise OR comparison would render the following result:

 Binary Decimal N1 1 0 1 1 1 0 1 1 187 N2 1 1 1 1 0 0 1 0 242 N1 | N2 1 1 1 1 1 0 1 1 251

You can also let the compiler perform the operation and produce a result. Here is an example:

`PRINT 187 | 242`

This would produce 251

Bits Comparison: The Bitwise-Exclusive XOR Operator ^

Like the previous two operators, the bitwise-exclusive OR operator performs a bit comparison of two values. It syntax is:

`Value1 ^ Value2`

The compiler compares the bit of one value to the corresponding bit of the other value. If one of the bits is 0 and the other is 1, the comparison produces 1. In the other two cases, that is, if both bits have the same value, the comparison produces 0. This operation is resumed as follows:

 Bit1 Bit2 Bit1 ^ Bit2 0 0 0 1 0 1 0 1 1 1 1 0

We will again consider decimals 187 and 242. Their bitwise-exclusive XOR comparison would render the following result:

 Binary Decimal N1 1 0 1 1 1 0 1 1 187 N2 1 1 1 1 0 0 1 0 242 N1 ^ N2 0 1 0 0 1 0 0 1 73

If the interpreter performs this operation, it can produce a result as in the following example:

`PRINT 187 ^ 242;`

This would produce 73

Logical Operators

Negation Operator: NOT

In reverse, you may want to use a rule that exclude your criterion and produces only those records that don't follow the rule. For example, you may want to see the list of your customers but excluding those who live in Mexico. To negate a condition, you can start it with the NOT operator. The condition in this case would become NOT (Country = 'Mexico'). Here is an example: Pattern Operator: LIKE

The comparison and the negation operators we reviewed above usually expect exact matches during their comparisons. For example, when a WHERE condition is formulated as Country = 'Mexico', the interpreter would look for a string under the Country column that exactly display Mexico and Mexico only. In some cases, you know know only the starting characters of a string, only the middle characters of a string, or only the ending characters of a string. The SQL allows you to look for a string that begins, contains, or ends with a few characters. The character or group of characters you provide is referred to as a pattern.

To use a pattern as criterion to look for in the values of a column, you can use the LIKE operator in your WHERE condition. The formula used by this operator is:

`ExpressionToMatch LIKE Pattern`

The ExpressionToMatch placeholder of this formula can be the name of a column whose values would be examined. The Pattern placeholder is an expression that would be looked for. To create a pattern, the LIKE operator uses wildcards. A wildcard is a placeholder in which you provide one or more characters.

One of the wildcards used to create a pattern is %. This is used as a placeholder for any character. For example, imagine that you want to find a list of your customers who live in a city whose name starts with M. In this case, any (number of) characters on the right side of M can be represented with the % placeholder. The condition would be formulated as follows:

```SELECT     *

FROM        Customers

WHERE     City LIKE 'M%'``` In the same way, you can use the % wildcard to specify the starting or the middle characters of a string.

Another wildcard used is the underscore _. Like the % wildcard, the underscore character is used as a placeholder but for only one character. Here is an example: Instead of the underscore, you can use a combination of the opening and the closing square brackets [] as a placeholder for a single character. While the underscore placeholder is used for any character, the difference with the square brackets is that, this time, you can specify the particular alphabetic-based ranged of characters you want to use. To specify the range or characters, between the square brackets, type both ending letters of the range separated by a dash character as in [a-e].

Another variance of the single character placeholder is the ^ used between the square brackets. It is used to exclude characters of the specified range. An example would be [^a-e]. This would produce a list of strings that don't start with either a, b, c, d, or e.

You can use combinations of these wildcards to create more precise patterns.

To negate a condition resulting from a LIKE pattern, you can use the NOT operator on the left side of LIKE as in NOT LIKE.

Logical Conjunction: The AND Operator

Imagine that you are reviewing the students� records of a school and you want to make sure that you know whom to contact in case of emergency for each student. For example, imagine that you are trying to make sure the record of each student provides an emergency telephone number. The statement to evaluate would be:

• This student's record provides an emergency phone number

If the record of the student referred to contains an emergency phone number, the statement is true. If the record does not provide this information, our goal is not met and the statement is false. Imagine that, on the other hand, we want to find out if the record of each student displays the name to contact in case of emergency. The statement considered would be:

• This student's record provides an emergency name

If the record considered displays an emergency name, the statement is true.

Imagine that a student's record indicates an emergency telephone number but does not provide an emergency name. What if a student's record indicates an emergency name but no emergency telephone number? It appears that in the absence of one of these pieces of information, it would be hard to perform an effective contact. To make a record complete or to accomplish our goal, we want each record to have both an emergency name and an emergency telephone number. We can combine both of the above statements as follows:

• "This student's record provides an emergency phone number" AND "This student's record provides an emergency name"

Evaluating the combined statement would produce the following results:

• If the record of the student referred to does not provide an emergency phone number, the record of the student is not complete and our goal is not met, regardless of the second statement. Therefore, the combined statement is false.
• If the record of the student referred to provides an emergency phone number, then we would consider the second statement.
If the record does not provide a name in case of emergency, the record is not complete and our goal is not met. Therefore, the combined statement is false.
• If the record of the student being considered provides neither an emergency phone number nor an emergency name, the record is still not complete and our goal is not met. Therefore, the combined statement is false.
• If the record of the student being considered provides both an emergency telephone number and an emergency name, we consider that the record is complete and our goal is met. Therefore, the combined statement is true.

To resume, our goal is met only if BOTH the first and the second statement are TRUE. This is done using the AND operator.

Logical Disjunction: The OR Operator

Imagine that our main goal is to make sure that we have a number we can use to contact somebody for the student. In this case, either the home phone number or the emergency number would be fine. We already have the following two statements:

1. This student's record indicates a home phone number
2. This student's record provides an emergency phone number

To get either number, we can concatenate these two statements and evaluate the truthfulness of either one of them. We create a new statement as follows:

"This student's record indicates a home phone number" OR "This student's record provides an emergency phone number"

The comparative evaluation produces the following results:

• If the student's record indicates a home phone number, our goal is met and we do not need to evaluate the second statement. Therefore, the combined statement is true.
• If the student's record does not indicate a home phone number, we can then consider the second statement.
If the student's record provides an emergency phone number, we have a number we can use. This means that the result of the first statement is not important. Therefore, the combined statement is true.
• If the student's record indicates a home phone number and the student's record provides an emergency phone number, our goal is still met; we can use either number. Therefore, the combined statement is true.
• If the student's record does not indicate a home phone number, then we consider the second statement. If the student's record does not provide an emergency phone number, our goal is not met: we do not have any number to contact somebody for the student in case of emergency. Therefore, the combined statement is false.

The logical disjunction shows us that a combined statement is true if EITHER of its two statements IS TRUE. The logical disjunction produces a false result if BOTH of its two statements are FALSE. To perform a logical disjunction, you can use the OR operator.

Range Test: BETWEEN

If you have a logical range of values and you want to know if a certain exact value is contained in that range, you use the BETWEEN operator. The BETWEEN operator is usually combined with the AND operator to get a list of records between two values. The basic formula of this operator is:

`Expression BETWEEN Start AND End`

The Expression placeholder of our formula is usually the name of the column whose values you want to examine. The Start factor is the starting value of the range to consider. The End factor is the highest value to consider in the range. After this condition is executed, it produces the list of values between Start and End. As an example, if you are creating some groups of students for activities, imagine that one group would have students from ID 54 to 72. You would write the WHERE condition as follows:

```SELECT     StudentID, StudentNbr, Gender, LastName, FirstName, DateOfBirth

FROM         Students

WHERE     StudentID BETWEEN 54 AND 72``` 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. In the same way, to validate that something is not null, you can use the expression IS NOT NULL.

Here is an example that displays the list of students whose records don't show the emergency name: Comparison Operators: IN

If you have a series of records and want to find a record or a group of records among them, you can use the IN operator.

SQL provides many and many other operators that we have not reviewed here.