R10-2 检索出年龄大于23岁的男同学的基本信息,查询结果按学号升序排列。
select *
from student
where timestampdiff(YEAR,birth,"2020-12-01")>=23
and sex='男'
order by sno
R10-1 查询各专业的学生人数
select major.mno 专业号,major.mname 专业,
count(stu.sno) 人数
from major
left join stu on stu.mno=major.mno
group by major.mno
R10-3 查询“0000008”号课程不及格的学生信息
select *
from students
where sno in (
select sno
from sc
where score<60 and cno=0000008
)
R10-5 删除选修C语言课程的女生成绩记录
delete from sc
where sno in(
select sno
from stu
where sex = 0
)and cno in(
select cno
from cou
where cname = 'C语言'
)
R10-6 查询信息学院学生所选修的课程号和成绩
select cno,score
from sc
where sno in(
select sno
from students
where sdept='信息学院'
)
R10-7 查询“陈晓东”同学所选课程的课号及成绩
select cno,score
from sc
where sno in(
select sno
from students
where sname='陈晓东'
)
R10-8 添加成绩等级rank字段
update sc set rank = case
when sc.grade <60 then 'E'
when sc.grade >=60 and sc.grade <70 then 'D'
when sc.grade >=70 and sc.grade<80 then 'C'
when sc.grade >=80 and sc.grade<90 then 'B'
when sc.grade >=90 then 'A'
else NULL
end
R10-9 查询xsda表中的学号、姓名、性别三项信息,结果按照女生优先的顺序显示。
select 学号,姓名,性别
from xsda
order by 性别 desc
R10-10 统计不及格人数超过3人的课程号和人数
SELECT cno 课程号,COUNT(score) 不及格人数
FROM sc WHERE score<60.0
GROUP BY cno
HAVING COUNT(score) >3;
R10-11 统计选修人数最多的3门课
SELECT cno '课程号',COUNT(sno) '选修人数'
FROM sc
GROUP BY cno
ORDER BY COUNT(sno) DESC
LIMIT 3