查询总分数(语文+数学)>=150的学生信息(学号、姓名、班级名称、总分数)
SELECT x.xh,x.xm,b.bjmc,SUM(c.yw+c.sx) AS 总分数
FROM xsb X,cjb c,bjb b
WHERE x.bjb_id=b.id AND x.id=c.id
GROUP BY c.id
HAVING SUM(c.yw+c.sx)>150
查询没有参加考试【没有成绩表】的学生(学号、姓名、性别、班级名称)
SELECT x.xh,x.xm,b.bjmc
FROM xsb X,bjb b
WHERE b.id=x.bjb_id
AND x.id NOT IN (SELECT id FROM cjb)
假设分数>=60分合格,分析学生的成绩是否合格(学号、姓名、语文合格情况[合格/不合格]、数学合格情况[合格/不合格])
SELECT x.xh,x.xm,
IF(c.yw>=60,'合格','不合格') AS 合格情况,
IF(c.sx>=60,'合格','不合格') AS 合格情况
FROM xsb X ,cjb c
WHERE x.id=c.id
查询有挂科【分数<60分】现象的学生(学号、姓名、语文分数、数学分数)
SELECT x.xh,x.xm,c.yw,c.sx
FROM xsb X,cjb c
WHERE x.id=c.id AND c.yw<60 OR c.sx<60
GROUP BY c.id
查询所有班级的平均分数(班级编号、班级名称、语文平均分数、数学平均分数)
SELECT x.xm,b.bjmc,AVG(c.yw),AVG(c.sx)
FROM bjb b,xsb X,cjb c
WHERE x.bjb_id=b.id AND c.id=x.id
GROUP BY b.id
查询班级人数>=2的班级(班级编号、班级名称、人数)
SELECT b.id,b.bjmc,COUNT(b.id) AS rs
FROM bjb b,xsb X
WHERE b.id=x.bjb_id
GROUP BY b.id,b.bjmc
HAVING COUNT(b.id)>=2