关于mysql: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'