MySQL经典习题50道

最新推荐文章于 2022-03-10 03:43:08 发布
最新推荐文章于 2022-03-10 03:43:08 发布
阅读量55 收藏 1
点赞数
文章标签： mysql 数据库 database
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/sinat_28916141/article/details/122806562
版权

根据以下博文所创建的自己的答案
原博文

先建四张表

学生表 Student

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-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2017-12-30' , '女');
insert into Student values('12' , '赵六' , '2017-01-01' , '女');
insert into Student values('13' , '孙七' , '2018-01-01' , '女');

课程表 Course

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');

教师表 Teacher

create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

成绩表 SC

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);

习题如下

--1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
 
SELECT s.*, c1.score, c2.score FROM
student s,
(SELECT SId, score FROM sc WHERE CId=01) c1, 
(SELECT SId, score FROM sc WHERE CId=02) c2
WHERE s.SId=c1.SId AND c1.SId=c2.SId AND c1.score>c2.score
 
--1.1 查询同时存在" 01 "课程和" 02 "课程的情况
 
SELECT * FROM (SELECT * FROM sc WHERE CId=01) c1 INNER JOIN (SELECT * FROM sc WHERE CId=02) c2 ON c1.SId=c2.SId
 
--1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
 
SELECT * FROM (SELECT * FROM sc WHERE CId=01) c1 LEFT JOIN (SELECT * FROM sc WHERE CId=02) c2 ON c1.SId=c2.SId
 
-- 1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
 
select * 
from sc
where SId not in (select SId from  sc where CId = '01') 
and CId = '02'
 
-- 2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
 
SELECT s.SId, s.Sname, c.avg
FROM
student s,
(SELECT SId, AVG(score) avg FROM sc GROUP BY SId HAVING AVG(score)>60) c
WHERE s.SId=c.SId
 
-- 3. 查询在 SC 表存在成绩的学生信息     
 
SELECT * FROM student s WHERE s.SId IN (SELECT SId FROM sc GROUP BY SId)
 
-- 4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为null)
 
SELECT s.SId, Sname, c.`选课总数`, c.`总成绩` 
FROM 
student s 
LEFT JOIN 
(SELECT SId, COUNT(*) '选课总数', SUM(score) '总成绩' FROM sc GROUP BY SId) c
ON s.SId=c.SId
 
-- 4.1 查有成绩的学生信息    
 
SELECT * FROM student s WHERE s.SId IN (SELECT SId FROM sc GROUP BY SId)
 
-- 5. 查询「李」姓老师的数量
 
select count(*) 
from teacher
where teacher.Tname like '李%'
 
-- 6. 查询学过「张三」老师授课的同学的信息
 
select student.* 
from student, sc 
where student.sid = sc.sid 
and sc.cid in (select cid from course, teacher where course.tid = teacher.tid and Tname = '张三')
 
-- 同时连接多张表
select student.*
from teacher  ,course  ,student, sc
where teacher.Tname='张三'
and   teacher.TId=course.TId
and   course.CId=sc.CId
and   sc.SId=student.SId
 
-- 7. 查询没有学全所有课程的同学的信息
 
-- 这种解法不包括什么课都没选的学生
select student.* from student, (select sid, count(cid) as coursecount from sc group by sid) as t1
where student.sid = t1.sid 
and t1.coursecount<>3
 
select student.*
from sc ,student
where sc.SId=student.SId
GROUP BY sc.SId
Having count(*)<(select count(*) from course)
 
 
-- 利用笛卡尔积可以把什么课都没选的同学查询出来
select DISTINCT student.*
from 
(select student.SId,course.CId
from student,course ) as t1 LEFT JOIN (SELECT sc.SId,sc.CId from sc)as t2 on t1.SId=t2.SId and t1.CId=t2.CId,student
where t2.SId is null
and   t1.SId=student.SId
 
 
select student.* from student
where student.sid not in (
select student.sid from sc, student 
where sc.sid = student.sid
group by sid 
having count(*) = (select count(*) from course))
 
