SQL Serve---嵌套查询

定义

嵌套查询：主要用于复杂的查询中。在SQL语言中，一个Select From Where语句称为一个查询块，将一个查询块嵌套在另一个查询的Where子句或Having短语中的查询称为嵌套查询。

--列出tb_student表中和“陈凯”年龄相同的学生的学号和姓名

年龄：year(getdate())-year(birthday)

--列出tb_student表中和“陈凯”年龄相同的学生的学号和姓名
 select s1.sno,s1.sn 
 from tb_student as s1
 where year(getdate())-year(birthday)=
(select  year(getdate())-year(birthday) 
from tb_student  as s2 
where s2.sn=‘陈凯’)

--在选修c02课程成绩大于该课平均成绩的学生学号，姓名，成绩

--在选修c02课程成绩大于该课平均成绩的学生学号（），姓名，成绩
 select s.sno,sn,s1.score
 from tb_student s,
(select sno,score 
from tb_score
where cno='c02' and score>(select  avg(score)
from tb_score  where cno='c02'))as s1
where s.sno=s1.sno

select  cn from  tb_course
where cno IN(select cno  from tb_ score 
where  left(sno,10)=‘2015010103')

select sno,sn,dept from  tb_student
where sno NOT IN (select sno from tb_score  
              where cno=‘c02')

--查询考取最高分的学生的学号、课号、课名，成绩

  select sno,s.cno,cn,score 
  from  tb_score s,tb_course c
  Where  s.cno=c.cno and score =
 (select max(score) from tb_score)

select sno,sn 
from  tb_student
where year(getdate())-year(birthday) > 
(select avg(year(getdate())-year(birthday) )
 from tb_student)

select distinct sno,cno,score
from tb_score
Where score >= any 
(select max(score)
 from    tb_score 
group by cno)

select  sno,sn  from tb_student
where  sno = 
any(select  sno  from tb_score)

select  sno  
from  tb_score 
where  cno='c01'  and  score>60  and sno in    
(  select  sno 
 from  tb_score 
 where   cno='c02' and score>60 )