CS50 2020 Week7: SQL Problem Answers

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

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

1

select title from movies where year=2008;

1

select birth from people where name="Emma Stone";

1

select title from movies where year >= 2018 order by title asc;

1
2
3

select count(movies.id) from movies 
join ratings on movies.id=ratings.movie_id 
and ratings.rating=10;

1
2
3

select title, year from movies 
where title like 'Harry Potter%' 
order by year;

1
2
3

select avg(rating) from ratings 
join movies on movies.id=ratings.movie_id 
where movies.year=2012;

1
2
3
4
5
6

select ratings.rating, movies.title
from movies
join ratings on movies.id=ratings.movie_id
where movies.year=2010
order by ratings.rating desc,
movies.title asc;

1
2
3
4

select name from people 
join stars on stars.person_id=people.id 
join movies on stars.movie_id=movies.id 
where movies.title="Toy Story";

1
2
3
4
5
6

select distinct name from people
join stars on stars.person_id=people.id
join movies on stars.movie_id=movies.id
join ratings on ratings.movie_id=movies.id
where movies.year=2004
order by people.birth;

1
2
3
4
5

select name from people
join directors on directors.person_id=people.id
join movies on directors.movie_id=movies.id
join ratings on ratings.movie_id=movies.id
where ratings.rating >= 9;

1
2
3
4
5
6
7

select movies.title from movies
join stars on stars.movie_id=movies.id
join people on stars.person_id=people.id
join ratings on ratings.movie_id=movies.id
where people.name="Chadwick Boseman"
order by ratings.rating desc
limit 5;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

select title from movies
join stars on stars.movie_id=movies.id
join people on stars.person_id=people.id
where people.name="Johnny Depp"
and title in (
select title from movies
join stars on stars.movie_id=movies.id
join people on stars.person_id=people.id
where people.name="Helena Bonham Carter"
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

select name from people
join stars on stars.person_id=people.id
join movies on stars.movie_id=movies.id
where movies.id in
(select movies.id from movies
join people on stars.person_id=people.id
join stars on stars.movie_id=movies.id
where people.name="Kevin Bacon"
and people.birth=1958)
and people.name != "Kevin Bacon";