Movies

Movies

ムービーのデータベースに関する質問に答えるSQLクエリを記述します。

始め方

ここでは、この問題をCS50 IDEにダウンロードする方法を説明します。CS50 IDEにログインし、ターミナルウィンドウで次の各コマンドを実行します。

  • cd ~ (または引数なしの単純なcd) を実行して、ホームディレクトリにいることを確認します。
  • mkdir pset7を実行して、pset7というディレクトリを作成 (新規作成) します。
  • cd pset7を実行して、そのディレクトリに移動 (ディレクトリを開く) します。
  • wget https://cdn.cs50.net/2020/fall/psets/7/movies/movies.zipを実行して、この問題のディストリビューションを含む (圧縮された) ZIPファイルをダウンロードします。
  • そのファイルを解凍するには、unzip movies.zipを実行します。
  • そのZIPファイルを削除するには、rm movies.zipの後にyesまたはyを実行します。
  • lsを実行します。ZIPファイルの中にmoviesというディレクトリがあるはずです。
  • cd moviesを実行して、そのディレクトリに移動します。
  • lsを実行します。movies.dbファイルと空の.sqlファイルも表示されます。

理解を深める

提供されるmovies.dbというファイルは、IMDb からのムービー、監督と主演者、およびレーティングに関するデータを格納するSQLiteデータベースです。ターミナルウィンドウでsqlite3 movies.dbを実行し、データベースに対するクエリの実行を開始します。

まず、sqlite3がクエリの入力を求めるプロンプトを表示したら、.schemaと入力してEnterキーを押します。これにより、データベース内の各テーブルの生成に使用されたCREATETABLEステートメントが出力されます。このステートメントを調べることで、各テーブルに存在するカラムを識別できます。

movies テーブルには、各ムービーを一意に識別するid列と、ムービーのタイトルtitleおよびムービーが公開された年yearの列があります。people テーブルにもid列があり、各個人の名前nameと生年月日birthの列があります。

一方、映画のレーティングはratingsテーブルに保存されます。テーブルの最初のカラムはmovie_idであり、moviesテーブルのidを参照する外部キーです。行の残りの部分には、各映画のratingと、その映画がIMDbで獲得した投票数votesにのデータが含まれます。

最後に、スターstars と監督directors のテーブルは、出演した映画や監督に人々をマッチさせます (主要なスターと監督のみが含まれています) 。各テーブルには、特定の映画と人物をそれぞれ参照するmovie_idとperson_idの2つのカラムしかありません。

今回の課題は、これらのテーブルの1つ以上からデータを選択して、さまざまな質問に答えるSQLクエリを作成することです。

仕様

次の各問題について、各問題で指定された結果を出力する単一のSQL問合せを作成する必要があります。レスポンスは単一のSQLクエリの形式である必要がありますが、クエリ内に他のクエリをネストすることもできます。特定の映画や人物のIDについて何も仮定すべきではありません。特定の映画や人物のIDが異なっていたとしても、クエリは正確でなければなりません。最後に、各クエリは質問に答えるために必要なデータのみを返す必要があります。たとえば、問題が映画の名前の出力だけを求める場合、クエリは各ムービーのリリース年は出力しません。

