进阶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"
  1. 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
  1. 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
  1. 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
  1. 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: 之前想多了, 总想着把三个表连接起来, 有选项为空是没问题的  
    
  1. 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  
    

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

  2. 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”, 加上都过了, 我吐了你呢?  
    
  1. 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
  1. 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 : 电影名字可能会重复, 故不能写编号, 但是评论家的名字为啥不能重复?  
    
  1. 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
  1. 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”, 粉墨登场  
    
  1. 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"
)
  1. 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: ???  
    
  1. 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: ???  
    
  1. 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
  1. 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  
    
  1. 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
  1. 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


  1. 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
```
 

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, 挺难的, 再说吧