Difference between revisions of "More JOIN operations/ja"

From SQLZOO
Jump to navigation Jump to search
 
(16 intermediate revisions by the same user not shown)
Line 1: Line 1:
{{Languages|More_JOIN_operations}}
<p>
<p>
このチュートリアルで join の理解を深める。データベースは、3つのテーブルで構成される。
このチュートリアルで join の理解を深める。データベースは、3つのテーブルで構成される。
Line 25: Line 26:
</table>
</table>


<table style='display:inline-block;margin:2ex;'><caption>casting</caption>
<table style='display:inline-block;margin:2ex;'><caption>casting 出演</caption>
<tr>
<tr>
<td><b><i>movieid</i></b></td>
<td><b><i>movieid</i></b></td>
Line 45: Line 46:
     [[More details about the database.]]
     [[More details about the database.]]
   </p>
   </p>
</div>




<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 56: Line 55:
</div></div>
</div></div>


==1962 movies==
 
==1962 映画==
<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 id, title
Line 72: Line 75:
</div>
</div>


==When was Citizen Kane released?==
 
==「市民ケーン」の上映年は?==
<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 85: Line 89:
</div>
</div>


==Star Trek movies==
 
==スタートレック映画==
<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 )。 年の順に掲載。
 
<source lang='sql' class='def'>
<source lang='sql' class='def'>
</source>
</source>
Line 98: Line 104:
</div>
</div>


==id for actor Glenn Close==
 
==グレン・クローズのid==
<div class='qu'>
<div class='qu'>
What '''id''' number does the actor 'Glenn Close' have? 
女優 'Glenn Close'  の id ナンバーは何ですか?
<source lang='sql' class='def'>
<source lang='sql' class='def'>
</source>
</source>
Line 111: Line 118:




==id for Casablanca==
==カサブランカのid==
<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 123: Line 130:
</source>
</source>
</div>
</div>
ポイント解説


[[Get to the point]]
[[Get to the point]]
==Cast list for Casablanca==
 
==カサブランカの出演者リスト==
<div class='qu'>
<div class='qu'>
Obtain the cast list for 'Casablanca'.
映画カサブランカの出演リスト(name)を出力する。'Casablanca'
<div title='what is a cast list?' class=hint>
 
The cast list is the names of the actors who were in the movie.
<div title='出演リストとは?' class=hint>
その映画に出演した役者のリスト
</div>
</div>
Use '''movieid=11768''', (or whatever value you got from the previous question)   
 
'''movieid=11768'''を使う。 (または、これまでの問題で得た値を何でも使ってよい)   
<source lang='sql' class='def'>
<source lang='sql' class='def'>
</source>
</source>
Line 145: Line 158:
</div>
</div>


==Alien cast list==
==エイリアンの出演リスト==
<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 160: Line 174:
</div>
</div>


==Harrison Ford movies==
==ハリソン=フォード映画==
<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 175: Line 189:
</div>
</div>


==Harrison Ford as a supporting actor==
==ハリソン=フォード共演==
<div class='qu'>
<div class='qu'>
List the films where 'Harrison Ford' has appeared - but not in the starring role.
'Harrison Ford'ハリソン=フォードが出演した映画で、彼が主演していない(ord <> 1) のリストを表示。
[Note: the '''ord''' field of casting gives the position of the actor.
 
If ord=1 then this actor is in the starring role]   
[Note:         ord は、映画の出演リスト順、1 が主演を意味する。]
 
<source lang='sql' class='def'>
<source lang='sql' class='def'>
</source>
</source>
Line 193: Line 208:
</div>
</div>


==Lead actors in 1962 movies==
==1962映画の主演者==
<div class='qu'>
<div class='qu'>
List the films together with the leading star for all 1962 films.     
 
1962年の全映画を、そのタイトルと主演と併記してリスト表示。
 
<source lang='sql' class='def'>
<source lang='sql' class='def'>
</source>
</source>
Line 209: Line 226:
</div>
</div>
<hr>
<hr>
Harder Questions
より難しい問題
<hr>
<hr>


==Busy years for John Travolta==
==ジョン=トラボルタが多忙の年==
<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' ジョン=トラボルタが最も忙しかった年はいつですか? その年と出演した映画の本数を表示する。
 
彼が2本より多く出演した各年について表示する。
 
<source lang='sql' class='def'>
<source lang='sql' class='def'>
SELECT yr,COUNT(title) FROM
SELECT yr,COUNT(title) FROM
Line 245: Line 265:
</source>
</source>
</div>
</div>
==Lead actor in Julie Andrews movies==
 
