1　 与“张三”同乡的男生姓名
2 　选修了赵露老师所讲课程的学生人数
3 　查询没学过“王”姓老师课的同学的学号、姓名
4　“数学”课程得最高分的学生姓名、性别
5 　统计每门课程的平均成绩,并按照成绩降序排序
6 　子查询实现：查询‘3-2班’"张立"同学的"英语"成绩
7 　查询“福建”地区学生所选修的全部课程名称
8 　查询所在班级和该班内学生的年龄之和，对该班级中每个人的年龄进行比对，(要求大于20岁的人参与统计)
9 　查询所在班级和该班内学生的年龄之和，(要求该班级中每个人的年龄都大于20岁)
10　用子查询实现查询选修“高等数学”课的全部学生的高等数学总成绩

select * from student;
select * from mark;
select * from course;
select * from teacher

1　 与“张三”同乡的男生姓名
step1:找出张三的籍贯
select snativeplace from student where sname='张三'
step2:找出与“张三”同乡的男生姓名
select sname from student where ssex='男' and snativeplace=(step1) and not sname='张三'
----最终答案
select sname from student where not sname='张三' and 
	  snativeplace=(select snativeplace from student where sname='张三') and 
	  ssex='男'

---------------------------------------------------------------------
2 　选修了赵露老师所讲课程的学生人数（一对多的关系）
--添加信息：
insert into teacher (tid,tname,tsex,tage,tlvl) values (30008,'赵露','女',35,'教授');

insert into course (cid,cname,cval,ctime,tid) values (2008,'语文',4,64,30008);
insert into course (cid,cname,cval,ctime,tid) values (2009,'线性代数',5,64,30008);

insert into mark (sid,cid,cmark) values (10001,2008,80);
insert into mark (sid,cid,cmark) values (10002,2008,78);
insert into mark (sid,cid,cmark) values (10002,2009,85);
insert into mark (sid,cid,cmark) values (10003,2008,89);
  
step1:找出赵露老师的tid
select tid
from teacher
where tname='赵露'
step2:找出tid=(step1)的cid
select cid from course
where tid=(step1)
----
select cid from course where tid=(select tid from teacher where tname='赵露')
step3:找出cid in(step2)的count(distinct sid)
select count(distinct sid) from mark where cid in(step2)
----最终答案
--全子查询
select count(distinct sid) from mark where cid in(select cid from course where tid=(select tid from teacher where tname='赵露'))
--或子查询+内连接
select count(distinct sid) from mark where cid in(select cid from course c join teacher t on c.tid=t.tid where t.tname='赵露')

---------------------------------------------------------------------
3 　查询没学过“王”姓老师课的同学的学号、姓名
--添加记录
select * from student;
select * from mark;
select * from course;
select * from teacher 

insert into teacher (tid,tname,tsex,tage,tlvl) values (30009,'王安','女',39,'中级教师');
insert into teacher (tid,tname,tsex,tage,tlvl) values (30010,'王清风','男',32,'中级教师');

insert into course (cid,cname,cval,ctime,tid) values (2010,'操作系统',3,48,30009);
insert into course (cid,cname,cval,ctime,tid) values (2011,'经济学',5,64,30010);

insert into mark (sid,cid,cmark) values (10001,2010,82);
insert into mark (sid,cid,cmark) values (10002,2011,85);
insert into mark (sid,cid,cmark) values (10003,2010,80);
insert into mark (sid,cid,cmark) values (10003,2011,87);

step1:找出“王”姓老师的tid——teacher
select tid
from teacher
where tname like'王%'
step2:找出tid in(step1)的cid——course
select cid
from course
where tid in(step1)
----
select cid
from course
where tid in(select tid
from teacher
where tname like'王%')
step3:找出cid in(step2)的sid——mark
select distinct sid
from mark
where cid in(step2)
----
select distinct sid
from mark
where cid in(select cid
from course
where tid in(select tid
from teacher
where tname like'王%'))
step4:找出sid 对应的sname——student
select sid,sname
from student
where sid in(step3)
order by sid
----最终答案
--全子查询
select sid,sname from student where sid not in(select distinct sid from mark where cid in(
select cid from course where tid in(select tid from teacher where tname like'王%'))) order by sid
--或子查询+内连接
select sid,sname from student where sid not in (
select distinct sid from mark m join course c on m.cid=c.cid where tid in (
select tid from teacher where tname like '王%'))