-- 8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
 
select DISTINCT student.* 
from sc, student
where sc.CId in (
select CId from sc where SId='01')
and sc.SId = student.SId
 
-- 9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
 
select DISTINCT student.*
from (
select student.SId, t.CId
from student ,(select sc.CId from sc where sc.SId='01') as t)
as t1 LEFT JOIN sc on t1.SId=sc.SId and t1.CId=sc.CId,student
where sc.SId is not null 
and t1.SId=student.SId
and t1.SId <>'01'
 
-- 10. 查询没学过"张三"老师讲授的任一门课程的学生姓名
 
 -- solution1
select * 
from student 
where student.SId not in (
select student.SId from (
select student.SId, t.CId from (
select course.CId from course, teacher 
where  course.TId = teacher.TId and teacher.Tname = '张三') as t, student) as t1 
right join sc on t1.SId=sc.SId and t1.CId=sc.CId, student
where t1.SId=student.SId)
 
-- solution2
select *
from student 
where student.SId not in 
(
    select student.SId
from student left join sc on student.SId=sc.SId 
where EXISTS 
(select *
from teacher ,course
where teacher.Tname='张三'
and   teacher.TId=course.TId
and 	course.CId=sc.CId))
 
-- 11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
 
select student.SId, student.Sname, t2.avgscore
from student, (select sc.SId, avg(sc.score) as avgscore from sc group by sc.SId) as t2
where student.SId = t2.SId
and student.SId in (
select t1.SId from
(select * from sc
where sc.score < 60) as t1
group by t1.SId
having count(*) >= 2)
 
-- 12. 检索" 01 "课程分数小于60，按分数降序排列的学生信息
 
select student.*, t1.score from student, (
select * from sc 
where sc.CId = '01' and sc.score < 60) as t1
where student.SId = t1.SId
order by t1.score DESC
 
-- 13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
 
SELECT sc.*, c.avg FROM sc INNER JOIN (SELECT SId, AVG(score) avg FROM sc GROUP BY SId) c ON sc.SId=c.SId ORDER BY c.avg DESC, sc.score DESC
 
-- 14. 查询各科成绩最高分、最低分和平均分： 以如下形式显示：
-- 课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 
-- 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
 
SELECT CId, COUNT(*) '选课人数', MAX(score), MIN(score), AVG(score), 
SUM(CASE 
	WHEN score>=60 THEN
		1
	ELSE
		0
END
)/COUNT(*) '及格率', 
SUM(CASE 
	WHEN score>=70 AND score<80 THEN
		1
	ELSE
		0
END
)/COUNT(*) '中等率',
SUM(CASE 
	WHEN score>=80 AND score<90 THEN
		1
	ELSE
		0
END
)/COUNT(*) '优良率',
SUM(CASE 
	WHEN score>=90 THEN
		1
	ELSE
		0
END
)/COUNT(*) '优秀率'
FROM sc GROUP BY CId ORDER BY COUNT(*) DESC, CId
 
-- 15. 按各科成绩进行排序，并显示排名，Score 重复时保留名次空缺
 
SELECT *, RANK() OVER(PARTITION BY CId ORDER BY score DESC) FROM sc
 
-- 15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
 
SELECT *, DENSE_RANK() OVER(PARTITION BY CId ORDER BY score DESC) FROM sc
 
-- 16. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺
 
SELECT sc.SId, SUM(score) '总成绩', RANK() OVER(ORDER BY SUM(score) DESC) '排名' FROM sc GROUP BY SId  
 
 
-- 16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
 
SELECT sc.SId, SUM(score) '总成绩', DENSE_RANK() OVER(ORDER BY SUM(score) DESC) '排名' FROM sc GROUP BY SId  
 
 
-- 17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
 
