• 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.
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 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.
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.
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'
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.
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.
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 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
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.
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'
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.
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.
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.
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
.
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
.
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.
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
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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 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 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 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 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 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';
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.
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.
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. 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.
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.
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%
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.
18.227.79.241