SQL语句 统计平时考试成绩大于等于80分的学生的姓名、考试的课程号、考试成绩和平均成绩，并将结果按平均成绩降序排列

假设有以下表：

- 学生表：student(sid, sname, gender)
- 课程表：course(cid, cname)
- 成绩表：score(sid, cid, score)

则可以使用以下 SQL 语句实现：

SELECT s.sname, sc.cid, sc.score, AVG(sc.score) AS avg_score 
FROM student s, score sc 
WHERE s.sid = sc.sid AND sc.score >= 80 
GROUP BY s.sid, sc.cid, sc.score 
ORDER BY avg_score DESC;

解释一下：

- 使用 `JOIN` 连接学生表和成绩表，查询条件为成绩大于等于 80 分。
- 使用 `GROUP BY` 分组，分别按照学生 ID、课程 ID 和成绩进行分组。
- 使用 `AVG` 函数计算每个分组的平均成绩，并将其命名为 `avg_score`。
- 使用 `ORDER BY` 按照平均成绩降序排列。

相关问题

写出创建满足下列要求的视图 (1）查询学生的学号、姓名、所在系、课程号、课程名、课程学分。 (2）查询学生的学号、姓名、选修的课程名和考试成绩。 (3）统计每个学生的选课门数,列出学生的学号和选课门数。 (4）统计每个学生的修课总学分，列出学生的学号和总学分（说明，考试成绩大于等于60才可获得此门课程的学分)。 2.修改第1题（4）定义的视图,使其查询每个学生的学号和总的选课门数。 3.写出实现下列操作的 SQL 语句,执行这些语句,并在SSMS工具中观察语句的执行结果。 （1）在Student表的Sde

pt列添加一个默认值为1的约束。(2）在Course表的Cname列中插入一条记录，值为'计算机网络'，并返回插入的记录的主键值。 (3）删除选修了编号为001的课程的学生的选课记录。 (4）将选修了编号为002的课程，且考试成绩为70分的学生的成绩修改为80分。

答：

创建视图：

CREATE VIEW StudentCourseInfo AS
SELECT s.Sno, s.Sname, s.Sdept, sc.Cno, c.Cname, c.Ccredit
FROM Student s, SC sc, Course c
WHERE s.Sno=sc.Sno AND sc.Cno=c.Cno;

CREATE VIEW StudentScoreInfo AS
SELECT s.Sno, s.Sname, c.Cname, sc.Score
FROM Student s, SC sc, Course c
WHERE s.Sno=sc.Sno AND sc.Cno=c.Cno;

CREATE VIEW StudentCourseNum AS
SELECT Sno, COUNT(*) AS CourseNum
FROM SC
GROUP BY Sno;

CREATE VIEW StudentTotalCredit AS
SELECT Sno, SUM(c.Ccredit) AS TotalCredit
FROM Student s, SC sc, Course c
WHERE s.Sno=sc.Sno AND sc.Cno=c.Cno AND sc.Score>=60
GROUP BY s.Sno;

修改视图：

ALTER VIEW StudentTotalCredit
AS
SELECT Sno, COUNT(*) AS TotalCourseNum
FROM SC
GROUP BY Sno;

SQL语句：

(1) ALTER TABLE Student ADD CONSTRAINT DF_Sdept DEFAULT '1' FOR Sdept;
(2) INSERT INTO Course(Cname) VALUES ('计算机网络'); SELECT @@IDENTITY AS new_Cno;
(3) DELETE FROM SC WHERE Cno='001';
(4) UPDATE SC SET Score=80 WHERE Cno='002' AND Score=70;

本题目要求编写sql语句， 创建视图v2，用于统计每个学生的修课总学分，列出学生学号和总学分。 注意：考试成绩大于等于60才可获得此门课程的学分。

### 回答1：
本题目要求编写SQL语句，创建视图v2，用于统计每个学生的修课总学分，列出学生学号和总学分。注意：考试成绩大于等于60才可获得此门课程的学分。

解答如下：

CREATE VIEW v2 AS
SELECT 学生学号, SUM(CASE WHEN 考试成绩 >= 60 THEN 学分 ELSE 0 END) AS 总学分
FROM 修课表
GROUP BY 学生学号;

其中，修课表是学生修课记录的表格，包含了学生学号、课程编号、学分和考试成绩等信息。使用SUM和CASE WHEN函数计算每个学生修课的总学分，并通过GROUP BY子句按学生学号进行归类，最终查询结果包含学生学号和总学分两列。

### 回答2：
本题目要求编写SQL语句创建视图v2，用于统计每个学生的修课总学分，列出学生学号和总学分。考试成绩大于等于60才可获得此门课程的学分。

首先要理解什么是视图。视图是一种虚拟的表，它的结构和数据都是基于一个或多个基表得到的，所以视图并不是一个关系。创建视图可以简化复杂的查询，提高查询效率，降低操作风险。

要创建视图v2，需要使用CREATE VIEW语句。视图的结构应该包含学生的学号和修课总学分，而学分的计算需要根据学生的考试成绩来判断。

考试成绩大于等于60才可获得此门课程的学分，因此需要在视图中添加条件过滤。根据题目要求，我们需要统计每个学生的修课总学分，因此需要在视图中使用GROUP BY子句对学生的学号进行分组计算总学分。

最终的SQL语句如下：

CREATE VIEW v2 AS
SELECT 学生表.学号, SUM(CASE WHEN 课程表.成绩 >= 60 THEN 课程表.学分 ELSE 0 END) AS 总学分
FROM 学生表
INNER JOIN 选课表 ON 学生表.学号 = 选课表.学号
INNER JOIN 课程表 ON 选课表.课程号 = 课程表.课程号
GROUP BY 学生表.学号;

解释一下SQL语句的逻辑。首先使用INNER JOIN关键字连接学生表、选课表和课程表。然后使用CASE WHEN语句过滤掉成绩小于60的选课记录，只对成绩大于等于60的选课记录计算学分总和。最后再根据学生的学号进行GROUP BY分组，得到每个学生的总学分。

创建视图v2后，如果需要查询每个学生的修课总学分，只需要使用SELECT语句从视图中检索数据即可。例如：

SELECT 学号, 总学分
FROM v2;

通过这个SQL语句，我们可以方便地得到每个学生的修课总学分，而不需要编写复杂的查询语句。视图的使用大大简化了我们的数据库操作。

### 回答3：
要创建视图v2，首先需要有学生表和修课表。假设学生表名为student，包含学生编号（id），学生姓名（name），修课表名为grade，包含学生编号（id），课程名称（course），课程学分（credit），课程成绩（score）。

我们需要统计每个学生的修课总学分，所以需要在grade表中筛选出成绩大于等于60的课程，再按照学生编号（id）分组，统计每个学生修课总学分。我们可以用如下的sql语句来实现：

CREATE VIEW v2 AS
SELECT id, SUM(credit) AS total_credits
FROM grade
WHERE score >= 60
GROUP BY id;

这个sql语句中，我们使用了SUM函数来统计每个学生修课的总学分，使用了WHERE语句来筛选出成绩大于等于60的课程，使用了GROUP BY语句来按学生编号分组。最终，我们得到了一个视图v2，列出了每个学生的学号和总学分。

使用视图v2可以方便地获得每个学生修课总学分的信息，例如我们可以用如下的sql语句来查询学生张三的修课总学分：

SELECT total_credits FROM v2 WHERE id='001';

这个语句将会返回张三的修课总学分。使用视图不仅方便我们查询数据，还可以提高查询速度和安全性，因为视图可以隐藏底层数据表的具体实现，只暴露需要的信息。