数据库原理及其应用实验代码.doc

实验2 数据查询(1)查询性别为“男”的所有学生的名称并按学号升序排列。SELECT SnameFROM StudentsWHERE Ssex=男ORDER BY Sno(2)查询学生的选课成绩合格的课程成绩，并把成绩换算为积分。积分的计算公式为：1+(考试成绩-60)*0.1*Ccredit。考试成绩=60 否则=0SELECT Sno, Tno, Reports.Cno, Score, Point of Score, CONVERT(FLOAT(1), (Score-60)*0.1*Ccredit+Ccredit)FROM Courses, ReportsWHERE Score=60 AND Reports.Cno=Courses.CnoUNIONSELECT Sno, Tno, Reports.Cno, Score, Point of Score, 0FROM Courses, ReportsWHERE Reports.Cno=Courses.Cno AND (Score =3)(10)查询编号S26的学生所选的全部课程的课程名和成绩。SELECT Courses.Cname,Reports.ScoreFROM Courses,ReportsWHERE Reports.Sno=S26 AND Courses.Cno=Reports.Cno(11)查询所有选了“数据库原理及其应用”课程的学生编号和姓名。SELECT Sno,SnameFROM StudentsWHERE Sno IN(SELECT Reports.SnoFROM Reports,CoursesWHERE Reports.Cno=Courses.Cno AND Courses.Cname=数据库原理及其应用)(12)求出至少被两名学生选修的课程编号。SELECT DISTINCT X.CnoFROM Reports X,Reports YWHERE X.Cno=Y.Cno AND X.SnoY.Sno(13)查询选修了编号S26的学生所选的某个课程的学生编号。SELECT Y.SnoFROM Reports X,Reports YWHERE X.Cno=Y.Cno AND X.Sno=S26/*注：在本题中，如果要求是“查询选修了编号S26的学生所选的某个课程的其他学生编号”时，也就是不包含编号S26学生自身的情况时，在查询条件WHERE中，还需要加上条件X.Sno75(28) 检索“张三”同学不学的课程的课程号SELECT distinct cnofrom Courseswhere cno not in (select Ro from Students, Reports where Students.sno= Reports.sno and Students.sname=张三 )实验3数据更新1、insertinto Studentsvalues(S78,李迪,LD,0,男)2、select Cno,COUNT(Sno)CntSno,AVG(Score)AVGScorefrom Reports Group by Cno33、update studentsset Sno=S70where Sname=李迪4、update Teachersset Tsalary=Tsalary+5005、update Reportsset Score=Score+6where 数据库原理及其应用=(select Cnofrom Courseswhere Courses.Cno=Reports.Cno)And 刘华=(select Snofrom Studentswhere Students.Sno=Reports.Sno)6、deletefrom Students where Sname=李迪7、deletefrom Courses where Cname=JAVA8、delete from Courses where Ccredit=602、create view SCT_ViewASselect Students.Sname,Courses.Cname,Teachers.Tnamefrom Students,Courses,Teachers,Reports where Students.Sno=Reports.Sno AND Reports.Cno=Courses.Cno And Reports.Tno=Teachers.Tno 3、create view EXP_View(Sname,Cname,Score)ASselect Students.Sname,Courses.Cname,Reports.Score+5from Students,Courses,Reportswhere Students.Sno=Reports.Sno and Courses.Cno=Reports.Cno4、create view Group_View(Sno,AVGScore)ASselect Sno,AVG(Score)from Reportsgroup by Sno5、create view VV_View(Sno,CountCno,AVGScore)Asselect Sno,COUNT(Cno),AVG(Score)from CS_Viewgroup by Sno6、create view C_viewAsselect Snamefrom Students,Courses,Reportswhere Students.Sno=Reports.Sno AND Reports.Cno=Courses.Cno And Cname=数据库原理及其应用7、insertinto CS_ViewValues(S52,T02,C02,59)8、UPDATE CS_ViewSET Score=Score+10where Cno=C019、DELETEFROM CS_ViewWHERE Sno=S0310、drop view SCT_View drop view CS_View实验5 数据控制1、 grant selecton Coursesto public2、 grant insert,updateon Studentsto USER1with grant option3、 grant insert,update,select(Sno)on Reportsto USER24、grant insert,updateon Studentsto USER2with grant option5、revoke selecton Course