select语句练习题（二）

==========================================================

1、查询1832班的成绩信息
select  math from grade  where  class=1832;

2，查询1833班，语文成绩大于80小于90的成绩信息
select chinese from grade where class=1833 and chinese>80 and chinese<90;

3，查询学生表中5到10行的数据
select * from grade limit 4,6;

4，显示1832班英语成绩为98，数学成绩为77的姓名与学号,
select  name,sid from grade where class=1832 and englishi=98 and math=77;

5，查询出1832班成绩并且按语文成绩排序（降序）
select * from grade where class=1832 order by chinese desc;

6，查询1833班与1832班，语文成绩与数学成绩都小于80的姓名。
select name from grade where chinese<80 and math<80  and class in(1833,1832);

7，查询出没有参加语文考试的学生姓名和班级名称。
select name,class from grade where chinese=0;

8，求出班上语文成绩不及格的学生姓名
select name from grade where chinese<60;

9，求出每个班的数学平均成绩
select avg(math),class,count(*) from grade group by class;

10、求出每个班级语文成绩总分
select sum(chinese),class,count(*) from grade group by class;

11、求出每个班英语成绩最高的那个人的姓名和班级名称
select name,class from grade group by class and english in(select max(english) from grade);

select * from grade;(老师讲解）
select * from grade order by english desc;
select * from(select * from grade order by english desc)a;
select * from(select * from grade order by english desc)a group by class;
select name,class from(select * from grade order by english desc)a group by class;


12、三科分数都大于70分的人名和年纪
select name,age from grade where math>70 and english>70 and chinese>70;

13、求出英语分数高于70且其它任何一科目大于60分的人和班级
select name,class from grade where english>70 and chinese>60 and math>60;

14、统计每个班的人数
select class,count(*) from grade group by class;

15、求每个班数学成绩大于80的人数
select class,count(*) from grade where math>80 group by class;

16、查询姓名为w开头的人的班级姓名年纪
select name,age from grade where name like 'w%';

17、查询三科成绩都及格的班级并显示成绩姓名和年纪
select * from grade where chinese>=60 and english>=60 and math>=60;

18、查询1833班语文成绩及格人数并显示及格成绩姓名，年纪和ID号
select name,age,sid from grade where class=1833 and chinese>=60;
select name,age,sid from grade where class=1833 and chinese between 59 and 101;

19、查询数学分数为88分的班级分数姓名和年纪
select class,math,name,age from grade where math=88;

20、查询英语成绩和数学成绩都不及格的班级成绩姓名年纪
select class,english,math,name,age from grade where english<60 and math<60;

21、查询查询语文成绩和英语成绩都低于60分的班级，并显示成绩姓名和ID号
select chinese,english,name,sid from grade where chinese<60 and english<60;

22、统计1833班语文成绩总和
select sum(chinese),count(*) from grade where class=1833;
select sum(chinese) from grade where class=1833;

23、统计1833班语文成绩大于平均成绩的人的姓名，成绩和ID号
select name,chinese,sid from grade where class=1833 and chinese>( select avg(chinese) from grade where class=1833);

24、查询姓名结尾为n的人，并显示班级成绩姓名
select class,chinese,english,math,name from grade where name like '%n';

25、查询英语成绩分数不为57的班级姓名年纪
select class,name,age from grade where english !=57;

26、给表增加3个字段（数据类型及长度自定义，建议要合理），
alter table grade add wuli int(3) shengwu int(3) huaxue int(3) after math;

27、将语文成绩不及格的学生成绩改为60分
update grade set chinese=60 where chinese<60;