根据数据库
db_student
中的tb_student
表、tb_score
、tb_course
,完成下列查询语句
表结构如下:
查询学生的性别信息, 并去掉重复记录。
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='数据库原理及应用')
)