面试必备杀技:SQL查询专项训练(二)

简介: 面试必备杀技:SQL查询专项训练

面试必备杀技:SQL查询专项训练(一)https://developer.aliyun.com/article/1424272


8、 查询物理课程比生物课程高的学生的学号(分别得到物理成绩表与生物成绩表,然后连表即可)

SELECT
    t1.student_id
FROM
    (
        SELECT
            student_id,
            num
        FROM
            score
        WHERE
            course_id = (
                SELECT
                    cid
                FROM
                    course
                WHERE
                    cname = '物理'
            )
    ) AS t1
INNER JOIN (
    SELECT
        student_id,
        num
    FROM
        score
    WHERE
        course_id = (
            SELECT
                cid
            FROM
                course
            WHERE
                cname = '生物'
        )
) AS t2 ON t1.student_id = t2.student_id
WHERE
    t1.num > t2.num;

9、 查询没有同时选修物理课程和体育课程的学生姓名(没有同时选修指的是选修了一门的,思路是得到物理+体育课程的学生信息表,然后基于学生分组,统计count(课程)=1)

SELECT
    student.sname
FROM
    student
WHERE
    sid IN (
        SELECT
            student_id
        FROM
            score
        WHERE
            course_id IN (
                SELECT
                    cid
                FROM
                    course
                WHERE
                    cname = '物理'
                OR cname = '体育'
            )
        GROUP BY
            student_id
        HAVING
            COUNT(course_id) = 1
    );

10、查询挂科超过两门(包括两门)的学生姓名和班级(求出<60的表,然后对学生进行分组,统计课程数目>=2)

SELECT
    student.sname,
    class.caption
FROM
    student
INNER JOIN (
    SELECT
        student_id
    FROM
        score
    WHERE
        num < 60
    GROUP BY
        student_id
    HAVING
        count(course_id) >= 2
) AS t1
INNER JOIN class ON student.sid = t1.student_id
AND student.class_id = class.cid;

11、查询选修了所有课程的学生姓名(先从course表统计课程的总数,然后基于score表按照student_id分组,统计课程数据等于课程总数即可)

SELECT
    student.sname
FROM
    student
WHERE
    sid IN (
        SELECT
            student_id
        FROM
            score
        GROUP BY
            student_id
        HAVING
            COUNT(course_id) = (SELECT count(cid) FROM course)
    );

12、查询李平老师教的课程的所有成绩记录

SELECT
    *
FROM
    score
WHERE
    course_id IN (
        SELECT
            cid
        FROM
            course
        INNER JOIN teacher ON course.teacher_id = teacher.tid
        WHERE
            teacher.tname = '李平老师'
    );

13、查询全部学生都选修了的课程号和课程名(取所有学生数,然后基于score表的课程分组,找出count(student_id)等于学生数即可)

SELECT
    cid,
    cname
FROM
    course
WHERE
    cid IN (
        SELECT
            course_id
        FROM
            score
        GROUP BY
            course_id
        HAVING
            COUNT(student_id) = (
                SELECT
                    COUNT(sid)
                FROM
                    student
            )
    );

14、查询每门课程被选修的次数

SELECT
    course_id,
    COUNT(student_id)
FROM
    score
GROUP BY
    course_id;

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

SELECT
    sid,
    sname
FROM
    student
WHERE
    sid IN (
        SELECT
            student_id
        FROM
            score
        GROUP BY
            student_id
        HAVING
            COUNT(course_id) = 1
    );

16、查询所有学生考出的成绩并按从高到低排序(成绩去重)

SELECT DISTINCT
    num
FROM
    score
ORDER BY
    num DESC;

17、查询平均成绩大于85的学生姓名和平均成绩

SELECT
    sname,
    t1.avg_num
FROM
    student
INNER JOIN (
    SELECT
        student_id,
        avg(num) avg_num
    FROM
        score
    GROUP BY
        student_id
    HAVING
        AVG(num) > 85
) t1 ON student.sid = t1.student_id;

18、查询生物成绩不及格的学生姓名和对应生物分数

SELECT
    sname 姓名,
    num 生物成绩
FROM
    score
LEFT JOIN course ON score.course_id = course.cid
LEFT JOIN student ON score.student_id = student.sid
WHERE
    course.cname = '生物'
AND score.num < 60;

19、查询在所有选修了李平老师课程的学生中,这些课程(李平老师的课程,不是所有课程)平均成绩最高的学生姓名

SELECT
    sname
FROM
    student
WHERE
    sid = (
        SELECT
            student_id
        FROM
            score
        WHERE
            course_id IN (
                SELECT
                    course.cid
                FROM
                    course
                INNER JOIN teacher ON course.teacher_id = teacher.tid
                WHERE
                    teacher.tname = '李平老师'
            )
        GROUP BY
            student_id
        ORDER BY
            AVG(num) DESC
        LIMIT 1
    );
相关文章
|
14天前
|
SQL
sql语句加正则 简化查询
sql语句加正则 简化查询
14 0
sql语句加正则 简化查询
|
1月前
|
SQL
sql server链接查询
sql server链接查询
17 1
|
1月前
|
SQL
sql server简单查询
sql server简单查询
14 1
|
22天前
|
SQL 关系型数据库 MySQL
mysql一条sql查询出多个统计结果
mysql一条sql查询出多个统计结果
14 0
|
1月前
|
SQL
sql高级查询
sql高级查询
12 0
|
20天前
|
SQL 关系型数据库 MySQL
SQL常见面试题总结2
SQL常见面试题总结
51 2
|
26天前
|
SQL 关系型数据库 MySQL
MySQL SQL语句面试准备
MySQL SQL语句面试准备
12 0
|
1月前
|
SQL 数据库
sql server高级查询,看这篇文章就够了
sql server高级查询,看这篇文章就够了
22 0
|
25天前
|
Java 程序员
java线程池讲解面试
java线程池讲解面试
48 1
|
2月前
|
存储 关系型数据库 MySQL
2024年Java秋招面试必看的 | MySQL调优面试题
随着系统用户量的不断增加,MySQL 索引的重要性不言而喻,对于后端工程师,只有在了解索引及其优化的规则,并应用于实际工作中后,才能不断的提升系统性能,开发出高性能、高并发和高可用的系统。 今天小编首先会跟大家分享一下MySQL 索引中的各种概念,然后介绍优化索引的若干条规则,最后利用这些规则,针对面试中常考的知识点,做详细的实例分析。
246 0
2024年Java秋招面试必看的 | MySQL调优面试题