实验四、复杂查询

实验目的

掌握两个表以上的连接查询的应用，包括嵌套查询。

实验内容

1．同一数据库中的多表查询

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

   select * from Student 
   where Sex = '男' and Birth < (
   	select Birth 
   	from Student
   	where Sname = '林红'
   );
   

   

2. 查询所有学生的选课信息，包括学号、姓名、课号、课程名、成绩。

   select t1.Sno 学号,t1.Sname 姓名,t2.Cno 课号, t2.Cname 课程名, t3.Grade 成绩 
   from Student t1,Course t2,SC t3 
   where t1.Sno=t3.Sno and t2.Cno=t3.Cno;
   

   

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

   select t1.Sno 学号,t1.Sname 姓名,t2.Cname 课程名,t3.Grade 成绩
   from SC t3
   left outer join Student t1 on (t1.Sno=t3.Sno),Course t2
   where t2.Cno=t3.Cno;
   

   

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

   select Sno 学号,Sname 姓名
   from Student
   where Sno in(
   	select Sno
   	from SC
   	where Cno in(
   		select Cno
   		from Course
   		where Cname='C语言程序设计'
   		)
   	);
   

   

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

   select Sno 学号,Sname 姓名,Home_addr 家庭住址
   from Student
   where Classno=(
   	select Classno
   	from Student
   	where Sname='张虹'
   	);
   

   

6. 查询其他班级中比“051”班所有学生年龄大的学生的学号、姓名。

   select Sno,Sname
   from Student
   where Classno <>'051'
   	and Birth < all(
   		select Birth
   		from Student
   		where Classno='051');
   

   

7. （选做）查询选修了全部课程的学生姓名。
   将题目意思转换为等价的用存在量词的形式,即 查询这样一个学生,没有一门课程中没有他的名字

   select Sname
   from Student
   where not exists(
   	select *
   	from Course
   	where not exists(
   		select *
   		from SC
   		where Student.Sno=SC.Sno and Course.Cno=SC.Cno
   		)
   	)
   

   

8. （选做）查询至少选修了学生“20110002”选修的全部课程的学生的学号、姓名。
   将题目的意思转换为等价的用存在量词的形式: 不存在这样的学生,学生’20110002’选修了一些课程y,而学生x没有选修这些课程y

   select distinct sx.Sno,Student.Sname
   from SC sx,Student
   where sx.Sno=Student.Sno and
   	not exists(
   		select * from SC sy
   		where sy.Sno='20110002' and
   			not exists(
   				select * from SC sz
   				where sz.Sno=sx.Sno and sz.Cno=sy.Cno
   				)
   	);
   

   

9. 查询学生的学号、姓名、学习课程名及课程成绩。

   select Student.Sno,Sname,Cname,Grade
   from Student,Course,SC
   where Student.Sno=SC.Sno and Course.Cno=SC.Cno;
   

   

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

    select Sno,SC.Cno,Grade
    from SC
    where Cno in(
    		select Cno from Course
    		where Cname='高数'
    		)
    	and Grade>all(
    		select Grade
    		from SC
    		where Cno='002')
    order by Grade desc;
    

    

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

    select Sno 学号,sum(case when Grade>=60 then Grade else 0 end) 总成绩
    from SC
    where Sno in(
    	select Sno from SC group by Sno having count(*)>3
    	)
    group by Sno
    order by 总成绩 desc;
    

    

12. 查询多于3名学生选修的并以3结尾的课程号的平均成绩。

    select Cno 课程号, avg(Grade) 平均成绩
    from SC
    where Cno in(
    	select Cno from SC group by Cno having count(*)>3
    	) and Cno='%3'
    group by Cno;
    

13. 查询最高分与最低分之差大于5分的学生的学号、姓名、最高分、最低分。

    select SC.Sno 学号,Sname 姓名,max(Grade) 最高分,min(Grade) 最低分
    from SC,Student
    where SC.Sno in (
    	select Sno from SC
    	group by Sno
    	having count(*)>2 and (max(Grade)-min(Grade))>5
    	) and Student.Sno=SC.Sno
    group by SC.Sno,Sname;
    

    

14. 创建一个表student_other，结构同Student，输入若干记录，部分记录和Student表中的相同。

    create table student_other
    ( 
    	Sno char(8) not null primary key,
    	Sname varchar(8) not null,
    	Sex char(2) not null default '男',
    	Birth smalldatetime not null,
    	Classno char(3) not null,
    	Entrance_date smalldatetime not null,
    	Home_addr varchar(40),
    	Sdept char(20) not null,
    	Postcode char(6),
    	check(Postcode like'[0-9][0-9][0-9][0-9][0-9][0-9]')
    )
    insert into student_other (Sno,Sname,Sex,Birth,Classno,Entrance_date,Home_addr,Sdept,Postcode)
    values('20110001','张虹','男','1992-09-11','051','2011-09-01','南京','计算机系','200413'),
    ('20110002','林红','女','1991-11-12','051','2011-09-01','北京','计算机系','100010'),
    ('20110103','赵清','男','1993-05-11','061','2011-09-01','上海','软件工程','200013');
    

    a．查询同时出现在Student表和student_other表中的记录。

    select * from Student intersect
    select * from student_other;
    

    

    b．查询Student表和student_other表中的全部记录。

    select * from Student
    select * from student_other
    

    

2．多个数据库间的多表查询

（选做）创建一个数据库student_info_other，参数自定。

create database student_info_other;

1. 当前数据库为Student_info，将student_info数据库中的表student_other复制到student_info_other中。

   select * into student_info_other.dbo.student_other1
   from student_info.dbo.student_other;
   

   

2. 查询同时出现在Student表和student_info_other数据库student_other表中的记录。

   select * from Student intersect
   select * from student_info_other.dbo.student_other1;
   

   

3．外连接查询

1. 查询所有课程信息及其选课信息，包含未被学生选修的课程。

   select * from Course
   left outer join SC on(SC.Cno=Course.Cno);
   

   

2. 查询所有学生信息，所有课程信息及其选课信息，包含未选修课程的学生及未被学生选修的课程。

   select * from Student
   left join SC on(SC.Sno=Student.Sno)
   left join Course on(SC.Cno=Course.Cno);
   

   

注:为了能更明显地显示出查询语句是否正确,我在中途自己添加了很多数据,所以前后查询应该显示的结果可能会有出入