数据库的嵌套查询和统计查询

①求选修了数据结构的学生学号和成绩。
select s_no,score
from choice
where s_no in (select s_no from course where course_name='数据结构')
②求01001课程的成绩高于张彬的学生学号和成绩。
select s_no,score
from choice
where course_no='01001' 
and score>(select score from choice,student
				where choice.s_no=student.s_no and s_name='张彬')
③求其他班中比js9901班某一学生年龄小的学生姓名和年龄。
select s_name,DATEDIFF(YEAR,s_birthday,GETDATE()) s_age
from student
where s_birthday>any(select s_birthday
from student
where class_no='js9901') and class_no<>'js9901'
④求其他班中比js9901班所有学生年龄都小的学生姓名和年龄。
select s_name,DATEDIFF(YEAR,s_birthday,GETDATE()) s_age
from student
where s_birthday>all(select s_birthday from student where class_no='js9901')
and class_no<>'js9901'
⑤求选修了02001课程的学生姓名。
select s_name from student
where exists(select * from choice where course_no='02001')
⑥求没有选修了02001课程的学生姓名。
select s_name from student
where not exists(select * from choice where course_no='02001')
⑦查询选修了全部课程的学生的姓名。
select s_name from student
where not exists(select * from choice where s_no=student.s_no)
⑧求至少选修了学号为991101的学生所选修的全部课程的学生学号和姓名。
select s_name from student
Where s_no in (select s_no from choice c1 where not exists(select choice c2
where c2.s_no='991101' 
and not exists(select choice c3 where c3.s_no=c1.s_no and c3.s_no=c2.s_no)))
①	查询学生总人数。
select count(*) from student
②	查询选修了课程的学生人数。
select count(distinct s_no) from choice
③	计算01001课程的学生平均成绩。
select avg(score) from choice where course_no='01001’
④	查询选修01001课程的学生的最高分数。
select max(score) from choice where course_no='01001'
⑤	求学号为991101学生的总分和平均分。
select count(score),avg(score) from choice where course_no='991101'
⑥	求各个课程号及相应的选课人数。
select course_no,count(s_no) from choice group by course_no
⑦	查询选修了3门以上课程的学生学号。
select s_no from choice group by s_no having count(*)>3
⑧查询选修了3门以上且各门课程均为及格的学生的学号及其总成绩，查询结果按总成绩降序列出。
select s_no,sum(score) as zongchengji from choice
where any(score)>=60
group by s_no
order by having(count(*)>3)
sum(score) desc
1．	集合查询
在Transact-SQL中没有直接提供集合的交操作INTERSECT和差MINUS操作，但可以使用其他方法实现。
①查询js9902班的学生及年龄不大于19岁的学生。
select DATEDIFF(YEAR,s_birthday,GETDATE()) s_age
from student
where s_age<=19
union
select *
from student
where class_no='js9902'
②查询选修了课程01001或者选修了01002的学生。
select s_no
from choice
where course_no='01001'
union
select s_no
from choice
where course_no='01002'
③查询学号为991101和学号为991102的学生的学号和总分。
select s_no,sum(score)
from choice
where s_no='991101'
union
select s_no,sum(score)
from choice
where s_no='991102'
④查询js9902班的学生与年龄不大于19岁的学生的交集。
select DATEDIFF(YEAR,s_birthday,GETDATE()) s_age
from student
where s_age<=19
intersect
select *
from student
where class_no='js9902'
⑤查询js9902班的学生与年龄不大于19岁的学生的差集。
    select DATEDIFF(YEAR,s_birthday,GETDATE()) s_age
from student
where s_age<=19
except
select *
from student
where class_no='js9902'