MySQL试题2

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL试题2
##二、题目:
#01)查询" 1 "课程比" 2 "课程成绩高的学生的信息及课程分数
#方法一
  select s.*,t1.score,t2.score from
  (select sid,score from t_score where cid=1) t1,
  (select sid,score from t_score where cid=2) t2,
  t_student s
  where t1.sid=t2.sid and t1.sid=s.sid 
  and t1.score>t2.score
#方法二
select stu.*,t1.score,t2.score from t_student stu
INNER JOIN
(select * from t_score where cid = 1) t1
on stu.sid = t1.sid
INNER JOIN
(select * from t_score where cid = 2) t2
on t1.sid = t2.sid and t1.score > t2.score
#02)查询同时存在" 01 "课程和" 02 "课程的情况
#方法一
  select * from 
    (select * from t_score as sc where sc.cid=1) as t1,
    (select * from t_score as sc where sc.cid=2) as t2
  where t1.sid = t2.sid;  
#方法二
select stu.*,t1.score,t2.score from t_student stu
INNER JOIN
(select * from t_score where cid = 1) t1
on stu.sid = t1.sid
INNER JOIN
(select * from t_score where cid = 2) t2
on t1.sid = t2.sid 
#03)查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
select stu.sname,tmp.c1,tmp.c2 from t_student stu inner join
  (select sid,
    SUM(case when cid = 1 then score else null end) c1,
    SUM(case when cid = 2 then score else null end) c2
  from t_score GROUP BY sid
  HAVING c1 is not null and c1>=0) tmp on stu.sid = tmp.sid
#04)查询不存在" 01 "课程但存在" 02 "课程的情况
select stu.sname,tmp.c1,tmp.c2 from t_student stu inner join
  (select sid,
    SUM(case when cid = 1 then score else null end) c1,
    SUM(case when cid = 2 then score else null end) c2
  from t_score GROUP BY sid
  HAVING c1 is null and c2 is not null) tmp on stu.sid = tmp.sid
#05)查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
#子查询+内连接
  select stu.sid,sname,avgScore from t_student as stu inner join(
    select sid,ROUND(avg(score),2) as avgScore from t_score
    GROUP BY sid
    HAVING avg(score) >=60
  )b
  on stu.sid = b.sid
#联表查询
select s.sid,s.sname,ROUND(avg(sc.score),2) as avgScore 
from t_student s,t_score sc
where s.sid = sc.sid
group by s.sid,s.sname
having avgScore>=60;
#内连接
  select stu.sid,sname,ROUND(avg(score),2) as avgScore 
  from t_student as stu 
  inner join 
  t_score as sc
  on stu.sid = sc.sid 
  GROUP BY 
  stu.sid
  HAVING avgScore>=60
#4
select stu.sid,stu.sname,tmp.a from t_student stu inner join
  (select sid,ROUND(avg(score),2) as avgScore from t_score group by sid ) tmp 
  on stu.sid = tmp.sid 
  where tmp.avgScore>=60
#06)查询在t_score表存在成绩的学生信息
#方法一
select DISTINCT s.* from t_student s,t_score sc where s.sid=sc.sid
#方法二
select DISTINCT stu.* from t_student as stu
inner join
t_score as sc
on stu.sid = sc.sid
#07)查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select stu.sid,sname,COUNT(score)
as 选课总数,IF(SUM(score)!=0,SUM(score),NULL) as 总成绩 from t_student as stu
inner join 
t_score as sc 
on stu.sid = sc.sid
GROUP BY stu.sid
#08)查询「李」姓老师的数量
select tid,tname,COUNT(*) as 数量 from t_teacher 
where tname like '李%'
group by tid
select COUNT(*) from t_teacher 
  where tname like'李%'
  GROUP BY tid
#09)查询学过「张五哥」老师授课的同学的信息
SELECT stu.* from t_student stu where sid in
(select sid from t_score where cid  in
(select cid from t_course where tid in
(select tid from t_teacher where tname ='张五哥')))
#10)查询没有学全所有课程的同学的信息
select * from t_student where sid not in(
  select sid from t_score 
  group by sid 
  having count(cid)=3);
  select sid,cid from t_score 
  group by sid 
  having count(cid)>=2
select stu.*,COUNT(*) as course from t_student as stu 
  LEFT JOIN t_score as sc
  on stu.sid = sc.sid
  GROUP BY stu.sid
  HAVING course<3
