Database Reference
In-Depth Information
Note:
The wildcard
%
is used to denote that the string being searched for must begin
with the character(s) specified; the characters that follow do not matter.
Example 35:
Get names of all second year courses (assuming that a three-digit
sequence number after the course acronym, indicates the level of the course):
Example 36:
Referring to the Oracle employee-department database (of the previous
sub-section), list employees within a salary range of 50,000 and 120,000:
Example 37:
List all students enrolled in MIS, Computer Science or Mathematics
(assuming sample data of Figure 7-2):
Note:
In the next section, you will see that the IN operator works quite nicely
with sub-queries.
12.10 Nested Queries
A sub-query is a nested query, i.e. a query within another query. A sub-query is
particularly useful when a query on a given table depends on data in the table itself.
Additionally, nested queries are alternatives to queries involving natural joins (not
recommended for complex join conditions). There are two types of nested queries:
•
Single-row sub-query: only one row is retrieved
•
Multiple-row sub-query: multiple rows are retrieved
The typical format for a sub-query is:
