SQL50题

create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙峰' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');
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 varchar(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);
select * from Student
select * from Course
select * from SC
select * from Teacher 
--1.查询” 01 “课程比” 02 “课程成绩高的学生的信息及课程分数
select a.SId 学生编号, a.语文课程分数,b.数学课程分数 
,s.Sname 学生姓名 from(
select sc.score 语文课程分数,sc.SId from SC sc where sc.CId=1 )as a
inner join 
(select sc.score 数学课程分数,sc.SId from SC sc where sc.CId=2 )as b
on a.SId=b.SId
inner join Student s 
on s.SId=b.SId
where a.语文课程分数>b.数学课程分数
--2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select sc.SId,avg(sc.score) from SC sc 
group by sc.SId 
having avg(sc.score)>60

select s.SId 学生编号,s.Sname 学生姓名 ,c.c 平均成绩 
from Student s 
inner join (select avg(sc.score) c,sc.SId from SC sc 
group by sc.SId 
having avg(sc.score)>60) c
on c.SId=s.SId
--3.查询在 SC 表存在成绩的学生信息（不停精进代码，让代码更优化。
select distinct s.Sname from SC sc inner join
Student s on sc.SId=s.SId
--4.查询所有同学的学生编号、学生姓名、选课总数、
--所有课程的总成绩(没成绩的显示
--为 null )
select * from Student s left join 
SC sc on sc.SId=s.SId
left join Course c on c.CId=sc.CId
select COUNT(sc.SId)选课总数 ,SUM(sc.score)总成绩
 from SC sc 
group by sc.SId 

select s.Sname ,sc.选课总数,sc.总成绩 from Student s left 
join (select COUNT(sc.SId)选课总数 ,SUM(sc.score)总成绩,sc.SId
 from SC sc 
group by sc.SId ) sc 
on sc.SId=s.SId

--5.查询「李」姓老师的数量
select COUNT(*) from Teacher t where t.Tname like'李%_'

--6.查询学过张三老师授课的同学的信息
select s.Sname from Student s 
inner join SC sc on s.SId=sc.SId
inner join Course c on c.CId=sc.CId
inner join Teacher t on c.TId=t.TId
where t.Tname='张三'

--7.查询没有学全所有课程的同学的信息
select *from Student s 
right join (
select count(*)所有课程,sc.SId  from SC  sc 
group by sc.SId having count(*)<3) a
on a.SId=s.SId

select s.Sname,s.Sage,s.Ssex from  Student s 
left join SC sc on s.SId=sc.SId 
group by s.SId,s.Sname,s.Sage
,s.Ssex having count(distinct sc.CId)<3 

--8.查询至少有一门课与学号为” 01 “的同学所学相同的同学的信息
select distinct s.*from Student s
inner join SC sc on sc.SId=s.SId
inner join Course c on c.CId=sc.CId
where c.Cname in(
select c.Cname from Student s
inner join SC sc on sc.SId=s.SId
inner join Course c on c.CId=sc.CId
where s.SId='01' )and s.SId!='01'
--9.查询和” 01 “号的同学学习的课程完全相同的其他同学的信息
select COUNT(s.SId)from Student s
inner join SC sc on sc.SId=s.SId
inner join Course c on c.CId=sc.CId
where  s.SId='01' group by s.SId

select s.Sname from Student s
inner join SC sc on sc.SId=s.SId
inner join Course c on c.CId=sc.CId
group by s.SId,s.Sname having COUNT(s.SId)=
(select COUNT(s.SId)from Student s
inner join SC sc on sc.SId=s.SId
inner join Course c on c.CId=sc.CId
where  s.SId='01'  group by s.SId) 
and s.SId<>'01'




 --10.查询没学过”张三”老师讲授的任一门课程的学生姓名
select s.Sname from Student s 
where s.SId not in(
select  sc.SId from SC sc
inner join  Course c on c.CId=sc.CId
inner join Teacher t on c.TId=t.TId
where t.Tname='张三')
--11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select * from  Student s,SC sc 
where sc.SId=s.SId and sc.score>60
group by s.SId

