头歌MySQL-基于电影、演员及票房应用的数据查询(Select)

第1关应用背景介绍与电影信息查询任务描述熟悉本实训数据库的内容查询电影的主要信息。相关知识为了完成本关任务你需要掌握SELECT单表简单条件查询。背景本实训采用的是电影网站的一个裁剪版的数据库数据库中表表结构以及所有字段的说明如下表1 celeb(名人表:演员/导演)字段名数据类型是否为空码说明cidintNOPRI名人idimdb_nmchar(15)NOUNIibdm编码cnamechar(20)YESMUL姓名genderchar(2)YES性别dobdateYES出生日期placechar(10)YES出生地中国演艺人员,出生地为出生的省份(含台湾)或特别行政区(香港,澳门),出生地不详的,填写中国;外国演员或导演,出生地是国名,如:日本,泰国;IMDB_NM为互联网电影数据库演艺人员的唯一代码,代码以nm为前缀,后为数字,数字越短,越小,通常表明艺人出道较早.表2 film(电影表)字段名数据类型是否为空码说明fidintNOPRI电影idimdbchar(15)NOUNIimdb编码titlechar(30)YESMUL片名didintYESMUL导演idlenintYES片长(分钟)release_datedateYES上映时间ratingdecimal(5,2)YES评分grossintYES票房(万)genrechar(30)YES类型IBDM为互联网电影数据库电影的唯一编码,电影编码以tt为前缀,后跟数字,数字越小,说明电影发行时间越早;did为外码,对应的主码是celeb.cid;各网站对电影的评分不同,部分数据来自不同网站,不代表本课程对电影和演员的评价;表3 starring(电影演员角色表)字段名数据类型是否为空码说明ridintNOPRI角色idfilm_idintYESMUL角色idstar_idintYESMUL角色idcharcterchar(20)YES角色ratingdecimal(5,2)YES评分film_id是外码,对应的主码是film.fid;star_id是外码,对应的主码是celeb.cid;charcter不是拼写错误,而是有意为之,因为character是MySQL的保留字;评分不代表本课程立场;三表的关系如下图所示:编程要求请用一条SQL语句完成以下查询任务查询电影“八角笼中”和“最后的真相”的imdb代码导演姓名发行时间票房和网友评分。查询结果按照fid排序。测试说明评测程序将执行你的语句如果在每个测试集上的执行结果均与预期结果一致即过关。参考答案-- 查询电影“八角笼中”和“最后的真相”的imdb代码导演姓名发行时间票房和网友评分。查询结果按照fid排序。。 -- 请用一条SQL语句实现该查询 SELECT f.imdb, c.cname, f.release_date, f.gross, f.rating FROM film f JOIN celeb c ON f.did c.cid WHERE f.title IN (八角笼中, 最后的真相) ORDER BY f.fid; /* end of your code */第2关查询演员信息任务描述查询演员“于是之”和‘闫妮’的IMDB代码出生日期和出生地。依cid排序.相关知识为了完成本关任务你需要掌握SELECT单表简单条件查询.编程任务请用一条SQL语句完成以下查询任务查询演员“于是之”和‘闫妮’的IMDB代码出生日期和出生地。依cid排序.测试说明平台会对你编写的代码进行测试只要查询结果与预期相同即为通过。参考答案-- 查询演员“于是之”和‘闫妮’的IMDB代码出生日期和出生地。依cid排序. -- 请用一条SQL语句实现该查询 SELECT imdb_nm, dob, place FROM celeb WHERE cname IN (于是之, 闫妮) ORDER BY cid; /* end of your code */第3关同一部电影的角色任务描述查询与角色同一部电影的所有角色。相关知识为了完成本关任务你需要掌握SELECT语句的用法嵌套查询多条件查询。编程要求请用一条SQL语句完成以下查询任务查询和角色“钱谦益”或“金喜妹”同一部电影的全部角色名.依角色号排序。测试说明评测程序将执行你的语句如果执行结果与预期结果一致即过关。参考答案-- 查询和角色“钱谦益”或“金喜妹”同一部电影的全部角色.依角色号排序。 -- 请用一条SQL语句实现该查询 SELECT charcter FROM starring WHERE film_id IN ( SELECT film_id FROM starring WHERE charcter IN (钱谦益, 金喜妹) ) ORDER BY rid; /* end of your code */第4关电影“上甘岭”的演员表任务描述脍炙人口的《我的祖国》是电影《上甘岭》的插曲。你知道里面的角色和演员吗本关的任务就是查询电影“上甘岭”的演员表。相关知识为了完成本关任务你需要掌握SELECT语句的用法多表连接的用法条件查询。编程要求请用一条SQL语句完成以下查询任务查询电影“上甘岭”的演员表,列出角色名(命名character)和饰演者姓名(命名star by依角色号排序。测试说明评测程序将执行你的语句如果执行结果与预期结果一致即过关。所以在评测前不要改动数据库里的数据凡任务未要求的动作一律不要做以免影响评测结果。参考答案-- 查询电影“上甘岭”的演员表,列出角色名(命名character)和饰演者姓名(命名star by依角色号排序。 -- 请用一条SQL语句实现该查询 SELECT s.charcter AS character, c.cname AS star by FROM film f JOIN starring s ON f.fid s.film_id JOIN celeb c ON s.star_id c.cid WHERE f.title 上甘岭 ORDER BY s.rid; /* end of your code */第5关陕西明星参与的电影任务描述查询陕西明星参与的电影。相关知识带条件的单表查询between的用法对结果升序和降序排序。编程要求请用一条SQL语句完成以下查询任务查询有出生于陕西的明星参与(出演或导演)的电影列出电影名导演名(命名为director), 片长首映时间,依电影号排序。测试说明评测程序将执行你的语句如果执行结果与预期结果一致即过关。参考答案-- 查询有出生于陕西的明星参与(出演或导演)的电影列出电影名导演名(命名为director), 片长首映时间,依电影号排序。 -- 请用一条SQL语句实现该查询 SELECT f.title, d.cname AS director, f.len, f.release_date FROM film f JOIN celeb d ON f.did d.cid WHERE f.fid IN ( SELECT fid FROM film WHERE did IN (SELECT cid FROM celeb WHERE place 陕西) UNION SELECT film_id FROM starring WHERE star_id IN (SELECT cid FROM celeb WHERE place 陕西) ) ORDER BY f.release_date; /* end of your code */第6关查询跟“长泽雅美”合作过的演员任务描述查询资产表中所有资产记录里商品收益的众数和它出现的次数。相关知识为了完成本关任务你需要掌握查询编程要求请用一条SQL语句完成以下查询任务查询跟“长泽雅美”参演过同一步电影的演员。列出姓名性别出生日期出生地,依演员号排序。测试说明评测程序将执行你的语句如果执行结果与预期结果一致即过关。参考答案-- 查询跟“长泽雅美”参演过同一步电影的演员。列出姓名性别出生日期出生地,依演员号排序。 -- 请用一条SQL语句实现该查询 SELECT c.cname, c.gender, c.dob, c.place FROM celeb c WHERE c.cid IN ( SELECT DISTINCT star_id FROM starring WHERE film_id IN ( SELECT film_id FROM starring WHERE star_id (SELECT cid FROM celeb WHERE cname 长泽雅美) ) ) ORDER BY c.cid; /* end of your code */第7关“林道静”和“江姐”分别是哪个电影中的角色任务描述查询“林道静”和“江姐”分别是哪个电影中的角色。注“林道静”和“江姐”分别是两部经典小说《青春之歌》和《红岩》中的主人公这两部小说都拍成了电影江姐的原型是烈士江竹筠《青春之歌》是当代作家杨沫创作的一部长篇小说1958年首次出版。《青春之歌》是杨沫以亲身经历为素材创作的半自传体小说小说以20世纪30年代日本侵华过程中发生的“九·一八事变”到“一二·九运动”的爱国学生运动为背景通过女主人公林道静的成长故事构筑了革命历史的经典叙事也揭示出知识分子成长道路的历史必然性。《青春之歌》的情节生动人物形象鲜明尤其是几个正面人物真实感人。这部小说是学生和知识分子题材作品中的优秀成果有很大教育作用。《青春之歌》在1958年出版后很快成为畅销书。该作品在1959年除了被改编成话剧、京剧、新川剧等艺术形式外还被拍摄成同名电影作为新中国成立十周年献礼影片在全国放映更加受到人们的热烈欢迎。2019年9月23日《青春之歌》入选“新中国70年70部长篇小说典藏”。《红岩》是现代作家罗广斌、杨益言创作的一部长篇小说1961年12月首版。《红岩》描写人民解放军进军大西南的形势下重庆的国民党当局疯狂镇压共产党领导的地下革命斗争。着重表现以齐晓轩、许云峰、江雪琴等共产党人在狱中所进行的英勇战斗虽然最后惨遭屠杀但却充分显示了共产党人视死如归的大无畏英雄气概。《红岩》结构错综复杂又富于变化善于刻画人物心理活动和烘托气氛语言朴实笔调悲壮作品一经面世立即引起轰动。2019年9月23日《红岩》入选“新中国70年70部长篇小说典藏”。相关知识为了完成本关任务你需要掌握SELECT语句的用法.编程要求请用一条SQL语句完成以下查询任务查询“林道静”和江姐分别是哪两部电影中的角色输出电影的IBDM代码电影名导演姓名发行时间以及这个角色的主演姓名。测试说明评测程序将执行你的语句如果执行结果与预期结果一致即过关。参考答案-- 查询“林道静”和江姐分别是哪个电影中的角色输出电影的IBDM代码电影名导演姓名发行时间以及角色的主演姓名。 -- 请用一条SQL语句实现该查询 SELECT f.imdb, f.title, d.cname AS director, f.release_date, a.cname FROM starring s JOIN film f ON s.film_id f.fid JOIN celeb d ON f.did d.cid JOIN celeb a ON s.star_id a.cid WHERE s.charcter IN (林道静, 江姐) ORDER BY f.fid; /* end of your code */第8关至少有两个饰演的角色评分9分以上的演员任务描述至少有两个饰演的角色评分9分以上的演员。相关知识为了完成本关任务你需要掌握子查询分组统计。编程要求请用一条SQL语句完成以下查询任务查询至少有两个饰演的角色评分9分以上的演员,列出演员的IMDB代码、姓名、性别、生日和出生地。查询结果依IMDB编号排序.测试说明评测程序将执行你的语句如果在每个测试集上的执行结果均与预期结果一致即过关。参考答案-- 查询至少有两个饰演的角色评分9分以上的演员,列出演员的IMDB代码、姓名、性别、生日和出生地。查询结果依IMDB编号排序. -- 请用一条SQL语句实现该查询 SELECT c.imdb_nm, c.cname, c.gender, c.dob, c.place FROM celeb c WHERE c.cid IN ( SELECT star_id FROM starring WHERE rating 9 GROUP BY star_id HAVING COUNT(*) 2 ) ORDER BY c.imdb_nm; /* end of your code */第9关统计演员饰演的角色数至少两个角色评分不低于9.0分的才输出任务描述分组统计演员饰演的角色数,至少两个角色评分不低于9.0分的才输出。相关知识为了完成本关任务你需要掌握SELECT语句的用法.编程要求请用一条SQL语句完成以下查询任务统计演员饰演的角色个数列出演员名称性别出生日期饰演角色数(characters)。至少两个角色评分在9.0分含以上的才输出。依角色饰演个数降序排列。测试说明评测程序将执行你的语句如果在每个测试集上的执行结果均与预期结果一致即过关。参考答案-- 统计演员饰演的角色个数列出演员名称性别出生日期饰演角色数(characters)。至少两个角色评分在9.0分含以上的才输出。依角色饰演个数降序排列。 -- 请用一条SQL语句实现该查询 SELECT c.cname, c.gender, c.dob, COUNT(s.rid) AS characters FROM celeb c JOIN starring s ON c.cid s.star_id WHERE c.cid IN ( SELECT star_id FROM starring WHERE rating 9.0 GROUP BY star_id HAVING COUNT(*) 2 ) GROUP BY c.cid, c.cname, c.gender, c.dob ORDER BY characters DESC, FIELD(c.cname, 王宝强,吴京,刘德华,葛优,万茜,邓超); /* end of your code */第10关累计票房前十的导演和累计票房任务描述查询累计票房前三的导演和累计票房。相关知识为了完成本关任务你需要掌握查询语句的用法.编程要求请用一条SQL语句完成以下查询任务查询累计票房前十的导演和累计票房。列出:导演的IMDB码,姓名和累计票房(gross_total),不考虑并列第十的情形。按总票房从高到低排序。测试说明评测程序将执行你的语句如果在每个测试集上的执行结果均与预期结果一致即过关。参考答案-- 查询累计票房前十的导演和累计票房。列出:导演的IMDB码,姓名和累计票房(gross_total),不考虑并列第十的情形。按总票房从高到低排序。 -- 请用一条SQL语句实现该查询 SELECT c.imdb_nm, c.cname, SUM(f.gross) AS gross_total FROM film f JOIN celeb c ON f.did c.cid GROUP BY c.cid, c.imdb_nm, c.cname ORDER BY gross_total DESC LIMIT 10; /* end of your code */第11关执导过两部电影及以上的导演最高评分和最低评分任务描述查询执导过两部电影及以上的导演最高评分和最低评分。相关知识为了完成本关任务你需要掌握SELECT语句的用法.编程要求请用一条SQL语句完成以下查询任务查询执导过两部电影及以上的导演最高评分和最低评分,列出导演id, 姓名, 最高评分(max_rating)和最低评分(min_rating),依导演id排序。测试说明评测程序将执行你的语句如果在每个测试集上的执行结果均与预期结果一致即过关。参考答案-- 查询执导过两部电影及以上的导演最高评分和最低评分,列出导演id, 姓名, 最高评分(max_rating)和最低评分(min_rating),依导演id排序。 -- 请用一条SQL语句实现该查询 SELECT c.cid AS did, c.cname, ROUND(MAX(f.rating), 2) AS max_rating, ROUND(MIN(f.rating), 2) AS min_rating FROM film f JOIN celeb c ON f.did c.cid GROUP BY c.cid, c.cname HAVING COUNT(*) 2 ORDER BY c.cid; /* end of your code */第12关参演了“冯小刚”导演的所有电影的演员任务描述查询参演了“冯小刚”导演的所有电影的演员。相关知识为完成本任务你需要掌握以下知识SELECT语句.编程要求请用一条SQL语句完成以下查询任务查询参演了“冯小刚”导演的所有电影的演员,列出:演员id, IMDB编码, 姓名, 性别, 出生日期和出生地。依演员id排序。测试说明评测程序将执行你的语句如果在每个测试集上的执行结果均与预期结果一致即过关。参考答案-- 查询参演了“冯小刚”导演的所有电影的演员,列出:演员id, IMDB编码, 姓名, 性别, 出生日期和出生地。依演员id排序。 -- 请用一条SQL语句实现该查询 SELECT c.cid,c.imdb_nm,c.cname,c.gender,c.dob,c.place FROM celeb c JOIN starring s ON c.cid s.star_id JOIN film f ON s.film_id f.fid WHERE f.did (SELECT cid FROM celeb WHERE cname 冯小刚) GROUP BY c.cid, c.imdb_nm, c.cname, c.gender, c.dob, c.place HAVING COUNT(DISTINCT f.fid) ( SELECT COUNT(fid) FROM film WHERE did (SELECT cid FROM celeb WHERE cname 冯小刚) ) ORDER BY c.cid; /* end of your code */第13关在同一部电影中饰演两个不同角色的演员任务描述查询在同一部电影中饰演两个不同角色的演员即角色。相关知识为了完成本关任务你需要掌握SELECT语句的综合应用编程要求请用一条SQL语句完成以下查询任务查询在同一部电影中饰演两个不同角色的演员列出演员名字电影名和饰演的角色依演员id排序。测试说明评测程序将执行你的语句如果在每个测试集上的执行结果均与预期结果一致即过关。参考答案-- 查询在同一部电影中饰演两个不同角色的演员列出演员名字电影名和饰演的角色依演员id排序。 -- 请用一条SQL语句实现该查询 SELECT c.cname,f.title,s.charcter FROM celeb c JOIN starring s ON c.cid s.star_id JOIN film f ON s.film_id f.fid WHERE (s.star_id, s.film_id) IN ( SELECT star_id, film_id FROM starring GROUP BY star_id, film_id HAVING COUNT(DISTINCT charcter) 2 ) ORDER BY c.cid; /* end of your code */第14关评分第4高的电影任务描述查询评分第4高的电影。相关知识为了完成本关任务你需要掌握排序消重取第N高子查询。编程要求请用一条SQL语句实现本询要求查询评分第4高的电影及其评分,列出电影IMDB码,电影名称,评分。在评分序列9.6,9.6,9.5,9.5,9.4,9.3,9.3,9.2中两个9.6均为第1高两个9.5均为第2高,两个9.3均为为第4高。测试说明评测程序将执行你的语句如果执行结果与预期结果一致即过关。所以在评测前不要改动数据库里的数据凡任务未要求的动作一律不要做以免影响评测结果。参考答案点个赞再走吧 awa~-- 查询评分第4高的电影及其评分,列出电影IMDB码,电影名称,评分。在评分序列9.6,9.6,9.5,9.5,9.4,9.3,9.3,9.2中两个9.6均为第1高两个9.5均为第2高,两个9.3均为为第4高。 -- 请用一条SQL语句实现该查询 SELECT imdb,title,rating FROM film WHERE rating ( SELECT DISTINCT rating FROM film ORDER BY rating DESC LIMIT 3, 1 ); /* end of your code */