10-19 查询学生成绩及汇总其总学分(MSSQL)

本题目要求编写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 snoFROM stuWHERE 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 snoFROM stuWHERE sname = N'张三'
)
AND sc.grade >= 60