数据库作业2

上机验证书上第三章例3.69到例3.96的所有例程，分别给出代码，对应将运行结果截图形成一个附件提交。注意压缩一下截图的大小。

例3.69 将一个新学生元组（学号：200215128,姓名：陈冬,性别：男,所在系：IS,年龄：18岁）插入到Student表中

INSERT INTO Student(Sno,Sname,Ssex,Sdept,Sage) VALUES ('200215128','陈冬','男','IS',18);

例3.70 将学生张成民的信息插入到Student表中

INSERT INTO Student VALUES ('200215125','张国庆','男',18,'CS');

例3.71 插入一条选课记录('200215125','0202')

INSERT INTO SC(Sno,Cno) VALUES ('200215125','0202');

例3.72 对每一个系,求学生的平均年龄,并把结果存入数据库

第一步：建表

CREATE TABLE Dept_age (Sdept CHAR(15),Avg_age SMALLINT);

第二步：插入数据

INSERT INTO Dept_age(Sdept,Avg_age) SELECT Sdept,AVG(Sage) FROM Student GROUP BY Sdept;

例3.73 将学生201215126的年龄改为22岁

UPDATE Student SET Sage=22 WHERE Sno='200215126';

例3.74 将所有学生的年龄增加1岁

UPDATE Student SET Sage=Sage+1;

例3.75 将计算机系全体学生的成绩置零

UPDATE SC SET Grade=0 WHERE Sno IN(SELECT Sno FROM Student WHERE Sdept='CS');

例3.76 删除学号为200215128的学生记录

DELETE FROM Student WHERE Sno='200215128';

例3.77 删除所有的学生选课记录

DELETE FROM SC;

例3.78 删除计算机科学系所有学生的选课记录

DELETE FROM SC WHERE Sno IN(SELECT Sno FROM Student WHERE Sdept='CS');

例3.79 向SC表中插入一个元组，学生号是"200215131"，课程号是"1111"，成绩为空

INSERT INTO Sc(Sno,Cno,Grade) VALUES('200215131','1111',NULL);

或

INSERT INTO Sc(Sno,Cno) VALUES('200215131','1111');

例3.80 将Student表中学生号为"200215131"的学生所属的系改为空值

UPDATE student SET sdept=NULL WHERE Sno='200215131';

例3.81 从Student表中找出漏填了数据的学生信息

SELECT * FROM student WHERE Sname IS NULL OR Ssex IS NULL OR Sage IS NULL OR Sdept IS NULL;

例3.82 找出选修"0207"号课程的不及格的学生

SELECT sno FROM SC WHERE Grade<60 AND Cno='0207';

例3.83 找出选修"0207"号课程的不及格的学生以及缺考的学生

SELECT sno FROM SC WHERE Grade<60 AND Cno='0207' UNION SELECT Sno FROM SC WHERE Grade IS NULL AND Cno='0207';

或

SELECT sno FROM SC WHERE Cno='0207' AND (Grade<60 OR Grade IS NULL);

例3.84 建立信息系学生的视图

CREATE VIEW IS_Student AS SELECT Sno,Sname,Sage FROM Student WHERE Sdept='IS';

例3.85 建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生

CREATE VIEW IS_Student AS SELECT Sno,Sname,Sage FROM Student WHERE Sdept='IS' WITH CHECK OPTION;

例3.86 建立信息系选修了0202号课程的学生的视图（包括学号、姓名、成绩）

CREATE VIEW IS_S0202(Sno,Sname,Grade) AS SELECT Student.Sno,Sname,Grade FROM Student,Sc WHERE Sdept='IS'and Student.Sno=Sc.Sno AND Sc.Cno='0202';

例3.87 建立信息系选修了0202号课程且成绩在90分以上的学生的视图

CREATE VIEW IS_S0204 AS SELECT Sno,Sname,Grade FROM IS_S0202 WHERE Grade>=90;

例3.88 定义一个反映学生出生年份的视图

CREATE VIEW BT_S(Sno,Sname,Sbirth) AS SELECT Sno,Sname,2014-Sage FROM Student;

例3.89 将学生的学号以及平均成绩定义为一个视图

CREATE VIEW S_G(Sno,Gavg) AS SELECT Sno,Avg(Grade) FROM Sc GROUP BY Sno;

例3.90 将Student表中所有女生记录定义为一个视图

CREATE VIEW F_Student(F_sno,name,sex,age,dept) AS SELECT * FROM Student WHERE Ssex='女';

例3.91 删除视图BT_S和视图IS_S1

DROP VIEW BT_S;
DROP VIEW IS_S1;

例3.92 在信息系学生的视图中找出年龄小于20岁的学生

SELECT Sno,Sage FROM IS_Student WHERE Sage<20;

例3.93 查询选修了0202号课程的信息系的学生

SELECT IS_Student.Sno,Sname FROM IS_Student,Sc WHERE IS_Student.Sno=Sc.Sno AND Sc.Cno='0202';

例3.94 在S_G视图中查询平均成绩在70分以上的学生学号和平均成绩

SELECT Sno,Avg(grade) FROM Sc GROUP BY Sno HAVING Avg(grade)>=70;

或

SELECT Sno,AVG(Grade) FROM Sc GROUP BY Sno HAVING AVG(Grade)>=70;

或

SELECT * FROM (SELECT Sno,Avg(grade) FROM Sc GROUP BY Sno) AS S_G(Sno,Gavg) WHERE Gavg>=70;

在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩

SELECT Sno,Avg(grade) FROM Sc GROUP BY Sno HAVING Avg(grade)>=90;

例3.95 将信息系学生视图IS_Student中学号为"200215131"的学生姓名改为"刘辰"

UPDATE IS_Student SET Sname='刘辰' WHERE Sno='200215131';

转换后的更新语句为

UPDATE Student SET Sname='刘辰' WHERE Sno='200215131' AND Sdept='IS';

例3.96 向信息系学生视图IS_Student中插入一个新的学生记录，其中学号为"201215129"，姓名为"赵新"，年龄为20岁

INSERT INTO IS_Student VALUES('201215129','赵新',20);

转换为对基本表的更新

INSERT INTO Student(Sno,Sname,Sage,Sdept) VALUES('201215129','赵新','20','IS');