数据库试题整理.docx
《数据库试题整理.docx》由会员分享,可在线阅读,更多相关《数据库试题整理.docx(10页珍藏版)》请在冰豆网上搜索。
数据库试题整理
1.查询选修课程最多的学生的学号和姓名(3)
selecttop1学号=student.sno,姓名=student.sname
fromstudentinnerjoingrade
onstudent.sno=grade.sno
groupbystudent.sname,student.sno
orderbycount(student.sno)desc
2.将“计算机”学生的“信息系统”的分数置0分处理。
(2)
createviewgg(score)
as
selectscore
fromgrade,student,department,course
wheregrade.sno=student.snoandstudent.dno=department.dnoanddepartment.dname='计算机'ando=oandame='信息系统'
updategg
setgg.score=0;
3.查询选修课程中,有课程没有成绩、但是其他课程的成绩均在80分以上的同学的姓名、课程号、成绩
(2)
方法1:
selectsname,cno,score
fromstudent,grade
wherestudent.sno=grade.snoand
grade.snoin(selectsnofromgradewherescoreisnull)
andgrade.snonotin(selectsnofromgradewherescore<80)
andgrade.snoin(selectsnofromgradewherescore>=80)
groupbysname,cno,score
错误:
selectsname,cno,score
fromstudent,grade
wherestudent.sno=grade.sno
andEXISTS(
select*
fromgrades1
wheres1.score>80andstudent.sno=snoand
exists
(select*
fromgrades2
wheres1.sno=s2.snoands2.scoreisnull
)
)
4检索所有平均成绩超过80分以上的课程名称、授课教师。
方法1:
createviewgradescore
as
selectcno,avg(score)average
fromgrade
groupbycno
selectcname,teacher
fromcourse
wherecnoin
(
selectcno
fromgradescore
whereaverage>80
)
方法2:
selectcname,teacher
fromcourse
wherecnoin
(selectcno
fromstudent,grade
wheregrade.sno=student.sno
groupbycno
havingavg(score)>=80
)
5.查询姓名是三个字的学生情况
(2)
方法1:
select*fromstudent
whereltrim(sname)like'___'
andltrim(sname)notlike'__'
andltrim(sname)notlike'_'
andltrim(sname)isnotnull
方法2:
select*fromstudent
wherelen(ltrim(sname))=3
6.查询所有未选修‘03’号课程的学生姓名(用存在量词)
----错误结果
selectdistinctstudent.sno,snamefromstudent
leftjoingradeonstudent.sno=grade.sno
wherenotexists(selectsnofromgradewherecno='03'andstudent.sno=grade.sno)
----正确
selectsno,snamefromstudent
wherenotexists
(select*fromgradewherecno='03'andstudent.sno=grade.sno)
7查询‘中文’系学生的详细记录情况,结果按性别升序、年龄降序排列。
(1)
selectstudent.sno,student.sname,student.sex,student.sage,student.dno,student.dormno
fromdepartment,student
wheredepartment.dno=student.dnoanddepartment.dname='中文'
orderbysexasc,sagedesc;
8将“计算机”学生的“信息系统”的分数置0分处理。
(2)
方法1:
createviewgg(score)
as
selectscore
fromgrade,student,department,course
wheregrade.sno=student.snoandstudent.dno=department.dnoanddepartment.dname='计算机'ando=oandame='信息系统'
updategg
setgg.score=0;
方法2:
Updategrade
Setscore=0
Wherecnoin(selectcnofromcoursewherecname=‘信息系统’)
Andsnoin(selectsnofromstudentwheredno=
(selectdnofromdepartmentwheredname=‘计算机系’))
9.创建计算机系所有不及格学生的视图
createviewis_student
as
selectsno,sname,sage
fromstudent
whereexists(select*fromdepartment,grade
wheredepartment.dno=student.dno
anddepartment.dname='计算机系'andgrade.sno=student.snoandgrade.score<60)
10查询显示所有学生选修课程的学分的累计情况,并按照总学分的高低顺序排序。
selectstudent.sno,sname,sum(credit)as总学分
fromstudent,course,grade
wherestudent.sno=grade.snoando=oandgrade.scoreisnotnull
groupbystudent.sno,sname
orderby总学分desc;
11创建视图VIEW_S中给出各个系的男女生人数情况
(1)
createviewVIEW_S--337
as
selectcount(casewhenSex='男'then1end)asNumMan,count(casewhenSex='女'then1end)asNumWoman,dno
fromstudentgroupby(dno)
12查询选修课程最多的学生的学号和姓名
方法1:
ifexists(selectnamefromsysobjects
wherename='vs1'andtype='V')
dropviewvs1
go
createviewvs1
as
selectsno,count(sno)cnt
fromgrade
groupbysno
go
ifexists(selectnamefromsysobjects
wherename='vs2'andtype='V')
dropviewvs2
go
createviewvs2
as
selectmax(cnt)maxs
fromvs1
go
selectsname,sno
fromstudent
wheresnoin(selectsnofromvs1wherecntin(selectmaxsfromvs2))
方法2:
selectsname,sno
fromstudent
wheresnoin(selecttop1snofromgradegroupbysnoorderbycount(cno)desc)
13查询选修了课程’01’但没有选修课程’02’的学生学号
方法1:
selectdistinctsnofromstudent,gradewhere
exists(select*fromgradewheresno=student.snoandcno='01'andnotexists
(select*fromgradewheresno=student.snoandcno='02'));
方法2:
selectdistinct(sno)
fromgrade
where
snonotin
(
selectdistinct(sno)
fromgrade
where
snonotin
(selectdistinct(sno)
fromgrade
wherecno='01')
or
snonotin
(selectdistinct(sno)
fromgrade
wherecno='02')
);
14查询每个系中成绩最高的学生的系号、学号和姓名
(1)
createviewvs1
as
selectdno,max(score)mx
fromstudent,grade
wherestudent.sno=grade.sno
groupbydno
selectstudent.dno,student.sno,sname
fromstudent,grade,vs1
wherestudent.dno=vs1.dnoandstudent.sno=grade.sno
andscore=mx
orderbystudent.dno
14查询每个宿舍中成绩最高的学生的宿舍号、学号和姓名
(1)
方法1:
createviewvs1
as
selectdormno,max(score)mx
fromstudent,grade
wherestudent.sno=grade.sno
groupbydormno
selectstudent.sno,student.dormno,sname
fromstudent,grade,vs1
wherestudent.dormno=vs1.dormnoandstudent.sno=grade.sno
andscore=mx
orderbystudent.dormno
方法2:
selects1.sno,dormno,snamefromstudents1,gradeg1
wheres1.sno=g1.snoand
scorein(selectmax(score)fromstudents2,gradeg2
wheres2.sno=g2.snoands1.dormno=s2.dormno
groupbys2.dormno)
orderbydormno
15查询每门课中成绩最低的学生学号、课程号、成绩,并按课程号排序。
(1)
方法一:
ifexists(selectnamefromsysobjects
wherename='v1'andtype='V')
dropviewv1
go
createviewv1
as
selectcno,min(score)mins
fromgrade
groupbycno
selectsno,o,minsfromgrade,v1
whereo=oandgrade.score=mins
方法2:
select*fromgradeg
wheresnoin(selecttop1snofromgradewherecno=oorderbyscore)
orderbyo
方法3:
selectg1.sno,o,g1.scorefromgradeg1
whereg1.scorein(selectmin(score)fromgradeg2whereo=ogroupbyo)
orderbyo
16查询其他系中比计算机系某一学生年龄小的学生姓名和年龄
(2)
方法1:
selectsname,sage
fromstudent
wheresage<(
selectmax(sage)
fromstudent
wheredno=(
selectdno
fromdepartment
wheredname='计算机系'
)
groupbydno
)
and
dno!
=(
selectdno
fromdepartment
wheredname='计算机系'
);
方法2:
selectsname,sagefromstudents1
wheresnonotin(selectsnofromstudent,department
wherestudent.dno=department.dnoanddname='计算机系')
andsagewherestudent.dno=department.dnoanddname='计算机系')
17假设一个宿舍最多可以住5个人,为没有分配宿舍的同学给出他住宿的所有可能(3)
selectsno,sname,dorm.dormnofromdorm,student
wheresnoin(selectsnofromstudentwheredormnoisnull)
anddorm.dormnoin(selectdormnofromstudentgroupbydormnohavingcount(sno)<5)
union
selectsno,sname,dorm.dormnofromdorm,student
wheresnoin(selectsnofromstudentwheredormnoisnull)
anddorm.dormnonotin(selectdistinctdormnofromstudentwheredormnoisnotnull)
orderbysno
18对所有“计算机系”的学生的选修课程的成绩提高10%。
updategrade
setscore=1.1*score
where'计算机系'in(selectdnamefromdepartment,student
wherestudent.sno=grade.sno);
select*fromgrade;
19查询选修课程中,有课程没有成绩、但是其他课程的成绩均在80分以上的同学的姓名、课程号、成绩
selectsname,cname,scorefromstudent,grade,course
whereo=oandstudent.sno=grade.snoandgrade.snoin(selectdistinctsnofromgradewherescoreisnull)
andscore>80
20.查询选修课程总学分最高的学生姓名和总学分
selecttop1sname,sum(score)sums
fromgrade,student
wheregrade.sno=student.sno
groupbystudent.sno,sname
orderbysum(score)desc
21重建相关数据表,修改其完整性规则,用级联删除规则实现:
删除“中文系”所有学生的相关信息。
altertabledepartmentaddconstraintpk_dno1primarykey(dno)
altertablestudentaddconstraintpk_dno2foreignkey(dno)referencesdepartment(dno)ondeletecascade
altertablestudentaddconstraintpk_sno1primarykey(sno)
altertablecourseaddconstraintpk_cnoprimarykey(cno)
altertablegradeaddconstraintpk_sno2foreignkey(sno)referencesstudent(sno)ondeletecascade
altertablegradeaddconstraintpk_cno3foreignkey(cno)referencescourse(cno)ondeletecascade
deletefromdepartment
wheredname='中文系'