Database Reference
In-Depth Information
SELECT NAME FROM ARTIST WHERE ARTIST_ID = ANY
(SELECT GUESTARTIST_ID FROM GUESTAPPEARANCE);
Those are the conditional comparisons available in Oracle SQL. Now
let's look at what I like to call logical operators.
5.3
Logical Operators in the WHERE Clause
Logical operators in Oracle SQL are AND, OR, and NOT. They work to
concatenate multiple conditional expressions together. Precedence rules
apply in that expressions are evaluated from left to right, unless overridden
by parenthesised (bracketed) sections. NOT has higher precedence than
AND, followed by OR. In the following example, the two TITLE column
checks are bracketed and are thus evaluated first, followed by the
RECORDING_DATE using AND. The result is shown in Figure 5.6.
SELECT TITLE, RECORDING_DATE FROM SONG
WHERE RECORDING_DATE > '01-JUL-2001'
AND (TITLE LIKE '%Me%' OR TITLE LIKE '%You%');
Removing the brackets in the following code snippet, as shown in Figure
5.7, can produce a spurious or meaningless result.
SELECT TITLE, RECORDING_DATE FROM SONG
WHERE RECORDING_DATE > '01-JUL-2001'
AND TITLE LIKE '%Me%' OR TITLE LIKE '%You%';
Next we look at Top-N queries.
5.4
Top-N Queries
Database tables grow as more and more data is added to them. One of the
challenges of the DBA or application programmer is to be able to quickly
assess the contents of large tables. This sometimes requires a query on thou-
sands, even millions of rows. The Top-N query feature introduced in Oracle
Database 9
provides the capability to retrieve small sections of a large table
without having to write an application or use expensive third-party tools to
dig into voluminous or copious amounts of data.
i
