数据库作业5

3. 创建视图的 SQL 语句

（1）查询学生的学号、姓名、所在系、课程号、课程名、课程学分

CREATE VIEW View_StudentCourse AS  
SELECT  
    S.Sno AS 学号,  
    S.Sname AS 姓名,  
    S.Sdept AS 所在系,  
    C.Cno AS 课程号,  
    C.Cname AS 课程名,  
    C.Credit AS 课程学分  
FROM Student S  
JOIN SC ON S.Sno = SC.Sno  
JOIN Course C ON SC.Cno = C.Cno;  

（2）查询学生的学号、姓名、选修的课程名和考试成绩

CREATE VIEW View_StudentScore AS  
SELECT  
    S.Sno AS 学号,  
    S.Sname AS 姓名,  
    C.Cname AS 课程名,  
    SC.Grade AS 考试成绩  
FROM Student S  
JOIN SC ON S.Sno = SC.Sno  
JOIN Course C ON SC.Cno = C.Cno;  

（3）统计每个学生的选课门数，列出学号和选课门数

CREATE VIEW View_CourseCount AS  
SELECT  
    Sno AS 学号,  
    COUNT(Cno) AS 选课门数  
FROM SC  
GROUP BY Sno;  

（4）统计每个学生的修课总学分，列出学号和总学分

CREATE VIEW View_TotalCredit AS  
SELECT  
    S.Sno AS 学号,  
    SUM(C.Credit) AS 总学分  
FROM Student S  
JOIN SC ON S.Sno = SC.Sno  
JOIN Course C ON SC.Cno = C.Cno  
GROUP BY S.Sno;  

（5）查询计算机系Java考试成绩最高的学生学号、姓名和Java考试成绩

CREATE VIEW View_JavaMaxScore AS  
SELECT  
    S.Sno AS 学号,  
    S.Sname AS 姓名,  
    SC.Grade AS Java成绩  
FROM Student S  
JOIN SC ON S.Sno = SC.Sno  
JOIN Course C ON SC.Cno = C.Cno  
WHERE S.Sdept = '计算机系'  
  AND C.Cname = 'Java'  
  AND SC.Grade = (  
      SELECT MAX(Grade)  
      FROM SC  
      JOIN Course C ON SC.Cno = C.Cno  
      WHERE C.Cname = 'Java'  
  );  

---

4. 利用视图完成查询

（1）查询考试成绩≥90的学生姓名、课程名和成绩

SELECT 姓名, 课程名, 考试成绩  
FROM View_StudentScore  
WHERE 考试成绩 >= 90;  

（2）查询选课门数超过3门的学生学号和选课门数

SELECT 学号, 选课门数  
FROM View_CourseCount  
WHERE 选课门数 > 3;  

（3）查询计算机系选课门数超过3门的学生学号和选课门数

SELECT S.Sno AS 学号, VC.选课门数  
FROM Student S  
JOIN View_CourseCount VC ON S.Sno = VC.学号  
WHERE S.Sdept = '计算机系'  
  AND VC.选课门数 > 3;  

（4）查询选课总学分超过10分的学生学号、姓名、所在系和总学分

SELECT S.Sno AS 学号, S.Sname AS 姓名, S.Sdept AS 所在系, VC.总学分  
FROM Student S  
JOIN View_TotalCredit VC ON S.Sno = VC.学号  
WHERE VC.总学分 > 10;  

---

5. 修改第3题（4）的视图，添加平均成绩和选课门数

-- 删除原视图  
DROP VIEW View_TotalCredit;  

-- 创建新视图  
CREATE VIEW View_TotalCredit AS  
SELECT  
    S.Sno AS 学号,  
    SUM(C.Credit) AS 总学分,  
    AVG(SC.Grade) AS 平均成绩,  
    COUNT(SC.Cno) AS 选课门数  
FROM Student S  
JOIN SC ON S.Sno = SC.Sno  
JOIN Course C ON SC.Cno = C.Cno  
GROUP BY S.Sno;  

---

6. 修改第3题（5）的视图，查询全体学生中Java考试最高分的学生信息

-- 删除原视图  
DROP VIEW View_JavaMaxScore;  

-- 创建新视图  
CREATE VIEW View_JavaMaxScore AS  
SELECT  
    S.Sno AS 学号,  
    S.Sname AS 姓名,  
    S.Sdept AS 所在系,  
    SC.Grade AS Java成绩  
FROM Student S  
JOIN SC ON S.Sno = SC.Sno  
JOIN Course C ON SC.Cno = C.Cno  
WHERE C.Cname = 'Java'  
  AND SC.Grade = (  
      SELECT MAX(Grade)  
      FROM SC  
      JOIN Course C ON SC.Cno = C.Cno  
      WHERE C.Cname = 'Java'  
  );  

---

注意事项

1. 视图依赖关系：修改视图前需先删除原视图（如题5、6）。
2. 聚合函数与分组：确保 GROUP BY 正确分组，避免遗漏字段。
3. 子查询性能：涉及子查询的视图（如题3（5））需注意查询效率，建议添加索引优化。
4. 字段别名：视图列名需清晰明确，便于后续查询使用。

---

说明：以上代码基于标准 SQL 语法，实际使用时需根据数据库系统（如 MySQL、SQL Server）调整语法细节（如 DROP VIEW 是否需要 CASCADE）。