SQL实验二：数据库查询实验报告

--（11）求选修了学生“”所选修的全部课程的学生学号和姓名。 select s.sno,sname from student s,sc where sc.cno in ( select cno from sc

where sno='95001' )and s.sno=sc.sno group by s.sno,sname

having count(cno)=(select count(cno)

--（12）查询每一门课的间接先修课。 select c1.cno,c2.cpno from course c1,course c2 where c1.cpno = c2.cno

--（13）列出所有学生所有可能的选课情况。 select s.sno,s.sname,c.cno,c.cname from student s cross join course c

--（14）列出每个学生的学号及选修课程号，没有选修的学生的学号也一并列出。 select s.sno,sc.cno

from student s left outer join sc on s.sno = sc.sno

--（15）输出与“张三”同性别并位于同一个系的所有同学的姓名。（请至少写出两种查询语句） --法一

select sname from student where sdept in (

select sdept from student

where sname = '张三' and ssex =(select ssex from student where sname=' from sc

where sno='95001') and s.sno!='95001'

张三') )

group by sname having sname!='张三' --法二

select sname from student where sdept = (

select sdept from student

where sname = '张三' and ssex =(select ssex from student where sname='

张三') )

group by sname having sname!='张三'

--（16）查询至少被两名男生选修的课程名。 select cname

from course c,student s,sc

where ssex='男' and c.cno=sc.cno and s.sno=sc.sno group by cname having count(*)>=2

--（17）对被两名以上学生所选修的课程统计每门课的选课人数。

-- 要求输出课程号和选修人数，查询结果按人数降序排列；若人数相同，按课程号升序排列。

select cno,count(*) as '选修人数' from sc group by cno having count(*)>2

order by '选修人数'desc,cno asc

--（18）列出选修课程超过门的学生姓名及选修门数。 select sname,count(*) as '选修门数' from student s,sc where s.sno=sc.sno group by s.sno,sname having count(*)>3

--（19）检索至少选修课程号为和的学生姓名。 select sname from student where sno in ( )

--（20）检索至少选修课程“数学”和“操作系统”的学生学号。 select sc.sno from course c,sc

select s1.sno from sc s1,sc s2

where s1.cno='1' and s2.cno='3' and s1.sno=s2.sno

where c.cname = '数学' and c.cno = sc.cno and sno in

(

select sc.sno from sc ,course c

where c.cname = '操作系统' and c.cno = sc.cno )

--（21）查询?操作系统?课程的最高分的学生的姓名、性别、所在系 select sname,ssex,sdept from student s,sc

where s.sno=sc.sno and grade=

--（22）查询数据结构的成绩低于操作系统的成绩的学生姓名及该生的这两门课的成绩 select s1.sname,sc1.grade as '操作系统成绩',sc2.grade as '数据结构成绩' from course c1,course c2,sc sc1,sc sc2,student s1,student s2 where c1.cname='操作系统' and c2.cname='数据结构' and sc1.grade>sc2.grade

--（23）所有成绩都在分以上的学生姓名及所在系。 select sname,sdept from student s,sc where s.sno=sc.sno group by sname,sdept having min(grade)>=70

and sc1.sno=sc2.sno

and c1.cno=sc1.cno and c2.cno=sc2.cno and s1.sno=sc1.sno and s2.sno=sc2.sno

(

select max(grade) from course c,sc

where sc.cno=c.cno and cname='操作系统' )