A total of 50 SQL exercises with answers (MySQL)

A total of 50 SQL exercises with answers (MySQL)

I've been really busy and super busy recently. You can change the data according to your query needs. If your SQL is 6 then of course it is best. If you are not particularly good, I suggest you understand these 50 Sql questions. After you finish, you will find that you have made a lot of progress! !! Come on

1. Student table student(s_id,s_name,s_birth,s_sex)

- Student number, student name, date of birth, student gender

2. Course table course(c_id, c_name, t_id)

- Course number, course name, teacher number

3. Teacher table teacher(t_id,t_name)

-– Teacher number, teacher name

4. Score table (s_id, c_id, s_score)

--Student number, course number, score

Just create a database and import data to the database:

- Create a curriculum
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `c_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT'' COMMENT'Course ID',
  `c_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT'' COMMENT'Course name',
  `t_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT'Teacher ID',
  PRIMARY KEY (`c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
- Course schedule data
INSERT INTO `course` VALUES ('01','Language', '02');
INSERT INTO `course` VALUES ('02','mathematics', '01');
INSERT INTO `course` VALUES ('03','English', '03');
- Score table
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `s_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT'' COMMENT'Student ID',
  `c_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT'' COMMENT'Course ID',
  `s_score` int(3) NULL DEFAULT NULL COMMENT'score',
  PRIMARY KEY (`s_id`, `c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
- Score data
INSERT INTO `score` VALUES ('01', '01', 80);
INSERT INTO `score` VALUES ('01', '02', 90);
INSERT INTO `score` VALUES ('01', '03', 99);
INSERT INTO `score` VALUES ('02', '01', 70);
INSERT INTO `score` VALUES ('02', '02', 60);
INSERT INTO `score` VALUES ('02', '03', 80);
INSERT INTO `score` VALUES ('03', '01', 80);
INSERT INTO `score` VALUES ('03', '02', 80);
INSERT INTO `score` VALUES ('03', '03', 80);
INSERT INTO `score` VALUES ('04', '01', 50);
INSERT INTO `score` VALUES ('04', '02', 30);
INSERT INTO `score` VALUES ('04', '03', 20);
INSERT INTO `score` VALUES ('05', '01', 76);
INSERT INTO `score` VALUES ('05', '02', 87);
INSERT INTO `score` VALUES ('05', '03', 95);
INSERT INTO `score` VALUES ('06', '01', 31);
INSERT INTO `score` VALUES ('06', '02', 88);
INSERT INTO `score` VALUES ('06', '03', 34);
INSERT INTO `score` VALUES ('07', '01', 66);
INSERT INTO `score` VALUES ('07', '02', 89);
INSERT INTO `score` VALUES ('07', '03', 98);
INSERT INTO `score` VALUES ('08', '01', 59);
INSERT INTO `score` VALUES ('08', '02', 88);
INSERT INTO `score` VALUES ('09', '02', 67);
INSERT INTO `score` VALUES ('09', '03', 88);
INSERT INTO `score` VALUES ('10', '01', 65);
INSERT INTO `score` VALUES ('10', '02', 78);
- Create a student table
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `s_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT'' COMMENT'Student ID',
  `s_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT'' COMMENT'student name',
  `s_birth` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT'' COMMENT'Birth month',
  `s_sex` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT'' COMMENT'Student gender',
  PRIMARY KEY (`s_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
- Student data
INSERT INTO `student` VALUES ('01','Snake', '1990-01-01','Male');
INSERT INTO `student` VALUES ('02','Zhang Yida', '1990-12-21','Male');
INSERT INTO `student` VALUES ('03','张大炮', '1990-05-20','Male');
INSERT INTO `student` VALUES ('04','Li Yunlong', '1990-08-06','Male');
INSERT INTO `student` VALUES ('05','Chu Yunfei', '1991-12-01','Female');
INSERT INTO `student` VALUES ('06','Zhao Ritian', '1992-03-01','Female');
INSERT INTO `student` VALUES ('07','Xiao Tiantian', '1989-07-01','Female');
INSERT INTO `student` VALUES ('08','Wang Juhua', '1990-01-20','Female');
INSERT INTO `student` VALUES ('09','Li Mubai', '1994-01-20','Male');
INSERT INTO `student` VALUES ('10','Tokyo Hot', '1980-01-20','Female');
- Create teacher list
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `t_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT'' COMMENT'Teacher ID',
  `t_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT'' COMMENT'Teacher's name',
  PRIMARY KEY (`t_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
- Teacher table data
INSERT INTO `teacher` VALUES ('01','ink white');
INSERT INTO `teacher` VALUES ('02','默狐');
INSERT INTO `teacher` VALUES ('03','lemon');
SET FOREIGN_KEY_CHECKS = 1;

1. Inquire about the information and course scores of students with higher grades in the "Chinese" course than in the "Mathematics" course

SELECT
    st.*,
    sc.s_score AS'Language',
    sc2.s_score'mathematics' 
FROM
    student st
    LEFT JOIN score sc ON sc.s_id = st.s_id 
    AND sc.c_id = '01'
    LEFT JOIN score sc2 ON sc2.s_id = st.s_id 
    AND sc2.c_id = '02' 
WHERE
    sc.s_score> sc2.s_score

2. Query the information and course scores of students whose grades in the "Chinese" course are lower than those in the "Mathematics" course

SELECT
    st.*,
    sc.s_score'language',
    sc2.s_score'mathematics' 
FROM
    student st
    LEFT JOIN score sc ON sc.s_id = st.s_id 
    AND sc.c_id = '01'
    LEFT JOIN score sc2 ON sc2.s_id = st.s_id 
    AND sc2.c_id = '02' 
WHERE
    sc.s_score <sc2.s_score

3. Query the student number and student name and average score of students with an average score greater than or equal to 60 points

SELECT
    st.s_id,
    st.s_name,
    ROUND( AVG( sc.s_score ), 2) "Average score" 
FROM
    student st
    LEFT JOIN score sc ON sc.s_id = st.s_id 
GROUP BY
    st.s_id 
HAVING
    AVG( sc.s_score )>= 60

4. Query the student number and student name and average score of students whose average score is less than 60 points (including those with and without scores)

SELECT
    st.s_id,
    st.s_name,(
    CASE

            WHEN ROUND( AVG( sc.s_score ), 2) IS NULL THEN
            0 ELSE ROUND( AVG( sc.s_score ), 2) 
        END 
        ) "average score" 
    FROM
        student st
        LEFT JOIN score sc ON sc.s_id = st.s_id 
    GROUP BY
        st.s_id 
    HAVING
    AVG( sc.s_score )< 60 
    OR AVG( sc.s_score) IS NULL

5. Query the student number, student name, total number of selected courses, and total score of all courses of all students

SELECT
    st.s_id,
    st.s_name,
    count( sc.c_id) "Total number of selected courses",
    sum( CASE WHEN sc.s_score IS NULL THEN 0 ELSE sc.s_score END) "Total score" 
FROM
    student st
    LEFT JOIN score sc ON st.s_id = sc.s_id 
GROUP BY
    st.s_id

6. Query the number of teachers surnamed "Mo"

SELECT
    t.t_name,
    count( t.t_id) 
FROM
    teacher t 
GROUP BY
    t.t_id 
HAVING
    t.t_name LIKE "墨%";

7. Query the information of the students who have studied the "Mo Bai" teacher

SELECT
    st.* 
FROM
    student st
    LEFT JOIN score sc ON sc.s_id = st.s_id
    LEFT JOIN course c ON c.c_id = sc.c_id
    LEFT JOIN teacher t ON t.t_id = c.t_id 
WHERE
    t.t_name = "Ink White"
8. Inquire about the information of students who have never studied the "lemon" teacher
- Lesson taught by Teacher Lemon
SELECT
    c.* 
FROM
    course c
    LEFT JOIN teacher t ON t.t_id = c.t_id 
WHERE
    t.t_name = "Lemon" 
- st.s_id with the class result of the lemon teacher
SELECT
    sc.s_id 
FROM
    score sc 
WHERE
    sc.c_id IN (SELECT c.c_id FROM course c LEFT JOIN teacher t ON t.t_id = c.t_id WHERE t.t_name = "Lemon") 
- The student information of st.s_id that is not found above, that is, the information of students who have not learned the lectures of Teacher Lemon
SELECT
    st.* 
FROM
    student st 
WHERE
    st.s_id NOT IN (
    SELECT
        sc.s_id 
    FROM
        score sc 
    WHERE
    sc.c_id IN (SELECT c.c_id FROM course c LEFT JOIN teacher t ON t.t_id = c.t_id WHERE t.t_name = "Lemon") 
    )

9. Query the information of students who have studied the course numbered "01" and also studied the course numbered "02"

SELECT
    st.* 
FROM
    student st
    INNER JOIN score sc ON sc.s_id = st.s_id
    INNER JOIN course c ON c.c_id = sc.c_id 
    AND c.c_id = "01" 
WHERE
    st.s_id IN (
    SELECT
        st2.s_id 
    FROM
        student st2
        INNER JOIN score sc2 ON sc2.s_id = st2.s_id
        INNER JOIN course c2 ON c2.c_id = sc2.c_id 
    AND c2.c_id = "02" 
    )
- or the second
SELECT
    a.* 
FROM
    student a,
    score b,
    score c 
WHERE
    a.s_id = b.s_id 
    AND a.s_id = c.s_id 
    AND b.c_id = '01' 
    AND c.c_id = '02';

10. Query the information of students who have studied the course number "01" but have not studied the course number "02"

SELECT
    st.* 
FROM
    student st
    INNER JOIN score sc ON sc.s_id = st.s_id
    INNER JOIN course c ON c.c_id = sc.c_id 
    AND c.c_id = "01" 
WHERE
    st.s_id NOT IN (
    SELECT
        st2.s_id 
    FROM
        student st2
        INNER JOIN score sc2 ON sc2.s_id = st2.s_id
        INNER JOIN course c2 ON c2.c_id = sc2.c_id 
    AND c2.c_id = "02" 
    )

11. Query the information of students who have not studied all courses

SELECT
    * 
FROM
    student 
WHERE
    s_id NOT IN (
    SELECT
        st.s_id 
    FROM
        student st
        INNER JOIN score sc ON sc.s_id = st.s_id 
        AND sc.c_id = "01" 
    WHERE
    st.s_id IN (SELECT st1.s_id FROM student st1 INNER JOIN score sc2 ON sc2.s_id = st1.s_id AND sc2.c_id = "02") 
    AND st.s_id IN (SELECT st2.s_id FROM student st2 INNER JOIN score sc2 ON sc2.s_id = st2.s_id AND sc2.c_id = "03" ))

12. Query the information of at least one class that is the same as the student whose student ID is "01"

SELECT DISTINCT
    st.* 
FROM
    student st
    LEFT JOIN score sc ON sc.s_id = st.s_id 
WHERE
    sc.c_id IN (
    SELECT
        sc2.c_id 
    FROM
        student st2
        LEFT JOIN score sc2 ON sc2.s_id = st2.s_id 
    WHERE
    st2.s_id = '01' 
    )

13. Query the information of other students who are learning the same courses as the students of "01"

SELECT
    st.* 
FROM
    student st
    LEFT JOIN score sc ON sc.s_id = st.s_id 
GROUP BY
    st.s_id 
HAVING
    group_concat( sc.c_id) = (
    SELECT
        group_concat( sc2.c_id) 
    FROM
        student st2
        LEFT JOIN score sc2 ON sc2.s_id = st2.s_id 
    WHERE
    st2.s_id = '01' 
    )

14. Inquire about the names of students who have not studied any course taught by the teacher "Mohu"

SELECT
    st.s_name 
FROM
    student st 
WHERE
    st.s_id NOT IN (
    SELECT
        sc.s_id 
    FROM
        score sc
        INNER JOIN course c ON c.c_id = sc.c_id
        INNER JOIN teacher t ON t.t_id = c.t_id 
    AND t.t_name = "Silent Fox" 
    )

15. Inquire about the student ID, name and average grade of students who failed two or more courses

SELECT
    st.s_id,
    st.s_name,
    avg( sc.s_score) 
FROM
    student st
    LEFT JOIN score sc ON sc.s_id = st.s_id 
WHERE
    sc.s_id IN (
    SELECT
        sc.s_id 
    FROM
        score sc 
    WHERE
        sc.s_score <60 
        OR sc.s_score IS NULL 
    GROUP BY
        sc.s_id 
    HAVING
        COUNT( 1 )>= 2 
    ) 
GROUP BY
    st.s_id

16. Retrieve the information of students whose scores of "01" course are less than 60, sorted in descending order of scores

SELECT
    st.*,
    sc.s_score 
FROM
    student st
    INNER JOIN score sc ON sc.s_id = st.s_id 
    AND sc.c_id = "01" 
    AND sc.s_score <60 
ORDER BY
    sc.s_score DESC

- The second type of SQL
SELECT
    st.*,
    sc.s_score 
FROM
    student st
    LEFT JOIN score sc ON sc.s_id = st.s_id 
WHERE
    sc.c_id = "01" 
    AND sc.s_score <60 
ORDER BY
    sc.s_score DESC

17. Display the grades and average grades of all the courses of all students from high to low according to their average grades

SELECT
    st.s_id,
    st.s_name,
    avg( sc4.s_score) "Average Score",
    sc.s_score "Language",
    sc2.s_score "Mathematics",
    sc3.s_score "English" 
FROM
    student st
    LEFT JOIN score sc ON sc.s_id = st.s_id 
    AND sc.c_id = "01"
    LEFT JOIN score sc2 ON sc2.s_id = st.s_id 
    AND sc2.c_id = "02"
    LEFT JOIN score sc3 ON sc3.s_id = st.s_id 
    AND sc3.c_id = "03"
    LEFT JOIN score sc4 ON sc4.s_id = st.s_id 
GROUP BY
    st.s_id 
ORDER BY
    avg( sc4.s_score) DESC

- The second
SELECT
    st.s_id,
    st.s_name,
    (CASE WHEN avg( sc4.s_score) IS NULL THEN 0 ELSE avg( sc4.s_score) END) "Average score",
    (CASE WHEN sc.s_score IS NULL THEN 0 ELSE sc.s_score END) "Language",
    (CASE WHEN sc2.s_score IS NULL THEN 0 ELSE sc2.s_score END) "Mathematics",
    (CASE WHEN sc3.s_score IS NULL THEN 0 ELSE sc3.s_score END) "English" 
FROM
    student st
    LEFT JOIN score sc ON sc.s_id = st.s_id 
    AND sc.c_id = "01"
    LEFT JOIN score sc2 ON sc2.s_id = st.s_id 
    AND sc2.c_id = "02"
    LEFT JOIN score sc3 ON sc3.s_id = st.s_id 
    AND sc3.c_id = "03"
    LEFT JOIN score sc4 ON sc4.s_id = st.s_id 
GROUP BY
    st.s_id 
ORDER BY
    avg( sc4.s_score) DESC

18. Query the highest score, lowest score and average score of each subject: display in the following format: course ID, course name, highest score, lowest score, average score, pass rate, medium rate, excellent rate, excellent rate

(Passing is >=60, medium is: 70-80, excellent is: 80-90, excellent is: >=90)

SELECT
    c.c_id,
    c.c_name,
    max( sc.s_score) "Highest Score",
    MIN( sc2.s_score) "lowest score",
    avg( sc3.s_score) "Average score", ((
        SELECT
            count( s_id) 
        FROM
            score 
        WHERE
            s_score >= 60 
            AND c_id = c.c_id 
            )/(
        SELECT
            count( s_id) 
        FROM
            score 
        WHERE
            c_id = c.c_id 
            )) "passing rate",((
        SELECT
            count( s_id) 
        FROM
            score 
        WHERE
            s_score >= 70 
            AND s_score <80 
            AND c_id = c.c_id 
            )/(
        SELECT
            count( s_id) 
        FROM
            score 
        WHERE
            c_id = c.c_id 
            )) "Medium rate",((
        SELECT
            count( s_id) 
        FROM
            score 
        WHERE
            s_score >= 80 
            AND s_score <90 
            AND c_id = c.c_id 
            )/(
        SELECT
            count( s_id) 
        FROM
            score 
        WHERE
            c_id = c.c_id 
            )) "Excellent rate",((
        SELECT
            count( s_id) 
        FROM
            score 
        WHERE
            s_score >= 90 
            AND c_id = c.c_id 
            )/(
        SELECT
            count( s_id) 
        FROM
            score 
        WHERE
            c_id = c.c_id 
        )) "Excellent Rate" 
FROM
    course c
    LEFT JOIN score sc ON sc.c_id = c.c_id
    LEFT JOIN score sc2 ON sc2.c_id = c.c_id
    LEFT JOIN score sc3 ON sc3.c_id = c.c_id 
GROUP BY
    c.c_id

19. Sort according to the results of each subject and display the ranking (incomplete realization)

SELECT
    c.c_id,
    c.c_name,
    max( sc.s_score) "Highest Score",
    MIN( sc2.s_score) "lowest score",
    avg( sc3.s_score) "Average score", ((
        SELECT
            count( s_id) 
        FROM
            score 
        WHERE
            s_score >= 60 
            AND c_id = c.c_id 
            )/(
        SELECT
            count( s_id) 
        FROM
            score 
        WHERE
            c_id = c.c_id 
            )) "passing rate",((
        SELECT
            count( s_id) 
        FROM
            score 
        WHERE
            s_score >= 70 
            AND s_score <80 
            AND c_id = c.c_id 
            )/(
        SELECT
            count( s_id) 
        FROM
            score 
        WHERE
            c_id = c.c_id 
            )) "Medium rate",((
        SELECT
            count( s_id) 
        FROM
            score 
        WHERE
            s_score >= 80 
            AND s_score <90 
            AND c_id = c.c_id 
            )/(
        SELECT
            count( s_id) 
        FROM
            score 
        WHERE
            c_id = c.c_id 
            )) "Excellent rate",((
        SELECT
            count( s_id) 
        FROM
            score 
        WHERE
            s_score >= 90 
            AND c_id = c.c_id 
            )/(
        SELECT
            count( s_id) 
        FROM
            score 
        WHERE
            c_id = c.c_id 
        )) "Excellent Rate" 
FROM
    course c
    LEFT JOIN score sc ON sc.c_id = c.c_id
    LEFT JOIN score sc2 ON sc2.c_id = c.c_id
    LEFT JOIN score sc3 ON sc3.c_id = c.c_id 
GROUP BY
    c.c_id

20. Query the student's total score and rank

SELECT
    st.s_id,
    st.s_name,(
    CASE

            WHEN sum( sc.s_score) IS NULL THEN
            0 ELSE sum( sc.s_score) 
        END 
        ) 
    FROM
        student st
        LEFT JOIN score sc ON sc.s_id = st.s_id 
    GROUP BY
        st.s_id 
ORDER BY
    sum( sc.s_score) DESC

21. Query the average score of different courses taught by different teachers from high to low display

SELECT
    t.t_id,
    t.t_name,
    c.c_name,
    avg( sc.s_score) 
FROM
    teacher t
    LEFT JOIN course c ON c.t_id = t.t_id
    LEFT JOIN score sc ON sc.c_id = c.c_id 
GROUP BY
    t.t_id 
ORDER BY
    avg( sc.s_score) DESC

22. Query the information of the students ranked 2nd to 3rd in the scores of all courses and the course scores

SELECT
    a.* 
FROM
    (
    SELECT
        st.*,
        c.c_id,
        c.c_name,
        sc.s_score 
    FROM
        student st
        LEFT JOIN score sc ON sc.s_id = st.s_id
        INNER JOIN course c ON c.c_id = sc.c_id 
        AND c.c_id = "01" 
    ORDER BY
        sc.s_score DESC 
        LIMIT 1,
        2 
    ) a UNION ALL
SELECT
    b.* 
FROM
    (
    SELECT
        st.*,
        c.c_id,
        c.c_name,
        sc.s_score 
    FROM
        student st
        LEFT JOIN score sc ON sc.s_id = st.s_id
        INNER JOIN course c ON c.c_id = sc.c_id 
        AND c.c_id = "02" 
    ORDER BY
        sc.s_score DESC 
        LIMIT 1,
        2 
    ) b UNION ALL
SELECT
    c.* 
FROM
    (
    SELECT
        st.*,
        c.c_id,
        c.c_name,
        sc.s_score 
    FROM
        student st
        LEFT JOIN score sc ON sc.s_id = st.s_id
        INNER JOIN course c ON c.c_id = sc.c_id 
        AND c.c_id = "03" 
    ORDER BY
        sc.s_score DESC 
        LIMIT 1,
    2 
    ) c

23. Count the number of people in each score section of each subject: course number, course name, [100-85], [85-70], [70-60], [0-60] and the percentage

SELECT
    c.c_id,
    c.c_name,((
        SELECT
            count( 1) 
        FROM
            score sc 
        WHERE
            sc.c_id = c.c_id 
            AND sc.s_score <= 100 AND sc.s_score> 80 
            )/(
        SELECT
            count( 1) 
        FROM
            score sc 
        WHERE
            sc.c_id = c.c_id 
            )) "100-85",((
        SELECT
            count( 1) 
        FROM
            score sc 
        WHERE
            sc.c_id = c.c_id 
            AND sc.s_score <= 85 AND sc.s_score> 70 
            )/(
        SELECT
            count( 1) 
        FROM
            score sc 
        WHERE
            sc.c_id = c.c_id 
            )) "85-70",((
        SELECT
            count( 1) 
        FROM
            score sc 
        WHERE
            sc.c_id = c.c_id 
            AND sc.s_score <= 70 AND sc.s_score> 60 
            )/(
        SELECT
            count( 1) 
        FROM
            score sc 
        WHERE
            sc.c_id = c.c_id 
            )) "70-60",((
        SELECT
            count( 1) 
        FROM
            score sc 
        WHERE
            sc.c_id = c.c_id 
            AND sc.s_score <= 60 AND sc.s_score >= 0 
            )/(
        SELECT
            count( 1) 
        FROM
            score sc 
        WHERE
            sc.c_id = c.c_id 
        )) "60-0" 
FROM
    course c 
ORDER BY
    c.c_id

24. Query the average score and ranking of students

SET @i = 0;
SELECT
    a.*,
    @i := @i + 1 
FROM
    (
    SELECT
        st.s_id,
        st.s_name,
        round(( CASE WHEN avg( sc.s_score) IS NULL THEN 0 ELSE avg( sc.s_score) END ), 2) "Average score" 
    FROM
        student st
        LEFT JOIN score sc ON sc.s_id = st.s_id 
    GROUP BY
        st.s_id 
    ORDER BY
    sc.s_score DESC 
    ) a

25. Query the top three records of each subject score

SELECT
    a.* 
FROM
    (
    SELECT
        st.s_id,
        st.s_name,
        c.c_id,
        c.c_name,
        sc.s_score 
    FROM
        student st
        LEFT JOIN score sc ON sc.s_id = st.s_id
        INNER JOIN course c ON c.c_id = sc.c_id 
        AND c.c_id = '01' 
    ORDER BY
        sc.s_score DESC 
        LIMIT 0,
        3 
    ) a UNION ALL
SELECT
    b.* 
FROM
    (
    SELECT
        st.s_id,
        st.s_name,
        c.c_id,
        c.c_name,
        sc.s_score 
    FROM
        student st
        LEFT JOIN score sc ON sc.s_id = st.s_id
        INNER JOIN course c ON c.c_id = sc.c_id 
        AND c.c_id = '02' 
    ORDER BY
        sc.s_score DESC 
        LIMIT 0,
        3 
    ) b UNION ALL
SELECT
    c.* 
FROM
    (
    SELECT
        st.s_id,
        st.s_name,
        c.c_id,
        c.c_name,
        sc.s_score 
    FROM
        student st
        LEFT JOIN score sc ON sc.s_id = st.s_id
        INNER JOIN course c ON c.c_id = sc.c_id 
        AND c.c_id = '03' 
    ORDER BY
        sc.s_score DESC 
        LIMIT 0,
    3 
    ) c

26. Query the number of students selected for each course

SELECT
    c.c_id,
    c.c_name,
    count( 1) 
FROM
    course c
    LEFT JOIN score sc ON sc.c_id = c.c_id
    INNER JOIN student st ON st.s_id = c.c_id 
GROUP BY
    st.s_id

27. Find out the student numbers and names of all students with only two courses

SELECT
    st.s_id,
    st.s_name 
FROM
    student st
    LEFT JOIN score sc ON sc.s_id = st.s_id
    INNER JOIN course c ON c.c_id = sc.c_id 
GROUP BY
    st.s_id 
HAVING
    count( 1 )=2

28. Query the number of boys and girls

SELECT
    st.s_sex,
    count( 1) 
FROM
    student st 
GROUP BY
    st.s_sex

29. Query the information of students whose names contain "白"

SELECT
    st.* 
FROM
    student st 
WHERE
    st.s_name LIKE "%白%";

30. Query the list of same-sex students with the same name and count the number of people with the same name

SELECT
    st.*,
    count( 1) 
FROM
    student st 
GROUP BY
    st.s_name,
    st.s_sex 
HAVING
    count( 1 )> 1

31. Query the list of students born in 1990

SELECT
    st.* 
FROM
    student st 
WHERE
    st.s_birth LIKE "1990%";

32. Query the average score of each course, and the results are sorted in descending order of the average score. When the average score is the same, they are sorted in ascending order by the course number.

SELECT
    c.c_id,
    c.c_name,
    avg( sc.s_score) 
FROM
    course c
    INNER JOIN score sc ON sc.c_id = c.c_id 
GROUP BY
    c.c_id 
ORDER BY
    avg( sc.s_score) DESC,
    c.c_id ASC

33. Query the student ID, name and average score of all students with an average score of 85 or more

SELECT
    st.s_id,
    st.s_name,
    avg( sc.s_score) 
FROM
    student st
    LEFT JOIN score sc ON sc.s_id = st.s_id 
GROUP BY
    st.s_id 
HAVING
    avg( sc.s_score )>= 85

34. Query the names and scores of students whose course name is "Mathematics" and whose score is lower than 60

SELECT
    st.s_id,
    st.s_name,
    sc.s_score 
FROM
    student st
    INNER JOIN score sc ON sc.s_id = st.s_id 
    AND sc.s_score <60
    INNER JOIN course c ON c.c_id = sc.c_id 
    AND c.c_name = "Mathematics"

35. Inquire about the courses and scores of all students

SELECT
    st.s_id,
    st.s_name,
    c.c_name,
    sc.s_score 
FROM
    student st
    LEFT JOIN score sc ON sc.s_id = st.s_id
    LEFT JOIN course c ON c.c_id = sc.c_id 
ORDER BY
    st.s_id,
    c.c_name

36. Query the name, course name and score of any course with a score of 70 or more

SELECT
    st2.s_id,
    st2.s_name,
    c2.c_name,
    sc2.s_score 
FROM
    student st2
    LEFT JOIN score sc2 ON sc2.s_id = st2.s_id
    LEFT JOIN course c2 ON c2.c_id = sc2.c_id 
WHERE
    st2.s_id IN (
    SELECT
        st.s_id 
    FROM
        student st
        LEFT JOIN score sc ON sc.s_id = st.s_id 
    GROUP BY
        st.s_id 
    HAVING
        min( sc.s_score )>= 70 
    ) 
ORDER BY
    s_id

37. Check failed courses

SELECT
    st.s_id,
    c.c_name,
    st.s_name,
    sc.s_score 
FROM
    student st
    INNER JOIN score sc ON sc.s_id = st.s_id 
    AND sc.s_score <60
    INNER JOIN course c ON c.c_id = sc.c_id

38. Query the student ID and name of the student whose course number is 01 and whose course score is above 80

SELECT
    st.s_id,
    st.s_name,
    sc.s_score 
FROM
    student st
    INNER JOIN score sc ON sc.s_id = st.s_id 
    AND sc.c_id = "01" 
    AND sc.s_score >= 80

39. Find the number of students in each course

SELECT
    c.c_id,
    c.c_name,
    count( 1) 
FROM
    course c
    INNER JOIN score sc ON sc.c_id = c.c_id 
GROUP BY
    c.c_id

40. Inquire about the information of the students with the highest scores and their scores among the students who took the courses taught by teacher "Zhang San"

SELECT
    st.*,
    c.c_name,
    sc.s_score,
    t.t_name 
FROM
    student st
    INNER JOIN score sc ON sc.s_id = st.s_id
    INNER JOIN course c ON c.c_id = sc.c_id
    INNER JOIN teacher t ON t.t_id = c.t_id 
    AND t.t_name = "Ink White" 
ORDER BY
    sc.s_score DESC 
    LIMIT 0,1

41. Query the student number, course number, and student scores of students with the same grades in different courses

SELECT
    st.s_id,
    st.s_name,
    sc.c_id,
    sc.s_score 
FROM
    student st
    LEFT JOIN score sc ON sc.s_id = st.s_id
    LEFT JOIN course c ON c.c_id = sc.c_id 
WHERE
    (
    SELECT
        count( 1) 
    FROM
        student st2
        LEFT JOIN score sc2 ON sc2.s_id = st2.s_id
        LEFT JOIN course c2 ON c2.c_id = sc2.c_id 
    WHERE
        sc.s_score = sc2.s_score 
    AND c.c_id != c2.c_id 
    )>1

42. Query the top two with the best scores in each class

SELECT
    a.* 
FROM
    (
    SELECT
        st.s_id,
        st.s_name,
        c.c_name,
        sc.s_score 
    FROM
        student st
        LEFT JOIN score sc ON sc.s_id = st.s_id
        INNER JOIN course c ON c.c_id = sc.c_id 
        AND c.c_id = "01" 
    ORDER BY
        sc.s_score DESC 
        LIMIT 0,
        2 
    ) a UNION ALL
SELECT
    b.* 
FROM
    (
    SELECT
        st.s_id,
        st.s_name,
        c.c_name,
        sc.s_score 
    FROM
        student st
        LEFT JOIN score sc ON sc.s_id = st.s_id
        INNER JOIN course c ON c.c_id = sc.c_id 
        AND c.c_id = "02" 
    ORDER BY
        sc.s_score DESC 
        LIMIT 0,
        2 
    ) b UNION ALL
SELECT
    c.* 
FROM
    (
    SELECT
        st.s_id,
        st.s_name,
        c.c_name,
        sc.s_score 
    FROM
        student st
        LEFT JOIN score sc ON sc.s_id = st.s_id
        INNER JOIN course c ON c.c_id = sc.c_id 
        AND c.c_id = "03" 
    ORDER BY
        sc.s_score DESC 
        LIMIT 0,
    2 
    ) c
    - Reference (more accurate and beautiful):
SELECT
    a.s_id,
    a.c_id,
    a.s_score 
FROM
    score a 
WHERE
    (SELECT COUNT( 1) FROM score b WHERE b.c_id = a.c_id AND b.s_score >= a.s_score )<= 2 
ORDER BY
    a.c_id

43. Count the number of elective students for each course (only count for courses with more than 5 students). It is required to output the course number and the number of electives, and the query results are sorted in descending order by the number of people. If the number of people is the same, they are sorted in ascending order by the course number

SELECT
    sc.c_id,
    count( 1) 
FROM
    score sc
    LEFT JOIN course c ON c.c_id = sc.c_id 
GROUP BY
    c.c_id 
HAVING
    count( 1 )> 5 
ORDER BY
    count( 1) DESC,
    sc.c_id ASC

44. Retrieve student IDs of students who have taken at least two courses

SELECT
    st.s_id 
FROM
    student st
    LEFT JOIN score sc ON sc.s_id = st.s_id 
GROUP BY
    st.s_id 
HAVING
    count( 1 )>=2

45. Query information about students who have taken all courses

SELECT
    st.* 
FROM
    student st
    LEFT JOIN score sc ON sc.s_id = st.s_id 
GROUP BY
    st.s_id 
HAVING
    count( 1 )=(
    SELECT
        count( 1) 
    FROM
    course 
    )

46. ​​Query the age of each student

SELECT
    st.*,
    timestampdiff(
        YEAR,
        st.s_birth,
    now()) 
FROM
    student st

47. Query students who have a birthday this week

SELECT
    st.* 
FROM
    student st 
WHERE
    WEEK (
    now()) = WEEK (
    date_format( st.s_birth,'%Y%m%d' ))

48. Query students who will have their birthday next week

SELECT
    st.* 
FROM
    student st 
WHERE
    WEEK (
    now())+ 1 = WEEK (
    date_format( st.s_birth,'%Y%m%d' ))

49. Query students who have a birthday this month

SELECT
    st.* 
FROM
    student st 
WHERE
    MONTH (
    now()) = MONTH (
    date_format( st.s_birth,'%Y%m%d' ))
50. Query the students who have a birthday next month
SELECT
    st.* 
FROM
    student st 
WHERE
    MONTH (
        timestampadd(
            MONTH,
            1,
        now()))= MONTH (
    date_format( st.s_birth,'%Y%m%d' ))
    - or
SELECT
    st.* 
FROM
    student st 
WHERE
    (MONTH (now()) + 1) MOD 12 = MONTH (
    date_format( st.s_birth,'%Y%m%d' ))