What You’ll Learn in This Hour:
▶ Defining operators
▶ Understanding operators in SQL
▶ Using operators singularly
▶ Using operators in combinations
Operators are used with the SELECT command’s WHERE clause to place extended constraints on data that a query returns. Various operators are available to SQL users that support all data querying needs. This hour shows you what operators are available for you to use and how to utilize them properly within the WHERE clause.
An operator is a reserved word or character used primarily in a SQL statement’s WHERE clause to perform operation(s) such as comparisons and arithmetic operations. Operators specify conditions in a SQL statement and serve as conjunctions for multiple conditions in a statement.
The operators discussed during this hour are listed here:
▶ Comparison operators
▶ Logical operators
▶ Operators used to negate conditions
▶ Arithmetic operators
Comparison operators test single values in a SQL statement. The comparison operators discussed consist of =, <>, <, and >.
These operators are used to test the following conditions:
▶ Equality
▶ Non-equality
▶ Less than
▶ Greater than
These comparison operators, including examples, are covered in the following sections.
The equal operator compares single values in a SQL statement. The equals 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) determines 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 hour that character data comparisons can either be case sensitive or not case sensitive, depending on how your relational database management system (RDBMS) is set up. Make sure you understand exactly how the query engine compares your values.
The following example shows that WINGSPAN is equal to 48:
WHERE WINGSPAN = 48
The following query returns the bird’s name and wingspan of any birds that have a wingspan of exactly 48 inches.
SQL> select bird_name, wingspan 2 from birds 3 where wingspan = 48; BIRD_NAME WINGSPAN ------------------------------ ---------- Red Tailed Hawk 48 1 row selected.
For every equality, multiple non-equalities exist. 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 WINGSPAN is not equal to 48:
WHERE WINGSPAN<>400
Tip
Options for Non-Equality
Another option for non-equality 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 query is similar to the previous one, but it is looking at non-equality: The output returns any birds that do not have a wingspan of exactly 48 inches.
SQL> select bird_name, wingspan 2 from birds 3 where wingspan <> 48; BIRD_NAME WINGSPAN ------------------------------ ---------- Great Blue Heron 78 Mallard 3.2 Common Loon 54 Bald Eagle 84 Golden Eagle 90 Osprey 72 Belted Kingfisher 23 Canadian Goose 72 Pied-billed Grebe 6.5 American Coot 29 Common Sea Gull 18 Ring-billed Gull 50 Double-crested Cormorant 54 Common Merganser 34 Turkey Vulture 72 American Crow 39.6 Green Heron 26.8 Mute Swan 94.8 Brown Pelican 90 Great Egret 67.2 Anhinga 42 Black Skimmer 15 22 rows selected.
Again, remember that your collation and whether your specific system is set up as case sensitive play a critical role in these comparisons. If your system is case sensitive, then OSPREY, Osprey, and osprey are considered different values, which might not be your intention.
Two of the most widely used comparison operators are greater than and less than. Greater than works the opposite of less than. You can use the symbols < (less than) and > (greater than) by themselves or in combination with each other or other operators to perform a comparison of non-null values. The results of both are a Boolean value that shows whether the comparison is accurate.
The following examples show that WINGSPAN is less than or greater than 48:
WHERE WINGSPAN < 400 WHERE WINGSPAN > 400
The following query returns any birds that have a wingspan that is greater than 72 inches.
SQL> select bird_name, wingspan 2 from birds 3 where wingspan > 72; BIRD_NAME WINGSPAN ------------------------------ ---------- Great Blue Heron 78 Bald Eagle 84 Golden Eagle 90 Mute Swan 94.8 Brown Pelican 90 5 rows selected.
In the next example, the result set returns any birds that have a wingspan less than 72 inches. Notice that some bird records are not included between the two queries. This is because the less than operator is not inclusive of the value it is compared against.
SQL> select bird_name, wingspan 2 from birds 3 where wingspan < 72; BIRD_NAME WINGSPAN ------------------------------ ---------- Mallard 3.2 Common Loon 54 Red Tailed Hawk 48 Belted Kingfisher 23 Pied-billed Grebe 6.5 American Coot 29 Common Sea Gull 18 Ring-billed Gull 50 Double-crested Cormorant 54 Common Merganser 34 American Crow 39.6 Green Heron 26.8 Great Egret 67.2 Anhinga 42 Black Skimmer 15 15 rows selected.
The equal operator can be combined with the less than and greater than operators to have them include the value they are compared against.
The following example shows that WINGSPAN is less than or equal to 400:
WHERE WINGSPAN <= 400
The next example shows that WINGSPAN is greater than or equal to 400:
WHERE WINGSPAN >= 400
“Less than or equal to 48” includes 48 and all values less than 48. Any value in that range returns TRUE; any value greater than 48 returns FALSE. “Greater than or equal to” also includes the value 48 in this case and works the same as the <= operator. The following example demonstrates how to use the combined operator to find all birds that have a wingspan of 72 or more. Notice the three records in the result set that were not included previously.
SQL> select bird_name, wingspan 2 from birds 3 where wingspan >= 72; BIRD_NAME WINGSPAN ------------------------------ ---------- Great Blue Heron 78 Bald Eagle 84 Golden Eagle 90 Osprey 72 Canadian Goose 72 Turkey Vulture 72 Mute Swan 94.8 Brown Pelican 90 8 rows selected.
Logical operators are operators that use SQL keywords instead of symbols to make comparisons. Following are the logical operators in SQL, which are covered in the following subsections:
▶ IS NULL
▶ BETWEEN
▶ IN
▶ LIKE
▶ EXISTS
▶ UNIQUE
▶ ALL, SOME, and ANY
IS NULLThe IS NULL operator compares a value with a NULL value. For example, you might look for birds that do not have a wingspan entered by searching for NULL values in the WINGSPAN column of the BIRDS table.
The following example compares a value to a NULL value; here, WINGSPAN has no value:
WHERE WINGSPAN IS NULL
The following example demonstrates finding all the birds from the BIRDS table that do not have a wingspan listed in the table. First, the overall result set from the BIRDS table is shown. You can see here that some of the values for WINGSPAN have been temporarily removed from this table, to better illustrate the IS NULL operator.
SQL> select bird_name, wingspan 2 from birds; BIRD_NAME WINGSPAN ------------------------------ ---------- Great Blue Heron 78 Mallard Common Loon 54 Bald Eagle 84 Golden Eagle 90 Red Tailed Hawk 48 Osprey 72 Belted Kingfisher Canadian Goose 72 Pied-billed Grebe American Coot Common Sea Gull Ring-billed Gull 50 Double-crested Cormorant 54 Common Merganser Turkey Vulture 72 American Crow Green Heron Mute Swan 94.8 Brown Pelican 90 Great Egret 67.2 Anhinga Black Skimmer 23 rows selected. SQL> select bird_name, wingspan 2 from birds 3 where wingspan is null; BIRD_NAME WINGSPAN ------------------------------ ---------- Mallard Belted Kingfisher Pied-billed Grebe American Coot Common Sea Gull Common Merganser American Crow Green Heron Anhinga Black Skimmer 10 rows selected.
The literal word null is different from a NULL value. Examine the following example and observe that you cannot interchange the string value 'NULL' because it does not mean the same as a NULL value:
SQL> update birds
2 set wingspan = ''
3 where bird_name = 'American Coot';
1 row updated.
SQL>
SQL> select bird_name
2 from birds
3 where wingspan is null;
BIRD_NAME
------------------------------
American Coot
1 row selected.
SQL> select bird_name
2 from birds
3 where wingspan = 'null';
where wingspan = 'null'
*
ERROR at line 3:
ORA-01722: invalid number
BETWEENThe BETWEEN operator searches 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 wingspans must fall between 48 and 90, including the values 48 and 90:
WHERE WINGSPAN BETWEEN 48 AND 90
Tip
Proper Use of BETWEEN
BETWEEN is inclusive and, therefore, includes the minimum and maximum values in the query results.
The following example shows birds that have a wingspan between 48 and 90 inches:
SQL> select bird_name, wingspan 2 from birds 3 where wingspan between 48 and 90; BIRD_NAME WINGSPAN ------------------------------ ---------- Great Blue Heron 78 Common Loon 54 Bald Eagle 84 Golden Eagle 90 Red Tailed Hawk 48 Osprey 72 Canadian Goose 72 Ring-billed Gull 50 Double-crested Cormorant 54 Turkey Vulture 72 Brown Pelican 90 Great Egret 67.2 12 rows selected.
Notice that the value 90 is included in the output.
INThe 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 the migration location must match one of the values Mexico or South America:
WHERE MIGRATION_LOCATION IN ('Mexico', 'South America')
The following example uses the IN operator to match all the migration locations that have a location name within a certain range of values (the first query shows all values in the MIGRATION table, for comparison purposes):
SQL> select * from migration;
MIGRATION_ID MIGRATION_LOCATION
------------ ------------------------------
1 Southern United States
2 Mexico
3 Central America
4 South America
5 No Significant Migration
6 Partial, Open Water
6 rows selected.
SQL> select migration_location
2 from migration
3 where migration_location in ('Mexico', 'South America');
MIGRATION_LOCATION
------------------------------
Mexico
South America
2 rows selected.
Using the IN operator achieves the same results as using the OR operator, but it returns the results more quickly because it is optimized in the database.
LIKEThe LIKE operator compares a value to similar values using wildcard operators. Two wildcards are used with the LIKE operator:
▶ The percent sign (%)
▶ The underscore (_)
The following example shows that two locations have the word America in the migration location name:
SQL> select migration_location 2 from migration 3 where migration_location like '%America%'; MIGRATION_LOCATION ------------------------------ Central America South America 2 rows selected.
The following example shows that only one bird has a nickname that starts with the word eagle:
SQL> select nickname 2 from nicknames 3 where nickname like 'Eagle%'; NICKNAME ------------------------------ Eagle 1 row selected.
The following example shows that three nicknames have the word eagle anywhere in the nickname:
SQL> select nickname 2 from nicknames 3 where nickname like '%Eagle%'; NICKNAME ------------------------------ Eagle Sea Eagle War Eagle 3 rows selected.
The final example shows three nicknames that end with the word eagle:
SQL> select nickname 2 from nicknames 3 where nickname like '%Eagle'; NICKNAME ------------------------------ Eagle Sea Eagle War Eagle 3 rows selected.
EXISTSThe EXISTS operator searches for the presence of a row in a specified table that meets certain criteria.
The following query returns the birds that have a nickname with the word eagle somewhere in it:
SQL> select bird_id, bird_name
2 from birds
3 where exists (select bird_id
4 from nicknames
5 where birds.bird_id = nicknames.bird_id
6 and nicknames.nickname like '%Eagle%');
BIRD_ID BIRD_NAME
---------- ------------------------------
4 Bald Eagle
5 Golden Eagle
2 rows selected.
ALL, SOME, and ANYThe ALL operator compares a value to all values in another value set.
The following example tests WINGSPAN to see whether it is greater than the seating wingspan of the Bald Eagle:
where wingspan > ALL (select wingspan
from birds
where bird_name = 'Bald Eagle')
The following example shows how the ALL operator is used with a subquery:
SQL> select bird_name, wingspan 2 from birds 3 where wingspan > ALL (select wingspan 4 from birds 5 where bird_name = 'Bald Eagle'); BIRD_NAME WINGSPAN ------------------------------ ---------- Brown Pelican 90 Golden Eagle 90 Mute Swan 94.8 3 rows selected.
In this output, three birds have a wingspan that is greater than that of the bald eagle.
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 WINGSPAN to see whether it is greater than any of the wingspans of birds greater than 48 inches:
where wingspan > ANY (select wingspan
from birds
where wingspan > 48);
The following example shows the ANY operator used with a subquery:
SQL> select bird_name, wingspan 2 from birds 3 where wingspan > ANY (select wingspan 4 from birds 5 where wingspan > 48); BIRD_NAME WINGSPAN ------------------------------ ---------- Mute Swan 94.8 Brown Pelican 90 Golden Eagle 90 Bald Eagle 84 Great Blue Heron 78 Osprey 72 Canadian Goose 72 Turkey Vulture 72 Great Egret 67.2 Common Loon 54 Double-crested Cormorant 54 11 rows selected.
Suppose you want to use multiple conditions to narrow data in a SQL statement. You must be able to combine the conditions. You can do this with the following conjunctive operators:
▶ 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.
ANDThe AND operator allows the existence of multiple conditions in a SQL statement’s WHERE clause. For an action to be taken by the SQL statement, whether it be a transaction or a query, all conditions separated by the AND must be TRUE.
The following example shows that the WINGSPAN must be between the values of 48 and 90:
WHERE WINGSPAN > 48 AND WINGSPAN < 90
The following example shows the use of the AND operator to find the birds with a wingspan between two limiting values:
SQL> select bird_name, height, weight, wingspan 2 from birds 3 where wingspan > 48 4 and wingspan < 90; BIRD_NAME HEIGHT WEIGHT WINGSPAN ------------------------------ ---------- ---------- ---------- Great Blue Heron 52 5.5 78 Common Loon 36 18 54 Bald Eagle 37 14 84 Osprey 24 3 72 Canadian Goose 43 14 72 Ring-billed Gull 19 1.1 50 Double-crested Cormorant 33 5.5 54 Turkey Vulture 32 3.3 72 Great Egret 38 3.3 67.2 9 rows selected.
In this output, the value for WINGSPAN must be both greater than 48 and less than 90 for data to be retrieved.
This statement retrieves no data because with the AND operator, both conditions must be true for data to be returned; here, it is impossible for a bird to have a wingspan that is both less than 48 inches and greater than 90 inches.
SQL> select bird_name, height, weight, wingspan 2 from birds 3 where wingspan < 48 4 and wingspan > 90; no rows selected
ORThe OR operator combines multiple conditions in a SQL statement’s WHERE clause. For an action to be taken by the SQL statement, whether it is a transaction or a query, at least one of the conditions that are separated by OR must be TRUE.
The following example shows that WINGSPAN must match either greater than 48 inches or less than 90 inches.
WHERE WINGSPAN > 48 AND WINGSPAN < 90
The following example shows the use of the OR operator to limit a query on the BIRDS table:
SQL> select bird_name, height, weight, wingspan 2 from birds 3 where wingspan > 48 4 or wingspan < 90; BIRD_NAME HEIGHT WEIGHT WINGSPAN ------------------------------ ---------- ---------- ---------- Great Blue Heron 52 5.5 78 Common Loon 36 18 54 Bald Eagle 37 14 84 Golden Eagle 40 15 90 Red Tailed Hawk 25 2.4 48 Osprey 24 3 72 Canadian Goose 43 14 72 Ring-billed Gull 19 1.1 50 Double-crested Cormorant 33 5.5 54 Turkey Vulture 32 3.3 72 Mute Swan 60 26 94.8 Brown Pelican 54 7.6 90 Great Egret 38 3.3 67.2 13 rows selected.
In this output, either one of the conditions must be TRUE for data to be retrieved.
SQL> select bird_name, height, weight, wingspan 2 from birds 3 where wingspan > 70 4 and height < 60 5 and weight < 18; BIRD_NAME HEIGHT WEIGHT WINGSPAN ------------------------------ ---------- ---------- ---------- Great Blue Heron 52 5.5 78 Bald Eagle 37 14 84 Golden Eagle 40 15 90 Osprey 24 3 72 Canadian Goose 43 14 72 Turkey Vulture 32 3.3 72 Brown Pelican 54 7.6 90 7 rows selected.
Note
Comparison Operators Can Be Stacked
Each of the comparison and logical operators can be used singularly or in combination with each other. This can be important in modeling complex statements, where you test for several different criteria. Utilizing AND and OR statements to stack both comparison and logical operators becomes an important tool in getting correct query results.
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.
SQL> select bird_name, height, weight, wingspan 2 from birds 3 where bird_name like '%Eagle%' 4 and (wingspan > 85 5 or height > 36); BIRD_NAME HEIGHT WEIGHT WINGSPAN ------------------------------ ---------- ---------- ---------- Bald Eagle 37 14 84 Golden Eagle 40 15 90 2 rows selected.
Tip
Group Your Queries to Make Them Easily Understandable
When using multiple conditions and operators in a SQL statement, you might find that using parentheses to separate statements into logical groups improves overall readability. However, be aware misusing parentheses can adversely affect your output results.
If you remove the parentheses, the result is much different, as you can see in the following example:
SQL> select bird_name, height, weight, wingspan 2 from birds 3 where bird_name like '%Eagle%' 4 and wingspan > 85 5 or height > 36; BIRD_NAME HEIGHT WEIGHT WINGSPAN ------------------------------ ---------- ---------- ---------- Great Blue Heron 52 5.5 78 Bald Eagle 37 14 84 Golden Eagle 40 15 90 Canadian Goose 43 14 72 Mute Swan 60 26 94.8 Brown Pelican 54 7.6 90 Great Egret 38 3.3 67.2 7 rows selected.
More rows of data are returned in the results of this query than in the previous query. This is because, without proper placement of parentheses, the OR operator allows for data to be returned if any, not all, of the conditions are met. Anything within the parentheses is treated as one condition, and conditions within the innermost parentheses are resolved first. 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.
Each logical operator can be negated, to change the tested condition’s viewpoint.
The NOT operator reverses the meaning of the logical operator with which it is used. 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.
NOT EQUALEarlier this hour, you learned how to test for inequality using the <> operator. To test for inequality, you negate the equality operator. Here you can see a second method for testing inequality that is available in some SQL implementations.
The following examples show that WINGSPAN is not equal to 48:
WHERE WINGSPAN <> 48 WHERE WINGSPAN != 48
In the second example, you can see that the exclamation mark negates the equality comparison. Some implementations allow the use of the exclamation mark in addition to the standard operator for inequality <>.
Note
Check Your Implementation
Check your particular implementation for the validity of using the exclamation mark to negate the inequality operator. The other operators mentioned are almost always the same when compared among different SQL implementations.
NOT BETWEENThe BETWEEN operator is negated with the NOT operator as follows:
WHERE WINGSPAN NOT BETWEEN 30 AND 60
The value for WINGSPAN cannot fall between 30 and 60 or include the values 30 and 60. Now let’s see how this works on the BIRDS table:
SQL> select bird_name, wingspan 2 from birds 3 where wingspan not between 30 and 60; BIRD_NAME WINGSPAN ------------------------------ ---------- Great Blue Heron 78 Bald Eagle 84 Golden Eagle 90 Osprey 72 Canadian Goose 72 Turkey Vulture 72 Mute Swan 94.8 Brown Pelican 90 Great Egret 67.2 9 rows selected.
NOT INThe IN operator is negated as NOT IN. All migration locations in the following example that are not in the listed values, if any, are returned:
where migration_location not in ('Mexico', 'South America')
The following example demonstrates using the negation of the IN operator:
SQL> select *
2 from migration
3 where migration_location not in ('Mexico', 'South America');
MIGRATION_ID MIGRATION_LOCATION
------------ ------------------------------
3 Central America
5 No Significant Migration
6 Partial, Open Water
1 Southern United States
4 rows selected.
In this output, records are not displayed for the listed identifications after the NOT IN operator.
NOT LIKEThe LIKE, or wildcard, operator is negated as NOT LIKE. When NOT LIKE is used, only values that are not similar are returned.
The following example demonstrates using the NOT LIKE operator to display a list of values:
SQL> select bird_name 2 from big_birds 3 where bird_name not like '%Eagle%'; BIRD_NAME ------------------------------ Great Blue Heron Common Loon Osprey Canadian Goose Ring-billed Gull Double-crested Cormorant Turkey Vulture Mute Swan Brown Pelican Great Egret 10 rows selected.
In this output, birds that have the word eagle anywhere in their name are not displayed.
IS NOT NULLThe IS NULL operator is negated as IS NOT NULL to test for values that are not NULL. The following example returns only NOT NULL rows:
WHERE WINGSPAN IS NOT NULL
The following example demonstrates using the IS NOT NULL operator to retrieve a list of birds whose WINGSPAN is NOT NULL:
SQL> select bird_name, wingspan 2 from big_birds 3 where wingspan is not null; BIRD_NAME WINGSPAN ------------------------------ ---------- Great Blue Heron 78 Common Loon 54 Bald Eagle 84 Golden Eagle 90 Osprey 72 Canadian Goose 72 Ring-billed Gull 50 Double-crested Cormorant 54 Turkey Vulture 72 Mute Swan 94.8 Brown Pelican 90 Great Egret 67.2 12 rows selected.
NOT EXISTSEXISTS is negated as NOT EXISTS.
The following example demonstrates the use of the NOT EXISTS operator with a subquery by returning only birds in the BIG_BIRDS table that do not have a nickname that has the word eagle in it:
SQL> select bird_id, bird_name
2 from big_birds
3 where not exists (select bird_id
4 from nicknames
5 where big_birds.bird_id = nicknames.bird_id
6 and nicknames.nickname like '%Eagle%');
BIRD_ID BIRD_NAME
---------- ------------------------------
14 Double-crested Cormorant
1 Great Blue Heron
7 Osprey
21 Great Egret
3 Common Loon
9 Canadian Goose
20 Brown Pelican
13 Ring-billed Gull
19 Mute Swan
16 Turkey Vulture
10 rows selected.
Arithmetic operators perform mathematical functions in SQL—the same as in most other languages. The four conventional operators for mathematical functions are listed here:
▶ + (addition)
▶ - (subtraction)
▶ * (multiplication)
▶ / (division)
Addition is performed by using the plus (+) symbol.
The following example begins by selecting the BIRD_NAME, EGGS, INCUBATION, and FLEDGING columns from the SMALL_BIRDS table, which was previously created based on the BIRDS table.
SQL> select bird_name, eggs, incubation, fledging 2 from small_birds; BIRD_NAME EGGS INCUBATION FLEDGING ------------------------------ ---------- ---------- ---------- Mallard 10 30 52 Red Tailed Hawk 3 35 46 Belted Kingfisher 7 24 24 Pied-billed Grebe 7 24 24 American Coot 12 25 52 Common Sea Gull 3 28 36 Common Merganser 11 33 80 American Crow 6 18 35 Green Heron 4 25 36 Anhinga 4 30 42 Black Skimmer 5 25 30 11 rows selected.
The following query from the SMALL_BIRDS table returns only birds that have a combined incubation and fledging period greater than 60 days combined.
SQL> select bird_name, eggs, incubation, fledging 2 from small_birds 3 where incubation + fledging > 60; BIRD_NAME EGGS INCUBATION FLEDGING ------------------------------ ---------- ---------- ---------- Mallard 10 30 52 Red Tailed Hawk 3 35 46 American Coot 12 25 52 Common Sea Gull 3 28 36 Common Merganser 11 33 80 Green Heron 4 25 36 Anhinga 4 30 42 7 rows selected.
Subtraction is performed using the minus (-) symbol.
This example returns any birds for which the difference between the fledging and incubation periods is less than 30 days:
SQL> select bird_name, eggs, incubation, fledging 2 from small_birds 3 where fledging - incubation > 30; BIRD_NAME EGGS INCUBATION FLEDGING ------------------------------ ---------- ---------- ---------- Common Merganser 11 33 80 1 row selected.
Multiplication is performed using the asterisk (*) symbol.
The next example returns any birds from the SMALL_BIRDS table that have a total number of eggs per year greater than or equal to 10. The total number of eggs per year is derived by multiplying the number of broods per year by the number of eggs per brood.
SQL> select bird_name, eggs, broods 2 from small_birds 3 where eggs * broods >= 10; BIRD_NAME EGGS BROODS ------------------------------ ---------- ---------- Mallard 10 1 American Coot 12 1 Common Merganser 11 1 3 rows selected.
The example that follows shows the arithmetic in the SELECT clause, not the WHERE clause. This query returns any small birds and shows the number of eggs they lay, the number of broods per year, and the estimated eggs per year, based on a calculation.
SQL> select bird_name "SMALL BIRD", eggs, broods, eggs * broods "EGGS PER YEAR" 2 from small_birds; SMALL BIRD EGGS BROODS EGGS PER YEAR ---------------------------- ---------- ---------- ------------- Mallard 10 1 10 Red Tailed Hawk 3 1 3 Belted Kingfisher 7 1 7 Pied-billed Grebe 7 1 7 American Coot 12 1 12 Common Sea Gull 3 1 3 Common Merganser 11 1 11 American Crow 6 1 6 Green Heron 4 2 8 Anhinga 4 1 4 Black Skimmer 5 1 5 11 rows selected.
Division is performed by using the slash (/) symbol.
This example starts by showing a query that lists the birds and the number of days spent with their young, which is derived by a calculation in the SELECT clause.
SQL> select bird_name, incubation + fledging "DAYS SPENT WITH YOUNG" 2 from small_birds; BIRD_NAME DAYS SPENT WITH YOUNG ------------------------------ --------------------- Mallard 82 Red Tailed Hawk 81 Belted Kingfisher 48 Pied-billed Grebe 48 American Coot 77 Common Sea Gull 64 Common Merganser 113 American Crow 53 Green Heron 61 Anhinga 72 Black Skimmer 55 11 rows selected.
Finally, the following query uses a calculation to estimate the average days spent with each young individually, based on the previous query.
SQL> select bird_name, 2 (incubation + fledging) / (eggs * broods) "AVG DAYS SPENT WITH EACH YOUNG" 3 from small_birds; BIRD_NAME AVG DAYS SPENT WITH EACH YOUNG ------------------------------ ------------------------------ Mallard 8.2 Red Tailed Hawk 27 Belted Kingfisher 6.85714286 Pied-billed Grebe 6.85714286 American Coot 6.41666667 Common Sea Gull 21.3333333 Common Merganser 10.2727273 American Crow 8.83333333 Green Heron 7.625 Anhinga 18 Black Skimmer 11 11 rows selected.
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, then addition and subtraction operations. The only way the user has control over the order of the mathematical operations is to use 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 following table shows some simple examples of how operator precedence can affect the outcome of a calculation:
Expression |
Result |
1 + 1 * 5 |
6 |
(1 + 1) * 5 |
10 |
10 – 4 / 2 +1 |
9 |
(10 – 4) / (2 + 1) |
2 |
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.
Caution
Make Sure Your Math Is Correct
When combining arithmetic operators, remember to consider the rules of precedence. The absence of parentheses in a statement can render inaccurate results. Although the syntax of a SQL statement is correct, a logical error might result.
This hour introduced you to various operators available in SQL. You learned the hows and whys of operators, and you saw examples of operators used both by themselves and in various combinations with one another, using the conjunctive-type operators AND and OR. You 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 can now see how elements are added to SQL statements to further specify conditions and better control the processing and retrieving capabilities provided with SQL. You saw numerous examples of applying these operators to the BIRDS database and now have the opportunity to apply what you have learned to the example BIRDS database—and to any other data you have created to this point. Remember to have fun with these exercises; the same questions often have multiple solutions. Take some time to experiment with your data because the last couple hours are extremely important as you build upon your knowledge of the SELECT statement.
Q. Can I have more than one AND in the WHERE clause?
A. Yes, you can use all the operators multiple times.
Q. What happens if I use single quotation marks around a NUMBER data type in a WHERE clause?
A. Your query still processes, but quotation marks are not necessary for NUMBER fields.
The following workshop consists 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 give you the opportunity to apply the concepts discussed during the current hour, as well as build on the knowledge you acquired in previous hours of study. Be sure to complete the quiz questions and exercises before continuing to the next hour. Refer to Appendix C, “Answers to Quizzes and Exercises,” for answers.
1. True or false: When using the OR operator, both conditions must be TRUE for data to be returned.
2. True or false: All specified values must match when using the IN operator for data to be returned.
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?
1. Use the original BIRDS database for these exercises. Write a SELECT statement from the BIRDS table to return all rows of data, to familiarize yourself with the data. Then write the appropriate SELECT statements using the operators you learned in this chapter for the remaining exercises.
2. Which birds have more than two broods per year?
3. Show all records in the MIGRATIONS table in which the MIGRATION_LOCATION is not Mexico.
4. List all birds that have a wingspan less than 48 inches.
5. List all birds that have a wingspan greater than or equal to 72 inches.
6. Write a query to return the BIRD_NAME and WINGSPAN of birds that have a wingspan between 30 and 70 inches.
7. Select all migration locations that are in Central America and South America.
8. List all birds by name that have the word green in their name.
9. List all birds that begin with the word bald.
10. Do any birds have a wingspan less than 20 inches or a height shorter than 12 inches?
11. Do any birds have a weight more than 5 pounds and a height shorter than 36 inches?
12. List all bird names that do not have the word green in their name.
13. List all bird names that have one of the three primary colors in their name.
14. How many birds spend more than 75 days total with their young?
15. Experiment with some of your own queries using the operators you learned in this chapter.