• SQL查询面试题，会这些基本够用了

    写在前面

    我已经记不起来，有多久没更新文章了。

    5月中旬我还在上班，中旬以后一系列发生的事情，真的远远超出了可承受范围，只能硬着头皮面对！

    我是谁，我应该是谁，又能怎样，只能向前·····

    数据库实例

    class表

    image.png

    course表

    image.png

    score表

    image.png

    student表

    image.png

    teacher表

    image.png

    实际语句

    1、查询所有的课程的名称以及对应的任课老师姓名

    sql
    # 自链接  
SELECT c.name,t.name FROM course c,teacher t WHERE c.teacher_id=t.id  
# 内连接  
SELECT course.name,teacher.name FROM course  
INNER JOIN teacher ON course.teacher_id=teacher.id

    结果：

    image.png

    2、查询学习课程"数据结构"比课程"java语言"成绩低的学生的学号；

    sql
    # 内连接  
SELECT shuju.student_id FROM  
(SELECT score.course_id,  
score.student_id,  
score.mark  
FROM score  
INNER JOIN course  
ON score.course_id=course.id  
WHERE course.name='数据结构') AS shuju  
INNER JOIN  
(SELECT score.course_id,  
score.student_id,  
score.mark  
FROM score  
INNER JOIN course  
ON score.course_id=course.id  
WHERE course.name='java') AS java  
ON shuju.student_id=java.student_id  
WHERE shuju.mark<java.mark  
  
# 自连接  
SELECT shuju.student_id  
FROM  
(SELECT s.course_id,  
s.student_id,  
s.mark  
FROM score s, course c  
WHERE c.`name`='数据结构'  
AND s.course_id=c.id) shuju,  
(SELECT s.course_id,  
s.student_id,  
s.mark  
FROM score s, course c  
WHERE c.`name`='java'  
AND s.course_id=c.id) java  
WHERE shuju.student_id=java.student_id  
AND shuju.mark<java.mark

    结果：

    image.png

    3、查询平均成绩大于65分的同学的id和平均成绩(保留两位小数)

    sql
    SELECT score.student_id,  
round(AVG(score.mark),2) AS avgScore  
FROM score  
GROUP BY score.student_id  
HAVING avgScore>65

    结果：

    image.png

    4、查询平均成绩大于65分的同学的姓名和平均成绩(保留两位小数)

    sql
    SELECT student.`name`,  
ROUND(AVG(score.mark),2) AS avgScore  
FROM score  
INNER JOIN student  
ON student.id=score.student_id  
GROUP BY score.student_id  
HAVING avgScore>65

    结果：

    image.png

    5、查询所有同学的姓名、选课数、总成绩

    sql
    SELECT student.name AS '名字', COUNT(score.course_id) AS '选课数',SUM(score.mark) AS '总成绩'FROM score
INNER JOIN student
ON student.id=score.student_id
GROUP BY  student_id

    结果：

    image.png

    6、查询没学过"大牛"老师课的同学的姓名

    sql
    select student.name from student  
where id not in(select student_id from score where course_id in(select course.id from course inner join teacher  
on course.teacher_id = teacher.id where teacher.name='大牛'))

    结果：

    image.png

    7、查询学过"大牛"老师所教的全部课程的同学的姓名

    sql
    select student.name from student  
where id in(select student_id from score where course_id in(3,3))

    结果：

    image.png

    8、查询所有课程成绩小于60分的同学的姓名

    sql
    select student.name from student inner join score on student.id = score.student_id  
where score.mark<60 group by score.student_id

    结果：

    image.png

    9、查询选修了全部课程的学生姓名

    sql
    select student.name from student  
where id in (select score.student_id from score group by score.student_id having count(1)=(select count(1) from course))

    结果：

    image.png

    10、查询至少有一门课程与"小草"同学所学课程相同的同学姓名

    sql
    SELECT student.name
