SQL QUESTION drop table roles; drop table movies; drop table directors; drop tab

Question

SQL QUESTION

drop table roles;
drop table movies;
drop table directors;
drop table actors;

create table actors
(actorid integer,
actorname varchar(30),
actordob date,
actordod date,
actornationality varchar(30),
actorgender char(1))

create table directors
(directorid integer,
directorname varchar(30),
directordob date,
directornationality varchar(30))

create table movies
(movieid integer,
title varchar(30),
yearmade integer,
directorid integer,
movieLanguage varchar(30),
boxoffice float,
parts integer)

create table roles
(movieid integer,
actorid integer,
role varchar(30))

1. Output the names of all actors in the movie with the title ‘Imitation Game’.

2. Output the number of movies Benedict Cumberbatch had a role in.

3. Output the names of all actors and the number of roles they have had.

4. Output the names of all actors and the number of roles they have had, but only include actors that have had at least ten roles.

5. Output the name and date of birth for all actors and directors.

6. Output the names of all actors that have never had a role.

7. Output the title, year made, box office, and director name for all movies. If a movie has no director, output the rest of the information anyway.

8. Output the title of the movie with the highest value of box office.

ANY HELP PLEASE

Explanation / Answer

1.

Select actorname from actors inner join roles on actor.actorid = roles.actorid inner join movies on movies.movieid = roles.movieid where movies.title = 'Imitation Game';

2.

Select count(*) from movies inner join roles on movies.movieid = roles.movieid inner join actors on movies.actorid = roles.actorid where actorname = 'Benedict Cumberbatch';

3.

Select actorname,count(movies) from actors inner join roles on actor.actorid = roles.actorid inner join movies on movies.movieid = roles.movieid group by actorname;

4.

Select actorname,count(movies) from actors inner join roles on actor.actorid = roles.actorid inner join movies on movies.movieid = roles.movieid group by actorname where count(movies) >= 10;

5.

(Select actorname,actordob from actors) UNION (select directorname,directordob from directors);

6.

(Select actorname from actors) MINUS (Select actorname from actors inner join roles on actor.actorid = roles.actorid)

7.

Select title,yearmade,boxoffice,directorname from movies left inner join director on movies.directorid = director.directorid ;

8. Output the title of the movie with the highest value of box office.

Select title from movies where boxoffice = (select max(boxoffice) from movies);

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

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