SQL练习题

最新推荐文章于 2024-04-29 16:06:43 发布
最新推荐文章于 2024-04-29 16:06:43 发布
阅读量444 收藏 2
点赞数 1
文章标签： sql mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/Stannis/article/details/120351581
版权
本文通过逻辑树分析法介绍了一系列复杂的SQL查询问题，包括课程成绩对比、学生信息筛选、教师授课查询、课程排名统计等多个场景。这些问题涵盖了对数据库表的深入查询和分析，旨在提升SQL技能和数据库理解。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

逻辑树分析法，对复杂问题进行拆解

在这里插入图片描述

create table student(
	sid varchar(6) not null primary key comment '学号',
	sname varchar(10) comment '学生姓名',
	sage datetime comment '出生日期',
	ssex varchar(10) comment '性别'
)engine=InnoDB default charset=utf8;
alter table student comment '学生';
insert into student(sid,sname,sage,ssex) values ('01','赵风','1990-01-01','男');
insert into student(sid,sname,sage,ssex) values ('02','钱雨','1990-12-21','男');
insert into student(sid,sname,sage,ssex) values ('03','孙雷','1990-05-20','男');
insert into student(sid,sname,sage,ssex) values ('04','李电','1990-08-06','男');
insert into student(sid,sname,sage,ssex) values ('05','周梅','1991-12-01','女');
insert into student(sid,sname,sage,ssex) values ('06','吴兰','1992-03-01','女');
insert into student(sid,sname,sage,ssex) values ('07','郑竹','1989-07-01','女');
insert into student(sid,sname,sage,ssex) values ('08','王菊','1990-01-20','女');

create table course(
	cid varchar(10) not null primary key comment '课程编号',
	cname varchar(10) comment '课程名称',
	tid varchar(10) comment '老师编号'
)engine=InnoDB default charset=utf8;
alter table course comment '课程';
insert into course(cid,cname,tid) values ('01','语文','02');
insert into course(cid,cname,tid) values ('02','数学','01');
insert into course(cid,cname,tid) values ('03','英语','03');

create table teacher(
	tid varchar(10) not null primary key comment '老师编号',
	tname varchar(10) comment '老师姓名'
)engine=InnoDB default charset=utf8;
alter table teacher comment '老师';
insert into teacher(tid,tname) values ('01','张三');
insert into teacher(tid,tname) values ('02','李四');
insert into teacher(tid,tname) values ('03','王五');

create table sc(
	sid varchar(10) comment '学号',
	cid varchar(10) comment '课程编号',
	score decimal(18,1) comment '分数',
	primary key(sid,cid)
)engine=InnoDB default charset=utf8;
alter table sc comment '成绩';
insert into sc(sid,cid,score) values ('01','01',80);
insert into sc(sid,cid,score) values ('01','02',90);
insert into sc(sid,cid,score) values ('01','03',99);
insert into sc(sid,cid,score) values ('02','01',70);
insert into sc(sid,cid,score) values ('02','02',60);
insert into sc(sid,cid,score) values ('02','03',80);
insert into sc(sid,cid,score) values ('03','01',80);
insert into sc(sid,cid,score) values ('03','02',80);
insert into sc(sid,cid,score) values ('03','03',80);
insert into sc(sid,cid,score) values ('04','01',50);
insert into sc(sid,cid,score) values ('04','02',30);
insert into sc(sid,cid,score) values ('04','03',20);
insert into sc(sid,cid,score) values ('05','01',76);
insert into sc(sid,cid,score) values ('05','02',87);
insert into sc(sid,cid,score) values ('06','01',31);
insert into sc(sid,cid,score) values ('06','03',34);
insert into sc(sid,cid,score) values ('07','02',89);
insert into sc(sid,cid,score) values ('07','03',98);

1、查询“01”课程比“02”课程成绩高的学生的信息及课程分数

	# 学生信息student（sid）、 sc（sid，cid）表
	# 纵表转横表 case...when...; 横表转纵表 union all 
	select s.*,a.score_01,a.score_02 from 
		(select 
			sid,
			sum(case when cid='01' then score end) as score_01,
			sum(case when cid='02' then score end) as score_02
		from sc 
		group by sid) a 
	left join student s on a.sid=s.sid 
	where (a.score_01 is not null or a.score_02 is not null) and a.score_01>a.score_02;

