mysql习题04

1、比赛结果result表内容如下：
Date                     Win
2017-07-12               胜
2017-07-12               负
2017-07-15               胜
2017-07-15               负
如果要生成下列结果, 正确的sql语句是：（      ）
比赛日期            胜     负
2017-07-12          1      1
2017-07-15          1      1

A、select Date As 比赛日期, (case when Win='胜' then 1 else 0 end) 胜, (case when Win='负' then 1 else 0 end) 负 from result group by Date;

B、select Date As 比赛日期, SUM(case when Win='胜' then 1 else 0 end) 胜, SUM(case when Win='负' then 1 else 0 end) 负 from result;

C、select Date As 比赛日期, SUM( when Win='胜' then 1 else 0 end) 胜, SUM( when Win='负' then 1 else 0 end) 负 from result group by Date;

D、select Date As 比赛日期, SUM(case when Win='胜' then 1 else 0 end) 胜, SUM(case when Win='负' then 1 else 0 end) 负 from result group by Date;

2、运动会比赛信息的数据库，有如下三个表：
运动员ATHLETE（运动员编号 Ano，姓名Aname，性别Asex，所属系名 Adep）， 项目 ITEM （项目编号Ino，名称Iname，比赛地点Ilocation）， 成绩SCORE （运动员编号Ano，项目编号Ino，积分Score）。
写出目前总积分最高的系名及其积分，SQL语句实现正确的是：（      ）

A、SELECT Adep,SUM(Score)

FROM ATHLETE,SCORE

WHERE ATHLETE.Ano=SCORE.Ano

GROUP BY Adep

HAVING SUM(Score)>=ANY (

                                                  SELECT SUM(Score)

                                                  FROM ATHLETE,SCORE

                                                  WHERE ATHLETE.Ano=SCORE.Ano

                                                  GROUP BY Adep

                                                  );

B、SELECT Adep,SUM(Score)

FROM ATHLETE,SCORE

WHERE ATHLETE.Ano=SCORE.Ano

GROUP BY Adep

HAVING SUM(Score)>=SOME (

                                                        SELECT SUM(Score)

                                                        FROM ATHLETE,SCORE

                                                        WHERE ATHLETE.Ano=SCORE.Ano

                                                        GROUP BY Adep

                                                        );

C、SELECT Adep,SUM(Score)

FROM ATHLETE,SCORE

WHERE ATHLETE.Ano=SCORE.Ano

GROUP BY Adep

HAVING SUM(Score) IN (

                                        SELECT SUM(Score)

                                        FROM ATHLETE,SCORE

                                        WHERE ATHLETE.Ano=SCORE.Ano

                                        GROUP BY Adep

                                        );

D、SELECT Adep,SUM(Score)

FROM ATHLETE,SCORE

WHERE ATHLETE.Ano=SCORE.Ano

GROUP BY Adep

HAVING SUM(Score)>=ALL (

                                                SELECT SUM(Score)

                                                FROM ATHLETE,SCORE

                                                WHERE ATHLETE.Ano=SCORE.Ano

                                                GROUP BY Adep

                                                );

3、某高校采取电子化考试，便于查询学生对应考场号创建视图，已知student表（学号sid，姓名sname，考号s_test_id），room表（考场号rid，座位号rseat，考号s_test_id），下列语句错误的是（）

A、CREATE VIEW view_room (sname,s_test_id,rid,rseat) AS

SELECT student.sname, student.s_test_id, room.rid, room.rseat

FROM student,room WHERE student.s_test_id=room.s_test_id;

B、CREATE ALGORITHM={TEMPTABLE} VIEW view_room AS

SELECT student.sname, student.s_test_id, room.rid, room.rseat

FROM student,room WHERE student.s_test_id=room.s_test_id;

C、CREATE VIEW view_room AS

SELECT student.sname, student.s_test_id, room.rid, room.rseat

FROM student,room WHERE student.s_test_id=room.s_test_id;

D、CREATE VIEW view_room AS (rid,rseat,sname,s_test_id)

SELECT student.sname, student.s_test_id, room.rid, room.rseat

FROM student,room WHERE student.s_test_id=room.s_test_id;

答案：DDD

解析：

1、由题意得，需要行转列按日期分组并对列求和，因此需要group by Date， sum(...胜/负)。

A中无sum，错；

B中无group by，错；

C中case when ... then ... else ... end用法不正确，错；

选D

2、本题主要考察多行比较操作符

IN：等于列表的任意一个

ANY：需要和单行比较操作符一起使用，和子查询返回的某一个值比较

ALL：需要和单行比较操作符一起使用，和子查询返回的所有值比较

SOME：实际上是ANY的别名，作用相同，一般常使用ANY

3、所有选项都是在多表上建立视图，AD选项在视图中重命名了选取列的列名，但是D选项的列名与选取列内容不一致，后续对于视图的操作很容易引起歧义，且指定列名和AS颠倒了位置，错误；B选项增加了将视图存入临时表的操作，然后用临时表来执行语句；C选项未重命名，但是并不影响。