一、题目
1.与“张三”同乡的男生姓名
select * from student where snativeplace=(select snativeplace from student where sname='张三') and ssex='男'
2.选修了赵露老师所讲课程的学生人数
select count(*) from mark where cid in(select cid from course where tid=(select tid from teacher where tname='赵露')) group by cid select count(distinct sid) from mark where cid in(select cid from course where tid=(select tid from teacher where tname='赵露'))
3.查询没学过“王”姓老师课的同学的学号、姓名
select * from student where sid not in(select sid from mark where cid in( select cid from course where tid in(select tid from teacher where tname like '王%')))
4.数学”课程得最高分的学生姓名、性别
select * from student where sid in( select sid from mark where cid=( select cid from course where cname='数学') and cmark>= all( select cmark from mark where cid=(select cid from course where cname='数学') ))
5.统计每门课程的平均成绩,并按照成绩降序排序
select *from(select (select cname from course where cid=m.cid), avg(cmark) cavg from mark m group by cid )order by cavg desc select * from (select avg(cmark) cavr from mark group by cid)order by cavg desc
6.子查询实现�查询‘3-2班’"张立"同学的"英语"成绩
select cmark 成绩 from mark where sid=( select sid from student where sname='张三'and sclass='2班') and cid =(select cid from course where cname='英语')
7.查询所在班级和该班内学生的年龄之和,对该班级中每个人的年龄进行比对，(要求大于20岁的人参与统计)
select sclass ,sum(sage) from student where sage>=20 group by sclass
8.查询所在班级和该班内学生的年龄之和,(要求该班级中每个人的年龄都大于20岁)
select sclass ,sum(sage) from student group by sclass having min(sage)>20 select sclass ,sum(sage) from student where sclass not in( select sclass from student where sage<20 group by sclass) group by sclass
9.用子查询实现查询选修“高等数学”课的全部学生的高等数学总成绩
select sum(cmark) from mark where cid =(select cid from course where cname='数学')
10.用子查询实现查询选修“高等数学”课的全部学生的所有课程总成绩*/
select sum(cmark) from mark where sid in(select sid from mark where cid =(select cid from course where cname='数学'))
11.请用两种方法实现:查找所有成绩都在68分以上的学生姓名*/
select sid,sname from student where sid in ( select sid from mark group by sid having min(cmark)>=68) select sid,sname from student s where exists ( select sid from mark m where s.sid=m.sid group by sid having min(cmark)>=68)
12.查找至少2门成绩在80分以上的学生姓名
select sname from student where sid in( select sid from mark where cmark >=80 group by sid having count(*)>=2 )
13.查询至少有一门课与张三同学所学相同的同学的学号和姓名
select sid,sname from student where sid in( select sid from mark where cid in ( select cid from mark where sid in ( select sid from student where sname='张三')))
14.没有选修“数学”课的学生的姓名*/
select sname from student where sid in ( select sid from mark where sid not in( select sid from mark where cid in( select cid from course where cname='数学' ) ) )
15.查询个人总成绩小于平均总成绩的学生姓名
step1、计算出平均总分 step1.1计算出个人总分表 select sid,sum(cmark) smk from mark group by sid step1.2对总分表求平均值 select avg(smk) from (step1.1) step2、找出在个人总分表中找出谁的总分低于step1 step2.1 select sid from (step1.1)where smk <(step1.2) step2.2 select sname from student where sid in (step2.1)