SQL练习六--More JOIN operations
movie
Field name | Type | Notes |
---|---|---|
id | INTEGER | An arbitrary unique identifier |
title | CHAR(70) | The name of the film - usually in the language of the first release. |
yr | DECIMAL(4) | Year of first release. |
director | INT | A reference to the actor table. |
budget | INTEGER | How much the movie cost to make (in a variety of currencies unfortunately). |
gross | INTEGER | How much the movie made at the box office. |
example of Movie
id | title | yr | director | budget | gross |
---|---|---|---|---|---|
10003 | "Crocodile" Dundee II | 1988 | 38 | 15800000 | 239606210 |
10004 | 'Til There Was You | 1997 | 49 | 10000000 |
actor
Field name | Type | Notes |
---|---|---|
id | INTEGER | An arbitrary unique identifier |
name | CHAR(36) | The name of the actor (the term actor is used to refer to both male and female thesps.) |
example of actor
id | name |
---|---|
20 | Paul Hogan |
50 | Jeanne Tripplehorn |
casting
Field name | Type | Notes |
---|---|---|
movieid | INTEGER | A reference to the movie table. |
actorid | INTEGER | A reference to the actor table. |
ord | INTEGER | The ordinal position of the actor in the cast list. The
star of the movie will have ord value 1 the co-star will have value 2, ... |
example of casting
movieid | actorid | ord |
---|---|---|
10003 | 20 | 4 |
10004 | 50 | 1 |
1、1962 movies(1962年电影)
List the films where the yr is 1962 [Show id, title]
获取1962年上映的电影id和名称。
SELECT id, title FROM movie WHERE yr=1962;
2、When was Citizen Kane released?(公民凯恩什么时候上映的)
Give year of 'Citizen Kane'.
获取《公民凯恩》上映的年份。
select yr from movie where title='Citizen Kane';
3、Star Trek movies(星际迷航系列电影)
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.
获取《星际迷航》系列的id,电影名称,上映年份;按照年份生序排名。
select id,title,yr from movie where title LIKE'%Star Trek%' order by yr;
4、id for actor Glenn Close(演员Glenn Close的id)
What id number does the actor 'Glenn Close' have?
select id from actor where name='Glenn Close';
5、id for Casablanca(《卡萨布兰卡》电影的id)
What is the id of the film 'Casablanca'
select id from movie where title='Casablanca';
6、Cast list for Casablanca(《卡萨布兰卡》的演员表)
Obtain the cast list for 'Casablanca'.
The cast list is the names of the actors who were in the movie.
Use movieid=11768, (or whatever value you got from the previous question)
select name from actor where id in(select actorid from casting where movieid=11768);
ps:在不知道《卡萨布兰卡》电影id的前提下,还需要用select in select查询moviedid
select name from actor where id in(select actorid from casting where movieid=(select id from movie where title='Casablanca'));
7、Alien cast list(《异形》演员表)
Obtain the cast list for the film 'Alien'
select name from actor where id in(select actorid from casting where movieid=(select id from movie where title='Alien'));
8、Harrison Ford movies(Harrison Ford出演的电影名称)
List the films in which 'Harrison Ford' has appeared
select title from movie left join casting on movie.id=casting.movieid where actorid =(select id from actor where name='Harrison Ford');
9、Harrison Ford as a supporting actor(Harrison Ford是配角的电影名称)
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]
select title from movie left join casting on movie.id=casting.movieid where actorid =(select id from actor where name='Harrison Ford')and ord>1;
10、Lead actors in 1962 movies(获取1962年,主演演员的姓名,出演电影名称)
List the films together with the leading star for all 1962 films.
select movie.title,actor.name from movie left join casting on movie.id=casting.movieid left join actor on casting.actorid=actor.id where ord=1 and yr=1962;
解答思路:本次考察的是多表连接。
11、Busy years for Rock Hudson(Rock Hudson的大热年)
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.
select yr,count(title) from movie left join casting on casting.movieid=movie.id left join actor on casting.actorid=actor.id where actor.name='Rock Hudson' group by yr having count(title)>2;
解题思路:1⃣️多表连接;2⃣️having+聚合函数。
12、Lead actor in Julie Andrews movies(获取Julie Andrews出演的电影名称以及主演名称)
List the film title and the leading actor for all of the films 'Julie Andrews' played in.
Julie Andrews starred in the 1980 remake of Little Miss Marker and not the original(1934).
Title is not a unique field, create a table of IDs in your subquery
select movie.title,actor.name from movie left join casting on casting.movieid=movie.id left join actor on casting.actorid=actor.id where movie.id in (select movieid from casting where actorid in(select id from actor where name='Julie Andrews')) and casting.ord=1;
解题思路:子查询
13、Actors with 15 leading roles(按照字母排序获取至少担任过15个主演的演员名称)
Obtain a list, in alphabetical order, of actors who've had at least 15 starring roles.
select actor.name from actor join casting on actor.id=casting.actorid where casting.ord=1 group by 1 having count(actor.name)>=15 order by 1;
解题思路:聚合函数和having的运用。
14、List the films released in the year 1978 ordered by the number of actors in the cast, then by title.(获取1978年上映的电影名称,按照演员个数倒序,然后是电影名称顺序)
select movie.title,count(casting.actorid) from movie join casting on movie.id=casting.movieid where movie.yr=1978 group by 1 order by 2 desc,1;
解题思路:join连接movie和casting两个表
15、List all the people who have worked with 'Art Garfunkel'.(列出所有和Art Garfunkel合作的演员姓名)
select DISTINCT(name) from actor join casting on actor.id=casting.actorid where movieid in (select movieid from casting join actor on actor.id=casting.actorid where name='Art Garfunkel') and name!='Art Garfunkel';
解题思路:1⃣️合作的演员姓名要排除掉Art Garfunkel自己本身2⃣️获取Art Garfunkel出演的电影id,嵌套select子查询。