一次笔试题目附答案（sql答卷）

已知以下表结构（括号内为表名和字段名，用SQL写出以下题目答案）：

 

业务情景限制：

1、  一个班级有且有多名学生，一名学生只属于一个班级；

2、  学生有可能没有成绩；

考题：

（1）       查询所有学生的信息（学号、姓名、性别、班级名称）

SELECT  x.xh,x.xm,x.xb,b.bjmc  FROM bjb b,xsb x WHERE x.bjb_id = b.id

（2）       查询所有人的课程分数（学号、姓名、性别、班级名称、语文分数、数学分数）

SELECT x.xh,x.xm,x.xb,b.bjmc,c.yw,c.sx  FROM bjb b,xsb x,cjb c WHERE x.bjb_id = b.id AND x.id = c.id

 （3）       查询语文分数比“张三”的高的学生（学号、姓名、性别、班级名称、语文分数）

SELECT x.xh,x.xm,x.xb,b.bjmc,c.yw  FROM bjb b,xsb x,cjb c WHERE x.bjb_id = b.id AND x.id = c.id AND c.yw > (SELECT c.yw FROM  xsb x,cjb c  WHERE  x.id = c.id AND x.xm='张三' )

 （4）       查询各科都合格【分数>=60分】的学生（学号、姓名、语文分数、数学分数）

SELECT x.xh,x.xm,c.yw,c.sx  FROM bjb b,xsb x,cjb c WHERE x.bjb_id = b.id AND x.id = c.id AND c.yw >= 60 AND c.sx >= 60 GROUP BY x.xh

 （5）       查询总分数（语文+数学）>=150的学生信息（学号、姓名、班级名称、总分数）

SELECT x.xh,x.xm,b.bjmc,sum(c.yw+c.sx) AS zf  FROM bjb b,xsb x,cjb c WHERE x.bjb_id = b.id AND x.id = c.id AND (c.yw + c.sx) >= 150 group by x.xh

 （6）       查询没有参加考试【没有成绩表】的学生（学号、姓名、性别、班级名称）

select x.xh,x.xm,x.xb,b.bjmc from bjb b ,xsb x ,cjb c where x.bjb_id=b.id and x.id not in (select id from cjb) group by x.xh,x.xm,x.xb,b.bjmc

 （7）       假设分数>=60分合格，分析学生的成绩是否合格

（学号、姓名、语文合格情况[合格/不合格]、数学合格情况[合格/不合格]）

SELECT x.xh,x.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 x.xh,x.xm,c.yw,c.sx from bjb b ,xsb x ,cjb c where x.bjb_id=b.id and x.id=c.id and (c.yw < 60 or c.sx < 60 )

 （9）       查询所有班级的平均分数（班级编号、班级名称、语文平均分数、数学平均分数）

select b.id,b.bjmc,AVG(c.yw) AS ywpjf,AVG(c.sx) AS sxpjf from bjb b ,xsb x, cjb c where c.id=x.id and b.id=x.bjb_id  group by b.id

 （10）   查询班级人数>=30的班级（班级编号、班级名称、人数）

select b.id,b.bjmc,count(c.id) AS rs from bjb b ,xsb x, cjb c where c.id=x.id and b.id=x.bjb_id  group by b.id,b.bjmc having count(c.id) >=30

本例子是通过mysql测试通过，如果用oracle请自行修改！！