1.查问英语分数在 80－90之间的同学。

SELECT stu_idFROM examWHERE score >=80 AND score <=90 AND sub_id = (    SELECT subject_id FROM `subject` WHERE subject_name = '英语')

2.查问数学分数为89,90,91的同学。

SELECT * FROM student WHERE stu_no IN(    SELECT stu_id FROM exam WHERE  score  IN(89,90,91)     AND  sub_id = (        SELECT subject_id FROM `subject` WHERE subject_name = '数学'    ))

3.查问所有姓李的学生问题。

SELECT * FROM exam WHERE  stu_id IN (    SELECT stu_no FROM student WHERE stuname LIKE "李%")

4.查问数JAVA>80 并且 HTML分>80的同学的信息。

SELECT stu_id , COUNT(*) FROM exam e , `subject` s WHERE e.sub_id= s.subject_id AND (    (e.score >= 80 AND s.subject_name='JAVA')       OR    (e.score >= 90 AND s.subject_name='HTML'))GROUP BY stu_id HAVING COUNT(*) = 2

5.对数学问题排序后输入。

SELECT s.stu_name,e.scoreFROM student s INNER JOIN exam e ON s.student_no=e.stu_id INNERJOIN `subject` k ON e.sub_id=k.subject_id WHERE `subject_name`='JAVA' ORDER BY e.score ASC;

6.对总分排序后输入，而后再按从高到低的程序输入

SELECT s.stu_name , SUM(e.score)FROM student s INNER JOIN exam e ON s.student_no=e.stu_id  GROUP BY e.stu_id ORDER BY SUM(e.score) ASC;

7.对姓李的学生数学问题排序输入.

SELECT s.stu_name , e.scoreFROM student s INNER JOIN exam e ON s.student_no=e.stu_id WHERE s.stu_name LIKE '李%' AND e.`sub_id`=        (           SELECT subject_id FROM `subject` WHERE subject_name='JAVA'        )ORDER BY e.`score`;

8.展现学生所有信息与所属年级信息.

SELECT *FROM student s INNER JOIN grade g ON s.gradeid=g.gradeid

9.展现所有学生的考试信息.(包含学生,考试课程,问题信息.) 三表内连贯.

SELECT *FROM student s INNER JOIN exam e ON s.student_no=e.stu_id INNER JOIN `subject` sub ON e.sub_id=sub.subject_idINNER JOIN grade g ON s.`gradeid`=g.`gradeid`

10.统计数学问题大于90的学生有多少个？

SELECT COUNT(*)FROM student s INNER JOIN exam e ON s.student_no=e.stu_id INNER JOIN `subject` sub ON e.sub_id=sub.subject_idWHERE e.score>90 AND sub.subject_name='JAVA'