SELECT CId, 
SUM(CASE 
	WHEN score<60 THEN
		1
	ELSE
		0
END
) s_60, 
SUM(CASE 
	WHEN score>=60 AND score<70 THEN
		1
	ELSE
		0
END
) s_70, 
SUM(CASE 
	WHEN score>=70 AND score<85 THEN
		1
	ELSE
		0
END
) s_85, 
SUM(CASE 
	WHEN score>=85 THEN
		1
	ELSE
		0
END
) s_100, 
SUM(CASE 
	WHEN score<60 THEN
		1
	ELSE
		0
END
)/COUNT(*) p_60, 
SUM(CASE 
	WHEN score>=60 AND score<70 THEN
		1
	ELSE
		0
END
)/COUNT(*) p_70, 
SUM(CASE 
	WHEN score>=70 AND score<85 THEN
		1
	ELSE
		0
END
)/COUNT(*) p_85, 
SUM(CASE 
	WHEN score>=85 THEN
		1
	ELSE
		0
END
)/COUNT(*) p_100
FROM sc GROUP BY CId
 
-- 18. 查询各科成绩前三名的记录
 
SELECT * FROM (SELECT *, RANK() OVER(PARTITION BY CId ORDER BY score DESC) r FROM sc) a WHERE a.r<=3
 
-- 19. 查询每门课程被选修的学生数
SELECT sc.CId, COUNT(*) FROM sc GROUP BY CId
 
 
-- 20. 查询出只选修两门课程的学生学号和姓名

SELECT s.SId, Sname FROM student s WHERE s.SId IN (SELECT SId FROM sc GROUP BY SId HAVING COUNT(*)=2) 

-- 21. 查询男生、女生人数
 
SELECT s.Ssex, COUNT(*) FROM student s GROUP BY Ssex
 
-- 22. 查询名字中含有「风」字的学生信息
 
SELECT * FROM student WHERE Sname LIKE '%风%'
 
 
-- 23. 查询同名同姓学生名单，并统计同名人数
 
SELECT Sname, count(*)
 
FROM student
 
GROUP BY Sname
 
HAVING COUNT(*) >1;
 
 
-- 24. 查询 1990 年出生的学生名单
 
SELECT * FROM student WHERE Sage LIKE '1990%'
 
-- 25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
 
SELECT CId, AVG(score) avg_score FROM sc GROUP BY CId ORDER BY avg_score DESC, CId 
 
-- 26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
 
SELECT s.SId, Sname, c.avg FROM student s, (SELECT SId, AVG(score) avg FROM sc GROUP BY SId HAVING AVG(score)>=85) c WHERE s.SId=c.SId
 
-- 27.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
 
SELECT Sname, score FROM student s, sc, (SELECT CId FROM course WHERE Cname='数学') c WHERE s.SId=sc.SId AND sc.CId=c.CId AND score<60
 
-- 28.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
 
SELECT student.SId, Sname, CId, score FROM student LEFT JOIN sc ON student.SId=sc.SId
 
-- 29.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
 
SELECT Sname, c.CId, score FROM student s, (SELECT * FROM sc WHERE score>70) c WHERE s.SId=c.SId
 
-- 30.查询不及格的课程
 
SELECT CId, score FROM sc WHERE score<60
 
-- 31.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
 
SELECT s.SId, Sname FROM student s, sc WHERE CId=01 AND score>80 AND s.SId=sc.SId
 
-- 32.求每门课程的学生人数
 
SELECT CId, COUNT(*) FROM sc GROUP BY CId
 
-- 33.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
 
SELECT s.*, sc.score FROM student s, sc, course c, teacher t WHERE Tname='张三' AND sc.CId=c.CId AND s.SId=sc.SId ORDER BY score DESC LIMIT 1
 
-- 34.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
 
SELECT * 
FROM 
(SELECT s.*, sc.score ,RANK() OVER(ORDER BY score DESC) r FROM student s, sc, course c, teacher t WHERE Tname='张三' AND sc.CId=c.CId AND s.SId=sc.SId) t 
WHERE t.r=1
 
