SQL Operators and Operands

An operation is an action performed on one or more values either to modify the value held by one or both of the variables or to produce a new value by combining values. Therefore, an operation is performed using at least one symbol and one value. The symbol used in an operation is called an operator. A value involved in an operation is called an operand.

A unary operator is an operator that performs its operation on only one operand. An operator is referred to as binary if it operates on two operands.

• Open the SQL Query Analyzer

PRINT WhatToPrint

1. To use PRINT, in the white area of the Query window, type
    
   PRINT 'Welcome to the wonderful world of MS SQL Server'
2. To execute the statement, press F5
    
   

SELECT, Anything

The SELECT keyword, the most widely used word of databases, can be used, among other things, to display a value. The SELECT keyword uses the following syntax:

SELECT What

Based on this, to use it, where it is needed, type SELECT followed by a number, a word, a string, or an expression. The item to display follows the same rules as PRINT. Here is an example:

SELECT 402

Practical Learning: Selecting a Value

1. To use the SELECT keyword, change the statement with
    
   SELECT 'Transact-SQL is the SQL implemented in MS SQL Server'
   GO
2. Execute the statement
    
   

SELECT This AS That

In the above introductions, we used either PRINT or SELECT to display something in the Query Window. One of their differences is that PRINT is primarily used to display text in the Messages section. As we will learn in other lessons, SELECT is used to select one or more values and/or perform other, more elaborate, operations. One of the characteristics of SELECT is that it can segment its result in different sections. To display its result(s), it uses the Grids property page of the lower section of the Query window and each section is represented on its side. To create various sections using SELECT, separate them with a comma. Here is an example:

SELECT represents each value in a section called a column. Each column is represented with a name also called a caption. By default, the caption displays as "(No column name)". If you want to use your own caption, on the right side of an expression, type the AS keyword followed by the desired caption in single quotes. Here is an example:

SELECT 26 AS 'Age'

The item on the right side of the AS keyword must be considered as one word. If you create it in one word, fine. If you want it in different words, put them in inside of an opening square bracket "[" and a closing square bracket "]". Here is an example:

If you create different sections, separated by a comma, you can follow each with AS and a caption.

Practical Learning: Setting Captions

1. To set some captions for columns, change the statement as follows:
    
   SELECT 'James Knight' As FullName, 20.48 AS Salary
   GO
2. Execute the statement
    
   
3. Change the statement as follows:
    
   SELECT 'James Knight' As [Full Name], 20.48 AS [Hourly Salary];
   GO
4. Execute it

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:

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:

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