Question

Movie Database

This database features two entities (MOVIES and ACTORS) in a many-to-many relation. Each entity has its own table. A third table, CASTING, is used to link them. The relationship is many-to-many because each film features many actors and each actor has appeared in many films. One possible state of the database is shown.

MOVIE

id	title	yr	director	budget	gross
10003	"Crocodile" Dundee II	1988	38	15800000	239606210
10004	'Til There Was You	1997	49	10000000

ACTOR

id	name
20	Paul Hogan
50	Jeanne Tripplehorn

CASTING

movieid	actorid	ord
10003	20	4
10004	50	1

1. List the films where 'Harrison Ford' has appeared - but not in the star role. [Note: the ord field of casting gives the position of the actor. If ord=1 then this actor is in the starring role]

2. Obtain a list in alphabetical order of actors who've had at least 30 starring roles.

Answer 1

List the films where 'Harrison Ford' has appeared - but not in the star role. [Note: the ord field of casting gives the position of the actor. If ord=1 then this actor is in the starring role]

select m.title from movie as m,actor as a,casting as c where a.name='Harrison Ford' and c.ord<>1 and m.id=c.movieid and a.id=c.actorid

Obtain a list in alphabetical order of actors who've had at least 30 starring roles.

select c.actorid,a.name from actor as a,casting as c where a.id=c.actorid and c.ord =1 group by c.actorid having count(c.movieid)>=30 order by a.name