2、查询平均成绩大于等于60分的同学的学生id、学生姓名和平均成绩

	# 先找出参加全部考试的学生id，和他们的平均成绩，然后左关联student表，筛选出平均成绩大于等于60即可
	select a.sid,b.sname,a.avg_score from 
		(select sid,avg(score) as avg_score from sc group by sid having count(cid)=3) A 
	left join student b on a.sid=b.sid
	where a.avg_score>=60;

3、查询在sc表存在成绩的学生信息

select * from student a where exists (select 1 from sc b where a.sid=b.sid);

4、查询所有同学的学生id、学生姓名、选课总数、所有课程的总成绩（没成绩的显示为null）

	# student(sid,sname) sc(count(cid),sum(score))
	select a.sid,a.sname,ifnull(b.count_course,0) as count_courses,b.sum_score
	from student a left join (
		select sid,count(cid) as count_course,sum(score) as sum_score from sc group by sid
	) b on a.sid=b.sid;

5、查询有成绩的学生信息

select * from student s where s.sid in (
		select distinct sid from sc
	);

6、查询“李”姓老师的数量

select count(*) from teacher where tname like '李%';

7、查询学过“张三”老师授课的同学的信息

# teacher(tid,tname) course(cid,tid) sc(sid,cid) student(sid)
	select * from student s where sid in (
		select distinct sid from sc where cid in (
			select cid from course where tid in (
				select tid from teacher where tname='张三'
			)
		)
	);

8、查询没有学全所有课程的同学的信息

	# 总的课程数量<>每个学生选课的数量
	select b.* from (
		select sid from sc group by sid having count(cid)<>(select count(*) from course)
	) a left join student b on a.sid=b.sid;

9、查询和“01”号同学学习的课程完全相同的其他同学的信息

	  1)找出学号=01的学生选修的所有课程
	  2)完全相同
	select * from student where sid in 
		(select sid from sc where sid not in
		(select sid from sc where cid not in (select cid from sc where sid='01'))
	group by sid 
	having count(*)=(select count(*) from sc where sid='01') and sid != '01');

10、查询至少有一门课与学号为“01”的同学所学相同的同学的信息

	select s.* from (
		select distinct b.sid from sc b where b.cid in (select a.cid from sc a where a.sid='01')
		) c left join student s on c.sid=s.sid 
	where c.sid<>'01';

11、查询没有学过“张三”老师讲授的任一门课程的学生姓名

select sname from student where sid not in(
		select distinct sid from sc where cid in(
			select cid from course where tid=(
				select tid from teacher where tname='张三')
			)
		);

12、查询两门及其以上不及格课程的同学的id，姓名及其平均成绩


	select b.*,a.avg_score from (
		select sid,avg(score) as avg_score from sc where score<60 group by sid
	) a left join student b on a.sid=b.sid;

13、检索“01”课程分数小于60，按分数降序排列的学生信息

	select a.sid,b.sname,a.score 
	from sc a left join student b on a.sid=b.sid 
	where a.cid='01' and a.score<60 
	order by a.score desc;

14、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

	# order by 平均成绩 desc 
	# sid cid score cid score cid score avg_scores
	# 方法一：纵表转横表 用avg函数求平均值，但是会忽略null，结果不符合逻辑
	select sid,
		   max(case when cid='01' then score end) as sc_01,
		   max(case when cid='02' then score end) as sc_02,
		   max(case when cid='03' then score end) as sc_03,
		   avg(score) as avg_score
	from sc 
	group by sid
	order by avg(score) desc;
	# 方法二：纵表转横表，使用sum函数，处理null值，除以课程总数，求得均值
	select sid,
		   max(case when cid='01' then score end) as sc_01,
		   max(case when cid='02' then score end) as sc_02,
		   max(case when cid='03' then score end) as sc_03,
		   sum(score)/3 as avg_score
	from sc 
	group by sid
	order by sum(score)/3 desc;

15、查询各科成绩最高分、最低分和平均分，要求输出课程号和选修人数

	# max(score) min(score) avg(score) count(*) group by cid 
	select cid,
		   max(score) as max_score,
		   min(score) as min_score,
		   avg(score) as avg_score,
		   count(*) as total
	from sc
	group by cid;

