SQL Queries Exercise: Assume the following Tables: Table: ACTOR Columns: id, fir

Question

SQL Queries Exercise:

Assume the following Tables:

Table: ACTOR Columns: id, first_name, last_name, gender

Table: MOVIES Columns: id, name, year, rank

Table: ROLES Columns: actor_id, movie_id Table:

DIRECTORS Columns: id, first_name, last_name

For the queries below, please use the above tables and indicate the SQL function/script:

1. Find all the actors that played in the movie Ocean's Twelve.

2. Find the time period in which each actor was active, by listing the earliest and the latest year in which the actor starred in a film.

3. Repeat the query above #2, but only list actors that have starred in at least 10 movies.

4. Find the director that has directed the largest number of movies.

5. List the director that worked with the largest number of actors.

Explanation / Answer

1) select * from ACTOR where id in (select actor_id from ROLES where movie_id=(select id from MOVIES where name='Ocean's Twelve'))

used subquery technique here in the query to retrieve the actors details as the id of both movie and actor works as foreign keys in ROLES table, the query should get its data from three tables

2) select ACTOR.id,ACTOR.first_name,ACTOR.last_name,min(year) AS earlies_year,max(year) as Latest_year,max(year)-min(year) As Timeperiod from ACTOR,MOVIES,ROLES where ACTOR.id=ROLES.actor_id and MOVIES.id=ROLES.movie_id

In this query , i had used the join technique to join the three tables as the ROLES table is necessary to integrate the data of MOVIES and ACTOR. The aggregate functions min and max are used to retrieve the from and to years which represents the active term of the actor.

3) select ACTOR.id,ACTOR.first_name,ACTOR.last_name,min(year),max(year),max(year)-min(year) As Timeperiod from ACTOR,MOVIES,ROLES where ACTOR.id=ROLES.actor_id and MOVIES.id=ROLES.movie_id having (select count(*) from ROLES group by actor_id)>10

in this query i hace used join technique along with having clause to retrieve the actors details who acted in more than ten movies

Question numbers 4 and 5 do not have sufficient information in the given tables .

AS the DIRECCTORS table doesn't have any foreig key relation with other tables such as MOVIES or ROLES , the answer can't be written.

Plese provide the full tablular structure of database so that we can answer questions 4 and 5.

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.