进阶30题

ps : 上一篇基础30题看不看初始化代码都无所谓, 但是这20个题目, 不能不看.

  1. Find the titles(title) of all movies directed by Steven Spielberg

    1
    2
    3
    select title
        from movie
        where Director = "Steven Spielberg"

     

  2. Find all years(year) that have a movie that received a rating of 4 or 5, and sort them in increasing order.

    1
    2
    3
    4
    select distinct year
        from rating, movie
        where stars > 3 and movie.mid = rating.mid
        order by year asc

     

  3. Find the titles(title) of all movies that have no ratings.

    1
    2
    3
    select title
        from movie left join rating on movie.mid = rating.mid
        where stars is null

     

  4. Some reviewers didn’t provide a date with their rating.
    Find the names(name) of all reviewers who have ratings with a NULL value for the date.

    1
    2
    3
    select distinct name
        from rating left join reviewer on rating.rid = reviewer.rid
        where ratingDate is null

     

  5. Write a query to return the ratings data in a more readable format: reviewer name, movie title, stars, and ratingDate.
    Also, sort the data, first by reviewer name, then by movie title, and lastly by number of stars.

    1
    2
    3
    4
    select reviewer.name, movie.title, rating.stars, rating.ratingDate
        from movie, reviewer, rating
        where movie.mid = rating.mid and rating.rid = reviewer.rid
    		order by reviewer.name, movie.title, rating.stars, rating.ratingDate

    PS: 之前想多了, 总想着把三个表连接起来, 有选项为空是没问题的
     

  6. For all cases where the same reviewer rated the same movie twice and gave it a higher rating the second time, return the reviewer’s name and the title of the movie.

    1
    2
    3
    4
    5
    6
    7
    select name, title 
        from movie, reviewer, rating r1, rating r2
            where movie.mid = r1.mid 
            and r1.rid = reviewer.rid 
            and r1.mid = r2.mid and r1.rid = r2.rid 
            and r1.ratingDate < r2.ratingDate 
            and r1.stars < r2.stars

    PS: rating r1, rating r2 同时定义了两个rating表, “rating”就不能直接用了, 只能用r1/r2
     

  7. 标号有点问题, 20个题目,第一题题号1030, 最后一个是1050, 所以中间跳过一个编号才是20题……OwO
     

  8. List movie titles(title) and average ratings(stars), from highest-rated to lowest-rated.
    If two or more movies have the same average rating, list them in alphabetical order.

    1
    2
    3
    4
    select title, avg(stars) as stars
        from movie inner join rating on movie.mid = rating.mid
        group by movie.mid
        order by avg(stars) desc, title
    1
    2
    3
    4
    5
    select title, avg(stars) as stars
        from movie, rating
            where movie.mid = rating.mid and stars is not null
            group by rating.mid
            order by avg(stars) desc, title
    1
    2
    3
    4
    5
    6
    select title, new.avgstars as stars
        from movie join (select rating.mid, avg(rating.stars) as avgstars
                         from rating
                         group by rating.mid) as new
        on movie.mid = new.mid
        order by new.avgstars desc, title;

    PS: 三种方法, 我换来换去, 结果发现没用”as stars”, 加上都过了, 我吐了你呢?
     

  9. Find the names(name) of all reviewers who have contributed three or more ratings. (As an extra challenge, try writing the query without HAVING or without COUNT.)

    1
    2
    3
    4
    select name
        from rating, reviewer
        where rating.rid = reviewer.rid
        group by rating.rid having count(rating.rid) > 2

     

  10. Find the names(name) of all reviewers who rated Gone with the Wind.

    1
    2
    3
    select distinct name
        from rating, reviewer, movie
            where rating.rid = reviewer.rid and rating.mid = movie.mid and movie.title = "Gone with the Wind"

    PS : 电影名字可能会重复, 故不能写编号, 但是评论家的名字为啥不能重复?
     

  11. For any rating where the reviewer is the same as the director of the movie, return the reviewer name, movie title, and number of stars.

    1
    2
    3
    select name, title, stars
        from movie, reviewer, rating
            where movie.director = reviewer.name and movie.mid = rating.mid and rating.rid = reviewer.rid

     

  12. Return all reviewer names(name) and movie names together in a single list, alphabetized. (Sorting by the first name of the reviewer and first word in the title is fine; no need for special processing on last names or removing “The”.) 

    1
    2
    3
    4
    select name from reviewer
        union
        select title as name from movie
        order by name;

    PS: “Union”, 粉墨登场
     

  13. Find the titles(title) of all movies not reviewed by Chris Jackson.

    1
    2
    3
    4
    5
    6
    7
    select title
        from movie
        where mid not in (
    	select mid
        from rating inner join reviewer using(rid)
        where name = "Chris Jackson"
    )

     

  14. For all pairs of reviewers such that both reviewers gave a rating to the same movie, return the names of both reviewers(reviewer_name1, reviewer_name2). Eliminate duplicates, don’t pair reviewers with themselves, and include each pair only once. For each pair, return the names in the pair in alphabetical order.

    1
    2
    3
    4
    5
    6
    select distinct r1.name as reviewer_name1, r2.name as reviewer_name2
        from reviewer r1, reviewer r2, rating rt1, rating rt2
            where r1.name < r2.name
            and r1.rid = rt1.rid
            and r2.rid = rt2.rid
            and rt1.mid = rt2.mid;

    PS: ???
     

  15. For each rating that is the lowest (fewest stars) currently in the database, return the reviewer name, movie title, and number of stars.

    1
    2
    3
    4
    5
    select r.name, m.title, rt.stars
        from rating rt
        left join reviewer r on r.rid = rt.rid
       	left join movie m on rt.mid = m.mid
        where rt.stars in (select min(stars) from rating);

    PS: ???
     

  16. List movie titles(title) and average ratings(avg_rating), from highest-rated to lowest-rated. If two or more movies have the same average rating, list them in alphabetical order.

    1
    2
    3
    4
    select title, avg(stars) as avg_rating
        from movie inner join rating using(mid)
            group by movie.mid
        	order by avg_rating desc, title

     

  17. Find the names(name) of all reviewers who have contributed three or more ratings. (As an extra challenge, try writing the query without HAVING or without COUNT.)

    1
    2
    3
    select distinct name
        from reviewer inner join rating using (rid)
            group by reviewer.rid having count(*) >= 3

    PS: 对于extra challenge, 我表示无能为力O.o
     

  18. Some directors directed more than one movie. For all such directors, return the titles(title) of all movies directed by them, along with the director name. Sort by director name(director), then movie title. (As an ex

    1
    2
    3
    4
    5
    6
    7
    8
    9
    select title, d1.director
        from movie d1
        where title is not null
        and director is not null
        and (select count(*)
            from movie d2
             where d1.director = d2.director
            ) > 1
        order by d1.director, title

     

  19. Find the movie(s) with the highest average rating. Return the movie title(s) and average rating(avg_rating).
    (Hint: This query is more difficult to write in SQLite than other systems; you might think of it as finding the highest average rating and then choosing the movie(s) with that average rating.)

    1
    2


     

  20. Find the movie(s) with the lowest average rating. Return the movie title(s) and average rating(avg_rating). (Hint: This query may be more difficult to write in SQLite than other systems; you might think of it as finding the lowest average rating and then choosing the movie(s) with that average rating.)

    1
    2
    3
    4
    5
    ```
    &nbsp;

    1050. For each director, return the **director**'s name together with the **title**(s) of the movie(s) they directed that received the highest rating(**stars**) among all of their movies, and the value of that rating. Ignore movies whose director is NULL.
    ```sql

     

PS: 最后三个需要多层select, 挺难的, 再说吧