Hour 8. Using Operators to Categorize Data
What You’ll Learn in This Hour:
• What is an operator?
• An overview of operators in SQL
• How are operators used singularly?
• How are operators used in combinations?
Operators are used in conjunction with the SELECT command’s WHERE clause to place extended constraints on data that a query returns. Various operators are available to the SQL user that support all data querying needs. In this hour we will show you what operators are available for you to use as well as how to utilize them properly within the WHERE clause.
What Is an Operator in SQL?
An operator is a reserved word or a character used primarily in an SQL statement’s WHERE clause to perform operation(s), such as comparisons and arithmetic operations. Operators are used to specify conditions in an SQL statement and to serve as conjunctions for multiple conditions in a statement.
The operators discussed during this hour are
• Comparison operators
• Logical operators
• Operators used to negate conditions
• Arithmetic operators
Comparison Operators
Comparison operators test single values in an SQL statement. The comparison operators discussed consist of =, <>, <, and >.
These operators are used to test
• Equality
• Non-equality
• Less-than values
• Greater-than values
Examples and the meanings of comparison operators are covered in the following sections.
Equality
The equal operator compares single values to one another in an SQL statement. The equal sign (=) symbolizes equality. When testing for equality, the compared values must match exactly, or no data is returned. If two values are equal during a comparison for equality, the returned value for the comparison is TRUE; the returned value is FALSE if equality is not found. This Boolean value (TRUE/FALSE) is used to determine whether data is returned according to the condition.
You can use the = operator by itself or combine it with other operators. Remember from the previous chapter that character data comparisons can either be case sensitive or case insensitive depending on how your relational database management system (RDBMS) is set up. So remember to check to ensure that you understand how exactly your values are compared by the query engine.
The following example shows that salary is equal to 20000:
WHERE SALARY = '20000'
The following query returns all rows of data where the PROD_ID is equal to 2345:
SELECT *
FROM PRODUCTS_TBL
WHERE PROD_ID = '2345';
PROD_ID PROD_DESC COST
-----------------------------------------------
2345 OAK BOOKSHELF 59.99
1 row selected.
Non-Equality
For every equality, there are multiple non-equalities. In SQL, the operator used to measure non-equality is <> (the less than sign combined with the greater than sign). The condition returns TRUE if the condition finds non-equality; FALSE is returned if equality is found.
The following example shows that salary is not equal to 20000:
WHERE SALARY <> '20000'
Did You Know?: Options for Non-Equality
Another option comparable to <> is !=. Many of the major implementations have adopted != to represent not-equal. Microsoft SQL Server, MySQL, and Oracle support both versions of the operator. Oracle actually supports a third, ^=, as another version, but it is rarely used because most people are accustomed to using the earlier two versions.
The following example shows all the product information from the PRODUCTS table that do not have the product ID of 2345:
SELECT *
FROM PRODUCTS_TBL
WHERE PROD_ID <> '2345';
PROD_ID PROD_DESC COST
-----------------------------------------------
11235 WITCH COSTUME 29.99
222 PLASTIC PUMPKIN 18 INCH 7.75
13 FALSE PARAFFIN TEETH 1.1
90 LIGHTED LANTERNS 14.5
15 ASSORTED COSTUMES 10
9 CANDY CORN 1.35
6 PUMPKIN CANDY 1.45
87 PLASTIC SPIDERS 1.05
119 ASSORTED MASKS 4.95
1234 KEY CHAIN 5.95
2345 OAK BOOKSHELF 59.99
11 rows selected.
Once again, remember that your collation and specifically whether your system is set up as case sensitive or case insensitive plays a critical role in these comparisons. If your system is case sensitive, then KEY CHAIN, Key Chain, and key chain would be considered different values, which might or might not be your intention.
Less Than, Greater Than
You can use the symbols < (less than) and > (greater than) by themselves or in combination with each other or other operators.
The following examples show that salary is less than or greater than 20000:
WHERE SALARY < '20000'
WHERE SALARY > '20000'
In the first example, anything less than and not equal to 20000 returns TRUE. Any value of 20000 or more returns FALSE. Greater than works the opposite of less than.
SELECT *
FROM PRODUCTS_TBL
WHERE COST > 20;
PROD_ID PROD_DESC COST
-----------------------------------------------
11235 WITCH COSTUME 29.99
2345 OAK BOOKSHELF 59.99
2 rows selected.
In the next example, notice that the value 29.99 was not included in the query’s result set. The less than operator is not inclusive.
SELECT *
FROM PRODUCTS_TBL
WHERE COST < 29.99;
PROD_ID PROD_DESC COST
-----------------------------------------------
222 PLASTIC PUMPKIN 18 INCH 7.75
13 FALSE PARAFFIN TEETH 1.1
90 LIGHTED LANTERNS 14.5
15 ASSORTED COSTUMES 10
9 CANDY CORN 1.35
6 PUMPKIN CANDY 1.45
87 PLASTIC SPIDERS 1.05
119 ASSORTED MASKS 4.95
1234 KEY CHAIN 5.95
9 rows selected.
Combinations of Comparison Operators
The equal operator can be combined with the less than and greater than operators.
The following example shows that salary is less than or equal to 20000:
WHERE SALARY <= '20000'
The next example shows that salary is greater than or equal to 20000:
WHERE SALARY >= '20000'
Less than or equal to 20000 includes 20000 and all values less than 20000. Any value in that range returns TRUE; any value greater than 20000 returns FALSE. Greater than or equal to also includes the value 20000 in this case and works the same as the <= operator.
SELECT *
FROM PRODUCTS_TBL
WHERE COST <= 29.99;
PROD_ID PROD_DESC COST
-----------------------------------------------
222 PLASTIC PUMPKIN 18 INCH 7.75
13 FALSE PARAFFIN TEETH 1.1
90 LIGHTED LANTERNS 14.5
15 ASSORTED COSTUMES 10
9 CANDY CORN 1.35
6 PUMPKIN CANDY 1.45
87 PLASTIC SPIDERS 1.05
119 ASSORTED MASKS 4.95
1234 KEY CHAIN 5.95
11235 WITCH COSTUME 29.99
10 rows selected.
Logical Operators
Logical operators are those operators that use SQL keywords to make comparisons instead of symbols. The logical operators covered in the following subsections are
• IS NULL
• BETWEEN
• IN
• LIKE
• EXISTS
• ALL and ANY
IS NULL
The NULL operator is used to compare a value with a NULL value. For example, you might look for employees who do not have a pager by searching for NULL values in the PAGER column of the EMPLOYEE_TBL table.
The following example compares a value to a NULL value; here, salary has no value:
WHERE SALARY IS NULL
The following example demonstrates finding all the employees from the EMPLOYEE table who do not have a pager:
SELECT EMP_ID, LAST_NAME, FIRST_NAME, PAGER
FROM EMPLOYEE_TBL
WHERE PAGER IS NULL;
EMP_ID LAST_NAM FIRST_NA PAGER
---------------------------------
311549902 STEPHENS TINA
442346889 PLEW LINDA
220984332 WALLACE MARIAH
443679012 SPURGEON TIFFANY
4 rows selected.
Understand that the literal word null is different from a NULL value. Examine the following example:
SELECT EMP_ID, LAST_NAME, FIRST_NAME, PAGER
FROM EMPLOYEE_TBL
WHERE PAGER = 'NULL';
no rows selected.
BETWEEN
The BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value. The minimum and maximum values are included as part of the conditional set.
The following example shows that salary must fall between 20000 and 30000, including the values 20000 and 30000:
WHERE SALARY BETWEEN '20000' AND '30000'
Did You Know?: Proper Use of Between
BETWEEN is inclusive and therefore includes the minimum and maximum values in the query results.
The following example shows all the products that cost between $5.95 and $14.50:
SELECT *
FROM PRODUCTS_TBL
WHERE COST BETWEEN 5.95 AND 14.5;
PROD_ID PROD_DESC COST
-----------------------------------------------
222 PLASTIC PUMPKIN 18 INCH 7.75
90 LIGHTED LANTERNS 14.5
15 ASSORTED COSTUMES 10
1234 KEY CHAIN 5.95
4 rows selected.
Notice that the values 5.95 and 14.5 are included in the output.
IN
The IN operator compares a value to a list of literal values that have been specified. For TRUE to be returned, the compared value must match at least one of the values in the list.
The following example shows that salary must match one of the values 20000, 30000, or 40000:
WHERE SALARY IN('20000', '30000', '40000')
The following example shows using the IN operator to match all the products that have a product ID within a certain range of values:
SELECT *
FROM PRODUCTS_TBL
WHERE PROD_ID IN ('13','9','87','119'),
PROD_ID PROD_DESC COST
-----------------------------------------------
119 ASSORTED MASKS 4.95
87 PLASTIC SPIDERS 1.05
9 CANDY CORN 1.35
13 FALSE PARAFFIN TEETH 1.1
4 rows selected.
Using the IN operator can achieve the same results as using the OR operator and can return the results more quickly.
LIKE
The LIKE operator is used to compare a value to similar values using wildcard operators. There are two wildcards used in conjunction with the LIKE operator:
• The percent sign (%)
• The underscore (_)
The percent sign represents zero, one, or multiple characters. The underscore represents a single number or character. The symbols can be used in combinations.
To find any values that start with 200:
WHERE SALARY LIKE '200%
To find any values that have 200 in any position:
WHERE SALARY LIKE '%200%'
To find any values that have 00 in the second and third positions:
WHERE SALARY LIKE '_00%'
To find any values that start with 2 and are at least three characters in length:
WHERE SALARY LIKE '2_%_%'
To find any values that end with 2:
WHERE SALARY LIKE '%2'
To find any values that have a 2 in the second position and end with a 3:
WHERE SALARY LIKE '_2%3'
To find any values in a five-digit number that start with 2 and end with 3:
WHERE SALARY LIKE '2___3'
The following example shows all product descriptions that end with the letter S in uppercase:
SELECT PROD_DESC
FROM PRODUCTS_TBL
WHERE PROD_DESC LIKE '%S';
PROD_DESC
-----------------
LIGHTED LANTERNS
ASSORTED COSTUMES
PLASTIC SPIDERS
ASSORTED MASKS
4 rows selected.
The following example shows all product descriptions whose second character is the letter S in uppercase:
SELECT PROD_DESC
FROM PRODUCTS_TBL
WHERE PROD_DESC LIKE '_S%';
PROD_DESC
-----------------
ASSORTED COSTUMES
ASSORTED MASKS
2 rows selected.
EXISTS
The EXISTS operator is used to search for the presence of a row in a specified table that meets certain criteria.
The following example searches to see whether the EMP_ID 3333333333 is in EMPLOYEE_TBL:
WHERE EXISTS (SELECT EMP_ID FROM EMPLOYEE_TBL WHERE EMPLOYEE_ID ='333333333')
The following example is a form of a subquery, which is further discussed during Hour 14, “Using Subqueries to Define Unknown Data”:
SELECT COST
FROM PRODUCTS_TBL
WHERE EXISTS ( SELECT COST
FROM PRODUCTS_TBL
WHERE COST > 100 );
No rows selected.
----------
There were no rows selected because no records existed where the cost was greater than 100.
Consider the following example:
SELECT COST
FROM PRODUCTS_TBL
WHERE EXISTS ( SELECT COST
FROM PRODUCTS_TBL
WHERE COST < 100 );
COST
-----------
29.99
7.75
1.1
14.5
10
1.35
1.45
1.05
4.95
5.95
59.99
11 rows selected.
The cost was displayed for records in the table because records existed where the product cost was less than 100.
ALL, SOME, and ANY Operators
The ALL operator is used to compare a value to all values in another value set.
The following example tests salary to see whether it is greater than all salaries of the employees living in Indianapolis:
WHERE SALARY > ALL SALARY (SELECT FROM EMPLOYEE_TBL WHERE CITY = 'INDIANAPOLIS')
The following example shows how the ALL operator is used in conjunction with subquery:
SELECT *
FROM PRODUCTS_TBL
WHERE COST > ALL ( SELECT COST
FROM PRODUCTS_TBL
WHERE COST < 10 );
PROD_ID PROD_DESC COST
-----------------------------------------------
11235 WITCH COSTUME 29.99
90 LIGHTED LANTERNS 14.5
15 ASSORTED COSTUMES 10
2345 OAK BOOKSHELF 59.99
4 rows selected.
In this output, five records had a cost greater than the cost of all records having a cost less than 10.
The ANY operator compares a value to any applicable value in the list according to the condition. SOME is an alias for ANY, so you can use them interchangeably.
The following example tests salary to see whether it is greater than any of the salaries of employees living in Indianapolis:
WHERE SALARY > ANY (SELECT SALARY FROM EMPLOYEE_TBL WHERE CITY = 'INDIANAPOLIS')
The following example shows the use of the ANY operator used in conjunction with a subquery:
SELECT *
FROM PRODUCTS_TBL
WHERE COST > ANY ( SELECT COST
FROM PRODUCTS_TBL
WHERE COST < 10 );
PROD_ID PROD_DESC COST
-----------------------------------------------
11235 WITCH COSTUME 29.99
222 PLASTIC PUMPKIN 18 INCH 7.75
13 FALSE PARAFFIN TEETH 1.1
90 LIGHTED LANTERNS 14.5
15 ASSORTED COSTUMES 10
9 CANDY CORN 1.35
6 PUMPKIN CANDY 1.45
119 ASSORTED MASKS 4.95
1234 KEY CHAIN 5.95
2345 OAK BOOKSHELF 59.99
10 rows selected.
In this output, more records were returned than when using ALL because the cost only had to be greater than any of the costs that were less than 10. The one record that was not displayed had a cost of 1.05, which was not greater than any of the values less than 10 (which was, in fact, 1.05). It should also be noted that ANY is not a synonym for IN because the IN operator can take an expression list of the form shown below, while ANY cannot:
IN (<Item#1>,<Item#2>,<Item#3>)
Additionally, the negation of IN, discussed in the section “Negative Operators,” would be NOT IN, and its alias would be <>ALL instead of <>ANY.
Conjunctive Operators
What if you want to use multiple conditions to narrow data in an SQL statement? You must be able to combine the conditions, and you would do this with conjunctive operators. These operators are
• AND
• OR
Conjunctive operators provide a means to make multiple comparisons with different operators in the same SQL statement. The following sections describe each operator’s behavior.
AND
The AND operator allows the existence of multiple conditions in an SQL statement’s WHERE clause. For an action to be taken by the SQL statement, whether it be a transaction or query, all conditions separated by the AND must be TRUE.
The following example shows that the EMPLOYEE_ID must match 333333333 and the salary must equal 20000:
WHERE EMPLOYEE_ID = '333333333' AND SALARY = '20000'
The following example shows the use of the AND operator to find the products with a cost between two limiting values:
SELECT *
FROM PRODUCTS_TBL
WHERE COST > 10
AND COST < 30;
PROD_ID PROD_DESC COST
------------------------------------------------
11235 WITCH COSTUME 29.99
90 LIGHTED LANTERNS 14.5
2 rows selected.
In this output, the value for cost had to be both greater than 10 and less than 30 for data to be retrieved.
This statement retrieves no data because each row of data has only one product identification:
SELECT *
FROM PRODUCTS_TBL
WHERE PROD_ID = '7725'
AND PROD_ID = '2345';
no rows selected
OR
The OR operator combines multiple conditions in an SQL statement’s WHERE clause. For an action to be taken by the SQL statement, whether it is a transaction or query, at least one of the conditions that are separated by OR must be TRUE.
The following example shows that salary must match either 20000 or 30000:
WHERE SALARY = '20000' OR SALARY = '30000'
The following example shows the use of the OR operator to limit a query on the PRODUCTS table:
SELECT *
FROM PRODUCTS_TBL
WHERE PROD_ID = '90'
OR PROD_ID = '2345';
PROD_ID PROD_DESC COST
------------------------------------------------
2345 OAK BOOKSHELF 59.99
90 LIGHTED LANTERNS 14.5
2 rows selected.
In this output, either one of the conditions had to be TRUE for data to be retrieved.
By the Way: Comparison Operators Can Be Stacked
Each of the comparison and logical operators can be used singularly or in combination with each other.
Two records that met either one or the other condition were found.
In the next example, notice the use of the AND and two OR operators. In addition, notice the logical placement of the parentheses to make the statement more readable.
SELECT *
FROM PRODUCTS_TBL
WHERE COST > 10
AND ( PROD_ID = '222'
OR PROD_ID = '90'
OR PROD_ID = '11235' );
PROD_ID PROD_DESC COST
------------------------------------------------
11235 WITCH COSTUME 29.99
90 LIGHTED LANTERNS 14.5
2 rows selected.
Did You Know?: Group Your Queries to Make Them Easily Understandable
When using multiple conditions and operators in an SQL statement, you might find that using parentheses to separate statements into logical groups improves overall readability. However, be aware that the misuse of parentheses could adversely affect your output results.
The cost in this output had to be greater than 10, and the product identification had to be any one of the three listed. A row was not returned for PROD_ID 222 because the cost for this identification was not greater than 10. Parentheses are not used just to make your code more readable but to ensure that logical grouping of conjunctive operators is evaluated properly. By default, operators are parsed from left to right in the order that they are listed. For example, you want to return all the products in a table whose cost is greater than 5 and whose PRODUCT_ID is in the range of values 222, 90, 11235, and 13. Try the following query to see the result set it returns:
SELECT *
FROM PRODUCTS_TBL
WHERE COST > 5
AND (PROD_ID = '222'
OR PROD_ID = '90'
OR PROD_ID = '11235'
OR PROD_ID = '13'),
PROD_ID PROD_DESC COST
11235 WITCH COSTUME 29.99
222 PLASTIC PUMPKIN 18 INCH 7.75
90 LIGHTED LANTERNS 14.50
3 rows in set
If you remove the parentheses, you can see how the result is much different:
SELECT *
FROM PRODUCTS_TBL
WHERE COST > 5
AND PROD_ID = '222'
OR PROD_ID = '90'
OR PROD_ID = '11235'
OR PROD_ID = '13';
PROD_ID PROD_DESC COST
11235 WITCH COSTUME 29.99
13 FALSE PARAFFIN TEETH 1.10
222 PLASTIC PUMPKIN 18 INCH 7.75
90 LIGHTED LANTERNS 14.50
3 rows in set
FALSE PARAFFIN TEETH gets returned now because this SQL query asks to return a PROD_ID equal to 222 and COST greater than 5 or any rows with PROD_ID equal to 90, 11235, or 13. Use parentheses properly within your WHERE clause to ensure that you are returning the correct logical result set. Otherwise, remember that your operators are evaluated in a certain order, which is normally from left to right.
Negative Operators
Of all the conditions tested by the logical operators discussed here, there is a way to negate each one of these operators to change the condition’s viewpoint.
The NOT operator reverses the meaning of the logical operator with which it is used. The NOT can be used with other operators to form the following methods:
• <>, != (NOT EQUAL)
• NOT BETWEEN
• NOT IN
• NOT LIKE
• IS NOT NULL
• NOT EXISTS
• NOT UNIQUE
Each method is discussed in the following sections. First, let’s look at how to test for inequality.
NOT EQUAL
You have learned how to test for inequality using the <> operator. Inequality is worth mentioning in this section because to test for it, you are actually negating the equality operator. Here we cover a second method for testing inequality available in some SQL implementations.
The following examples show that salary is not equal to 20000:
WHERE SALARY <> '20000'
WHERE SALARY != '20000'
In the second example, you can see that the exclamation mark negates the equality comparison. The use of the exclamation mark is allowed in addition to the standard operator for inequality <> in some implementations.
By the Way: Check Your Implementation
Check your particular implementation for the use of the exclamation mark to negate the inequality operator. The other operators mentioned are almost always the same if compared between different SQL implementations.
NOT BETWEEN
Watch Out!: Remember How BETWEEN Works
Remember that BETWEEN is inclusive; therefore, in the previous example, any rows that equal 5.95 or 14.50 are not included in the query results.
The BETWEEN operator is negated as follows:
WHERE Salary NOT BETWEEN '20000' AND '30000'
The value for salary cannot fall between 20000 and 30000 or include the values 20000 and 30000. Let’s see how this works on PRODUCTS_TBL:
SELECT *
FROM PRODUCTS_TBL
WHERE COST NOT BETWEEN 5.95 AND 14.5;
PROD_ID PROD_DESC COST
------------------------------------------------
11235 WITCH COSTUME 29.99
13 FALSE PARAFFIN TEETH 1.1
9 CANDY CORN 1.35
6 PUMPKIN CANDY 1.45
87 PLASTIC SPIDERS 1.05
119 ASSORTED MASKS 4.95
2345 OAK BOOKSHELF 59.99
7 rows selected.
NOT IN
The IN operator is negated as NOT IN. All salaries in the following example that are not in the listed values, if any, are returned:
WHERE SALARY NOT IN ('20000', '30000', '40000')
The following example demonstrates using the negation of the IN operator:
SELECT *
FROM PRODUCTS_TBL
WHERE PROD_ID NOT IN (119,13,87,9);
PROD_ID PROD_DESC COST
------------------------------------------------
11235 WITCH COSTUME 29.99
222 PLASTIC PUMPKIN 18 INCH 7.75
90 LIGHTED LANTERNS 14.5
15 ASSORTED COSTUMES 10
6 PUMPKIN CANDY 1.45
1234 KEY CHAIN 5.95
2345 OAK BOOKSHELF 59.99
7 rows selected.
In this output, records were not displayed for the listed identifications after the NOT IN operator.
NOT LIKE
The LIKE, or wildcard, operator is negated as NOT LIKE. When NOT LIKE is used, only values that are not similar are returned.
To find values that do not start with 200:
WHERE SALARY NOT LIKE '200%'
To find values that do not have 200 in any position:
WHERE SALARY NOT LIKE '%200%'
To find values that do not have 00 starting in the second position:
WHERE SALARY NOT LIKE '_00%'
To find values that do not start with 2 and have a length of 3 or greater:
WHERE SALARY NOT LIKE '2_%_%'
The following example demonstrates using the NOT LIKE operator to display a list of values:
SELECT PROD_DESC
FROM PRODUCTS_TBL
WHERE PROD_DESC NOT LIKE 'L%';
PROD_DESC
-----------------------
WITCH COSTUME
PLASTIC PUMPKIN 18 INCH
FALSE PARAFFIN TEETH
ASSORTED COSTUMES
CANDY CORN
PUMPKIN CANDY
PLASTIC SPIDERS
ASSORTED MASKS
KEY CHAIN
OAK BOOKSHELF
10 rows selected.
In this output, the product descriptions starting with the letter L were not displayed.
IS NOT NULL
The IS NULL operator is negated as IS NOT NULL to test for values that are not NULL. The following example only returns NOT NULL rows:
WHERE SALARY IS NOT NULL
The following example demonstrates using the IS NOT NULL operator to retrieve a list of employees whose page number is NOT NULL:
SELECT EMP_ID, LAST_NAME, FIRST_NAME, PAGER
FROM EMPLOYEE_TBL
WHERE PAGER IS NOT NULL;
EMP_ID LAST_NAM FIRST_NA PAGER
--------------------------------------
213764555 GLASS BRANDON 3175709980
313782439 GLASS JACOB 8887345678
2 rows selected.
NOT EXISTS
EXISTS is negated as NOT EXISTS.
The following example searches to see whether the EMP_ID 3333333333 is not in EMPLOYEE_TBL:
WHERE NOT EXISTS (SELECT EMP_ID FROM EMPLOYEE_TBL WHERE EMP_ID = '3333333333')
The following example demonstrates the use of the NOT EXISTS operator in conjunction with a subquery:
SELECT MAX(COST)
FROM PRODUCTS_TBL
WHERE NOT EXISTS ( SELECT COST
FROM PRODUCTS_TBL
WHERE COST > 100 );
MAX(COST)
----------
59.99
The maximum cost for the table is displayed in this output because no records contained a cost greater than 100.
Arithmetic Operators
Arithmetic operators perform mathematical functions in SQL—the same as in most other languages. The four conventional operators for mathematical functions are
• + (addition)
• - (subtraction)
• * (multiplication)
• / (division)
Addition
Addition is performed through the use of the plus (+) symbol.
The following example adds the SALARY column with the BONUS column for a total for each row of data:
SELECT SALARY + BONUS FROM EMPLOYEE_PAY_TBL;
This example returns all rows where the total of the SALARY and BONUS columns together is greater than 40000:
SELECT SALARY FROM EMPLOYEE_PAY_TBL WHERE SALARY + BONUS > '40000';
Subtraction
Subtraction is performed using the minus (-) symbol.
The following example subtracts the BONUS column from the SALARY column for the difference:
SELECT SALARY - BONUS FROM EMPLOYEE_PAY_TBL;
This example returns all rows where the SALARY minus the BONUS is greater than 40000:
SELECT SALARY FROM EMPLOYEE_PAY_TBL WHERE SALARY - BONUS > '40000';
Multiplication
Multiplication is performed by using the asterisk (*) symbol.
The following example multiplies the SALARY column by 10:
SELECT SALARY * 10 FROM EMPLOYEE_PAY_TBL;
The next example returns all rows where the product of the SALARY multiplied by 10 is greater than 40000:
SELECT SALARY FROM EMPLOYEE_PAY_TBL WHERE SALARY * 10 > '40000';
The pay rate in the following example is multiplied by 1.1, which increases the current pay rate by 10%:
SELECT EMP_ID, PAY_RATE, PAY_RATE * 1.1
FROM EMPLOYEE_PAY_TBL
WHERE PAY_RATE IS NOT NULL;
EMP_ID PAY_RATE PAY_RATE*1.1
---------------------------------
442346889 14.75 16.225
220984332 11 12.1
443679012 15 16.5
3 rows selected.
Division
Division is performed through the use of the slash (/) symbol.
The following example divides the SALARY column by 10:
SELECT SALARY / 10 FROM EMPLOYEE_PAY_TBL;
This example returns all rows that are greater than 40000:
SELECT SALARY FROM EMPLOYEE_PAY_TBL WHERE SALARY > '40000';
This example returns all rows where the salary divided by 10 is greater than 40000:
SELECT SALARY FROM EMPLOYEE_PAY_TBL WHERE (SALARY / 10) > '40000';
Arithmetic Operator Combinations
You can use the arithmetic operators in combination with one another. Remember the rules of precedence in basic mathematics. Multiplication and division operations are performed first, and then addition and subtraction operations. The only way the user has control over the order of the mathematical operations is through the use of parentheses. Parentheses surrounding an expression cause that expression to be evaluated as a block.
Precedence is the order in which expressions are resolved in a mathematical expression or with embedded functions in SQL. The table that follows shows some simple examples of how operator precedence can affect the outcome of a calculation:
In the following examples, notice that the placement of parentheses in an expression does not affect the outcome if only multiplication and division are involved. Precedence is not a factor in these cases. Although it might not appear to make sense, it is possible that some implementations of SQL do not follow the ANSI standard in cases like this; however, this is unlikely.
Watch Out!: Ensure That Your Math Is Correct
When combining arithmetic operators, remember to consider the rules of precedence. The absence of parentheses in a statement could render inaccurate results. Although the syntax of an SQL statement is correct, a logical error might result.
The following are some more examples:
SELECT SALARY * 10 + 1000
FROM EMPLOYEE_PAY_TBL
WHERE SALARY > 20000;
SELECT SALARY / 52 + BONUS
FROM EMPLOYEE_PAY_TBL;
SELECT (SALARY - 1000 + BONUS) / 52 * 1.1
FROM EMPLOYEE_PAY_TBL;
And here’s a rather wild example:
SELECT SALARY
FROM EMPLOYEE_PAY_TBL
WHERE SALARY < BONUS * 3 + 10 / 2 - 50;
Because parentheses are not used, mathematical precedence takes effect, altering the value for BONUS tremendously for the condition.
Summary
You have been introduced to various operators available in SQL. You have learned the hows and whys of operators. You have also seen examples of operators being used by themselves and in various combinations with one another, using the conjunctive-type operators AND and OR. You have learned the basic arithmetic functions: addition, subtraction, multiplication, and division. Comparison operators test equality, inequality, less than values, and greater than values. Logical operators include BETWEEN, IN, LIKE, EXISTS, ANY, and ALL. You are already experiencing how elements are added to SQL statements to further specify conditions and better control the processing and retrieving capabilities provided with SQL.
Q&A
Q. Can I have more than one AND in the WHERE clause?
A. Yes. In fact, you can use all the operators multiple times. An example would be
SELECT SALARY
FROM EMPLOYEE_PAY_TBL
WHERE SALARY > 20000
AND BONUS BETWEEN 1000 AND 3000
AND POSITION = 'VICE PRESIDENT'
Q. What happens if I use single quotation marks around a NUMBER data type in a WHERE clause?
A. Your query still processes. Quotation marks are not necessary for NUMBER fields.
Workshop
The following workshop is composed of a series of quiz questions and practical exercises. The quiz questions are designed to test your overall understanding of the current material. The practical exercises are intended to afford you the opportunity to apply the concepts discussed during the current hour, as well as build upon the knowledge acquired in previous hours of study. Please take time to complete the quiz questions and exercises before continuing. Refer to Appendix C, “Answers to Quizzes and Exercises,” for answers.
Quiz
1. True or false: Both conditions when using the OR operator must be TRUE.
2. True or false: All specified values must match when using the IN operator.
3. True or false: The AND operator can be used in the SELECT and the WHERE clauses.
4. True or false: The ANY operator can accept an expression list.
5. What is the logical negation of the IN operator?
6. What is the logical negation of the ANY and ALL operators?
7. What, if anything, is wrong with the following SELECT statements?
a. SELECT SALARY
FROM EMPLOYEE_PAY_TBL
WHERE SALARY BETWEEN 20000, 30000
b. SELECT SALARY + DATE_HIRE
FROM EMPLOYEE_PAY_TBL
c. SELECT SALARY, BONUS
FROM EMPLOYEE_PAY_TBL
WHERE DATE_HIRE BETWEEN 2009-09-22
AND 2009-11-23
AND POSITION = 'SALES'
OR POSITION = 'MARKETING'
AND EMPLOYEE_ID LIKE '%55%
Exercises
1. Using the following CUSTOMER_TBL, write a SELECT statement that returns customer IDs and customer names (alpha order) for customers who live in Indiana, Ohio, Michigan, or Illinois and whose names begin with the letters A or B:
DESCRIBE CUSTOMER_TBL;
Name Null? Type
------------------------------------------------------
CUST_ID NOT NULL VARCHAR (10)
CUST_NAME NOT NULL VARCHAR (30)
CUST_ADDRESS NOT NULL VARCHAR (20)
CUST_CITY NOT NULL VARCHAR (12)
CUST_STATE NOT NULL VARCHAR (2)
CUST_ZIP NOT NULL VARCHAR (5)
CUST_PHONE VARCHAR (10)
CUST_FAX VARCHAR (10)
2. Using the following PRODUCTS_TBL, write a SELECT statement that returns the product ID, product description, and product cost. Limit the product cost to between $1.00 and $12.50:
DESCRIBE PRODUCTS_TBL
Name Null? Type
-----------------------------------------------------
PROD_ID NOT NULL VARCHAR (10)
PROD_DESC NOT NULL VARCHAR (25)
COST NOT NULL DECIMAL(6,2)
3. Assuming that you used the BETWEEN operator in Exercise 2, rewrite your SQL statement to achieve the same results using different operators. If you did not use the BETWEEN operator, do so now.
4. Write a SELECT statement that returns products that are either less than 1.00 or greater than 12.50. There are two ways to achieve the same results.
5. Write a SELECT statement that returns the following information from PRODUCTS_TBL: product description, product cost, and 5% sales tax for each product. List the products in order from most to least expensive.
6. Write a SELECT statement that returns the following information from PRODUCTS_TBL: product description, product cost, 5% sales tax for each product, and total cost with sales tax. List the products in order from most to least expensive. There are two ways to achieve the same results. Try both.
7. Pick three items from the PRODUCTS_TBL. Now write a query to return the rows of data from the table associated with those three items. Now rewrite the query to return everything but those three items. For your query use combinations of equality operators and conjunctive operators.
8. Rewrite the queries you wrote in Exercise 7 using the IN operator. Which statement is more efficient? Which one is more readable?
9. Write a query to return all the products that start with the letter P. Now write a query to return all products that do not start with the letter P.