--21.查询男生、女生人数
--根据性别进行分组在进行统计
SELECTs.Ssex,COUNT(*)
FROM Student s
group by s.Ssex ;
--22.查询名字中含有「风」字的学生信息
SELECT s.*
FROM Student s
WHERE s.Sname LIKE '%风%';
--23.查询同名学生名单，并统计同名人数
--对学生的姓名进行分组，如果有相同名字的学生就会分到一组，那么对分组之后进行统计大于等于2输出
SELECTs.Sname,COUNT(*)
from Student s
group by s.Sname
HAVING COUNT(*)>=2;
--24.查询 1990 年出生的学生名单
--year(s.sage)--表示取年
--month（s.sage)--表示取月份
--day(s.sage)--取天数
SELECT YEAR (s.Sage),MONTH (s.Sage),DAY (s.Sage)
from Student s ;
SELECT s.*
from Student s
where YEAR (s.Sage) =1990;
--25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
--查询每门课程的平均分，要对课程进行分组然后求平均数,然后进行排序
SELECT s.CId ,AVG(s.score)
FROM SC s
group by s.CId
ORDER by AVG(s.score) DESC,s.CId ;
--26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
--第一步:先获取平均成绩大于等于85分的同学sid
SELECT s.SId,AVG(s.score) as avgscore
FROM SC s
group by s.SId
HAVINGAVG(s.score)>=85;
--第二步：和sutdent表进行关联并输出平均成绩大于等于85的学生信息
SELECT s.SId ,s.Sname ,a.avgscore
from Student s join (SELECT s.SId,AVG(s.score) as avgscore
FROM SC s
group by s.SId
HAVINGAVG(s.score)>=85)a
on s.SId =a.sid ;
--27.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
--对课程进行分组并选出课程为数学且份素
SELECT s2.Sname ,s.score
from Course c
join SC s on c.CId =s.CId
JOIN Student s2 on s2.SId =s.SId
where c.Cname ='数学' and s.score <60;
--28.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
SELECT s.SId ,s.Sname ,s2.CId ,s2.score
from Student s
left join SC s2 on s.SId =s2.SId ;
--29.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
SELECT s2.Sname ,c.Cname ,a.score
from Student s2
join (SELECT*
FROM SC s
where s.score >70)a on s2.SId =a.sid
join Course c on a.cid =c.CId ;
--30.查询存在不及格的课程
SELECT DISTINCT s.CId
FROM SC s
where s.score <60;
--31.查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名
SELECT s2.SId ,s2.Sname
FROM Student s2
join sc s on s2.SId =s.SId
where s.CId =01 and s.score =80;
--32.求每门课程的学生人数
SELECT s.CId ,COUNT(*)as stunum
from SC s
group by s.CId ;
--33.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
--第一步:找到张三老师的id
SELECT t.TId
FROM Teacher t
where t.Tname ='张三';
--第二步：通过张三老师的id去关联course去找到张三老师教授的课程id
SELECT c.CId,c.Cname
FROM Course c
join (SELECT t.TId
FROM Teacher t
where t.Tname ='张三')a on c.TId =a.tid ;
--第三步:
SELECT top 1 s.SId , a.cid,MAX(s.score)as maxscore
from SC s
join (SELECT c.CId
FROM Course c
join (SELECT t.TId
FROM Teacher t
where t.Tname ='张三')a on c.TId =a.tid)a on s.CId =a.cid
GROUP BY a.cid,s.SId
ORDER BY MAX(s.score) DESC;
--第四步：把第三步中找出来的sid作为临时表去关联student
SELECT DISTINCT s.*,a.cid ,a.maxscore
from Student s
join (SELECT top 1 s.SId , a.cid,MAX(s.score)as maxscore
from SC s
join (SELECT c.CId
FROM Course c
join (SELECT t.TId
FROM Teacher t
where t.Tname ='张三')a on c.TId =a.tid)a on s.CId =a.cid
GROUP BY a.cid,s.SId
ORDER BY MAX(s.score) DESC)a on s.SId =a.sid;
--法二:四张表进行关联,老师条件为张三然后进行降序排序，最后取第一个即可
SELECT DISTINCT s.*,s2.CId,s2.score
from Student s
join SC s2 on s.SId =s2.SId
join Course c on s2.CId =c.CId
join Teacher t on t.TId =c.TId
where 1=1
AND t.Tname ='张三'
AND s2.score =(SELECT MAX(s2.score)
from Student s
join SC s2 on s.SId =s2.SId
join Course c on s2.CId =c.CId
join Teacher t on t.TId =c.TId
where 1=1
AND t.Tname ='张三');
--34.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT DISTINCT s1.*
FROM SC s1
join SC s2
on s1.SId =s2.SId
and s1.CId <>s2.CId
and s1.score =s2.score;
--35.查询每门功课成绩最好的前两名(****)
select a.sid,a.cid,a.score from sc as a
left join sc as b
on a.cid = b.cid and a.score<b.score
group by a.cid, a.sid,a.score
having count(b.cid)<2
order by a.cid;
--36.统计每门课程的学生选修人数（超过 5 人的课程才统计）
SELECTs.CId,COUNT(*)
FROM SC s
group by s.CId
HAVING COUNT(*)>5;
--37.检索至少选修两门课程的学生学号
--第一步：对sc表根据sid分组并输出选修课程大于等于2的学生sid
SELECTs.SId,COUNT(*)
FROM SC s
group by s.SId
HAVINGCOUNT(*)>=2;
--关联student
SELECT s.*
from Student s
join(SELECTs.SId,COUNT(*)asnum
FROM SC s
group by s.SId
HAVINGCOUNT(*)>=2)aons.SId=a.sid;
--38.查询选修了全部课程的学生信息
--第一步：查出总的课程数
SELECT COUNT(*)
from Course c ;
--第二步：获取选修3门课程的学生sid
SELECT s.SId ,COUNT(*) as course_nu
FROM SC s
group by s.SId
HAVING COUNT(*)=(SELECT COUNT(*) as course_num
from Course c);
--第三步:关联student表
SELECT DISTINCT s.*
from Student s
join (SELECT s.SId ,COUNT(*) as course_num
FROM SC s
group by s.SId
HAVING COUNT(*)=(SELECT COUNT(*) as course_num
from Course c))a on a.sid =s.sid ;
--39.查询各学生的年龄，只按年份来算
--第一步：截取学生的age和当前时间（其实在真正的开发下，这个就是明细表）
SELECT YEAR(s.Sage) as stu_year,YEAR (GETDATE()) as now_date
from Student s;
--第二步：把第一步作为临时表 now_date -stu_year（数据服务层DWS）
SELECT now_date -stu_year as stu_age
from (SELECT YEAR(s.Sage) as stu_year,YEAR (GETDATE()) as now_date
from Student s) a;
--40.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
--第一步:获取当前月份，和学生出生月份
SELECT
s.SId,
MONTH(GETDATE())asnow_month,
MONTH (s.Sage) as stu_month_age,
YEAR(s.Sage) as stu_year,
YEAR(GETDATE())asnow_date
from
Student s;
--第二步：把第一步作为临时表去计算即可
SELECT a.sid , a.now_date -a.stu_year-1 as stu_age
FROM (SELECT
s.sid,
MONTH(GETDATE())asnow_month,
MONTH (s.Sage) as stu_month_age,
YEAR(s.Sage) as stu_year,
YEAR(GETDATE())asnow_date
from
Student s)a
where a.now_month <a.stu_month_age;
