Page 106
You can use the keywords AND and OR to combine multiple conditions that need to be satisfied in a query. For example, to see a list of sophomores whose street address contains the string ASH, you would use something like Listing 5.8 to specify both conditions in the WHERE clause.
Listing 5.8. Using AND to combine query criteria.
SQL> select Last_Name, First_Name, Street_Address 2 from Student 3 where 4 Year = `SOPHOMORE' and 5 Street_Address like `%ASH%'; LAST_NAME FIRST_NAME STREET_ADDRESS ------------------------ ------------------------ ------------------ GORDON IVY 544 ASH ST. JACKEL LINDA 493 ASH ST. DEURRE PIERRE 555 ASH ST. PINKWATER PETER 533 ASH ST.
Notice the word LIKE in the fourth line of the preceding example. This operator is one of SQL's most powerful tools.
The basic syntax for using the LIKE operator is as follows:
column_name LIKE `pattern'
The variables are defined as follows:
column_name is a valid column in the table referenced in the FROM clause.
pattern is a string pattern for which you are searching.
The % serves as a wildcard in this context; it is the equivalent of zero or more characters.
The _ (underscore) is used to signify a placeholder of any single character.
| NOTE |
In the examples that follow, the keyword NULL is all uppercase. I've used uppercase to emphasize the word, but it isn't mandatory; you can use whatever case you choose with any of the Oracle SQL reserved words. |
Page 107
The ORDER BY clause designates which columns should be used to order the rows that are returned by the query. The ORDER BY clause is optional, but remember this: The order in which rows are returned by a query is always arbitrary (whether the table has been indexed or not). Therefore, you'll usually want to specify an ORDER BY clause in a SELECT statement.
For example, you might want a list of instructors at Flugle College sorted first by Department ID and then by instructor last and first names, as shown in Listing 5.9.
Listing 5.9. Ordering retrieved rows by multiple columns.
SQL> select Department_ID, Last_Name, First_Name 2 from Instructor 3 order by Department_ID, Last_Name, First_Name; DEPARTMENT_ID LAST_NAME FIRST_NAME -------------------- ------------------------ ------------------------ ANTHRO DANIELS LAURA ANTHRO HITCHCOCK BORIS BIO EDWARDS SAMANTHA BIO WEISS ROBERTA ECON PARKER WILLIAM ENG CHU STEVEN ENGL CHERNOW BESS HIST RESTON RAPHAEL HIST TORRES PETER MATH CHANG ROGER MATH NILAND MARTINA MATH YOUNG ALLAN PHILO ANGELO ARTHUR PHILO BILLINGS BENJAMIN PHILO RICHARDSON NANCY PSYCH BATES JOSEPH PSYCH JASON JERROLD 17 rows selected.
| NOTE |
You can specify columns in an ORDER BY clause even if they aren't selected from the table, as shown in this example:select Last_Name from Instructor order by PositionYou also can specify columns in the ORDER BY clause regardless if the column is part of an index on the table or not. |
Page 108
By default, Oracle orders the rows in ascending order. To order the rows in descending order, you must add the keyword DESC (for descending) after the column name. You can specify ascending columns and descending columns in the same ORDER BY clause, as shown in Listing 5.10.
Listing 5.10. Specifying descending order.
SQL> select Department_ID, Last_Name, First_Name 2 from Instructor 3 order by Department_ID desc, Last_Name, First_Name; DEPARTMENT_ID LAST_NAME FIRST_NAME -------------------- ------------------------ ------------------------ PSYCH BATES JOSEPH PSYCH JASON JERROLD PHILO ANGELO ARTHUR PHILO BILLINGS BENJAMIN PHILO RICHARDSON NANCY MATH CHANG ROGER MATH NILAND MARTINA MATH YOUNG ALLAN HIST RESTON RAPHAEL HIST TORRES PETER ENGL CHERNOW BESS ENG CHU STEVEN ECON PARKER WILLIAM BIO EDWARDS SAMANTHA BIO WEISS ROBERTA ANTHRO DANIELS LAURA ANTHRO HITCHCOCK BORIS 17 rows selected.
If you want to know how many rows in a table satisfy the specified criteria, but you really don't need to retrieve the rows themselves, you can use the COUNT function. COUNT returns a single row that reports the number of rows that satisfy the specified criteria. Listing 5.11 is an example.
Listing 5.11. Counting rows in a table.
SQL> select count(*) 2 from Instructor 3 where 4 Position = `ASSOCIATE PROFESSOR';
Page 109
COUNT(*)
---------
7
| NOTE |
COUNT is a group function (which you will learn more about on Day 8, "More Sophisticated Queries with SQL"). The asterisk instructs Oracle to return all rows that satisfy the criteria. Instead of the asterisk, you can specify a column name, but if you do, Oracle returns only those rows where the specified column name has been assigned a value (in other words, rows where the column isn't null). |
One major difference between RDBMSs (Relational Database Management System) and older DBMS (Database Management System) technology is the concept of the null value. In non-relational database systems, a special value is used to indicate the absence of a value in a character or numeric field.
In a relational database, a NULL value for a column represents different things:
If you want to retrieve records from a table where a specific column value isn't defined, you can specify the criteria in the WHERE clause. Listing 5.12 is a query that returns the names of students who do not have a fax number.
Listing 5.12. Retrieving rows in which a column is NULL.
SQL> select Last_Name, First_Name 2 from Student 3 where 4 Fax is null; LAST_NAME FIRST_NAME ------------------------ ------------------------ SMYTHE JACKSON HAN COREY
continues