mysql填空题与答案_MySQL50道练习题与答案

create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));

insert into SC values('01' , '01' , 80);

insert into SC values('01' , '02' , 90);

insert into SC values('01' , '03' , 99);

insert into SC values('02' , '01' , 70);

insert into SC values('02' , '02' , 60);

insert into SC values('02' , '03' , 80);

insert into SC values('03' , '01' , 80);

insert into SC values('03' , '02' , 80);

insert into SC values('03' , '03' , 80);

insert into SC values('04' , '01' , 50);

insert into SC values('04' , '02' , 30);

insert into SC values('04' , '03' , 20);

insert into SC values('05' , '01' , 76);

insert into SC values('05' , '02' , 87);

insert into SC values('06' , '01' , 31);

insert into SC values('06' , '03' , 34);

insert into SC values('07' , '02' , 89);

insert into SC values('07' , '03' , 98);

#1.1查询01课程比02课程成绩高的学生信息及课程分数 01 02都存在select student.*,

max(case when sc.cid='01' then score end) as C1,

max(case when sc.cid='02' then score end)as C2

from student left join sc

on student.sid=sc.sid

group by sid

having c1 is not null

and c2 is not null

and c1>c2;

select * from student right join(

select t1.sid, C1,C2 from

(select sid,score as C1 from sc where sc.cid='01') as t1,

(select sid,score as C2 from sc where sc.cid='02') as t2

where t1.sid=t2.sid and t1.c1>t2.c2

)r

on student.sid=r.sid;

#1.2查询01课程比02课程成绩高的学生信息及课程分数 01存在 02可能不存在select student.*,

max(case when cid='01' then score end) as C1,

max(case when cid='02' then score end) as C2

from student left join sc

on student.sid=sc.sid

group by sid

having c1 is not null

and c1>c2

or c2 is null;

#1.3查询01课程分数不存在但02存在select student.*,

max(case when cid='01' then score end) as C1,

max(case when cid='02' then score end) as C2

from student left join sc

on student.sid=sc.sid

group by sid

having c1 is null

and c2 is not null;

##2.查询平均成绩大于等于60的同学的学生编号和学生姓名和平均成绩select Sname,SId,(select avg(score)

from sc

where sc.SId=student.SId

group by SId

having AVG(score)>=60)as avg_score

from student;

##3.查询SC表存在成绩的