用T_SQL语句多表连接查询查询选修了大于等于三门课程的学生的学号、姓名、所属专业、选修课程门数与平均分并 按要求以列名呈现；

Question

用T_SQL语句多表连接查询查询选修了大于等于三门课程的学生的学号、姓名、所属专业、选修课程门数与平均分并 按要求以列名呈现；

Answer 1

没有表结构么

SELECT s.ID AS '学号', s.Name AS '姓名', s.Major AS '所属专业', COUNT(cs.CourseID) AS '选修课程门数', AVG(g.Score) AS '平均分'
FROM Student s
INNER JOIN CourseSelection cs ON s.ID = cs.StudentID
INNER JOIN Grade g ON cs.StudentID = g.StudentID AND cs.CourseID = g.CourseID
GROUP BY s.ID, s.Name, s.Major
HAVING COUNT(cs.CourseID) >= 3