Question

I am having trouble writing these queries in MYSQL. Using the schema listed below, please write the following queries in MYSQL:

1) Find the actors that have movies in all the categories.

2) Movies have a length in minutes. Show a list with all the lengths ordered from shorter to longer, and the number of movies for each of the lengths.

3) Show the 10 first results with the first name, last name and average length of the movies for each actor order by actor first name, and actor last name.

Schema:

Consider a movie relational database schema description provided below which is used to manage a movie database, where:

A movie can have 0 or more actors

A movie can have 1 or more categories

A movie has 1 and only one language

Rating is one of these values: 'G','PG','PG-13','R','NC-17'

People can comment about the movies on a webpage, and the comments are stored in the comments table. The reviewer_name is introduced by the person on each comment, so we don't have a table with “reviewers”. A reviewer can create any number of comments about a movie. The comments will have a score about the movie with values from 0 to 100.

Note, the last update fields are going to be stored as a “timestamp”.

IMPORTANT: 2 entries in the same relation can have the same lastupdate, so, for example, 2 movies can have the same lastupdate value.

The relations are:

ACTOR (actor_id, first_name, last_name, last_update)

LANGUAGE (language_id, name, last_update)

CATEGORY (category_id, name, last_update)

FILM (film_id, title, description, release_year, language_id, length, rating, last_update)

FILM_ACTOR(actor_id, film_id, last_update)

FILM_CATEGORY (film_id, category_id, last_update)

COMMENTS (review_id, film_id, reviewer_name, comment, score, last_update)

Answer 1

-- 1

SELECT A.actor_id, first_name, last_name

FROM FILM_ACTOR AS FA, ACTOR A

WHERE FA.actor_id = A.actor_id AND NOT EXISTS(

SELECT *

FROM CATEGORY C

WHERE NOT EXISTS(

SELECT *

FROM FILM_CATEGORY AS FC

WHERE FA.film_id = FC.film_id AND C.category_id = FC.category_id

)

);

-- 2

SELECT `length`, COUNT(*) AS noOfMovies

FROM FILM

GROUP BY `length`

ORDER BY `length`;

-- 3

SELECT first_name, last_name, AVG(length) AS avgLength

FROM ACTOR AS A, FILM_ACTOR AS FA, FILM AS F

WHERE A.actor_id = FA.actor_id AND FA.film_id = F.film_id

GROUP BY A.actor_id, first_name, last_name

ORDER BY first_name, last_name

LIMIT 10;

Let me know if you have any clarifications. Thank you...