SQL练习

问题及描述：
--1.学生表
Student(SID,Sname,Sage,Ssex) --SID 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
--2.课程表
Course(CID,Cname,TID) --CID --课程编号,Cname 课程名称,TID 教师编号
--3.教师表
Teacher(TID,Tname) --TID 教师编号,Tname 教师姓名
--4.成绩表
SC(SID,CID,score) --SID 学生编号,CID 课程编号,score 分数
*/
--创建测试数据
create table Student(SID varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
create table Course(CID varchar(10),Cname nvarchar(10),TID varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(TID varchar(10),Tname nvarchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
create table SC(SID varchar(10),CID varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

--1，查询“01”课程比“02”课程成绩高的学生的信息及课程分数
--1.1，查询同时存在“01”课程和“02”课程的情况
选择a。*，b.score课程01的分数，c.score课程02的分数来自学生a，SC b，SC c
其中a.SID = b.SID和a.SID = c.SID和b.CID ='01'和c.CID =' 02'和b.score> c.score
--1.2，查询同时存在“01”课程和“02”课程的情况和存在“01”课程但可能不存在“02”课程的情况（不存在时显示为从以下内容
选择a。*，b.score课程01的分数，c.score课程02的分数来自学生a
左侧连接SC b on a.SID = b.SID和b。（以下存在相同内容时不再解释）CID =
'01 ' 左边连接SC c on a.SID = c.SID和c.CID = '02'
，其中b.score> isnull（c.score，0）

- 2，查询“01”课程比“02”课程成绩低的学生的信息及课程分数
--2.1，查询同时存在“01”课程和“02”课程的情况
选择a。*，b.score课程01的分数，c.score课程02的分数来自学生a，SC b，SC c
其中a.SID = b.SID和a.SID = c.SID和b.CID ='01'和c.CID =' 02'和b.score <c.score
--2.2，查询同时存在“01”课程和“02”课程的情况和不存在“01”课程的存在“02”课程的情况
选择a。*，b。得分课程01的分数，c.score课程02的分数来自学生a
左连接SC b on a.SID = b.SID和b.CID ='01'
左连接SC c on a.SID = c.SID and c .CID ='02'
其中isnull（b.score，0）<c.score

- 3，查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均
成绩选择a.SID，a.Sname，cast（avg（b.score）as decimal（18,2））avg_score
from Student a，sc b
其中a.SID = b.SID
group by a.SID，a.Sname with
cast（avg（b.score）as decimal（18,2））> = 60
order by a.SID

- 4，查询平均成绩小于60分的同学的学生编号和学生姓名和平均
成绩--4.1，查询在sc表存在成绩的学生信息的SQL语句。
选择a.SID，a.Sname，cast（avg） （b.score）as decimal（18,2））avg_score
from Student a，sc b
其中a.SID = b.SID
group by a.SID，a.Sname with
cast（avg（b.score）as decimal（18） ，2））<60
顺序a.SID -
4.2，查询在sc表中不存在成绩的学生信息的SQL语句。
选择a.SID，a.Sname，isnull（cast（avg（b.score）as as十进制（18,2）），0）avg_score
来自学生a左连接sc b
on a.SID = b.SID
group by a.SID，a.Sname has
isnull（cast（avg（b.score）as decimal（18） ，2）），0）<60
顺序由.SID

- 5，查询所有同学的学生编号，学生姓名，选课总数，所有课程的总
成绩--5.1，查询所有有
成绩的SQL。选择a.SID学生编号，a.Sname学生姓名，计数（b。 CID）选课总数，sum（得分）所有课程的总成绩 
来自学生a，SC b
其中a.SID = b.SID
group by a.SID，a.Sname
order by a.SID
--5.2，查询所有（包括有成绩和无
成绩）的SQL。选择a.SID学生编号，a.Sname学生姓名，count（b.CID）选课总数，sum（得分）所有课程的总成绩 
从学生左边加入SC b
上a。 SID = b.SID
group by a.SID，a.Sname
order by a.SID

- 6，查询“李”姓老师的数量
- 方法1
选择计数（Tname）李姓老师的数量来自老师，其中Tname喜欢'李％'
- 方法2
选择计数（Tname）李姓老师的数量来自左边的老师（Tname，1）='李'

- 7，查询学过“张三”老师授课的同学的信息
选择不同的学生。*来自学生，
学士，课程，教师，学生.SID = SC.SID和SC.CID = Course.CID和Course.TID = Student.TID和Teacher.Tname ='张三'
由Student.SID订购

- 8，查询没学过“张三”老师授课的同学的信息
从学生m中选择m。*，其中SID不在（从SC选择不同的SC.SID，课程，教师，其中SC.CID = Course.CID和Course.TID = Teacher.TID和Teacher.Tname ='张三'）由m.SID订购

--9，查询学过编号为“01”并且也学过编号为“02”的课程的同学的信息
- 方法1
从Student，SC中选择Student。*，其中Student.SID = SC.SID和SC。 CID ='01'并且存在（从SC SC_2中选择1，其中SC_2.SID = SC.SID和SC_2.CID ='02'）按Student.SID排序
- 方法2
选择学生。*来自学生，SC，其中学生。 SID = SC.SID和SC.CID ='02'并且存在（从SC SC_2中选择1，其中SC_2.SID = SC.SID和SC_2.CID ='01'）按Student.SID排序
- 方法3
选择m。 *从学生m，其中SID中
（
  从选择SID
  （
    选择从SC不同SID其中CID = '01'
    UNION ALL
    选择从SC，其中CID = '02'不同的SID
  ）t由SID组计数（1）= 2
）
按m.SID排序

--10，查询学过编号为“01”但是没有学过编号为“02”的课程的同学的信息
- 方法1
从Student，SC中选择Student。*，其中Student.SID = SC.SID和SC。 CID ='01'并且不存在（从SC SC_2中选择1，其中SC_2.SID = SC.SID和SC_2.CID ='02'）按Student.SID排序
- 方法2
从Student，SC中选择Student。* .SID = SC.SID和SC.CID ='01'且Student.SID不在（从SC SC_2中选择SC_2.SID，其中SC_2.SID = SC.SID和SC_2.CID ='02'）按Student.SID排序

--11，查询没有学全所有课程的同学的信息
--11.1，从Student，SC 中
选择Student。*
，
其中Student.SID = SC.SID
group by Student.SID，Student.Sname，Student.Sage，Student。 Ssex有计数（CID）<（从课程中选择计数（CID）） -
11.2
选择学生。*
来自学生左连接学生SC
上的SC.SID = SC.SID
组由Student.SID，Student.Sname，Student.Sage， Student.Ssex有计数（CID）<（从课程中选择计数（CID））

- 12，查询至少有一门课与学号为“01”的同学所学相同的同学的信息
select distinct Student。* from Student，SC其中Student.SID = SC.SID和SC.CID in（选择CID）来自SC，其中SID ='01'）和Student.SID <>'01'

--13，查询和“01”号的同学学习的课程完全相同的其他同学的信息
选择学生。*来自学生的SID
（从SC中选择不同的SC.SID，其中SID <>'01'和SC.CID in（从SC中选择不同的CID，
其中SID ='01'）SC.SID具有count（1）=（从SC中选择count（1），其中SID ='01'））

--14，查询没学过“张三”老师讲授的任一门课程的学生姓名
选择学生。*来自学生所在的学生。不在
（从sc，课程，教师sc.CID中选择sc.SID。 = course.CID和course.TID = teacher.TID和teacher.tname ='张三'）学生
订单.SID

--15，查询两门及其以上不及格课程的同学的学号，姓名及其平均
成绩选择student.SID，student.sname，cast（avg（score）as decimal（18,2））avg_score from student ，sc
其中student.SID = SC.SID和student.SID in（从SC中选择SID得分<60，SID得分（1）> = 2）按学生
分组.SID，student.sname

--16，检索“01”课程分数小于60，按分数降序排列的学生信息
选择学生。*，sc.CID，sc.score from student，sc
where student.S.SID = SC.SID and sc.score <60和sc.CID ='01'
由sc.score desc命令

--17，按平均成绩从高到低显示所有学生的所有课程的成绩以及平均
成绩--17.1 SQL 2000静态
选择a.SID学生编号，a.Sname学生姓名，
       max（案例c.Cname时'语文'然后b.score else null end）语文，
       max（案例c.Cname当'数学'然后b.score其他null结束）数学，
       max（案例c.Cname当'英语'然后b.score else null结束）英语，
       铸造（avg（b.score）为十进制（18,2））平均分
来自学生a
左连接SC b on a.SID = b.SID
左连接课程c on b.CID = c.CID
group by a。 SID，a.Sname
order by平均分desc
--17.2 SQL 2000动态
声明@sql nvarchar（4000）
设置@sql ='select a.SID'+'学生编号'+'，a.Sname'+'学生姓名'
选择@sql = @sql +'，max（情况c.Cname当'''+ Cname +'''然后b.score else null结束）'+ Cname +''
来自（从课程中选择不同的Cname）作为t
set @sql = @sql +'，cast（avg（b.score）as decimal（18,2））'+'平均分'+'来自学生一个左连接SC b on a.SID = b.SID左连接课程c on b.CID = c.CID
group by a.SID，a.Sname order by'+'flat mean class'+'
desc'exec（@sql）

--18，查询各科成绩最高分，最低分和平均分：以如下形式显示：课程ID，课程名称，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-及格为> = 60，中等为：70-80，优良为：80-90，优秀为：> = 90
- 方法1
选择m.CID课程编号，m .Cname课程名称， 
  max（n.score）最高分，
  min（n.score）最低分，
  cast（avg（n.score）as decimal（18,2））平均分，
  cast（（从SC中选择count（1），其中CID = m.CID并得分> = 60）* 100.0 /（从SC中选择计数（1），其中CID = m.CID）为十进制（18,2））及格率，强制转换
  （（从SC中选择计数（1），其中CID = m.CID和得分> = 70且得分<80）* 100.0 /（从SC中选择计数（1），其中CID = m.CID）为十进制（18,2））中等率，
  cast（（从SC中选择计数（1），其中CID = m .CID和得分> = 80且得分<90）* 100.0 /（从SC中选择计数（1），其中CID = m.CID）为十进制（18,2））优良率，
  cast（（从SC中选择count（1），其中CID = m.CID，得分> = 90）* 100.0 /（从SC中选择count（1），其中CID = m.CID）为十进制（18,2））优秀率 
from course m，SC n
其中m.CID = n.CID
group by m.CID，m.Cname
order by m.CID
- 方法2
选择m.CID课程编号，m .Cname课程名称， 
  （选择max（得分） ）来自SC，其中CID = m.CID）最高分，
  （从SC中选择min（得分），其中CID = m.CID）最低分，
  （从SC 选择演员（avg（得分）为十进制（18,2）） CID = m.CID）平均分，
  cast（（从SC中选择count（1），其中CID = m.CID并且得分> = 60）* 100.0 /（从SC中选择计数（1），其中CID = m.CID）as十进制（18,2））及格率，
  cast（（从SC中选择count（1），其中CID = m.CID，得分> = 70且得分<80）* 100.0 /（从SC中选择计数（1），其中CID = m.CID）为十进制（18,2） ））中等率，
  cast（（从SC中选择count（1），其中CID = m.CID并且得分> = 80并且得分<90）* 100.0 /（从SC中选择计数（1），其中CID = m.CID）as十进制（18,2））优良率，强制转换
  （（从SC中选择计数（1），其中CID = m.CID且得分> = 90）* 100.0 /（从SC中选择计数（1），其中CID = m.CID） as decimal（18,2））优秀率
来自
m.CID的Course m  order

- 19，按各科成绩进行排序，并显示排名
--19.1 sql 2000用子
查查完成 - 分数重复时保留名次空缺
选择t。*，px =（从SC中选择count（1），其中CID = t .CID和得分> t.score）+ 1来自sc t order by t.cid，px 
--Score重复时合并名次
select t。*，px =（从SC中选择计数（不同分数），其中CID = t.CID并得分> = t.score）来自sc t order by t.cid，px 
--19.2 sql 2005用秩，DENSE_RANK完成
--Score重复时保留名次空缺（rank完成）
select t。*，px = rank（）通过t.CID从sc t顺序开始（由cid顺序除以分数desc），px 
--Score重复时合并名次（DENSE_RANK完成）
选择t。*，px = DENSE_RANK（）结束（通过cid顺序按分数desc分区） ）从t.CID，px开始 

--20，查询学生的总
成绩并进行排名--20.1查询学生的总
成绩选择m.SID学生编号， 
       m.Sname学生姓名，
       isnull（sum（score），0）总成绩 
来自Student m left join SC n在m.SID = n.SID 
group by m.SID，m.Sname
order by总
成绩desc --20.2查询学生的总成绩并进行排名，sql 2000用子查查完成，分总分复制时保留名次空缺和不保留名次空缺两种。
选择t1。*，px =（从
（
  选择m.SID学生编号， 
         m.Sname学生姓名，
         isnull（sum（score），0）中  选择count（1） 
  来自Student m左边连接SC n on m.SID = n.SID 
  group by m.SID，m.Sname
）t2 where总成绩> t1。总  成绩）+ 1来自 
（
选择m.SID学生编号， 
         m.Sname学生姓名，
         isnull （总和（得分），0）总成绩 
  从学生m左边加入SC n on m.SID = n.SID 
  group by m.SID，m.Sname
）t1
order by px

select t1。*，px =（select count（distinct总  成绩）from 
（
选择m.SID学生编号， 
         m.Sname学生姓名，
         isnull（sum（score），0）总成绩 
  来自学生m左连接SC n on m .SID = n.SID 
  group by m.SID，m.Sname
）t2 where总成绩> = t1。总  成绩）来自 
（
选择m.SID学生编号， 
         m.Sname学生姓名，
         isnull（sum（score），0 ）总成绩 
  来自学生m左边加入SC n on m.SID = n.SID 
  group by m.SID，m.Sname
）t1
order by px
--20.3查询学生的总成绩并进行排名，sql 2005用排名，DENSE_RANK完成，分总重复时保留名次空缺和不保留名次空缺两种。
选择t。*，px = rank（）结束（按总  成绩desc排序）
（
选择m.SID学生编号， 
         m.Sname学生姓名，
         isnull（sum（score），0）总成绩 
  来自学生m左边加入SC n on m.SID = n.SID 
  group by m.SID，m.Sname
）t
order by px

选择t。*，px = DENSE_RANK（）over（按总  成绩desc排序）
（
选择m.SID学生编号， 
         m.Sname学生姓名，
         isnull（sum（score），0）总成绩 
  来自Student m left join SC n在m.SID = n.SID 
  group by m.SID，m.Sname
）t
order by px

- 21，查询不同老师所教不同课程平均分从高到低显示 
选择m.TID，m.Tname，cast（avg（o.score）as decimal（18,2））avg_score
from Teacher m，Course n ，SC o
其中m.TID = n.TID，n.CID = o.CID
group by m.TID，m.Tname
order by avg_score desc

- 22，查询所有课程的成绩第2名到第3名的学生信息及该课程
成绩--22.1 sql 2000用子查询完成 -
Score重复时保留名次空缺
select * from（select t。*，px =（从SC中选择count（1），其中CID = t.CID并得分> t.score）+ 1来自sc t）m其中px在2和3之间依次为m.cid，m.px 
--Score重复时合并名词
select * from（select t。*，px =（从SC中选择计数（不同分数），其中CID = t.CID，得分> = t.score）来自sc t）m，其中px在2和3之间，以m为单位。 cid，m.px 
--22.2 sql 2005用秩，DENSE_RANK完成
--Score重复时保留名次空缺（rank完成）
select * from（select t。*，px = rank（）over（cid order by partition desc） ）来自sc t）m其中px介于2和3之间，按m.CID，m.px 
--Score重复时合并名次（DENSE_RANK完成）
select * from（select t。*，px = DENSE_RANK（）over（cid order by scc order by sc t）m，其中px在2到3之间，按m.CID，m.px排序 

- 23，统计各科成绩各分数段人数：课程编号，课程名称，100-85,85-70,70-60,0-60及所占 
百分比--23.1统计各科成绩各分数段人数：课程编号，课程名称，100-85,85-70,70-60,0-60 
- 横向显示
选择Course.CID课程编号，Cname为课程名称，
  sum（得分> = 85然后1其他0结束时的情况）85-100，
  总和（得分> = 70且得分<85然后其他0结束时的情况）70-85，
  总和（得分> = 60且得分<70然后其他0结束时的情况）60-70，
  总和（得分<60然后其他0结束的情况）0-60 
来自sc，课程 
SC.CID = Course.CID 
group by Course.CID，Course.Cname
order by Course.CID
- 显示存在的分数段）
选择m.CID课程编号，m .Cname课程名称，分数段=（
  当n.score> = 85然后'85 -100'时的情况
       当n.score> = 70且n.score <85然后'70 -85'，
       当n.score> = 60且n.score <70然后'60 -70'，
       否则'0-60'
  结束）， 
  count（1 ）数量 
来自Course m，sc n
其中m.CID = n.CID 
group by m.CID，m.Cname，（
  当n.score> = 85然后'85 -100'，
       当n.score> = 70和n时的情况） .score <85然后'70 -85'
       当n.score> = 60且n.score <70然后'60 -70'，
       否则'0-60'
  结束）
由m.CID，m.Cname，分数段
- - 纵向显示2（显示存在的分数段，不存在的分数段用0显示）
选择m.CID课程编号，m.Cname课程名称，分数段=（
  当n.score> = 85然后'85 -100时的情况'
       当n.score>= 70和n.score <85然后'70 -85'
       当n.score> = 60且n.score <70然后'60 -70'，
       否则'0-60'
  结束）， 
  计算（1）数量 
来自Course m，sc n
其中m.CID = n.CID 
group by all m.CID，m.Cname，（
  当n.score> = 60时，当n.score> = 85然后是'85 -100'
       ，
       当n.score> = 60时n.score <= 85然后n.score <85然后'70 -85' 并且n.score <70然后'60 -70'
       其他'0-60'
  结束）
由m.CID，m.Cname，分数段订购

- 23.2统计各科成绩各分数段人数：课程编号，课程名称，100-85,85-70,70-60，<60及所占百分比 
- 横向显示
选择m.CID课程编号，m.Cname课程名称，
  （从SC中选择计数（1），其中CID = m.CID且得分<60）0-60，
  演员（（从SC中选择计数（1），其中CID = m.CID且得分<60）* 100.0 / （从SC中选择count（1），其中CID = m.CID）为十进制（18,2））百分比，
  （从SC中选择计数（1），其中CID = m.CID，得分> = 60，得分<70）60 -70，
  强制转换（（从SC中选择计数（1），其中CID = m.CID，得分> = 60，得分<70）* 100.0 /（从SC中选择计数（1），其中CID = m.CID）为十进制（ 18,2））百分比，
  （从SC中选择计数（1），其中CID = m.CID，得分> = 70，得分<85）70-85，
  cast（（从SC中选择count（1），其中CID = m.CID并且得分> = 70且得分<85）* 100.0 /（从SC中选择计数（1），其中CID = m.CID）为十进制（18,2） ））百分比，
  （从SC中选择计数（1），其中CID = m.CID并且得分> = 85）85-100，
  施放（（从SC中选择计数（1），其中CID = m.CID并且得分> = 85） * 100.0 /（从SC中选择计数（1），其中CID = m.CID）为十进制（18,2））百分比 
从课程m 
顺序m.CID
- 纵向显示1（显示存在的分数段）
选择m。 CID课程编号，m.Cname课程名称，分数段=（
  当n.score> = 85然后'85 -100'，
       当n.score> = 70且n.score <85然后'70 -85'
       时n）。得分> = 60且n.score <70然后'60 -70'
       否则'0-60'
  结束）， 
  count（1）数量，  
  cast（count（1）* 100.0 /（从sc中选择count（1），其中CID = m.CID）为十进制（18,2））百分比 
来自Course m，sc n
其中m.CID = n.CID 
group by m .CID，m.Cname，（
  n.score> = 85然后'85 -100'，
       当n.score> = 70且n.score <85然后'70 -85'，
       当n.score> = 60和n时的情况） .score <70然后'60 -70'
       其他'0-60'
  结束）
由m.CID，m.Cname，分数段
- 纵向显示2（显示存在的分数段，不存在的分数段用0显示） ）
选择m.CID课程编号，m.Cname课程名称，分数段=（
  n.score> = 85时的情况，然后
       当n.score> = 70且n.score <85然后'70 -85时为'85 -100' '
       当n.score> = 60且n.score <70然后'60 -70'
       别的'0-60'
  结束）， 
  count（1）数量，  
  cast（count（1）* 100.0 /（从sc中选择count（1），其中CID = m.CID）as decimal（18,2））百分比
来自Course m，sc n
其中m.CID = n.CID 
组由所有m.CID，m.Cname组成（
  当n.score> = 85然后'85 -100'
       时n.score> = 70且n.score <85然后'70 -85'
       时n .score> = 60和n.score <70然后'60 -70'，
       否则'0-60'
  结束）
按m.CID，m.Cname，分数段排序

 

--24，查询学生平均成绩及其名次 -
24.1查询学生的平均成绩并进行排名，sql 2000用子查询完成，分平均成绩重复时保留名次空缺和不保留名次空缺两种。
选择t1。* ，px =（选择count（1）from
（
  选择m.SID学生编号，
         m.Sname学生姓名，
         isnull（cast（avg（得分）为十进制（18,2）），0）平均成绩 
  来自Student m left join SC n on m.SID = n.SID
  group by m.SID，m.Sname
）t2，其中平均成绩> t1。平均  成绩）+ 1来自
（
选择m.SID学生编号，
         m.Sname学生姓名，
         isnull） （avg（得分）为十进制（18,2）），0）平均成绩 
  来自学生m左连接SC n on m.SID = n.SID
  group by m.SID，m.Sname
）t1
order by px

select t1。*，px =（select count（distinct平均  成绩）from
（
select m.SID学生编号，
         m.Sname学生姓名，
         isnull（cast（avg（score）as decimal（18,2）），0）平均成绩 
  来自学生m左边加入SC n on m.SID = n.SID
  group by m.SID，m.Sname
）t2，其中平均成绩> = t1。平均  成绩）来自
（
选择m.SID学生编号，
         m.Sname学生姓名，
         isnull（cast（avg（得分）为十进制（18,2）），0）平均成绩 
  来自学生m左连接SC n on m.SID = n.SID
  group by m.SID，m.Sname
）t1
order通过px
--24.2查询学生的平均成绩并进行排名，sql 2005用秩，DENSE_RANK完成，分平均成绩重复时保留名次空缺和不保留名次空缺两种。
选择t。*，px = rank（）over（订购平均成绩desc）from
（
  选择m.SID学生编号，
         m.Sname学生姓名，
         isnull（cast（avg（得分）为十进制（18,2）），0）平均成绩 
  来自学生m左连接SC n on m.SID = n.SID
  group由m.SID，m.Sname
）
按px排序

选择t。*，px = DENSE_RANK（）over（按平均  成绩desc排序）
（
选择m.SID学生编号，
         m.Sname学生姓名，
         isnull（cast（avg（score）as decimal（18,2））， 0）平均成绩 
  来自学生m左边连接SC n on m.SID = n.SID
  group by m.SID，m.Sname
）t
order by px
 
--25，查询各科成绩前三名的记录
--25.1分数重复时保留名次空缺
从学生m，SC n中选择m。*，n.CID，n.score，其中m.SID = n.SID和n.score in
（从sc中选择前3个得分，其中CID = n.CID顺序通过得分desc）由n.CID命令，n.score desc
--25.2分数重复时不保留名次空缺，合并名次
--sql 2000用子查询实现
select * from（select t。*，px =（从SC中选择计数（不同分数），其中CID = t.CID，得分> = t.score）来自sc t）m，其中px介于1和3之间，按m.Cid排序，m.px
--sql 2005用DENSE_RANK实现
select * from（select t。*，px = DENSE_RANK（）over（cid order by score desc）来自sc t）m，其中px在1和3之间，以m为单位。 CID，m.px

-26，查询每门课程被选修的学生数
选择Cid，count（SID）学生数来自sc group by CID

- 27，查询出只有两门课程的全部学生的学号和姓名
选择Student.SID，Student.Sname
来自学生，SC
其中Student.SID = SC.SID
group by Student.SID，Student.Sname
有计数（ SC.CID）=
Student.SID的2个订单

- 28，查询男生，女生
人数选择计数（Ssex）作为男生人数来自学生，其中Ssex = N'男'
选择计数（Ssex）为学生的女生人数，其中Ssex = N'女'
选择总和（Ssex =时的情况= N'男'然后1其他0结束）男生人数，总和（当Ssex = N'女'然后1其他0结束时的情况）女生人数从学生
选择案例当Ssex = N'男'然后N'男生人数'其他N '女生人数'结束男女情况，数（1）人数来自学生群体Ssex = N'男'然后N'男生人数'其他N'女生人数'结束

--29，查询名字中含有“风”字的学生信息
select * from student where sname like N'％
wind ％' select * from student where charindex（N'风'，sname）> 0

--30，查询同名同性学生名单，并统计同名人数
选择Sname学生姓名，计数（*）人数来自学生组的Sname有计数（*）> 1

- 31，查询1990年出生的学生名单（注：学生表中Sage列的类型是datetime）
select * from Student where year（sage）= 1990
select * from student where datediff（yy，sage，'1990-01 -01'）= 0
select * from Student其中datepart（yy，sage）= 1990
select * from Student where convert（varchar（4），sage，120）='1990'

- 32，查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
选择m.CID，m.Cname，cast（avg（n.score）as decimal（18 ，2））avg_score
from Course m，SC n
其中m.CID = n.CID   
group by m.CID，m.Cname
order by avg_score desc，m.CID asc

--33，查询平均成绩大于等于85的所有学生的学号，姓名和平均
成绩选择a.SID，a.Sname，cast（avg（b.score）as decimal（18,2））avg_score
from Student a ，sc b
其中a.SID = b.SID
group by a.SID，a.Sname with
cast（avg（b.score）as decimal（18,2））> = 85
order by a.SID

--34，查询课程名称为“数学”，且分数低于60的学生姓名和分数
选择sname，得分
来自学生，SC，课程
SC.SID = Student.SID和SC.CID = Course.CID and Course .Cname = N'数学'并得分<60

--35，查询所有学生的课程及分数情况;
选择学生。*，Course.Cname，SC.CID，SC.score 
来自学生，SC，课程
，其中Student.SID = SC.SID和SC.CID =课程。
由Student.SID，SC.CID订购的CID

- 36，查询任何一门课程成绩在70分以上的姓名，课程名称和分数;
选择学生。*，Course.Cname，SC.CID，SC.score 
来自学生，SC，课程
，其中Student.SID = SC .SID和SC.CID = Course.CID和SC.score> = 70学生
顺序.SID，SC.CID

--37，查询不及格的课程
选择Student。*，Course.Cname，SC.CID，SC.score 
来自Student，SC，
Course.School.SID = SC.SID和SC.CID = Course.CID和SC。
由Student.SID，SC.CID 评分<60

--38，查询课程编号为01且课程成绩在80分以上的学生的学号和姓名;
。选择学生*，Course.Cname，SC.CID，SC.score 
从学生，SC，当然
其中Student.SID = SC.SID和SC.CID = Course.CID和SC.CID ='01'和SC.score> = 80
by Student.SID，SC.CID

--39，求每门课程的学生
人数选择Course.CID，Course.Cname，count（*）学生人数 
来自Course，SC
其中Course.CID = SC.CID
group by Course.CID，Course.Cname
order by Course .CID，Course.Cname

--40，查询选修“张三”老师所授课程的学生中，成绩最高的学生信息及其
成绩--40.1当最高分只有一个时
选择前1名学生。*，Course.Cname，SC.CID， SC.score 
从学生，SC，场，教师
其中Student.SID = SC.SID和SC.CID = Course.CID和Course.TID = Teacher.TID和Teacher.Tname = N '张三'
由SC.score顺序desc
--40.2当最高分出现多个时
选择Student。*，Course.Cname，SC.CID，SC.score 
来自学生，SC，课程，教师
，其中Student.SID = SC.SID和SC.CID =课程。 CID和Course.TID = Teacher.TID和Teacher.Tname = N'hang三'和
SC.score =（从SC，课程，教师中选择最大（SC.score），其中SC.CID = Course.CID和Course.TID = Teacher.TID和Teacher.Tname = N'张三'）

- 41，查询不同课程成绩相同的学生的学生编号，课程编号，学生成绩
- 方法1
从SC m中选择m。*（选择CID，通过CID从SC组得分，得分有计数（1）> 1）n
其中m.CID = n.CID和m.score = n.score order by m.CID，m.score，m.SID
- 方法2
从SC m中选择m。*存在（从中选择1）选择CID，按CID从SC组得分，得分有计数（1）> 1）n
，其中m.CID = n.CID，m.score = n.score）按m.CID，m.score，m.SID排序

- 42，查询每门功能成绩最好的前两名
从sc获得t。*得分（从sc中选择前2分，其中CID = T.CID，按分数desc排序）顺序为t.CID，t .score desc

--43，统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列 
选择Course.CID ，Course.Cname，count（*）学生人数 
来自Course，SC
其中Course.CID = SC.CID
group by Course.CID，Course.Cname
具有count（*）> = 5
order by学生人数desc，Course.CID

--44，检索至少选修两门课程的学生学号
选择student.SID，student.Sname
来自学生，SC
其中student.SID = SC.SID
group by student.SID，student.Sname
having count（1）> = 2学生
订单.SID

- 45，查询选修了全部课程的学生信息
- 方法1根据数量来完成
选择学生。*来自学生的SID
（通过SID从sc组中选择SID计数（1）=（从中选择计数（1）当然））
- 方法2使用双重否定来完成
选择t。*来自学生t，其中t.SID不在
（
  选择不同的m.SID
  （
    选择SID，CID来自学生，课程
  ）m不存在（从中选择1） sc n其中n.SID = m.SID和n.CID = m.CID）
）
- 方法3使用双重否定来完成
从学生t中选择t。*不存在（从中选择1
（
  从中选择不同的m.SID）
  （
    从学生选择SID，CID，课程
  ）m不存在（从sc n中选择1，其中n.SID = m.SID和n.CID = m.CID）
）k，其中k.SID = t.SID
）

--46，查询各学生的年龄
--46.1只按照年份来算
select *，datediff（yy，sage，getdate（））年龄来自学生
--46.2按照出生日期来算，当前月日<出生年月的月日则，年龄减一
选*，右边的情况（转换（varchar（10），getdate（），120），5）<right（convert（varchar（10），sage，120），5）then datediff（ yy，sage，getdate（）） - 另外1个日期（yy，sage，getdate（））end year age来自学生

--47，查询本周过生日的学生
select * from student where datediff（week，datename（yy，getdate（））+ right（convert（varchar（10），sage，120），6），getdate（）） = 0

--48，查询下周过生日的学生
select * from student where datediff（week，datename（yy，getdate（））+ right（convert（varchar（10），sage，120），6），getdate（）） = -1

- 49，查询本月过生日的学生
select * from student where datediff（mm，datename（yy，getdate（））+ right（convert（varchar（10），sage，120），6），getdate（）） = 0

--50，查询下月过生日的学生
select * from student where datediff（mm，datename（yy，getdate（））+ right（convert（varchar（10），sage，120），6），getdate（）） = -1

drop table学生，课程，老师，SC