数据库复习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;