10-29 插入学生总学分表(MSSQL)

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

注意：

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

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

3）执行Insert语句之前，totalcredit表中没有任何记录。

提示：MSSQLServer 评测SQL语句。

表结构:

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

-- 学生表
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)
);
-- 课程表
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 )
)
-- 总学分表
CREATE TABLE totalcredit(
  sno char(4) NOT NULL,
  totalcredit smallint DEFAULT 0
);

 

表样例

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

cou表：

 stu表：

sc表：

 

 

输出样例:

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

 思路：

1. 首先，我们使用一个子查询来获取每个学生的学号和根据成绩计算的学分。在子查询中，我们使用了LEFT JOIN连接了三个表：stu（学生表）、sc（学生选课表）和cou（课程表）。通过stu.sno和sc.sno以及sc.cno和cou.cno的关联，我们可以获取到学生的学号、成绩和课程的学分。在子查询中，我们使用了CASE语句来判断成绩是否大于等于60，如果是，则计算相应的学分，否则为0。

2. 接下来，我们在主查询中使用GROUP BY子句将结果按学生编号（sno）进行分组。然后，使用SUM函数计算每个学生的总学分，将其命名为totalcredit。

3. 最后，我们使用INSERT INTO语句将学生的学号（sno）和总学分（totalcredit）插入到"totalcredit"表中。通过ORDER BY子句，我们可以按学生编号升序排序插入的数据。 这样，我们就可以将每个学生的总学分插入到"totalcredit"表中。

INSERT INTO totalcredit (sno, totalcredit)
SELECT sno, SUM(credit) AS totalcredit
FROM (
    SELECT stu.sno, CASE WHEN sc.grade >= 60 THEN cou.credit ELSE 0 END AS credit
    FROM stu
    LEFT JOIN sc ON stu.sno = sc.sno
    LEFT JOIN cou ON sc.cno = cou.cno
) AS subquery
GROUP BY sno
ORDER BY sno ASC;