2024年3月6日发(作者：)
1、 查询选c1课程并及格的学生总人数及最高分、最低分。
select count(*),max(grade),min(grade) from enrolls
where cno='c1' and grade>60
2、 按学号求每个学生所选课程的平均成绩超过88分以上的学生。
select sno,avg(grade) as avg_grade from enrolls
group by sno having avg(grade)>88
3、 找出选课学生超过2人的课程的平均成绩及选课人数。
select cno,avg(grade),count(*) as st_number from enrolls
group by cno having count(*)>2
4、 查询student中男生的每一年龄组（超过并包含2人）有多少，要求查询结果按人数升序排列，人数相同时按年龄降序排列。
select age,count(sno) as number from student
where sex='男'
group by age having count(sno)>=2
order by number,age desc
5、 查询与张春明在同一个系学习的学生。
select ,,ment from student s1,student s2
where ment=ment and ='张春明'
select sno,sname,department from student
where department=(select department from student where sname='张春明')
6、查询其他系中比计算机系中所有学生年龄都大的学生姓名及年龄。此查询用any或all谓词实现，或用集函数两种方法。
select sname,age from student
where age>all(select age from student where department='计算机')
and department<>'计算机'
select sname,age from student
where age>all(select max(age) from student where department='计算机')
and department<>'计算机'
7、查询选修了所有课程的学生姓名。
select sname from student
where not exists
(select * from courses
where not exists
(select * from enrolls
where sno= and cno=))
8、查询与95022同学选修课程一样的同学的学号与姓名。
select sno,sname from student
where sno in
(select distinct sno from enrolls e1 where not exists
(select * from enrolls e2
where ='95022' and not exists
(select * from enrolls e3
where = and =)))
9、查询选课门数唯一的学生的学号。
select sno,sname from student
where sno in
(select sno from enrolls e1 where sno not in
(select sno from enrolls
where cno<>))
10、查询没有选修c3课程的学生姓名。
select sno,sname from student
where sno
not in
(select sno from enrolls
where cno='c3')
11、查询至少选修两门课程的学生姓名。
select sno,sname from student
where sno in
(select distinct from enrolls e1,enrolls e2
where = and <>)
12、查询丁同学不选课程的课程号。
select cno,cname from courses where not exists
(select * from enrolls,student
where = and = and sname like '丁%')
13、查询全部学生都选修的课程的课程号和课程名。
select cno,cname from courses where not exists
(select * from student where not exists
(select * from enrolls where = and = ))
14、查询至少选修课程号为C2和C4的学生学号。
select from enrolls e1,enrolls e2
where = and ='c2' and ='c4'
15、查询比95022同学成绩高的学生的学号和姓名。
select sno,sname from student where sno in
(select from enrolls e1,enrolls e2 where > and ='95012' )
发布评论