sqlzoo练习13-join-quiz2
还是通过下面的3张表进行练习
image
练习
- Select the statement which lists the unfortunate directors of the movies which have caused financial loses (gross < budget)
找出毛利小于预算的导演
需要注意的是导演们的名字也是在actor表中的
select name
from actor
inner join movie on actor.id=director -- actor表中的id和director相同即可
where gross < budget;
- Select the correct example of JOINing three tables
3个表的联结通过两个Join实现：
actor—>casting—>movie
select *
from actor
join casting on actor.id=actorid
join movie on movie.id=movieid;
- Select the statement that shows the list of actors called 'John' by order of number of movies in which they acted
找出由名字中包含
John的演员的电影（通配符的使用），根据电影数量排序笔记：
order by排序的时候可以使用字段名字，也可以使用字段的相对位置
select name, count(movieid) -- 统计数量
from casting
join actor on actorid=actor.id -- 两个表的联结
where name like 'John %'
group by name -- 名字分组
order by 2 desc -- 2 表示的是第2个字段count(movieid)
- Select the result that would be obtained from the following code:
选择由该演员出演主角的电影
select title
from movie
join casting on (movieid=movie.id)
join actor on (actorid=actor.id)
where name='Paul Hogen'
and ord=1
image-20200130183611907
- Select the statement that lists all the actors that starred in movies directed by Ridley Scott who has id 351
找出由RS导演的电影的主演
select name
from movie
join casting on movie.id=movieid
join actor on actor.id=actorid
where ord=1 -- 主演
and director = 351 -- 导演编号
- There are two sensible ways to connect movie and actor. They are:
两种方式将
movie和actor表进行联结
- 通过director字段和actor表中的主键相连接
- 通过casting表将另外两个表连接
image-20200130183908556
- Select the result that would be obtained from the following code:
通过代码选出正确答案
select title, yr
from movie, casting, actor
where name='Robert De Niro'
and movieid=movie.id
and actorid=actor.id
and ord=3 -- 排名第3
image
image
