sqlzoo [MoreJoins]

Problem 1 : List the films WHERE the yr IS 1962 [SHOW id, title]

SELECT id, title FROM movie WHERE yr = 1962



Problem 2 : Give YEAR OF ‘Citizen Kane’.


SELECT yr
FROM movie
WHERE title='Citizen Kane'


Problem 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.)

SELECT id,title, yr
FROM movie
WHERE title LIKE '%Star%Trek%'
ORDER BY yr ASC


Problem 4 : What are the titles OF the films WITH id 11768, 11955, 21191


SELECT title
FROM movie
WHERE id IN (11768, 11955, 21191)


Problem 5 : What id NUMBER does the actor ‘Glenn Close’ have?

SELECT id
FROM actor
WHERE name='Glenn Close'



Problem 6 : What IS the id OF the film ‘Casablanca’

SELECT id
FROM movie
WHERE title = 'Casablanca'


REAL JOIN questions BEGIN :
Problem 7 : Obtain the CAST list FOR ‘Casablanca’. USE the id VALUE that you obtained IN the previous question.


SELECT a.Name
FROM
casting c
JOIN actor a ON
c.actorid = a.id
WHERE movieid = 27



Problem 8 : Obtain the CAST list FOR the film ‘Alien’

SELECT a.Name
FROM
 casting c
JOIN actor a ON
c.actorid = a.id
WHERE movieid IN
(SELECT id FROM movie WHERE title = 'Alien');



Problem 9 : List the films IN which ‘Harrison Ford’ has appeared

SELECT m.title
FROM
 movie m
JOIN casting c ON
 m.id = c.movieid
WHERE c.actorid IN
(SELECT id FROM actor WHERE name='Harrison Ford');




Problem 10 : List the films WHERE ‘Harrison Ford’ has appeared - but NOT IN the star ROLE.

SELECT m.title
FROM
 movie m
JOIN casting c ON
 m.id = c.movieid AND
 c.ord <> 1
WHERE c.actorid IN
(SELECT id FROM actor WHERE name='Harrison Ford')




Problem 11: List the films together WITH the LEADING star FOR ALL 1962 films.

SELECT m.title,a.name
 FROM casting c JOIN movie m ON
   m.id = c.movieid
 JOIN actor a ON
   a.id = c.actorid  AND
   c.ord = 1
WHERE m.yr = 1962




Problem 12 : Which were the busiest years FOR ‘John Travolta’. SHOW the NUMBER OF movies he made FOR each 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
)



Problem 13 : List the film title AND the LEADING actor FOR ALL OF ‘Julie Andrews’ films.

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



Problem 14 : Obtain a list OF actors IN who have had at least 30 starring roles.

SELECT a.name
  FROM casting c JOIN movie m ON
    m.id = c.movieid
  JOIN actor a ON
    a.id = c.actorid AND
    c.ord = 1
GROUP BY a.name
HAVING COUNT(m.id) >= 30




Problem 15 : List the 1978 films BY ORDER OF CAST list SIZE.

SELECT m.title, COUNT(c.actorid)
  FROM casting c JOIN movie m ON
    m.id = c.movieid
  WHERE m.yr = 1978
GROUP BY m.title
ORDER BY COUNT(c.actorid) DESC




Problem 16 : List ALL the people who have worked WITH ‘Art Garfunkel’.

SELECT a.name
 FROM casting c JOIN actor a ON
  a.id = c.actorid
 WHERE
  a.name <> 'Art Garfunkel' AND
  c.movieid
 IN (
  SELECT m.id
     FROM casting c JOIN movie m ON
       m.id = c.movieid
     JOIN actor a ON
       c.actorid = a.id
     WHERE a.name = 'ART Garfunkel'
)
ORDER BY a.name