[SQL][sqlzoo] More JOIN operations

More JOIN operations                                                                                                                      

More JOIN operations - SQLZOO

1.1962 movies

SELECT id, title
 FROM movie
 WHERE yr=1962

2.When was Citizen Kane released?

select yr 
from movie 
where title='Citizen Kane'

3.Star Trek movies

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

4.id for actor Glenn Close

select ID 
from actor
where name ='Glenn Close'

5.id for Casablanca

select ID 
from movie 
where title ='Casablanca'

6.Cast list for Casablanca

SELECT name 
FROM actor 
JOIN casting ON actor.id = casting.actorid
WHERE movieid=11768

7.Alien cast list

SELECT name 
FROM actor 
JOIN casting ON casting.actorid = actor.id
JOIN movie ON movie.id = casting.movieid 
WHERE title = 'Alien'

8.Harrison Ford movies

SELECT title 
FROM actor 
JOIN casting ON casting.actorid = actor.id
JOIN movie ON movie.id = casting.movieid 
WHERE name = 'Harrison Ford'

9.Harrison Ford as a supporting actor

SELECT title 
FROM actor 
JOIN casting ON casting.actorid = actor.id
JOIN movie ON movie.id = casting.movieid 
WHERE name = 'Harrison Ford' and ord !='1'

10.Lead actors in 1962 movies

SELECT title,name 
FROM actor 
JOIN casting ON casting.actorid = actor.id 
JOIN movie ON movie.id = casting.movieid 
WHERE ord ='1' and yr='1962'

11.Busy years for Rock Hudson

SELECT yr,COUNT(title) 
FROM movie 
JOIN casting ON movie.id=movieid
JOIN actor ON actorid=actor.id
WHERE name='Rock Hudson' 
GROUP BY yr 
HAVING COUNT(title) > 1
ORDER BY COUNT(title) DESC 
LIMIT 2;

12.Lead actor in Julie Andrews movies

select title,name
from actor 
join casting on casting.actorid=actor.id
join movie on movie.id=casting.movieid
where movieid in (SELECT movieid FROM casting WHERE actorid IN (SELECT id FROM actor WHERE name='Julie Andrews')) and ord=1

13.Actors with 15 leading roles

SELECT name
FROM actor 
JOIN casting ON id = actorid
AND ord = 1
GROUP BY name
HAVING COUNT(name) >= 15;

14

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

15

SELECT name 
FROM actor 
JOIN casting ON actor.id = casting.actorid 
JOIN movie ON movie.id = casting.movieid and name !='Art Garfunkel' 
where movie.id in(SELECT movieid FROM actor JOIN casting ON actor.id = casting.actorid WHERE name = 'Art Garfunkel')

JOIN Quiz 2                                                                                                                   

1.

SELECT name
  FROM actor INNER JOIN movie ON actor.id = director
 WHERE gross < budget

2.

SELECT *
  FROM actor JOIN casting ON actor.id = actorid
  JOIN movie ON movie.id = movieid

3.

SELECT name, COUNT(movieid)
  FROM casting JOIN actor ON actorid=actor.id
 WHERE name LIKE 'John %'
 GROUP BY name ORDER BY 2 DESC

4.

Table-B

"Crocodile" Dundee

Crocodile Dundee in Los Angeles

Flipper

Lightning Jack

5.

SELECT name
  FROM movie JOIN casting ON movie.id = movieid
  JOIN actor ON actor.id = actorid
WHERE ord = 1 AND director = 351

6.

• link the director column in movies with the primary key in actor
• connect the primary keys of movie and actor via the casting table

7.

Table-B

A Bronx Tale	1993
Bang the Drum Slowly	1973
Limitless	2011