#11)查询没学过"张五哥"老师讲授的任一门课程的学生姓名
SELECT stu.* from t_student stu where sid not in
(select sid from t_score where cid  in
(select cid from t_course where tid in
(select tid from t_teacher where tname ='张五哥')))
#12)查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
  select stu.sid,stu.sname,tmp.a as 平均成绩 from t_student stu 
  inner join
  (select sid,
  ROUND(avg(score),2) as a,
  sum(case when cid = 1 then (case when score>60 then score else 0 end) else 0 end) c1,
  sum(case when cid = 2 then (case when score>60 then score else 0 end) else 0 end) c2,
  sum(case when cid = 3 then (case when score>60 then score else 0 end) else 0 end) c3
  from t_score 
  group by sid 
  having
  c1=0 and c2=0 or c1=0 and c3=0 or c2=0 and c3=0) tmp
  on stu.sid = tmp.sid 
          SELECT t.sid, COUNT(*) num FROM (SELECT sid, cid FROM t_score WHERE score < 60) t GROUP BY t.sid HAVING num >= 2
  select sid,count(score) from t_score where score < 60  group by sid
#13)检索" 01 "课程分数小于 60,按分数降序排列的学生信息
select stu.*,emp.score from t_student stu inner join(
  select * from t_score where cid = 1 and score < 60) emp
  on stu.sid = emp.sid
  ORDER BY 
  emp.score desc
#14)按平均成绩从高到低(降序)显示所有学生的所有课程的成绩以及平均成绩
SELECT stu.sid,stu.sname, 
  SUM(CASE WHEN sc.cid = 1 THEN sc.score ELSE 0 END) as c1,
  SUM(CASE WHEN sc.cid = 2 THEN sc.score ELSE 0 END) as c2,
  SUM(CASE WHEN sc.cid = 3 THEN sc.score ELSE 0 END) as c3,
  ROUND(AVG(sc.score), 2) as avgScore
FROM t_score as sc 
INNER JOIN t_student as stu ON sc.sid = stu.sid
 GROUP BY stu.sid, stu.sname
 ORDER BY avgScore DESC;
#15)查询各科成绩最高分、最低分和平均分:以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
select 
cid as '课程编号',
count(cid) as'选修人数',
max(score)as'最高分',
min(score)as'最低分',
ROUND(avg(score),2)as'平均分' ,
CONCAT(ROUND((select count(*) from t_score where score>=60 and cid=sc.cid group by cid)/count(cid)*100,2),'%')as'及格率',
CONCAT(ROUND((select count(*) from t_score where score between 70 and 80 and cid =sc.cid group by cid)/count(cid)*100,2),'%')as'中等率',
CONCAT(ROUND((select count(*) from t_score where score between 80 and 90 and cid =sc.cid group by cid)/count(cid)*100,2),'%')as'优良率',
CONCAT(ROUND((select count(*) from t_score where score>=90 and cid =sc.cid group by cid)/count(cid)*100,2),'%')as'优秀率'
from t_score as sc 
group by cid
order by count(cid) desc, cid
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
相关文章
|
JSON 数据格式
SpringMVC-接收请求中的json数据及日期类型参数传递
SpringMVC-接收请求中的json数据及日期类型参数传递
309 0
|
7月前
|
JSON 数据格式 Python
X86架构属于国产化架构吗
在信息时代,处理器架构至关重要。本文通过简单流程和代码示例,帮助初学者理解X86架构是否属于国产化架构。首先明确国产化架构指本土研发的处理器架构,如龙芯、飞腾等。X86架构由Intel设计,主要由外资公司开发,通常不被视为国产架构。文章提供了一个判断X86架构是否国产化的流程图,并用Python代码实现判断过程。Architecture类描述架构来源和类型,App类负责获取和判定架构信息。通过这些步骤,初学者可以更好地掌握相关知识并提升开发技能。
553 5
|
运维 Java Devops
阿里云云效操作报错合集之在进行GitLab代码分支迁移时遇到报错,一般是什么原因
本合集将整理呈现用户在使用过程中遇到的报错及其对应的解决办法,包括但不限于账户权限设置错误、项目配置不正确、代码提交冲突、构建任务执行失败、测试环境异常、需求流转阻塞等问题。阿里云云效是一站式企业级研发协同和DevOps平台,为企业提供从需求规划、开发、测试、发布到运维、运营的全流程端到端服务和工具支撑,致力于提升企业的研发效能和创新能力。
|
缓存 开发工具 git
Git Cloning into :error: RPC failed
Git Cloning into :error: RPC failed
213 0
|
传感器 开发工具 vr&ar
ManoMotion⭐二、Unity手势识别插件简介,及效果录屏
ManoMotion⭐二、Unity手势识别插件简介,及效果录屏
|
并行计算 算法 编译器
什么是SSA模式,它的工作原理是什么
【9月更文挑战第1天】什么是SSA模式,它的工作原理是什么
823 0
discuz,如何去掉标题中的Powered by Discuz! 电脑端手机端整合
discuz,如何去掉标题中的Powered by Discuz! 电脑端手机端整合
1140 0
航班管理系统(最全最细)
航班管理系统(最全最细)
|
JavaScript 前端开发 API
Vue与React数据流设计比较:响应式与单向数据流
Vue与React数据流设计比较:响应式与单向数据流