学习内容
来自上课老师讲的题目
具体代码
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