[SOLVED] Writing an SQL statement that references a nested VARRAY

Writing an SQL statement that references a nested VARRAY

I am trying to write an SQL statement that queries a column actors (defined as VARRAY(5)) in a table named movie without using PL/SQL, to answer the question:

List any actors that appear in multiple movies, showing the movie title and actor

select a.column_value, count(*), m.title
from movie m, table (m.actors) a
where count(*) > 2,
group by a.column_value;

Solution

You should use the HAVING clause for checking the condition instead of where clause and additionally LISTAGG may be used to show the movie titles.

CREATE OR REPLACE TYPE actortype IS VARRAY(5) OF VARCHAR2(20);
/

create table movie ( id integer , title VARCHAR2(100), actors actortype );

INSERT INTO movie (
     id,
     title,
     actors
) VALUES (
     1,
     'The Shawshank Redemption',
     actortype('Morgan Freeman','Tim Robbins','Bob Gunton')
);
INSERT INTO movie (
     id,
     title,
     actors
) VALUES (
     1,
     'The Dark Knight',
     actortype('Morgan Freeman','Christian Bale','Heath Ledger')
);

Query

SELECT a.column_value as Actor,
       COUNT(*) as num_of_movies,
       LISTAGG( m.title, ',') WITHIN GROUP ( ORDER BY id ) as movies
FROM movie m,
     TABLE ( m.actors ) a
GROUP BY a.column_value
HAVING COUNT(*) > 1;

ACTOR          NUM_OF_MOVIES MOVIES
------------  -------------  -------
Morgan Freeman  2            The Dark Knight,The Shawshank Redemption