数据库实验4-查询

1实验四表的查询操作(4学时)【实验目的】了解SQL语言的使用，进一步理解关系运算，巩固数据库的基础知识。【实验要求】掌握利用Select语句进行各种查询操作：单表查询、多表连接及查询、嵌套查询、集合查询等。【实验内容】在实验三创建并插入数据的表（Student，Course，SC，Teacher，TC）的基础上，完成以下操作。1.对实验步骤中所给示例进行验证。2.参考所给示例，完成下列各种查询操作。（1）将教师‘罗莉’的名字改为‘罗莉莉’。updateTeachersetTname=’罗莉莉’whereTname=’罗莉’（2）将两个同学（数据自己临时设置，用后即删除）的两门课程的成绩以运行sql程序文件的形式插入score表中。该题用以验证、理解和掌握关系模型的完整性规则；InsertintoSC(Sno,Cno,Grade)values(‘04563002’,’C003’,’80’)InsertintoSC(Sno,Cno,Grade)values(‘04563003’,’C004’,’87’)DeletefromSCwhereSno=‘04563002’andCno=’C003’andGrade=’80’DeletefromSCwhereSno=‘04563003’andCno=’C004’andGrade=’87’（3）求每门课的平均成绩，并把结果存入average表（自行设计并创建）；Createtableaverage(Cnovarchar(8)primarykey,Averagefloat(2));Insertintoaverage(Cno,average)selectCno,avg(Grade)fromscgroupbyCno;（4）将学生“马丽”的年龄改为24；UpdateStudentset2014-year(Sbirth)年龄whereSname=’马丽’（5）将所有学生的zipcode属性列值填补上；UpdateStudentsetSzipcode=221300（6）将average表中的所有课程的平均成绩置零；UpdateaveragesetAverage=0（7）删除average表中的课程号为‘c007’的平均成绩记录；DeletefromaveragewhereCno=‘c007’（8）删除所有average表中平均成绩记录；Deletefromaverage（9）建立一个临时学生信息表（tstudent），删除该表中的学号含‘101’的所有学生记录。createtabletstudent(Snochar(8)primarykey,Snamevarchar(8)unique);DeletefromtstudentwhereSnolike'001011%';’（10）查询全体学生的学号与姓名；selectSno,SnamefromStudent（11）查询全体学生的学号、姓名、所属系；selectSno,Sname,SdeptfromStudent（12）查询全体学生的详细记录；select*fromStudent（13）查询全体学生的姓名及其年龄；select2014-year(Sbirth)年龄,Snamefrom2Student（14）查询全体学生的姓名、出生年份；selectSname,SbirthfromStudent（15）查询所有修过课的学生的学号；selectSnofromSC（16）查询“计算机系”班全体学生名单；select*fromStudentwhereSdept=’计算机系’（17）查询查询所有年龄在23岁以下的学生姓名及其年龄；selectSname,selectSname,2014-year(Sbirth)年龄fromStudentwhere2014-year(Sbirth)=23（18）查询考试成绩有不及格的学生的学号；selectSnofromSCwhereGrade60（19）查询年龄在20至22岁之间的学生姓名、系和年龄；selectSname,Sdept,2014-year(Sbirth)年龄fromStudentwhere2014-year(Sbirth)between20and22（20）查询年龄不在20至22岁之间的学生姓名、系和年龄；selectSname,Sdept,2014-year(Sbirth)年龄fromStudentwhere2014-year(Sbirth)notbetween20and22（21）查询“计算机系”和“电商系”的学生的姓名；selectSnamefromStudentwhereSdeptin('计算机系','电商系')（22）查询既不是“计11”也不是“计61”班的学生的姓名和班级信息；selectSname,SclassfromStudentwhereSclassnotin('计11','计61')（23）查询学号为“04262002”的学生的详细情况；Select*fromStudentwhereSno='04262002'（24）查询学号以“04262”打头的学生信息；Select*fromStudentwhereSnolike'04262%'（25）查询所有姓“张”学生的学号、姓名、性别、年龄；SelectSno,Sname,Ssex,2014-year(Sbirth)年龄fromStudentwhereSnamelike'张%'（26）查询名字中第二个字有“海”字的学生的学号、姓名、性别、年龄；SelectSno,Sname,Ssex,2014-year(Sbirth)年龄fromStudentwhereSnamelike'_海%'（27）查询所有不姓“刘”学生的姓名；SelectSnamefromStudentwhereSnamenotlike‘刘%’（28）查询课程号以“C”开头的最后两个字母为“05”的课程号和课程名；SelectCno,CnamefromCoursewhereCnolike'C%05'（29）某些学生选修某门课程后没有参加考试，所以有选修课记录，但没有考试成绩，试查找缺少考试成绩的学生和相应的课程号；selectSno,CnofromSCwhereGradeisnull（30）查找全部有成绩记录的学生学号、课程号；selectSno,CnofromSCwhereGradeisnotnull（31）查找“计算机系”年龄在22岁以下的学生学号、姓名；selectSname,SnofromStudentwhereSdept='计算机系'and2014-year(Sbirth)20（32）查找选修了“C001”号课程的学生学号及其成绩，查询结果按分数降序排序；selectSno,GradefromSCwhereCno='C001'orderbyGradedesc（33）查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列；select*fromStudentorderbySdept,2014-year(Sbirth)desc（34）查询学生总人数；Selectcount(*)fromStudent（35）查询选修了课程的学生人数；Selectcount(distinctSno)fromSC（36）在所有课程中查询最高分的学生学号和成绩；SelectSno,GradefromSCwhereGrade=(selectMAX(Grade)fromSC)（37）查询学习“C001”课程的学生最高分数；3selectMAX(Grade)fromSCwhereCno='C001'（38）计算各个课程号与相应的选课人数；SelectCno,count(Sno)fromSCgroupbyCno（39）查询“计算机系”选修了两门课程以上的学生学号、姓名；selectSno,SnamefromStudentwhereSdept='计算机系'andSnoin(selectSnofromSCgroupbySnohavingcount(*)2)（40）自然连接student和score表；SelectStudent.Sno,Sname,Ssex,Sdept,Sclass,Sremark,Saddress,Szipcode,Sphone,Semail,Sbirth,Cno,GradefromStudent,SCwhereStudent.Sno=SC.Sno（41）使用自身连接查询每一门课程的间接先行课（即先行课的先行课）Selectfirst.Cno,second.CpnofromCoursefirst,Coursesecondwherefirst.Cpno=second.Cno（42）使用复合条件连接查询选修“c001”号课程且成绩在90分以上的所有同学；SelectStudent.Sno,SnamefromStudent,SCwhereStudent.Sno=SC.SnoAndSC.Cno='C001'andSC.Grade90（43）使用复合条件连接查询每个学生选修的课程名及其成绩；SelectStudent.Sno,Sname,Cname,GradefromStudent,Course,SCwhereStudent.Sno=SC.SnoandCourse.Cno=SC.Cno（44）查询选修了全部课程的学生；selectSnamefromStudentwherenotexists(select*fromCoursewherenotexists(select*fromSCwhereSno=Student.SnoandCno=Course.Cno))（45）查询所有选修了C001号课程的学生学号、姓名；SelectSno,SnamefromStudentwhereSnoin(selectSnofromSCwhereCno='C001')（46）查询选修了课程C001或c007的学生学号、姓名；SelectSno,SnamefromStudentwhereSnoin(selectSnofromSCwhereCno='C001'orCno='C007')（47）查询“计算机系”的学生及年龄不大于23岁的学生；select*fromStudentwhereSdept='计算机系'and2014-year(Sbirth)23（48）查询既选修了课程C001又选修了课程c007的所有学生学号、姓名；SelectSno,SnamefromStudentwhereSnoin(selectSnofromSCwhereCno='C001'andCno='C002')（49）查询选修了课程名为“数据库原理”的学生的学号、姓名、性别、年龄；selectStudent.Sno,Ssex,SnamefromStudent,SC,CoursewhereStudent.Sno=SC.SnoandSC.Cno=Course.CnoandCourse.Cname='数据库原理'（50）查询其他班中比“计算机系”所有学生年龄都小的学生名单；selectSname,2014-year(Sbirth)年龄fromStudentwhere2014-year(Sbirth)all(select2014-year(Sbirth)年龄fromStudentwhereSdept='计算机系')andSdept'计算机系'（51）查询与“夏天”在同一个系学习的学生学号、姓名、性别、年龄；selectSno,Sname,Ssex,2014-year(Sbirth)年龄fromStudentwhereSdeptin4(selectSdeptfromStudentwhereSname='夏天')（52）建立“计算机系”学生的视图1；createviewIS_StudentasselectSno,Sname,2014-year(Sbirth)年龄fromStudentwhereSdept='计算机系'（53）建立“计算