本题目要求编写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