实验五复杂查询.doc

实验五 复杂查询班级:200805W1 姓名：王文东 学号：20083554 专业：计算机科学与技术一、实验目的 掌握两个表以上的连接查询的应用，包括嵌套查询。二、实验内容 （1）查询比“林红”年纪大的男学生信息。T-SQL语句：select * from studentwhere (year(getdate()- year(birth)(select year(getdate()- year(birth) from studentwhere Sname like林红)and Sex like男（2）检索所有学生的选课信息。T-SQL语句：select * from SC（3）查询已选课学生的学号、姓名、课程名、成绩。连接查询TSQL语句：select student.Sno,Sname,Cname,grade from student,Course,SCwhere student.Sno=SC.Sno（4）查询选修了“C语言程序设计”的学生的学号和姓名。T-SQL语句：select student.Sno,Snamefrom studentwhere Sno in(select Sno from SC where Cno =(select Cno from Course where Cname likeC语言程序设计)（5）查询与“张虹”在同一个班级的学生学号、姓名、家庭住址。（子查询）T-SQL语句： select Sno,Sname,Home_addrfrom studentwhere Classno =(select Classno from student where Sname like张虹) 连接查询T-SQL语句：select S1.Sno,S1.Sname,S1.Home_addrfrom student S1,student S2where S1.Classno=S2.Classno and S2.Sname=张虹（6）查询其他班级中比“051”班任一学生年龄大的学生的学号、姓名。带有ANY或ALL谓词的子查询语句：select Sno,Snamefrom studentwhere (year(getdate()-year(birth) any(select year(getdate()-year(birth) from student where Classno =051) and Classno 051 用聚合函数实现：select Sno,Snamefrom studentwhere year(getdate()-year(birth) (select Min(year(getdate()-year(birth) from student where Classno=051) and Classno051 （7）查询选修了全部课程的学生姓名。T-SQL语句：select Snamefrom studentwhere not exists (select * from Course where not exists (select * from SC where Sno=student.Sno and Cno=Course.Cno) （8）查询至少选修了学生“20050002”选修的全部课程的学生的学号，姓名。T-SQL语句：select Sno,Snamefrom studentwhere Sno in(select distinct Sno from SC SCX where not exists (select * from SC SCY where SCY.Sno=20050002and not exists (select * from SC SCZ where SCZ.Sno=SCX.Sno and SCZ.Cno=SCY.Cno) （9）检索学生的学号、姓名、学习课程名及课程成绩。T-SQL语句：select student.Sno,Sname,Cname,gradefrom student,SC,Coursewhere student.Sno=SC.Sno and Course.Cno=SC.Cno （10）检索选修了“高数”课且成绩至少高于选修课程号为“002”课程的学生的学号、课程号、成绩，并按成绩从高到低次序排列。T-SQL语句：select student.Sno,Cno,gradefrom student,SCwhere student.Sno=SC.Sno and Cno=(select Cno from Course where Cname=高数)and grade(select Max(grade) from SC where Cno=002)order by grade DESC（11）检索选修3门以上课程的学生的学号、总成绩（不统计不及格的课程），并要求按总成绩的降序排列出来。T-SQL语句：select Sno,SUM(grade)from SCgroup by Snohaving count(grade)3 and Min(grade)=60 （12）检索出每位学生的学号、姓名、未修课程名。T-SQL语句：select student.Sno 学号,姓名=Sname,Cname as 未修课程名from student,Course where not exists( select * from SC where Sno=student.Sno and Cno=Course.Cno) （13）检索多于3名学生选修的并以3结尾的课程号的平均成绩。T-SQL语句：select Cno,AVG(grade) as平均成绩from SCgroup by Cnohaving count(Sno)3 and Cno like%3（14）检索最高分与最低分之差大于5分的学生的学号、姓名、最高分、最底分。T-SQL语句：select student.Sno,Sname,Max(grade) as 最高分,Min(grade) as 最低分from SC,studentgroup by student.Sno,SC.Sno,student.Snamehaving Max(grade)-Min(grade)5 and SC.Sno=student.Sno （15）集合查询若学生信息表分别存放在两个数据库中，XSGL与XUE库（如student1表，见实验三）中，要求检索所有学生的姓名、性别和出日年份。T-SQL语句：select Sname ,Sex,Birth