ハーバード大学 コンピュータ・サイエンス講座 CS50 2020 week7 SQL Pset Movies
CS50 2020 week7 Pset Movies
まさかの13問！
でも題材が好きな映画なので、楽しんで取り組めた。
なんか、最後のケビン・ベーコンだけ結果が答えより1行分多く出てモヤモヤ…
まあ、いいか…
理解を深める
提供されるmovies.dbというファイルは、IMDb からのムービー、監督と主演者、およびレーティングに関するデータを格納するSQLiteデータベースです。ターミナルウィンドウでsqlite3 movies.dbを実行し、データベースに対するクエリの実行を開始します。
まず、sqlite3がクエリの入力を求めるプロンプトを表示したら、.schemaと入力してEnterキーを押します。これにより、データベース内の各テーブルの生成に使用されたCREATETABLEステートメントが出力されます。このステートメントを調べることで、各テーブルに存在するカラムを識別できます。
sqlite> .schema
CREATE TABLE movies (
id INTEGER,
title TEXT NOT NULL,
year NUMERIC,
PRIMARY KEY(id)
);
CREATE TABLE stars (
movie_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
FOREIGN KEY(movie_id) REFERENCES movies(id),
FOREIGN KEY(person_id) REFERENCES people(id)
);
CREATE TABLE directors (
movie_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
FOREIGN KEY(movie_id) REFERENCES movies(id),
FOREIGN KEY(person_id) REFERENCES people(id)
);
CREATE TABLE ratings (
movie_id INTEGER NOT NULL,
rating REAL NOT NULL,
votes INTEGER NOT NULL,
FOREIGN KEY(movie_id) REFERENCES movies(id)
);
CREATE TABLE people (
id INTEGER,
name TEXT NOT NULL,
birth NUMERIC,
PRIMARY KEY(id)
);
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つの列にまとめたテーブルが出力されます。
SELECT title FROM movies WHERE year = 2008;
SELECT COUNT(title) FROM movies WHERE year = 2008;
▼結果
9545行 OK
2.sqlで、Emma Stoneの誕生年を判別するSQLクエリを記述します。
クエリは、Emma Stoneの誕生年を含む単一の列と単一の行 (ヘッダーを含まない) を持つテーブルを出力します。
データベースにはEmma Stoneという名前の人物が1人だけ存在すると仮定します。
SELECT birth FROM people WHERE name = 'Emma Stone';
3.sqlで、リリース日が2018年以降のすべてのムービーのタイトルをアルファベット順にリストするSQLクエリを記述します。
クエリを実行すると、各ムービーのタイトルを1つの列にまとめたテーブルが出力されます。
2018年に公開された映画、および今後の公開日が指定された映画を含める必要があります。
SELECT title FROM movies WHERE year > 2017 ORDER BY title;
SELECT COUNT(title) FROM movies WHERE year > 2017 ORDER BY title;
▼結果
50863行 OK
4.sqlで、IMDbレーティングが10.0の映画の数を判別するSQL問合せを記述します。
クエリは、10.0レーティングの映画の数を含む単一の列と単一の行 (ヘッダーを含まない) を持つテーブルを出力します。
別に「ratingが10.0の映画のタイトルを表示しろ」とは言ってないので、ratingが10.0の映画の数だけをカウントすればOK。
SELECT COUNT(movie_id) FROM ratings WHERE rating = 10.0;
5.sqlで、すべてのハリー・ポッター映画のタイトルと公開年を時系列でリストするSQL問合せを記述します。
クエリを実行すると、2つの列を持つテーブルが出力されます。1つは各映画のタイトル用で、もう1つは各映画のリリース年用です。
すべてのハリー・ポッター映画のタイトルが 「Harry Potter」 という単語で始まると思っているかもしれません。実際、映画のタイトルが 「Harry Potter」 という単語で始まる場合、それはハリー・ポッター映画であると判別できます。
SELECT title,year FROM movies WHERE title LIKE 'Harry Potter%' ORDER BY year;
SELECT COUNT(title) FROM movies WHERE title LIKE 'Harry Potter%' ORDER BY year;
▼結果
10行 OK
6.sqlで、2012年にリリースされたすべての映画の平均レーティングを決定するSQLクエリを記述します。
クエリは、平均レーティングを含む単一の列と単一の行 (ヘッダーを含まない) を持つテーブルを出力します。
主キー、外部キーを使ったネストっぽい→INを使う
①2012年にリリースされたすべての映画のid
SELECT id FROM movies WHERE year = 2012;
②平均レーティング
SELECT AVG(rating) FROM ratings WHERE movie_id IN (SELECT id FROM movies WHERE year = 2012);
▼数は合う？
SELECT COUNT(rating) FROM ratings WHERE movie_id IN (SELECT id FROM movies WHERE year = 2012);
7785
SELECT COUNT(id) FROM movies WHERE year = 2012;
13591
↑合わない→ratingが登録されていない映画もあるってコト？！
7.sqlで、2010年にリリースされたすべての映画とそのレーティングをレーティングの降順でリストするSQLクエリを記述します。同じ評価の映画の場合は、タイトルのアルファベット順に並べ替えます。
クエリを実行すると、2つの列を持つテーブルが出力されます。1つは各ムービーのタイトル、もう1つは各ムービーのレーティングです。
レーティングが設定されていない映画は結果に含めないでください。
異なるテーブルの列を結合する必要がある→JOINを使う
SELECT <columns> FROM <table1> JOIN <table2> ON <predicate>
SELECT movies.title,ratings.rating FROM movies JOIN ratings ON movies.id = ratings.movie_id WHERE movies.year =2010 ORDER BY rating DESC;
SELECT COUNT(movies.title) FROM movies JOIN ratings ON movies.id = ratings.movie_id WHERE movies.year =2010 ORDER BY rating DESC
▼結果
6864行 OK
8.sqlで、トイ・ストーリーに出演したすべての人の名前をリストするSQL問合せを記述します。
クエリを実行すると、各人物の名前を1つの列で表すテーブルが出力されます。
データベースにはToy Storyというタイトルのムービーが1つだけあるとします。
主キー、外部キーを使ったネスト→INを使う
出演者＝stars
movie_id→moviesテーブル
person_id→peopleテーブル
①トイストーリーのid
SELECT id FROM movies WHERE title = 'Toy Story';
②トイストーリーの出演者のperson_id
SELECT person_id FROM stars WHERE movie_id IN (SELECT id FROM movies WHERE title = 'Toy Story');
③出演者の名前
SELECT name FROM people WHERE id IN (SELECT person_id FROM stars WHERE movie_id IN (SELECT id FROM movies WHERE title = 'Toy Story'));
SELECT COUNT(name) FROM people WHERE id IN (SELECT person_id FROM stars WHERE movie_id IN (SELECT id FROM movies WHERE title = 'Toy Story'));
▼結果
4行 OK
9.sqlで、2004年に公開された映画に主演したすべての人の名前を、誕生年順にリストするSQLクエリを記述します。
クエリを実行すると、各ユーザーの名前を1つの列で表すテーブルが出力されます。
同じ生年月日の人は、任意の順序でリストできます。
生年月日が記入されていない場合、生年月日のある人が順番に記載されていれば問題ありません。
ある人物が2004年に複数の映画に出演した場合、その人物は一度だけ検索結果に表示されます。
①2004年に公開された映画のid→moviesテーブル
SELECT id FROM movies WHERE year = 2004;
②出演者のid→starsテーブル
SELECT person_id FROM stars WHERE movie_id IN (SELECT id FROM movies WHERE year = 2004);
③出演者の名前を誕生年順に
SELECT name,birth FROM people WHERE id IN (SELECT person_id FROM stars WHERE movie_id IN (SELECT id FROM movies WHERE year = 2004)) ORDER BY birth;
SELECT COUNT(name) FROM people WHERE id IN (SELECT person_id FROM stars WHERE movie_id IN (SELECT id FROM movies WHERE year = 2004)) ORDER BY birth;
▼結果
18237行 OK
10.sqlで、9.0以上のレーティングを受けた映画を監督したすべての人の名前をリストするSQLクエリを記述します。
クエリを実行すると、各ユーザーの名前を1つの列で表すテーブルが出力されます。
①9.0以上のレーティングを受けた映画のid
SELECT id FROM movies WHERE id IN (SELECT movie_id FROM ratings WHERE rating > 8.9);
②監督の名前
SELECT name FROM people WHERE id IN (SELECT person_id FROM directors WHERE movie_id IN (SELECT id FROM movies WHERE id IN (SELECT movie_id FROM ratings WHERE rating > 9.0)));
SELECT COUNT(id) FROM movies WHERE id IN (SELECT movie_id FROM ratings WHERE rating > 8.9);
SELECT COUNT(name) FROM people WHERE id IN (SELECT person_id FROM directors WHERE movie_id IN (SELECT id FROM movies WHERE id IN (SELECT movie_id FROM ratings WHERE rating > 8.9)));
▼結果
1887行 OK
11.sqlで、SQLクエリを記述し、Chadwick Bosemanが主演した映画の評価の高い順に5つのタイトルをリストします。
クエリを実行すると、各ムービーのタイトルを1つの列にまとめたテーブルが出力されます。
データベースにはChadwick Bosemanという名前の人が1人しかいないとします。
①Chadwick Bosemanのid
SELECT id FROM people WHERE name = 'Chadwick Boseman';
①Chadwick Bosemanが主演した映画のmovie_id(starsテーブル)
SELECT movie_id FROM stars WHERE person_id IN (SELECT id FROM people WHERE name = 'Chadwick Boseman');
③Chadwick Bosemanが主演した映画の評価 ※評価順に並べるにはratingsとmoviesを結合する必要がある
SELECT movies.title,ratings.rating FROM movies JOIN ratings ON movies.id = ratings.movie_id WHERE movies.id IN (SELECT movie_id FROM stars WHERE person_id IN (SELECT id FROM people WHERE name = 'Chadwick Boseman')) ORDER BY ratings.rating DESC LIMIT 5;
12.sqlで、Johnny DeppとHelena Bonham Carterの両方が主演したすべての映画のタイトルをリストするSQLクエリを記述します。
クエリを実行すると、各ムービーのタイトルを1つの列にまとめたテーブルが出力されます。
データベースにはJohnny Deppという名前の人が1人しかいないと仮定します。
データベースには、Helena Bonham Carterという名前の人物が1人だけ存在するものとします。
①Johnny Deppのid
SELECT id FROM people WHERE name = 'Johnny Depp';
②Helena Bonham Carterのid
SELECT id FROM people WHERE name = 'Helena Bonham Carter';
③2人が出演した映画のid(starsテーブルのmovie_id)
↓ANDで繋げる？ ←違うっぽい
SELECT movie_id FROM stars WHERE person_id = (SELECT id FROM people WHERE name = 'Johnny Depp') AND person_id = (SELECT id FROM people WHERE name = 'Helena Bonham Carter');
④Johnny Deppだけが出演した映画
SELECT movie_id FROM stars WHERE person_id IN (SELECT id FROM people WHERE name = 'Johnny Depp');
↑ちゃんと出る
⑤Helena Bonham carterだけが出演した映画
SELECT movie_id FROM stars WHERE person_id IN (SELECT id FROM people WHERE name = 'Helena Bonham Carter');
↑ちゃんと出る
④と⑤の両方の結果で重複しているidを抽出するには？
重複レコードを抽出する方法
HAVING句を使用する場合はGROUP BY句でグループ化して、
COUNT関数で件数を取得した結果が1より大きいレコード（2件以上あるレコード）をHAVING句の条件に指定します。
SELECT movie_id FROM stars WHERE person_id IN (SELECT id FROM people WHERE name = 'Johnny Depp') OR person_id IN (SELECT id FROM people WHERE name = 'Helena Bonham Carter') GROUP BY movie_id HAVING COUNT(movie_id) > 1;
SELECT title FROM movies WHERE id IN (SELECT movie_id FROM stars WHERE person_id IN (SELECT id FROM people WHERE name = 'Johnny Depp') OR person_id IN (SELECT id FROM people WHERE name = 'Helena Bonham Carter') GROUP BY movie_id HAVING COUNT(movie_id) > 1);
▼結果
6行 OK
title
Corpse Bride
Charlie and the Chocolate Factory
Sweeney Todd: The Demon Barber of Fleet Street
Alice in Wonderland
Dark Shadows
Alice Through the Looking Glass
13.Kevin Baconが主演した映画に出演したすべての人の名前をリストするSQLクエリを記述します。
クエリを実行すると、各ユーザーの名前を1つの列で表すテーブルが出力されます。
データベースにKevin Baconという名前の人が複数存在する可能性があります。必ず1958年生まれのKevin Baconだけを選んでください。
Kevin Bacon本人は結果リストに含まれません。
①1958年生まれのKevin Baconが出演した全ての映画のid
SELECT id FROM movies WHERE id IN (SELECT movie_id FROM stars WHERE person_id IN (SELECT id FROM people WHERE name = 'Kevin Bacon' AND birth = 1958));
②①に出演した人の名前
SELECT name FROM people WHERE id IN (SELECT person_id FROM stars WHERE movie_id IN(SELECT id FROM movies WHERE id IN (SELECT movie_id FROM stars WHERE person_id IN (SELECT id FROM people WHERE name = 'Kevin Bacon' AND birth = 1958))));
SELECT COUNT(name) FROM people WHERE id IN (SELECT person_id FROM stars WHERE movie_id IN(SELECT id FROM movies WHERE id IN (SELECT movie_id FROM stars WHERE person_id IN (SELECT id FROM people WHERE name = 'Kevin Bacon' AND birth = 1958))));
▼結果
177行 1行多い…？
この記事が気に入ったらサポートをしてみませんか？