mysql 练习1 电影演员

mysql 练习
https://sqlzoo.net/wiki/More_JOIN_operations
1
select id,title from movie where yr = 1962
2
select yr from movie where title=‘Citizen Kane’
3
select id,title ,yr from movie where title like ‘%Star Trek%’
4
select id from actor where name= ‘Glenn Close’
5
select id from movie where title=‘Casablanca’
6
select actor.name from casting join movie on (movie.id=casting.movieid) join actor on (actor.id=casting.actorid)
where title= ‘Casablanca’
7
select actor.name from movie join casting on (movie.id=casting.movieid) join actor on (actor.id=casting.actorid) where title= ‘Alien’
8
select c.title from (select movie.title title, actor.name name from movie join casting on (movie.id=casting.movieid) join actor on (actor.id=casting.actorid)
) c where c.name=‘Harrison Ford’
9
select c.title from (select movie.title title, actor.name name,ord from movie join casting on (movie.id=casting.movieid) join actor on (actor.id=casting.actorid)
) c where c.name=‘Harrison Ford’ and c.ord<>1
10
select title,actor.name from movie join casting on (movie.id=casting.movieid) join actor on (actor.id=casting.actorid)
where movie.yr=1962 and ord=1
11
SELECT yr,COUNT(title) FROM
movie JOIN casting ON movie.id=movieid
JOIN actor ON actorid=actor.id
WHERE actor.name=‘Rock Hudson’
GROUP BY yr
HAVING COUNT(title) > 1
12
select title ,actor.name from movie
JOIN casting ON movie.id=movieid
JOIN actor ON actorid=actor.id
where movie.id in (select movieid from casting where
casting.actorid=
(SELECT id FROM actor
WHERE name=‘Julie Andrews’) )
and ord=1
13
select actor.name from actor join (select actorid, count(*) number from casting where ord=1 group by actorid ) a on (a.actorid=actor.id)
where number>=15
14

select title,number from movie join (select count(*) number,movieid from casting group by movieid) a on movie.id= a.movieid where yr=1978 order by number desc , title
15
select name from actor join(
select actorid from casting where movieid in (
select movieid from casting where actorid=(select id from actor where name=‘Art Garfunkel’)) and actorid<> (select id from actor where name=‘Art Garfunkel’)) a on actor.id=a.actorid