SQL实验指导答案.doc

/*1*/select student.sno 学号,sn 姓名,cn 课程, score 成绩
from student,sc,course
where score>80 and score<90 and student.sno=sc.sno and o=o

/*2*/select cn 课程
from sc,course
where o=o
group by
having count(*)>=4

/*3*/select sn 姓名,age 年龄,dept 系别from student where age>any
(
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 o<>o 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 o=o group by o)

/*8*/
select student.sn,cn,score from student,sc,course where student.sno=sc.sno and o=o and sc.sno in
(select sc1.sno from sc sc1,sc sc2 where o='01001'and o='01002'and sc1.sno=sc2.sno
and sc1.score>sc2.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 课程名,o 课程号
from sc,course where o=o
group by o,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.sno<>'991102'