10-30 插入学生总学分表

10-30 插入学生总学分表

本题目要求编写Insert语句， 计算每位同学获得的总学分，并将所有学生的总学分按学号升序排序后一起插入到totalcredit表中。

注意：

1）当某门课程成绩在60分以上时才能合计计入总学分

2）如果某学生尚未选修任何课程时，总学分计为0，并插入到totalcredit表中。

表结构:

请在这里写定义表结构的SQL语句。例如：

CREATE TABLE `cou` (
  `cno` char(4) NOT NULL,  -- 课程号
  `cname` varchar(30) NOT NULL,  -- 课程名
  `credit` smallint(6) DEFAULT NULL,  -- 学分
  `ptime` char(5) DEFAULT NULL,
  `teacher` char(10) DEFAULT NULL,
  PRIMARY KEY (`cno`)
);
CREATE TABLE `stu` (
  `sno` char(4) NOT NULL,
  `sname` char(8) NOT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  `mno` char(2) DEFAULT NULL,
  `birdate` datetime DEFAULT NULL,
  `memo` text,
  PRIMARY KEY (`sno`)
);

CREATE TABLE `sc` (
  `sno` char(4) NOT NULL,
  `cno` char(4) NOT NULL,
  `grade` decimal(6,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`)
);
CREATE TABLE `totalcredit` (
  `sno` char(4) NOT NULL,
  `totalcredit` smallint(6) DEFAULT '0'
);

表样例：

请在这里给出上述表结构对应的表样例。例如：
cou表：

stu表：

sc表：

输出样例:

插入所有学生的总学分后，totalcredit表如下：

解题方法：

这题没写出来之前觉得挺难的，但是想出来又觉得好像就应该这么想，挺简单的。。有点无语。
其实就是先将三个表left outer join一下，然后找出成绩>=60的学生，然后再用group by根据sno分组，然后计算他们的各自的学分。

代码：

insert into totalcredit
select sno,sum(credit)
from(
	select stu.sno sno,case when sc.grade>=60 then credit else 0 end credit
	from stu left outer join sc on stu.sno=sc.sno left outer join cou on sc.cno=cou.cno
	group by stu.sno,credit,grade) a
group by sno

小结：

希望对你有所帮助！