カテゴリー
Subscribe Now

* You will receive the latest news and updates on your favorite celebrities!

Trending News
Rampsをはじめるきっかけ 

  目次1 日本からの電話2 日本行きを阻む香港政府のコロナ対策3 34歳のふわっとした夢…

Blog Post

米ハーバード大学 CS50:課題「映画のデータベースを操る魔術師になる」の回答例(Harvard CS50 Week 7 SQL − PSet 7: Movies)
Lifestyle, Tech

米ハーバード大学 CS50:課題「映画のデータベースを操る魔術師になる」の回答例(Harvard CS50 Week 7 SQL − PSet 7: Movies) 

IMDbという映画サイトのデータベースを使って、データベースの操作になれるというのが今回の課題。

SELECT title FROM movies WHERE year = 2008;
SELECT birth FROM people WHERE name = 'Emma Stone';
SELECT title FROM movies WHERE year >= 2018 ORDER BY title;
SELECT COUNT(movie_id) FROM ratings WHERE rating = 10.0
SELECT title, year FROM movies
WHERE title LIKE "Harry Potter%"
ORDER BY year;
SELECT avg(rating) FROM ratings
JOIN movies ON ratings.movie_id = movies.id
WHERE year = 2012;
SELECT title, rating
FROM movies JOIN ratings ON id = movie_id
WHERE year = 2010
ORDER BY rating DESC, title ASC
SELECT name FROM people
JOIN stars ON people.id = stars.person_id
JOIN movies ON stars.movie_id = movies.id
WHERE movies.title = "Toy Story";
SELECT distinct(people.name) FROM stars
JOIN people ON stars.person_id = people.id
JOIN movies ON stars.movie_id = movies.id
WHERE movies.year = 2004
ORDER BY birth;
SELECT name from people
JOIN directors ON people.id = directors.person_id
JOIN movies ON directors.movie_id = movies.id
JOIN ratings on movies.id = ratings.movie_id
WHERE rating >= 9;
SELECT movies.title FROM people
JOIN stars ON people.id = stars.person_id
JOIN movies ON stars.movie_id = movies.id
JOIN ratings on movies.id = ratings.movie_id
WHERE people.name = "Chadwick Boseman"
ORDER BY rating DESC
LIMIT 5;
SELECT movies.title FROM people
JOIN stars ON people.id = stars.person_id
JOIN movies ON stars.movie_id = movies.id
WHERE people.name = "Johnny Depp" AND movies.title IN(
SELECT movies.title FROM people
JOIN stars ON people.id = stars.person_id
JOIN movies ON stars.movie_id = movies.id
WHERE people.name = "Helena Bonham Carter");
SELECT distinct(name) FROM people
JOIN stars ON people.id = stars.person_id
JOIN movies ON stars.movie_id = movies.id
WHERE movies.title IN(SELECT distinct(movies.title) FROM people
JOIN stars ON people.id = stars.person_id
JOIN movies ON stars.movie_id = movies.id
WHERE people.name = "Kevin Bacon" AND people.birth = 1958) AND people.name != "Kevin Bacon";

Related posts

コメントを残す

Required fields are marked *