Key
1)select b1.sno
from (select sno, cno, score from sc where cno =‘001') b1,
(select sno, cno, score from sc where cno =‘002') b2
where b1.sno = b2.sno
and b1.score > b2.score
2)select sno,avg(score)
from sc
group by sno
having avg(score)>60
3)select s.sno,max(s.sname),count(c.cname),nvl(sum(score),0)
from student s,course c,sc
where s.sno=sc.sno(+)
and sc.cno=c.cno
group by s.sno
4)select count(tname)
from teacher
where tname like’li%'
5)select sno, sname
from student
where sno not in (select sno
from course c, teacher t
where c.tno = t.tno
and t.tname =‘yepin')
6)select sno, sname
from student
where sno in (select sno
from sc
where cno =‘001'
intersect
select sno from sc where cno =‘002')
7)select sno, sname
from student
where sno in (select sno
from sc, course, teacher
where sc.cno = course.cno
and teacher.tno = course.tno
and teacher.tname =‘yepin'
group by sno
having count(sc.cno) = (select count(cno)
from course, teacher
where teacher.tno = course.tno
评论2