4实验四复杂查询
实验四复杂查询一、实验目的
掌握两个表以上的连接查询的应用,包括嵌套查询。
二、实验容
(1)查询比“林红”年纪大的男学生信息。
select*from Student
where Sex='男'and
YEAR(Birth)-(select YEAR(Birth)
from Student where Sname='林红')<0
(2)检索所有学生的选课信息,包括学号、、课号、课程名、成绩。select SC.Sno,Sname,Sex,Classno,ame,Grade
from Students,SC,Coursec
where s.Sno=SC.Sno and SC.o=c.o
(3)查询已选课学生的学号、、课程名、成绩。select SC.Sno,Sname,ame,Grade
from Students,coursec,SC
where s.sno=SC.sno and c.o=SC.o
(4)查询选修了“C语言程序设计”的学生的学号和。
select sc.Sno,Sname
from Students,coursec,sc
where c.ame='C语言程序设计'and s.Sno=sc.Sno and sc.o=c.o
(5)查询与“虹”在同一个班级的学生学号、、家庭住址。
a.用子查询
select Sno,Sname,Home_addr
from Student
where Classno='051'and Sname!='虹'
b.用连接查询
select Sno,Sname,Home_addr
from Student
where Classno=(select Classno from Student where Sname='虹')
and Sname!='虹'
(6)查询其他班级中比“051”班所有学生年龄大的学生的学号、。
select Sno,Sname
from Student
where Classno<>'051'
and Birth where Classno='051') (7)(选作)查询选修了全部课程的学生。 本题使用除运算的方法。 由题意可得另一种语言,没有一个选了课的学生没有选course表里的课程。那么,我们需要两个NOT EXISTS表示双重否定;另一种思路可详见书例4.52 select Sname from Student where notexists( select*from Course where notexists( select*from SC where Sno=Student.sno and o=Course.o)) (8)(选作)查询至少选修了学生“20110002”选修的全部课程的学生的学号,。 select Sno,Sname from Student where Sno in( selectdistinct Sno from SC as SC1 where notexists( select*from SC as SC2 where SC2.Sno='20110002' andnotexists( select*from SC as SC3 where SC3.Sno=SC1.Sno and SC3.o=SC2.o)) ) (9)检索学生的学号、、学习课程名及课程成绩。 select s.Sno,Sname,ame,Grade from Students,Coursec,SC where s.Sno=sc.Sno and sc.o=c.o (10)检索选修了“高数”课且成绩至少高于选修课程号为“002”课程的学生的学号、课 程号、成绩,并按成绩从高到低次序排列。 由题意得,选修了高数课的学生的成绩要高于选修002课号课程的学生的成绩selectdistinct Sno,o,Grade from SC where o in( select o from Course where ame='高数') and Grade>(select MAX(Grade) from SC where o='002') orderby Grade desc (11)检索选修3门以上课程的学生的学号、总成绩(不统计不及格的课程),并要求按总成绩的降序排列出来。 select Sno,sum(grade)as总成绩 from SC where Sno in( select Sno from SC groupby Sno having count(*)>3) and Grade>=60 groupby Sno orderby总成绩desc (12)检索多于3名学生选修的并以3结尾的课程号的平均成绩。 select avg(Grade)as平均成绩 from SC where o like'%3' groupby o having count(o)>3 (13)检索最高分与最低分之差大于5分的学生的学号、、最高分、最底分。selectdistinct SC.Sno学号,Sname, max(grade)as最高分,min(grade)as最低分 from Student,SC where SC.Sno=Student.Sno groupby SC.Sno,Sname having max(grade)-min(grade)>5 (14)外连接 对实验二中的表6和表7做一个外连接查询,显示每门课程的课号、课名、选修该门课的学号、成绩,没有同学选修的课程(如Visual_Basic)也要在查询结果中。 select c.o课号,ame课名,Sno学号,Grade成绩 from Coursec leftouterjoin SC on (c.o=SC.o) (15)创建一个表Student_other,结构同Student,输入若干记录,部分记录和Student表中的相同。 创建过程: createtable Student_other( Sno char(8)primarykey, Sname varchar(8)notnull, Sex char(2)notnull, Birth smalldatetime notnull, Classno char(3)notnull, Entrance_date smalldatetime notnull, Home_addr varchar(40), Sdept char(2)notnull, Postcode char(6) ) 随意输入几条Student表中没有的信息,完成创建 a.查询同时出现在Student表和Student_other表中的记录select*from student_otherso,Students where so.Sno=s.Sno b. 查询Student表和Student_other表中的全部记录 select*from student union select*from student_other (16)(选作)创建一个数据库Student_info_other,参数自定。创建过程: 新建数据库 名称确定,参数自定义,然后“确定”即可 a.当前数据库为Student_info,将Student_info数据库中的Student_other复制到Student_info_other中。 select*into Student_info_other.dbo.Student_other from Student_info.dbo.Student_other b.查询同时出现在Student表和Student_info_other数据库Student_other表中的记录。 select*from Student_info_other.dbo.student_otherso, Student_info.dbo.Students where so.sno=s.sno