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