# 学习内容

来自上课老师讲的题目


# 具体代码

## 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


posted @ 2020-04-22 17:49  Hyjjing  阅读(167)  评论(0编辑  收藏  举报