MySQL elementary exercises and questions

1. Query students with English scores between 80-90.

SELECT stu_id
FROM exam
WHERE score >=80 AND score <=90 
AND sub_id = (
    SELECT subject_ id FROM `subject` WHERE subject_ Name = 'English'
)

2. Query students with math scores of 89, 90 and 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 = 'Math'
    )
)

3. Check the scores of all students surnamed Li.

SELECT * FROM exam WHERE  stu_id IN (
    SELECT stu_ No from student where stuname like "Li%"
)

4. Query the information of students with Java > 80 and HTML score > 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. Sort the math scores and output them.

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. Sort the total score and output it, and then output it from high to low

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. Sort and output the math scores of students surnamed Li

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 'Li%' 
AND e.`sub_id`=
        (
           SELECT subject_id FROM `subject` WHERE subject_name='JAVA'
        )

ORDER BY e.`score`;

8. Display all information of students and grade information

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

9. Show all students’ exam information (including students, examination courses and score information.) Three meter connection

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. How many students have statistical mathematics scores greater than 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'