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

发表于 分类于 阅读次数： 本文字数： 4.2k 阅读时长 ≈ 4 分钟

实验内容

根据数据库 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)

    任务题解

    任务一

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

1
2
3
use db_student20
select distinct sex -- distinct 去重
from tb_student

任务二

查询 tb_student 表中共有几个系别

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

任务三

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

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

任务四

查询你所在班级的人数。

1
2
3
4
5
6
7
8
9
-- 方法一
select count(*) as 总人数
from tb_student
where sno like 'XXXXXXX%'

-- 方法二
select count(sno) 总人数
from tb_student
where LEFT(sno,8)='XXXXXXXX'

任务五

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

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

任务六

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

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

任务七

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

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

任务八

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

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

任务九

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

1
2
3
4
5
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 种方法实现）

1
2
3
4
5
6
7
8
9
--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”系别相同的学生的学号和姓名

1
2
3
4
5
6
7
8
9
--子查询
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 课程的学生姓名

1
2
3
4
5
6
7
8
--子查询
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 课程的学生的学号和姓名

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

任务十四

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

1
2
3
4
5
6
7
8
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.最高分

任务十五

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

1
2
3
4
5
6
7
-- 子查询
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 人的科目及人数。

1
2
3
4
5
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 科成绩不及格的学号，对应学生表中所有列的信息。

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

任务十八

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

1
2
3
4
5
6
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

任务十九

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 子查询
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”、成绩高于“王磊”的学生的学号和成绩。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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' )
)
)

任务二十一

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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='数据库原理及应用')
)