SELECT A.民族, Count(A.民族) AS 人数 INTO mz
FROM (SELECT JS.mzdm, IIf([JS]![mzdm]='01','汉族','其它民族') AS 民族 FROM JS) AS A
GROUP BY A.民族
ORDER BY A.民族 DESC
11:
SELECT KCAP.bjbh AS 班级编号, Mid([KCAP].[xqbm],3,2) &"-" &Mid([KCAP].[xqbm],13,1) AS 学期编码, Count(KCAP.kcdm) AS 开课门数, Sum(KC.kss) AS 总课时数
FROM KC INNER JOIN KCAP ON KC.kcdm = KCAP.kcdm
GROUP BY KCAP.bjbh, Mid([KCAP].[xqbm],3,2) &"-" &Mid([KCAP].[xqbm],13,1)
ORDER BY KCAP.bjbh, Mid([KCAP].[xqbm],3,2) &"-" &Mid([KCAP].[xqbm],13,1)
12:
SELECT Left([KCAP].[xqbm],9) AS 学年, KCAP.bjbh AS 班级编号, Count(KCAP.kcdm) AS 开课门数, Sum(KC.kss) AS 总课时数
FROM KC INNER JOIN KCAP ON KC.kcdm = KCAP.kcdm
GROUP BY Left([KCAP].[xqbm],9), KCAP.bjbh
ORDER BY Left([KCAP].[xqbm],9)
13:
SELECT XS.bjbh AS 班级编号, XS.xh AS 学号, XS.xm AS 姓名, Avg(CJ.cj) AS 平均成绩 INTO bjcj
FROM XS INNER JOIN CJ ON XS.xh = CJ.xh
GROUP BY XS.bjbh, XS.xh, XS.xm
HAVING (((XS.bjbh) In (SELECT bjbh FROM xs WHERE xm = "史建平")) AND ((Avg(CJ.cj))>75))
ORDER BY Avg(CJ.cj) DESC
14:
SELECT YXDM.yxmc AS 院系统名称, YXDM.zymc AS 专业名称, IIf(M.男生人数 Is Null,0,M.男生人数) AS 男生人数, IIf(F.女生人数 Is Null,0,F.女生人数) AS 女生人数
FROM (YXDM LEFT JOIN (SELECT XS.yxzydm, Count(XS.xb) AS 男生人数
FROM XS
WHERE (((XS.xb)="男"))
GROUP BY XS.yxzydm) AS M ON YXDM.yxzydm = M.yxzydm) LEFT JOIN (SELECT XS.yxzydm, Count(XS.xb) AS 女生人数
FROM XS
WHERE (((XS.xb)="女"))
GROUP BY XS.yxzydm) AS F ON YXDM.yxzydm = F.yxzydm
ORDER BY YXDM.yxmc
--15.基于xs表查询所有1990年后出生同学的学号、姓名和出生日期，要求输出字段包括xh、xm、xb、csrq、jg和bjbh，结果按出生日期排序。select xh,XML_SCHEMA_NAMESPACE,xb,csrq,jq,bjbh from xs
where YEAR(csrq)>=1990
order by csrq asc
--16.基于xs表查询张姓和王姓同学的姓名和籍贯，要求输出字段包括xm和jg，不能有重复内容，结果按籍贯排序，相同时再按姓名排序。
select distinct xm,jg from xs
where xm like'张%'or xm like '王%'
order by jg ,xm
--17.基于CJ表查询每门课程的最高分、最低分、平均分、总分和选课人数，要求输出字段包括kcdm、最高分、最低分、平均分、总分和选课人数，结果按平均分降序排序。
select kcdm,max([成绩])as [最高分],min([成绩]) as[最低分],avg([成绩])as [平均分],sum([成绩])as[每门课程总分],count(*) from cj
group by kcdm
order by [平均分] --这个题目没有表结构可能有很多种情况，最好把成绩表贴出来
--18.基于xs表统计男女同学的人数。要求输出：性别和人数（字段名依次分别为xb和rs），且查询结果按性别降序排列，性别相同时按人数降序排列。
select xb,count(*) as rs from xs
group by xb
order by xb desc ,rs desc
--19.基于XS表和CJ表，查询选课的学生名单，要求输出字段为：xh和xm。
select xh,xm from from XS
where xh in(select xh from CJ) --如果用连接查询可能有漏洞
20.基于XS表和CJ表，查询班级编号为“050202”未选课的学生名单。要求输出字段为：xh、xm，查询结果按学号升序排序。（提示：可采用左联接）
select XS.xh,xm from XS left join CJ on XS.xh=CJ.xh
where CJ.xh is NULL and [班级编号]='050202'
--21.基于XS表和CJ表，查询每位学生参加考试的课程门数、平均成绩和不及格课程数（成绩小于60者不及格）。要求输出字段为XH、XM、选课门数、平均成绩、不及格课程数，查询结果仅显示平均成绩大于或等于75的记录。
select S.xh,xm,[课程门数],[平均成绩],[不及格课程数] from (
select a.xh ,[课程门数],[平均成绩],[不及格课程数] from (select xh,count(*) as [课程门数],avg([成绩]) as[平均成绩] from CJ group by xh )a join
(select xh,count(*) as[不及格课程数] from CJ where [成绩]<60 group by xh) b on a.xh=b.xh)S join XS on S.xh=XS.xh
where [平均成绩]>=75
--22.基于KC表和CJ表，查询每门课程参加考试的人数、平均成绩和不及格人数（成绩小于60者不及格）。要求：输出字段依次为KCDM、KCMC、人数、平均成绩、不及格人数，查询结果按平均成绩降序排序，查询去向为表ABC。
select S.kcdm,kcmc,[参加考试人数],[平均成绩],[不及格人数] into ABC from
(select a.kcdm,[参加考试人数],[平均成绩],[不及格人数] from
(select kcdm,count(*) as[参加考试人数] ,avg([成绩])as[平均成绩] from CJ group by kcdm) a join
(select kcdm,count(*) as[不及格人数] from CJ where [成绩]<60 group by kcdm)b on a.kcdm=b.kcdm)S join KC
on S.kcdm=KC.kcdm
order by [平均成绩]desc
--23.基于KC表和CJ表查询所有不能毕业的学生列表。要求查询输出字段为：学号、总学分，查询结果按总学分降序排序。注意：若成绩不低于60分学生可以取得对应课程的学分，总学分大于或等于20的学生可以毕业。
select xh,sum([学分])as [总学分] from ( select xh, CJ.kcdm,[学分] from CJ join KC on CJ.kcdm=KC.kcdm
where [成绩]>=60 )a
group by xh
having sum([学分])<20
--24.基于XS表、CJ表和KC表，查询每位学生取得学分的课程门数和总学成绩（成绩大于或等于60时才能取得学分）。要求：输出字段依次为XH、XM、课程门数、总学分，查询结果按总学分降序排序，输出总学分排在前10位同学。
SELECT XS.xh,xm,[课程门数],[总学分] from (select xh,count(*)as[课程门数],sum([学分]) as [总学分] from (select xh, CJ.kcdm,[学分] from CJ join KC on CJ.kcdm=KC.kcdm
where [成绩]>=60)a group by xh )b join XS on b.xh=XS.xh
--25.基于kc表和cj表，找出每门课程的kcdm、kcmc、选课人数、总成绩、平均分和最低分、最高分，结果先按总成绩降序排序，再按课程代码升序排序，把结果保存到kccj表文件中。
select CJ.kcdm,kcmc,count(*) as[选课人数],sum([成绩])as[总成绩],avg([成绩])as [平均成绩],min([成绩])as [最低分],max([成绩])as [最高分]
into kccj from CJ join KC
on CJ.kcdm=KC.kcdm
group by CJ.kcdm,kcmc
order by [总成绩]desc ,CJ.kcdm asc
--26.基于xs表和cj表，找出学生的xh、xm以及总成绩，要求输出总分前5名学习情况。
select top(5) xs.xh,xm ,sum([成绩])as[总成绩]from xs join cj on xs.xh=cj.xh
group by xs.xh,xm
order by [总成绩]desc
--27.基于xs表和cj表，找出每个学生的xh、xm、总成绩、平均成绩以及最低分，要求输出总分不低于480分，最低分不低于60分的所有学生学习情况，最终按学生xh进行排序。
select xs.xh,xm ,sum([成绩])as[总成绩],avg([成绩])as[平均成绩],min([成绩]) as[最低分]from xs join cj on xs.xh=cj.xh
group by xs.xh,xm
having sum([成绩])>=480 and min([成绩])>=60
order by xh
/*28. 已知教师表（JS）含有工号（gh，C）、院系专业代码（yxzydm，C）等字段；自由表图书借阅表（JYB）含有读者号（dzh，C）、
分类号（flh，C）、借阅日期（jyrq，D）、还书日期(hsrq，D)等字段，教师的读者号与工号相同。要求在JXGL项目创建查询jsjybchaxun：
基于JS表和JYB表查询各院系专业借书情况。要求：查询输出字段为yxzydm、借书本数、
最大借阅天数；查询结果按最大借阅天数降序排序；输出去向为临时表TEMP。
注：借阅天数为hsrq与jyrq之差，未还的图书按当前系统日期计算借阅天数。
*/
use JXGL
go
declare @date datetime
declare @hsrq datetime
set @date=getdate()
if hsrq is null
set @hsrq=@date
else
set @hsrq=(select hsrq from JYB)
select yxzydm,count(*) as[借书本数],max(hsrq-jyrq) as[最大借阅天数] from JYB join JS ON dzh=gh
group by yxzydm
