##################################################
--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
)
--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
)
##################################################