SQL试题（二）

1.大的国家

这里有张 World 表

namecontinentareapopulationgdpAfghanistanAsia6522302550010020343000AlbaniaEurope28748283174112960000AlgeriaAfrica238174137100000188681000AndorraEurope468781153712000AngolaAfrica124670020609294100990000

如果一个国家的面积超过 300 万平方公里，或者人口超过 2500 万，那么这个国家就是大国家。 编写一个 SQL 查询，输出表中所有大国家的名称、人口和面积。 例如，根据上表，我们应该输出:

namepopulationareaAfghanistan25500100652230Algeria371000002381741

答案

select name, population, area from World where area >3000000 or population>25000000

2.超过5名学生的课

有一个courses 表 ，有: student (学生) 和 class (课程)。

请列出所有超过或等于5名学生的课。

例如，表：

studentclassAMathBEnglishCMathDBiologyEMathFComputerGMathHMathIMath

应该输出:

classMath

答案

大神作业

#共三种写法 #最朴实的写法，共三层查询，先利用 DISTINCT 去掉重复记录得到表 A，再利用 GROUP BY 为 CLASS 分组，然 #后用 COUNT() 统计每组个数得到表 B，最后在最外层限定数量 >=5 查到结果 SELECT B.CLASS #最外层 FROM (SELECT A.CLASS,COUNT(A.CLASS) C #第二层查询，得到具有 CLASS、COUNT(CLASS) 的表 B FROM (SELECT DISTINCT * #第三层查询，去重得到表 A FROM COURSES) A GROUP BY A.CLASS) B #分组 WHERE B.C >= 5; #条件 #稍微优化，两层查询，主要是因为用了 HAVING 省了一层查询 SELECT A.CLASS #最外层 FROM (SELECT DISTINCT * #第二层查询，去重得到表 a FROM COURSES) A GROUP BY A.CLASS #分组 HAVING COUNT(A.CLASS) >= 5; #利用 COUNT() 计算每组个数并筛选 #极致优化，一层查询，利用 GROUP BY 为 CLASS 分组后，直接用 COUNT() 统计每组学生个数，在统计前先用 #DISTINCT 去掉重复学生 SELECT CLASS FROM COURSES GROUP BY CLASS #分组 HAVING COUNT(DISTINCT STUDENT) >= 5; #利用 COUNT() 统计每门课 STUDENT 的个数，同时利 #用 DISTINCT 去掉重复学生

3.体育馆的人流量

表：Stadium

Column NameTypeidintvisit_datedatepeopleint

visit_date 是表的主键 每日人流量信息被记录在这三列信息中：序号 (id)、日期 (visit_date)、 人流量 (people) 每天只有一行记录，日期随着 id 的增加而增加 编写一个 SQL 查询以找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。 返回按 visit_date 升序排列的结果表。

查询结果格式如下所示。 Stadium table:

idvisit_datepeople12017-01-011022017-01-0210932017-01-0315042017-01-049952017-01-0514562017-01-06145572017-01-0719982017-01-09188

Result table:

idvisit_datepeople52017-01-0514562017-01-06145572017-01-0719982017-01-09188

id 为 5、6、7、8 的四行 id 连续，并且每行都有 >= 100 的人数记录。 请注意，即使第 7 行和第 8 行的 visit_date 不是连续的，输出也应当包含第 8 行，因为我们只需要考虑 id 连续的记录。 不输出 id 为 2 和 3 的行，因为至少需要三条 id 连续的记录。

答案

select DISTINCT s1.id,s1. visit_date,s1.people from Stadium s1, Stadium s2, Stadium s3 where (s1.people>=100 and s2.people>=100 and s3.people>=100) and ( (s1.id = s2.id-1 and s2.id = s3.id -1) or (s1.id = s2.id-1 and s1.id = s3.id +1) or (s1.id = s2.id+1 and s2.id = s3.id +1) )order by s1.id

4.有趣的电影

某城市开了一家新的电影院，吸引了很多人过来看电影。该电影院特别注意用户体验，专门有个 LED显示板做电影推荐，上面公布着影评和相关电影描述。

作为该电影院的信息部主管，您需要编写一个 SQL查询，找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片，结果请按等级 rating 排列。

例如，下表 cinema:

idmoviedescriptionrating1Wargreat 3D8.92Sciencefiction8.53irishboring6.24Ice songFantacy8.65House cardInteresting9.1

对于上面的例子，则正确的输出是为：

idmoviedescriptionrating5House cardInteresting9.11Wargreat 3D8.9

答案

SELECT id, movie, description, rating FROM cinema WHERE id & 1 AND description <> 'boring' ORDER BY rating DESC

5. 换座位

小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。

其中纵列的 id 是连续递增的

小美想改变相邻俩学生的座位。

你能不能帮她写一个 SQL query 来输出小美想要的结果呢？

示例：

idstudent1Abbot2Doris3Emerson4Green5Jeames

假如数据输入的是上表，则输出结果如下：

idstudent1Doris2Abbot3Green4Emerson5Jeames

注意：

如果学生人数是奇数，则不需要改变最后一个同学的座位。

答案

SELECT (CASE WHEN MOD(id,2) = 1 AND id = (SELECT COUNT(*) FROM seat) THEN id WHEN MOD(id,2) = 1 THEN id+1 ElSE id-1 END) AS id, student FROM seat ORDER BY id;