mysql练习题

1，求平均成绩大于60分的学生姓名，学号，平均成绩

SELECT
    student_id,
    sname,
    B.cc
FROM
    (
        SELECT
            student_id,
            avg(num) AS cc
        FROM
            score
        GROUP BY
            student_id
        HAVING
            avg(num) > 60
     ) AS B 
LEFT JOIN student ON B.student_id = student.sid;

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

SELECT
    student_id,
    student.sname,
    count(1),
    sum(num)
FROM
    score
LEFT JOIN student ON student.sid = score.student_id
GROUP BY
    student_id

3，查询姓“李”的老师的个数

SELECT
    count(tid)
FROM
    teacher
WHERE
    tname LIKE '李%';

4，查询没学过“李平”老师课的同学的学号、姓名

 SELECT

　　student.sid,
　　student.sname

 FROM
     student
 WHERE
     sid NOT IN (
         SELECT
             student_id
         FROM
             score
         WHERE
             course_id IN (
                 SELECT
                     cid
                 FROM
                     course
                 LEFT JOIN teacher ON course.teacher_id = teacher.tid
                 WHERE
                     teacher.tname = '李平老师'
             )
         GROUP BY
             student_id
)

5，查询“生物”课程比“物理”课程成绩高的所有学生的学号

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

 6,查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

SELECT
    student.sid,
    student.sname
FROM
    score
LEFT JOIN student ON student.sid = score.student_id
WHERE
    course_id = 1
OR course_id = 2
GROUP BY
    student_id
HAVING
    count(student_id) > 1

7,查询学过“李平老师”所教的所有课的同学的学号、姓名；

SELECT
    student.sid,student.sname
FROM
    score
LEFT JOIN student on student.sid = score.student_id
WHERE
    course_id IN (
        SELECT
            cid
        FROM
            course
        LEFT JOIN teacher ON teacher.tid = course.teacher_id

        WHERE
            teacher.tname = '李平老师'
    )
GROUP BY
    student_id
HAVING
    count(student_id) = (
        SELECT
            count(cid)
        FROM
            course
        LEFT JOIN teacher ON teacher.tid = course.teacher_id
        WHERE
        teacher.tname = '李平老师')

8,查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名

ps: 该题与第五题相似，注意in的用法

SELECT
    student.sid,
    student.sname
FROM
    student
WHERE
    sid IN (
        SELECT
            A.student_id
        FROM
            (
                SELECT
                    student_id,
                    num
                FROM
                    score
                WHERE
                    course_id = 1
            ) AS A
        INNER JOIN (
            SELECT
                student_id,
                num
            FROM
                score
            WHERE
                course_id = 2
        ) AS b ON A.student_id = B.student_id
        WHERE
            A.num > B.num
    )

9,查询有课程成绩小于60分的同学的学号、姓名

ps：distinct可以去重，但是效率低，建议用group by

SELECT
    sid,
    sname
FROM
    student
WHERE
    sid IN (
        SELECT DISTINCT
            student_id
        FROM
            score
        WHERE
            num < 60
    )

10.,查询没有学全所有课的同学的学号、姓名

SELECT
    student_id,
    count(student_id)
FROM
    score
GROUP BY
    student_id
HAVING
    count(student_id) < (SELECT count(cid) FROM course)

 11,查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名

SELECT
    student_id,
    sname
FROM
    score
LEFT JOIN student ON student.sid = score.student_id
WHERE
    student_id != 1
AND course_id IN (
    SELECT
        course_id
    FROM
        score
    WHERE
        student_id = 1
)
GROUP BY
    student_id

12,查询至少学过学号为“001”同学所选课程中任意一门课的其他同学学号和姓名

SELECT
    student_id,
    sname
FROM
    score
LEFT JOIN student ON student.sid = score.student_id
WHERE
    student_id != 1
AND course_id IN (
    SELECT
        course_id
    FROM
        score
    WHERE
        student_id = 1
)
GROUP BY
    student_id
HAVING
    count(1) = (
        SELECT
            count(course_id)
        FROM
            score
        WHERE
            student_id = 1
    )

13,查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名

SELECT
    student_id
FROM
    score
LEFT JOIN student ON student.sid = score.student_id
WHERE
    student_id IN (
        SELECT
            student_id
        FROM
            score
        WHERE
            student_id != 2
        GROUP BY
            student_id
        HAVING
            count(1) = (
                SELECT
                    count(course_id)
                FROM
                    score
                WHERE
                    student_id = 2
            )
    )
AND course_id IN (
    SELECT
        course_id
    FROM
        score
    WHERE
        student_id = 2
)
GROUP BY
    student_id
HAVING
    count(1) = (
        SELECT
            count(course_id)
        FROM
            score
        WHERE
            student_id = 2
    )