More JOIN operations/zh

more join.png

1
2
3
4
1.列出1962年首影的電影，[顯示 id, title]
SELECT id,title
 FROM movie
 WHERE yr=1962
1
2
3
2.電影大國民'Citizen Kane'的首影年份。
select yr from movie
where title = 'Citizen Kane'
1
2
3
4
3.列出全部Star Trek星空奇遇記系列的電影，包括id, title和yr(此系統電影都以Star Trek為電影名稱的開首)。按年份順序排列。
select id,title,yr from movie
where title like 'Star Trek%'
order by yr
1
2
3
4.id是11768, 11955, 21191的電影是什麼名稱?
select title from movie
where id in (11768,11955,21191)
1
2
3
5.女演員'Glenn Close'的編號 id是什麼?
select id from actor
where name = 'Glenn Close'
1
2
3
6.電影北非諜影'Casablanca'的編號id是什麼?
select id from movie
where title = 'Casablanca'
1
2
3
4
7.列出電影北非諜影'Casablanca'的演員名單。
select name from actor join casting on (actor.id=actorid)
                       join movie on (movie.id=movieid)
where title = 'Casablanca'
1
2
3
4
8.顯示電影異型'Alien'的演員清單。
select name from actor join casting on (actor.id=actorid)
                       join movie on (movie.id=movieid)
where title = 'Alien'
1
2
3
4
9.列出演員夏里遜福'Harrison Ford'曾演出的電影。
select title from actor join casting on (actor.id=actorid)
                       join movie on (movie.id=movieid)
where name = 'Harrison Ford'
1
2
3
4
10.列出演員夏里遜福'Harrison Ford'曾演出的電影，但他不是第1主角。
select title from actor join casting on (actor.id=actorid)
                       join movie on (movie.id=movieid)
where name = 'Harrison Ford' and ord != 1
1
2
3
4
11.列出1962年首影的電影及它的第1主角。
select title,name from actor join casting on (actor.id=actorid)
                       join movie on (movie.id=movieid)
where yr = 1962 and ord = 1

More Difficult

1
2
3
4
5
6
7
8
9
10
11
12
13
12.尊·特拉華達'John Travolta'最忙是哪一年? 顯示年份和該年的電影數目。
SELECT yr,COUNT(title) FROM
  movie JOIN casting ON movie.id=movieid
         JOIN actor   ON actorid=actor.id
where name='John Travolta'
GROUP BY yr
HAVING COUNT(title)=(SELECT MAX(c) FROM
(SELECT yr,COUNT(title) AS c FROM
   movie JOIN casting ON movie.id=movieid
         JOIN actor   ON actorid=actor.id
 where name='John Travolta'
 GROUP BY yr) AS t
)
1
2
3
4
5
6
7
8
9
10
11
13.列出演員茱莉·安德絲'Julie Andrews'曾參與的電影名稱及其第1主角。
select title,name from 
 actor join casting on (actor.id=actorid)
       join movie on (movie.id=movieid)
where ord = 1 and movieid in 
(select movieid from 
 actor join casting on (actor.id=actorid)
       join movie on (movie.id=movieid)
 where name = 'Julie Andrews')

应该用movieid而不用title，因为title可能会重复
1
2
3
4
5
6
14.列出按字母順序，列出哪一演員曾作30次第1主角。
select name from actor join casting on (actor.id=actorid)
where ord = 1
group by actorid,name
having count(movieid) >= 30
order by name
1
2
3
4
5
6
7
15.列出1978年首影的電影名稱及角色數目，按此數目由多至少排列。
select title,count(actorid) from casting join movie on (movie.id=movieid)
where yr = 1978
group by movieid,title
order by count(actorid) desc

英文版最后的排序条件还会加上title，中文版一直通不过...
1
2
3
4
5
6
7
16.列出曾與演員亞特·葛芬柯'Art Garfunkel'合作過的演員姓名。
select name from
 actor join casting on (actor.id=actorid)
where name != 'Art Garfunkel' and movieid in
(select movieid from
 actor join casting on (actor.id=actorid)
 where name = 'Art Garfunkel')

Quiz

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

1
2
3
SELECT name
  FROM actor INNER JOIN movie ON actor.id = director
 WHERE gross < budget

2.Select the correct example of JOINing three tables
Answer：

1
2
3
SELECT *
  FROM actor JOIN casting ON actor.id = actorid
  JOIN movie ON movie.id = movieid

3.Select the statement that shows the list of actors called’John’by order of number of movies in which they acted
Answer：

1
2
3
4
SELECT name, COUNT(movieid)
  FROM casting JOIN actor ON actorid=actor.id
 WHERE name LIKE 'John %'
 GROUP BY name ORDER BY 2 DESC

4.Select the result that would be obtained from this code:

1
2
3
4
SELECT title 
  FROM movie JOIN casting ON (movieid=movie.id)
             JOIN actor   ON (actorid=actor.id)
 WHERE name='Paul Hogan' AND ord = 1

Answer：
444.png
5.Select the statement that lists all the actors that starred in movies directed by Ridley Scott who has id 351
Answer：

1
2
3
4
SELECT name
  FROM movie JOIN casting ON movie.id = movieid
  JOIN actor ON actor.id = actorid
WHERE ord = 1 AND director = 351

6.There are two sensible ways to connect movie and actor. They are:
Answer：

1
2
link the director column in movies with the primary key in actor
connect the primary keys of movie and actor via the casting table

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

1
2
3
SELECT title, yr 
  FROM movie, casting, actor 
 WHERE name='Robert De Niro' AND movieid=movie.id AND actorid=actor.id AND ord = 3

Answer：
777.png

Summary