16、按平均成绩进行排序，显示总排名和各科排名，score重复时保留名次空缺

	# rank() 横表转纵表 总排名 各科排名
	select A.*,B.avg_scores,B.total_ranks
		from (
			select 
			t1.sid,
			max(case when t1.cid='01' then t1.score end) as cid_01,
			max(case when t1.cid='01' then t1.ranks end) as ranks_01,
			max(case when t1.cid='02' then t1.score end) as cid_02,
			max(case when t1.cid='02' then t1.ranks end) as ranks_02,
			max(case when t1.cid='03' then t1.score end) as cid_03,
			max(case when t1.cid='03' then t1.ranks end) as ranks_03
		from (select *,rank() over(partition by cid order by score desc) as ranks from sc) t1
		group by sid
		) A left join (
			select s1.*,rank() over(order by s1.avg_scores desc) as total_ranks 
			from (select s.sid,sum(s.score)/3 as avg_scores from sc s group by s.sid) s1
		) B on A.sid=B.sid 
		order by B.total_ranks,A.ranks_01,A.ranks_02,A.ranks_03;
		
	-- select t.*,rank() over(partition by t.cid order by t.score desc) as ranks from sc t;

17、按平均成绩进行排序，显示总排名和各科排名，score重复时合并名次

	# dense_rank() 横表转纵表

		--1、先对课程成绩排名
		select *,dense_rank() over(partition by cid order by score desc) as ranks from sc;
		--2、在1的基础上横表转纵表，求出各科目成绩及排名
		select 
			t1.sid,
			max(case when t1.cid='01' then t1.score end) as cid_01,
			max(case when t1.cid='01' then t1.ranks end) as ranks_01,
			max(case when t1.cid='02' then t1.score end) as cid_02,
			max(case when t1.cid='02' then t1.ranks end) as ranks_02,
			max(case when t1.cid='03' then t1.score end) as cid_03,
			max(case when t1.cid='03' then t1.ranks end) as ranks_03
		from (select *,dense_rank() over(partition by cid order by score desc) as ranks from sc) t1
		group by sid;
		--3、求出平均成绩名次，并进行排名
		select s1.*,dense_rank() over(order by s1.avg_scores desc) as total_ranks from (select s.sid,sum(s.score)/3 as avg_scores from sc s group by s.sid) s1;
		--4、两表关联
		select A.*,B.avg_scores,B.total_ranks
		from (
			select 
			t1.sid,
			max(case when t1.cid='01' then t1.score end) as cid_01,
			max(case when t1.cid='01' then t1.ranks end) as ranks_01,
			max(case when t1.cid='02' then t1.score end) as cid_02,
			max(case when t1.cid='02' then t1.ranks end) as ranks_02,
			max(case when t1.cid='03' then t1.score end) as cid_03,
			max(case when t1.cid='03' then t1.ranks end) as ranks_03
		from (select *,dense_rank() over(partition by cid order by score desc) as ranks from sc) t1
		group by sid
		) A left join (
			select s1.*,dense_rank() over(order by s1.avg_scores desc) as total_ranks 
			from (select s.sid,sum(s.score)/3 as avg_scores from sc s group by s.sid) s1
		) B on A.sid=B.sid 
		order by B.total_ranks,A.ranks_01,A.ranks_02,A.ranks_03;

18、统计各科成绩各分数段人数：课程id，课程名称，【100-85】，【85-70】，【70-60】，【60-0】及所占百分比

	# cid  cname count() 转置
	# cid 【100-85】【85-70】【70-60】【60-0】
	select 
		SC.Cid,Course.Cname,
		sum(case when score>=85 and score<=100 then 1 else 0 end) as '[85-100]',
		concat(sum(case when score>=85 and score<=100 then 1 else 0 end)/count(*)*100,'%') as '[85-100]%',
		sum(case when score>=70 and score<85 then 1 else 0 end) as '[70-85)',
		concat(sum(case when score>=70 and score<85 then 1 else 0 end)/count(*)*100,'%') as '[70-85)%',  
		sum(case when score>=60 and score<70 then 1 else 0 end) as '[60-70)',
		concat(sum(case when score>=60 and score<70 then 1 else 0 end)/count(*)*100,'%') as '[60-70)%',
		sum(case when score>=0 and score<60 then 1 else 0 end) as '[0-60)',
		concat(sum(case when score>=0 and score<60 then 1 else 0 end)/count(*)*100,'%') as '[0-60)%'
	from SC left join Course on SC.Cid=Course.Cid
	group by SC.Cid;

19、查询各科成绩前三名的记录

	select sid,cid,score from (
		select sid,cid,score,row_number() over(partition by cid order by score desc) as tt from sc) A 
	where a.tt<4;

