Mysql题目集（21-40）

10-21 查询学生表中大于19岁的女生

select sno as 学号,sname as 姓名,sex as 性别,mno as 专业,(2020-year(birdate)) as 年龄,memo as 备注
from stu
where (2020-year(birdate)) > 19 and sex = 0

10-22 查询年龄18-20之间的学生信息

select sno as 学号,sname as 姓名,sex as 性别,mno as 专业,(2020-year(birdate)) as 年龄,memo as 备注
from stu
where (2020-year(birdate)) between 18 and 20

10-23 查询姓‘李’的学生记录

select sno as 学号,sname as 姓名,sex as 性别,mno as 专业,birdate as 出生日期,memo as 备注
from stu
where sname like '李%';

10-24 查询部分专业的学生

select sno as 学号,sname as 姓名,sex as 性别,major.mname as 专业
from stu 
inner join major on major.mno = stu.mno
where major.mname = '计算机工程' or major.mname = '软件工程'
order by 学号 asc;

10-25 查询所有学生的平均成绩

select stu.sno as 学号,avg(ifnull(sc.grade,0)) as 平均成绩
from stu
left join sc on stu.sno=sc.sno
group by stu.sno;

10-26 查询各专业学生的平均成绩

select op.a as 专业,op.b as 平均成绩
from major,(
select major.mname as a,ifnull(avg(grade),0) as b
from major
left join stu ON major.mno=stu.mno
left join sc ON stu.sno=sc.sno
group by major.mname
)as op
where op.a = major.mname
order by major.mno;

10-27 查询平均成绩高于75分的学生

select sc.sno as 学号, avg(grade) as 平均成绩
from sc
group by sno
having avg(grade) > 75
order by 学号;

10-28 查询未登记成绩的学生

select sno
from sc
where grade is null;

10-29 查询选修‘C语言’课程的学生

select stu.sname as 姓名,sc.grade as 成绩
from sc
inner join cou on cou.cno=sc.cno
inner join stu on stu.sno=sc.sno
where cname = 'C语言'
order by 成绩 desc;

10-30 查询没有选修'C语言'课程的学生

select sno as 学号,sname as 姓名
from stu
where sno not in
(
    select sno 
    from cou,sc 
    where sc.cno =cou.cno and cname = 'C语言' and stu.sno = sc.sno
);

10-31 查询同专业的学生

select sno as 学号,sname as 姓名
from stu
where mno = (select mno from stu where sname='张三') and sname != '张三'
order by 学号;

10-32 查询学生成绩及汇总其总学分

select 
sc.cno as 课程号,cou.cname as 课程名,grade as 成绩,credit as 学分
from sc
left join cou on sc.cno = cou.cno
where sno = (select sno from stu where sname = '张三') and grade >= 60
union
select '张三','所有及格课程','合计总学分',SUM(lwf.money)
from
(select sc.cno ,cou.cname ,grade ,credit as money
from sc
left join cou on sc.cno = cou.cno
where sno = (select sno from stu where sname = '张三') and grade >= 60) as lwf
order by 课程号;

10-33 查询选修某两门课程的学生

select distinct sno as 学号
from sc
where 
sno in(select sno from sc where cno = 'C001') and 
sno in(select sno from sc where cno =  'C002');

10-34 查询S001学生选修而S003学生未选修的课程

select cno as 课程号
from sc
where sno ='S001' and cno not in (select cno from sc where sno = 'S003');

10-35 查询学生选修的课程

select cno as 课程号,cname as 课程
from cou
where cno in
(
    select distinct cno
    from sc
    where sno = 'S001' or sno = 'S003'
)
order by cno asc

10-36 查询平均成绩以上的课程

select sno as 学号,cou.cname as 课程名,sc.grade as 成绩 
from sc,cou 
where sc.cno=cou.cno and sc.grade >
(
    select score 
    from (select sno,avg(grade) as score from sc group by sno) as op
    where op.sno = sc.sno
);

10-37 查询平均分高于80分的学生

select sname
from stu,sc
where sc.sno = stu.sno
group by sc.sno
having avg(grade)>80;

10-38 查询平均分高于60分的课程

select cno as 课程号,cname as 课程名
from cou
where
cno in
(
    select cno 
    from sc
    group by cno
    having avg(grade)>60
);

10-39 查询成绩最高的前三名同学

select  stu.sname as 姓名,sc.grade as 成绩
from sc,stu
where 
sc.sno = stu.sno and sc.cno=(select cno from cou where cname='C语言')
order by grade desc
limit 3;

10-40 查询平均成绩最高的前3名同学

select  stu.sno as 学号,sname as 姓名,sex as 性别,year("2020-03-01")-year(birdate) as 年龄, score as 平均成绩
from stu ,(select sno ,avg(grade) as score from sc group by sno) as b1
where b1.sno = stu.sno
order by 平均成绩 desc 
limit 0,3;