数据库复习1
一. 简答题（共1题，100分）
1. (简答题)
存在数据库test，数据库中有如下表：
1.学生表
Student(Sno,Sname,Sage,Ssex) --Sno 学号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
主键Sno
2.教师表
Teacher(Tno,Tname) --Tno 教师编号,Tname 教师姓名
主键Tno
3.课程表
Course(Cno,Cname,Tno) --Cno --课程编号,Cname 课程名称,Tno 教师编号
主键Cno 外键Tno参照教师表
4.成绩表
SC(Sno,Cno,score) --Sno 学生编号,Cno 课程编号,score 分数
主键（Sno,Cno） 外键Sno,Cno分别参照学生表和课程表
按要求写出如下SQL语句：
1、查询学生学号、姓名、总成绩并按总成绩从大到小排名（要考虑没有选课的同学，总成绩显示0）
2、查询课程名称为"数据库"，且分数低于60的学生姓名和分数
3、查询同名学生名单，并统计同名人姓名与人数
4、检索"1"课程分数小于60，按分数降序排列的学生所有信息
5、查询每门课程（已选课）的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
6、查询平均成绩大于等于60的所有学生的学号、姓名和平均成绩，并按平时成绩降序。
7、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩（要考虑没有选课的同学，没有选课的同学平均成绩显示0）
我的答案：
１．
SELECT ｓ.Sno, ｓ.Sname, COALESCE(SUM(ｃ.score), 0) total
FROM Studentｓ
LEFT JOIN SC ｃON ｓ.Sno = ｃ.Sno
GROUP BY ｓ.Sno, ｓ.Sname
ORDER BY total DESC
（这里的COALESCE函数和ISNULL函数用法一致）
２．
SELECT s.Sname ,c.score
FROM Student s ,SC c,Course co
WHERE s.Sno =c.Sno AND c.Cno = co.Cno AND co.Cname = '数据库' AND c.score <60
3 .
SELECT Sname, COUNT(Sno)
FROM Student
GROUP BY Sname
WHERECOUNT (Sno)>1
4 .
SELECT S.*
FROM Studnet s,SC c.Course co
WHERE s.Sno=c.Sno AND c.Cno=co.Cno AND co.Cname ='1' AND c。score<60
ORDER BY c.score DESC
5 .
SELECT co.Cno,co.Cname,AVG(c.score) avg_score
FROM Coures co,SC c
WHERE co.Cno =c.Cno
GROUP BY co.Cno,co.Cname
ORDER BY avg_score DESC,co.Cno ASC
6 .
SELECT s.Sno,s.Sname,AVG(c.score) avg_score
FROM student s,SC c
WHERE s.Sno=c.Sno
GROUP BY s.Sno,s.Sname
HAVING avg_score>=60
ORDER BY avg_score DESC
7 .
SELECT s.Sno,s.Sname,COALESCE(AVG(c.score),0) avg_score
FROM student s
LEFT JOIN SC c ON s.Sno=c.Sno
GROUP BY s.Sno,s.Sname
WHERE avg_score <60
标准参考答案
1、SELECT student.sno,student.Sname, IFNULL(sum(sc.score),0)
from student LEFT OUTER JOIN sc ON
student.Sno=sc.Sno
GROUP BY student.Sno
ORDER BY SUM(sc.score) DESC;
2、 SELECT sname,score from student,course,sc
where student.sno=sc.sno and sc.cno=course.Cno and cname='数据库' and score<60;
3、SELECT sname,count(*) from student
GROUP BY Sname
HAVING count(*)>1;
4、 SELECT student.*,sc.Cno,sc.score from student,sc
where student.sno=sc.Sno and sc.cno='1' and sc.score<60
ORDER BY sc.score DESC;
5、SELECT course.cno,course.Cname,avg(sc.score)
FROM course,sc
WHERE course.Cno=sc.Cno
GROUP BY course.cno
ORDER BY avg(sc.score) desc ,course.cno;
6、SELECT student.sno,student.Sname,avg(sc.score)
FROM student,sc
WHERE student.sno=sc.sno
GROUP BY student.sno
HAVING avg(sc.score)>=60
ORDER BY avg(sc.score) DESC;
7、SELECT student.sno,student.Sname,IFNULL(avg(sc.score),0)
FROM student LEFT OUTER JOIN sc
ON student.sno=sc.sno
GROUP BY student.sno
HAVING IFNULL(avg(sc.score),0)<60
ORDER BY avg(sc.score) DESC;