10-13 查询各专业学生的平均成绩(MSSQL)

本题目要求编写SQL语句，
统计各专业的学生选课的平均成绩，如果某专业尚未有任何学生选修课程或成绩为空时，平均分计为0。输出结果集按照major表中的mno升序排序。

提示：MSSQLServer 评测SQL语句。

表结构:

定义表结构的SQL语句如下：

-- 专业表
CREATE TABLE major (  
  mno char(2) NOT NULL,
  mname nvarchar(20) NOT NULL,
  PRIMARY KEY (mno)
);
-- 学生表
CREATE TABLE stu (    
  sno char(4) NOT NULL,
  sname nvarchar(8) NOT NULL,
  sex smallint DEFAULT NULL,
  mno char(2) DEFAULT NULL,
  birdate datetime DEFAULT NULL,
  memo ntext,
  PRIMARY KEY (sno),
  CONSTRAINT  fk_stu_mno FOREIGN KEY (mno) REFERENCES major (mno)
);
-- 课程表
CREATE TABLE  cou  (  
   cno  char(4) NOT NULL,
   cname  nvarchar(30) NOT NULL,
   credit  smallint DEFAULT NULL,
   ptime  char(5) DEFAULT NULL,
   teacher  nvarchar(10) DEFAULT NULL,
  PRIMARY KEY ( cno )
);
-- 学生选课成绩表
CREATE TABLE  sc  (  
   sno  char(4) NOT NULL,
   cno  char(4) NOT NULL,
   grade  decimal(4,1) DEFAULT NULL,
  PRIMARY KEY ( sno , cno ),
  CONSTRAINT  fk_sc_cno  FOREIGN KEY ( cno ) REFERENCES  cou  ( cno ),
  CONSTRAINT  fk_sc_sno  FOREIGN KEY ( sno ) REFERENCES  stu  ( sno )
);

表样例

上述表结构对应的表样例。例如

major表：

stu表：

sc表：

输出样例:

请在这里给出输出样例。例如：

SELECT
  mname 专业,
  isnull( avg( grade ), 0 )平均成绩 
FROM
  major
  LEFT JOIN ( SELECT mno, grade FROM stu, sc WHERE stu.sno = sc.sno ) AS a ON major.mno = a.mno 
GROUP BY
  major.mno,
  mname 
ORDER BY
  major.mno;
--思路：使用left join 能查询到该专业有哪些学生没有选择，连接stu，sc表，查询学生的专业号和成绩，在与major连接，最后通过专业号，和专业名分组，因为major和a相连有mno和mname字段，所以要根据除聚合函数外的字段分组，本题要求使用sql server，这里判断平均成绩为空，就不能使用ifnull，而是使用isnull