看看我以前作过的:
student(sno,sname,sex,age)
course(cno,cname,credit,teachno)
stu_cors(id,stuno,corsno,score)
teacher(tno,tname)
--一检索学习jsp这门课程的学生,列出学生的名字
select student.sname
from student
where sno in(select stuno from stu_cors where corsno =(select cno from course where cname='jsp'))
go
select student.sno
from student inner join stu_cors on student.sno=stu_cors.stuno
inner join course on corsno=course.cno
where course.cname='jsp'
select sname
from student
where sno in(select stu_cors.stuno
from stu_cors inner join course on stu_cors.corsno=course.cno where course.cname='jsp')
--二检索java课程有多少学生学习.
select COUNT(stuno) as 'JAVA学习人数'
from stu_cors
where corsno =(select cno from course where cname='java')
--三检索各科课程各有多少学生学习,
select course.cname,COUNT(stu_cors.stuno) as '学习人数'
from stu_cors inner join course on stu_cors.corsno=course.cno
group by course.cname
--四检索学习了jsp和servlet两门课程的学生有多少
select COUNT(sno) as 'jsp和servlet课程的学生人数'
from student
where sno in(select stu_cors.stuno
from stu_cors inner join course on stu_cors.corsno=course.cno
where course.cname='jsp') and
sno in(select stu_cors.stuno
from stu_cors inner join course on stu_cors.corsno=course.cno
where course.cname='jsp')
--五检索学习了超过两门课程的学生有多少
select corsno,COUNT(stuno) as '学习超过两门课程的学生人数'
from stu_cors
group by corsno having COUNT(stuno)
温馨提示:答案为网友推荐,仅供参考