【无标题】

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