select * from Student s
inner join 
(select COUNT(sc.SId)和,AVG(sc.score)平均成绩 ,sc.SId from SC sc 
where sc.score<60
group by sc.SId
having COUNT(sc.SId)>=2) a on s.SId=a.SId
--12.检索” 01 “课程分数小于 60，按分数降序排列的学生信息
select * from Student s inner join(
select * from SC sc where sc.score<60 
and sc.CId=01)
a on s.SId=a.SId
--13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select sc.SId,avg(sc.score)平均成绩 from 
SC sc group by sc.SId order by AVG(sc.score) desc

select s.SId,s.Sname 姓名,c.Cname 科目,sc.score 成绩 ,b.平均成绩
from Student s
inner join SC sc on sc.SId=s.SId
inner join Course c on c.CId=sc.CId 
inner join (select sc.SId,avg(sc.score)平均成绩 from 
SC sc group by sc.SId) b on b.SId=s.SId 
order by b.平均成绩 desc   
--14.查询各科成绩最高分、最低分和平均分：
--以如下形式显示：课程 ID，课程 name，最高分，
--最低分，平均分，及格率，中等率，
--优良率，优秀率
--及格为>=60，中等为：70-80，
--优良为：80-90，优秀为：>=90
--要求输出课程号和选修人数，
--查询结果按人数降序排列列，
--若人数相同，按课程号升序
--排列
select c.CId 课程编号,c.Cname 课程,a.最高分,a.最低分,a.平均分 from Course c
inner join ( 
select MAX(sc.score)最高分,MIN(sc.score)最低分,
AVG(sc.score)平均分 ,sc.CId
from SC sc  group by sc.CId) a
on c.CId=a.CId                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                


select *from SC
select c.CId 课程编号,c.Cname 课程,a.最高分,a.最低分,a.平均分 from Course c
inner join ( 
select MAX(sc.score)最高分,MIN(sc.score)最低分,
AVG(sc.score)平均分 ,
sum(case when sc.score>=60 then 1 else 0 end)及格,
sum(case when sc.score>=70 and sc.score<=80 then 1 else 0 end)中等,
sum(case when sc.score>=80 and sc.score<=90 then 1 else 0 end)优良,
sum(case when sc.score>=90 then 1 else 0 end)优秀,
sc.CId
from SC sc  group by sc.CId) a
on c.CId=a.CId                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
select count(sc.SId) from SC  group by SC.CId
select convert(decimal(6,2), 4/6)
--15.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
select *,rank() over(partition by sc.CId
 order by sc.score desc)排名  from SC sc 

