本文共 2181 字，大约阅读时间需要 7 分钟。

一、带in谓词的嵌套查询

select snamefrom studentwhere sno in(	select s2.sno	from score s1,score s2	where s1.cno = '3105' and s2.cno ='3245' and s1.sno = s2.sno)

2、查询被所有学生都选修的课程的任教教师名。

select tnamefrom teacherwhere tno in(	select tno	from course	where cno in(		select cno 		from score		group by cno		having count(sno)=7//学生人数为7个	))

3、查询选修了高等数学的学生姓名。

select snamefrom student where sno in(	select sno 	from score	where cno in(		select cno		from course		where cname = '高等数学'	))

4、查询男教师所授课程的平均成绩。

select avg(grade) as 平均成绩from scorewhere cno in(	select cno	from tno in(		select tno		from teacher		where sex ='男'	))

5、查询电子工程系教师所授课程中每门课程的最高分。

select max(grade) as 最高分from scorewhere cno in(	select cno 	from course	where tno in(		select tno 		from teacher		where dept = '电子工程'	))

6、查询姓王的同学所选修课程的任课教师名。

select tnamefrom teacherwhere tno in(	select tno	from course	where cno in(		select cno		from score		where sno in(			select sno			from student			where sname like '王%'		)	))

7、查询没有选修3245课程的学生姓名。

select snamefrom studentwhere sno not in(	select sno	from score	where cno = '3245')

8、查询由助教所授课程中平均成绩在75分以上课程的选修人数。

select count(sno) as 选修人数from scoregroup by cnohaving avg(grade) > 75 and cno in(	select cno	from course	where tno in(		select tno 		from teacher		where prof = '助教'	))

9、查询有超过6个人选修的课程的任课教师职称。

select proffrom teacherwhere tno in(	select tno 	from course	where cno in(		select cno 		from score		group by cno		having count(sno) > 6	))

10、查询选修了超过3门课程的学生学号和姓名。

select sno,snamefrom studentwhere sno in(	select sno	from score	group by sno	having count(cno) > 3)

11、查询计算机系的学生所选课程成绩中最高分课程的任课教师名。

select tnamefrom teacherwhere tno in(	select tno	from course	where cno in(		select cno		from score		where grade in(			select grade			from score			where sno in(				select sno 				from student 				where dept = '计算机'			)		)	))

12、查询匡明同学所选课程的任课教师所在的系。

select deptfrom teacherwhere tno in(	select tno 	from course	where cno in(		select cno 		from score		where sno in(			select sno			from student			where sname = '匡明'		)	))

转载地址：http://gtppz.baihongyu.com/