group by 学生_ID having sum(学分)<5 )
and year(getdate())-届=4
-------------------------------------------------------------------------------------- 35--年龄大于 学校老师平均年龄的 外语系老师
select *
from dbo.教师 j ,dbo.系 x
where datediff(year,出生日期,getdate())> ( select avg( datediff(year,出生日期,getdate()) ) from dbo.教师 )
and j.系_ID=x.系_ID and 系名 like '%外语%'
------------------------------------------------------------------------------------------ 36----教过张靓影的老师 select *
from dbo.教师 where 教师_ID in ( select distinct 教师_ID from dbo.教师认课 where 开课_ID in ( select 开课_ID from dbo.学生选课 where 学生_ID in ( select 学生_ID from dbo.学生 where 姓名 like '%张靓影%' ) ) )
-------------------------------------------------------------- 37--java在前两名 而vc在后两名的 学生 select *
from dbo.学生 where 学生_ID in ( select top 2 学生_ID from dbo.学生选课 a ,dbo.开课 b,dbo.课程 c where a.开课_ID =b.开课_ID and b.课程_ID =c.课程_ID and c.课程名 ='java' order by 成绩 desc ) and
学生_ID in ( select top 2 学生_ID from dbo.学生选课 a ,dbo.开课 b,dbo.课程 c where a.开课_ID =b.开课_ID and b.课程_ID =c.课程_ID and UPPER(c.课程名) ='VC' order by 成绩 asc )
------------------------------------------------------------------ 38 --查询选修 所有计算机系课程 的学生姓名 select 姓名
from dbo.学生 where 学生_ID in ( select 学生_ID from dbo.学生选课 a,dbo.开课 b,dbo.课程 c where a.开课_ID =b.开课_ID and b.课程_ID =c.课程_ID and 系_ID=1 group by 学生_ID having count(*)= ( select count(*) from dbo.课程 where 系_ID=1 ) )
39.--选修了2号学生 所有课程 的学生 select distinct 学生_ID from dbo.学生选课 x
where x.学生_ID!=2 and not exists ( select * from dbo.学生选课 y where y.学生_ID=2 and not exists ( select * from dbo.学生选课 z where z.学生_ID=x.学生_ID and z.开课_ID=y.开课_ID ) )
40--每科 都及格的学生
--变换：没有一科 不及格的学生 select * from 学生
where 学生_ID not in
( select distinct 学生_ID from 学生选课 where 成绩<60 )
--区别
select distinct 学生_ID from 学生选课 a where not exists ( select 学生_ID from 学生选课 b where a.学生_ID=b.学生_ID and b.成绩<60 )
41 --物理系的课程 总学分 比外语系 多多少 select ( select sum(学分) from dbo.课程 where 系_ID=3 ) - ( select sum(学分) from dbo.课程 where 系_ID=2 )
42 -- 至少选3门课的学生 select *
from dbo.学生 where 学生_ID in ( select 学生_ID from dbo.学生选课 group by 学生_ID having count(*)>=3 )
43--按年龄段，统计学生人数(行转列)
select
count(*),(year(getdate())-year(出生日期))/10*10
from dbo.学生
group by (year(getdate())-year(出生日期))/10*10 --(行转列) select
count(case when (year(getdate())-year(出生日期)) between 20 and 30 then 班级 end) as '20-30', count(case when (year(getdate())-year(出生日期)) between 30 and 40 then 班级 end) as '30-40', count(case when (year(getdate())-year(出生日期)) between 40 and 50 then 班级 end) as '40-50', count(case when (year(getdate())-year(出生日期)) between 50 and 60 then 班级 end) as '50-60', count(case when (year(getdate())-year(出生日期)) between 60 and 70 then 班级 end) as '60-70' from dbo.学生
10.试用SQL查询语句表达下列对教学数据库3个基本表S,C,SC的查询.
S(S#, Sn, Sa, Sex)
C(C#, Cn, TEACHER) SC(S#, C#, GRADE)
(1)在表C中统计开设课程的教师人数. (2)求选修C4课程的女学生的平均年龄.
(3)求每个学生都选修课程(已有成绩)的门数和平均成绩.
(4)统计每个学生选修课程的门数(超过5门的学生才统计).要求输出学生学号和选修门数,查询结果按门数降序排列,若门数相同,按学号升序排列.
(5)检索学号比WANG同学大,而年龄比他小的学生姓名. (6)在表SC中检索成绩为空值的学生学号和课程号. (7)检索姓名以L开头的所有学生的姓名和年龄. (8)求年龄大于女同学平均年龄的男学生姓名和年龄. (9)求年龄大于所有女同学年龄的男学生的姓名和年龄. 参考答案：
(1)在表C中统计开设课程的教师人数. Select Count(distinct TEACHER) from C
(2)求选修C4课程的女学生的平均年龄. Select AVG(SA) from S
where Sex=0 and S# in (select S# from SC
where C#='C4')
(3)求每个学生都选修课程(已有成绩)的门数和平均成绩. Select S#, AVG(GRADE), COUNT(C#) from SC Group by S#
(4)统计每个学生选修课程的门数(超过5门的学生才统计).要求输出学生学号和选修门数,查询结果按门数降序排列,若门数相同,按学号升序排列. Select COUNT(C#), S# from SC
GROUP BY S#
HAVING COUNT(*)>5
ORDER BY COUNT(*) DESC, SC.S# ASC
(5)检索学号比WANG同学大,而年龄比他小的学生姓名. 方法一： Select Sn from S
where S.S#>(select S1.S# from S as S1
where S1.Sn='WANG') and S.Sa< ( select S2.Sa from S as S2
where S2.Sn='WANG') 方法二： Select Sn from S as S1
where Exists (select *
from S as S2
where S2.Sn='WANG') and S1.S#>S2.S# and S1.Sa (Select AVG(S1.Sa) from S as S1
where S1.Sex=’女’)
(9)求年龄大于所有女同学年龄的男学生的姓名和年龄. 方法一： Select S1.Sn, S1.Sa from S S1
where S1.Sex=’男’ and S1.Sa> ( select Max(S2.Sa) from S S2
where S2.Sex=’女’ ) 方法二：
Select S1.Sn, S1.Sa from S S1
where S1.Sex=’男’ and S1.Sa>ALL (select S1.Sa from S S2
where S2.Sex=’女’ )
作业
--1学校学生中最大的姓（姓人数最多的学生） select top 1 count(*) as xing1 from (
select left(姓名,1) as xing from dbo.学生 )lin
group by lin.xing order by xing1 desc
--物理系姓王的<22的学生 select *
from dbo.学生
where left(姓名,1)='王' and 学生_ID in ( select 学生_ID from dbo.系 where 系名='物理系' )
and year(getdate())-year(出生日期)<22
--物理系的老师比计算机系所有老师年龄都大的老师 --3. select *