当前位置： 首页 > news >正文

SQL经典五十道选刷

news 来源：原创 2024/9/20 23:20:44

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;

相关文章：

  • 北京网站建设多少钱？
  • 辽宁网页制作哪家好_网站建设
  • 高端品牌网站建设_汉中网站制作
  • Tushare库：Python金融数据分析的利器
  • 鸿蒙（API 12 Beta6版）图形【 请求动画绘制帧率】方舟2D图形服务
  • Linux开发：优化VSCode C++开发体验
  • 什么酱酒能收藏几十年？快来打造你的私人酒窖宝藏
  • Vue双向绑定
  • 【Linux操作系统】线程的互斥与同步
  • machine learning - 2
  • 【VUE】Vue 组件详解
  • 【SpringBoot】使用Redis
  • 一文搞懂 | Pytorch维度转换操作：view，reshape，permute，flatten函数详解
  • linux中vim常用命令大全
  • 微软RD客户端 手机 平板 远程控制 Windows桌面
  • 【Linux】进程优先级｜进程切换
  • 【机器学习-神经网络】循环神经网络
  • GateWay三大案例组件
  • SegmentFault for Android 3.0 发布
  • ［分享］iOS开发－关于在xcode中引用文件夹右边出现问号的解决办法
  • CSS 三角实现
  • C学习-枚举（九）
  • docker容器内的网络抓包
  • Javascript弹出层-初探
  • js学习笔记
  • Protobuf3语言指南
  • SpiderData 2019年2月25日 DApp数据排行榜
  • vue脚手架vue-cli
  • Webpack4 学习笔记 - 01：webpack的安装和简单配置
  • 解决iview多表头动态更改列元素发生的错误
  • 三分钟教你同步 Visual Studio Code 设置
  • 使用SAX解析XML
  • raise 与 raise ... from 的区别
  • 测评：对于写作的人来说，Markdown是你最好的朋友 ...
  • ​什么是bug？bug的源头在哪里？
  • (delphi11最新学习资料) Object Pascal 学习笔记---第8章第5节（封闭类和Final方法）
  • (LNMP) How To Install Linux, nginx, MySQL, PHP
  • （Pytorch框架）神经网络输出维度调试，做出我们自己的网络来！！（详细教程~）
  • （二）七种元启发算法（DBO、LO、SWO、COA、LSO、KOA、GRO）求解无人机路径规划MATLAB
  • （附源码）springboot炼糖厂地磅全自动控制系统 毕业设计 341357
  • （十）c52学习之旅-定时器实验
  • （限时免费）震惊！流落人间的haproxy宝典被找到了！一切玄妙尽在此处！
  • .NET BackgroundWorker
  • .Net Web项目创建比较不错的参考文章
  • .NET教程 - 字符串 编码 正则表达式（String Encoding Regular Express）
  • .NET开发者必备的11款免费工具
  • @Mapper作用
  • @Transactional事务注解内含乾坤？
  • [ 数据结构 - C++]红黑树RBTree
  • [AR Foundation] 人脸检测的流程
  • [autojs]autojs开关按钮的简单使用
  • [AX]AX2012 AIF（四）：文档服务应用实例
  • [bzoj4010][HNOI2015]菜肴制作_贪心_拓扑排序
  • [BZOJ5250][九省联考2018]秘密袭击(DP)
  • [echarts] y轴不显示0
  • [hihocoder1395] 最大权闭合子图
  • [IE编程] 如何编程清除IE缓存
  • [ios] IOS文件操作的两种方式：NSFileManager操作和流操作【转】