20、查询出只选修两门课程的学生id和姓名

	# count(*)=2 group by sid
	select b.sid,c.sname from 
		(select a.sid,count(*) from sc a group by a.sid having count(*)=2) b 
	left join student c on b.sid=c.sid;

21、查询名字中含有“风”字的学生信息

	select * from student where sname like '%风%';

22、查询1990年出生的学生信息

	select * from student where year(sage)=1990;

23、成绩不重复，查询选修“李四”老师所授课程的学生中，成绩最高的学生信息及其成绩

	select b.*,a.score 
	from sc a left join student b on a.sid=b.sid 
	where a.cid=(select cid from course where tid=(select tid from teacher where tname='李四'))
	order by a.score desc 
	limit 1;

24、成绩有重复的情况下，查询选修“李四”老师所授课程的学生中，成绩最高的学生信息及其成绩

	select s.*,b.score from (
		select a.*,dense_rank() over(partition by a.cid order by a.score desc) as rows_num 
		from sc a
		where a.cid=(select cid from course where tid=(select tid from teacher where tname='李四'))) b 
	left join student s on b.sid=s.sid 
	where b.rows_num=1;

25、查询各学生的年龄，只按年份来算

	select *,(year(now())-year(sage)) as age from student;

26、查询各学生的年龄，按照出生日期来算，当前月日<出生年月的月日则，年龄减一

	select *,
		(case when date_format(sage,'%m-%d')-date_format(now(),'%m-%d')>0 then year(now())-year(sage)-1 else year(now())-year(sage) end) as age
	 from student;

27、查询本周过生日的学生

	select * from student where week(sage)=week(now());

28、查询本月过生日的学生

	select * from student where month(sage)=month(now());

29、查询至少选修了202002学生的全部课程的学生学号

		1）找出学号=202002的学生选修的所有课程 
		2）根据1）的结果，找出所有跟202002选修了同一门课（任意一门）的数据 
		3）按学号进行分组，从2）的结果中找出选课数量等于202002学生的选课数量的所有学生
	 select a.* 
	 from sql02_score a 
	 where a.cno in(select b.cno from sql02_score b where b.sno='202002')
	 group by a.sno 
	 having count(*)=(select count(*) from sql02_score c where c.sno='202002');
