数据库课后作业

精品文档，仅供学习与交流，如有侵权请联系网站删除数据库大作业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 VALUES ('2001110','3',80);SElECT *FROM dbo.Grade;（4）将学号为“2001110”的学生的成绩修改为70分；UPDATE dbo.Grade SET Gmark = 70 WHERE Sno = '2001110'SElECT *FROM dbo.Grade;（5）删除学号为“2001110”的学生的成绩记录；DELETE FROM dbo.Grade WHERE Sno = '2001110'SElECT *FROM dbo.Grade;（6）在学生表的Clno属性上创建一个名为IX_Class的索引，以班级号的升序排序；CREATE INDEX 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,Student WHERE Grade.Sno = Student.Sno;（2）找出01311班女学生的个人信息；SELECT * FROM StudentWHERE Ssex = '女' 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 Clno FROM Student WHERE Sname = '李勇'); （6）找出课程名为操作系统的平均成绩、最高分、最低分；SELECT MAX(Gmark) AS '最高分' ,MIN(Gmark) AS '最低分' ,AVG(Gmark) AS '平均成绩'FROM Grade g,Course cWHERE g.Cno IN(SELECT c.Cno FROM Course WHERE c.Cname = '操作系统'); （7）找出选修了课程的学生人数；SELECT COUNT(DISTINCT Sno) AS '选修了课程的学生人数'FROM Grade;（8）找出选修了课程操作系统的学生学号和姓名；SELECT S.Sno,S.SnameFROM Student S,Grade GWHERE S.Sno IN( SELECT G.Sno FROM Grade WHERE G.Cno = ( SELECT Cno FROM Course WHERE Cname = '操作系统'); （9）找出2000级计算机软件班的成绩为空的学生姓名。SELECT S.SnameFROM Student SWHERE S.Sno NOT IN( SELECT Sno FROM Grade) AND S.Clno = (SELECT Clno FROM Class WHERE Inyear = '2000' AND Speciality = '计算机软件'); 13. 针对习题10的四个表，用SELECT的嵌套查询完成以下各项查询：（1）找出与李勇在同一个班级的学生信息；SELECT *FROM StudentWHERE Clno = (SELECT Clno FROM Student WHERE Sname = '李勇') AND Sname NOT IN('李勇'); （2）找出所有与李勇有相同选修课程的学生信息；SELECT *FROM StudentWHERE Sno IN( SELECT Sno FROM Grade WHERE Grade.Cno IN( SELECT Cno FROM Grade WHERE Sno = ( SELECT Sno FROM Student WHERE Sname = '李勇') AND Sname NOT IN('李勇'); （3）找出年龄介于学生李勇与25岁之间的学生信息（已知李勇的年龄小于25岁）；SELECT *FROM StudentWHERE Sage < 25 AND Sage > (SELECT Sage FROM Student WHERE Sname = '李勇'); （4）找出选修了课程操作系统的学生学号与姓名；SELECT Sno,SnameFROM StudentWHERE Sno IN(SELECT Sno FROM Grade WHERE Cno = (SELECT Cno FROM Course WHERE Cname = '操作系统'); （5）找出没有选修1号课程的所有学生姓名；SELECT Sname FROM Student WHERE Sno NOT IN(SELECT Sno FROM Grade WHERE Cno = '1'); （6）找出选修了全部课程的学生姓名。SELECT Sname FROM Student WHERE NOT EXISTS(SELECT * FROM Course WHERE NOT EXISTS(SELECT * FROM Grade WHERE Sno= Student.Sno AND Cno= Course.Cno);14. 针对习题10的四个表，用SQL语言完成以下各项查询：（1）查询选修了3门以上的课程的学生学号及其成绩，并按成绩的降序排列；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 Sno HAVING(COUNT(Sno)>3); 15. 针对习题10的四个表，用SQL语言完成以下各项操作：（1）将01311班的全体学生的成绩置零； UPDATE GradeSET Gmark = 0WHERE Sno IN(SELECT Sno FROM Student WHERE Clno = '01311'); （2）删除2001级计算机软件的全体学生的选课记录； DELETE FROM GradeWHERE Sno IN (SELECT Sno FROM Student WHERE Clno = (SELECT Clno FROM Class WHERE Inyear = '2001' AND Speciality = '计算机软件'); （3）学生李勇已退学，从数据库中删除有关他的记录；UPDATE ClassSET Monitor = NULLWHERE Clno = (SELECT Clno FROM Student WHERE Sname = '李勇')DELETE FROM GradeWHERE Sno = (SELECT Sno FROM Student WHERE Sname = '李勇') DELETE FROM StudentWHERE Sname = '李勇'（4）对每个班，求学生的平均年龄，并把结果存入数据库。CREATE VIEW V_SagAS SELECT Clno,AVG(Sage) AS 'Sag' FROM Student GROUP BY Clno; 16.视图操作：（1）建立01311班选修了1号课程的学生视图Stu_01311_1； CREATE VIEW Stu_01311_1 AS SELECT * FROM Student WHERE Sno IN(SELECT Sno FROM Grade WHERE Cno = 1) AND Clno = '01311'（2）建立01311班选修了1号课程并且不及格的学生视图Stu_01311_2；CREATE VIEW Stu_01311_2AS SELECT * FROM Student WHERE Sno IN(SELECT Sno FROM Grade WHERE Cno = 1 AND Gmark < 60) AND Clno = '01311'（3）建立视图Stu_year，由学生学号、姓名、出生年份组成；CREATE VIEW Stu_yearAS SELECT Sno AS '学号',Sname AS '姓名',(2014-Sage) AS '出生年份' FROM Student;（4）查询1990年以后出生的学生姓名；SELECT SnameFROM dbo.Stu_yearWHERE '出生年份' > '1990' （5）查询01311班选修了1号课程并且成绩不及格的学生的学号、姓名、出生年份。SELECT *FROM Stu_yearWHERE Sno IN (SELECT Sno FROM Stu_01311_2); 第四章 触发器11.对于成绩管理数据库,为成绩管理数据库中的Student表创建一触发器:当向表中插入或删除记录时,修改Class表中相应的班级人数 CREATE TRIGGER fri_stu ON Student AFTER INSERT,DELETE AS IF UPDATE(Sno) UPDATE Class SET Number = Number + 1 WHERE Clno = (SELECT Clno FROM INSERTED) ELSE UPDATE Class SET Number = Number - 1 WHERE Clno = (SELECT Clno FROM DELETED);第五章 存储过程10.创建一存储过程:根据学生学号查询该学生所有选修课成绩,学号作为输入参数 CREATE PROCEDURE proc_Stu stu_Sno char(7) AS SELECT Gmark,Cno FROM Grade WHERE Sno = Stu_Sno;【精品文档】第 6 页