–创建测试数据
create table Student(S# varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10))
insert into Student values(\’01\’ , N\’赵雷\’ , \’1990-01-01\’ , N\’男\’)
insert into Student values(\’02\’ , N\’钱电\’ , \’1990-12-21\’ , N\’男\’)
insert into Student values(\’03\’ , N\’孙风\’ , \’1990-05-20\’ , N\’男\’)
insert into Student values(\’04\’ , N\’李云\’ , \’1990-08-06\’ , N\’男\’)
insert into Student values(\’05\’ , N\’周梅\’ , \’1991-12-01\’ , N\’女\’)
insert into Student values(\’06\’ , N\’吴兰\’ , \’1992-03-01\’ , N\’女\’)
insert into Student values(\’07\’ , N\’郑竹\’ , \’1989-07-01\’ , N\’女\’)
insert into Student values(\’08\’ , N\’王菊\’ , \’1990-01-20\’ , N\’女\’)

create table Course(C# varchar(10),Cname nvarchar(10),T# varchar(10))
insert into Course values(\’01\’ , N\’语文\’ , \’02\’)
insert into Course values(\’02\’ , N\’数学\’ , \’01\’)
insert into Course values(\’03\’ , N\’英语\’ , \’03\’)
insert into Course values(\’04\’ , N\’法语\’ , \’03\’)

create table Teacher(T# varchar(10),Tname nvarchar(10))
insert into Teacher values(\’01\’ , N\’张三\’)
insert into Teacher values(\’02\’ , N\’李四\’)
insert into Teacher values(\’03\’ , N\’王五\’)

create table SC(S# varchar(10),C# varchar(10),score decimal(18,1))
insert into SC values(\’01\’ , \’01\’ , 80)
insert into SC values(\’01\’ , \’02\’ , 90)
insert into SC values(\’01\’ , \’03\’ , 99)
insert into SC values(\’02\’ , \’01\’ , 70)
insert into SC values(\’02\’ , \’02\’ , 60)
insert into SC values(\’02\’ , \’03\’ , 80)
insert into SC values(\’03\’ , \’01\’ , 80)
insert into SC values(\’03\’ , \’02\’ , 80)
insert into SC values(\’03\’ , \’03\’ , 80)
insert into SC values(\’04\’ , \’01\’ , 50)
insert into SC values(\’04\’ , \’02\’ , 30)
insert into SC values(\’04\’ , \’03\’ , 20)
insert into SC values(\’05\’ , \’01\’ , 76)
insert into SC values(\’05\’ , \’02\’ , 87)
insert into SC values(\’06\’ , \’01\’ , 31)
insert into SC values(\’06\’ , \’03\’ , 34)
insert into SC values(\’07\’ , \’02\’ , 89)
insert into SC values(\’07\’ , \’03\’ , 98)
insert into SC values(\’07\’ , \’04\’ , 90)

题目：–7.查询学过“王五”老师所教的所有课的同学的学号、姓名；

答案：select s#,sname from Student where s# in(
select s# from SC,Course,Teacher where SC.C#=Course.C#
and Teacher.T#=Course.T# and Teacher.Tname=\’王五\’
group by S# having COUNT(SC.C#)=(
select COUNT(c#) from Course,Teacher where Teacher.T#=Course.T# and Tname=\’王五\’))

自己的答案：

select a.s#,a.Sname,a.score,b.score2 from (select Student.Sname,Student.S#,SC.score from sc,student where SC.S#=Student.S# and sc.c#=\’02\’) a ,
(select Student.Sname,Student.S#,SC.score as score2 from SC,Student where sc.S#=Student.S# and sc.C#=\’01\’) b where a.s#=b.s# and score2 <score

对其中的 的部分语句进行分析（是可单独执行到的）

SELECT Student.s#,Student.Sname,score ,
(SELECT score FROM SC SC_2 WHERE SC_2.s#=Student.s# AND SC_2.c#=\’01\’) score2
FROM Student,SC WHERE Student.s#=SC.s# AND c#=\’02\’

通过看执行计划，可知，是先执行的子查询，然后在执行外面的。

如果细心的去看，你会发现这个语句的查询与子查询的表是一样的，所以我们可以试想一下，如果我把这个语句分开分别得到什么结果：

select Student.Sname,Student.S#,SC.score from sc,student where SC.S#=Student.S# and sc.c#=\’02\’

select Student.Sname,Student.S#,SC.score as score2 from SC,Student where sc.S#=Student.S# and  sc.C#=\’01\’

那么如果我们在通过where 或者left,right连接会有什么不同呢，你会发现，上面的子查询相当是分别满足某些条件的数据（两个基本相同的表的数据）进行left关联