数据库笔记（三）

学习内容

---

来自上课老师讲的题目

---

具体代码

1.查询每人的成绩(学号、课程号、成绩)和所有成绩平均分；

select * , ( select AVG(DEGREE) from SCORE ) av 
from SCORE

---

2.查询每人的成绩(学号、课程号、成绩)和本课程平均分；

select * , (select AVG(DEGREE) from SCORE where cno = sc.cno) av
from SCORE sc

---

3.查询每人的成绩(学号、姓名,课程名、成绩)和本班总平均分；

select s.sno, sname, cname, degree,
( select AVG(DEGREE) from STUDENT s1 join SCORE sc1 on s1.sno = sc1.sno where s1.class = s.class ) av
from STUDENT s join SCORE sc on s.sno = sc.sno
join COURSE c on c.cno = sc.cno

---

4.查询每人的成绩(学号、姓名,课程名、成绩)和本班本科平均分；

select s.sno, sname, cname, degree,
( select AVG(DEGREE) from STUDENT s1 join SCORE sc1 on s1.sno = sc1.sno where s1.class = s.class and sc1.cno = sc.cno ) av
from STUDENT s join SCORE sc on s.sno = sc.sno
join COURSE c on c.cno = sc.cno 

---

5.查询成绩高于学号为“101”的课程号为“3-105”的成绩的所有记录。

select * from SCORE 
where degree > ( select degree from SCORE where cno = '3-105' and sno = '101' )

---

6.查询和学号为101的同学同月出生的所有学生的Sno、Sname和Sbirthday列。

select sno, sname, sbirthday from STUDENT
where MONTH(SBIRTHDAY) = ( select MONTH(SBIRTHDAY) from STUDENT where sno = '101' )

---

7.查询“张旭“教师任课的学生成绩(学号、姓名,课程名、成绩)。

select s.sno, sname, cname, degree from STUDENT s 
join SCORE sc on s.sno = sc.sno 
join COURSE c on c.cno = sc.cno
where tno = ( select tno from TEACHER where tname = '张旭' )

---

8.查询每科的最高分信息(学号、姓名,课程名、成绩)

select s.sno, sname, cname, degree from STUDENT s 
join SCORE sc on s.sno = sc.sno
join COURSE c on c.cno = sc.cno
where degree >= all( select degree from SCORE where cno = c.cno )
--where degree = ( select MAX(DEGREE) from SCORE where cno = c.cno )

---

9.查询有成绩不及格的同学的学号,姓名。

select sno, sname from STUDENT where sno in ( select sno from SCORE where degree<60 )
--select sno, sname from STUDENT s where 60> any( select sno from SCORE where sno = s.sno )
--select sno, sname from STUDENT s where exists ( select sno from SCORE where degree<70 and sno = s.sno )

---

10. 查询选修两门及两门以上课程的学生学号及姓名,课程名,成绩,并保存到’SC’表中。

select s.sno, sname, cname, degree from STUDENT s 
join SCORE sc on s.sno = sc.sno
join COURSE c on c.cno = sc.cno 
where ( select COUNT(*) from SCORE where sno = s.sno )>1

---