sqlzoo more join -- Movie Database

1. List the first film of 1962, [display id, title]

SELECT  id,title
 FROM movie
 WHERE yr=1962

2. The first movie year of the movie "Citizen Kane".

select yr from movie where title= 'Citizen Kane' 

3. List all the Star Trek series movies, including id, title and yr (this system movie starts with Star Trek). In chronological order.

select  id,title,yr from movie
where title like 'Star Trek%'
group by yr

4. What is the name of the movie with ID 11768, 11955, 21191?

select title from movie where id in(11768, 11955, 21191)

5. What is the number id of actress' Glenn Close '?

select id from actor where name='Glenn Close'

6. What is the number id of Casablanca?

select id from movie where title='Casablanca'

Syntagmatic grammar

7. List the actors of the movie "Casablanca".
What is the cast?
Use movieid=11768, which is the result of your last question.

select name from actor 
join casting on id=actorid 
where movieid=11768

8. List of actors showing Alien 'Alien' movies.

select name from casting 
join movie on movieid=movie.id 
join actor on actor.id=casting.actorid
where title='Alien'

9. List the films performed by actor Harrison Ford.

select title from casting 
join movie on movieid=movie.id 
join actor on actor.id=casting.actorid
where name= 'Harrison Ford'

10. List the films that actor Harrison Ford has performed, but he is not the first leading actor.

select title from 
casting join movie on movieid=movie.id 
join actor on actor.id=casting.actorid
where name= 'Harrison Ford' and ord!=1

11. List the first film of 1962 and its first protagonist.

select distinct title,name from 
casting join movie on movieid=movie.id 
join actor on actor.id=casting.actorid
where yr=1962 and ord=1

A difficult subject
12. What's the busiest year for John Travolta? Show the year and the number of movies in that year.

SELECT yr,COUNT(title) FROM
  movie JOIN casting ON movie.id=movieid
         JOIN actor   ON actorid=actor.id
where name='John Travolta'
GROUP BY yr
HAVING COUNT(title)=(SELECT MAX(c) FROM
(SELECT yr,COUNT(title) AS c FROM
   movie JOIN casting ON movie.id=movieid
         JOIN actor   ON actorid=actor.id
 where name='John Travolta'
 GROUP BY yr) AS t
)

13. List the names of the films in which actress Julie Andrews participated and the first protagonist.
Is the movie "Little Miss Marker" listed twice?
She rejoined the film Little Miss Marker in 1980. The original was made in 1934, and she also participated. The film name is not unique. Use movie numbers in subqueries.

SELECT title,name FROM 
  movie JOIN casting ON movie.id=movieid
         JOIN actor   ON actorid=actor.id
  WHERE movieid in (select distinct movieid from casting where actorid in (select id from actor where name='Julie Andrews' )) and ord=1;

14. List in alphabetical order which actor has played the first leading role for 30 times.

select name from movie JOIN casting ON movie.id=movieid
         JOIN actor   ON actorid=actor.id
where ord=1
group by name 
having count(*)>=30
order by name

15. List the names and the number of characters of the first film in 1978, in order of their number.

select title,count(actorid) as c
from movie join
casting on movie.id=casting.movieid
where yr=1978
group by title
order by c desc

16. List the names of actors who have worked with actor Art Garfunkel

select name from casting join  actor on id=actorid 
where movieid in (select movieid from casting join actor on id=actorid 
where name='Art Garfunkel') and name!='Art Garfunkel';