SQL多表查询

-- 1、那如果是统计每门课的平均成绩，并且显示课号和平均成绩，怎么解
select avg(Score) as 平均成绩,CourseCode as 课号 from SelectionCourse group by CourseCode

-- 2、查询学生每门功课的总成绩，显示学号和总分(应该是：学生的总成绩，显示学号和总分）
select Sum(Score) as 总分,Number as 学号 from SelectionCourse group by Number

-- 3、统计每门功课的总成绩低于300分的学生，显示学号和总分(应该是：学生的总成绩低于300，显示学号和总分）
select Sum(Score) as 总分,Number as 学号 from SelectionCourse group by Number Having Sum(Score)<300

-- 4、统计每个老师所上课程的学生的总人数，显示教师工号，总人数，按教师工号降序排列
select t1.Code as 教师工号,t3.人数 form Teacher as t1
 left join (select TeacherCode,CourseCode from TeacherCourse) as t2 on t1.Code=t2.TeacherCode
 left join (select count(*) as 人数,CourseCode from SelectionCourse group by CourseCode) as t3 on t2.CourseCode=t3.CourseCode
 order by t1.Code desc

-- 第5，6，自己想想吧，可以参照第4的联接

学习