SQL_Server_的一些练习.doc

一、 建立S_T数据库1. 使用下面语句：CREATE DATABASE S_T2. 用菜单建立二、 用CREATE TABLE语句建立Student、Course和SC三张表（参见教材P82）1. Student(Sno, Sname, Ssex, Sage, Sdept)要求：Sno为主码，Sname取唯一值USE S_TCREATE TABLE Student (Sno VARCHAR(9) PRIMARY KEY, /* 列级完整性约束条件*/ Sname VARCHAR(20) UNIQUE, /* Sname取唯一值*/Ssex VARCHAR(2), Sage INT, Sdept VARCHAR(20) );2. Course(Cno, Cname, Cpno, Ccredit)要求：Cno是主码；Cname不能为空；Cpno为先修课程编号，参照本表的CnoCREATE TABLE Course (Cno VARCHAR(4) PRIMARY KEY, Cname VARCHAR(40) NOT NULL, Cpno VARCHAR(4) , Ccredit INT, FOREIGN KEY (Cpno) REFERENCES Course(Cno) ); 3. SC(Sno, Cno, Grade)要求：主码是Sno和Cno;Sno参照Student表中的Sno; Cno参照Course表中的CnoCREATE TABLE SC (Sno VARCHAR(9), Cno VARCHAR(4), Grade INT, PRIMARY KEY (Sno, Cno), FOREIGN KEY (Sno) REFERENCES Student(Sno), FOREIGN KEY (Cno) REFERENCES Course(Cno); 三、 用DROP TABLE语句将之前建立的三张表删除SQLServer不支持droptable.cascade语句，用户需要自己手动删除那些引用删除表的表。所以请注意删除表的顺序DROP TABLE SC;DROP TABLE Student;DROP TABLE Course;四、 用菜单建立上面要求的三张表，在关系图中建立外码参照关系。五、 修改这三张表1. 在Course表中加入一个属性列Cdept，记录开课的系名ALTER TABLE Course ADD Cdept VARCHAR(20);2. 增加课程名称必须取唯一值的约束条件ALTER TABLE Course ADD UNIQUE(Cname); 3. 取消Student表中的Sname取唯一值的约束ALTER TABLE Student DROP CONSTRAINT 索引名称; 说明：SQL SERVER不支持DROP UNIQUE，在表的设计窗口中查询索引名称，然后使用上面的语句。4. 在表Student的设计窗口中，将Sage的默认值设置为0六、 按照书上的数据，填充数据表。七、 用SQL的查询语句表达下列查询：1. 检索李勇同学所学课程的课程号和课程名。SELECT Course.Cno, CnameFROM Student, Course, SCWHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno AND Sname=李勇2. 检索年龄大于18岁的男学生的学号和姓名。SELECT Sno, SnameFROM StudentWHERE Ssex=男 AND Sage183. 检索选修了 2 号课程的女学生姓名SELECT SnameFROM Student, SCWHERE Student.Sno=SC.Sno AND Cno=2 AND Ssex=女4. 检索200215121同学不学的课程的课程号。SELECT CnoFROM CourseWHERE Cno NOT IN (SELECT Cno FROM SC WHERE Sno=200215121)或者SELECT CnoFROM CourseWHERE NOT EXISTS ( SELECT * FROM SC WHERE Course.Cno=SC.Cno AND Sno=200215121)5. 检索至少选修两门课程的学生学号。SELECT SnoFROM SCGROUP BY SnoHAVING COUNT(*)=2;或者SELECT SnoFROM StudentWHERE 2=2ORDER BY NUM DESC, Cno;10. 检索学号比刘晨同学大，而年龄比她小的学生姓名。SELECT SnameFROM StudentWHERE Sno(Select Sno FROM Student WHERE Sname=刘晨) AND Sage=2 )19. 检索李勇同学没有选修的课程的课程号。SELECT CnoFROM CourseWHERE Cno NOT IN ( SELECT Cno FROM SC, Student WHERE Student.Sno=SC.Sno AND Sname=李勇)20. 检索选修计算机系（CS）所开全部课程的学生的学号与姓名。SELECT Sno, SnameFROM StudentWHERE NOT EXISTS ( SELECT * FROM Course WHERE Cdept=CS AND NOT EXISTS ( SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=Course.Cno ) )21. 检索选修信息系统且成绩大于等于90分的计算机系（CS）的学生的姓名与学号。SELECT Sname, SnoFROM StudentWHERE Sno IN ( SELECT DISTINCT Sno FROM SC, Course WHERE SC.Cno=Course.Cno AND Grade=90 AND Cname=信息系统 )22. 查询选修3门以上课程且平均成绩大于80的学生的学号、姓名、所在系名。SELECT Sno, Sname, SdeptFROM StudentWHERE Sno IN ( SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*)=3 AND AVG(Grade)80 );23. 求计算机系（CS）所开课程的每门课程的学生平均成绩，按平均成绩从高到低排序输出。SELECT Cno, AVG(Grade)FROM SCWHERE Cno IN ( SELECT Cno FROM Course WHERE Cdept=CS )GROUP BY CnoORDER BY AVG(Grade) DESC24. 检索缺考2门以上的学生姓名。SELECT SNAMEFROM StudentWHERE Sno IN ( SELECT Sno FROM SC WHERE Grade IS NULL GROUP BY Sno HAVING count(*)=2)25. 检索缺考或不及格们数3门以上的学生学号和姓名。SELECT Sno, SNAMEFROM StudentWHERE Sno IN ( SELECT Sno FROM SC WHERE Grade IS NULL OR Grade=3)26. 检索每个学生的总学分SELECT Sno, Sum(Ccredit)FROM SC, CourseWHERE SC.Cno=Course.Cno GROUP BY Sno27. 查询既选修了数据库又选修了信息系统的学生的姓名及学号SELECT Sno, SnameFROM StudentWHERE NOT EXISTS ( SELECT * FROM Course WHERE ( Cname=数据库 OR Cname=信息系统 ) AND NOT EXISTS ( SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=Course.Cno ) )28. 查询即没有选修数据库又没有选修 操作系统的学生的姓名及学号。SELECT Sno, SnameFROM StudentWHERE Sno NOT IN ( SELECT Sno FROM SC, Course WHERE SC.Cno=Course.Cno AND ( Cname=数据库 OR Cname=操作系统 ) )八、 写出下面数据更新语句1. 将一个新学生元组（学号：200215128；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。INSERT INTO Student ( Sno, Sname, Ssex, Sdept, Sage )VALUES ( 200215128, 陈冬, 男, IS, 18 );2. 插入一条选课记录( 200215128, 1 )。INSERT INTO SC( Sno, Cno )VALUES ( 200215128, 1);3. 创建表Dept_age(Sdept, Avg_age)，求每个系学生的平均年龄，写入该表。CREATE TABLE Dept_age (Sdept VARCHAR(20) , Avg_age INT); INSERT INTO Dept_age ( Sdept, Avg_age )SELECT Sdept, AVG(Sage)FROM StudentGROUP BY Sdept; 4. 将学生200215121的年龄改为22岁。UPDATE StudentSET Sage = 22WHERE Sno = 200215121;5. 将所有学生的年龄增加1岁。UPDATE StudentSET Sage= Sage+1; 6. 将计算机科学系全体学生的成绩置零。UPDATE SCSET Grade = 0WHERE CS= (SELECT Sdept FROM Student WHERE Student.Sno = SC.Sno );7. 删除计算机科学系所有学生的选课记录DELETE FROM SCWHERE CS= (SELECT Sdept FROM Student WHERE Student.Sno = SC.Sno ); 8. 删除学号为200215128的学生记录（注意此时SC表中，已经有相关记录）。DELETE FROM SCWHERE Sno = 200215128 ;DELETE FROM StudentWHERE Sno = 200215128 ; 九、 视图练习：1. 用CREATE VIEW语句，将上面的查询练习26，创建为视图V_Totalcredit。CREATE VIEW V_Totalcredit (Sno, Tcredit)AS SELECT Sno, Sum(Ccredit) FROM SC, Course WHERE SC.Cno=Course.Cno GROUP BY Sno2. 打开视图V_Totalcredit的设计窗口，将Tcredit改名为Totcredit3. 用DROP VIEW语句，将视图V_Totalcredit删除。 DROP VIEW V_Totalcredit4. 用菜单命令，重新创建视图V_Totalcredit5. 利用视图V_Totalcredit进行查询：所有学生的学号、姓名和所修课程的总学分（要考虑有的学生没有选修课程）SELECT Sno, Sname, TotcreditFROM Student LEFT OUTER JOIN V_Totalcredit ON (Student.Sno = V_Totalcredit.Sno)十、 触发器练习创建触发器，当Student表中的数据发生变化时（插入、更新、删除），自动修改Dept_age表中的数据（提示，需要两个触发器）（1）当在Student表中插入或修改学生信息时，在Dept_age更新或插入数据CREATE TRIGGER trg_newage ON StudentAFTER INSERT, UPDATEAS BEGINUPDATE Dept_age SET Avg_age = (SELECT AVG(Sage) FROM Student WHERE Student. Sdept = Dept_age. Sdept) WHERE Sdept IN ( SELECT Sdept FROM inserted );INSERT INTO Dept_age (Sdept, Avg_age)SELECT Sdept, AVG(Sage) FROM StudentWHERE Sdept IN ( SELECT Sdept FROM inserted ) AND Sdept NOT IN (SELECT Sdept FROM Dept_age) GROUP BY Sdept; END; （2）当在Student表中删除学生信息时，在Dept_age更新或删除数据CREATE TRIGGER trg_delage ON StudentAFTER DELETE AS BEGINU