(004)初级大数据开发_基础SQL（21题-40题）

最新推荐文章于 2024-07-15 10:47:29 发布

数据开发小胡

最新推荐文章于 2024-07-15 10:47:29 发布

阅读量1.8k

点赞数

分类专栏： SQL mysql 文章标签： mysql linux 其他数据库 database

本文链接：https://blog.csdn.net/qq_42209718/article/details/124212980

版权

SQL 同时被 2 个专栏收录

3 篇文章 0 订阅

订阅专栏

mysql

3 篇文章 0 订阅

订阅专栏

本文详细介绍了多个SQL查询示例，包括按性别统计学生人数、查找特定字符姓名、查询同名学生、筛选特定年份出生学生、计算课程平均成绩、找出高分学生、查询特定科目低分者、展示所有学生课程分数、获取特定课程高分学生、统计每门课程选修人数、检索选修多门课程学生、查询全科学生、计算学生年龄、以及处理年龄计算特殊情况。这些案例涵盖了SQL的基础操作和高级应用。

摘要由CSDN通过智能技术生成

--21.查询男生、女生人数

--21.查询男生、女生人数
--根据性别进行分组在进行统计
SELECT s.Ssex ,COUNT(*) 
FROM  Student s
group by s.Ssex ;

--22.查询名字中含有「风」字的学生信息

--22.查询名字中含有「风」字的学生信息
SELECT s.*
FROM  Student s 
WHERE  s.Sname LIKE '%风%';

--23.查询同名学生名单，并统计同名人数

--23.查询同名学生名单，并统计同名人数
--对学生的姓名进行分组，如果有相同名字的学生就会分到一组，那么对分组之后进行统计大于等于2输出
SELECT s.Sname ,COUNT(*) 
from Student s 
group by s.Sname 
HAVING COUNT(*)>=2;

--24.查询 1990 年出生的学生名单

--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.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

--25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
--查询每门课程的平均分，要对课程进行分组然后求平均数,然后进行排序
SELECT s.CId ,AVG(s.score) 
FROM  SC s 
group by s.CId 
ORDER by AVG(s.score) DESC,s.CId ;

--26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

--26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
--第一步:先获取平均成绩大于等于85分的同学sid
SELECT s.SId,AVG(s.score) as avgscore  
FROM SC s 
group by s.SId
HAVING AVG(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
HAVING AVG(s.score)>=85 )a 
on s.SId =a.sid ;

--27.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

--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.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

--28.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
SELECT s.SId ,s.Sname ,s2.CId ,s2.score 
from Student s 
left join SC s2 on s.SId =s2.SId ;

--29.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

--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.查询存在不及格的课程

--30.查询存在不及格的课程
SELECT DISTINCT  s.CId 
FROM SC s
where s.score <60;

--31.查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名

--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.求每门课程的学生人数

--32.求每门课程的学生人数
SELECT s.CId ,COUNT(*)as stunum 
from SC s
group by s.CId ;

--33.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

--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.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

--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.查询每门功课成绩最好的前两名(****)

--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 人的课程才统计）

--36.统计每门课程的学生选修人数（超过 5 人的课程才统计）
SELECT s.CId , COUNT(*) 
FROM SC s 
group by s.CId 
HAVING COUNT(*)>5;

--37.检索至少选修两门课程的学生学号

--37.检索至少选修两门课程的学生学号
--第一步：对sc表根据sid分组并输出选修课程大于等于2的学生sid
SELECT s.SId ,COUNT(*) 
FROM SC s 
group by s.SId 
HAVING COUNT(*)>=2 ;
--关联student
SELECT s.*
from Student s 
join (SELECT s.SId ,COUNT(*) as num  
FROM SC s 
group by s.SId 
HAVING COUNT(*)>=2)a on s.SId =a.sid ;

--38.查询选修了全部课程的学生信息

--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.查询各学生的年龄，只按年份来算

--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.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

--40.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
--第一步:获取当前月份，和学生出生月份
SELECT 
    s.SId ,
	MONTH (GETDATE()) as now_month ,
	MONTH (s.Sage) as stu_month_age,
	YEAR(s.Sage) as stu_year,
	YEAR (GETDATE()) as now_date
from
	Student s;
--第二步：把第一步作为临时表去计算即可
SELECT a.sid , a.now_date -a.stu_year-1 as stu_age
FROM (SELECT
    s.sid,
	MONTH (GETDATE()) as now_month ,
	MONTH (s.Sage) as stu_month_age,
	YEAR(s.Sage) as stu_year,
	YEAR (GETDATE()) as now_date
from
	Student s)a
where a.now_month <a.stu_month_age;