1、查询“c001”课程比“c002”课程成绩高的所有学生的学号；
--方法一：
select m.* from
(select * from sc a where a.cno='c001') m,--分组课程
(select * from sc b where b.cno='c002') n
where m.sno = n.sno and m.score > n.score;--学号一样，课程成绩比较
--方法二：
select * from sc a
where a.cno='c001'
and exists(select * from sc b where b.cno='c002' and a.score>b.score and a.sno = b.sno)；
--在学号一样时，存在一个b的课程成绩小于a
2、查询平均成绩大于60 分的同学的学号和平均成绩；
select c.sno "学号",avg(c.score) "平均成绩" from sc c group by c.sno having avg(c.score) > 60;
3、查询所有同学的学号、姓名、选课数、总成绩；
select * from sc;
select *from student;
select a.*,s.sname from (select sno,sum(score),count(cno) from sc group by sno) a ,student s where a.sno=s.sno;
--在两表中学号一样的进行学号、总成绩、选课数分组
4、查询姓“刘”的老师的个数；
select count(*) "姓 刘 的老师个数" from teacher t where t.tname like '刘%';
5、查询没学过“谌燕”老师课的同学的学号、姓名；
--放法一：
select s.sno "学号",s.sname "姓名" from student s
where s.sno
not in
(select distinct s.sno --去掉重复学号
from sc s,
(select c.*
from course c ,-- c总课程
(select tno
from teacher t
where tname='谌燕')t -- t是 谌燕 老师的课程
where c.tno=t.tno) b -- b是总课程与谌燕老师交集
where s.cno = b.cno ) --条件 课程一样
select * from teacher ;
select * from course ;
--方法二：
select st.sno "学号",st.sname "姓名" from student st where st.sno not
in(select distinct s.sno --去学号重复的
from sc s join course c on s.cno=c.cno join teacher t on c.tno=t.tno
--要求表sc和表course的课程一样，表course和表teacher的课程编号一样
and tname='谌燕') --也可以用 where
6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；
select st.sno "学号",st.sname "姓名" from sc s
join sc a on s.sno = a.sno --学号是一样的
join student st
on s.sno = a.sno
where s.cno = 'c001' and a.cno = 'c002' and st.sno=s.sno;
7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名；
select st.sno"学号",st.sname "姓名",s.cno "课程" from student st
join sc s on st.sno=s.sno --学号一样
join course c on s.cno=c.cno --再课程一样
join teacher t on c.tno=t.tno --课程编号一样
where t.tname='谌燕';
8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名；
select /*st.sno "学号",st.sname "姓名"*/* from student st
join sc s1 on st.sno = s1.sno
join sc s2 on st.sno = s2.sno
where s1.cno ='c001' and s2.cno = 'c002' and s1.score > s2.score;
9、查询所有课程成绩小于60 分的同学的学号、姓名；
select /*st.sno "学号",st.sname "姓名"*/* from student st
join sc s on st.sno = s.sno
/*join course c on s.cno=c.cno*/--也可以加上
where s.score < 60;
10、查询没有学全所有课的同学的学号、姓名；
select stu.sno,stu.sname,count(sc.cno) from
student stu left join sc on stu.sno=sc.sno --student和sc左连接
group by stu.sno,stu.sname
having count(sc.cno)<(select count(distinct cno)from course)
11、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名；
select st.* from student st,
(select distinct a.sno from --去掉重复学号
(select * from sc) a,
(select * from sc where sc.sno='s001') b --sc表中学号为s001的作为一个表对象
where a.cno=b.cno) h --a、b表 课程一样
where st.sno=h.sno and st.sno<>'s001';--学号不为s001的
