数据库表的查询操作实践演练(实验三)
继前两次的实验,本次实验以熟练掌握利用select语句进行各种查询操作:单表查询、多表连接及查询、嵌套查询、集合查询等,巩固数据库查询操作。
下面就跟着小编一起练习吧!
在实验一创建并插入数据的表(Student,Course,SC,Teacher,TC)的基础上,完成以下操作。
(1)将教师‘罗莉'的名字改为‘罗莉莉'。
updateTeachersettname='罗莉莉'wheretname='罗莉'
(2)将两个同学(数据自己临时设置,用后即删除)的两门课程的成绩以运行sql程序文件的形式插入score表中。该题用以验证、理解和掌握关系模型的完整性规则;
插入:
insertintoScore(sno,cno,grade)values('04261006','C003','64') insertintoScore(sno,cno,grade)values('04261007','C004','79')
查询:
selectsno学号,cno课程号,grade分数fromScorewheresno=04261006orsno=04261007;
删除:
deletefromScorewheresno=04261006orsno=04261007;
(3)求每门课的平均成绩,并把结果存入average表(自行设计并创建);
CREATETABLEaverage ( cnoCHAR(8), avscorenumeric(5,2), constrainta1primarykey(cno), constrainta2foreignkey(cno)referencesCourse(cno), ) insertintoaverage(cno,avscore) selectdistinctcno,avg(grade)fromScoregroupbycno
(4)将学生“马丽”的年龄改为24;
UpdateStudentset2014-year(Sbirth)年龄whereSname='马丽'
(5)将所有学生的szipcode属性列值填补上;
updateStudentsetszipcode='221000'
(6)将average表中的所有课程的平均成绩置零;
updateaveragesetavscore='0'
(7)删除average表中的课程号为‘C007'的平均成绩记录;
deletefromaveragewherecno='C007'
(8)删除所有average表中平均成绩记录;
deletefromaverage;
(9)建立一个临时学生信息表(tstudent),删除该表中的学号含‘101'的所有学生记录。
create table tstudent (Sno char(8) primary key, Sname varchar(8) unique); Delete from tstudent where Sno like'001011%';
(10)查询全体学生的学号与姓名;
selectsno学号,sname姓名fromStudent
(11)查询全体学生的学号、姓名、所属系;
selectsno学号,sname姓名,sdept系fromStudent
(12)查询全体学生的详细记录;
select*fromStudent
(13)查询全体学生的姓名及其年龄;
selectsname姓名,2014-year(sbirth)年龄fromStudent
(14)查询全体学生的姓名、出生年份;
selectsname姓名,year(sbirth)出生年份fromStudent
(15)查询所有修过课的学生的学号;
selectdistinctsnofromScore selectdistinctstudent.snofromStudent,ScorewhereStudent.sno=Score.snoandScore.grade>0;
(16)查询“计算机系”班全体学生名单;
selectsno,snamefromStudentwheresdept='计算机系'
(17)查询查询所有年龄在23岁以下的学生姓名及其年龄;
selectsname姓名,2014-year(sbirth)年龄fromStudentwhere2014-year(sbirth)<23;
(18)查询考试成绩有不及格的学生的学号;
selectdistinctsnofromScorewheregrade<60;
(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)查询“计算机系”和“电商系”的学生的姓名;
selectsnamefromStudentwheresdept='计算机系'orsclass='电商系'
(22)查询既不是“计11”也不是“计61”班的学生的姓名和班级信息;
selectsname,sclassfromStudentwheresclassnotin('计','计'); (23)查询学号为“04262002”的学生的详细情况; [code]selectstudent.sno,sname,ssex,2014-year(sbirth),sclass,gradefromStudent,ScorewhereStudent.sno=Score.snoandStudent.sno='04262002';
(24)查询学号以“04262”打头的学生信息;
select*fromStudentwheresnolike'04262%'
(25)查询所有姓“张”学生的学号、姓名、性别、年龄;
selectsno学号,sname姓名,ssex性别,2011-year(sbirth)年龄fromStudentwheresnamelike'王%'
(26)查询名字中第二个字有“海”字的学生的学号、姓名、性别、年龄;
selectsno学号,sname姓名,ssex性别,2011-year(sbirth)年龄fromStudentwheresnamelike'_田%'
(27)查询所有不姓“刘”学生的姓名;
selectsname姓名fromStudentwheresnamenotlike'刘%'
(28)查询课程号以“C”开头的最后两个字母为“05”的课程号和课程名;
selectcno,cnamefromCoursewherecnolike'C%05'
(29)某些学生选修某门课程后没有参加考试,所以有选修课记录,但没有考试成绩,试查找缺少考试成绩的学生和相应的课程号;
selectStudent.sno,sname,cnofromStudent,ScorewhereStudent.sno=Score.snoandgradeisNULL;
(30)查找全部有成绩记录的学生学号、课程号;
selectsno,cnofromScorewheregradeisnotNULL;
(31)查找“计算机系”年龄在22岁以下的学生学号、姓名;
selectsno,snamefromStudentwheresdept='计算机系'and2014-year(sbirth)<22
(32)查找选修了“C001”号课程的学生学号及其成绩,查询结果按分数降序排序;
selectstudent.sno,gradefromstudent,ScorewhereStudent.sno=Score.snoandcno='C001'orderbygradedesc;
(33)查询全体学生情况,查询结果按所在系升序排列,对同一系中的学生按年龄降序排列;
select*fromstudentorderbysdeptasc,2014-year(sbirth)desc;
(34)查询学生总人数;
selectcount(*)人数fromStudent;
(35)查询选修了课程的学生人数;
selectcount(distinctsno)人数fromScore;
(36)在所有课程中查询最高分的学生学号和成绩;
selectsno,gradefromScorewheregrade=(selectmax(grade)fromScore)
selectdistincta.*fromScoreawherea.snoIN(selecttop1Score.snofromScorewhereScore.cno=a.cnoorderbygradedesc)
(37)查询学习“C001”课程的学生最高分数;
selectmax(grade)最高分数fromScorewherecno='C001'
(38)计算各个课程号与相应的选课人数;
selectcount(sno)选课人数fromScoregroupbycno;
(39)查询“计算机系”选修了两门课程以上的学生学号、姓名;
selectStudent.sno,snamefromStudentwhereStudent.snoin (selectStudent.snofromStudent,Scorewhere sdept='计算机系'andStudent.sno=Score.snogroupbyStudent.snohavingcount(cno)>=2);
(40)自然连接student和score表;
selectstudent.*,Score.gradefromstudent,Scorewherestudent.sno=Score.sno;
(41)使用自身连接查询每一门课程的间接先行课(即先行课的先行课)
selecta.cno,b.cpnofromCoursea,Coursebwherea.cpno=b.cno;
(42)使用复合条件连接查询选修“c001”号课程且成绩在90分以上的所有同学;
selectsname,gradefromstudent,ScorewhereStudent.sno=Score.snoandcno='C001'andgrade>=90;
(43)使用复合条件连接查询每个学生选修的课程名及其成绩;
selectStudent.sno,sname,cname,gradefromCourse,Score,StudentwhereCourse.cno=Score.cnoandstudent.sno=Score.sno;
(44)查询选修了全部课程的学生;
selectSnamefromStudentwherenotexists(select* fromCoursewherenotexists(select* fromScorewhereSno=Student.SnoandCno=Course.Cno))
(45)查询所有选修了C001号课程的学生学号、姓名;
selectstudent.sno,snamefromstudent,Scorewherestudent.sno=Score.snoandcno='C001'; (46)查询选修了课程C001或C007的学生学号、姓名; [code]selectstudent.sno,sname,cnofromstudent,Scorewherestudent.sno=Score.snoandcnoin('C001','C007');[/code]
(47)查询“计算机系”的学生及年龄不大于23岁的学生;
selectsno,sname,2014-year(sbirth)age,sclassfromstudentwheresdept='计算机系'or2014-year(sbirth)<=23;
(48)查询既选修了课程C001又选修了课程C007的所有学生学号、姓名;
selectstudent.sno,snamefromstudent,Scorewherestudent.sno=Score.snoandcno='C001'andstudent.snoin(selectstudent.snofromstudent,Scorewherestudent.sno=Score.snoandcno='C007')
(49)查询选修了课程名为“数据库原理”的学生的学号、姓名、性别、年龄;
selectstudent.sno,sname,ssex,cname,2011-year(sbirth)agefromstudent,Score,Coursewherestudent.sno=Score.snoandScore.cno=Course.cnoandcname='数据库原理';
(50)查询其他班中比“计算机系”所有学生年龄都小的学生名单;
selectsno,sname,2014-year(sbirth)agefromstudentwhere2014-year(sbirth)<(selectmin(2014-year(sbirth))fromstudentwheresclass='计61')andsclass!='计61';
(51)查询与“夏天”在同一个系学习的学生学号、姓名、性别、年龄;
selectsno,sname,ssex,2014-year(sbirth)agefromstudentwheresdept=(selectsdeptfromstudentwheresname='夏天')andsname!='夏天'
(52)建立“计算机系”学生的视图1;
createviewview_student asselectsno,sname,ssex,sbirth,sclassfromstudentwheresclass='13z网络'
(53)建立“计算机系”学生的视图2,并要求进行修改与插入时,仍须保证该视图只有“计算机系”班学生;
createviewview_student2 asselectsno,sname,ssex,sbirth,sclassfromstudentwheresclass='13z网络'withcheckoption;
(54)建立“计算机系”选修了“C001”课程的学生的视图,定义视图名为“v_cs_C001_student1”;
createviewv_cs_C001_student1 asselectstudent.sno,sname,ssex,sbirth,sclassfromStudent,Scorewhere student.sno=Score.snoandsclass='13z网络'andcno='C001';
(55)建立“计算机系”班选修了“C001”课程且成绩在90分以上的学生的视图,定义视图名为“cs_c001_student2”;
createviewcs_c001_student2 as selectstudent.sno,sname,ssex,sbirth,sclass,cnofromstudent,Scorewhere student.sno=Score.snoandcno='C001'andsclass='13z网络'andstudent.snoin(selectstudent.snofromstudent,Scorewherestudent.sno=Score.snoandgrade>90)
(56)定义一个反映学生年龄的视图,定义视图名为“v_birth_student”;
createviewv_birth_student as selectsno,sname,2014-year(sbirth)agefromstudent
(57)将学生表中所有女生记录定义为一个视图,视图名为“v_female_student”;
createviewv_female_student as select*fromstudentwheressex='女';
(58)将学生的学号及其平均成绩定义为一个视图,视图名为“v_average_student”;
createviewv_average_student as selectsno,avg(grade)avscorefromScoregroupbysno;
(59)在“计算机系”学生视图中找出年龄小于22岁的学生;
select*fromview_studentwhere2014-year(sbirth)<=22;
(60)利用视图查询“计算机系”选修了“C001”课程的学生;
select*fromv_cs_C001_student1;
(61)通过(52)中的“计算机系”视图修改某个学生的名字;
updateview_studentsetsname='王某某'wheresno=04261001;
(62)通过(53)中的“计算机系”视图,插入一个新学生记录。
insertintoview_student2(sno,sname,ssex,sbirth,sclass)values('04262004','张某某','男','1987/11/09','计');
(63)通过(53)中的“计算机系”视图,删除一个学生记录。
deletefromview_student2wheresno='04262004'andsname='张某某';
实验课结束了,相信通过本节课的实践操作,小伙伴们都对数据库表的操作有了更进一步的了解。
以上就是查询数据库表的基本操作,几乎涵盖了各种查询操作所遇到的情况,值得大家亲自操作一下,相信对大家的学习有所帮助。