SQL Operators and Operands

 

Operators Fundamentals

 

Introduction

In order to use a computer language, you usually give instructions to an internal program. An instruction can be formulated as a command. Examples are "give me food" or "touch your head". Another type of instruction can be formulated as a question. Examples are "what time is it?" or "Are you hungry?". The sentence or group of words that constitute an instruction is also called a statement.

To use the Structured Query Language (SQL), you create SQL statements. A statement is then communicated to an internal program called an interpreter or the SQL interpreter. The interpreter analyzes the statement. If it is able to understand it, then it executes it. If it cannot understand your statement, in a good (not necessarily the best) case scenario, it may display an error. In a bad (not necessarily the worse) case scenario, it may not display anything or it may display an unreliable result.

Formulating SQL Statements

There are rules you should or must following in order to create good instructions and reliable statements. Every SQL interpreter has its own rules. This means that there are some differences in the rules to follow when using Oracle, MySQL, Microsoft Access, DB2, Paradox, Microsoft SQL Server, etc. This is not necessarily because the interpreters are from different companies. For example, there are some differences in the ways Microsoft Access and Microsoft SQL Server interpret some instructions.

To create your statements, you will use two categories of words. A keyword is a word that is internally used by the interpreter. We will mention the keywords as we move on. The other words are those you will make up on your own. SQL is not case-sensitive. This means that CASE, Case, and case represent the same word.

 

GO For the Semi-Colon

After writing a statement, you must indicate its end. This is because MySQL and MSDE work at the command prompt but the interpreter must be able to allow you to provide as long a statement as you want. That is, you must be able to write a statement that spans various lines. Therefore, when you are ready to hand the statement to the interpreter, you must let it know:

  • In MSDE, to indicate the end of a statement, type GO on its own line. This means that you can type any part of the statement and press Enter at the end of each line. In some cases, you will need to type the semi-colon to indicate the end of an expression. Optionally, in all statements, before typing GO on its own line, in the last line of your statement, you can type the semi-colon. To execute a statement in MSDE, after typing GO, you must press Enter
  • In MySQL, to indicate the end of a statement, type the semi-colon and press Enter

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 values, 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. A binary  operator is an operator that performs its operation on two operands.

 

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 can be anything that is allowed and it is provided on the right side of SELECT. If it is a normal (called a constant) number, simply type it on the right side of SELECT. Here is an example:

SELECT 258

The item to display can also be an operation or the result of an operation. 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.

 

Practical LearningPractical Learning: Selecting a Value

  1. Open the Command Prompt
     
  2. Type
     
    SELECT 'Welcome to the Wonderful World of the Structured Query Language'
  3. If you are using MSDE, press Enter, type GO and press Enter again
     

     
    If you are using MySQL, type the semi-colon and press Enter
     
 

SELECT This AS That

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. Each section is represented separately. To create various sections using SELECT, separate them with a comma. Here is an example:

SELECT 873, 4683

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 is empty. If you want to use a 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 inside of an opening square bracket "[" and a closing square bracket "]". Here is an example:

SELECT 'John Kasdan, III' AS [Chief Operating Officer]

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, execute the following statement:
     
    SELECT 'Alex Kouma' As Employee, 14.82 As Salary;
     
     Employee   Salary
     ---------- ------
     Alex Kouma  14.82
    
    (1 row affected)
  2. Execute the following statement:
     
    SELECT 'Alex Kouma' AS [Employee Name], 14.82 AS [Hourly Salary];

     
    1> SELECT 'Alex Kouma' AS [Employee Name], 14.82 AS [Hourly Salary];
    2> GO
     Employee Name Hourly Salary
     ------------- -------------
     Alex Kouma            14.82
    
    (1 row affected)
    1>

 
New Convention
From now on, when we write "Execute the following statement", we will give you the statement. If you are using MSDE, after typing the statement, press Enter, type GO as the last line and press Enter to execute the statement.
 

Previous Copyright © 2004-2005 FunctionX, Inc. Next