Stannis
关注
SQLSERVER单项选择题
archer9812的博客
12-08 3510
老师发给我们的练习题
查询至少选修了学生200215122选修的全部课程的学生号码
垃圾佬的博客
05-10 1万+
查询至少选修了学生200215122选修的全部课程的学生号码 解体思路 如果有一门课学生200215122选了而另一个同学没选就不输出这个学生好吗 即不存在一个课程Y，同学200215122选了 而同学X没选 代码如下 SELECT DISTINCT sno FROM SC AS SCX WHERE NOT EXISTS ( SELECT * FROM SC AS SCY ...
SQL练习题合集
gly1653810310的博客
06-02 1992
本文都是SQL练习题，本人写的答案不一定是正确或者完善的，如果有更简便的方法，欢迎留言交流学习
常用的MySQL查询语句
韩帅平的博客
04-14 2459
查询1987年以后出生的学生的姓名和出生年份select sname,(2008-Sage) as birthday from student where (2008-Sage)&gt;1987 order by (2008-Sage) asc;--查询选修了2号课程学生的学号及其成绩，查询结果按分数降序排列select Sno,grade from sc where cno='002' orde...
查询至少选修了学生201215122选修的全部课程的学生号码(1)
2401_84281729的博客
04-29 511
阶课程，涵盖了95%以上网络安全知识点，真正体系化！
SQL练习题 SQL练习题 SQL练习题
09-06
以下是一些SQL练习题，涵盖了SELECT、INSERT、UPDATE、DELETE、JOIN、SUBQUERY、AGGREGATE、GROUP BY等语句和函数： 选择数据 从 employees 表中选择所有列： SELECT * FROM employees; 从 employees 表中选择 ...
Hive SQL练习题库
04-24
### Hive SQL练习题库知识点详解 #### 一、Hive SQL概述 Hive 是一个基于 Hadoop 的数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供简单的 SQL 查询功能，使不熟悉 MapReduce 的用户可以利用 SQL...
sql练习题sql练习题sql练习题sql练习题
06-04
SQL练习题通常涵盖了以下核心概念和操作： 1. **数据类型**：SQL中的数据类型包括数值型（如INT, FLOAT）、字符串型（VARCHAR, CHAR）、日期时间型（DATE, DATETIME）等，了解并熟练使用这些数据类型是基础。 2. *...
50道SQL练习题及答案与详细分析
12-14
通过这50道练习题，我们可以深入理解和掌握SQL的基础语法以及高级特性。以下是一些关键知识点的讲解： 1. **创建表**：在SQL中，我们使用`CREATE TABLE`语句来创建新的表格。例如，`CREATE TABLE Student`定义了...
2024年经典SQL练习题-IT类课程习题资源（源代码和文档）
最新发布
09-26
2024年经典SQL练习题-IT类课程习题资源（史上最全资料）2024年经典SQL练习题-IT类课程习题资源（史上最全资料）2024年经典SQL练习题-IT类课程习题资源（史上最全资料）2024年经典SQL练习题-IT类课程习题资源（史上最...
mysql查询学号和姓名_数据库SQL语句中 查询选修了全部课程的学生的学号和姓名...
weixin_39588104的博客
01-20 1万+
一、SQL语言查询选修了全部课程的学生的学号和姓名。两种解决途径：第一种：我们可以表示为在SC表中某个学生选修的课程数等于C表中课程总数。相应的SQL语言如下：selectS#，SNAMEfromSwhere S# in(selectS#fromSCgroup by S# --根据Sno分组，统计每个学生选修了几门课程。如果等于C表课程的总数，就是我们要找的S#having count(*) = ...
SQL五十道经典题型——（五）查询没学过“张三”老师课的学生的学号、姓名（重点）
智十七的博客
02-06 6317
查询没学过“张三”老师课的学生的学号、姓名（重点） 解题思路 重点：分析各个表之前对应的关系。score分数表将各个科目与学生id相关联， 第一步，在 teacher 表中获取“张三”老师的 t_id ， 第二步，根据 t_id 获取到“张三”老师所带的科目信息， 第三步，根据科目的id(c_id) 获取学过张三老师所带科目的学生id（s_id)， 第四步，使用 `IN` , `NOT` 操作符与学生表进行关联。 IN操作符，用来指定条件范围，范围中的每个条件都可进行匹配。IN取合法值由逗号分隔，并且
MySQL练习题 31-50
qq_40723803的博客
12-13 1524
MySQL练习题31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列37、查询不及格的课程，并按课程号从大到小排列（有歧义） 表结构预览 31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime) SELECT sid, sname, year(sage) # 对datetime型数据的操作 from student WHERE year(sa
SQL查询学生的平均成绩及其名次
热门推荐
WuXiang_IT的博客
01-19 2万+
Student(Sid,Sname,Sage,Ssex) 学生表 Course(Cid,Cname,Tid) 课程表 SC(Sid,Cid,score) 成绩表 Teacher(Tid,Tname) 教师表 SQL查询语句为： SELECT 1+(SELECT COUNT( distinct 平均成绩)FROM (SELECT Sid,AVG(score) 平均成绩 FROM SC GROUP BY Sid ) T1 WHERE 平均成绩 > T2.平均成绩) 名次, Sid 学生学...
经典数据库sql查询50题
qq_41338740的博客
07-13 1万+
create table Student( S 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' ...
Mysql：查询每科成绩前二的学生信息和课程信息（即使存在多个第一，也可以有效查询）
good_good_xiu的博客
04-15 5831
Mysql查询每科成绩前二的学生1.要求：查询每科成绩排名前二的学生信息解决方法：1.自关联查询优点：sql简单易懂缺点：当第一名的分数存在3个或3个以上时，无法查询到第一名的分数。同理第二也是。然后第三、第四就变成第一、第二思路：创建一张成绩表（cjb）的中间表（zjb），使用count（）函数统计，中间表的每个课程的每个学生分数是否2.排序、使用变量优点：修复第一种方法存在的缺陷。会把所有第一和第二的数据查出来。而且可以根据是是否不重复排名来实现不同的需求。重复排名：排名名次可重复，存在多个第一。不重复
mysql计算每门课程的平均分_学生各门课程成绩统计SQL语句大全
weixin_33791488的博客
02-02 2万+
转载创建表SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[stuscore]( [name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,[subject] [varchar](50) COLLATE Chinese_...
mysql sql语句面试经典50题_SQL：经典面试50题
weixin_42509914的博客
01-27 2383
查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数1.1 查询同时存在" 01 "课程和" 02 "课程的情况1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )1.3 查询不存在" 01 "课程但存在" 02 "课程的情况查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩查询在 SC 表存在成绩的学生信息查询所有同学的学...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值