Sql server数据库原理实验4

Question

(0)用SQL语句去实现插入数据。
(1)删除2号课程的信息。
(2)将“数据库”课程的名称改为“数据库原理”。
(3)将考试成绩60至70分之间的学生成绩提高10%。
(4)将姓名为李勇的学生的数据库原理课程的成绩加5分。
(5)对每个课程，求学生的选课人数和学生的平均成绩，并把结果存入数据库表中。（需
要新建一个表）
(6)创建一张用于查看男同学信息的视图male_student，查询视图的内容，向视图中插
入一条女生的信息，查看视图中是否有新插入的元组，再去查看student基本表是
否有新插入的元组。
(7)创建女生信息的视图female_student，并要求修改和插入操作时仍需保证该视图只
有女生withcheckoption。向视图中插入一条男生的信息，查看视图中是否有新插
入的元组。再向视图中插入一条女生的信息，查看视图中是否有新插入的元组，再
去查看student基本表是否有新插入的元组。
(8)创建视图view_Score(Sno,NumberC,AvgScore)，查询每个学生的选修课程数目和平
均考试成绩。
(9)建立视图sname_cname(Sname,Cname,Grade)，显示学生姓名，以及他选修课程名和
成绩。
(10)从8题的视图中，查找平均成绩高于80分的学生学号。
(11)使用UPDATE更新视图view_Score，把95001的平均成绩改为95分。
(12)使用UPDATE更新视图male_student，把“李勇”的年龄改为21岁，然后再次查
询视图male_student，查看李勇年龄的变换，再查询Student表，检查李勇年龄是否
也改变。

Answer 1

(0) 插入数据

INSERT INTO Student (Sno, Sname, Ssex, Sage, Sdept)  
VALUES (1, '张三', '男', 20, '计算机系');  
  
INSERT INTO Course (Cno, Cname, Tname, StudentNum, AvgGrade)  
VALUES (1, '数学', '王老师', 30, 85.0);  
-- 假设其他数据以类似方式插入

(1) 删除2号课程的信息

DELETE FROM Course  
WHERE Cno = 2;

(2) 修改课程名称

UPDATE Course  
SET Cname = '数据库原理'  
WHERE Cname = '数据库';

(3) 提高60至70分之间的学生成绩

UPDATE Course  
SET Grade = Grade * 1.10  
WHERE Grade BETWEEN 60 AND 70;

(4) 给李勇的数据库原理课程成绩加5分

UPDATE Course  
SET Grade = Grade + 5  
WHERE Sno = (SELECT Sno FROM Student WHERE Sname = '李勇') AND Cname = '数据库原理';

(5) 求选课人数和平均成绩并存入新表

-- 创建新表  
CREATE TABLE CourseStats (  
    Cno INT,  
    StudentCount INT,  
    AvgGrade DECIMAL(5, 2)  
);  
  
-- 插入数据  
INSERT INTO CourseStats (Cno, StudentCount, AvgGrade)  
SELECT Cno, COUNT(Sno), AVG(Grade)  
FROM Course  
GROUP BY Cno;

(6) 创建男同学信息视图并测试

-- 创建视图  
CREATE VIEW male_student AS  
SELECT *  
FROM Student  
WHERE Ssex = '男'  
WITH CHECK OPTION;  
  
-- 查询视图  
SELECT * FROM male_student;  
  
-- 尝试插入女生信息（将失败）  
-- INSERT INTO male_student (Sno, Sname, Ssex, Sage, Sdept) VALUES (999, '小红', '女', 22, '外语系');  
  
-- 尝试插入男生信息（将成功）  
-- INSERT INTO male_student (Sno, Sname, Ssex, Sage, Sdept) VALUES (998, '小王', '男', 23, '计算机系');  
  
-- 查询视图和基表以验证  
SELECT * FROM male_student;  
SELECT * FROM Student;

(7) 创建女生信息视图并测试

-- 创建视图  
CREATE VIEW female_student AS  
SELECT *  
FROM Student  
WHERE Ssex = '女'  
WITH CHECK OPTION;  
  
-- 尝试插入男生信息（将失败）  
-- INSERT INTO female_student (Sno, Sname, Ssex, Sage, Sdept) VALUES (997, '小赵', '男', 24, '管理系');  
  
-- 尝试插入女生信息（将成功）  
-- INSERT INTO female_student (Sno, Sname, Ssex, Sage, Sdept) VALUES (996, '小孙', '女', 22, '中文系');  
  
-- 查询视图和基表以验证  
SELECT * FROM female_student;  
SELECT * FROM Student;

(8) 创建学生选修课程数目和平均成绩的视图

CREATE VIEW view_Score AS  
SELECT   
    Sno,  
    COUNT(Cno) AS NumberC,  
    AVG(Grade) AS AvgScore  
FROM   
    Course  
GROUP BY   
    Sno;

(9) 创建显示学生姓名、课程名和成绩的视图

CREATE VIEW sname_cname AS  
SELECT   
    Student.Sname,  
    Course.Cname,  
    Course.Grade  
FROM   
    Student  
JOIN   
    Course ON Student.Sno = Course.Sno;

(10) 查找平均成绩高于80分的学生学号

SELECT Sno  
FROM view_Score  
WHERE AvgScore > 80;

(11) 更新视图view_Score中的平均成绩

UPDATE view_Score  
SET AvgScore = 95  
WHERE Sno = 95001; -- 假设95001是特定学生的学号

(12) 更新视图male_student中的年龄并验证

UPDATE male_student  
SET Sage = 21  
WHERE Sname = '李勇';  
  
-- 查询视图以验证  
SELECT * FROM male_student WHERE Sname = '李勇';  
  
-- 查询基表以验证  
SELECT * FROM Student WHERE Sname = '李勇';