数据库第二章 参考答案

1、

/*student表数据*/

insert into `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) values('10000','123','郭靖','男','1','13645667783','天津市河西区','1990-09-08 00:00:00',NULL,NULL);
insert into `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) values('10001','123','李文才','男','1','13645667890','地址不详','1994-04-12 00:00:00',NULL,NULL);
insert into `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) values('10002','123','李斯文','男','1','13645556793','河南洛阳','1993-07-23 00:00:00',NULL,NULL);
insert into `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) values('10003','123','张萍','女','1','13642345112','地址不详','1995-06-10 00:00:00',NULL,NULL);
insert into `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) values('10004','123','韩秋洁','女','1','13812344566','北京市海淀区','1995-07-15 00:00:00',NULL,NULL);
insert into `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) values('10005','123','张秋丽','女','1','13567893246','北京市东城区','1994-01-17 00:00:00',NULL,NULL);
insert into `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) values('10006','123','肖梅','女','1','13563456721','河北省石家庄市','1991-02-17 00:00:00',NULL,NULL);
insert into `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) values('10007','123','秦洋','男','1','13056434411','上海市卢湾区','1992-04-18 00:00:00',NULL,NULL);
insert into `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) values('10008','123','何睛睛','女','1','13053445221','广州市天河区','1997-07-23 00:00:00',NULL,NULL);
insert into `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) values('20000','123','王宝宝','男','2','15076552323','地址不详','1996-06-05 00:00:00',NULL,NULL);
insert into `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) values('20010','123','何小华','女','2','13318877954','地址不详','1995-09-10 00:00:00',NULL,NULL);
insert into `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) values('30011','123','陈志强','男','3','13689965430','地址不详','1994-09-27 00:00:00',NULL,NULL);
insert into `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) values('30012','123','李露露','女','3','13685678854','地址不详','1992-09-27 00:00:00',NULL,NULL);

/*result表数据*/


insert into `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) values('10000','1','2016-02-15 00:00:00','71');
insert into `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) values('10000','1','2016-02-17 00:00:00','60');
insert into `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) values('10001','1','2016-02-17 00:00:00','46');
insert into `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) values('10002','1','2016-02-17 00:00:00','83');
insert into `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) values('10003','1','2016-02-17 00:00:00','60');
insert into `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) values('10004','1','2016-02-17 00:00:00','60');
insert into `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) values('10005','1','2016-02-17 00:00:00','95');
insert into `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) values('10006','1','2016-02-17 00:00:00','93');
insert into `result` (`studentNo`, `subjectNo`, `examDate`, `studentResult`) values('10007','1','2016-02-17 00:00:00','23');

2、

#将学生表中学号为20000的学生的邮箱修改为stu20000@163.com，
密码改为000
UPDATE `student` SET `email` = 'stu20000@163.com',`loginPwd` = '000'
 WHERE `studentName` = '20000';

#将数据表科目表中课时数大于200且年级编号为1的科目的课时减少10
UPDATE `subject` SET `classHour`=classHour-9 
WHERE `classHour`>200 AND `gradeID` = 1

subject

3、

#查询2016年2月17日考试前5名的学员的学号和分数
SELECT studentNo,studentResult FROM result
WHERE examDate='2016-02-17'
ORDER BY studentResult DESC
LIMIT 5

#将所有女学生按年龄从大到小排序，从第2条记录开始显示6名女学生的姓名、年龄、出生日期、手机号信息
SELECT studentname,phone,borndate,address FROM student
 WHERE sex = '女' ORDER BY borndate LIMIT 2,6;

#按出生年份分组统计学生人数，将各组中人数达到2人的年份和人数显示出来
SELECT YEAR(borndate) AS 出生年份,COUNT(studentNo) AS 人数 
FROM student GROUP BY YEAR(borndate) HAVING 人数>=2;

#查询参加2016年2月17日考试的所有学员的最高分、最低分、平均分。
SELECT MAX(studentResult) AS 最高分,MIN(studentResult) AS 最低分,
AVG(studentResult) AS 平均分 
FROM result
 WHERE examDate = '2016-2-17';

4、

SELECT MAX(`studentResult`)  AS 最高分,
               MIN(`studentResult`) AS 最低分
FROM `result`
WHERE `subjectNo`=
    (SELECT `subjectNo` FROM `subject` 
      WHERE `subjectName`='Logic Java' ) AND `examDate`=(SELECT MAX(`examDate`) FROM `result` 
      WHERE `subjectNo`=(SELECT `subjectNo` FROM `subject` WHERE `subjectName`='Logic Java' ) );

5、

SELECT `subjectName` FROM `subject` WHERE `gradeId` IN (
       SELECT `gradeId` FROM `grade` WHERE `gradeName`='S1'
);

8、

SELECT `studentName` FROM `student` 
WHERE `studentNo` NOT IN (
      SELECT `studentNo` FROM `result` 
      WHERE `subjectNo` = (
          SELECT `subjectNo` FROM `subject` 
          WHERE `subjectName`='HTML'
       )
      AND `examDate` = (
          SELECT MAX(`examDate`) FROM `result` 
          WHERE `subjectNo` =(
              SELECT `subjectNo` FROM `subject` 
              WHERE `subjectName`='HTML' ) 
       )
)
AND `gradeId` = (
      SELECT `subjectNo` FROM `subject` 
      WHERE `subjectName`='HTML'
);