ムービーのデータベースに関する質問に答えるSQLクエリを記述します。
始め方
VS Codeを開きます。
ターミナルウィンドウ内をクリックすることから始めて、それからcdを実行します。
その後プロンプトは次のようになっていることがわかります。
$ターミナルウィンドウの内側をクリックし、次のように入力します。
wget https://cdn.cs50.net/2021/fall/psets/7/movies.zipその後にEnterを押すと、movies.zipというZIPがあなたのCodespaceにダウンロードされます。wgetと次のURLの間にあるスペースや、その他の文字を見落とさないように注意してください。
次に
unzip movies.zipを実行して、moviesというフォルダを作成します。
ZIPファイルは不要になったため、
rm movies.zipを実行し、プロンプトで “y “に続いてEnterで応答すると、ダウンロードしたZIPファイルが削除されます。
次に
cd moviesの後にEnterを押して、そのディレクトリに移動する（つまり、開く）。これでプロンプトは以下のようになります。
movies/ $lsを実行すると、13個の.sqlファイルと、movies.dbが表示されるはずです。
問題が発生した場合は、同じ手順をもう一度実行して、どこが間違っていたかを判断できるかどうかを確認してください。
理解を深める
提供される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.sqlで、2008年にリリースされたすべての映画のタイトルをリストするSQLクエリを記述します。- クエリを実行すると、各ムービーのタイトルを1つの列にまとめたテーブルが出力されます。
2.sqlで、Emma Stoneの誕生年を判別するSQLクエリを記述します。- クエリは、Emma Stoneの誕生年を含む単一の列と単一の行 (ヘッダーを除きます) を持つテーブルを出力します。
- データベースにはEmma Stoneという名前の人物が1人だけ存在すると仮定します。
3.sqlで、リリース日が2018年以降のすべてのムービーのタイトルをアルファベット順にリストするSQLクエリを記述します。- クエリを実行すると、各ムービーのタイトルを1つの列にまとめたテーブルが出力されます。
- 2018年に公開された映画、および今後の公開日が指定された映画を含める必要があります。
4.sqlで、IMDbレーティングが10.0の映画の数を判別するSQL問合せを記述します。- クエリは、10.0レーティングの映画の数を含む単一の列と単一の行 (ヘッダーを除きます) を持つテーブルを出力します。
5.sqlで、すべてのハリー・ポッター映画のタイトルと公開年を時系列でリストするSQL問合せを記述します。- クエリを実行すると、2つの列を持つテーブルが出力されます。1つは各映画のタイトル用で、もう1つは各映画のリリース年用です。
- すべてのハリー・ポッター映画のタイトルが 「Harry Potter」 という単語で始まると思っているかもしれません。実際、映画のタイトルが 「Harry Potter」 という単語で始まる場合、それはハリー・ポッター映画であると判別できます。
6.sqlで、2012年にリリースされたすべての映画の平均レーティングを決定するSQLクエリを記述します。- クエリは、平均レーティングを含む単一の列と単一の行 (ヘッダーを除きます) を持つテーブルを出力します。
7.sqlで、2010年にリリースされたすべての映画とそのレーティングをレーティングの降順でリストするSQLクエリを記述します。同じ評価の映画の場合は、タイトルのアルファベット順に並べ替えます。- クエリを実行すると、2つの列を持つテーブルが出力されます。1つは各ムービーのタイトル、もう1つは各ムービーのレーティングです。
- レーティングが設定されていない映画は結果に含めないでください。
8.sqlで、トイ・ストーリーに出演したすべての人の名前をリストするSQL問合せを記述します。- クエリを実行すると、各人物の名前を1つの列で表すテーブルが出力されます。
- データベースにはToy Storyというタイトルのムービーが1つだけあるとします。
9.sqlで、2004年に公開された映画に主演したすべての人の名前を、誕生年順にリストするSQLクエリを記述します。- クエリを実行すると、各ユーザーの名前を1つの列で表すテーブルが出力されます。
- 同じ生年月日の人は、任意の順序でリストできます。
- 生年月日が記入されていない場合、生年月日のある人が順番に記載されていれば問題ありません。
- ある人物が2004年に複数の映画に出演した場合、その人物は一度だけ検索結果に表示されます。
10.sqlで、9.0以上のレーティングを受けた映画を監督したすべての人の名前をリストするSQLクエリを記述します。- クエリを実行すると、各ユーザーの名前を1つの列で表すテーブルが出力されます。
11.sqlで、SQLクエリを記述し、Chadwick Bosemanが主演した映画の評価の高い順に5つのタイトルをリストします。- クエリを実行すると、各ムービーのタイトルを1つの列にまとめたテーブルが出力されます。
- データベースにはChadwick Bosemanという名前の人が1人しかいないとします。
12.sqlで、Johnny DeppとHelena Bonham Carterの両方が主演したすべての映画のタイトルをリストするSQLクエリを記述します。- クエリを実行すると、各ムービーのタイトルを1つの列にまとめたテーブルが出力されます。
- データベースにはJohnny Deppという名前の人が1人しかいないと仮定します。
- データベースには、Helena Bonham Carterという名前の人物が1人だけ存在するものとします。
- Kevin Baconが主演した映画に出演したすべての人の名前をリストするSQLクエリを記述します。
- クエリを実行すると、各ユーザーの名前を1つの列で表すテーブルが出力されます。
- データベースにKevin Baconという名前の人が複数存在する可能性があります。必ず1958年生まれのKevin Baconだけを選んでください。
- Kevin Bacon本人は結果リストに含まれません。
ウォークスルー
使い方
VS Codeでクエリをテストするには、次のコマンドを実行してデータベースをクエリします。
$ cat filename.sql | sqlite3 movies.dbここで、filename.sql はあなたのSQLクエリが記入されているファイルです。
以下を実行することもできます。
$ cat filename.sql | sqlite3 movies.db > output.txtこれは、クエリの出力をoutput.txtというテキストファイルにリダイレクトします (クエリによって返された行数をチェックするのに便利です) 。
ヒント
- 役立つSQL構文については、このSQLキーワードのリファレンス を参照してください。
- SQLのスタイルに関するポイントはsqlstyle.guideをご覧ください。特にクエリーが複雑になるにつれて、より良いスタイルが求められます。
テスト
この問題についてはcheck50を使用できますが、次の各項目についてコードを自分でテストすることをお勧めします。sqlite3 movies.dbを実行して、データベースに対して追加のクエリを実行し、結果が正しいことを確認できます。
この問題セットの配布に含まれているmovies.dbデータベースを使用している場合は、
1.sqlを実行すると、1列9,952行の表になります。2.sqlを実行すると、1列1行の表になります。3.sqlを実行すると、1列69,705行の表になります。4.sqlを実行すると、1列1行の表になります。5.sqlを実行すると、2列11行の表になります。6.sqlを実行すると、1列1行の表になります。7.sqlを実行すると、2列7,046行の表になります。8.sqlを実行すると、1列4行の表になります。9.sqlを実行すると、1列18,730行の表になります。10.sqlを実行すると、1列2,236行の表になります。11.sqlを実行すると、1列5行の表になります。12.sqlを実行すると、1列6行の表になります。13.sqlを実行すると、1列185行の表になります。
行数には、列名を表示するヘッダー行は含まれないことに注意してください。
check50を使用して以下を実行し、コードの正確性を評価します。
check50 cs50/problems/2022/x/movies提出方法
ターミナルで、以下のコマンドを実行して提出してください。
submit50 cs50/problems/2022/x/movies謝辞
データはIMDb (imdb.com)の好意によります。許可を得て使用しています。