Mysql初级练习题及答案

最新推荐文章于 2023-12-17 22:27:22 发布

啥都会一点的混血猿

最新推荐文章于 2023-12-17 22:27:22 发布

阅读量292

点赞数

分类专栏：在开发中探索文章标签： sql mysql

本文链接：https://blog.csdn.net/tanjianjun123456/article/details/120669151

版权

在开发中探索专栏收录该内容

11 篇文章 0 订阅

订阅专栏

本文介绍了多个SQL查询示例，涉及筛选特定分数范围内的学生、按科目分组查询、按姓名筛选、多条件组合查询、成绩排序以及统计高分学生数量等。这些查询涵盖了数据过滤、分组、联接和聚合函数的使用，展示了数据库操作的基本技巧。

摘要由CSDN通过智能技术生成

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'