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
小结:
希望对你有所帮助!