期末复习之sql语句

1.将计算机学院全体学生的成绩置零

update sc 
set grade=0 
where sno in(
select sno 
from student
where sdept="计算机学院"
)

2.查询学生及其选修课的情况

select student.*,cno,grade 
from student,sc
where student.sno=sc.sno(+) 

3.查询平均成绩高于或等于’ 1’号课程最高成绩的学生情况 。

select student.* 
from student
where sno in
(
select sno 
from sc
group by sno 
having avg(grade)>=( 
select max(grade)
from sc 
where cno="1"
)
)

4.建立计算机学院选修了1号课程且成绩在90分以上的学生的视图

create view stu as
select student.*
from student,sc

where 
student.sno=sc.sno

and cno="1"

and grade>=90

5.成绩平均分超过85的课程,并按平均分升序排列

select cno,avg(grade)
from sc
group by cno asc
having
avg(grade)>=85

6.查询其他学院中比计算机学院某一学生年龄小的学生姓名和年龄

select sname,sage
from student
where sdept <>"计算机学院"
and sage < any(
select sage from student 
where sdept ="计算机学院"
)

7.查看成绩高于85分的学生信息及成绩情况

select student.* ,sc.*
from student,sc
where student.sno=sc.sno
and grade>=85

8.查询每一门课的间接先修课（即先修课的先修课）

select first.cno,second.cpno
from course first,course second
where first.cpno=second.cno