==ジュリー=アンドリューズ出演映画==
<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' ジュリー=アンドリューズが出演した映画について、主演した役者の名前を調べ、その全てについて タイトル と 主演 を表示する。
<div class='hint' title='Did you get "Little Miss Marker twice"?'>
 
Julie Andrews starred in the 1980 remake of Little Miss Marker and not the original(1934).
<div class='hint' title='"Little Miss Marker twice"は2回得られましたか?'>
Julie Andrews Little Miss Marker の1980 年のリメイクに主演したが、オリジナル(1934)ではしていない。
 
Title はユニークフィールドではない。サブクエリーでidのテーブルを作成する。


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 274: Line 297:
</div>
</div>


==Actors with 30 leading roles==
==主演30本の役者達==
<div class='qu'>
<div class='qu'>
Obtain a list, in alphabetical order, of actors who've had at least 30 starring roles.       
少なくとも30タイトル以上に主演した役者の名前をアルファベット順に掲載。
   
<source lang='sql' class='def'>
<source lang='sql' class='def'>
</source>
</source>
Line 292: Line 316:
<div class='qu'>
<div class='qu'>
<span class='param respectorder'></span>
<span class='param respectorder'></span>
List the films released in the year 1978 ordered by the number of actors in the cast, then by title.
1978年の映画を、出演者数が多い順に、タイトルを表示。
<source lang='sql' class='def'>
<source lang='sql' class='def'>
</source>
</source>
Line 307: Line 331:


<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>

Latest revision as of 04:32, 24 March 2020

Language:Project:Language policy English  • 日本語 • 中文

このチュートリアルで join の理解を深める。データベースは、3つのテーブルで構成される。 movie(映画) と actor(役者) と casting(出演) である。

テーブルの詳細は、以下である。

movie 映画
id title タイトル yr 年 director 監督 budget 予算 gross 収益


actor 役者
id name 名前
casting 出演
movieid actorid ord 出演掲載順序 1なら主演

Movie-er.png

※ SQL の表記で id は movie.id と actor.id の2種類あるので注意。

※ movie.id = actor.id のようには使われない(映画のid と 役者のid なので 参照キーと外部キーの関係にはならない)

※ movie.id = casting.movieid や actor.id = casting.actorid の関係がテーブル間にある。

映画データベースの詳細は以下のリンクから表示。 More details about the database.


サマリー


1962 映画

1962 年の映画のリストを表示( id と title を表示)

(ヒント) 実行するだけ。文法の確認。

SELECT id, title
 FROM movie
 WHERE yr=1962
SELECT id, title
 FROM movie
 WHERE yr=1962


「市民ケーン」の上映年は?

'Citizen Kane'の年を示す。

SELECT yr 
FROM movie 
WHERE title='Citizen Kane'


スタートレック映画

スタートレック('Star Trek')というシリーズ映画のリストを表示( id title yr )。 年の順に掲載。

SELECT id,title, yr FROM movie
 WHERE title LIKE 'Star Trek%'
 ORDER BY yr


グレン・クローズのid

女優 'Glenn Close'  の id ナンバーは何ですか?

SELECT id FROM actor
  WHERE name= 'Glenn Close'


カサブランカのid

映画 'Casablanca' カサブランカの id は何ですか?

SELECT id 
FROM movie 
WHERE title='Casablanca'


ポイント解説

Get to the point

カサブランカの出演者リスト

映画カサブランカの出演リスト(name)を出力する。'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'ハリソン=フォードが出演した映画で、彼が主演していない(ord <> 1) のリストを表示。

[Note: ord は、映画の出演リスト順、1 が主演を意味する。]

SELECT title
  FROM movie, casting, actor
 WHERE name='Harrison Ford'
    AND movieid=movie.id
    AND actorid=actor.id
  AND ord<>1

1962映画の主演者

1962年の全映画を、そのタイトルと主演と併記してリスト表示。

SELECT title, name
  FROM movie, casting, actor
 WHERE yr=1962
    AND movieid=movie.id
    AND actorid=actor.id
    AND ord=1

より難しい問題


ジョン=トラボルタが多忙の年

'John Travolta' ジョン=トラボルタが最も忙しかった年はいつですか? その年と出演した映画の本数を表示する。

彼が2本より多く出演した各年について表示する。

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' ジュリー=アンドリューズが出演した映画について、主演した役者の名前を調べ、その全てについて タイトル と 主演 を表示する。

Julie Andrews は Little Miss Marker の1980 年のリメイクに主演したが、オリジナル(1934)ではしていない。

Title はユニークフィールドではない。サブクエリーでidのテーブルを作成する。

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本の役者達

少なくとも30タイトル以上に主演した役者の名前をアルファベット順に掲載。

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,1 ASC

'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.