SQL实验指导答案.doc

/*1*/select student.sno 学号,sn 姓名,cn 课程, score 成绩 from student,sc,course where score80 and score90 and student.sno=sc.sno and sc.cno=course.cno /*2*/select cn 课程 from sc,course where sc.cno=course.cno group by course.cn having count(*)=4 /*3*/select sn 姓名,age 年龄,dept 系别from student where ageany ( select age from student where dept=信息 ) and dept信息 order by age desc /*4*/select sno 学号, sn 姓名,dept 系别from studentn where age=any ( select age from student where sn=张建国 ) and sn张建国 /*5*/select sn 姓名from student where sno=any ( select sno from sc group by sno having count(*)=2 ) select sn 姓名from student,sc where student.sno=sc.sno group by sc.sno,sn having count(*)=2 select sn 姓名from student where sno in ( select sc1.sno from sc sc1,sc sc2 where sc1.cnosc2.cno and sc1.sno=sc2.sno ) /*6*/select distinct student.sno 学号, sn 姓名,dept 系别from student,sc where student.sno=sc.sno and cno in ( select cno from sc,student where student.sno=sc.sno and sn=张建国 ) /*7*/ select * from sc sc1 where score (select avg(score) from sc sc2 where sc1.cno=sc2.cno group by sc2.cno) /*8*/ select student.sn,cn,score from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and sc.sno in (select sc1.sno from sc sc1,sc sc2 where sc1.cno=01001and sc2.cno=01002and sc1.sno=sc2.sno and sc1.scoresc2.score) /*9*/ select sn from student where sno not in(select sno from sc where cno=01001) select sn from student where not exists (select sno from sc where student.sno=sc.sno and cno=01001) /*10*/ select sc.sno,avg(score),max(score) from student,sc group by sc.sno order by avg(score) desc,max(score)desc /*11*/ select cn 课程名,sc.cno 课程号 from sc,course where course.cno=sc.cno group by sc.cno,cn having count(*)=any(select count(*) from student ) /*12*/ select distinct student.sno 学号,sn 姓名 from student,sc where cno in (select cno from sc where sno=991102) and student.sno=sc.sno and student.sno991102