数据库上机实验3

--查询选了“C001”课程的学生姓名和所在系。
select Sname,Sdept from Student where Sno In(
select Sno from SC where Cno = 'C001');

--查询通信工程系成绩80分以上的学生的学号和姓名。

select Sno,Sname from Student where Sdept = '通信工程系' and Sno IN(
select Sno from SC where Grade > 80);

--查询计算机系考试成绩最高的学生的姓名。

Select Sname from student Where Sdept='计算机系' AND Sno IN(select Sno from SC
Where Grade IN(select MAX(Grade) from SC));

--查询年龄最大的男生的姓名、所在系和年龄
select Sname,Sdept,Sage from Student where Sage IN 
(Select MAX(Sage) from student) AND Ssex = '男';

--查询C001课程的考试成绩高于该课程平均成绩的学生的学号和成绩。
select Sno,Grade from SC where Grade >
(select AVG(Grade) from SC where Cno = 'C001') AND Cno = 'C001'; 

--查询计算机系学生考试成绩高于计算机系学生平均成绩的学生的姓名、考试的课程名和考试成绩。
select Sname,Cname,Grade from Student,SC,Course 
where Student.Sno = SC.Sno AND SC.Cno = Course.Cno 
AND Grade > (select AVG(Grade) from SC 
             where Sno IN (select S