SQL经典五十道选刷

2024-08-31 22:04
文章标签 sql 经典 database 五十道 选刷

本文主要是介绍SQL经典五十道选刷，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

SQL经典五十道选刷（选了较有代表性的三十道，不代表最优解，仅提供思路）
–1.学生表
— Student(S,Sname,Sage,Ssex)
–S 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
–2.课程表
– Course(C,Cname,T)
–C --课程编号,Cname 课程名称,T 教师编号
–3.教师表
– Teacher(T,Tname)
–T 教师编号,Tname 教师姓名
–4.成绩表
– SC(S,C,score)
–S 学生编号,C 课程编号,score 分数

–创建测试数据
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’ , ‘男’);
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(‘08’ , ‘王菊’ , ‘1990-01-20’ , ‘女’);
create table Course(C varchar(10),Cname varchar(10),T varchar(10));
insert into Course values(‘01’ , ‘语文’ , ‘02’);
insert into Course values(‘02’ , ‘数学’ , ‘01’);
insert into Course values(‘03’ , ‘英语’ , ‘03’);
create table Teacher(T varchar(10),Tname varchar(10));
insert into Teacher values(‘01’ , ‘张三’);
insert into Teacher values(‘02’ , ‘李四’);
insert into Teacher values(‘03’ , ‘王五’);
create table SC(S varchar(10),C 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);