-----------------------------------------------------------------
4　 “数学”课程得最高分的学生姓名、性别
step1:找出数学课程的cid——course
select cid
from course
where cname='数学'
step2:找出cid=(step1)的max(cmark)——mark
select max(cmark)
from mark
where cid=(step1)
----
select max(cmark)
from mark
where cid=(select cid
from course
where cname='数学')
step3:找出cmark=(step2)的sid and cid=(step1)——mark
select sid
from mark
where cmark=(step2) and cid=(step1)
----
select sid
from mark
where cmark=(select max(cmark)
from mark
where cid=(select cid
from course
where cname='数学')) and cid=(select cid
from course
where cname='数学')
step4:找出sid对应的sname,ssex 
select sname,ssex
from student
where sid=(step3)
-----最终答案
--全子查询
select sname,ssex from student where sid=(select sid from mark where cmark=(
select max(cmark) from mark where cid=(select cid from course where cname='数学')) and cid=(
select cid from course where cname='数学'))
--或子查询+内连接
select sname,ssex from student s join mark m on s.sid=m.sid where cid=(
select cid from course where cname='数学') and cmark = (
select max(cmark) from mark where cid=(select cid from course where cname='数学'))

select * from student;
select * from mark;
select * from course;
select * from teacher
5 　统计每门课程的平均成绩,并按照成绩降序排序
格式：课程名 平均成绩
----最终答案
select cname,avg(cmark) amk from mark m join course c on m.cid=c.cid 
group by m.cid,cname order by amk desc

6 　子查询实现：查询‘3-2班’"张立"同学的"英语"成绩 
--添加信息
insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10019,'张立',20,'男','浙江','心理学','3-2班','汉族');
insert into mark (sid,cid,cmark) values (10019,2002,90);

step1:找出‘3-2班’"张立"同学的sid   --——student
select sid
from student
where sclass='3-2班' and sname='张立'
step2:找出cname='英语'的cid--——course
select cid
from course
where cname='英语'
step3:找出sid in(step1) and cid=(step2)的cmark--——mark
select cmark
from mark
where sid in(step1) and cid=(step2)
----最终答案
select cmark from mark where sid in(select sid from student where sclass='3-2班' and sname='张立') and cid=(
select cid from course where cname='英语')

----------------------------------------------------------------------------------------
7 　查询“福建”地区学生所选修的全部课程名称 
step1:找出snativeplace='福建'的sid  --student
select sid
from student
where snativeplace='福建'
step2:找出sid in(step1)的 distinct cid  --mark
select distinct cid
from mark
where sid in(step1)
----
select distinct cid
from mark
where sid in(select sid
from student
where snativeplace='福建')
step3:找出cid in(step2)对应的cname——course
select cname
from course
where cid in (step2)
order by cid
----最终答案
--全子查询
select cname from course where cid in (select distinct cid from mark where sid in(
select sid from student where snativeplace='福建')) order by cid
--或子查询+内连接
select distinct cname from course c join mark m on c.cid=m.cid where sid in (
select sid from student where snativeplace='福建')
----------------------------------------------------------------------------------------
8 　查询所在班级和该班内学生的年龄之和,对该班级中每个人的年龄进行比对，(要求大于20岁的人参与统计)
step:找出sclass,sum(sage),过滤条件sage>20,分组条件sclass
----最终答案
select sclass,sum(sage) from student where sage>20 group by sclass

----------------------------------------------------------------------------------------
9 　查询所在班级和该班内学生的年龄之和,(要求该班级中每个人的年龄都大于等于20岁)
--添加信息
step1:找出有<20的学生所在的班级sclass
select distinct sclass
from student
where sage < 20
step2:找出sclass,sum(sage)并且sclass not in (step1)
select sclass,sum(sage)
from student
where sclass not in(step1)
group by sclass
----最终答案
--思路一
select sclass,sum(sage) from student where sclass not in (
select distinct sclass from student where sage <20) group by sclass
--思路二
select sclass,sum(sage) from student group by sclass having min(sage)>=20

----------------------------------------------------------------------------------------
10　用子查询实现查询选修“数学”课的全部学生的高等数学总成绩 
step1:找出cname='数学'的cid——course
select cid
from course
where cname='数学'
step2:找出cid=(step1)的sum(cmark)
select sum(cmark)
from mark
where cid=(step1)
----最终答案
select sum(cmark) from mark where cid=(select cid from course where cname='数学')