SELECT mov_id, mov_title, YEAR(mov_dt_rel) AS release_year FROM movie WHERE mov_year BETWEEN2010AND2020 ORDERBY release_year ASC;
3.找出那些没有参演任何影片的演员，输出这些演员的ID信息。
1 2 3 4
SELECT act_id FROM actor WHERE act_id NOTIN (SELECTDISTINCT act_id FROM movie_cast);
4.编写一个SQL查询，查找所有评分为8星或8星以上的评论者的昵称。
1 2 3 4 5
SELECTDISTINCT r.rev_name FROM reviewer r JOIN rating rt ON r.rev_id = rt.rev_id WHERE rt.rev_stars >=8; # 这个查询通过连接reviewer表和rating表，选择所有评分大于或等于8的评论者的昵称，并确保结果中没有重复的昵称。
5.查询电影《活着》的所有演员信息，返回演员表的所有字段。
1 2 3 4 5 6
SELECT a.* FROM actor a JOIN movie_cast mc ON a.act_id = mc.act_id JOIN movie m ON mc.mov_id = m.mov_id WHERE m.mov_title ='活着'; # 这个查询通过连接actor、movie_cast和movie表，选择电影《活着》的所有演员信息。
6.查询平均评分最高的电影ID和名称，平均评分，参评人数.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
### 参考答案 SELECT m.mov_id, m.mov_title, AVG(r.rev_stars) AS average_rating, SUM(r.num_o_ratings) AS total_ratings FROM movie m JOIN rating r ON m.mov_id = r.mov_id GROUPBY m.mov_id, m.mov_title ORDERBY average_rating DESC, total_ratings DESC LIMIT 1;
1 2 3 4 5 6 7 8
### 可能（错误）的答案 SELECT m.mov_id, m.mov_title, AVG(r.rev_stars) AS avg_rating, COUNT(r.rev_id) AS num_of_ratings FROM rating r JOIN movie m ON r.mov_id = m.mov_id GROUPBY m.mov_id, m.mov_title ORDERBY avg_rating DESC LIMIT 1;
## 参考答案 -- 首先，找出获奖项目超过3项的电影ID WITH movie_award_counts AS ( SELECT mov_id, COUNT(DISTINCT awd_id) AS award_count FROM movie_awards GROUPBY mov_id HAVING COUNT(DISTINCT awd_id) >3 )
-- 然后，获取这些电影的具体获奖信息 SELECT ma.mov_id, ma.awd_type, ma.awd_id FROM movie_awards ma JOIN movie_award_counts mac ON ma.mov_id = mac.mov_id ORDERBY ma.mov_id, ma.awd_id;
1 2 3 4 5 6 7
# 可能错误的答案 SELECT ma.mov_id, aw.awd_level, ma.awd_id FROM movie_awards ma JOIN awards aw ON ma.awd_id = aw.awd_id GROUPBY ma.mov_id, aw.awd_level, ma.awd_id HAVINGCOUNT(ma.awd_id) >3;
## 正确答案一 SELECT m.mov_title, MAX(r.rev_stars) AS highest_rating FROM movie m JOIN rating r ON m.mov_id = r.mov_id GROUPBY m.mov_id, m.mov_title HAVING COUNT(DISTINCT r.rev_id) >=3;
## 正确答案2 -- 首先，找出评论者数量不少于3的电影ID WITH movie_reviewer_counts AS ( SELECT mov_id, COUNT(DISTINCT rev_id) AS reviewer_count FROM rating GROUPBY mov_id HAVING COUNT(DISTINCT rev_id) >=3 ),
-- 然后，找到这些电影的最高评分 max_ratings AS ( SELECT mov_id, MAX(rev_stars) AS max_rating FROM rating WHERE mov_id IN (SELECT mov_id FROM movie_reviewer_counts) GROUPBY mov_id )
-- 最后，获取这些电影的片名和最高评分 SELECT m.mov_title, mr.max_rating FROM movie m JOIN max_ratings mr ON m.mov_id = mr.mov_id;
9.找出所有参演了电影“建国大业”的演员姓名和ID
1 2 3 4 5 6 7 8 9 10 11 12
-- 选择参演了电影“建国大业”的演员姓名和ID SELECT a.act_name, a.act_id FROM actor a JOIN movie_cast mc ON a.act_id = mc.act_id JOIN movie m ON mc.mov_id = m.mov_id WHERE m.mov_title ='建国大业';
或
1 2 3 4 5
SELECT a.act_id, a.act_name FROM actor a JOIN movie_cast mc ON a.act_id = mc.act_id WHERE mc.mov_id =101; -- 电影《建国大业》的ID为101
SELECT m.mov_id, m.mov_title, CASE WHENCOUNT(ma.awd_id) >0THEN'是' ELSE'否' ENDAS 是否获奖, COUNT(ma.awd_id) AS 获奖数量 FROM movie m LEFTJOIN movie_awards ma ON m.mov_id = ma.mov_id GROUPBY m.mov_id, m.mov_title;
分析： LEFT JOIN 用于连接 movie 表和 movie_awards 表，以确保所有电影都被列出，即使没有获奖。 CASE 语句用于判断电影是否获奖，若获奖数量大于0则显示“是”，否则显示“否”。 COUNT(ma.awd_id) 用于计算获奖数量，未获奖的电影将显示为0。
## 答案2 -- 计算每部电影的获奖数量 WITH movie_award_counts AS ( SELECT mov_id, COUNT(*) AS award_count FROM movie_awards GROUPBY mov_id )
-- 查询所有电影信息，包括电影ID、电影名、是否获奖以及获奖数量 SELECT m.mov_id, m.mov_title, CASE WHEN mac.award_count >0THEN'是' ELSE'否' ENDAS 是否获奖, COALESCE(mac.award_count, 0) AS 获奖数量 FROM movie m LEFTJOIN movie_award_counts mac ON m.mov_id = mac.mov_id ORDERBY m.mov_id;
## 正确答案 -- 首先，找出所有属于“喜剧”类型的电影ID WITH comedy_movies AS ( SELECT mg.mov_id FROM movie_genres mg JOIN genres g ON mg.gen_id = g.gen_id WHERE g.gen_title ='喜剧' ),
-- 然后，获取这些电影的评分信息 movie_ratings AS ( SELECT m.mov_id, m.mov_title, r.rev_stars FROM movie m JOIN rating r ON m.mov_id = r.mov_id WHERE m.mov_id IN (SELECT mov_id FROM comedy_movies) )
-- 最后，根据评分对这些电影进行排序，并选择评分最高的前3名 SELECT mr.mov_title, MAX(mr.rev_stars) AS max_rating FROM movie_ratings mr GROUPBY mr.mov_id, mr.mov_title ORDERBY max_rating DESC LIMIT 3;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
## 错误答案 SELECT m.mov_title FROM movie m JOIN movie_genres g ON m.mov_id = g.mov_id JOIN rating r ON m.mov_id = r.mov_id WHERE g.gen_id =302-- 喜剧的gen_id ORDERBY r.rev_stars DESC LIMIT 3;
-- 找出唐国强的act_id WITH tang_guoqiang AS ( SELECT act_id FROM actor WHERE act_name ='唐国强' ),
-- 找出唐国强参演的所有电影 tang_movies AS ( SELECT mov_id FROM movie_cast WHERE act_id = (SELECT act_id FROM tang_guoqiang) ),
-- 找出与唐国强共同出演同一部电影的演员（不包括唐国强自己） co_actors AS ( SELECT mc.act_id, a.act_name FROM movie_cast mc JOIN actor a ON mc.act_id = a.act_id JOIN tang_movies tm ON mc.mov_id = tm.mov_id WHERE mc.act_id != (SELECT act_id FROM tang_guoqiang) -- 排除唐国强自己 )
-- 选择共同出演的演员ID和姓名（不包括唐国强自己） -- 就是这里，cccccc SELECT ca.act_name, ca.act_id FROM co_actors ca;
SELECT m.mov_id, m.mov_title, AVG(r.rev_stars) AS average_rating, CASE WHENAVG(r.rev_stars) <6THEN'不推荐' WHENAVG(r.rev_stars) BETWEEN6AND8THEN'推荐' ELSE'强烈推荐' ENDAS recommendation FROM movie m JOIN movie_direction d ON m.mov_id = d.mov_id LEFTJOIN rating r ON m.mov_id = r.mov_id WHERE d.dir_id = (SELECT dir_id FROM director WHERE dir_name ='吴宇森') GROUPBY m.mov_id, m.mov_title ORDERBY m.mov_id;
-- 获取科幻类型的gen_id WITH sci_fi_genre AS ( SELECT gen_id FROM genres WHERE gen_title ='科幻' ),
-- 获取所有科幻电影及其平均评分 sci_fi_movies AS ( SELECT m.mov_id, m.mov_title, AVG(r.rev_stars) AS avg_rating FROM movie m JOIN movie_genres mg ON m.mov_id = mg.mov_id JOIN rating r ON m.mov_id = r.mov_id WHERE mg.gen_id = (SELECT gen_id FROM sci_fi_genre) GROUPBY m.mov_id, m.mov_title ),
-- 找出平均评分最高的科幻电影 highest_rated_sci_fi AS ( SELECT mov_title, avg_rating, RANK() OVER (ORDERBY avg_rating DESC) AS rank FROM sci_fi_movies )
-- 返回评分最高的科幻电影 SELECT mov_title, avg_rating FROM highest_rated_sci_fi WHERE rank =1;
-- 获取张艺谋导演的所有电影 WITH zhang_yimou_movies AS ( SELECT md.mov_id FROM movie_direction md WHERE md.dir_id =202-- 张艺谋的dir_id ),
-- 获取所有参演张艺谋电影的演员及其参演的电影数量 zhang_yimou_actors AS ( SELECT mc.act_id, a.act_name, COUNT(DISTINCT mc.mov_id) AS movie_count FROM movie_cast mc JOIN actor a ON mc.act_id = a.act_id JOIN zhang_yimou_movies zym ON mc.mov_id = zym.mov_id GROUPBY mc.act_id, a.act_name )
-- 找出参演了张艺谋全部影片的演员 SELECT za.act_name FROM zhang_yimou_actors za WHERE za.movie_count = (SELECTCOUNT(DISTINCT mov_id) FROM zhang_yimou_movies);
