(1)查询每个学生及其选课情况
select student.*,sc.*
from student,sc where student.sno=sc.sno
(2)查询每门课的间接先修课。
select first.cno,second.cpno
from course first left join course second
on first.cpno=second.cno
(3)将student,sc进行右链接
select student.* ,sc.*
from student right join sc on student.sno=sc.sno
(4)查询不及格学生的姓名和所在系
select sname,sdept
from student where sno in(
select sno from sc group by sno having min(grade)<60)
(5)查询所有成绩为优秀(大于90分的学生信息)的学生姓名
select sname from student where sno in(
select sno from sc group by sno having min(grade)>90)
(6)查询即选修了2号课程又选修了3号课程的学生姓名，学号
select sname,A.sno from student ,sc A ,sc B where A.cno=‘002‘ and B.cno=‘003‘ and A.sno=B.sno and A.sno=student.sno
(7)查询和刘晨同一年龄的学生
select * from student where sage=(
select sage from student where sname=‘刘晨‘)
(8) 选修了课程名为“数据库”的学生姓名和年龄
select sname ,sage from student where sno in(
select sno from sc where cno in(
select cno from course where cname=‘数据库‘))
(9)查询其他系比is系任意学生年龄小的学生名单
select * from student where sage<any(
select sage from student where sdept=‘is‘) and sdept<>‘is‘
(10)查询其他系比is系所有学生年龄都小的学生名单
select * from student where sage<all(
select sage from student where sdept=‘is‘) and sdept<>‘is‘
(11)查询选修了全部课程的学生姓名
SELECT sname
from student
where sno in
(
select sno
from sc
group by sno
having count(cno)=7
)
(12)查询计算机系学生及其性别是男的学生
select * from student where sdept=‘is‘and ssex=‘男‘
(13)查询选修课程1的学生集合和选修2号课程学生集合的差集
select sno from sc where cno=‘001‘and sno not in(
select sno from sc where cno=‘002‘)
(14)查询李丽同学不学的课程号
select distinct cno from sc where cno not in(
select cno from student,sc where sname=‘李丽‘and student.sno=sc.sno)
(15)查询选修了3号课程的学生平均年龄
select avg(sage) from student where sno in(
select sno from sc where cno=‘003‘)
(16)求每门课的平均成绩
select cno,avg(grade) from sc group by cno
(17)统计每门课程的学生选修人数(超过3人的统计)。要求输出课程号和选修人数，结果按人数降序排列，若人数相同，按课程号升序排列。
select distinct cno ,count(sno)
from sc
group by cno
having count(sno)>3
order by 2 desc,cno asc
(18)查询学号比刘晨大的，而年龄比他小的学生姓名。
select sname from student where sno>(
select sno from student where sname=‘刘晨‘)and sage<(
select sage from student where sname=‘刘晨‘)
(19) 求年龄大于女同学平均年龄的男同学姓名和年龄
select sname ,sage from student where sage>(
select avg(sage) from student where ssex=‘女‘)and ssex=‘男‘
(20)求年龄大于所有女同学年龄的男同学姓名和年龄
select sname ,sage from student where sage>(
select max(sage) from student where ssex=‘女‘)and ssex=‘男‘
(21)查询至少选修了95002选修的全部课程的学生号码
输入的数据中没有95002的，查询了08002的
select sno from sc where cno in(
select cno from sc where sno=‘08002‘)
group by sno having count(cno)=(
select count(cno) from sc where sno=‘08002‘)
(22)查询95001和95002两个学生都选修的课程信息
数据中没有95001和95002的查询了08001和08002
select * from course where cno in (
select cno from sc where sno=‘08001‘)
and cno in (
select cno from sc where sno=‘08002‘)