Teach Yourself SQL in 21 Days, Second Edition
  
- Day 3 -
Expressions, Conditions, and Operators
Objectives
On Day 2, "Introduction to the Query: The SELECT Statement,"
you used SELECT and FROM to manipulate data in interesting (and
useful) ways. Today you learn more about SELECT and FROM and expand
the basic query with some new terms to go with query, table, and row, as well as
a new clause and a group of handy items called operators. When the sun sets on Day
3, you will
- Know what an expression is and how to use it
- Know what a condition is and how to use it
- Be familiar with the basic uses of the WHERE clause
- Be able to use arithmetic, comparison, character, logical, and set operators
- Have a working knowledge of some miscellaneous operators
NOTE: We used Oracle's Personal Oracle7
to generate today's examples. Other implementations of SQL may differ slightly in
the way in which commands are entered or output is displayed, but the results are
basically the same for all implementations that conform to the ANSI standard.
Expressions
The definition of an expression is simple: An expression returns a value.
Expression types are very broad, covering different data types such as String, Numeric,
and Boolean. In fact, pretty much anything following a clause (SELECT or
FROM, for example) is an expression. In the following example amount
is an expression that returns the value contained in the amount column.
SELECT amount FROM checks;
In the following statement NAME, ADDRESS, PHONE and ADDRESSBOOK
are expressions:
SELECT NAME, ADDRESS, PHONE
FROM ADDRESSBOOK;
Now, examine the following expression:
WHERE NAME = 'BROWN'
It contains a condition, NAME = 'BROWN', which is an example of a Boolean
expression. NAME = 'BROWN' will be either TRUE or FALSE,
depending on the condition =.
Conditions
If you ever want to find a particular item or group of items in your database,
you need one or more conditions. Conditions are contained in the WHERE clause.
In the preceding example, the condition is
NAME = 'BROWN'
To find everyone in your organization who worked more than 100 hours
last month, your condition would be
NUMBEROFHOURS > 100
Conditions enable you to make selective queries. In their most common form, conditions
comprise a variable, a constant, and a comparison operator. In the first example
the variable is NAME, the constant is 'BROWN', and the comparison
operator is =. In the second example the variable is NUMBEROFHOURS,
the constant is 100, and the comparison operator is >. You need
to know about two more elements before you can write conditional queries: the WHERE
clause and operators.
The WHERE Clause
The syntax of the WHERE clause is
SYNTAX:
WHERE <SEARCH CONDITION>
SELECT, FROM, and WHERE are the three most frequently
used clauses in SQL. WHERE simply causes your queries to be more selective.
Without the WHERE clause, the most useful thing you could do with a query
is display all records in the selected table(s). For example:
INPUT:
SQL> SELECT * FROM BIKES;
lists all rows of data in the table BIKES.
OUTPUT:
NAME FRAMESIZE COMPOSITION MILESRIDDEN TYPE
-------------- --------- ------------ ----------- -------
TREK 2300 22.5 CARBON FIBER 3500 RACING
BURLEY 22 STEEL 2000 TANDEM
GIANT 19 STEEL 1500 COMMUTER
FUJI 20 STEEL 500 TOURING
SPECIALIZED 16 STEEL 100 MOUNTAIN
CANNONDALE 22.5 ALUMINUM 3000 RACING
6 rows selected.
If you wanted a particular bike, you could type
INPUT/OUTPUT:
SQL> SELECT *
FROM BIKES
WHERE NAME = 'BURLEY';
which would yield only one record:
NAME FRAMESIZE COMPOSITION MILESRIDDEN TYPE
-------------- --------- -------------- ----------- -------
BURLEY 22 STEEL 2000 TANDEM
ANALYSIS:
This simple example shows how you can place a condition on the data that you want
to retrieve.
Operators
Operators are the elements you use inside an expression to articulate how you
want specified conditions to retrieve data. Operators fall into six groups: arithmetic,
comparison, character, logical, set, and miscellaneous.
Arithmetic Operators
The arithmetic operators are plus (+), minus (-), divide (/),
multiply (*), and modulo (%). The first four are self-explanatory.
Modulo returns the integer remainder of a division. Here are two examples:
5 % 2 = 1
6 % 2 = 0
The modulo operator does not work with data types that have decimals, such as
Real or Number.
If you place several of these arithmetic operators in an expression without any
parentheses, the operators are resolved in this order: multiplication, division,
modulo, addition, and subtraction. For example, the expression
2*6+9/3
equals
12 + 3 = 15
However, the expression
2 * (6 + 9) / 3
equals
2 * 15 / 3 = 10
Watch where you put those parentheses! Sometimes the expression does exactly what
you tell it to do, rather than what you want it to do.
The following sections examine the arithmetic operators in some detail and give
you a chance to write some queries.
Plus (+)
You can use the plus sign in several ways. Type the following statement to display
the PRICE table:
INPUT:
SQL> SELECT * FROM PRICE;
OUTPUT:
ITEM WHOLESALE
-------------- ----------
TOMATOES .34
POTATOES .51
BANANAS .67
TURNIPS .45
CHEESE .89
APPLES .23
6 rows selected.
Now type:
INPUT/OUTPUT:
SQL> SELECT ITEM, WHOLESALE, WHOLESALE + 0.15
FROM PRICE;
Here the + adds 15 cents to each price to produce the following:
ITEM WHOLESALE WHOLESALE+0.15
-------------- --------- --------------
TOMATOES .34 .49
POTATOES .51 .66
BANANAS .67 .82
TURNIPS .45 .60
CHEESE .89 1.04
APPLES .23 .38
6 rows selected.
ANALYSIS:
What is this last column with the unattractive column heading WHOLESALE+0.15?
It's not in the original table. (Remember, you used * in the SELECT
clause, which causes all the columns to be shown.) SQL allows you to create a virtual
or derived column by combining or modifying existing columns.
Retype the original entry:
INPUT/OUTPUT:
SQL> SELECT * FROM PRICE;
The following table results:
ITEM WHOLESALE
-------------- ---------
TOMATOES .34
POTATOES .51
BANANAS .67
TURNIPS .45
CHEESE .89
APPLES .23
6 rows selected.
ANALYSIS:
The output confirms that the original data has not been changed and that the column
heading WHOLESALE+0.15 is not a permanent part of it. In fact, the column
heading is so unattractive that you should do something about it.
Type the following:
INPUT/OUTPUT:
SQL> SELECT ITEM, WHOLESALE, (WHOLESALE + 0.15) RETAIL
FROM PRICE;
Here's the result:
ITEM WHOLESALE RETAIL
-------------- --------- ------
TOMATOES .34 .49
POTATOES .51 .66
BANANAS .67 .82
TURNIPS .45 .60
CHEESE .89 1.04
APPLES .23 .38
6 rows selected.
ANALYSIS:
This is wonderful! Not only can you create new columns, but you can also rename
them on the fly. You can rename any of the columns using the syntax column_name
alias (note the space between column_name and alias).
For example, the query
INPUT/OUTPUT:
SQL> SELECT ITEM PRODUCE, WHOLESALE, WHOLESALE + 0.25 RETAIL
FROM PRICE;
renames the columns as follows:
PRODUCE WHOLESALE RETAIL
-------------- --------- ---------
TOMATOES .34 .59
POTATOES .51 .76
BANANAS .67 .92
TURNIPS .45 .70
CHEESE .89 1.14
APPLES .23 .48
NOTE: Some implementations of SQL use
the syntax <column name = alias>. The preceding example would be written
as follows:
SQL> SELECT ITEM = PRODUCE,
WHOLESALE,
WHOLESALE + 0.25 = RETAIL,
FROM PRICE;
Check your implementation for the exact syntax.
You might be wondering what use aliasing is if you are not using command-line
SQL. Fair enough. Have you ever wondered how report builders work? Someday, when
you are asked to write a report generator, you'll remember this and not spend weeks
reinventing what Dr. Codd and IBM have wrought.
So far, you have seen two uses of the plus sign. The first instance was the use
of the plus sign in the SELECT clause to perform a calculation on the data
and display the calculation. The second use of the plus sign is in the WHERE
clause. Using operators in the WHERE clause gives you more flexibility when
you specify conditions for retrieving data.
In some implementations of SQL, the plus sign does double duty as a character
operator. You'll see that side of the plus a little later today.
Minus (-)
Minus also has two uses. First, it can change the sign of a number. You can use
the table HILOW to demonstrate this function.
INPUT:
SQL> SELECT * FROM HILOW;
OUTPUT:
STATE HIGHTEMP LOWTEMP
---------- -------- ---------
CA -50 120
FL 20 110
LA 15 99
ND -70 101
NE -60 100
For example, here's a way to manipulate the data:
INPUT/OUTPUT:
SQL> SELECT STATE, -HIGHTEMP LOWS, -LOWTEMP HIGHS
FROM HILOW;
STATE LOWS HIGHS
---------- -------- ---------
CA 50 -120
FL -20 -110
LA -15 -99
ND 70 -101
NE 60 -100
The second (and obvious) use of the minus sign is to subtract one column from
another. For example:
INPUT/OUTPUT:
SQL> SELECT STATE,
2 HIGHTEMP LOWS,
3 LOWTEMP HIGHS,
4 (LOWTEMP - HIGHTEMP) DIFFERENCE
5 FROM HILOW;
STATE LOWS HIGHS DIFFERENCE
---------- -------- -------- ----------
CA -50 120 170
FL 20 110 90
LA 15 99 84
ND -70 101 171
NE -60 100 160
Notice the use of aliases to fix the data that was entered incorrectly. This remedy
is merely a temporary patch, though, and not a permanent fix. You should see to it
that the data is corrected and entered correctly in the future. On Day 21, "Common
SQL Mistakes/Errors and Resolutions," you'll learn how to correct bad data.
This query not only fixed (at least visually) the incorrect data but also created
a new column containing the difference between the highs and lows of each state.
If you accidentally use the minus sign on a character field, you get something
like this:
INPUT/OUTPUT:
SQL> SELECT -STATE FROM HILOW;
ERROR:
ORA-01722: invalid number
no rows selected
The exact error message varies with implementation, but the result is the same.
Divide (/)
The division operator has only the one obvious meaning. Using the table PRICE,
type the following:
INPUT:
SQL> SELECT * FROM PRICE;
OUTPUT:
ITEM WHOLESALE
-------------- ---------
TOMATOES .34
POTATOES .51
BANANAS .67
TURNIPS .45
CHEESE .89
APPLES .23
6 rows selected.
You can show the effects of a two-for-one sale by typing the next statement:
INPUT/OUTPUT:
SQL> SELECT ITEM, WHOLESALE, (WHOLESALE/2) SALEPRICE
2 FROM PRICE;
ITEM WHOLESALE SALEPRICE
-------------- --------- ---------
TOMATOES .34 .17
POTATOES .51 .255
BANANAS .67 .335
TURNIPS .45 .225
CHEESE .89 .445
APPLES .23 .115
6 rows selected.
The use of division in the preceding SELECT statement is straightforward
(except that coming up with half pennies can be tough).
Multiply (*)
The multiplication operator is also straightforward. Again, using the PRICE
table, type the following:
INPUT:
SQL> SELECT * FROM PRICE;
OUTPUT:
ITEM WHOLESALE
-------------- ---------
TOMATOES .34
POTATOES .51
BANANAS .67
TURNIPS .45
CHEESE .89
APPLES .23
6 rows selected.
This query changes the table to reflect an across-the-board 10 percent discount:
INPUT/OUTPUT:
SQL> SELECT ITEM, WHOLESALE, WHOLESALE * 0.9 NEWPRICE
FROM PRICE;
ITEM WHOLESALE NEWPRICE
-------------- --------- --------
TOMATOES .34 .306
POTATOES .51 .459
BANANAS .67 .603
TURNIPS .45 .405
CHEESE .89 .801
APPLES .23 .207
6 rows selected.
These operators enable you to perform powerful calculations in a SELECT
statement.
Modulo (%)
The modulo operator returns the integer remainder of the division operation. Using
the table REMAINS, type the following:
INPUT:
SQL> SELECT * FROM REMAINS;
OUTPUT:
NUMERATOR DENOMINATOR
--------- ------------
10 5
8 3
23 9
40 17
1024 16
85 34
6 rows selected.
You can also create a new column, REMAINDER, to hold the values of NUMERATOR
% DENOMINATOR:
INPUT/OUTPUT:
SQL> SELECT NUMERATOR,
DENOMINATOR,
NUMERATOR%DENOMINATOR REMAINDER
FROM REMAINS;
NUMERATOR DENOMINATOR REMAINDER
--------- ----------- ---------
10 5 0
8 3 2
23 9 5
40 17 6
1024 16 0
85 34 17
6 rows selected.
Some implementations of SQL implement modulo as a function called MOD
(see Day 4, "Functions: Molding the Data You Retrieve"). The following
statement produces results that are identical to the results in the preceding statement:
SQL> SELECT NUMERATOR,
DENOMINATOR,
MOD(NUMERATOR,DENOMINATOR) REMAINDER
FROM REMAINS;
Precedence
This section examines the use of precedence in a SELECT statement. Using
the database PRECEDENCE, type the following:
SQL> SELECT * FROM PRECEDENCE;
N1 N2 N3 N4
--------- --------- --------- ---------
1 2 3 4
13 24 35 46
9 3 23 5
63 2 45 3
7 2 1 4
Use the following code segment to test precedence:
INPUT/OUTPUT:
SQL> SELECT
2 N1+N2*N3/N4,
3 (N1+N2)*N3/N4,
4 N1+(N2*N3)/N4
5 FROM PRECEDENCE;
N1+N2*N3/N4 (N1+N2)*N3/N4 N1+(N2*N3)/N4
----------- ------------- -------------
2.5 2.25 2.5
31.26087 28.152174 31.26087
22.8 55.2 22.8
93 975 93
7.5 2.25 7.5
Notice that the first and last columns are identical. If you added a fourth column
N1+N2* (N3/N4), its values would also be identical to those of the current
first and last columns.
Comparison Operators
True to their name, comparison operators compare expressions and return one of
three values: TRUE, FALSE, or Unknown. Wait a minute!
Unknown? TRUE and FALSE are self-explanatory, but what
is Unknown?
To understand how you could get an Unknown, you need to know a little
about the concept of NULL. In database terms NULL is the absence
of data in a field. It does not mean a column has a zero or a blank in it. A zero
or a blank is a value. NULL means nothing is in that field. If you make
a comparison like Field = 9 and the only value for Field is NULL,
the comparison will come back Unknown. Because Unknown is an uncomfortable
condition, most flavors of SQL change Unknown to FALSE and provide
a special operator, IS NULL, to test for a NULL condition.
Here's an example of NULL: Suppose an entry in the PRICE table
does not contain a value for WHOLESALE. The results of a query might look
like this:
INPUT:
SQL> SELECT * FROM PRICE;
OUTPUT:
ITEM WHOLESALE
-------------- ----------
TOMATOES .34
POTATOES .51
BANANAS .67
TURNIPS .45
CHEESE .89
APPLES .23
ORANGES
Notice that nothing is printed out in the WHOLESALE field position for
oranges. The value for the field WHOLESALE for oranges is NULL.
The NULL is noticeable in this case because it is in a numeric column. However,
if the NULL appeared in the ITEM column, it would be impossible
to tell the difference between NULL and a blank.
Try to find the NULL:
INPUT/OUTPUT:
SQL> SELECT *
2 FROM PRICE
3 WHERE WHOLESALE IS NULL;
ITEM WHOLESALE
-------------- ----------
ORANGES
ANALYSIS:
As you can see by the output, ORANGES is the only item whose value for
WHOLESALE is NULL or does not contain a value. What if you use
the equal sign (=) instead?
INPUT/OUTPUT:
SQL> SELECT *
FROM PRICE
WHERE WHOLESALE = NULL;
no rows selected
ANALYSIS:
You didn't find anything because the comparison WHOLESALE = NULL returned
a FALSE--the result was unknown. It would be more appropriate to use an
IS NULL instead of =, changing the WHERE statement to
WHERE WHOLESALE IS NULL. In this case you would get all the rows where a
NULL existed.
This example also illustrates both the use of the most common comparison operator,
the equal sign (=), and the playground of all comparison operators, the
WHERE clause. You already know about the WHERE clause, so here's
a brief look at the equal sign.
Equal (=)
Earlier today you saw how some implementations of SQL use the equal sign in the
SELECT clause to assign an alias. In the WHERE clause, the equal
sign is the most commonly used comparison operator. Used alone, the equal sign is
a very convenient way of selecting one value out of many. Try this:
INPUT:
SQL> SELECT * FROM FRIENDS;
OUTPUT:
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP
-------------- -------------- --------- -------- -- -----
BUNDY AL 100 555-1111 IL 22333
MEZA AL 200 555-2222 UK
MERRICK BUD 300 555-6666 CO 80212
MAST JD 381 555-6767 LA 23456
BULHER FERRIS 345 555-3223 IL 23332
Let's find JD's row. (On a short list this task appears trivial, but you may have
more friends than we do--or you may have a list with thousands of records.)
INPUT/OUTPUT:
SQL> SELECT *
FROM FRIENDS
WHERE FIRSTNAME = 'JD';
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP
-------------- -------------- --------- -------- -- -----
MAST JD 381 555-6767 LA 23456
We got the result that we expected. Try this:
INPUT/OUTPUT:
SQL> SELECT *
FROM FRIENDS
WHERE FIRSTNAME = 'AL';
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP
-------------- -------------- --------- -------- -- -----
BUNDY AL 100 555-1111 IL 22333
MEZA AL 200 555-2222 UK
NOTE: Here you see that = can
pull in multiple records. Notice that ZIP is blank on the second record.
ZIP is a character field (you learn how to create and populate tables on
Day 8, "Manipulating Data"), and in this particular record the NULL
demonstrates that a NULL in a character field is impossible to differentiate
from a blank field.
Here's another very important lesson concerning case sensitivity:
INPUT/OUTPUT:
SQL> SELECT * FROM FRIENDS
WHERE FIRSTNAME = 'BUD';
FIRSTNAME
--------------
BUD
1 row selected.
Now try this:
INPUT/OUTPUT:
SQL> select * from friends
where firstname = 'Bud';
no rows selected.
ANALYSIS:
Even though SQL syntax is not case sensitive, data is. Most companies prefer to
store data in uppercase to provide data consistency. You should always store data
either in all uppercase or in all lowercase. Mixing case creates difficulties when
you try to retrieve accurate data.
Greater Than (>) and Greater Than or Equal To (>=)
The greater than operator (>) works like this:
INPUT:
SQL> SELECT *
FROM FRIENDS
WHERE AREACODE > 300;
OUTPUT:
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP
-------------- -------------- --------- -------- -- -----
MAST JD 381 555-6767 LA 23456
BULHER FERRIS 345 555-3223 IL 23332
ANALYSIS:
This example found all the area codes greater than (but not including) 300.
To include 300, type this:
INPUT/OUTPUT:
SQL> SELECT *
2 FROM FRIENDS
3 WHERE AREACODE >= 300;
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP
-------------- -------------- --------- -------- -- -----
MERRICK BUD 300 555-6666 CO 80212
MAST JD 381 555-6767 LA 23456
BULHER FERRIS 345 555-3223 IL 23332
ANALYSIS:
With this change you get area codes starting at 300 and going up. You
could achieve the same results with the statement AREACODE > 299.
NOTE: Notice that no quotes surround 300
in this SQL statement. Number-defined fieldsnumber-defined fields do not require
quotes.
Less Than (<) and Less Than or Equal To (<=)
As you might expect, these comparison operators work the same way as >
and >= work, only in reverse:
INPUT:
SQL> SELECT *
2 FROM FRIENDS
3 WHERE STATE < 'LA';
OUTPUT:
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP
-------------- -------------- --------- -------- -- ------
BUNDY AL 100 555-1111 IL 22333
MERRICK BUD 300 555-6666 CO 80212
BULHER FERRIS 345 555-3223 IL 23332
NOTE: How did STATE get changed
to ST? Because the column has only two characters, the column name is shortened
to two characters in the returned rows. If the column name had been COWS,
it would come out CO. The widths of AREACODE and PHONE
are wider than their column names, so they are not truncated.
ANALYSIS:
Wait a minute. Did you just use < on a character field? Of course
you did. You can use any of these operators on any data type. The result varies by
data type. For example, use lowercase in the following state search:
INPUT/OUTPUT:
SQL> SELECT *
2 FROM FRIENDS
3 WHERE STATE < 'la';
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP
-------------- -------------- --------- -------- -- -----
BUNDY AL 100 555-1111 IL 22333
MEZA AL 200 555-2222 UK
MERRICK BUD 300 555-6666 CO 80212
MAST JD 381 555-6767 LA 23456
BULHER FERRIS 345 555-3223 IL 23332
ANALYSIS:
Uppercase is usually sorted before lowercase; therefore, the uppercase codes returned
are less than 'la'. Again, to be safe, check your implementation.
TIP: To be sure of how these operators
will behave, check your language tables. Most PC implementations use the ASCII tables.
Some other platforms use EBCDIC.
To include the state of Louisiana in the original search, type
INPUT/OUTPUT:
SQL> SELECT *
2 FROM FRIENDS
3 WHERE STATE <= 'LA';
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP
-------------- -------------- --------- -------- -- -----
BUNDY AL 100 555-1111 IL 22333
MERRICK BUD 300 555-6666 CO 80212
MAST JD 381 555-6767 LA 23456
BULHER FERRIS 345 555-3223 IL 23332
Inequalities (< > or !=)
When you need to find everything except for certain data, use the inequality symbol,
which can be either < > or !=, depending on your SQL implementation.
For example, to find everyone who is not AL, type this:
INPUT:
SQL> SELECT *
2 FROM FRIENDS
3 WHERE FIRSTNAME <> 'AL';
OUTPUT:
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP
-------------- -------------- --------- -------- -- -----
MERRICK BUD 300 555-6666 CO 80212
MAST JD 381 555-6767 LA 23456
BULHER FERRIS 345 555-3223 IL 23332
To find everyone not living in California, type this:
INPUT/OUTPUT:
SQL> SELECT *
2 FROM FRIENDS
3 WHERE STATE != 'CA';
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP
-------------- -------------- --------- -------- -- -----
BUNDY AL 100 555-1111 IL 22333
MEZA AL 200 555-2222 UK
MERRICK BUD 300 555-6666 CO 80212
MAST JD 381 555-6767 LA 23456
BULHER FERRIS 345 555-3223 IL 23332
NOTE: Notice that both symbols, <>
and !=, can express "not equals."
Character Operators
You can use character operators to manipulate the way character strings are represented,
both in the output of data and in the process of placing conditions on data to be
retrieved. This section describes two character operators: the LIKE operator
and the || operator, which conveys the concept of character concatenation.
I Want to Be Like LIKE
What if you wanted to select parts of a database that fit a pattern but weren't
quite exact matches? You could use the equal sign and run through all the possible
cases, but that process would be boring and time-consuming. Instead, you could use
LIKE. Consider the following:
INPUT:
SQL> SELECT * FROM PARTS;
OUTPUT:
NAME LOCATION PARTNUMBER
-------------- -------------- ----------
APPENDIX MID-STOMACH 1
ADAMS APPLE THROAT 2
HEART CHEST 3
SPINE BACK 4
ANVIL EAR 5
KIDNEY MID-BACK 6
How can you find all the parts located in the back? A quick visual inspection
of this simple table shows that it has two parts, but unfortunately the locations
have slightly different names. Try this:
INPUT/OUTPUT:
SQL> SELECT *
2 FROM PARTS
3 WHERE LOCATION LIKE '%BACK%';
NAME LOCATION PARTNUMBER
-------------- -------------- ----------
SPINE BACK 4
KIDNEY MID-BACK 6
ANALYSIS:
You can see the use of the percent sign (%) in the statement after LIKE.
When used inside a LIKE expression, % is a wildcard. What you asked for
was any occurrence of BACK in the column location. If you queried
INPUT:
SQL> SELECT *
FROM PARTS
WHERE LOCATION LIKE 'BACK%';
you would get any occurrence that started with BACK:
OUTPUT:
NAME LOCATION PARTNUMBER
-------------- -------------- ----------
SPINE BACK 4
If you queried
INPUT:
SQL> SELECT *
FROM PARTS
WHERE NAME LIKE 'A%';
you would get any name that starts with A:
OUTPUT:
NAME LOCATION PARTNUMBER
-------------- -------------- ----------
APPENDIX MID-STOMACH 1
ADAMS APPLE THROAT 2
ANVIL EAR 5
Is LIKE case sensitive? Try the next query to find out.
INPUT/OUTPUT:
SQL> SELECT *
FROM PARTS
WHERE NAME LIKE 'a%';
no rows selected
ANALYSIS:
The answer is yes. References to data are always case sensitive.
What if you want to find data that matches all but one character in a certain
pattern? In this case you could use a different type of wildcard: the underscore.
Underscore (_)
The underscore is the single-character wildcard. Using a modified version of the
table FRIENDS, type this:
INPUT:
SQL> SELECT * FROM FRIENDS;
OUTPUT:
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP
-------------- -------------- --------- -------- -- -----
BUNDY AL 100 555-1111 IL 22333
MEZA AL 200 555-2222 UK
MERRICK UD 300 555-6666 CO 80212
MAST JD 381 555-6767 LA 23456
BULHER FERRIS 345 555-3223 IL 23332
PERKINS ALTON 911 555-3116 CA 95633
BOSS SIR 204 555-2345 CT 95633
To find all the records where STATE starts with C, type the
following:
INPUT/OUTPUT:
SQL> SELECT *
2 FROM FRIENDS
3 WHERE STATE LIKE 'C_';
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP
-------------- -------------- --------- -------- -- -----
MERRICK BUD 300 555-6666 CO 80212
PERKINS ALTON 911 555-3116 CA 95633
BOSS SIR 204 555-2345 CT 95633
You can use several underscores in a statement:
INPUT/OUTPUT:
SQL> SELECT *
2 FROM FRIENDS
3 WHERE PHONE LIKE'555-6_6_';
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP
-------------- -------------- --------- -------- -- -----
MERRICK BUD 300 555-6666 CO 80212
MAST JD 381 555-6767 LA 23456
The previous statement could also be written as follows:
INPUT/OUTPUT:
SQL> SELECT *
2 FROM FRIENDS
3 WHERE PHONE LIKE '555-6%';
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP
-------------- -------------- --------- -------- -- -----
MERRICK BUD 300 555-6666 CO 80212
MAST JD 381 555-6767 LA 23456
Notice that the results are identical. These two wildcards can be combined. The
next example finds all records with L as the second character:
INPUT/OUTPUT:
SQL> SELECT *
2 FROM FRIENDS
3 WHERE FIRSTNAME LIKE '_L%';
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP
-------------- -------------- --------- -------- -- -----
BUNDY AL 100 555-1111 IL 22333
MEZA AL 200 555-2222 UK
PERKINS ALTON 911 555-3116 CA 95633
Concatenation (||)
The || (double pipe) symbol concatenates two strings. Try this:
INPUT:
SQL> SELECT FIRSTNAME || LASTNAME ENTIRENAME
2 FROM FRIENDS;
OUTPUT:
ENTIRENAME
----------------------
AL BUNDY
AL MEZA
BUD MERRICK
JD MAST
FERRIS BULHER
ALTON PERKINS
SIR BOSS
7 rows selected.
ANALYSIS:
Notice that || is used instead of +. If you use + to
try to concatenate the strings, the SQL interpreter used for this example (Personal
Oracle7) returns the following error:
INPUT/OUTPUT:
SQL> SELECT FIRSTNAME + LASTNAME ENTIRENAME
FROM FRIENDS;
ERROR:
ORA-01722: invalid number
It is looking for two numbers to add and throws the error invalid number
when it doesn't find any.
NOTE: Some implementations of SQL use
the plus sign to concatenate strings. Check your implementation.
Here's a more practical example using concatenation:
INPUT/OUTPUT:
SQL> SELECT LASTNAME || ',' || FIRSTNAME NAME
FROM FRIENDS;
NAME
------------------------------------------------------
BUNDY , AL
MEZA , AL
MERRICK , BUD
MAST , JD
BULHER , FERRIS
PERKINS , ALTON
BOSS , SIR
7 rows selected.
ANALYSIS:
This statement inserted a comma between the last name and the first name.
NOTE: Notice the extra spaces between
the first name and the last name in these examples. These spaces are actually part
of the data. With certain data types, spaces are right-padded to values less than
the total length allocated for a field. See your implementation. Data types will
be discussed on Day 9, "Creating and Maintaining Tables."
So far you have performed the comparisons one at a time. That method is fine for
some problems, but what if you need to find all the people at work with last names
starting with P who have less than three days of vacation time?
Logical Operators
logical operatorsLogical operators separate two or more conditions in the WHERE
clause of an SQL statement.
Vacation time is always a hot topic around the workplace. Say you designed a table
called VACATION for the accounting department:
INPUT:
SQL> SELECT * FROM VACATION;
OUTPUT:
LASTNAME EMPLOYEENUM YEARS LEAVETAKEN
-------------- ----------- --------- ----------
ABLE 101 2 4
BAKER 104 5 23
BLEDSOE 107 8 45
BOLIVAR 233 4 80
BOLD 210 15 100
COSTALES 211 10 78
6 rows selected.
Suppose your company gives each employee 12 days of leave each year. Using what
you have learned and a logical operator, find all the employees whose names start
with B and who have more than 50 days of leave coming.
INPUT/OUTPUT:
SQL> SELECT LASTNAME,
2 YEARS * 12 - LEAVETAKEN REMAINING
3 FROM VACATION
4 WHERE LASTNAME LIKE 'B%'
5 AND
6 YEARS * 12 - LEAVETAKEN > 50;
LASTNAME REMAINING
-------------- ---------
BLEDSOE 51
BOLD 80
ANALYSIS:
This query is the most complicated you have done so far. The SELECT clause
(lines 1 and 2) uses arithmetic operators to determine how many days of leave each
employee has remaining. The normal precedence computes YEARS * 12 - LEAVETAKEN.
(A clearer approach would be to write (YEARS * 12) - LEAVETAKEN.)
LIKE is used in line 4 with the wildcard % to find all the B
names. Line 6 uses the > to find all occurrences greater than 50.
The new element is on line 5. You used the logical operator AND to ensure
that you found records that met the criteria in lines 4 and 6.
AND
AND means that the expressions on both sides must be true to return TRUE.
If either expression is false, AND returns FALSE. For example,
to find out which employees have been with the company for 5 years or less
and have taken more than 20 days leave, try this:
INPUT:
SQL> SELECT LASTNAME
2 FROM VACATION
3 WHERE YEARS <= 5
4 AND
5 LEAVETAKEN > 20 ;
OUTPUT:
LASTNAME
--------
BAKER
BOLIVAR
If you want to know which employees have been with the company for 5
years or more and have taken less than 50 percent of their leave, you could
write:
INPUT/OUTPUT:
SQL> SELECT LASTNAME WORKAHOLICS
2 FROM VACATION
3 WHERE YEARS >= 5
4 AND
5 ((YEARS *12)-LEAVETAKEN)/(YEARS * 12) < 0.50;
WORKAHOLICS
---------------
BAKER
BLEDSOE
Check these people for burnout. Also check out how we used the AND to
combine these two conditions.
OR
You can also use OR to sum up a series of conditions. If any of the comparisons
is true, OR returns TRUE. To illustrate the difference, conditionsrun
the last query with OR instead of with AND:
INPUT:
SQL> SELECT LASTNAME WORKAHOLICS
2 FROM VACATION
3 WHERE YEARS >= 5
4 OR
5 ((YEARS *12)-LEAVETAKEN)/(YEARS * 12) >= 0.50;
OUTPUT:
WORKAHOLICS
---------------
ABLE
BAKER
BLEDSOE
BOLD
COSTALES
ANALYSIS:
The original names are still in the list, but you have three new entries (who
would probably resent being called workaholics). These three new names made the list
because they satisfied one of the conditions. OR requires that only one
of the conditions be true in order for data to be returned.
NOT
NOT means just that. If the condition it applies to evaluates to TRUE,
NOT make it FALSE. If the condition after the NOT is FALSE,
it becomes TRUE. For example, the following SELECT returns the
only two names not beginning with B in the table:
INPUT:
SQL> SELECT *
2 FROM VACATION
3 WHERE LASTNAME NOT LIKE 'B%';
OUTPUT:
LASTNAME EMPLOYEENUM YEARS LEAVETAKEN
-------------- ----------- -------- ----------
ABLE 101 2 4
COSTALES 211 10 78
NOT can also be used with the operator IS when applied to NULL.
Recall the PRICES table where we put a NULL value in the WHOLESALE
column opposite the item ORANGES.
INPUT/OUTPUT:
SQL> SELECT * FROM PRICE;
ITEM WHOLESALE
-------------- ---------
TOMATOES .34
POTATOES .51
BANANAS .67
TURNIPS .45
CHEESE .89
APPLES .23
ORANGES
7 rows selected.
To find the non-NULL items, type this:
INPUT/OUTPUT:
SQL> SELECT *
2 FROM PRICE
3 WHERE WHOLESALE IS NOT NULL;
ITEM WHOLESALE
-------------- ---------
TOMATOES .34
POTATOES .51
BANANAS .67
TURNIPS .45
CHEESE .89
APPLES .23
6 rows selected.
Set Operators
On Day 1, "Introduction to SQL," you learned that SQL is based on the
theory of sets. The following sections examine set operators.
UNION and UNION ALL
UNION returns the results of two queries minus the duplicate rows. The
following two tables represent the rosters of teams:
INPUT:
SQL> SELECT * FROM FOOTBALL;
OUTPUT:
NAME
--------------------
ABLE
BRAVO
CHARLIE
DECON
EXITOR
FUBAR
GOOBER
7 rows selected.
INPUT:
SQL> SELECT * FROM SOFTBALL;
OUTPUT:
NAME
--------------------
ABLE
BAKER
CHARLIE
DEAN
EXITOR
FALCONER
GOOBER
7 rows selected.
How many different people play on one team or another?
INPUT/OUTPUT:
SQL> SELECT NAME FROM SOFTBALL
2 UNION
3 SELECT NAME FROM FOOTBALL;
NAME
--------------------
ABLE
BAKER
BRAVO
CHARLIE
DEAN
DECON
EXITOR
FALCONER
FUBAR
GOOBER
10 rows selected.
UNION returns 10 distinct names from the two lists. How many names are
on both lists (including duplicates)?
INPUT/OUTPUT:
SQL> SELECT NAME FROM SOFTBALL
2 UNION ALL
3 SELECT NAME FROM FOOTBALL;
NAME
--------------------
ABLE
BAKER
CHARLIE
DEAN
EXITOR
FALCONER
GOOBER
ABLE
BRAVO
CHARLIE
DECON
EXITOR
FUBAR
GOOBER
14 rows selected.
ANALYSIS:
The combined list--courtesy of the UNION ALL statement--has 14 names.
UNION ALL works just like UNION except it does not eliminate duplicates.
Now show me a list of players who are on both teams. You can't do that with UNION--you
need to learn INTERSECT.
INTERSECT
INTERSECT returns only the rows found by both queries. The next SELECT
statement shows the list of players who play on both teams:
INPUT:
SQL> SELECT * FROM FOOTBALL
2 INTERSECT
3 SELECT * FROM SOFTBALL;
OUTPUT:
NAME
--------------------
ABLE
CHARLIE
EXITOR
GOOBER
ANALYSIS:
In this example INTERSECT finds the short list of players who are on
both teams by combining the results of the two SELECT statements.
MINUS (Difference)
Minus returns the rows from the first query that were not present in
the second. For example:
INPUT:
SQL> SELECT * FROM FOOTBALL
2 MINUS
3 SELECT * FROM SOFTBALL;
OUTPUT:
NAME
--------------------
BRAVO
DECON
FUBAR
ANALYSIS:
The preceding query shows the three football players who are not on the softball
team. If you reverse the order, you get the three softball players who aren't on
the football team:
INPUT:
SQL> SELECT * FROM SOFTBALL
2 MINUS
3 SELECT * FROM FOOTBALL;
OUTPUT:
NAME
--------------------
BAKER
DEAN
FALCONER
Miscellaneous Operators: IN and BETWEEN
The two operators IN and BETWEEN provide a shorthand for functions
you already know how to do. If you wanted to find friends in Colorado, California,
and Louisiana, you could type the following:
INPUT:
SQL> SELECT *
2 FROM FRIENDS
3 WHERE STATE= 'CA'
4 OR
5 STATE ='CO'
6 OR
7 STATE = 'LA';
OUTPUT:
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP
-------------- -------------- --------- -------- -- -----
MERRICK BUD 300 555-6666 CO 80212
MAST JD 381 555-6767 LA 23456
PERKINS ALTON 911 555-3116 CA 95633
Or you could type this:
INPUT/OUTPUT:
SQL> SELECT *
2 FROM FRIENDS
3 WHERE STATE IN('CA','CO','LA');
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP
-------------- -------------- --------- -------- -- -----
MERRICK BUD 300 555-6666 CO 80212
MAST JD 381 555-6767 LA 23456
PERKINS ALTON 911 555-3116 CA 95633
ANALYSIS:
The second example is shorter and more readable than the first. You never know
when you might have to go back and work on something you wrote months ago. IN
also works with numbers. Consider the following, where the column AREACODE
is a number:
INPUT/OUTPUT:
SQL> SELECT *
2 FROM FRIENDS
3 WHERE AREACODE IN(100,381,204);
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP
-------------- -------------- --------- -------- -- -----
BUNDY AL 100 555-1111 IL 22333
MAST JD 381 555-6767 LA 23456
BOSS SIR 204 555-2345 CT 95633
If you needed a range of things from the PRICE table, you could write
the following:
INPUT/OUTPUT:
SQL> SELECT *
2 FROM PRICE
3 WHERE WHOLESALE > 0.25
4 AND
5 WHOLESALE < 0.75;
ITEM WHOLESALE
-------------- ---------
TOMATOES .34
POTATOES .51
BANANAS .67
TURNIPS .45
Or using BETWEEN, you would write this:
INPUT/OUTPUT:
SQL> SELECT *
2 FROM PRICE
3 WHERE WHOLESALE BETWEEN 0.25 AND 0.75;
ITEM WHOLESALE
-------------- ---------
TOMATOES .34
POTATOES .51
BANANAS .67
TURNIPS .45
Again, the second example is a cleaner, more readable solution than the first.
NOTE: If a WHOLESALE value of
0.25 existed in the PRICE table, that record would have been retrieved
also. Parameters used in the BETWEEN operator are inclusive parametersinclusive.
Summary
At the beginning of Day 3, you knew how to use the basic SELECT and FROM
clauses. Now you know how to use a host of operators that enable you to fine-tune
your requests to the database. You learned how to use arithmetic, comparison, character,
logical, and set operators. This powerful set of tools provides the cornerstone of
your SQL knowledge.
Q&A
- Q How does all of this information apply to me if I am not using SQL from
the command line as depicted in the examples?
A Whether you use SQL in COBOL as Embedded SQL or in Microsoft's Open Database
Connectivity (ODBC), you use the same basic constructions. You will use what you
learned today and yesterday repeatedly as you work with SQL.
Q Why are you constantly telling me to check my implementation? I thought
there was a standard!
A There is an ANSI standard (the most recent version is 1992); however,
most vendors modify it somewhat to suit their databases. The basics are similar if
not identical, and each instance has extensions that other vendors copy and improve.
We have chosen to use ANSI as a starting point but point out the differences as we
go along.
Workshop
The Workshop provides quiz questions to help solidify your understanding of the
material covered, as well as exercises to provide you with experience in using what
you have learned. Try to answer the quiz and exercise questions before checking the
answers in Appendix F, "Answers to Quizzes and Exercises."
Quiz
Use the FRIENDS table to answer the following questions.
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP
-------------- -------------- --------- -------- -- -----
BUNDY AL 100 555-1111 IL 22333
MEZA AL 200 555-2222 UK
MERRICK BUD 300 555-6666 CO 80212
MAST JD 381 555-6767 LA 23456
BULHER FERRIS 345 555-3223 IL 23332
PERKINS ALTON 911 555-3116 CA 95633
BOSS SIR 204 555-2345 CT 95633
- 1. Write a query that returns everyone in the database whose last name
begins with M.
2. Write a query that returns everyone who lives in Illinois with a first
name of AL.
3. Given two tables (PART1 and PART2) containing columns
named PARTNO, how would you find out which part numbers are in both tables?
Write the query.
4. What shorthand could you use instead of WHERE a >= 10 AND a <=30?
5. What will this query return?
SELECT FIRSTNAME
FROM FRIENDS
WHERE FIRSTNAME = 'AL'
AND LASTNAME = 'BULHER';
Exercises
- 1. Using the FRIENDS table, write a query that returns the following:
NAME ST
------------------- --
AL FROM IL
INPUT:
SQL> SELECT (FIRSTNAME || 'FROM') NAME, STATE
2 FROM FRIENDS
3 WHERE STATE = 'IL'
4 AND
5 LASTNAME = 'BUNDY';
OUTPUT:
NAME ST
------------------- --
AL FROM IL
- 2. Using the FRIENDS table, write a query that returns the following:
NAME PHONE
-------------------------- -------------
MERRICK, BUD 300-555-6666
MAST, JD 381-555-6767
BULHER, FERRIS 345-555-3223
© Copyright, Macmillan Computer Publishing. All
rights reserved.
| 
