Difference between revisions of "More JOIN operations/zh"

From SQLZOO
Jump to: navigation, search
(Created page with "<h1>電影數據庫</h1> <p>此教程練習表格合拼。數據庫有三個表格 <code>movie電影(id編號, title電影名稱, yr年份, director導演, budget製作費,...")
 
Line 1: Line 1:
 
<h1>電影數據庫</h1>
 
<h1>電影數據庫</h1>
 
  <p>此教程練習表格合拼。數據庫有三個表格
 
  <p>此教程練習表格合拼。數據庫有三個表格
   <code>movie電影(id編號, title電影名稱, yr年份, director導演, budget製作費, gross票房收入)</code>
+
   <code>movie電影(id編號, title電影名稱, yr首影年份, director導演, budget製作費, gross票房收入)</code><br/>
  ,
+
   <code>actor演員(id編號, name姓名)</code><br/>
   <code>actor演員(id編號, name姓名)</code>
+
   <code>casting角色(movieid電影編號, actorid演員編號, ord角色次序)</code><br/>
  and
+
   角色次序代表第1主角是1, 第2主角是2...如此類推.
   <code>casting角色(movieid電影編號, actorid演員編號, ord角色次序)</code>
 
   .
 
 
    
 
    
 
</p>
 
</p>
<div class='sc'></div>
 
<div class = 'ref_section'>
 
<table class = 'db_ref'>
 
<tr><th>'''movie'''</th><th>'''actor'''</th><th>'''casting'''</th></tr>
 
<tr><td>id</td><td>id</td><td>movieid</td></tr>
 
<tr><td>title</td><td>name</td><td>actorid</td></tr>
 
<tr><td>yr</td><td></td><td>ord</td></tr>
 
<tr><td>director</td><td></td><td></td></tr>
 
<tr><td>budget</td><td></td><td></td></tr>
 
<tr><td>gross</td><td></td><td></td></tr>
 
<tr><td></td><td></td><td></td></tr>
 
</table>
 
</div>
 
 
   <p>
 
   <p>
 
     [[More details about the database.]]
 
     [[More details about the database.]]
Line 28: Line 13:
  
 
<div class="progress_panel"><div>
 
<div class="progress_panel"><div>
   <div class="summary">Summary</div>
+
   <div class="summary"></div>
 
   <div class="progressbarbg">
 
   <div class="progressbarbg">
 
     <div class="progressbar"></div>
 
     <div class="progressbar"></div>
Line 34: Line 19:
 
</div></div>
 
</div></div>
  
<h2>Let's go to work.</h2>
+
<h2>現在開始.</h2>
<p>Limbering up</p>
+
<p>熱身</p>
 
<div class='qu'>
 
<div class='qu'>
List the films where the '''yr''' is 1962 [Show '''id''', '''title''']   
+
列出1962年首影的電影, [顯示 '''id''', '''title''']   
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
SELECT id, title
+
SELECT title
 
  FROM movie
 
  FROM movie
  WHERE yr=1962
+
  WHERE yr=1980
 
</source>
 
</source>
  
Line 52: Line 37:
  
 
<div class='qu'>
 
<div class='qu'>
Give year of 'Citizen Kane'.   
+
電影大國民 'Citizen Kane' 的首影年份。
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>
Line 59: Line 44:
 
SELECT yr  
 
SELECT yr  
 
FROM movie  
 
FROM movie  
WHERE title='Citizen Kane'
+
WHERE title='Citizen Kane'  
 
</source>
 
</source>
 
</div>
 
</div>
  
 
<div class='qu'>
 
<div class='qu'>
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.
+
列出全部Star Trek星空奇遇記系列的電影,包括'''id''', '''title''' '''yr'''(此系統電影都以Star Trek為電影名稱的開首)。按年份順序排列。
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>
Line 77: Line 62:
 
<h2>Looking at the '''id''' field.</h2>
 
<h2>Looking at the '''id''' field.</h2>
 
<div class='qu'>
 
<div class='qu'>
What are the titles of the films with '''id''' 11768, 11955, 21191  
+
'''id'''11768, 11955, 21191 的電影是什麼名稱?
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>
Line 89: Line 74:
  
 
<div class='qu'>
 
<div class='qu'>
What '''id''' number does the actress 'Glenn Close' have?  
+
女演員'Glenn Close'的編號 '''id'''是什麼?
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>
Line 101: Line 86:
  
 
<div class='qu'>
 
