sql 加强

##################################################
--1学校学生中最大的姓（姓人数最多的学生）
select *
from 
(
select left(姓名,1) as xing,count(*) as renshu
from dbo.学生
group by left(姓名,1)
)as l1
where renshu = 
(
select max( renshu  )
from 
(
select left(姓名,1) as xing,count(*) as renshu
from dbo.学生
group by left(姓名,1)
) as lin
)
order by l1.renshu desc




--2物理系姓王的年龄小于22的学生
select 姓名
from dbo.学生
where 系_ID=
(
select 系_ID
from 系
where 系名 like '%物理%'
)
and datediff(yy,出生日期,getdate())<22
--3物理系的老师比计算机系所有老师年龄都大的老师


select 教师名
from dbo.教师
where 系_ID=
(
select 系_ID
from 系
where 系名 like '%物理%'
)
and 出生日期< all
(
select 出生日期
from dbo.教师
where 系_ID =
(
select 系_ID
from 系
where 系名 like '%计算机%'
)
)
--4.--没讲过任何课的老师
select 教师名
from dbo.教师
where 教师名 not in
(
select 教师名
from dbo.教师 as js inner join dbo.教师认课 as jsrk
on  js.教师_ID = jsrk.教师_ID
)


--5.--计算机系06年开课的总学分
select sum(学分)
from dbo.课程 as kc inner join dbo.开课 as kk
on kc.课程_ID = kk.课程_ID
where 年度 = 2006
and 系_ID in
(
select 系_ID
from 系
where 系名 like '%计算机%'
)
--6.--2003年平均成绩在全校前50%的学生
select 姓名
from dbo.学生
where 学生_ID in
(
select top 50 percent 学生_ID
from
(
select xsxk.学生_ID,avg(成绩) as 平均成绩
from dbo.学生 as xs inner join dbo.学生选课 as xsxk
on xs.学生_ID = xsxk.学生_ID
where xs.届=2003
group by xsxk.学生_ID
)as l1
order by 平均成绩
)
--7.同时显示姓刘的老师和姓刘的学生的人数
select
(
select count(*) 
from dbo.学生
where 姓名 like '刘%'
) as 学生人数 ,
(
select count(*) 
from dbo.教师
where  教师名 like '刘%'
)教师人数
--8.在星期3第2节课105号教室上过课的学生的总人数
select count(姓名) as 学生人数
from dbo.课时安排 as ksap inner join dbo.开课 as kk
on ksap.开课_ID = kk.开课_ID inner join dbo.学生选课 as xsxk
on kk.开课_ID = xsxk.开课_ID inner join dbo.学生 as xs
on xsxk.学生_ID = xs.学生_ID
where 星期 = 2
and 节 = 2
and 教室号 = 106


--9 物理系都开过什么课
select 课程名
from dbo.课程 as kc inner join dbo.系 as xi
on kc.系_ID = xi.系_ID
where xi.系名= '物理系'
--10计算机系今年3科以上没及格的学生
select 姓名,count(*)
from dbo.学生选课 as xsxk inner join dbo.学生 as xs
on xsxk.学生_ID = xs.学生_ID
where xsxk.成绩<60
and xs.系_ID =
(
select 系_ID
from 系
where 系名 like '%计算机%'
)
and 开课_ID in 
(
select 开课_ID
from dbo.开课
where 年度 = year(getdate())
)
group by 姓名
having count(*)>3


--11列出全校所有学生数和物理系，计算机系的学生人数(提示：横向统计)
select count(学生_ID) as 学生人数,
(
select count(学生_ID)
from dbo.学生
where 系_ID = 3
)as 物理系,
(
select count(学生_ID)
from dbo.学生
where 系_ID = 1
)as 计算机系
from dbo.学生
--12老师平均年龄最大的系
select  top 1 系名
from
(
select 系_ID,avg(  datediff(yy,出生日期,getdate())   ) as 平均年龄
from dbo.教师
group by 系_ID
)as linshi inner join  dbo.系 as xi
on linshi.系_ID = xi.系_ID
order by linshi.平均年龄 desc


--13每科 都及格的学生
select   distinct 姓名,xs.学生_ID
from dbo.学生 as xs inner join dbo.学生选课 as xsxk
on xs.学生_ID = xsxk.学生_ID
where xsxk.学生_ID not in
(
select 学生_ID
from dbo.学生选课
where 成绩<60
)
--14 50岁以上的教师
select 教师名
from dbo.教师
where datediff(yyyy,出生日期,getdate())>=50
--15.每个系的最大年龄
select 系名 ,max(datediff(yyyy,出生日期,getdate())) as 最大年龄
from dbo.教师 as js inner join 系 as xi
on js.系_ID = xi.系_ID
group by  系名
---16.本学校的师生比例(提示：横向统计)
select
(
select count(教师_ID) as 教师数 
from dbo.教师
)/1.0/
(
select count(学生_ID) as 学生数
from dbo.学生
)
--17.每个系开设的课程总数
select 系名,count(*) as 课程总数
from dbo.课程 as kc inner join  dbo.系 as xi
on kc.系_ID = xi.系_ID
group by  系名
--18.  2005年学C++的人数
select count(学生_ID) as 人数
from
(
select distinct 学生_ID
from dbo.学生选课 as xsxk inner join dbo.开课 as kk
on xsxk.开课_ID = kk.开课_ID inner join dbo.课程 as kc
on kk.课程_ID = kc.课程_ID
where  kc.课程名 = 'java'
and kk.年度 = 2006
)as linshi






