Chapter 8: Using Operators to Categorize Information

When we are talking about operators in your database, we are talking about the reserved characters and words that are mostly used in the WHERE clause of your statements.

Operators are used in order to perform operations in your statements, such as mathematical equations and comparisons, and they can even help to specify the parameters you want to set for the statements.

And of course, they are able to connect more than one parameter that is found within the SQL statement.

There are a few different types of operators that you will be able to use within your SQL statement including:

• Arithmetic operators
• Comparison operators
• Logical operators
• Operators for negating conditions

Each of these will work in a slightly different way to help you to get the results that you want. 

Logical operators

The logical operators are going to be the keywords that you will use in your statements in order to perform comparisons.

Some of the logical operators you can use include:

• In—this operator will allow you to compare the value of specified literal values that are set.

You will get a true if one or more of the specified values is equal to the value that you test.

• Like—the like operator is going to make it so that you can compare a value against others that are similar.

You will usually combine it with the “_” or the “%” sign to get more done.

The underscore is used to represent a number or a character ad the % is used for several characters, zero, or one.

• Unique—with the unique operator, you will be able to take a look at one or more of your data rows and see if they are unique or not. 
• Exists—you will need to use this operator to find the data rows that will meet the criteria that you put down.

It basically allows you to put down some criteria and see if there are any rows that exist that meet with this. 

• Between—you can use this operator in order to find values that will fall into a specific range.

You will need to assign the maximum and the minimum values to make this work. 

• Is null— the is null operator will allow you to compare the value of your choosing with a NULL one. 
• Any and all—any and all values often go together.

Any operator is going to compare a value against all of the values on a list.

The list of values is to be set up with predetermined conditions.

ALL, on the other hand, will compare the values that you select against the values that are in a different value set.

These are important for helping you to look up new data points within your database or to make some comparisons.

You can try out a few of these in your database and see what they come up with.

Comparison operators

The comparison operators are great if you would like to check the single values that are found inside of an SQL statement.

This particular category is going to be composed of some basic mathematical signs, so they are pretty easy to figure out.

Some of the comparison operators that you will be able to use within your SQL database includes:

• Non-equality—if you are testing the non-equality operator, you will use the “<>” signs.

The operation is going to give you the result of TRUE if the data does not equal and FALSE if the data does equal.

You can also use the “!=” to check for this as well. 

• Equality—you will be able to use this operator in order to test out some single values in your statement.

You will simply need to use the “=” sign to check for this.

You are only going to get a response if the data that you chose has identical values.

If the values aren’t equal, you will get a false, and if they are equal, you will get a true. 

• Greater-than values and Less-than values—these two are going to rely on the “<” and “>” signs to help you out.

They will work as stand-alone operators if you would like, but often they are more effective when you combine them together with some of the other operators.

Arithmetic operations

These operators are great if you are looking to do some mathematical operations in your SQL language.

There are four main type types that you will be able to use in your equations.

These include:

• Addition—you will just need to use the “+” sign to get started on using addition.

For this example, we are going to add up the values for the overhead column and the materials column.

The statement that works for this is:

• SELECT MATERIALS + OVERHEAD FROM PRODUCTION_COST_TBL. 
• Subtraction—you can also do some basic subtraction when it comes to your SQL statements.

You will simply need to use the “-“ sign to do this. 

• Multiplication—it is possible to do multiplication from your tables as well.

You would need to use the “*” sign to do this. 

• Division—this one will just need the “/” sign to get started.

You can combine a few of the arithmetic operations as well if this is what your process needs.

For example, you may want to go through and figure out how much you are earning each month and then subtract the costs when you are all done.

Your earnings are probably going to have more than one entry, and this is possible for how much you spent as well, so you may need to do a few different operations to make this happen.

If you are going to use any combination of arithmetic operators, you need to remember that the principles of precedence are going to come into play.

This means that the syntax is going to take care of all the things that need to be multiplied first, and then the division, and then addition, and then subtraction.

It will not go from left to right, but it will go around to work with the symbols that you have up for the arithmetic operations, so keep this in mind when you are writing out your syntax.

Operators to use when negating conditions

In some cases, you will want to negate the operators in your database when you want to change the viewpoint of the condition.

You will want to use the NOT command in order to cancel the operator that it is used for.

Some of the techniques that you can use to do this include:

• Not equal—you can use this when you want to find the results that are not equal to something.

For example, if you did use the “<>” or the “!=” symbols, you would get anything that was not equal to the value that you placed into the equation. 

• Not between—you can also negate with the not between and the between operators.

For example, if you are using a price, you could decide that you want to find the values that are not between 500 and 1000 or any value.

This means that all the values that come up need to be either 499 and below or 1001 and above. 

• Not in—if you will be able to use the not in command in order to negate the in operator.

You will be able to pick some values that you don’t want to have listed, and then the not in command will return you anything that doesn’t fit in that amount. 

• Not like—this is the term that you will use to negate the operator like.

When you are using this, you are only going to see the values that are different from the one that you placed into the equation. 

• Is not null—this one will work to negate the is null command.

You will want to use this in order to check for the data that isn’t null.

These are going to help you to set up some of the conditions of your search, so you are able to get a wide variety of information back without having to go through every little part of your database.

Conjunctive operators

There are going to be times when you will need to use several different criteria to make something happen.

For example, if you are getting some results that are confusing from the database searches, you may decide to add in a few different criteria to see how it will turn up.

You will be able to combine together some different criteria in the statement in order to make the conjunctive operator.

Some of the conjunctive operators that you can use include:

• OR—you will use this statement to combine the conditions of the WHERE clause.

Before this statement can take action, the criteria need to be separated by the OR, or it should be TRUE.

• AND—this operator is going to make it easier to include multiple criteria into the WHERE section of your statement.

The statement will then only take action when the criteria have been segregated by the AND, and they are all true.

Keep in mind there are times when you will want to combine these operators together inside of the statement.

Just make sure that you are adding in parentheses to improve readability.