-- 1、查询与10060101学生选修的全部课程相同的学生的学号、课程号、期末考试成绩
select sno, cno, grade
from sc a
group by sno, cno, grade
having a.sno<>'10060101'
and not exists ((select cno from sc where sno='10060101') except (select cno from sc where sno=a.sno))
and not exists ((select cno from sc where sno=a.sno) except (select cno from sc where sno='10060101'))
--2 查询至少选了10060101选修的全部课程的学生的学号
select sno
from sc a
group by a.sno
having a.sno<>'10060101'
and not exists ((select cno from sc where sno='10060101') except (select cno from sc where sno=a.sno))
--3 查询年龄比所在院系平均年龄小的学生的学号、姓名、年龄、院系,按院系和年龄升序排列
select sno, sname, age, dept
from student s
where age<(select avg(age) from student where dept=s.dept)
order by s.dept, s.age
--4 查询每门课都在80分以上的学生的学号和姓名
select sno, sname
from student s
where exists (select * from sc where sno=s.sno)
and not exists (select * from sc where sno=s.sno and grade<80)
温馨提示:答案为网友推荐,仅供参考