mysql初级练习题以及题目

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

SELECT stu_id
FROM exam
WHERE 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.score
FROM student s INNER JOIN exam e ON s.student_no=e.stu_id INNER
JOIN `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.score
FROM 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_id
INNER 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_id
WHERE e.score>90 AND sub.subject_name='JAVA'