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 Content and the Reviewer Name for each comment, about “ACADEMY DINOSAUR” only if the same reviewer has commented about “ACE GOLDFINGER” too.

2) Retrieve the title of all the Movies in Japanese without any comment, ordered alphabetically.

3) Find all the movie titles where an actor called “TOM” or an actor called “BEN" acted, where there was another actor called “MARY” acting too.

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

Table Create

CREATE TABLE ACTOR (actor_id INT PRIMARY KEY, first_name VARCHAR(20), last_name VARCHAR(20), last_update DATE);

CREATE TABLE LANGUAGE (language_id INT PRIMARY KEY, name VARCHAR(20), last_update DATE);
CREATE TABLE CATEGORY (category_id INT PRIMARY KEY, name VARCHAR(20), last_update DATE);
CREATE TABLE FILM (film_id INT PRIMARY KEY, title VARCHAR(20), description VARCHAR(20), release_year INT, language_id INT, length INT, rating INT, last_update DATE);
CREATE TABLE FILM_ACTOR(actor_id INT, film_id INT, last_update DATE);
CREATE TABLE FILM_CATEGORY (film_id INT, category_id INT, last_update DATE);
CREATE TABLE COMMENTS (review_id INT PRIMARY KEY, film_id INT, reviewer_name VARCHAR(20), comment VARCHAR(20), score INT, last_update DATE);

insert into LANGUAGE values(1,'Japanese','11-01-2018');
insert into LANGUAGE values(2,'English','11-01-2018');
Select * from LANGUAGE;

insert into FILM values(101,'ACADEMY DINOSAUR','none',2011,1,100,10,'01-01-2018');
insert into FILM values(102,'ACE GOLDFINGER','none',2015,2,101,5,'11-01-2018');
insert into FILM values(103,'ACE','none',2013,2,101,5,'11-01-2018');
insert into FILM values(104,'Pirates Of Curebian','none',2013,1,101,5,'11-01-2018');
Select * from FILM;

insert into COMMENTS values(201,101,'viral','good',1,'11-01-2018');
insert into COMMENTS values(202,102,'viral','Not Bad',5,'11-01-2018');
insert into COMMENTS values(203,102,'poonam','Not Bad',8,'11-01-2018');
Select * from COMMENTS;

insert into ACTOR values(301,'Tom','V','11-01-2018');
insert into ACTOR values(302,'Ben','V','11-01-2018');
insert into ACTOR values(303,'Mary','V','11-01-2018');
Select * From ACTOR;

insert into FILM_ACTOR values(301,101,'11-01-2018');
insert into FILM_ACTOR values(302,102,'11-01-2018');
insert into FILM_ACTOR values(301,103,'11-01-2018');
insert into FILM_ACTOR values(303,103,'11-01-2018');
Select * From FILM_ACTOR;

Query:
/*this is called nested query which will fetch data which is review for both acadamey Dinosur and ACE GOLDFINGER*/
Select reviewer_name from COMMENTS where film_id=(Select film_id from FILM WHERE title='ACADEMY DINOSAUR') and reviewer_name in((Select reviewer_name from COMMENTS where film_id=(Select film_id from FILM WHERE title='ACE GOLDFINGER')));

Query:
/*Here it which is japanes language film and not review*/
Select title from film where language_id=(Select language_id from LANGUAGE where name='Japanese') and film_id not in(Select film_id from comments) order by title;

Query:
/*this is example of union where we return title in both query and from both we */
SELECT title from film inner join FILM_ACTOR on film.film_id=FILM_ACTOR.film_id inner join ACTOR on actor.actor_id=FILM_ACTOR.actor_id where actor.actor_id in(Select actor_id from actor where first_name='TOM' or first_name='Ben') and title in (SELECT title from film inner join FILM_ACTOR on film.film_id=FILM_ACTOR.film_id inner join ACTOR on actor.actor_id=FILM_ACTOR.actor_id where actor.actor_id in(Select actor_id from actor where first_name='Mary'));

if you still have any Problem regarding this question please comment and if you like my code please appreciate me by thumbs up thank you.........