本次试题设计两个表：student、exam
student表
exam表
一、写一条SQL语句，按学号排序输出数学成绩
SELECT s.sno sno,score
FROM exam e,student s
WHERE e.`subject` = '数学' AND e.name = s.name
GROUP BY s.sno
二、写一条SQL语句，按学号排序，输出：学号，姓名，总分，平均分
SELECT sno,s.`name`,SUM(score) ss,AVG(score) gg
FROM exam e,student s
WHERE e.`name` = s.`name`
GROUP BY `name`
ORDER BY ss
三、写一条SQL语句，查询出数学考试成绩最高的学生学号和姓名
SELECT sno,exam.name name,score
FROM exam,student
WHERE (exam.`subject` = '数学') AND (exam.name = student.name)
ORDER BY exam.score DESC
LIMIT 0,1;
四、写一条SQL语句，查询出每门课都大于80分的学生姓名
方法一：ALL()函数
思路：查找出每个人所有科目是否都大于80
SELECT DISTINCT NAME
FROM exam a
WHERE 80 < ALL(SELECT score
FROM exam e
WHERE e.name = a.name );
方法二：not exists 字段
思路：排除掉存在学科分数低于80的学生，剩下的就是所有学科分数都大于80的学生
SELECT DISTINCT NAME
FROM exam a
WHERE NOT EXISTS (SELECT name
FROM exam e
WHERE e.name = a.name AND e.score < 80 );
方法三：not in 字段
思路：同上
SELECT DISTINCT NAME
FROM exam
WHERE NAME NOT IN (SELECT NAME
FROM exam
WHERE score<=80)
方法四：having 字段
思路：过滤出学科中最低分数大于80的学生
SELECT name
FROM exam
GROUP BY name
HAVING min(score) > 80
五、张三退学后，请写相关的SQL语句，注意严谨
DELETE
FROM exam
WHERE name = '张三';
DELETE
FROM student
WHERE name = '张三';
DELETE exam,student
FROM exam
LEFT JOIN student
ON exam.name = student.name
WHERE exam.name = '张三' ;
六、你会对该表哪些字段做索引
id - 唯一索引
七、这两个表的设计，有哪些可以改进的地方
student表中sno字段可以改成‘studentID’，更语义化，
exam表中取消name字段及该列数据，改用student中sno学号字段代替，更易于检索