数据库复习3
一. 简答题（共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、查询选了3门课程的同学信息
2、查询没有学全所有课程的同学的信息（要显示没有选课的同学信息）
3、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
4、查询选修"吴大"老师所授课程的学生中，成绩最高的学生信息及其成绩
当最高分只有一个时：
5、创建一个选了2门课程的学生的学号、姓名及平均成绩的视图V1
6、查询选了2门课程且平均成绩在60分以下的学生的姓名与平均成绩
我的答案
1.
select s.*
from student s
inner join (
select Sno
from SC
group by SC.Sno
having count(Sno) =3
) c on s.Sno=c.Sno
2.
select s.*
from student s,SC c
where s.Sno=c.Sno
group by c.Sno
having count(c.Sno) < (select count(*) from course)
3.
select s.Sno,s.Sname,AVG(c.score)
from Student s
inner join SC c on s.Sno = c.Sno
where c.score<60
group by s.Sno,s.Sname
having count(* ) >= 2
4.
select s.*
from student s
join SC c on s.Sno = c.Sno
join Course co on c.Cno = co.Cno
join Teacher t on co.Tno = t.Tno
where t.Tname = '吴大'
order by c.score desc
limit 1
5.
create view V1
as
select s.Sno，s.Sname,avg_score
from student s
inner join (
select Sno ,AVG(SC.score) avg_score
from SC
group by SC.Sno
having count(Sno) =2
) c on s.Sno=c.Sno
6.
查询选了2门课程且平均成绩在60分以下的学生的姓名与平均成绩
select s.Sname,avg_score
from student s
inner join (
select Sno,AVG(score) avg_score
from SC
where count(Sno) = 2
group by Sno
having AVG(score)<60
)c on s.Sno=c.Sno
这里也可以使用第五题中创建的视图
select s.Sname,avg_score
from V1
where avg_score<60
标准参考答案
1、select student.*
from student,sc
where student.sno=sc.Sno
GROUP BY Sno
HAVING count(sc.Cno)=2;
2、select student.*
from student LEFT OUTER JOIN sc
ON student.sno=sc.Sno
GROUP BY Sno
HAVING count(sc.Cno)<(SELECT count(*) from course);
3、SELECT student.sno,student.Sname,avg(sc.score)
FROM student,sc
WHERE student.sno=sc.Sno and student.sno in (
SELECT sno FROM sc where score<60
GROUP BY Sno
HAVING count(*)>=2)
GROUP BY student.Sno;
4、SELECT student.*,sc.score
FROM student,sc,course,teacher
WHERE teacher.Tno=course.Tno AND course.Cno=sc.Cno AND sc.Sno=student.Sno
AND teacher.Tname='吴大' ORDER BY sc.score DESC
LIMIT 0,1;
当最高分出现多个时：
SELECT student.*,sc.score
FROM student,sc,course,teacher
WHERE teacher.Tno=course.Tno AND course.Cno=sc.Cno AND sc.Sno=student.Sno
AND teacher.Tname='吴大'
AND sc.score=(SELECT MAX(sc.score)
FROM sc,course,teacher
WHERE teacher.Tno=course.Tno AND course.Cno=sc.Cno AND teacher.Tname='吴大');
5、
CREATE VIEW v1(sno,sname,ascore)
AS
SELECT student.Sno, student.Sname,AVG(sc.score)
from student,sc
where student.Sno=sc.Sno
GROUP BY student.Sno
HAVING count(*)=2;
6、SELECT sname ,ascore from v1
where ascore<60;