--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 分数
1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
解析：多表查询，自连接
select a.*,SC.CID,score from Student a,SC b1,SC b2
where a.SID=sc.SID and a.SID=b1.SID and a.SID=b2.SID and b1.CID='01'and b2.CID='02' and b1.score > b2.score
上面可以改写为,区别在于上面为竖排CID，下面为横排：
select a.*,b1.score,b2.score from Student a,SC b1,SC b2
where a.SID=b1.SID and a.SID=b2.SID and b1.CID='01'and b2.CID='02' and b1.score > b2.score
上面可以改写为,处理不存在02课程的情况：
select a.*,b1.score,b2.score from Student a
left join SC b1 on a.SID=b1.SID and b1.CID='01'
left join SC b2 on a.SID=b1.SID and b1.CID='02'
where b1.score > isnull（b2.score,0）
2.查询"01"课程比"02"课程成绩低的学生的信息及课程分数
解析：同上，01,02 互换就行了
3.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
解析：多表查询,cast(avg(b.score) as decimal(18.2)) avg_score 代替 avg(b.score) 确定精度小数位数
select a.SID,a.Sname,b.avg(score) from Student a, SC b
where a.SID=b.SID
group by a.SID,a.Sname
having avg(b.score) >= 60
order by a.SID
4.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
解析：同上,>= 变成 <即可;另外，如果学生表有名字，但成绩表不存在成绩可以用：
having isnull(cast(avg(b.score) as decimal(18,2)),0) < 60 代替having avg(b.score)
5.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
解析：多表查询
①：（有成绩）
select a.SID,a.Sname,count(b.CID) C_nm,sum(b.score) C_totalscore
from Student a , SC b
where a.SID=b.SID
group by a.SID,a.Sname
order by a.SID
②：（包括有成绩和无成绩）
select a.SID,a.Sname,count(b.CID) C_nm,sum(b.score) C_totalscore
from Student a left join SC b on a.SID=b.SID
group by a.SID,a.Sname
order by a.SID
6.查询"李"姓老师的数量
解析： like
select count(TID) T_nm_Li from Teacher where Tname like '李%' --'李%'=CONCAT('李', '%')
或者：
select count(TID) T_nm_Li from Teacher where left(Tname,1)='李'
7.查询学过"张三"老师授课的同学的信息
select a.* from Student a, SC b, Course c,Teacher d
where a.SID=b.SID and b.CID=C.CID and c.TID=d.TID and d.Tname ='张三'
8.查询没学过"张三"老师授课的同学的信息
select * from Student where SID not in
(select a.* from Student a, SC b, Course c,Teacher d
where a.SID=b.SID and b.CID=C.CID and c.TID=d.TID and d.Tname ='张三'
)
9.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
解析：先查询学过01的，同时加入子查询学过02的
①：--exists
select Student.* from Student , SC
where Student.SID = SC.SID and SC.CID = '01'
and exists (Select 1 from SC SC_2 where SC_2.SID = SC.SID and SC_2.CID = '02')
order by Student.SID
②：--union all
select m.* from Student m where SID in
(
select SID from
(
select distinct SID from SC where CID = '01'
union all
select distinct SID from SC where CID = '02'
) t group by SID having count(1) = 2 --记录数为2
)
order by m.SID
③：简洁
SELECT
a.* FROM
student a,
sc b1,
sc b2
WHERE a.SID = b1.SID AND a.SID = b2.SID AND b1.CID = '01' AND b2.CID = '02'
④：--
select * from Student where SID in
(select a.SID,count(b.CID) from Student a, SC b
where a.SID=b.SID and (b.CID='01' or b.CID='02')
group by a.SID
having count(b.CID)=2
)
⑤：--
select Student.* from Student , SC
where Student.SID = SC.SID and (SC.CID = '01' or SC.CID = '02')
group by Student.SID having count(1) = 2
10.查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
解析：先查询学过01的，同时加入子查询非学过02的
select Student.* from Student , SC
where Student.SID = SC.SID and SC.CID = '01'
and not exists (Select 1 from SC SC_2 where SC_2.SID = SC.SID and SC_2.CID = '02')
order by Student.SID
11.查询没有学全所有课程的同学的信息
解析：比较一下课程表中课程数和成绩表中每个学生的课程数，返回学生表信息
select a.* from Student a,SC b where a.SID=b.SID
group by a.SID
having count(b.CID)<(select count(c.CID) form Course)
order by a.SID
或者：
select Student.*
from Student left join SC
on Student.SID = SC.SID
group by Student.SID , Student.Sname , Student.Sage , Student.Ssex
having count(CID) < (select count(CID) from Course)
12.查询至少有一门课与学号为"01"的同学所学相同的同学的信息
解析：比较其他学生和01学生课程名称、in、distinct 避免重复
select distinct a.* from Student a,SC b where a.SID<>'01'and a.SID=b.SID and b.CID in
(select CID from SC where SID='01')
13.查询和"01"号的同学学习的课程完全相同的其他同学的信息
解析：在上面的基础上，多加一个查询记录和01学生课程记录数相等即可
select distinct a.* from Student a,SC b where a.SID<>'01'and a.SID=b.SID and b.CID in
(select CID from SC where SID='01')
group by a.SID,a.Sname , a.Sage , a.Ssex
having count(b.CID)=(select count(CID) from SC where SID='01')
select Student.* from Student where SID in
(select distinct SC.SID from SC where SID <> '01'
and SC.CID in (select distinct CID from SC where SID = '01')
group by SC.SID having count(1) = (select count(1) from SC where SID='01')
)
14.查询没学过"张三"老师讲授的任一门课程的学生姓名
解析：查询张三老师讲授过的学生名字，not in
select * from Student where SID not in
(select distinct a.SID from SC a,Course b, Teacher c
where a.CID=b.CID and b.TID=c.TID and c.Tname='张三')
15.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select a.SID,a.Sname,avg(b.score) from Student a,SC b
where a.SID=b.SID and a.SID in (select SID from SC where score <60 group by SID count(1)>=2)
group by a.SID,a.Sname
16.检索"01"课程分数小于60，按分数降序排列的学生信息
select a.*,b.score from Student a, SC b where b.SID='01'and b.score <60 and a.SID=b.SID
order by b.score DESC
17.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
解析：按照文字题意，展示所有课程的成绩及平均成绩，可以有两周展示方案，
第一种列分布各科成绩及平均成绩（推荐）；第二种行分布
select a.Sname，
sum(case when c.Cname='语文' then b.score else null end) Score_chinese,
sum(case when c.Cname='数学' then b.score else null end) Score_math
--(.......)
avg(score) avg_score
from Student a
left join SC b on a.SID=b.SID
left join Course c on b.CID=c.CID
group by a.Sname
order by avg(score) DESC
18.查询各科成绩最高分、最低分和平均分：以如下形式显示
课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
解析：这里的*100 加不加就是结果是 72.22 与结果是 0.72 的区别 最好加|| ‘%’？
select m.CID 课程编号 , m.Cname 课程名称 ,
max(n.score) 最高分 ,
min(n.score) 最低分 ,
cast(avg(n.score) as decimal(18,2)) 平均分 ,
cast((select count(1) from SC where CID = m.CID and score >= 60)*100.0 / (select count(1) from SC where CID = m.CID) as decimal(18,2)) 及格率 ,
cast((select count(1) from SC where CID = m.CID and score >= 70 and score < 80 )*100.0 / (select count(1) from SC where CID = m.CID) as decimal(18,2)) 中等率 ,
cast((select count(1) from SC where CID = m.CID and score >= 80 and score < 90 )*100.0 / (select count(1) from SC where CID = m.CID) as decimal(18,2)) 优良率 ,
cast((select count(1) from SC where CID = m.CID and score >= 90)*100.0 / (select count(1) from SC where CID = m.CID) as decimal(18,2)) 优秀率
from Course m , SC n
where m.CID = n.CID
group by m.CID , m.Cname
order by m.CID
和下面基本没有啥区别，上面是直接max聚合的，需要后面写好 group by ，下面是直接写好最高的select的
select m.CID 课程编号 , m.Cname 课程名称 ,
(select max(score) from SC where CID = m.CID) 最高分 ,
(select min(score) from SC where CID = m.CID) 最低分 ,
(select cast(avg(score) as decimal(18,2)) from SC where CID = m.CID) 平均分 ,
cast((select count(1) from SC where CID = m.CID and score >= 60)*100.0 / (select count(1) from SC where CID = m.CID) as decimal(18,2)) 及格率,
cast((select count(1) from SC where CID = m.CID and score >= 70 and score < 80 )*100.0 / (select count(1) from SC where CID = m.CID) as decimal(18,2)) 中等率 ,
cast((select count(1) from SC where CID = m.CID and score >= 80 and score < 90 )*100.0 / (select count(1) from SC where CID = m.CID) as decimal(18,2)) 优良率 ,
cast((select count(1) from SC where CID = m.CID and score >= 90)*100.0 / (select count(1) from SC where CID = m.CID) as decimal(18,2)) 优秀率
from Course m
order by m.CID
19.按各科成绩进行排序，并显示排名
解析：rank() over ( partition by order by ) 保留名次空缺和合并名次
select a.*,rank() over(partition by CID order by CID DESC ) as rank from SC a order by a.CID, rank
20.查询学生的总成绩并进行排名
解析：查询总成绩 然后排序 根据需要可以分 rank 还是 dense rank
select b.*,rank() over (order by score_total desc) rank from
(select a.SID,a.Sname,isnull(sum(b.score),0)score_total from Student a
left join SC b on a.SID=b.SID
group by a.SID,a.Sname
) b
order by rank
21.查询不同老师所教不同课程平均分从高到低显示
select a.Tname,b.CID,avg(c.score) avg_score from Teacher a
left join Course b on a.TID=b.TID
left join SC c on b.CID=c.ICD
group by a.Tname,b.CID
order by avg_score desc
22.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
解析：partition by CID, having rank between 2 to 3
select a.*,b.score,rank() over (partition by CID order by score) rank from
Student a,SC b where a.SID=b.SID and (rank between 2 to 3 )
select * from
(select t.* , px = rank() over(partition by cid order by score desc) from sc t) m
where px between 2 and 3
order by m.CID , m.px
23.统计各科成绩各分数段人数：课程编号,课程名称, 100-85 , 85-70 , 70-60 , 0-60 及所占百分比
解析： two question 分数段人数和占比 count(1) case when then else end
排列：分数段作为c列字段值，人数和占比作为d、f列字段名；或者分数段及占比作为c、d、e、f、g、h、i、j列字段名，即分为纵向和横向显示两种结果。
select a.CID,b.Cname,
case when score>=85 then '100-85'
when score>=70 and score<85 then '85-70'
when score>=60 and score<70 then '70-60'
when score<60 then '0-60' End
as flag,
count(1) as S_nm
count(1)/(select count(1) from a where a.CID=b.CID) as S_nm%
from SC a, Course b
where a.CID=b.CID
group by a.CID,b.Cname,
case when score>=85 then '100-85'
when score>=70 and score<85 then '85-70'
when score>=60 and score<70 then '70-60'
when score<60 then '0-60' End
order by a.CID,b.Cname,flag
不存在的分数段显示0：
select m.CID 课程编号 , m.Cname 课程名称 , 分数段 = (
case when n.score >= 85 then '85-100'
when n.score >= 70 and n.score < 85 then '70-85'
when n.score >= 60 and n.score < 70 then '60-70'
else '0-60'
end) ,
count(1) 数量 ,
cast(count(1) * 100.0 / (select count(1) from sc where CID = m.CID) as decimal(18,2)) 百分比
from Course m , sc n
where m.CID = n.CID
group by all m.CID , m.Cname , (
case when n.score >= 85 then '85-100'
when n.score >= 70 and n.score < 85 then '70-85'
when n.score >= 60 and n.score < 70 then '60-70'
else '0-60'
end)
order by m.CID , m.Cname , 分数段
横向显示：
select m.CID 课程编号, m.Cname 课程名称,
(select count(1) from SC where CID = m.CID and score < 60) 0-60 ,
cast((select count(1) from SC where CID = m.CID and score < 60)*100.0 / (select count(1) from SC where CID = m.CID) as decimal(18,2)) 百分比 ,
(select count(1) from SC where CID = m.CID and score >= 60 and score < 70) 60-70 ,
cast((select count(1) from SC where CID = m.CID and score >= 60 and score < 70)*100.0 / (select count(1) from SC where CID = m.CID) as decimal(18,2)) 百分比 ,
(select count(1) from SC where CID = m.CID and score >= 70 and score < 85) 70-85 ,
cast((select count(1) from SC where CID = m.CID and score >= 70 and score < 85)*100.0 / (select count(1) from SC where CID = m.CID) as decimal(18,2)) 百分比 ,
(select count(1) from SC where CID = m.CID and score >= 85) 85-100 ,
cast((select count(1) from SC where CID = m.CID and score >= 85)*100.0 / (select count(1) from SC where CID = m.CID) as decimal(18,2)) 百分比
from Course m
order by m.CID
24.查询学生平均成绩及其名次
解析：类似第20题
select b.*,rank() over (order by avg_total desc) rank from
(select a.SID,a.Sname,isnull(avg(b.score),0)avg_total from Student a
left join SC b on a.SID=b.SID
group by a.SID,a.Sname
) b
order by rank
25.查询各科成绩前三名的记录
解析：先排序，然后取前三
select * from
(
select a.*,rank()over (partition by b.CID order by b.score DESC ) rank from Student a,SC b where a.SID=b.SID
)
where rank between 1 to 3
26.查询每门课程被选修的学生数
select a.CID,a.Cname ,count(b.SID) from Course a,SC b where a.CID=b.CID group by a.CID,a.Cname
27.查询出只有两门课程的全部学生的学号和姓名
select a.SID,a.Sname from Student a,SC b where a.SID=b.SID
group by a.SID,a.Snmae
having count(b.CID)=2
28.查询男生、女生人数
select case when Ssex='男' then 'male' when Ssex='女' then 'famale' END as gander ,count(1) as nm from Student
group by case when Ssex='男' then 'male' when Ssex='女' then 'famale' end
select Ssex as '性别',COUNT(1) as '人数' from Student group by Ssex
29.查询名字中含有"风"字的学生信息
select * from Student where Sname like '%风%'
30.查询同名同性学生名单，并统计同名人数
解析：查询出来，记录数大于等于2即可
select Sname,Ssex, count(1) from Student
group by Sname,Ssex
having count(1)>1
31.查询1990年出生的学生名单
解析：限定年份即可 substr(Sage,1,4)='1990'
select * from Student where year(sage) = 1990
select * from Student where datediff(yy,sage,'1990-01-01') = 0
select * from Student where datepart(yy,sage) = 1990
select * from Student where convert(varchar(4),sage,120) = '1990'
32.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
解析：排序
select m.CID , m.Cname , cast(avg(n.score) as decimal(18,2)) avg_score
from Course m, SC n
where m.CID = n.CID
group by m.CID , m.Cname
order by avg_score desc, m.CID asc
33.查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
解析：having
select a.SID , a.Sname , cast(avg(b.score) as decimal(18,2)) avg_score
from Student a , sc b
where a.SID = b.SID
group by a.SID , a.Sname
having cast(avg(b.score) as decimal(18,2)) >= 85
order by a.SID
34.查询课程名称为"数学"，且分数低于60的学生姓名和分数
select sname , score
from Student , SC , Course
where SC.SID = Student.SID and SC.CID = Course.CID and Course.Cname ='数学' and score < 60
35.查询所有学生的课程及分数情况
解析：用left join比较好
select Student.Sname , Course.Cname , SC.score
from Student, SC , Course
where Student.SID = SC.SID and SC.CID = Course.CID
order by Student.SID, SC.CID
select a.Sname,c.Cname,b.score from Student a
left join SC b on a.SID=b.SID
left join Course on b.CID=c.ICD
order by a.Sname,c.Cname
36.查询任何一门课程成绩在70分以上的姓名、课程名称和分数
select Student.Sname , Course.Cname , SC.score
from Student, SC , Course
where Student.SID = SC.SID and SC.CID = Course.CID and score>70
order by Student.SID, SC.CID
37.查询不及格的课程
select Student.Sname , Course.Cname , SC.score
from Student, SC , Course
where Student.SID = SC.SID and SC.CID = Course.CID and score<60
order by Student.SID, SC.CID
38.查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
select Student.SID, Student.CID
from Student, SC
where Student.SID = SC.SID and SC.CID = '01' and SC.score >= 80
order by Student.SID , Student.CID
39.求每门课程的学生人数
select Course.Cname , count(1) 学生人数
from Course , SC
where Course.CID = SC.CID
group by Course.Cname
order by Course.Cname
40.查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
解析：子查询为张三老师授课学生SID
select Student.* , Course.Cname ,SC.score
from Student, SC , Course , Teacher
where Student.SID = SC.SID and SC.CID = Course.CID and Course.TID = Teacher.TID and Teacher.Tname = '张三' and
SC.score = (select max(SC.score) from SC , Course , Teacher where SC.CID = Course.CID and Course.TID = Teacher.TID and Teacher.Tname = '张三')
当前考虑张三老师教了多门课，取总成绩最高
select Student.* , Course.Cname ,SC.score
from Student, SC , Course , Teacher
where Student.SID = SC.SID and SC.CID = Course.CID and Course.TID = Teacher.TID and Teacher.Tname = '张三' and
Student.SID in
(select SID from
(select a.SID,sum(SC.score),rank() over(partition by SID order by sum(SC.score) desc) as rank from SC a, Course b , Teacher c
where a.CID = b.CID and b.TID =c.TID and c.Tname = '张三' and rank=1
group by a.SID)
)
41.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select a.* from SC a ,
(select CID , score from SC group by CID , score having count(1) > 1) b
where a.CID= b.CID and a.score = b.score
order by a.CID , a.score , a.SID
select m.* from SC m
where
exists
(select 1 from
(select CID , score from SC group by CID , score having count(1) > 1) n
where m.CID= n.CID and m.score = n.score
)
order by m.CID , m.score , m.SID
42.查询每门功成绩最好的前两名
select a.* from sc a where score in
(select top 2 score from sc where CID = T.CID order by score desc)
order by a.CID , a.score desc
select a.* from SC a where a.SID in
(select SID from
(select SID,rank() over(partition by CID order by score desc) as rank where rank=2)
)
order by a.CID , a.score desc
43.统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select Course.CID , Course.Cname , count(*) 学生人数
from Course , SC
where Course.CID = SC.CID
group by Course.CID , Course.Cname
having count(*) >= 5
order by 学生人数 desc , Course.CID
44.检索至少选修两门课程的学生学号
select SID from SC group by SID
having count(1) >= 2
order by SID
45.查询选修了全部课程的学生信息
解析：课程表数量和成绩表中数量即可
select student.* from student where SID in
(select SID from sc group by SID having count(1) = (select count(1) from course))
46.查询各学生的年龄
select * , datediff(yy , sage , getdate()) 年龄 from student
select * ,
case when right(convert(varchar(10),getdate(),120),5) < right(convert(varchar(10),sage,120),5) then datediff(yy , sage , getdate()) - 1
else datediff(yy , sage , getdate()) end --为什么不是102而是120 ，也可以用substr
年龄 from student
47.查询本周过生日的学生
select * from student where datediff(week,datename(yy,getdate()) - right(convert(varchar(10),sage,120),6),getdate()) = 0
select * from student where ((select WEEKOFYEAR(now()))- (select WEEKOFYEAR(sage))) =0
48.查询下周过生日的学生
select * from student where datediff(week,datename(yy,getdate()) - right(convert(varchar(10),sage,120),6),getdate()) = -1
select * from student where ((select WEEKOFYEAR(now()))- (select WEEKOFYEAR(sage))) = -1
49.查询本月过生日的学生
select * from student where datediff(mm,datename(yy,getdate()) - right(convert(varchar(10),sage,120),6),getdate()) = 0
select * from student where ((select month(now()))- (select month(sage))) = 0
50.查询下月过生日的学生
select * from student where datediff(mm,datename(yy,getdate()) - right(convert(varchar(10),sage,120),6),getdate()) = -1
select * from student where ((select month(now()))- (select month(sage))) = -1
网友评论