pta mysql训练题集(301-320)

10-301 统计每门课程的选课人数和最高分

select cno 课程号,count(*) 选课人数,max(score) 最高分
from sc
group by cno

10-302 查询选修了3门以上课程的学生学号

select sno 学号,count(*) 选课门数
from sc
group by sno
having count(*)>3;

10-303 查询至少选修一门课程的学生学号

select sno
from sc
group by sno
having count(cno)>0;

10-304 统计输出各系学生的人数

select sdept as 系,count(sno) as 人数
from students
group by sdept;

10-305 查询选修了“0000008”课程的学生的学号及其成绩，查询结果按分数降序排列

select sno,score
from sc
where cno='0000008'
order by score desc;

10-306 查询成绩不及格的学生学号、课号和成绩，并按成绩降序排列

select sno,cno,score
from sc
where score<60
order by score desc;

10-307 统计每个学生的选课门数和考试总成绩，并按选课门数升序排列

select sno as 学号,count(*) as 选课门数,sum(score) as 考试总成绩
from sc
group by sno

10-308 统计各系的男、女生人数

select sdept 系别,ssex 性别,count(*) as 人数
from students
group by sdept,ssex;

10-309 统计各班男、女生人数

select class 班级,ssex 性别,count(*) as 人数
from students
group by class,ssex;

10-310 统计各系的老师人数，并按人数升序排序

select tdept as 系别,count(*) as 教师人数
from teachers
group by tdept
order by 教师人数;

10-311 统计不及格人数超过3人的课程号和人数

select cno as 课程号,count(*) as 不及格人数
from sc
where score<60
group by cno
having count(*)>3;

10-312 查询信息学院的男生信息，查询结果按出生日期升序排序，出生日期相同的按生源地降序排序

select *
from students
where sdept='信息学院' and ssex='男'
order by bday,bplace desc;

10-313 统计选修人数最多的3门课

select *
from
(
    select cno 课程号,count(*) 选修人数
    from sc
    group by cno
    order by 选修人数 desc
)as a
limit 3;

10-314 查询信息学院女学生的学生学号、姓名、课号及考试成绩

select a.sno,sname,cno,score
from students as a,sc
where a.sno = sc.sno and sdept='信息学院' and ssex='女';

10-315 查询“陈红”同学所选课程的成绩，列出课号和成绩(不考虑重名)

select cno,score
from students
inner join sc on students.sno=sc.sno
where sname='陈红' ;

10-316 查询“王珊”老师所授课程的课程名称

select distinct cname
from course 
inner join teaching on teaching.cno=course.cno
inner join teachers on teachers.tno=teaching.tno
where tname='王珊';

10-317 查询女教师所授课程的课程号和课程名称

select distinct course.cno,cname
from course 
inner join teaching on teaching.cno = course.cno
inner join teachers on teachers.tno = teaching.tno
where tsex='女';

10-318 查询至少选修2门课程的女生姓名

select distinct sname
from students 
inner join sc on sc.sno=students.sno
where ssex='女'
group by sname 
having count(*)>=2;

10-319 查询选修课名中含有“数据库”三个字的课程且成绩在80～90分之间的学生学号及成绩

select sno,score
from sc
where cno in
(
select cno
from course
where cname like '%数据库%'
)
and score between 80 and 90

10-320 查询选修“0000011”课程的学生至2050年时平均年龄

select avg(2050-year(bday)) as 平均年龄
from students 
where sno in (select sno from sc where cno = '0000011');