答案不全面,欢迎交流沟通
-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select * from sc s INNER JOIN sc c INNER JOIN student st on s.S = c.S and st.S=s.S where s.C=01 and c.C=02 and s.score > c.score;
-- //select * from sc s inner join sc c on s.s=c.s where s.c=01 and c.c=02 and s.scorse>c.scorse;
-- select * from student,(select * from sc where c=01) q INNER JOIN(select * from sc where c=02) w on q.s=w.s on where q.score > w.score;//
-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select * from sc s INNER JOIN sc c INNER JOIN student st on s.S = c.S and st.S=s.S where s.C=01 and c.C=02 and s.score < c.score;
-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select s.S,avg(s.score),st.Sname from student st INNER JOIN sc s on st.S=s.S group by s.S having avg(s.score) >=60;
-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
select s.S,avg(s.score),st.Sname from student st INNER JOIN sc s on st.S=s.S GROUP BY s.S HAVING avg(score)>60;
-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select st.S,st.Sname, sum(s.score),count(s.C)from student st INNER JOIN sc s ON st.S=s.S GROUP BY s.S;
-- 6、查询"江"姓老师的数量
select count(s) as '总人数'from sc where sc.C=(select t from teacher where Tname='江老师')
-- 7、查询学过"向老师"授课的同学的信息
select * from sc INNER JOIN student on sc.S =student.S where sc.C=(select t from teacher where Tname='向老师') and sc.S=student.S;
-- 8、查询没学过"向老师"授课的同学的信息
//select * from teacher tt INNER JOIN course c ON tt.T=c.T INNER JOIN sc s on c.C=s.C INNER JOIN student st on s.S=st.S where tt.Tname !='向老师';
-- 9、查询学过编号为"01"