Question

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

1) Find all the Comments created by “Mike_34” with a score bigger than 80

2) Find the Film title from all the movies with an actor with first name “TOM”.

3) Find the full name of all the actors in the movies with at least a comment with a score equal to 100.

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

Following are the queries in MSQL for the given questions.

1. SELECT comment from COMMENTS where reviewer_name = ‘Mike_34’ AND score > 80;

2. SELECT FILM.title from FILM INNER JOIN ON FILM.film_id = FILM_ACTOR.film_id INNER JOIN FILM_ACTOR.actor_id = ACTOR.actor_id where ACTOR.first_name = ‘TOM’

3. SELECT CONCAT(ACTOR.first_name, ‘ ’, ACTOR.last_name) FROM ACTOR INNER JOIN COMMENTS.film_id = FILM_ACTOR.film_id INNER JOIN FILM_ACTOR.actor_id = ACTOR.actor_id where COMMENTS.score = 100