クエリの結果をIMDb自体と照合することはできますが、データがダウンロードされてからさらに多くの票が投じられている可能性があるため、Webサイトの評価はmovies.dbの評価とは異なる場合があることに注意してください。

  1. 1.sqlで、2008年にリリースされたすべての映画のタイトルをリストするSQLクエリを記述します。
    1. クエリを実行すると、各ムービーのタイトルを1つの列にまとめたテーブルが出力されます。
  2. 2.sqlで、Emma Stoneの誕生年を判別するSQLクエリを記述します。
    1. クエリは、Emma Stoneの誕生年を含む単一の列と単一の行 (ヘッダーを含まない) を持つテーブルを出力します。
    1. データベースにはEmma Stoneという名前の人物が1人だけ存在すると仮定します。
  3. 3.sqlで、リリース日が2018年以降のすべてのムービーのタイトルをアルファベット順にリストするSQLクエリを記述します。
    1. クエリを実行すると、各ムービーのタイトルを1つの列にまとめたテーブルが出力されます。
    1. 2018年に公開された映画、および今後の公開日が指定された映画を含める必要があります。
  4. 4.sqlで、IMDbレーティングが10.0の映画の数を判別するSQL問合せを記述します。
    1. クエリは、10.0レーティングの映画の数を含む単一の列と単一の行 (ヘッダーを含まない) を持つテーブルを出力します。
  5. 5.sqlで、すべてのハリー・ポッター映画のタイトルと公開年を時系列でリストするSQL問合せを記述します。
    1. クエリを実行すると、2つの列を持つテーブルが出力されます。1つは各映画のタイトル用で、もう1つは各映画のリリース年用です。
    1. すべてのハリー・ポッター映画のタイトルが 「Harry Potter」 という単語で始まると思っているかもしれません。実際、映画のタイトルが 「Harry Potter」 という単語で始まる場合、それはハリー・ポッター映画であると判別できます。
  6. 6.sqlで、2012年にリリースされたすべての映画の平均レーティングを決定するSQLクエリを記述します。
    1. クエリは、平均レーティングを含む単一の列と単一の行 (ヘッダーを含まない) を持つテーブルを出力します。
  7. 7.sqlで、2010年にリリースされたすべての映画とそのレーティングをレーティングの降順でリストするSQLクエリを記述します。同じ評価の映画の場合は、タイトルのアルファベット順に並べ替えます。
    1. クエリを実行すると、2つの列を持つテーブルが出力されます。1つは各ムービーのタイトル、もう1つは各ムービーのレーティングです。
    1. レーティングが設定されていない映画は結果に含めないでください。
  8. 8.sqlで、トイ・ストーリーに出演したすべての人の名前をリストするSQL問合せを記述します。
    1. クエリを実行すると、各人物の名前を1つの列で表すテーブルが出力されます。
    1. データベースにはToy Storyというタイトルのムービーが1つだけあるとします。
  9. 9.sqlで、2004年に公開された映画に主演したすべての人の名前を、誕生年順にリストするSQLクエリを記述します。
    1. クエリを実行すると、各ユーザーの名前を1つの列で表すテーブルが出力されます。
    1. 同じ生年月日の人は、任意の順序でリストできます。
    1. 生年月日が記入されていない場合、生年月日のある人が順番に記載されていれば問題ありません。
    1. ある人物が2004年に複数の映画に出演した場合、その人物は一度だけ検索結果に表示されます。
  10. 10.sqlで、9.0以上のレーティングを受けた映画を監督したすべての人の名前をリストするSQLクエリを記述します。
    1. クエリを実行すると、各ユーザーの名前を1つの列で表すテーブルが出力されます。
  11. 11.sqlで、SQLクエリを記述し、Chadwick Bosemanが主演した映画の評価の高い順に5つのタイトルをリストします。
    1. クエリを実行すると、各ムービーのタイトルを1つの列にまとめたテーブルが出力されます。
    1. データベースにはChadwick Bosemanという名前の人が1人しかいないとします。
  12. 12.sqlで、Johnny DeppとHelena Bonham Carterの両方が主演したすべての映画のタイトルをリストするSQLクエリを記述します。
    1. クエリを実行すると、各ムービーのタイトルを1つの列にまとめたテーブルが出力されます。
    1. データベースにはJohnny Deppという名前の人が1人しかいないと仮定します。
    1. データベースには、Helena Bonham Carterという名前の人物が1人だけ存在するものとします。
  13. Kevin Baconが主演した映画に出演したすべての人の名前をリストするSQLクエリを記述します。
    1. クエリを実行すると、各ユーザーの名前を1つの列で表すテーブルが出力されます。
    1. データベースにKevin Baconという名前の人が複数存在する可能性があります。必ず1958年生まれのKevin Baconだけを選んでください。
    1. Kevin Bacon本人は結果リストに含まれません。

ウォークスルー

使い方

CS50 IDEでクエリをテストするには、次のコマンドを実行してデータベースをクエリします。

$ cat filename.sql | sqlite3 movies.db

ここで、filename.sql はあなたのSQLクエリが記入されているファイルです。

以下を実行することもできます。

$ cat filename.sql | sqlite3 movies.db > output.txt

これは、クエリの出力をoutput.txtというテキストファイルにリダイレクトします (クエリによって返された行数をチェックするのに便利です) 。

ヒント

テスト

この問題についてはcheck50を使用できますが、次の各項目についてコードを自分でテストすることをお勧めします。sqlite3 movies.dbを実行して、データベースに対して追加のクエリを実行し、結果が正しいことを確認できます。

この問題セットの配布に含まれているmovies.dbデータベースを使用している場合は、

  • 1.sqlを実行すると、1列9,545行の表になります。
  • 2.sqlを実行すると、1列1行の表になります。
  • 3.sqlを実行すると、1列50,863行の表になります。
  • 4.sqlを実行すると、1列1行の表になります。
  • 5.sqlを実行すると、2列10行の表になります。
  • 6.sqlを実行すると、1列1行の表になります。
  • 7.sqlを実行すると、2列6,864行の表になります。
  • 8.sqlを実行すると、1列4行の表になります。
  • 9.sqlを実行すると、1列18,237行の表になります。
  • 10.sqlを実行すると、1列1,887行の表になります。
  • 11.sqlを実行すると、1列5行の表になります。
  • 12.sqlを実行すると、1列6行の表になります。
  • 13.sqlを実行すると、1列176行の表になります。

行数には、列名を表示するヘッダー行は含まれないことに注意してください。

check50を使用して以下を実行し、コードの正確性を評価します。

check50 cs50/problems/2021/x/movies

提出方法

次のコマンドを実行し、GitHubのユーザー名とパスワードを入力してログインします。セキュリティのため、パスワードには実際の文字ではなくアスタリスク (*) が表示されます。

submit50 cs50/problems/2021/x/movies

謝辞

データはIMDb (imdb.com)の好意によります。許可を得て使用しています。