sqlzoo练习12-More Join
This tutorial introduces the notion of a join. The database consists of three tablemovie
, actor
and casting
.
练习-基础
- List the films where the yr is 1962 [Show id, title]
1 | select id, title |
- Give year of ‘Citizen Kane’.
1 | select yr |
- List all of the Star Trek movies, include the id, title and yr (all of these movies include the words Star Trek in the title). Order results by year.
1 | select id, title, yr |
- What id number does the actor ‘Glenn Close’ have?
1 | select id |
- What is the id of the film ‘Casablanca’
1 | select id |
- Obtain the cast list for ‘Casablanca’.
Use movieid=11768, (or whatever value you got from the previous question)
What is a cast list
The cast list is the names of the actors who were in the movie.
将某个电影中的全部演员列出来
1 | select name |
- Obtain the cast list for the film ‘Alien’
Alien这个演员的cast list
1 | select name |
- List the films in which ‘Harrison Ford’ has appeared
笔记:列出这个演员出现的所有电影。
movie 和actor两个表是没有直接联系的,只能通过casting表的actorid字段来进行搭桥
1 | select title |
- List the films where ‘Harrison Ford’ has appeared - but not in the starring role. [Note: the ord field of casting gives the position of the actor. If ord=1 then this actor is in the starring role]
列出该演员出现但不是主演(ord=1)的电影
1 | select title |
- List the films together with the leading star for all 1962 films.
笔记:列出1962年的所有电影和主演名字
- 主演:ord-casting表
- 演员名字:name-actor表
1 | select title, name |
- Which were the busiest years for ‘Rock Hudson’, show the year and the number of movies he made each year for any year in which he made more than 2 movies.
找出该演员的电影数目大于2的年份
- 演员name
- 年份yr
- 电影movie
1 | select yr, count(title) -- 统计电影数目 |
- List the film title and the leading actor for all of the films ‘Julie Andrews’ played in.
找出JA演员参加的电影和主演
1 | select title, name -- 找出电影和主演 |
解题步骤
- 先找出JA参加的电影的全部演员actroid
- 找出上步中的演员参演的电影movieid
- 确定主演ord=1
- 找出电影和主演
- Obtain a list, in alphabetical order, of actors who’ve had at least 15 starring roles.
找出至少当过15次主角(ord,casting表)的演员名字name(actor)
按照名字的字母排序
1 | select name |
- List the films released in the year 1978 ordered by the number of actors in the cast, then by title.
- 1978年发行的电影
- 根据电影中的演员数量进行排名
- 再根据title排名
1 | select title, count(*) as actorcount |
- List all the people who have worked with ‘Art Garfunkel’.
找出和AG合作的全部演员
1 | select name |