实验三(2) 数据库复杂查询

实验三（2）复杂查询班级：姓名：学号：专业：

一、实验目的

（1）熟练掌握复杂查询的select语句。

（2）熟练掌握连接查询方法

（3）熟练掌握嵌套查询方法

二、实验内容

（1）查询比“林红”年纪大的男学生信息。

T-SQL语句：

select*

from Student

where Sex='男'and Birth<

(select Birth

from Student

where Sname='林红'

)

（2）检索所有学生的选课信息。

T-SQL语句：

select distinct*

from SC,Student

where SC.Sno=Student.Sno

（3）查询已选课学生的学号、姓名、课程名、成绩。

连接查询T—SQL语句：

SELECT SC.Sno,Sname,Cname,Grade

FROM Course,SC,Student

WHERE SC.Sno=Student.Sno and https://www.360docs.net/doc/d015847559.html,o=https://www.360docs.net/doc/d015847559.html,o;

（4）查询选修了“C语言程序设计”的学生的学号和姓名。

T-SQL语句：

select Student.Sname'学生',Student.Sno'学号'

from SC,Student,Course

where SC.Sno=Student.Sno

and https://www.360docs.net/doc/d015847559.html,o=https://www.360docs.net/doc/d015847559.html,o

and Cname='C语言程序设计';

（5）查询与“张虹”在同一个班级的学生学号、姓名、家庭住址。

（子查询）T-SQL语句：

select Sno,Sname,Home_addr

from Course,Student

where Classno in

(select Classno

from Student

where Sname='张虹')

连接查询T-SQL语句：

select S1.Sname'姓名',S1.Sno'学号',S1.Home_addr'家庭住址' from Student S1,Student S2

where S1.Classno=S2.Classno

and S2.Sname='张虹'and not S1.Sname='张虹';

（6）查询其他班级中比“051”班任一学生年龄大的学生的学号、姓名。带有ANY或ALL谓词的子查询语句：

select Student.Sname'姓名',Student.Sno'学号'

from Student

where Birth

(select Birth

from Student

where Classno='051')

and not Classno='051';

（7）查询选修了全部课程的学生姓名。

T-SQL语句：

select Sname

from Student

where not exists

(select*

from Course

where not exists

(select*

from SC

where Sno=Student.Sno

and Cno=https://www.360docs.net/doc/d015847559.html,o))

（8）查询至少选修了学生“20050002”选修的全部课程的学生的学号，姓名。

T-SQL语句：

select distinct SC1.Sno'学号',Student.Sname'学生'

from SC SC1,Student

where not exists(

select* from SC SC2where SC2.Sno='20050002'

and not exists

(select* from SC SC3where SC3.Sno=SC1.Sno and https://www.360docs.net/doc/d015847559.html,o=https://www.360docs.net/doc/d015847559.html,o) )

and not Student.Sno='20050002'

and Student.Sno=SC1.Sno;

（9）检索学生的学号、姓名、学习课程名及课程成绩。

T-SQL语句：

SELECT Sname,Student.Sno,https://www.360docs.net/doc/d015847559.html,ame,Grade

FROM Student,Course,SC

WHERE Student.Sno=SC.Sno

and https://www.360docs.net/doc/d015847559.html,o=https://www.360docs.net/doc/d015847559.html,o

（10）检索选修了“高数”课且成绩至少高于选修课程号为“002”课程的学生的学号、课程号、成绩，并按成绩从高到低次序排列。

T-SQL语句：

select Student.Sno'学号',https://www.360docs.net/doc/d015847559.html,o'课程号',SC.Grade'成绩'

from SC,Student,Course

where https://www.360docs.net/doc/d015847559.html,ame='高数'

and SC.Grade>all(

select Grade

from SC

where https://www.360docs.net/doc/d015847559.html,o='002')

and SC.Sno=Student.Sno

and https://www.360docs.net/doc/d015847559.html,o=https://www.360docs.net/doc/d015847559.html,o

order by SC.Grade desc;

（11）检索选修3门以上课程的学生的学号、总成绩（不统计不及格的课程），并要求按总成绩的降序排列出来。

T-SQL语句：

select Student.Sno'学号',SUM(SC.Grade)'总成绩'

from SC,Student

where SC.Sno=Student.Sno

and SC.Grade in

(select SC.Grade

from SC

where SC.Grade>=60)

group by Student.Sno

having count(https://www.360docs.net/doc/d015847559.html,o)>3

order by SUM(SC.Grade)desc;

（12）检索出每位学生的学号、姓名、未修课程名。

T-SQL语句：

select student.Sno,Sname,Cname

from student,course

where not exists(select*

from SC

where student.Sno=SC.Sno and https://www.360docs.net/doc/d015847559.html,o=https://www.360docs.net/doc/d015847559.html,o

)

（13）检索多于3名学生选修的并以3结尾的课程号的平均成绩。

T-SQL语句：

select AVG(SC.Grade)'平均成绩'

from Student,SC

where https://www.360docs.net/doc/d015847559.html,o like'%3'

and SC.Sno=Student.Sno

group by https://www.360docs.net/doc/d015847559.html,o

having COUNT(https://www.360docs.net/doc/d015847559.html,o)>=3;

（14）检索最高分与最低分之差大于5分的学生的学号、姓名、最高分、最底分。

T-SQL语句：

select Student.Sno,Student.Sname,MAX(SC.Grade),MIN(SC.Grade)

from Student,SC

where Student.Sno=SC.Sno

group by Student.Sno,Student.Sname

having (MAX(SC.Grade)-MIN(SC.Grade))>=5;

（15）集合查询

若学生信息表分别存放在两个数据库中，XSGL与XUE库（如student1表，见实验三）中，要求检索所有学生的姓名、性别和出日年份。

T-SQL语句：

select Sname,Sex,DATEPART(year,Birth)出生年份

from Student

union

select Sname,Sex,DATEPART(year,Birth)出生年份

from XUE.dbo.Student

（16）若学生信息表分别存放在两个数据库中，XSGL与XUE库中；选课表在XSGL库中。检索XUE库中的学生的选课信息，给出学生的学号，姓名及成绩。

select XUE_.Student.Sname,XUE.Student.Sno,SC.Grade

from XUE.Student,SC

where SC.Sno=XUE.Student.Sno