pta mysql 题集（31-50）

10-31 sql-sample

​
select b1.stu_id,b1.stu_name,b1.class_name,b1.score
from
(
select a3.stu_id,a4.name as stu_name,a2.name as class_name,score
from tb_score as a1,tb_class as a2,tb_student_class as a3,tb_student as a4
where a1.stu_id = a3.stu_id and a3.class_id = a2.id and a4.id = a3.stu_id
) as b1,
(
select name,min(score) as mins
from tb_score as a1,tb_class as a2,tb_student_class as a3
where a1.stu_id = a3.stu_id and a3.class_id = a2.id
group by name
) as b2
where b1.class_name = b2.name and b1.score = b2.mins

​

10-32 查询xsda表中所有女生的记录

select * from xsda
where 性别='女';

10-33 查询xscj表中的学号，姓名，计算机三项信息，结果按计算机成绩的降序排列

select 学号,姓名,计算机 from xscj
order by 计算机 desc;

10-34 查询xscj表中的计算机成绩在80至90之间（包含80和90分）的同学的学号，姓名，计算机三项信息。

select 学号,姓名,计算机 from xscj
where 计算机 between 80 and 90;

10-35 计算xscj表中计算机课程的最高分

select max(计算机) as 计算机最高分 from xscj;

10-36 计算xscj表中计算机课程的最低分

select min(计算机) as 计算机最低分 from xscj;

10-37 计算xscj表中英语课程的平均分

select avg(英语) as 英语平均分 from xscj;

10-38 统计xscj表中计算机课程成绩在90至100之间的人数（包含90和100）

select count(计算机) as 计算机优秀人数 from xscj
where 计算机>=90 and 计算机<=100;

10-39 查询xsda表中的学号、姓名、性别三项信息，结果按照女生优先的顺序显示。

select 学号,姓名,性别 from xsda
order by 性别 desc;

10-40 统计xsda表中男女生的人数

select 性别,count(*) as 人数 from xsda
group by 性别;

10-41 计算xsda表中男女生的平均身高

select 性别,avg(身高) as 平均身高 from xsda
group by 性别;

10-42 计算xsda表中最高同学的身高

select max(身高) as 最高同学的身高 from xsda;

10-43 计算xsda表中最矮同学的身高

select min(身高) as 最矮同学的身高 from xsda;

10-44 查询zgda表中所有女教授的信息

select * from zgda
where 性别='女' and 职称='教授';

10-45 查询zgda表中的工号，姓名，性别，职称4项信息，显示结果时首先按照女性在前的顺序，如果性别相同则按照职称的升序排列。

select 工号,姓名,性别,职称 from zgda
order by 性别 desc,职称 asc;

10-46 sql-update-sample

update Student
set name='test'
where id>50;

10-47 sql-select-sample

select * from Student
where id>50;

10-48 查询学生表所有学生记录

select * from stu;

10-49 查询学生表中部分信息

select sno,sname, (case when sex=1 then '男' else '女'end) as sex 
from stu;

10-50 查询学生表中的女生信息

select sno as 学号,sname as 姓名 from stu
where sex = 0
order by sno;