FROM student
WHERE id IN 
    (SELECT student_id
    FROM score
    WHERE course_id IN 
        (SELECT course_id
        FROM score
        WHERE student_id=5))
            AND student.name!='小草'

    结果：

    image.png

    11、查询至少有一门课程和"小草"同学所学课程不相同的同学姓名

    sql
    select student.name from student  
where id in (select student_id from score  
where course_id not in (select course_id from score  
where student_id=5)) and student.name!='小草'

    结果：

    image.png

    12、查询各科成绩最高和最低的分：以如下形式显示：课程id，最高分，最低分

    sql
    select course_id as '课程id',max(mark) as '最高分',min(mark) as '最低分'from score group by course_id

    结果：

    image.png

    13、查询只选修了一门课程的学生的学号和姓名

    sql
    # 感觉有点low，但是能查出来  
select student.id as '学号',student.name as '姓名'from student inner join score on student.id = score.student_id  
where student.id=(select student_id from score group by student_id having count(course_id)=1)

# 这个好一些  
select student.id as '学号',student.name as '姓名'from student inner join score on student.id = score.student_id  
group by student_id having count(course_id)=1

    结果：

    image.png

    14、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程id降序排列

    sql
    select course.id as '课程id',course.name AS "课程名称",avg(mark) as "平均成绩" from course  
inner JOIN score on course.id = score.course_id  
group by course_id order by avg(mark) ,"平均成绩",course_id desc

    结果：

    image.png

    15、按平均成绩倒序显示所有学生的"数据库原理"、“java语言”、"C语言"三门的课程成绩，

    按如下形式显示： 学生id、数据库原理、java语言、C语言、课程数、平均分；(高级应用较难)

    sql
    select sc.student_id as '学生id',  
(select mark from score inner join course on course.id=score.course_id where course.name='数据库原理' and score.student_id=sc.student_id) as '数据库原理',  
(select mark from score inner join course on course.id=score.course_id where course.name='java' and score.student_id=sc.student_id) as 'java',  
(select mark from score inner join course on course.id=score.course_id where course.name='C语言' and score.student_id=sc.student_id) as 'C语言',  
count(course_id) as '课程数',  
round(avg(sc.mark),2) as '平均分'  
from score as sc group by sc.student_id  
order by avg(sc.mark) desc

    结果：

    image.png

    写在最后

    整个数据库这部分的复习，早在近一个月前就开始了。

    在做了两道题后，就遇到了各种事情，就被搁置了，差点被遗忘了。。。

    今天有时间，接着把学习的感觉续上，总体下来，算是初步复习了下sql的一些常用查询操作，就一个测试仔来说，我个人感觉这些都能写正确写出来，真的很厉害，我也是用了近6小时呢。

    不管遇到了什么难事，学习、跑步都不能停（我又胖了5斤，好扎心）.....

    明天继续我的5公里，加油！


    __EOF__

  • 本文作者： 久曲健的测试窝
  • 本文链接： https://www.cnblogs.com/longronglang/p/17483257.html
  • 关于博主： 评论和私信会在第一时间回复。或者直接私信我。
  • 版权声明： 本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
  • 声援博主： 如果您觉得文章对您有帮助，可以点击文章右下角【推荐】一下。
  • 相关阅读:
    移动端H5封装一个 ScrollList 横向滚动列表组件，实现向左滑动
    Kotlin File.reader BufferedReader readLine
    【JavaScript-循环-js你学懂了吗？】
    测开 - 测试管理工具禅道篇 - 细节狂魔
    NeuralProphet之一：安装与使用
    大模型狂潮背后：AI基础设施的“老化”与改造工程
    leetcode 39. 组合总和 回溯法求解（c++版本）
    【AI开发：音频】一、GPT-SoVITS整合工具包的部署问题解决（GPU版）
    MySQL基础-多表查询
    Android Studio Emulator一直卡在Google Logo出不来
  • 原文地址：https://www.cnblogs.com/longronglang/p/17483257.html