– 第一道，查询01课程成绩大于02课程成绩的学生信息及课程分数
SELECT
s.Sname,
s.Ssex,
s.Sage,
a.S,
a.C,
a.score score01,
b.score score02
FROM
( SELECT * FROM sc WHERE C = 01 ) a
INNER JOIN ( SELECT * FROM sc WHERE C = 02 ) b ON a.S = b.S
INNER JOIN student s ON a.S=s.S
WHERE
a.score > b.score;
– 第二道，查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT
s.Sname,
sc.S,
AVG( sc.score )
FROM
sc
INNER JOIN student s ON s.S = sc.S
GROUP BY
sc.S;
– 第三道，查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT
s.S,
s.Sname,
count( sc.C ),
SUM( sc.score )
FROM
sc
INNER JOIN student s ON s.S = sc.S
GROUP BY
sc.S;
– 第四道，查询"李"姓老师的数量
SELECT
COUNT( t.Tname )
FROM
teacher t
WHERE
t.Tname LIKE ‘李%’;
– 第五道，查询学过"张三"老师授课的同学的信息
SELECT
s.S,
s.Sname,
s.Sage,
s.Ssex
FROM
student s
INNER JOIN sc ON s.S = sc.S
WHERE
sc.C =(
SELECT
c.C
FROM
teacher t
INNER JOIN course c ON t.T = c.T
WHERE
t.Tname = ‘张三’
);
– 第六道，查询没学过"张三"老师授课的同学的信息
SELECT
s.S,
s.Sname,
s.Sage,
s.Ssex
FROM
student s
WHERE
s.S NOT IN (
SELECT
s.S
FROM
student s
INNER JOIN sc ON s.S = sc.S
WHERE
sc.C =(
SELECT
c.C
FROM
teacher t
INNER JOIN course c ON t.T = c.T
WHERE
t.Tname = ‘张三’
));
– 第七道，查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT
s.S,
s.Sname,
s.Sage,
s.Ssex
FROM
student s
INNER JOIN ( SELECT * FROM sc WHERE sc.C = ‘01’ ) a ON s.S = a.S
INNER JOIN ( SELECT * FROM sc WHERE sc.C = ‘02’ ) b ON s.S = b.S;
– 第八道，查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT
s.S,
s.Sname,
s.Sage,
s.Ssex
FROM
student s
INNER JOIN ( SELECT * FROM sc WHERE sc.C = ‘01’ ) a ON a.S = s.S
WHERE
s.S NOT IN ( SELECT sc.S FROM sc WHERE sc.C = ‘02’ );
– 第九道，查询没有学全所有课程的同学的信息
SELECT
s.S,
s.Sname,
s.Sage,
s.Ssex,
COUNT( sc.C ) c_num
FROM
student s
INNER JOIN sc ON s.S = sc.S
GROUP BY
sc.S
HAVING
c_num <(
SELECT
COUNT( c.C )
FROM
course c)
– 第十道，查询至少有一门课与学号为"01"的同学所学相同的同学的信息
SELECT DISTINCT
s.S,
s.Sname,
s.Sage,
s.Ssex
FROM
student s
INNER JOIN sc ON sc.S = s.S
WHERE
sc.C IN (
SELECT
sc.C
FROM
sc
WHERE
sc.S = ‘01’);
– 第十一道，查询和"01"号的同学学习的课程完全相同的其他同学的信息
SELECT
s.S,
s.Sname,
s.Sage,
s.Ssex,
GROUP_CONCAT( sc.C ORDER BY sc.C ) cs
FROM
student s
INNER JOIN sc ON sc.S = s.S
GROUP BY
sc.S
HAVING
cs =(
SELECT
GROUP_CONCAT( sc.C ORDER BY sc.C )
FROM
sc
WHERE
sc.S = ‘01’
);
– 第十二道，查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT
s.S,
s.Sname
FROM
student s
INNER JOIN sc ON sc.S = s.S
GROUP BY
sc.S
HAVING
NOT FIND_IN_SET((
SELECT
GROUP_CONCAT( c.C )
FROM
course c
INNER JOIN teacher t ON t.T = c.T
WHERE
t.Tname = ‘张三’
),
GROUP_CONCAT( sc.C ));
– 第十三道，查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT
s.Sname,
temp.S,
AVG( temp.score ) avg_score
FROM
( SELECT sc.S, sc.score FROM sc WHERE sc.score < 60 ) AS temp
INNER JOIN student s ON temp.S = s.S
GROUP BY
sc.S
HAVING
COUNT( temp.score )>=2;
– 第十四道，检索"01"课程分数小于60，按分数降序排列的学生信息
SELECT
s.*,
sc.C,
sc.score
FROM
student s
INNER JOIN sc ON s.S = sc.S
WHERE
sc.S IN ( SELECT sc.S FROM sc WHERE sc.C = ‘01’ AND sc.score < 60 )
ORDER BY
sc.score DESC
– 第十五道，按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT
sc.S,
sc.C,
sc.score,
temp.avg_score
FROM
sc
INNER JOIN ( SELECT sc.S, AVG( sc.score ) avg_score FROM sc GROUP BY sc.S ) AS temp ON temp.S = sc.S
ORDER BY
avg_score DESC;
– 第十六道，查询各科成绩最高分、最低分和平均分：以如下形式显示：–课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率–及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
WITH t1 AS (SELECT sc.C,COUNT(sc.score) AS scores FROM sc WHERE sc.score >= 60 AND sc.score <= 70 GROUP BY sc.C),
t2 AS (SELECT sc.C,COUNT(sc.score) AS scores FROM sc WHERE sc.score>70 AND sc.score<=80 GROUP BY sc.C),
t3 AS (SELECT sc.C,COUNT(sc.score) AS scores FROM sc WHERE sc.score>80 AND sc.score<=90 GROUP BY sc.C),
t4 AS (SELECT sc.C,COUNT(sc.score) AS scores FROM sc WHERE sc.score>90 GROUP BY sc.C)
SELECT sc.C ‘课程ID’,c.Cname ‘课程name’,MAX(sc.score) ‘最高分’,MIN(sc.score) ‘最低分’,AVG(sc.score) ‘平均分’,
ROUND(t1.scores/COUNT(sc.score),2) ‘及格率’,
ROUND(t2.scores/COUNT(sc.score),2) ‘中等率’,
ROUND(t3.scores/COUNT(sc.score),2) ‘优良率’,
ROUND(t4.scores/COUNT(sc.score),2) ‘优秀率’
FROM sc
INNER JOIN course c ON sc.C=c.C
LEFT JOIN t1 ON sc.C=t1.C
LEFT JOIN t2 ON sc.C=t2.C
LEFT JOIN t3 ON sc.C=t3.C
LEFT JOIN t4 ON sc.C=t4.C
GROUP BY sc.C;
– 第十七道，查询学生的总成绩并进行排名
SELECT
sc.S,
SUM( sc.score ) AS sum_score
FROM
sc
GROUP BY
sc.S
ORDER BY
sum_score DESC
– 第十八道，查询不同老师所教不同课程平均分从高到低显示
SELECT
t.Tname,
sc.C,
AVG( sc.score ) AS avg_score
FROM
sc
INNER JOIN course c ON c.C = sc.C
INNER JOIN teacher t ON t.T = c.T
GROUP BY
sc.C
ORDER BY
avg_score DESC;