<div class='qu'>
What is the '''id''' of the film 'Casablanca'  
+
電影北非諜影'Casablanca' 的編號 '''id'''是什麼?
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>
Line 112: Line 97:
 
</div>
 
</div>
  
<h2>[[Get to the point]]</h2>
+
<h2>[[Get to the point|合拼語法]]</h2>
 
<div class='qu'>
 
<div class='qu'>
Obtain the cast list for 'Casablanca'.
+
列出電影北非諜影 'Casablanca'的演員名單。
<div title='what is a cast list?' class=hint>
+
<div title='什麼是演員名單?' class=hint>
The cast list is the names of the actors who were in the movie.
+
演員名單,即是電影中各演員的真實姓名清單。
 
</div>
 
</div>
Use '''movieid=11768''', this is the value that you obtained in the previous question. 
+
使用 '''movieid=11768''', 這是你上一題得到的結果。
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>
Line 133: Line 118:
  
 
<div class='qu'>
 
<div class='qu'>
Obtain the cast list for the film 'Alien'
+
顯示電影異型'Alien' 的演員清單。
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>
Line 147: Line 132:
  
 
<div class='qu'>
 
<div class='qu'>
List the films in which 'Harrison Ford' has appeared   
+
列出演員夏里遜福 'Harrison Ford' 曾演出的電影。
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>
Line 161: Line 146:
  
 
<div class='qu'>
 
<div class='qu'>
List the films where 'Harrison Ford' has appeared - but not in the starring role.
+
列出演員夏里遜福 'Harrison Ford' 曾演出的電影,但他不是第1主角。
[Note: the '''ord''' field of casting gives the position of the actor.
 
If ord=1 then this actor is in the starring role]   
 
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>
Line 178: Line 161:
  
 
<div class='qu'>
 
<div class='qu'>
List the films together with the leading star for all 1962 films.     
+
列出1962年首影的電影及它的第1主角。
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>
Line 192: Line 175:
 
</div>
 
</div>
  
<h2>Harder Questions</h2>
+
<h2>困難的題目</h2>
 
<div class='qu'>
 
<div class='qu'>
Which were the busiest years for 'John Travolta', show the year and the number of movies he made each year for any year in which he made more than 2 movies.     
+
尊·特拉華達'John Travolta'最忙是哪一年?
 +
顯示年份和該年的電影數目。
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
SELECT yr,COUNT(title) FROM
 
SELECT yr,COUNT(title) FROM
Line 227: Line 211:
  
 
<div class='qu'>
 
<div class='qu'>
List the film title and the leading actor for all of the films 'Julie Andrews' played in.
+
列出演員茱莉·安德絲'Julie Andrews'曾參與的電影名稱及其第1主角。
<div class='hint' title='Did you get "Little Miss Marker twice"?'>
+
<div class='hint' title='是否列了電影 "Little Miss Marker"兩次?'>
Julie Andrews starred in the 1980 remake of Little Miss Marker and not the original(1934).
+
她於1980再參與此電影Little Miss Marker. 原作於1934年,她也有參與。
 
+
電影名稱不是獨一的。在子查詢中使用電影編號。
Title is not a unique field, create a table of IDs in your subquery
 
 
</div>
 
</div>
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
Line 254: Line 237:
  
 
<div class='qu'>
 
<div class='qu'>
Obtain a list, in alphabetical order, of actors who've had at least 30 starring roles.       
+
列出按字母順序,列出哪一演員曾作30次第1主角。
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>
Line 269: Line 252:
  
 
<div class='qu'>
 
<div class='qu'>
List the films released in the year 1978 ordered by the number of actors in the cast.     
+
列出1978年首影的電影名稱及角色數目,按此數目由多至少排列。
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>
Line 284: Line 267:
  
 
<div class='qu'>
 
<div class='qu'>
List all the people who have worked with 'Art Garfunkel'.       
+
列出曾與演員亞特·葛芬柯'Art Garfunkel'合作過的演員姓名。
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>

Revision as of 00:48, 27 March 2016

電影數據庫

此教程練習表格合拼。數據庫有三個表格 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, titleyr(此系統電影都以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
Clear your results

That is definitely enough. Students should, under no circumstances look at the next tutorial, concerning outer joins.