【SQL语句练习】（一) 查询学生获得的总学分

【SQL语句练习】（一) 查询学生获得的总学分

题目

本题目要求编写SQL语句， 检索出学生‘张三’选修的所有课程及成绩，最后计算他所获得的总学分。输出成绩结果集按课程号升序排序。 注意：选课成绩在60分以上才能获得相应的学分。cou表中credit列为某课程的学分值 。

数据表

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 `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 `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`)
);

解题思路

分别查出张三的各科成绩，和总学分，再把两个查询结果用union all合并好。

SQL语句

因为union all和order by语句不能同时使用，所以先查询完成绩后排序，再选择出查询的结果。

select * from (
select couo 课程号, couame 课程,sc.grade 成绩 from stu
inner join sc on sc.sno=stu.sno
inner join cou on sco=couo
where stu.sname='张三'
order by 课程号
limit 1000
) a
union all
select stu.sname 课程号,
'总学分',
sum(credit)  成绩
from stu
inner join sc on sc.sno=stu.sno
inner join cou on sco=couo
where stu.sname='张三'  ade>=60
group by stu.sname;