MySQL经典练习题(二)
1、查询Score表中的最高分的学生学号和课程号。
select sno,cno,degree
from score
where DEGREE = (select max(DEGREE)max_grade from score )
2、查询‘3-105’号课程的平均分。
select cno,avg(DEGREE) avg_grade
from score
where cno ='3-105'
3、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select cno,avg(degree) avg_grade
from score
where cno like "3%"
group by cno
having count(*)>5
4、查询最低分大于70,最高分小于90的Sno列。
-- 不去重,可能某个sno的学生的多门课程满足该条件
select sno
from score
group by degree
having min(degree) >70 and max(degree)<90
-- 去重,因为最终结果只是需要sno列,所以可以去重处理
select DISTINCT sno
from score
group by degree
having min(degree) >70 and max(degree)<90
5、查询所有学生的Sname、Cno和Degree列。
-- 方法一
select sname,cno,degree
from student s , score sc
where s.sno = sc.sno
-- 方法二
select sname,cno,degree
from student s inner join score sc
on s.sno = sc.sno
-- 方法三
select sname,cno,degree
from student s join score sc
on s.sno = sc.sno
6、查询所有学生的Sno、Cname和Degree列。
-- 方法一
select sno,cname,degree
from score s ,course c
where s.cno =c.cno
-- 方法二
select sno,cname,degree
from score s inner join course c
on s.cno =c.cno
-- 方法三
select sno,cname,degree
from score s join course c
on s.cno =c.cno
7、查询所有学生的Sname、Cname和Degree列。
-- 方法一
select sname,cname,degree
from student s , course c, score sc
where c.cno = sc.cno and sc.sno = s.sno
-- 方法二
select sname,cname,degree
from student s inner join course c inner join score sc
on c.cno = sc.cno and sc.sno = s.sno
-- 方法三
select sname,cname,degree
from student s join course c join score sc
on c.cno = sc.cno and sc.sno = s.sno
8、查询“95033”班所选课程的平均分。
-- 方法一
select class,avg(degree) avg_grade
from score sc ,student s
where sc.sno = s.sno and class ='95033'
-- 方法二
select class,avg(degree) avg_grade
from score sc inner join student s
on sc.sno = s.sno
where class ='95033'
-- 方法三
select class,avg(degree) avg_grade
from score sc join student s
on sc.sno = s.sno
where class ='95033'
-- 方法四
select class,avg(degree) avg_grade
from score sc join student s
on sc.sno = s.sno and class ='95033'
-- 方法五
select class,avg(degree) avg_grade
from score sc inner join student s
on sc.sno = s.sno and class ='95033'
9、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select *
from score
where cno ="3-105" and degree >(select max(degree) from score where sno ='109')
10、查询score中的同学中分数为非最高分成绩的记录。
-- 方法一
select *
from score
where degree != (select max(degree) from score )
-- 方法二
select *
from score
where degree <> (select max(degree) from score )
11、假设使用如下命令建立了一个grade表:
create table grade(low NUMERIC(10, 1),upp NUMERIC(10, 1),rank char(1));
insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
insert into grade values(70,79,'C');
insert into grade values(60,69,'D');
insert into grade values(0,59,'E');
现查询所有同学的Sno、Cno和rank列。
方法一:
select sno,cno,degree,rank
from score ,grade
where degree between low and upp;
-- 方法二:
select sno,cno,degree,rank
from score inner join grade
on degree between low and upp;
-- 方法三:
select sno,cno,degree,rank
from score join grade
on degree between low and upp;
-- 方法四:
select sno,cno,degree,rank
from score join grade
on degree >= low and degree <= upp;
-- 方法五:
select sno,cno,degree,rank
from score,grade
where degree >= low and degree <= upp;
-- 方法六:
select sno,cno,degree,rank
from score inner join grade
on degree >= low and degree <= upp;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· C++代码改造为UTF-8编码问题的总结
· DeepSeek 解答了困扰我五年的技术问题
· 为什么说在企业级应用开发中,后端往往是效率杀手?
· 用 C# 插值字符串处理器写一个 sscanf
· Java 中堆内存和栈内存上的数据分布和特点
· 为DeepSeek添加本地知识库
· 精选4款基于.NET开源、功能强大的通讯调试工具
· DeepSeek智能编程
· 大模型工具KTransformer的安装
· [计算机/硬件/GPU] 显卡