– 第十九道，查询同名同性学生名单，并统计同名人数
SELECT s.Sname, COUNT( s.Sname ) AS rep_names
FROM student s
GROUP BY s.Sname
HAVING rep_names >= 2;

– 第二十道，查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)
SELECT
s.S,
s.Sname
FROM
student s
WHERE
s.Sage BETWEEN ‘1990-01-01’
AND ‘1990-12-31’;
– 第二十一道，查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号
SELECT
AVG( sc.score ) AS avg_score,
sc.C
FROM
sc
GROUP BY
sc.C
ORDER BY
avg_score,
sc.C;
– 第二十二道，查询课程名称为"数学"，且分数低于60的学生姓名和分数
WITH temp AS (
SELECT
c.C,
sc.S,
sc.score
FROM
sc
INNER JOIN course c ON c.C = sc.C
WHERE
c.Cname = ‘数学’
AND sc.score < 60
) SELECT
s.Sname,
sc.score
FROM
student s
INNER JOIN sc ON sc.S = s.S
INNER JOIN temp ON temp.S = s.S
WHERE
s.S = temp.S
AND sc.C = temp.C;
– 第二十三道，查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
WITH temp AS ( SELECT sc.score FROM sc GROUP BY sc.score HAVING COUNT( sc.score )>= 2 ) SELECT
sc.S,
sc.C,
sc.score AS c_num
FROM
sc
INNER JOIN temp ON temp.score = sc.score
ORDER BY
sc.score;
– 第二十四道，查询每门课成绩最好的前两名
WITH ranked_score AS (
SELECT
sc.S,
sc.C,
sc.score,
ROW_NUMBER() OVER ( PARTITION BY sc.C ORDER BY sc.score DESC ) AS rn
FROM
sc
) SELECT
S,
C,
score
FROM
ranked_score
WHERE
rn <= 2
ORDER BY
C,
score DESC;
– 第二十五道，统计每门课程的学生选修人数（超过5人的课程才统计），要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT sc.C, COUNT( sc.S ) AS s_num
FROM sc
GROUP BY sc.C
HAVING s_num > 5
ORDER BY s_num DESC,C;
– 第二十六道，检索选修超过两门课程的学生学号
SELECT
sc.S
FROM
sc
GROUP BY
sc.S
HAVING
COUNT( sc.S )> 2;
– 第二十七道，查询选修了全部课程的学生信息
SELECT
s.S,
s.Sname,
s.Ssex,
s.Sage
FROM
student s
INNER JOIN sc ON sc.S = s.S
GROUP BY
sc.S
HAVING
COUNT( sc.S )=(
SELECT
COUNT( c.C )
FROM
course c
);
– 第二十八道，查询各学生的年龄
SELECT
s.S,
s.Sname,
TIMESTAMPDIFF( YEAR, s.Sage, CURRENT_DATE ) AS age
FROM
student s;
– 第二十九道，查询本周过生日的学生
SELECT s.S, s.Sname, s.Sage
FROM student s
WHERE WEEK(s.Sage, 1) = WEEK(CURDATE(), 1)
AND DAYOFYEAR(s.Sage) >= DAYOFYEAR(CURDATE()) - WEEKDAY(CURDATE())
AND DAYOFYEAR(s.Sage) <= DAYOFYEAR(CURDATE()) + (6 - WEEKDAY(CURDATE()));
– 第三十道，查询下个月过生日的学生
SELECT s.S,s.Sname,s.Sage
FROM student s
WHERE MONTH(s.Sage)=
CASE
WHEN MONTH(CURDATE()=12) THEN 1
ELSE MONTH(CURDATE())+1
END
AND YEAR(s.Sage)=
CASE
WHEN MONTH(CURDATE()=12) THEN YEAR(CURDATE())+1
ELSE YEAR(CURDATE())
END;

