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 s1.sno,s1.sname,s1.department from student s1,student s2
  where s1.department=s2.department and s2.sname='张春明'
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=student.sno and cno=courseso))
8、查询与95022同学选修课程一样的同学的学号与姓名。
select sno,sname  from student
  where sno in
    (select distinct sno from enrolls e1 where not exists
        (select * from enrolls e2
      where e2.sno='95022' and not exists
        (select * from enrolls e3
          where e1.sno=e2.sno and e2o=e3o)))
sql语句查询不包含9、查询选课门数唯一的学生的学号。
select sno,sname  from student
  where sno in
    (select  sno from enrolls e1 where sno not in
    (select sno from enrolls
          where cno<>e1o))
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 e1.sno from enrolls e1,enrolls e2
      where e1.sno=e2.sno and e1o<>e2o)
12、查询丁同学不选课程的课程号。
select cno,cname from courses where not exists
  (select * from enrolls,student
      where enrolls.sno=student.sno and enrollso=courseso and sname like '丁%')
13、查询全部学生都选修的课程的课程号和课程名。
select cno,cname from courses where not exists
  (select * from student where not exists
      (select * from enrolls where enrolls.sno=student.sno and enrollso=courseso ))
14、查询至少选修课程号为C2和C4的学生学号。
select e1.sno from enrolls e1,enrolls e2
  where e1.sno=e2.sno and e1o='c2' and e2o='c4'
15、查询比95022同学成绩高的学生的学号和姓名。
select sno,sname from student where sno in
  (select e1.sno from enrolls e1,enrolls e2 ade&ade and e2.sno='95012' )

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。