数据库实验四复杂查询.docx
《数据库实验四复杂查询.docx》由会员分享,可在线阅读,更多相关《数据库实验四复杂查询.docx(15页珍藏版)》请在冰点文库上搜索。
数据库实验四复杂查询
实验四复杂查询
一、实验目的
掌握两个表以上的连接查询的应用,包括嵌套查询。
二、实验内容
(1)查询比“林红”年纪大的男学生信息。
select*
fromStudent_20103322
where(year(GETDATE())-year(Birth))>
(selectyear(GETDATE())-year(Birth)
fromStudent_20103322
whereSname='林红')
andSex='男'
(2)检索所有学生的选课信息,包括学号、姓名、课号、课程名、成绩。
selectStudent_20103322.Sno,Sname,Course_20103322.Cno,Cname,Grade
fromStudent_20103322,Course_20103322,SC_20103322
whereStudent_20103322.Sno=SC_20103322.SnoAND
Course_20103322.Cno=SC_20103322.Cno
(3)查询已选课学生的学号、姓名、课程名、成绩。
selectStudent_20103322.Sno,Sname,Cname,Grade
fromStudent_20103322,Course_20103322,SC_20103322
whereStudent_20103322.Sno=SC_20103322.SnoAND
Course_20103322.Cno=SC_20103322.Cno
(4)查询选修了“C语言程序设计”的学生的学号和姓名。
selectStudent_20103322.Sno,Sname
fromStudent_20103322
whereSnoIN
(selectSno
fromSC_20103322
whereCnoin
(selectCno
fromCourse_20103322
whereCname='C语言程序设计'))
(5)查询与“张虹”在同一个班级的学生学号、姓名、家庭住址。
a.用子查询
selectStudent_20103322.Sno,Sname,Homeaddr
fromStudent_20103322
whereClassno=(selectClassno
fromStudent_20103322
whereSname='张虹')
b.用连接查询
selectA.Sno,A.Sname,A.Homeaddr
fromStudent_20103322A,Student_20103322B
whereA.Classno=B.ClassnoANDB.Sname='张虹'
(6)查询其他班级中比“051”班所有学生年龄大的学生的学号、姓名。
selectStudent_20103322.Sno,Sname
fromStudent_20103322
whereClassno!
='051'AND
(year(GETDATE())-year(Birth))>ANY
(selectyear(GETDATE())-year(Birth)
fromStudent_20103322
whereClassno='051')
(7)(选作)查询选修了全部课程的学生姓名。
selectSname
fromStudent_20103322
wherenotexists
(select*
fromCourse_20103322
wherenotexists
(select*
fromSC_20103322
whereSno=Student_20103322.SnoANDCno=Course_20103322.Cno))
(8)(选作)查询至少选修了学生“20050002”选修的全部课程的学生的学号,姓名。
selectSno,Sname
fromStudent_20103322
whereSnoIN
(selectdistinctSno
fromSC_20103322A
wherenotexists
(select*
fromSC_20103322B
whereB.Sno='20050002'AND
notexists
(select*
fromSC_20103322C
whereC.Sno=A.SnoANDC.Cno=B.Cno)))
(9)检索学生的学号、姓名、学习课程名及课程成绩。
selectStudent_20103322.Sno,Sname,Cname,Grade
fromStudent_20103322,Course_20103322,SC_20103322
whereStudent_20103322.Sno=SC_20103322.SnoAND
Course_20103322.Cno=SC_20103322.Cno
(10)检索选修了“高数”课且成绩至少高于选修课程号为“002”课程的学生的学号、课程号、成绩,并按成绩从高到低次序排列。
selectStudent_20103322.Sno,Cno,Grade
fromStudent_20103322,SC_20103322
whereStudent_20103322.Sno=SC_20103322.SnoAND
Cno=(selectCno
fromCourse_20103322
whereCname='高数')AND
Grade>ANY
(selectGrade
fromSC_20103322
whereCno='002')
orderbyGradeDESC
(11)检索选修3门以上课程的学生的学号、总成绩(不统计不及格的课程),并要求按总成绩的降序排列出来。
selectSno,SUM(Grade)
fromSC_20103322
groupbySno
havingCOUNT(Grade)>3AND
MIN(grade)>=60
orderbySUM(Grade)DESC
(12)检索多于3名学生选修的并以3结尾的课程号的平均成绩。
selectCno,AVG(Grade)as'平均成绩'
fromSC_20103322
groupbyCno
havingCOUNT(Sno)>3AND
Cnolike'%3'
(13)检索最高分与最低分之差大于5分的学生的学号、姓名、最高分、最底分。
selectSname,Student_20103322.Sno,MAX(Grade)'最高分',MIN(Grade)'最低分'
fromStudent_20103322,SC_20103322
groupbyStudent_20103322.Sno,SC_20103322.Sno,Student_20103322.Sname
havingStudent_20103322.Sno=SC_20103322.SnoAND
(MAX(Grade)-MIN(Grade))>5
(14)外连接
对实验二中的表6和表7做一个外连接查询,显示每门课程的课号、课名、选修该门课的学号、成绩,没有同学选修的课程(如Visual_Basic)也要在查询结果中。
select*fromStudent_20103322fulljoinSC_20103322
on(Student_20103322.Sno=SC_20103322.Sno)fullouterjoinCourse_20103322
on(Course_20103322.Cno=SC_20103322.Cno)
(15)创建一个表Student_other,结构同Student,输入若干记录,部分记录和Student表中的相同。
createtableStudent_other_20103322
(
Snochar(8)notnullprimarykey,--学号
Snamevarchar(8)notnull,--学生姓名
Sexchar
(2)notnulldefault'男',--性别
Birthsmalldatetimenotnull,--出生年月
Classnochar(3)notnull,--班级号
Entrance_datesmalldatetimenotnull,--入学时间
Homeaddrvarchar(40)notnull,--家庭住址
)
a.查询同时出现在Student表和Student_other表中的记录
selectStudent_20103322.*
fromStudent_20103322,Student_other_20103322
whereStudent_20103322.Sno=Student_other_20103322.Sno
b.查询Student表和Student_other表中的全部记录
selectStudent_20103322.*,Student_other_20103322.*
fromStudent_20103322fullouterjoinStudent_other_20103322
on(Student_20103322.Sno=Student_other_20103322.Sno)