1、数据库大作业11.针对习题 10 的四个表，用 SQL 语言完成以下各项操作：（1）给学生表增加一属性 Nation（民族） ，数据类型为 Varchar（20） ；ALTER TABLE dbo.Student ADD Nation VarChar(20);SElECT *FROM dbo.Student;（2）删除学生表中新增的属性 Nation；ALTER TABLE dbo.Student DROP COLUMN Nation;SElECT *FROM dbo.Student;（3）向成绩表中插入记录（“2001110” ， “3”，80） ；INSERT INTO dbo.Grade
2、 VALUES (2001110,3,80);SElECT *FROM dbo.Grade;（4）将学号为“2001110”的学生的成绩修改为 70 分；UPDATE dbo.Grade SET Gmark = 70WHERE Sno = 2001110;SElECT *FROM dbo.Grade;（5）删除学号为“2001110”的学生的成绩记录；DELETE FROM dbo.Grade WHERE Sno = 2001110;SElECT *FROM dbo.Grade;（6）在学生表的 Clno 属性上创建一个名为 IX_Class 的索引，以班级号的升序排序；CREATE INDE
3、X IX_CLASS ON dbo.Student(Clno);SELECT *FROM dbo.Student;（7）删除 IX_Class 索引。DROP INDEX Student.IX_CLASS; SELECT *FROM dbo.Student;12. 针对习题 10 的四个表，用 SQL 语言完成以下各项查询：（1）找出所有被学生选修了的课程号；SELECT DISTINCT Cno FROM Grade,StudentWHERE Grade.Sno = Student.Sno;（2）找出 01311 班女学生的个人信息；SELECT * FROM StudentWHERE Ss
4、ex = 女 AND Clno = 01311;（3）找出 0311 班和 01312 班的学生姓名、性别、出生年份；SELECT Sname,Ssex,(2014-Sage) AS 出生年份 FROM StudentWHERE Clno IN(01311,01312);（4）找出所有姓李的学生的个人信息；SELECT * FROM StudentWHERE Sname LIKE 李%;（5）找出学生李勇所在班级的学生人数；SELECT COUNT(*) AS 李勇班级人数 FROM StudentWHERE Clno IN(SELECT ClnoFROM StudentWHERE Sname
5、 = 李勇); （6）找出课程名为操作系统的平均成绩、最高分、最低分；SELECT MAX(Gmark) AS 最高分,MIN(Gmark) AS 最低分 ,AVG(Gmark) AS 平均成绩 FROM Grade g,Course cWHERE g.Cno IN(SELECT c.CnoFROM CourseWHERE c.Cname = 操作系统); （7）找出选修了课程的学生人数；SELECT COUNT(DISTINCT Sno) AS 选修了课程的学生人数FROM Grade;（8）找出选修了课程操作系统的学生学号和姓名；SELECT S.Sno,S.SnameFROM Stude
6、nt S,Grade GWHERE S.Sno IN(SELECT G.SnoFROM GradeWHERE G.Cno = (SELECT CnoFROM CourseWHERE Cname = 操作系统 ); （9）找出 2000 级计算机软件班的成绩为空的学生姓名。SELECT S.SnameFROM Student SWHERE S.Sno NOT IN(SELECT SnoFROM Grade) AND S.Clno = (SELECT ClnoFROM ClassWHERE Inyear = 2000 AND Speciality = 计算机软件 ); 13. 针对习题 10 的四
7、个表，用 SELECT 的嵌套查询完成以下各项查询：（1）找出与李勇在同一个班级的学生信息；SELECT *FROM StudentWHERE Clno = (SELECT ClnoFROM StudentWHERE Sname = 李勇) AND Sname NOT IN(李勇 ); （2）找出所有与李勇有相同选修课程的学生信息；SELECT *FROM StudentWHERE Sno IN(SELECT SnoFROM GradeWHERE Grade.Cno IN(SELECT CnoFROM GradeWHERE Sno = (SELECT SnoFROM StudentWHERE
8、Sname = 李勇) AND Sname NOT IN(李勇); （3）找出年龄介于学生李勇与 25 岁之间的学生信息（已知李勇的年龄小于 25 岁） ；SELECT *FROM StudentWHERE Sage (SELECT SageFROM StudentWHERE Sname = 李勇); （4）找出选修了课程操作系统的学生学号与姓名；SELECT Sno,SnameFROM StudentWHERE Sno IN(SELECT SnoFROM GradeWHERE Cno = (SELECT CnoFROM CourseWHERE Cname = 操作系统 ); （5）找出没有选
9、修 1 号课程的所有学生姓名；SELECT SnameFROM StudentWHERE Sno NOT IN(SELECT SnoFROM GradeWHERE Cno = 1); （6）找出选修了全部课程的学生姓名。SELECT SnameFROM StudentWHERE NOT EXISTS(SELECT *FROM CourseWHERE NOT EXISTS(SELECT *FROM GradeWHERE Sno= Student.SnoAND Cno= Course.Cno);14. 针对习题 10 的四个表，用 SQL 语言完成以下各项查询：（1）查询选修了 3 门以上的课程的
10、学生学号及其成绩，并按成绩的降序排列；SELECT Sno,GmarkFROM GradeWHERE Cno = 3ORDER BY Gmark DESC;（2）查询全体学生信息，要求查询结果按班级号升序排列同一班级按年龄降序排列；SELECT *FROM StudentORDER BY Clno,Sage DESC;（3）求每个课程号及相应的选课人数；SELECT Cno,COUNT(Cno) AS 选修人数FROM GradeGROUP BY Cno;（4）查询选修了 3 门以上课程的学生学号。SELECT Sno,COUNT(Sno) AS 选修课程数FROM GradeGROUP BY
11、 Sno HAVING(COUNT(Sno)3); 15. 针对习题 10 的四个表，用 SQL 语言完成以下各项操作：（1）将 01311 班的全体学生的成绩置零；UPDATE GradeSET Gmark = 0WHERE Sno IN(SELECT SnoFROM StudentWHERE Clno = 01311); （2）删除 2001 级计算机软件的全体学生的选课记录；DELETE FROM GradeWHERE Sno IN (SELECT SnoFROM StudentWHERE Clno = (SELECT ClnoFROM ClassWHERE Inyear = 2001
12、AND Speciality = 计算机软件); （3）学生李勇已退学，从数据库中删除有关他的记录；UPDATE ClassSET Monitor = NULLWHERE Clno = (SELECT ClnoFROM StudentWHERE Sname = 李勇)DELETE FROM GradeWHERE Sno = (SELECT SnoFROM StudentWHERE Sname = 李勇) DELETE FROM StudentWHERE Sname = 李勇;（4）对每个班，求学生的平均年龄，并把结果存入数据库。CREATE VIEW V_SagAS SELECT Clno,A
13、VG(Sage) AS SagFROM StudentGROUP BY Clno; 16.视图操作：（1）建立 01311 班选修了 1 号课程的学生视图 Stu_01311_1； CREATE VIEW Stu_01311_1 AS SELECT *FROM StudentWHERE Sno IN(SELECT SnoFROM GradeWHERE Cno = 1) AND Clno = 01311;（2）建立 01311 班选修了 1 号课程并且不及格的学生视图 Stu_01311_2；CREATE VIEW Stu_01311_2AS SELECT *FROM StudentWHERE
14、Sno IN(SELECT SnoFROM GradeWHERE Cno = 1 AND Gmark 1990; （5）查询 01311 班选修了 1 号课程并且成绩不及格的学生的学号、姓名、出生年份。SELECT *FROM Stu_yearWHERE Sno IN (SELECT SnoFROM Stu_01311_2); 第四章 触发器11.对于成绩管理数据库,为成绩管理数据库中的 Student 表创建一触发器 :当向表中插入或删除记录时,修改 Class 表中相应的班级人数 CREATE TRIGGER fri_stuON StudentAFTER INSERT,DELETEASIF UPDATE(Sno)UPDATE ClassSET Number = Number + 1WHERE Clno = (SELECT ClnoFROM INSERTED)ELSEUPDATE ClassSET Number = Number - 1WHERE Clno = (SELECT ClnoFROM DELETED);第五章 存储过程10.创建一存储过程:根据学生学号查询该学生所有选修课成绩,学号作为输入参数CREATE PROCEDURE proc_Stustu_Sno char(7)ASSELECT Gmark,CnoFROM GradeWHERE Sno = Stu_Sno;