这篇关于SQL经典五十道选刷的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1125115。

相关文章

Mysql 中的多表连接和连接类型详解

Mysql 中的多表连接和连接类型详解

《Mysql中的多表连接和连接类型详解》这篇文章详细介绍了MySQL中的多表连接及其各种类型,包括内连接、左连接、右连接、全外连接、自连接和交叉连接,通过这些连接方式,可以将分散在不同表中的相关数据... 目录什么是多表连接？1. 内连接（INNER JOIN）2. 左连接（LEFT JOIN 或 LEFT
阅读更多...
mysql重置root密码的完整步骤(适用于5.7和8.0)

mysql重置root密码的完整步骤(适用于5.7和8.0)

《mysql重置root密码的完整步骤(适用于5.7和8.0)》：本文主要介绍mysql重置root密码的完整步骤,文中描述了如何停止MySQL服务、以管理员身份打开命令行、替换配置文件路径、修改... 目录第一步：先停止mysql服务，一定要停止！方式一：通过命令行关闭mysql服务方式二：通过服务项关闭
阅读更多...
SQL Server数据库磁盘满了的解决办法

SQL Server数据库磁盘满了的解决办法

《SQLServer数据库磁盘满了的解决办法》系统再正常运行,我还在操作中,突然发现接口报错,后续所有接口都报错了,一查日志发现说是数据库磁盘满了,所以本文记录了SQLServer数据库磁盘满了的解... 目录问题解决方法删除数据库日志设置数据库日志大小问题今http://www.chinasem.cn天发
阅读更多...
mysql主从及遇到的问题解决

mysql主从及遇到的问题解决

《mysql主从及遇到的问题解决》本文详细介绍了如何使用Docker配置MySQL主从复制,首先创建了两个文件夹并分别配置了`my.cnf`文件,通过执行脚本启动容器并配置好主从关系,文中还提到了一些... 目录mysql主从及遇到问题解决遇到的问题说明总结mysql主从及遇到问题解决1.基于mysql
阅读更多...
MySQL的索引失效的原因实例及解决方案

MySQL的索引失效的原因实例及解决方案

《MySQL的索引失效的原因实例及解决方案》这篇文章主要讨论了MySQL索引失效的常见原因及其解决方案,它涵盖了数据类型不匹配、隐式转换、函数或表达式、范围查询、LIKE查询、OR条件、全表扫描、索引... 目录1. 数据类型不匹配2. 隐式转换3. 函数或表达式4. 范围查询之后的列5. like 查询6
阅读更多...
Linux下MySQL8.0.26安装教程

Linux下MySQL8.0.26安装教程

《Linux下MySQL8.0.26安装教程》文章详细介绍了如何在Linux系统上安装和配置MySQL,包括下载、解压、安装依赖、启动服务、获取默认密码、设置密码、支持远程登录以及创建表,感兴趣的朋友... 目录1.找到官网下载位置1.访问mysql存档2.下载社区版3.百度网盘中2.linux安装配置1.
阅读更多...
PostgreSQL如何用psql运行SQL文件

PostgreSQL如何用psql运行SQL文件

《PostgreSQL如何用psql运行SQL文件》文章介绍了两种运行预写好的SQL文件的方式：首先连接数据库后执行,或者直接通过psql命令执行,需要注意的是,文件路径在Linux系统中应使用斜杠/... 目录PostgreSQ编程L用psql运行SQL文件方式一方式二总结PostgreSQL用psql运
阅读更多...
SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2