SQL 基础（七）数据查询实战演练二

2022-06-07 320

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： SQL 基础（七）数据查询实战演练二

实验内容

根据数据库 db_student 中的 tb_student 表、tb_score、tb_course，完成下列查询语句

表结构

表结构如下：

tb_student(sno,sn,dept,sex,birthday,polity)
tb_score(sno,cno,score)
tb_cource(cno,cn,ct)

任务题解

任务一

查询学生的性别信息, 并去掉重复记录。

use db_student20
select distinct sex -- distinct 去重
from tb_student

任务二

查询 tb_student 表中共有几个系别

use db_student20
select COUNT(distinct dept) as 系别总数
from tb_student

任务三

计算 tb_student 表中各个系的人数。

select dept as 系别名称,COUNT(*) as 总人数
from tb_student
group by dept

任务四

查询你所在班级的人数。

-- 方法一
select count(*) as 总人数
from tb_student
where sno like 'XXXXXXX%'
-- 方法二
select count(sno) 总人数
from tb_student
where LEFT(sno,8)='XXXXXXXX'

任务五

显示每名同学的成绩，要求显示学号、姓名、课号和成绩

select stu.sno as 学号,sn as 姓名,cno 课程号,score 成绩
from tb_student stu,tb_score sco -- 同一学生成绩信息,避免冲突,相同字段重命名表 引用
where stu.sno=sco.sno

任务六

显示 90 分以上每名同学的成绩，要求显示学号、姓名、课号和成绩

select stu.sno as 学号,sn as 姓名,cno 课程号,score 成绩
from tb_student stu,tb_score sco
where stu.sno=sco.sno and score > 90  -- 添加成绩约束条件

任务七

每科成绩最高最低分，要求显示科目名称。

select cn 课程名,MAX(score) as 最高分,MIN(score) as 最低分
from tb_course co,tb_score sc
where co.cno=sc.cno
group by cn

任务八

每科有多少人需要补考，要求显示科目名称。

select cn 课程名,COUNT(*) as 补考人数
from tb_course c,tb_score sc
where c.cno=sc.cno and score<60 -- cno 课程编号
group by cn

任务九

每班计算机基础科成绩最高、最低分。

select LEFT(sno,8) as 班级,cn 课程名,MAX(score) 最高分,MIN(score) 最低分
from tb_course c,tb_score sc
where c.cno=sc.cno 
group by LEFT(sno,8),cn
having cn='MATLAB与系统仿真' -- 选中基础课名称

任务十

查询所有学生的选课情况，包括没选课的学生（使用左连接、右连接 2 种方法实现）

--left join
select * 
from tb_student s left join tb_score sc
on(s.sno=sc.sno)
--right join
select * 
from tb_score sc right join tb_student s
on(sc.sno=s.sno)

任务十一

列出 tb_student 表中和“XXX”系别相同的学生的学号和姓名

--子查询
select sno 学号,sn 姓名
from tb_student
where dept =any(select dept from tb_student where sn='XXX')--'XXX 系别'
--自身连接
select s1.sno,s1.sn 
from tb_student s1,tb_student s2
where s1.dept=s2.dept and s2.sn='XXX'

任务十二

查询选修 c03 课程的学生姓名

--子查询
select sn from tb_student
where sno in(select sno from tb_score where cno='c03')
--连接
select sn 
from tb_student s,tb_score sc
where s.sno=sc.sno and cno='c03'

任务十三

查询不选 c04 课程的学生的学号和姓名

select sno,sn 
from tb_student
where sno not in(select sno from tb_score where cno='c04')

任务十四

查询每科最高分的学生的学号和科目

select sno 学号,cn 课程名,score 成绩
from tb_score sc,tb_course c,
(
select cno, MAX(score) as 最高分
from tb_score
group by cno
) as b
where sc.cno=c.cno and c.cno=b.cno and score=b.最高分

任务十五

显示需要进行补考的科目编号和名称

-- 子查询
select cno,cn from tb_course
where cno in(select distinct cno from tb_score where score<60)
-- 连接
select distinct c.cno,cn 
from tb_course c,tb_score sc
where c.cno=sc.cno and score<60

任务十六

不及格人数多于 1 人的科目及人数。

select cn,COUNT(*)as 不及格人数
from tb_course c,tb_score sc
where c.cno=sc.cno and score<60
group by cn
having COUNT(*)>1

任务十七

显示 c01 科成绩不及格的学号，对应学生表中所有列的信息。

select * from tb_student
where sno in(select sno from tb_score where cno='c03'and score<60)

任务十八

列出成绩表中各个科目成绩的最高分、最低分和平均分, 并显示成绩表中平均分大于 75 分的科目名称，并按平均分降序排序

select cn,MAX(score) 最高分,MIN(score) 最低分,AVG(score) 平均分
from tb_course c,tb_score sc
where c.cno=sc.cno
group by cn
having AVG(score)>75
order by 平均分 desc

任务十九

查询选修了“数据库原理与应用”的学生的学号和姓名。

-- 子查询
select sno,sn from tb_student
where sno in
(
select sno from tb_score
where cno=(select cno from tb_course where cn='数据库原理与应用')
)
select sno,sn from tb_student
where sno in
(
select sno from tb_score sc,tb_course c
where sc.cno=c.cno and cn='数据库原理与应用'
)
-- 连接
select sn,sn from tb_student s,tb_score sc,tb_course c
where s.sno=sc.sno and sc.cno=c.cno
and cn='数据库原理与应用'

任务二十

査询课程号为“C02”、成绩高于“王磊”的学生的学号和成绩。

select sno,score from tb_score
where cno='c03'and score>
(
select score 
from tb_score sc,tb_student s
where sc.sno=s.sno and cno='c03'
and sn='XXX'
-- 多层子查询
select sno,score from tb_score
where cno='c03'and score>
(
select score from tb_score
where cno='c03'and 
sno=(select sno from tb_student
where sn='XXX' )
)
)

任务二十一

查询和“王磊”的“数据库原理及应用”课程分数相同的学生的学号。

select distinct sno from tb_score sc,tb_course c
where sc.cno=c.cno and cn='数据库原理及应用'
and score=
(
select score from tb_score 
where sno=
(
select sno from tb_student
where sn='XXX'
)and cno=(select cno from tb_course where cn='数据库原理及应用')
)
select distinct sno from tb_score 
where score=
(
select score from tb_score 
where sno=
(
select sno from tb_student
where sn='XXX'
)
and cno=(select cno from tb_course where cn='数据库原理及应用')
)