More JOIN operations/zh
Language: | English • 日本語 • 中文 |
---|
電影數據庫
此教程練習表格合拼。數據庫有三個表格
movie電影(id編號, title電影名稱, yr首影年份, director導演, budget製作費, gross票房收入)
actor演員(id編號, name姓名)
casting角色(movieid電影編號, actorid演員編號, ord角色次序)
角色次序代表第1主角是1, 第2主角是2...如此類推.
More details about the database.
現在開始.
熱身
列出1962年首影的電影, [顯示 id, title]
SELECT title
FROM movie
WHERE yr=1980
SELECT id, title
FROM movie
WHERE yr=1962
電影大國民 'Citizen Kane' 的首影年份。
SELECT yr
FROM movie
WHERE title='Citizen Kane'
列出全部Star Trek星空奇遇記系列的電影,包括id, title 和 yr(此系統電影都以Star Trek為電影名稱的開首)。按年份順序排列。
SELECT id,title, yr FROM movie
WHERE title LIKE 'Star Trek%'
ORDER BY yr
Looking at the id field.
id是 11768, 11955, 21191 的電影是什麼名稱?
SELECT title
FROM movie
WHERE id IN (11768, 11955, 21191)
女演員'Glenn Close'的編號 id是什麼?
SELECT id FROM actor
WHERE name= 'Glenn Close'
電影北非諜影'Casablanca' 的編號 id是什麼?
SELECT id
FROM movie
WHERE title='Casablanca'
合拼語法
列出電影北非諜影 'Casablanca'的演員名單。
演員名單,即是電影中各演員的真實姓名清單。
使用 movieid=11768, 這是你上一題得到的結果。
SELECT name
FROM casting, actor
WHERE movieid=(SELECT id
FROM movie
WHERE title='Casablanca')
AND actorid=actor.id
顯示電影異型'Alien' 的演員清單。
SELECT name
FROM movie, casting, actor
WHERE title='Alien'
AND movieid=movie.id
AND actorid=actor.id
列出演員夏里遜福 'Harrison Ford' 曾演出的電影。
SELECT title
FROM movie, casting, actor
WHERE name='Harrison Ford'
AND movieid=movie.id
AND actorid=actor.id
列出演員夏里遜福 'Harrison Ford' 曾演出的電影,但他不是第1主角。
SELECT title
FROM movie, casting, actor
WHERE name='Harrison Ford'
AND movieid=movie.id
AND actorid=actor.id
AND ord<>1
列出1962年首影的電影及它的第1主角。
SELECT title, name
FROM movie, casting, actor
WHERE yr=1962
AND movieid=movie.id
AND actorid=actor.id
AND ord=1
困難的題目
尊·特拉華達'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
)
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
)
列出演員茱莉·安德絲'Julie Andrews'曾參與的電影名稱及其第1主角。
她於1980再參與此電影Little Miss Marker. 原作於1934年,她也有參與。 電影名稱不是獨一的。在子查詢中使用電影編號。
SELECT movieid FROM casting
WHERE actorid IN (
SELECT id FROM actor
WHERE name='Julie Andrews')
SELECT title, name
FROM movie, casting, actor
WHERE movieid=movie.id
AND actorid=actor.id
AND ord=1
AND movieid IN
(SELECT movieid FROM casting, actor
WHERE actorid=actor.id
AND name='Julie Andrews')
列出按字母順序,列出哪一演員曾作30次第1主角。
SELECT name
FROM casting JOIN actor
ON actorid = actor.id
WHERE ord=1
GROUP BY name
HAVING COUNT(movieid)>=30
列出1978年首影的電影名稱及角色數目,按此數目由多至少排列。
SELECT title, COUNT(actorid)
FROM casting,movie
WHERE yr=1978
AND movieid=movie.id
GROUP BY title
ORDER BY 2 DESC
列出曾與演員亞特·葛芬柯'Art Garfunkel'合作過的演員姓名。
SELECT DISTINCT d.name
FROM actor d JOIN casting a ON (a.actorid=d.id)
JOIN casting b on (a.movieid=b.movieid)
JOIN actor c on (b.actorid=c.id
and c.name='Art Garfunkel')
WHERE d.id!=c.id