Fork me on GitHub

sqlzoo练习13-join-quiz2

sqlzoo练习13-join-quiz2

还是通过下面的3张表进行练习

练习

  1. Select the statement which lists the unfortunate directors of the movies which have caused financial loses (gross < budget)

找出毛利小于预算的导演

需要注意的是导演们的名字也是在actor表中的

1
2
3
4
select name
from actor
inner join movie on actor.id=director -- actor表中的id和director相同即可
where gross < budget;
  1. Select the correct example of JOINing three tables

3个表的联结通过两个Join实现:actor—>casting—>movie

1
2
3
4
select *
from actor
join casting on actor.id=actorid
join movie on movie.id=movieid;
  1. Select the statement that shows the list of actors called ‘John’ by order of number of movies in which they acted

找出由名字中包含John的演员的电影(通配符的使用),根据电影数量排序

笔记:order by 排序的时候可以使用字段名字,也可以使用字段的相对位置

1
2
3
4
5
6
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)
  1. Select the result that would be obtained from the following code:

选择由该演员出演主角的电影

1
2
3
4
5
6
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

  1. Select the statement that lists all the actors that starred in movies directed by Ridley Scott who has id 351

找出由RS导演的电影的主演

1
2
3
4
5
6
select name
from movie
join casting on movie.id=movieid
join actor on actor.id=actorid
where ord=1 -- 主演
and director = 351 -- 导演编号
  1. There are two sensible ways to connect movie and actor. They are:

两种方式将movieactor表进行联结

  • 通过director字段和actor表中的主键相连接
  • 通过casting表将另外两个表连接

image-20200130183908556

  1. Select the result that would be obtained from the following code:

通过代码选出正确答案

1
2
3
4
5
6
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-20200130184344783

本文标题:sqlzoo练习13-join-quiz2

发布时间:2020年01月30日 - 18:01

原始链接:http://www.renpeter.cn/2020/01/30/sqlzoo%E7%BB%83%E4%B9%A013-join-quiz2.html

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

Coffee or Tea