Listing 5.12. continued

REYNOLDS                  PAULA
PARKER                    FREDERICK
TANAKA                    JEFFREY
COEN                      JOSEPH
NEWTON                    ELEANOR
PURCELL                   STEVEN
JACKEL                    LINDA
ROSEN                     ROSEMARY
MALLARD                   HENRY
GUSSEY                    LISA
MICHAELS                  MARCUS
DEURRE                    PIERRE
WONG                      LE-MING
ABBOT                     KENNETH
POSEN                     HUGO
GOMEZ                     LINDA
MASSEY                    RICHARD
FERGUSON                  RALPH
BING                      RUDOLPH
ANASTATIA                 ANNA
CHIN                      MICHAEL
HOLMES                    IVAN
PLOWCHARD                 CATHERINE
FERNANDEZ                 PAUL
PINKWATER                 PETER
MABEISI                   ANTON

28 rows selected.

You also can use the NOT operator to retrieve rows whose column values are not NULL.
For example, you can count the number of students with fax numbers with the query in Listing 5.13.

Listing 5.13. Retrieving rows in which a column is not NULL.

SQL> select Last_Name, First_Name
  2  from Student
  3  where
  4  Fax is not null;

LAST_NAME                 FIRST_NAME
------------------------  ------------------------
GORDON                    IVY
CLAUSEN                   THOMAS
JACKSON                   ROBERT

You should be aware of how NULL values are processed by arithmetic operations. To see how a NULL value differs from a value of zero, look at Listing 5.14. Suppose you have a table named Intelligence that has two columns—Last_Name and IQ.

Listing 5.14. Retrieving all values of last name and IQ.

SQL> select Last_Name, IQ
  2  from Intelligence;

LAST_NAME                        IQ
------------------------ ----------
SMITH                           100
GORDON                          125
JONES                           150
WILSON
RICHARDS

It so happens that IQ is NULL for Wilson and Richards. If you want to see the average IQ for the records in the Intelligence table, enter the query in Listing 5.15.

Listing 5.15. Selecting the average IQ for non-NULL values of IQ.

SQL> select avg(IQ) from Intelligence;

  AVG(IQ)
---------
     125

As you can see, the rows containing the NULL value for IQ were not used to compute the average IQ. Oracle computed the average IQ by calculating (100 + 125 + 150) / 3. If you change the NULL values to 0, the results are different, as shown in Listing 5.16.

Listing 5.16. Setting IQ to zero where it is currently NULL.

SQL> update Intelligence
  2  set IQ = 0
  3  where
  4  IQ is NULL;

2 rows updated.

SQL> select Last_Name, IQ

                                                    continues

Listing 5.16. continued

  2  from Intelligence;

LAST_NAME                        IQ
------------------------ ----------
SMITH                           100
GORDON                          125
JONES                           150
WILSON                            0
RICHARDS                          0

SQL> select avg(IQ) from Intelligence;

  AVG(IQ)
---------
       75

Searching for Rows with the LIKE Operator

You have already seen an example of the use of the LIKE operator. Oracle users rely on the LIKE operator to search through a table when they're not sure of the exact spelling for the item they're interested in finding.

The benefactor of Flugle College, Reginald Flugle, established a scholarship fund for students whose last name contains the characters IN. As a result, the college registrar, Jean Smith, wants to notify all students whose last name contains this pattern that they may be eligible for this special scholarship. Listing 5.17 shows how she constructs a SELECT statement to find these students.

Listing 5.17. Using LIKE to search for a pattern.

SQL> select Student_ID, Last_Name, First_Name
  2  from Student
  3  where
  4  Last_Name like `%IN%';

STUDENT_ID           LAST_NAME                 FIRST_NAME
-------------------- ------------------------  ------------------------
10231318             BING                      RUDOLPH
10231304             CHIN                      MICHAEL
10231323             PINKWATER                 PETER

Jean has been asked by the college president to prepare a list of courses whose description includes the word workshop. After pondering this problem for a while, Jean submits Listing 5.18 to Oracle.

Listing 5.18. Retrieving courses in which the description contains the pattern workshop.

SQL> select Department_ID, Course_ID, Title
  2  from Course
  3  where
  4  Description like `%workshop%';

DEPARTMENT_ID        COURSE_ID            TITLE
-------------------- -------------------- ----------------------------
ECON                 189                  MONETARY POLICY
PHILO                198                  MODERN PHILOSOPHY
ECON                 199                  WORKSHOP ON MARX
HIST                 199                  WORKSHOP ON JEFFERSON
PSYCH                181                  WORKSHOP ON NEUROSES

However, Jean realized that this query wouldn't include any course whose description started with the word Workshop because the first letter is a capital W. As a result, she modified the query as shown in Listing 5.19.

Listing 5.19. Using the UPPER function for string comparison.

SQL> select Department_ID, Course_ID, Title
  2  from Course
  3  where
  4  upper(Description) like `%WORKSHOP%';

DEPARTMENT_ID        COURSE_ID            TITLE
-------------------- -------------------- ----------------------------
ECON                 189                  MONETARY POLICY
PHILO                198                  MODERN PHILOSOPHY
ECON                 199                  WORKSHOP ON MARX
HIST                 199                  WORKSHOP ON JEFFERSON
ENGL                 193                  SEMINAR ON THEME ANALYSIS
PSYCH                181                  WORKSHOP ON NEUROSES
6 rows selected.

Jean was correct. Instead of returning only five records, the query now returns six records. By applying the UPPER function to the Description column, she was able to compare the contents of the column without worrying about whether the pattern was in uppercase or lowercase.

You can separate string patterns by the wildcard character, %, as shown in Listing 5.20.

Listing 5.20. Using multiple wildcards.

SQL> select Description
  2  from Course
  3  where
  4  upper(Description) like `%STUDY%BEHAVIOR%';

DESCRIPTION
----------------------------------------------------------------------
Discussion and field study of the strange behavior of the
ÂNacimera culture

Sometimes you really want to search for a string that contains the % character. Normally, the % character, contained in single quotes, is interpreted by the Oracle RDBMS as a wildcard. You place the escape character before the character that you want the Oracle RDBMS to interpret literally. As shown in Listing 5.21, you want to retrieve course descriptions that contain the character %; therefore, you place \% between wildcards.

Listing 5.21. Using an escape character to refer to a literal character.

SQL> select Description
  2  from Course
  3  where
  4  Description like `%\%%' escape `\';

DESCRIPTION
------------------------------------------------------------------
Learn about advanced operations such as % and logarithms.

This query uses the backslash character (\) to tell Oracle that the % that follows the \ should be interpreted literally. You can use the same method when you want to search for an underscore (_), rather than have it represent any single character.

Here are some suggestions for using the LIKE operator in your searches:

• Apply the UPPER or LOWER function to the column you're searching so that all characters are in the same case.
• If you're looking for records where a string occurs at the beginning of a column, don't use the wildcard character at the beginning of the pattern:

  select Department_ID, Title_ID, Description
  from Course
  where
  upper(Description) like `SEMINAR%';