-- 35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
 
 
SELECT SId, sc.CId, sc.score 
FROM sc, 
(SELECT sc.CId, sc.score FROM sc GROUP BY CId, score HAVING COUNT(*)>=2) s 
WHERE sc.CId=s.CId AND sc.score=s.score 
 
 
-- 36.查询每门功成绩最好的前两名
 
SELECT * 
FROM 
(SELECT *, RANK() OVER(PARTITION BY CId ORDER BY score DESC) r FROM sc) s 
WHERE s.r<=2
 
 
-- 37.统计每门课程的学生选修人数（超过 5 人的课程才统计)
 
SELECT CId, COUNT(*) FROM sc GROUP BY CId HAVING COUNT(*)>5
 
 
-- 38.检索至少选修两门课程的学生学号
 
SELECT SId FROM sc GROUP BY SId HAVING COUNT(*)>=2
 
-- 39.查询各学生的年龄，只按年份来算
 
SELECT SId, Sname, YEAR(NOW())-YEAR(Sage) age FROM student
 
 
-- 40.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
 
SELECT SId, Sname, TIMESTAMPDIFF(YEAR,Sage,NOW()) age FROM student
 
-- 41. 查询本周过生日的学生
 
SELECT * FROM student 
WHERE 
WEEKOFYEAR(CONCAT(YEAR(NOW()), '-', MID(Sage,6,5))) = WEEKOFYEAR(NOW())
 
 
-- 42.查询下周过生日的学生
 
SELECT * FROM student WHERE WEEKOFYEAR(CONCAT(YEAR(NOW()), '-', MID(Sage,6,5)))+1 = WEEKOFYEAR(NOW())
 
-- 43.查询本月过生日的学生
 
SELECT * FROM student WHERE MONTH(Sage) = MONTH(NOW())
 
 
-- 44.查询下月过生日的学生
 
