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

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='操作系统' )

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库SQL实验二：数据库查询实验报告(2)在线全文阅读。