(1)查询所有学生的信息(学号、姓名、性别、班级名称)
select xh,xm,xb,bjmc from xsb x left join bjb b on x.bjb_id = b.id
(2)查询所有人的课程分数(学号、姓名、性别、班级名称、语文分数、数学分数)
select xh,xm,xb,bjmc,yw,sx from xsb x left join bjb b on x.bjb_id = b.id left join cjb c on x.id = c.id
(3)查询语文分数比“张三”的高的学生(学号、姓名、性别、班级名称、语文分数)
select xh,xm,xb,bjmc,yw from xsb x left join bjb b on x.bjb_id = b.id left join cjb c on x.id = c.id where yw > (select yw from xsb x left join cjb c on x.id = c.id where x.xm = '张三')
(4)查询各科都合格【分数>=60分】的学生(学号、姓名、语文分数、数学分数)
select xh,xm,yw,sx from xsb x left join cjb c on x.id = c.id where yw>=60 and sx>=60
(5)查询总分数(语文+数学)>=150的学生信息(学号、姓名、班级名称、总分数)
select xh,xm,bjmc,sum(yw+sx) as 'zfs' from xsb x left join bjb b on x.bjb_id = b.id left join cjb c on x.id = c.id where (yw+sx)>150
(6)查询没有参加考试【没有成绩表】的学生(学号、姓名、性别、班级名称)
select xh,xm,xb,bjmc from xsb x left join bjb b on x.bjb_id = b.id left join cjb c on x.id = c.id where c.id is null
(7)假设分数>=60分合格,分析学生的成绩是否合格(学号、姓名、语文合格情况[合格/不合格]、数学合格情况[合格/不合格])
select xh,xm,if(c.yw >= 60,'合格','不合格') as '语文合格情况',if(c.sx >= 60,'合格','不合格') as '数学合格情况' from bjb b,xsb x,cjb c where x.bjb_id = b.id and x.id = c.id
(8)查询有挂科【分数<60分】现象的学生(学号、姓名、语文分数、数学分数)
select xh,xm,yw,sx from xsb x left join cjb c on x.id = c.id where yw<60 or sx<60
(9)查询所有班级的平均分数(班级编号、班级名称、语文平均分数、数学平均分数)
select b.id,b.bjmc,avg(c.yw),avg(c.sx) from xsb x left join bjb b on x.bjb_id = b.id left join cjb c on x.id = c.id group by b.id
(10)查询班级人数>=30的班级(班级编号、班级名称、人数)
select b.id,b.bjmc,count(x.bjb_id) from xsb x left join bjb b on x.bjb_id = b.id group by x.bjb_id