For the purposes of this course, we will limit ourselves primarily
to what are known as "unnesting" queries. In particular, we won't
make any use of the cursor feature mentioned in your
textbook.
All the examples on this web page use the movies database you will be working with in lab 2. You can create that database using the script ORmovies.sql.
The first thing to know is that whenever you use a nested table as
a source to select from, you need to wrap it with
table(...). For example, suppose we want a list of the
movies, showing for each one the title of the movie and the number of
actors who appear in it. We could use the following query:
select m.title, (select count(*) from table(m.actors)) from movies m;
Note in particular that in order to select a count of rows from the
movie's nested table of actors, we selected from
table(m.actors).
The same principle holds even if we move the subselect into a
where clause; as long as the subselect is selecting from
a nested table, we need table(...). For example, suppose
we just want to know which movies have no actors at all in them. We
could test whether the count of actors is zero:
select m.title from movies m where (select count(*) from table(m.actors)) = 0;
or we could equivalently check that no actor exists for the movie:
select m.title from movies m where not exists (select * from table(m.actors));
Both variants use table(...) because a selection from
the nested table is occurring. As it turns out, Oracle provides
features that would allow us to more directly formulate our queries by
applying functions to the nested tables, rather than by selecting
from them. In these versions, we do not use
table(...):
select m.title, cardinality(m.actors) from movies m; select m.title from movies m where m.actors is empty;
Returning to the use of table(...) to indicate
selection from a nested table, you can also use this feature in the
main select, rather than only in subselects as shown thus far. For
example, let's consider queries of the following form:
select ... from movies m, table(m.actors) a where ...;
In this query, the selection is joining together each row from the
movies table with the rows from that particular movie's
nested table of actors. This does not form a
full cross product of every movie with every actor. Rather, even
without any explicit restriction in the where clause, the
combinations are limited to sensible ones, where the actor and movie
go together. This may be a bit surprising to some students, but really
is just an extension of what we saw in the earlier queries. For
example, we only counted the actors who appeared in a particular
movie, rather than counting all actors whatsoever.
Now we can turn to filling in the ... parts of the
prior query to get a real example. This brings us face to face with
the second big thing you need to know about working with nested
tables. Namely, you cannot access an attribute of a particular actor,
such as the last name, by using an expression like
a.lname. This is because the nested table does not
contain columns called lname and fnames.
Instead, it contains only a single column, which is a reference to a
person object (in the separate persons table), which has
the lname and fnames attributes. Thus, we
need to explicitly navigate from a (the row in the nested
table) to the lname attribute by way of that intervening column.
Because that column was never given an explicit name, Oracle
automatically gives it the pseudoname column_value.
Don't worry if this is very mysterious to you; it has mystified a
lot of far more experienced computer scientists as well. All you need
to remember is to use an expression like
a.column_value.lname rather than the incorrect
a.lname.
As a concrete example, if what we want to produce is a table of movie titles and the corresponding last names of actors who appeared in those movies, we would use the following query:
select m.title, a.column_value.lname from movies m, table(m.actors) a;
That query would produce a rather long result table, so a better
example might use a where clause to limit the rows
produced. For example, consider the following trivia question. What
cases exist where a movie title has the same length as the last name
of one of the actors in that movie? Given that there is an SQL
function length(...) for finding the length of a string,
this trivia question can be answered with the following query:
select m.title, a.column_value.lname from movies m, table(m.actors) a where length(m.title) = length(a.column_value.lname);