More_JOIN_operations.sql

--Table 1: movie(id, title, yr, director, budget, gross)
--Table 2: actor(id, name)
--Table 3: casting(movieid, actorid, ord)

--1. List the films where the yr is 1962.

SELECT id, title
 FROM movie
 WHERE yr='1962';
 
--2. Give year of 'Citizen Kane'.

SELECT yr
 FROM movie
 WHERE title='Citizen Kane';
 
--3. List all of the Star Trek movies, include the id, title and yr 
--(all of these movies include the words Star Trek in the title). Order results by year.

SELECT id, title, yr
 FROM movie
 WHERE title LIKE '%Star Trek%';
 
--4. What id number does the actor 'Glenn Close' have?

SELECT id
 FROM actor
 WHERE name='Glenn Close';
 
--5. What is the id of the film 'Casablanca'. 

SELECT id
 FROM movie
 WHERE title='Casablanca';
 
--6. Obtain the cast list for 'Casablanca'. Use movieid=11768.

SELECT name
 FROM actor JOIN casting ON actor.id=casting.actorid
 WHERE movieid=11768;
 
--7. Obtain the cast list for the film 'Alien'.

SELECT name
 FROM actor JOIN casting ON actor.id=casting.actorid
 WHERE casting.movieid =
  (SELECT id
    FROM movie
    WHERE title='Alien');
                      
 --8. List the films in which 'Harrison Ford' has appeared.
 
SELECT title
 FROM movie JOIN casting 
 ON movie.id=casting.movieid
 WHERE casting.actorid =
   (SELECT id
     FROM actor
     WHERE name='Harrison Ford');
                          
 --9. List the films where 'Harrison Ford' has appeared - but not in the starring role. 
 
SELECT movie.title
 FROM movie
 JOIN casting ON casting.movieid=movie.id
 JOIN actor ON actor.id=casting.actorid
 WHERE actor.name='Harrison Ford' 
 AND casting.ord !=1;
 
 --10. List the films together with the leading star for all 1962 films.
 
SELECT movie.title, actor.name
 FROM movie
 JOIN casting ON casting.movieid=movie.id
 JOIN actor ON actor.id=casting.actorid
 WHERE movie.yr='1962' and casting.ord=1;
 
--11. Which were the busiest years for 'Rock Hudson', 
--show the year and the number of movies he made each year 
--for any year in which he made more than 2 movies.

SELECT movie.yr, COUNT(title)
 FROM movie
 JOIN casting ON casting.movieid=movie.id
 JOIN actor ON actor.id=casting.actorid
 WHERE actor.name='Rock Hudson'
 GROUP BY movie.yr
 HAVING COUNT(title)>2;
 
--12. List the film title and the leading actor for all of the films 'Julie Andrews' played in.

SELECT movie.title, actor.name
 FROM movie
 JOIN casting ON (casting.movieid=movie.id AND casting.ord=1)
 JOIN actor ON actor.id=casting.actorid
  WHERE casting.movieid IN 
   (SELECT movieid FROM casting
     WHERE actorid IN (
        SELECT id FROM actor
         WHERE name='Julie Andrews'));