本题目要求编写SQL语句, 检索出学生‘张三’选修的所有及格的课程及成绩,最后计算他所获得的总学分。
注意:选课成绩在60分以上才能获得相应的学分。cou表中credit列为某课程的学分值 。假定学生姓名没有重名的情况。 输出成绩结果集忽略顺序只对比数据。
提示:可使用WITH ROLLUP语句或UNION子句汇总结果。
表结构:
请在这里写定义表结构的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 ) );
表样例
请在这里给出上述表结构对应的表样例。例如
stu表:
cou表:
sc表:
输出样例:
请在这里给出输出样例。例如:
思路:
这个查询语句的目的是获取学生姓名为"张三"的课程成绩信息以及该学生所修课程的总学分。
1.使用内连接将"sc"表和"cou"表连接起来,连接条件是"sc"表中的课程号(cno)等于"cou"表中的课程号(cno)。
2.使用子查询获取学生姓名为"张三"的学生学号(sno)。 在连接后的结果中,使用WHERE子句过滤出成绩大于等于60的记录。
3.使用UNION ALL操作符将上述结果与另一个查询结果连接起来。这个查询结果返回了三个NULL值和学生所修课程的总学分。
4.使用子查询获取学生姓名为"张三"的学生学号(sno),并在WHERE子句中过滤出成绩大于等于60的记录。
5.最终的结果将包括学生姓名为"张三"的课程成绩信息以及该学生所修课程的总学分。
SELECT sc.cno as 课程号,cou.cname as 课程名,sc.grade as 成绩,cou.credit as 学分
FROM sc
INNER JOIN cou ON sc.cno = cou.cno
WHERE sc.sno = (
SELECT sno
FROM stu
WHERE sname = N'张三')
AND sc.grade >= 60
UNION ALL
SELECT NULL, NULL, NULL, SUM(cou.credit)
FROM sc
INNER JOIN cou ON sc.cno = cou.cno
WHERE sc.sno = (
SELECT sno
FROM stu
WHERE sname = N'张三'
)
AND sc.grade >= 60
好文链接
发表评论