SELECT * FROM student WHERE MONTH(Sage) = MONTH(NOW())+1
今晚月亮有点圆
关注
经典50道SQL练习题
wbc199685的博客
09-15 2万+
前言 作为一个SQL小白，在一个月的努力后终于把这50道练习题做完了，分享一下我的答案给大家，说不定能给你提供一些思路。 我使用的是在线SQL，个人认为十分方便，使用的版本是“My SQL Server 2017”。 一、数据表 学生表 Student(SId,Sname,Sage,Ssex)–SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别 create table Student(SId varchar(10),Sname varchar(10),Sage dateti
SQL经典50题
纸上得来终觉浅，绝知此事要躬行
07-07 1万+
SQL经典50题参考自B站视频
Mysql 练习题
victoyr的博客
09-08 809
练习数据 数据表 –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.成绩表...
经典的SQL练习50题，附答案及笔者的实践内容
Python数据之道
12-05 5423
相信大多学习了 Mysql 数据库语言的同学都会上网找练习来练手，而大部分的人肯定知道有一篇 Mysql 经典练习题50题的帖子，上面的题目基本上涵盖了 Mysql 查询语句的关键知识点...
50道MySQL经典练习题
12-14
这个是网上流传的50道SQL练习题，最近拿来练习，刚做完，这里把我做的答案给大家做个参考，如有错误，还请告知。 另外，做题的时候先不要看答案（答案不唯一，只要满足要求即可），要有自己的一个思考过程，这样做...
MySQL50道练习题.zip
04-13
这里我们有“MySQL50道练习题”的压缩包，它包含了一系列问题，旨在帮助用户深入理解和掌握MySQL的基本操作、查询技巧以及高级功能。通过解答这些练习题，你可以提升自己的SQL技能，更好地应对实际工作中的数据管理...
mysql经典50题_大数据_mysql经典50题_mysql经典五十题_hive_
10-01
下面，我们将深入探讨MySQL的经典问题以及它们与Hive的相关性。 1. **SQL基础** - **数据类型**：MySQL支持多种数据类型，如整型（INT, TINYINT等）、浮点型（FLOAT, DOUBLE）、字符串（VARCHAR, CHAR）和日期时间...
MySQL数据库练习题(50道).pdf
04-25
MySQL数据库练习题50道.pdf 本资源为MySQL数据库练习题，共50道题，涵盖数据库设计、数据表创建、数据插入、查询等多方面的知识点。下面是对该资源的详细知识点总结： 一、数据库设计 * 数据库设计是指根据具体的...
MySQL常用练手题目集合 MySQL查询练习题（50题） 共18页.pdf
08-21
MySQL查询练习题旨在帮助用户熟悉和提升SQL查询技巧，涵盖了多方面的数据库操作，如联接查询、聚合函数、条件查询、子查询等。以下是对这些题目的详细解析： 1. **建表**： - 学生表：可能包含学生ID、姓名、性别...
经典SQL语句50题
12-02
一个项目涉及到50个SQL语句。（经典）
MySQL练习50题
dieheng7501的博客
03-29 170
这是网上的练习，花了点时间用MySQL做了一下。 初始化数据库： create database db50issues; use db50issues; create table Student(sid varchar(10),sname nvarchar(10),sage datetime,ssex nvarchar(10)); insert into Stude...
SQL 经典50题（题目+解答）（1）
热门推荐
Robin_Pi的博客
03-10 1万+
SQL 经典50题（题目+解答）（1）
经典sql面试及答案（50）
chuyinquan2438的博客
10-10 1165
经典sql面试及答案（50） （新链接）SQL 经典五十道题 联想笔记本数据库csdn1010 复习 重点知识/关键词 like、rlike MySQL EXTRACT() 函数 行列转换&多行合并为一行（第40）【数据库表行列转置的实现_My...
50道经典SQL练习题
xjfrb的博客
05-28 626
说在前面 这是网上比较火的50道SQL练习题，附上自己的答案，有错误的地方希望各位可以指出，共同进步。 表的内容及练习题参考自： 作者：LSD_Monkey 链接：https://www.jianshu.com/p/476b52ee4f1b 数据表及数据 数据表介绍 数据表介绍 –1.学生表 Student(SId,Sname,Sage,Ssex) –SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别 –2.课程表 Course(CId,Cname,TId) –CId 课程编
数据人的经典SQL50题
weixin_45014634的博客
09-02 581
sql经典五十题 – 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数 select * from (select * from (select s_id,c_id, s_score from Score where c_id=01)a , (select c_id as n_id, s_score n_score from Score where c_id=02) b where a.s_score > b.n_score) c inner join student on c.s_
SQL笔试经典50题
liushuang99的博客
08-28 4241
MySQL经典50题 1、 #1、查询“01”课程比“02”课程成绩高的所有学生的学号； SELECT s1.sid FROM SC s1 JOIN SC s2 ON s1.sid=s2.sid AND s1.cid='01' AND s2.cid='02' AND s1.score>s2.score 2、 #2、查询平均成绩大于60分的同学的学号和平均成绩； SELECT sid as 学号, avg(score) as 平均成绩 FROM SC GROUP BY sid HAVING 平均成绩&
SQL经典五十道面试题
WC名字好难取的博客
11-19 2916
注：这些题不是作者本人设计，只是收集。 数据库表 Student(S#,Sname,Sage,Ssex) 学生表 S#：学号；Sname：学生姓名；Sage：学生年龄；Ssex：学生性别 Course(C#,Cname,T#) 课程表 C#,课程编号；Cname：课程名字；T#：教师编号 SC(S#,C#,score) 成绩表 S#：学号；C#,课程编号；score：成绩 Teacher(T#,T...
mysql经典50道练习题pdf
最新发布
08-17
MySQL经典50道练习题pdf是一本经典的MySQL数据库练习题集合，对于想要提高MySQL数据库操作能力的人士来说是非常有价值的。 这本练习题集合涵盖了各种MySQL数据库操作方面的题目，包括数据库创建、表创建、数据插入...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值