Home

SQL Operators and Operands

 

Fundamental SQL Operators

 

Introduction

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.

Practical Learning Practical Learning: Introducing Code Writing

  • Open the SQL Query Analyzer

PRINT Something

Like every language, SQL ships with some words used to carry its various operations. If 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.

 

Practical Learning Practical Learning: Printing Something

  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 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 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:

-∞   -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:

PRINT +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
 

Previous Copyright © 2005-2010 FunctionX, Inc. Next