select sc.CId from SC sc group by sc.CId
--16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺
select SUM(sc.score) 总成绩 ,
rank()over(order by SUM(sc.score)desc) 排名 
from  SC sc group by sc.SId 
--17. 统计各科成绩各分数段人数：课程编号，课程名称，
--[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
select c.CId,c.Cname,
count (case when sc.score>=85 then 1 else null end) '100-85'
,count (case when sc.score>=70 and sc.score<=85 then 1 else null end) '85-70'
,count (case when sc.score>=60 and sc.score<=70 then 1 else null end) '70-60'
,count (case when sc.score<=60 then 1 else null end) '60-0'
from SC sc 
inner join Course c on sc.CId=c.CId
group by  c.CId,c.Cname
--18.查询各科成绩前三名的记录
select * from (
select RANK() over(partition by sc.CId order by sc.score desc )
as b,* from SC sc ) s where s.b<=3
go

--19.查询每门课程被选修的学生数
select * from  Course 
select c.Cname 课程,count(c.Cname)学生数 from  Course c
inner join SC sc on sc.CId=c.CId
inner join Student s on sc.SId=s.SId
group by c.Cname
--20.查询出只选修两门课程的学生学号和姓名
select s.Sname,s.SId from Student s 
where s.SId in(
select sc.SId from 
SC sc group by sc.SId
having COUNT(sc.CId)=2
) 
--21. 查询男生、女生人数
select s.Ssex,count(s.Ssex)人数 from  Student s  
group  by s.Ssex
--22. 查询名字中含有「风」字的学生信息
select * from Student s where s.Sname like'%风%'
--23查询同名同性学生名单，并统计同名人数
select s.Sname,COUNT(*)人数 from Student s group by s.Sname
having COUNT(*)>=2
--24.查询 1990 年年出生的学生名单
select *from Student s where s.Sage like'%1990%'
--25.查询每门课程的平均成绩，结果按平均成绩降序排列，
--平均成绩相同时，按课程编
--号升序排列
select sc.CId,AVG(sc.score)平均成绩 from SC sc group by sc.CId 
order by AVG(sc.score)desc
--26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select s.SId,s.Sname,b.平均成绩 from Student s inner join (
select sc.SId,AVG(sc.score)平均成绩 from SC sc group by sc.SId
having AVG(sc.score)>=85) b on s.SId=b.SId
--27.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
select s.Sname,b.score from Student s inner join (
select * from SC sc where sc.CId=(
select c.CId from Course c where c.Cname='数学')
and sc.score<60) b on s.SId=b.SId
--28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select s.Sname,c.Cname,sc.score from Student s left join
SC sc on sc.SId=s.SId
left join Course c on c.CId=sc.CId
--29.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select s.Sname,c.Cname,sc.score from Student s inner join
SC sc on sc.SId=s.SId
inner join Course c on c.CId=sc.CId
and sc.score>70
--30.查询不及格的课程
select s.Sname,c.Cname,sc.score from Student s inner join
SC sc on sc.SId=s.SId
inner join Course c on c.CId=sc.CId
and sc.score<60
--31.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
select s.SId,s.Sname from Student s inner join(
select* from SC  sc where sc.CId=1 and sc.score>=80)
b on s.SId=b.SId
--32.求每门课程的学生人数
select sc.CId,COUNT(sc.CId) from SC sc 
group by sc.CId
--33.成绩不重复，查询选修「张三」老师所授课程的学生中，
--成绩最高的学生信息及其成绩
select top 1 RANK()over(order by sc.score desc)
,* from Student s inner join SC sc
on s.SId=sc.SId inner join Course c
on c.CId=sc.CId inner join Teacher t
on c.TId=t.TId where t.Tname='张三'
--34.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中
--，成绩最高的学生信息及其成绩
--35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select s.SId,c.CId,sc.score from Student s inner join SC sc
on sc.SId=s.SId inner join Course c on c.CId=sc.CId
and sc.SId=(
select sc.SId from SC sc group by sc.score,sc.SId
having COUNT(*)>=2)

select distinct a.* from sc a join sc b on a.SId=b.SId
where a.score=b.score and a.CId!=b.CId

--36. 查询每门成绩最好的前两名
select b.SId ,b.score,b.CId from(
select *,ROW_NUMBER() over (partition  by sc.CId order by
sc.score desc ) c from SC sc) as b where b.c<=2


--37. 统计每门课程的学生选修人数（超过 5 人的课程才统计）。
select sc.CId,COUNT(*) from SC sc group by sc.CId
--38.检索至少选修两门课程的学生学号
select sc.SId,COUNT(*) from SC sc group by sc.SId having COUNT(*)>=2
--39.查询选修了全部课程的学生信息
select * from Student s where s.SId
in(
select sc.SId from SC sc group by sc.SId,sc.SId having COUNT(*)=3)
--40.查询各学生的年龄，只按年份来算
select Sname,Sage,
year(getdate())-year(Sage) as 年龄 
from student
--41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一  
----42.查询本周过生日的学生
--SELECT * FROM student
----43. 查询下周过生日的学生
--SELECT * FROM student
--where month(Sage)=(getdate())
--44.查询本月过生日的学生
SELECT * FROM student
where month(Sage)=month(getdate())
--45.查询下月过生日的学生
SELECT * FROM student
where month(Sage)=month(getdate())+1