---19.历年学JAVA的人数
select 年度,count(xsxk.开课_ID)
from dbo.学生选课 as xsxk inner join dbo.开课 as kk
on xsxk. 开课_ID    = kk.开课_ID inner join  dbo.课程 as kc
on kk.课程_ID = kc.课程_ID
where 课程名 = 'java'
group by 年度
----20.将学生的成绩分为
100--90 A
89--70  B
70-50 C
50以下 D
四个层次，查询出2006年，不同分数等级的学生数


select  l1.等级,count(*)
from
(
select 
(
case 
when 成绩>=90 then 'A'
when 成绩>=70 then 'B'
when 成绩>=50 then 'C'
else 'D'
end
)as 等级,学生_ID,xsxk.开课_ID
from dbo.开课 as kk inner join dbo.学生选课 as xsxk
on kk.开课_ID = xsxk.开课_ID
where 年度 = 2006
)as l1
group by l1.等级
--21.统计全校师生中生日在1月份的人数，2月份人数，其它月份的人数
select *
from
(
select month(出生日期) as 月份,count(*) as 人数
from dbo.教师
group by month(出生日期)
union all
select month(出生日期),count(*)
from dbo.学生
group by month(出生日期)
)as lin
where lin.月份 is not null
order by lin.月份


--22. java前5名，VC后5名 的学生
select *
from
(
select top 5 xs.姓名,xsxk.成绩,kc.课程名
from dbo.课程 as kc inner join dbo.开课 as kk
on kc.课程_ID = kk.课程_ID inner join dbo.学生选课 as xsxk
on kk.开课_ID = xsxk.开课_ID inner join dbo.学生 as xs
on xsxk.学生_ID = xs.学生_ID
where  kc.课程名 = 'java'
order by xsxk.成绩 desc
union all
select top 5 xs.姓名,xsxk.成绩,kc.课程名
from dbo.课程 as kc inner join dbo.开课 as kk
on kc.课程_ID = kk.课程_ID inner join dbo.学生选课 as xsxk
on kk.开课_ID = xsxk.开课_ID inner join dbo.学生 as xs
on xsxk.学生_ID = xs.学生_ID
where  kc.课程名 = 'vc'
order by xsxk.成绩 desc
)as linshi


--23.随机抽取学生表中前10名学生做为奥运志原者
select  top 10 *
from dbo.学生
order by newid()


--24.横向列出2006, 2007年度 开课的门数 (用case when then)
select sum
(
case 年度
when 2006 then 1
else 0
end
)as '2006',
sum
(
case 年度
when 2007 then 1
else 0
end
)as '2007'
from dbo.开课
--25.年龄大于 学校老师平均年龄的  外语系老师
select 教师名
from dbo.教师
where 系_ID=
(
select 系_ID
from dbo.系
where 系名= '外语系'
)
and datediff(yy,出生日期,getdate()) >
(
select avg( datediff(yy ,出生日期,getdate() ) )
from dbo.教师
)


--26.每个系年龄最大的教师姓名
select lin2.教师名,lin1.最大年龄,xi.系名
from
(
select 系_ID,max(datediff(yy ,出生日期,getdate())) as 最大年龄
from dbo.教师
group by 系_ID 
)as lin1 inner join 
(
select  系_ID,教师名,datediff(yy ,出生日期,getdate()) as 年龄
from dbo.教师
)as lin2
on lin1. 系_ID=lin2. 系_ID inner join dbo.系 as xi
on lin1.系_ID = xi.系_ID
where lin2.年龄 = lin1.最大年龄


--27比物理系所有老师 年龄都大的 外语系老师
select 教师名,datediff(yy ,出生日期,getdate())as 年龄
from dbo.教师
where 系_ID = 
(
select 系_ID
from dbo.系
where 系名= '外语系'
)
and datediff(yy ,出生日期,getdate()) > all
(
select datediff(yy ,出生日期,getdate())
from dbo.教师
where 系_ID = 
(
select 系_ID
from dbo.系
where 系名= '物理系'
)
)
--28.每科 都及格的学生
select 姓名,学生_ID
from dbo.学生
where 学生_ID not in
(
select distinct 学生_ID
from dbo.学生选课
where 成绩<60
)


--29.张三丰教过的 姓张的物理系的学生
select 姓名
from dbo.学生 as xs inner join dbo.学生选课 as xsxk
on xs.学生_ID = xsxk.学生_ID
where 姓名 like '张%'
and 系_ID = 
(
select 系_ID
from dbo.系
where 系名= '物理系'
)
and xsxk.开课_ID in
(
select jsrk.开课_ID   --16  -17
from dbo.教师 as js inner join dbo.教师认课 as jsrk
on js.教师_ID = jsrk.教师_ID
where  教师名 = '张三丰'
)
--30. 毕业年级中学分不够8学分 的学生
select 姓名
from dbo.学生
where 届=year(getdate())-4
and 学生_ID in
(
select 学生_ID
from dbo.学生选课 as xsxk inner join dbo.开课 as kk
on xsxk.开课_ID = kk.开课_ID inner join dbo.课程 as kc
on kk.课程_ID = kc.课程_ID
where xsxk.成绩 >60
group by 学生_ID having  sum(kc.学分)<8
)

##################################################