[SQL][sqlzoo] More JOIN operations

0.1. More JOIN operations                                                                                                                      

More JOIN operations - SQLZOO

1. 1.1962 movies

      

      

      

    
    

      <sql />
    
SELECT id, title
 FROM movie
 WHERE yr=1962

2. 2.When was Citizen Kane released?

      

      

      

    
    

      <sql />
    
select yr 
from movie 
where title='Citizen Kane'

3. 3.Star Trek movies

      

      

      

    
    

      <sql />
    
select id,title,yr 
from movie 
where title like'%Star Trek%' 
order by yr

4. 4.id for actor Glenn Close

      

      

      

    
    

      <sql />
    
select ID 
from actor
where name ='Glenn Close'

5. 5.id for Casablanca

      

      

      

    
    

      <sql />
    
select ID 
from movie 
where title ='Casablanca'

6. 6.Cast list for Casablanca

      

      

      

    
    

      <sql />
    
SELECT name 
FROM actor 
JOIN casting ON actor.id = casting.actorid
WHERE movieid=11768

7. 7.Alien cast list

      

      

      

    
    

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

8. 8.Harrison Ford movies

      

      

      

    
    

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

9. 9.Harrison Ford as a supporting actor

      

      

      

    
    

      <sql />
    
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. 10.Lead actors in 1962 movies

      

      

      

    
    

      <sql />
    
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. 11.Busy years for Rock Hudson

      

      

      

    
    

      <sql />
    
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. 12.Lead actor in Julie Andrews movies

      

      

      

    
    

      <sql />
    
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. 13.Actors with 15 leading roles

      

      

      

    
    

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

14. 14

      

      

      

    
    

      <sql />
    
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. 15

      

      

      

    
    

      <sql />
    
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')

15.1. JOIN Quiz 2                                                                                                                   

1.

      

      

      

    
    

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

2.

      

      

      

    
    

      <sql />
    
SELECT *
  FROM actor JOIN casting ON actor.id = actorid
  JOIN movie ON movie.id = movieid

3.

      

      

      

    
    

      <sql />
    
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.

      

      

      

    
    

      <sql />
    
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