数据库第二章 参考答案
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' );
