MySQL 基本精選練習題及答案

WBOY
發布: 2024-08-05 21:00:20
原創
1164 人瀏覽過

Essential MySQL Selected Practice Questions with Answers

表名和欄位(MySQL)

  1. 學生桌
    學生(s_id,s_name,s_birth,s_sex)
    學生證、學生姓名、出生日期、學生性別

  2. 課表

    課程(c_id, c_name, t_id)
    課程 ID、課程名稱、教師 ID

  3. 老師桌

    老師(t_id, t_name)
    教師 ID、教師姓名

  4. 分數表

    分數(s_id, c_id, s_score)
    學生ID、課程ID、分數

Test Data - Creating Tables

  1. Student Table
CREATE TABLE  `Student`(  
`s_id`  VARCHAR(20),  
`s_name`  VARCHAR(20) NOT NULL DEFAULT '',  
`s_birth`  VARCHAR(20) NOT NULL DEFAULT '',  
`s_sex`  VARCHAR(10) NOT NULL DEFAULT '',  
PRIMARY KEY(`s_id`)  
);
登入後複製
  1. Course Table
CREATE TABLE  `Course`(  
`c_id`  VARCHAR(20),  
`c_name`  VARCHAR(20) NOT NULL DEFAULT '',  
`t_id`  VARCHAR(20) NOT NULL,  
PRIMARY KEY(`c_id`)  
);
登入後複製
  1. Teacher Table
CREATE TABLE  `Teacher`(  
`t_id`  VARCHAR(20),  
`t_name`  VARCHAR(20) NOT NULL DEFAULT '',  
PRIMARY KEY(`t_id`)  
);
登入後複製
  1. Score Table
CREATE TABLE  `Score`(  
`s_id`  VARCHAR(20),  
`c_id`  VARCHAR(20),  
`s_score`  INT(3),  
PRIMARY KEY(`s_id`,`c_id`)  
);
登入後複製
  1. Inserting Test Data into Student Table
INSERT INTO Student VALUES('01', 'John Doe', '1990-01-01', 'Male');  
INSERT INTO Student VALUES('02', 'Jane Smith', '1990-12-21', 'Male');  
INSERT INTO Student VALUES('03', 'Michael Brown', '1990-05-20', 'Male');  
INSERT INTO Student VALUES('04', 'Emily Davis', '1990-08-06', 'Male');  
INSERT INTO Student VALUES('05', 'Lucy Johnson', '1991-12-01', 'Female');  
INSERT INTO Student VALUES('06', 'Sophia Williams', '1992-03-01', 'Female');  
INSERT INTO Student VALUES('07', 'Olivia Taylor', '1989-07-01', 'Female');  
INSERT INTO Student VALUES('08', 'Victoria King', '1990-01-20', 'Female');
登入後複製
  1. Inserting Test Data into Course Table
INSERT INTO Course VALUES('01', 'Literature', '02');  
INSERT INTO Course VALUES('02', 'Mathematics', '01');  
INSERT INTO Course VALUES('03', 'English', '03');
登入後複製
  1. Inserting Test Data into Teacher Table
INSERT INTO Teacher VALUES('01', 'Andrew');  
INSERT INTO Teacher VALUES('02', 'Bethany');  
INSERT INTO Teacher VALUES('03', 'Charlie');
登入後複製
  1. Transcript Test 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('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);
登入後複製

Exercise questions and SQL statements

  1. Retrieve the information and course scores of students who have a higher score in course '01' than in course '02'
SELECT a.*, b.s_score AS '01_score', c.s_score AS '02_score'  
FROM student a  
JOIN score b ON a.s_id = b.s_id AND b.c_id = '01'  
LEFT JOIN score c ON a.s_id = c.s_id AND c.c_id = '02'  
WHERE b.s_score > COALESCE(c.s_score, 0); -- Using COALESCE instead of OR c.c_id = NULL  

-- Alternatively  
SELECT a.*, b.s_score AS '01_score', c.s_score AS '02_score'  
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'  
AND b.s_score > c.s_score;
登入後複製
  1. Retrieve the information and course scores of students who have a lower score in course '01' than in course '02'
SELECT a.*, b.s_score AS '01_score', c.s_score AS '02_score'  
FROM student a  
LEFT JOIN score b ON a.s_id = b.s_id AND b.c_id = '01'  
JOIN score c ON a.s_id = c.s_id AND c.c_id = '02'  
WHERE COALESCE(b.s_score, 0) < c.s_score; -- Using COALESCE for clarity

登入後複製
  1. Retrieve student IDs, names, and average scores for students with an average score of 60 or above
SELECT b.s_id, b.s_name, ROUND(AVG(a.s_score), 2) AS avg_score  
FROM student b  
JOIN score a ON b.s_id = a.s_id  
GROUP BY b.s_id, b.s_name  
HAVING AVG(a.s_score) >= 60;
登入後複製
  1. Retrieve student IDs, names, and average scores for students with an average score below 60 (including those with no scores)
SELECT b.s_id, b.s_name, ROUND(AVG(a.s_score), 2) AS avg_score  
FROM student b  
LEFT JOIN score a ON b.s_id = a.s_id  
GROUP BY b.s_id, b.s_name  
HAVING AVG(a.s_score) < 60  
UNION  
SELECT a.s_id, a.s_name, 0 AS avg_score  
FROM student a  
WHERE a.s_id NOT IN (SELECT DISTINCT s_id FROM score);
登入後複製
  1. Retrieve student IDs, names, total courses selected, and total scores across all courses
SELECT a.s_id, a.s_name, COUNT(b.c_id) AS sum_course, SUM(b.s_score) AS sum_score  
FROM student a  
LEFT JOIN score b ON a.s_id = b.s_id  
GROUP BY a.s_id, a.s_name;
登入後複製
  1. Query the number of teachers with the surname "Smith"
SELECT  COUNT(t_id) FROM teacher WHERE t_name LIKE  'Smith%';
登入後複製
  1. Query the information of students who have taken classes taught by Teacher "John Doe"
SELECT a.*  
FROM student a  
JOIN score b ON a.s_id = b.s_id  
WHERE b.c_id IN (  
    SELECT c_id FROM course  
    WHERE t_id = (  
        SELECT t_id FROM teacher  
        WHERE t_name = 'John Doe'  
    )  
);
登入後複製
  1. Query the information of students who have not taken classes taught by Teacher "John Doe"
SELECT *  
FROM student c  
WHERE c.s_id NOT IN (  
    SELECT a.s_id  
    FROM student a  
    JOIN score b ON a.s_id = b.s_id  
    WHERE b.c_id IN (  
        SELECT a.c_id  
        FROM course a  
        JOIN teacher b ON a.t_id = b.t_id  
        WHERE t_name = 'John Doe'  
    )  
);
登入後複製
  1. Query the information of students who have taken both courses with IDs "Math101" and "Science101"
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 = 'Math101'  
AND c.c_id = 'Science101';
登入後複製
  1. Query the information of students who have taken the course with ID "Math101" but have not taken the course with ID "Science101"
SELECT a.*
FROM student a
WHERE a.s_id IN (SELECT s_id FROM score WHERE c_id = 'Math101')
AND a.s_id NOT IN (SELECT s_id FROM score WHERE c_id = 'Science101');
登入後複製
  1. Query information of students who have not taken all courses
-- @wendiepei's approach
SELECT s.*
FROM student s
LEFT JOIN Score s1 ON s1.s_id = s.s_id
GROUP BY s.s_id
HAVING COUNT(s1.c_id) < (SELECT COUNT(*) FROM course);
-- @k1051785839's approach
SELECT *  
FROM student  
WHERE s_id NOT IN (  
    SELECT s_id   
    FROM score t1    
    GROUP BY s_id   
    HAVING COUNT(*) = (SELECT COUNT(DISTINCT c_id) FROM course)  
);
登入後複製
  1. Query information of students who have taken at least one course in common with student ID '01'
SELECT *   
FROM student   
WHERE s_id IN (  
    SELECT DISTINCT a.s_id   
    FROM score a   
    WHERE a.c_id IN (  
        SELECT c_id   
        FROM score   
        WHERE s_id = '01'  
    )  
);
登入後複製
  1. Query information of students who have taken exactly the same courses as student ID '01'
SELECT
 t3.*
FROM
 (
  SELECT
   s_id,
   group_concat(c_id ORDER BY c_id) group1
  FROM
   score
  WHERE
   s_id &lt;> '01'
  GROUP BY
   s_id
 ) t1
INNER JOIN (
 SELECT
  group_concat(c_id ORDER BY c_id) group2
 FROM
  score
 WHERE
  s_id = '01'
 GROUP BY
  s_id
) t2 ON t1.group1 = t2.group2
INNER JOIN student t3 ON t1.s_id = t3.s_id
登入後複製
  1. Query the names of students who have not taken any course taught by Teacher "Tom"
select a.s_name from student a where a.s_id not in (
    select s_id from score where c_id = 
                (select c_id from course where t_id =(
                    select t_id from teacher where t_name = 'Tom')));
登入後複製
  1. Query student IDs, names, and average scores of students who have failed two or more courses
SELECT a.s_id, a.s_name, ROUND(AVG(b.s_score), 2) AS average_score  
FROM student a  
LEFT JOIN score b ON a.s_id = b.s_id  
WHERE a.s_id IN (  
    SELECT s_id  
    FROM score  
    WHERE s_score < 60  
    GROUP BY s_id  
    HAVING COUNT(*) >= 2  
)  
GROUP BY a.s_id, a.s_name;
登入後複製
  1. Retrieve student information for students who scored less than 60 on course "01", ordered by score in descending order.
SELECT a.*, b.c_id, b.s_score  
FROM student a  
JOIN score b ON a.s_id = b.s_id  
WHERE b.c_id = '01' AND b.s_score < 60  
ORDER BY b.s_score DESC;
登入後複製
  1. Display the scores of all courses and the average score for each student, ordered by their average score from highest to lowest.
SELECT   
    a.s_id,  
    MAX(CASE WHEN c_id = '01' THEN s_score END) AS Chinese,  
    MAX(CASE WHEN c_id = '02' THEN s_score END) AS Math,  
    MAX(CASE WHEN c_id = '03' THEN s_score END) AS English,  
    ROUND(AVG(s_score), 2) AS average_score  
FROM score a  
GROUP BY a.s_id  
ORDER BY average_score DESC;
登入後複製
  1. Query the highest score, lowest score, average score, pass rate, medium rate, good rate, and excellent rate for each course. Display in the following format: Course ID, Course Name, Highest Score, Lowest Score, Average Score, Pass Rate, Medium Rate, Good Rate, Excellent Rate. -- Pass is >=60, Medium is 70-80, Good is 80-90, Excellent is >=90
    SELECT   
        a.c_id,  
        b.c_name,  
        MAX(s_score) AS HighestScore,  
        MIN(s_score) AS LowestScore,  
        ROUND(AVG(s_score), 2) AS AverageScore,  
        ROUND(100 * (SUM(CASE WHEN s_score >= 60 THEN 1 ELSE 0 END) / COUNT(s_score)), 2) AS PassRate,  
        ROUND(100 * (SUM(CASE WHEN s_score BETWEEN 70 AND 80 THEN 1 ELSE 0 END) / COUNT(s_score)), 2) AS MediumRate,  
        ROUND(100 * (SUM(CASE WHEN s_score BETWEEN 80 AND 90 THEN 1 ELSE 0 END) / COUNT(s_score)), 2) AS GoodRate,  
        ROUND(100 * (SUM(CASE WHEN s_score >= 90 THEN 1 ELSE 0 END) / COUNT(s_score)), 2) AS ExcellentRate  
    FROM   
        score a   
    LEFT JOIN   
        course b ON a.c_id = b.c_id   
    GROUP BY   
        a.c_id, b.c_name;
    
    登入後複製
    1. Sort scores by course and display rankings. MySQL does not have a built-in RANK() function, so we'll use variables to simulate it.
    SELECT   
        a.s_id,  
        a.c_id,  
        @rank := IF(@prev_score = a.s_score, @rank, @rank + 1) AS rank_without_ties,  
        @prev_score := a.s_score AS score  
    FROM   
        (SELECT s_id, c_id, s_score FROM score ORDER BY c_id, s_score DESC) a,  
        (SELECT @rank := 0, @prev_score := NULL) r  
    ORDER BY   
        a.c_id, a.rank_without_ties;
    
    登入後複製
    1. Query the total score of each student and rank them
    SELECT   
        a.s_id,  
        @rank := IF(@prev_score = a.sum_score, @rank, @rank + 1) AS rank,  
        @prev_score := a.sum_score AS total_score  
    FROM   
        (SELECT s_id, SUM(s_score) AS sum_score FROM score GROUP BY s_id ORDER BY sum_score DESC) a,  
        (SELECT @rank := 0, @prev_score := NULL) r  
    ORDER BY   
        total_score DESC;
    
    登入後複製
    1. Query the average score of different courses taught by different teachers, sorted from highest to lowest
    SELECT   
        a.t_id,  
        c.t_name,  
        a.c_id,  
        ROUND(AVG(s_score), 2) AS avg_score   
    FROM   
        course a  
    LEFT JOIN   
        score b ON a.c_id = b.c_id   
    LEFT JOIN   
        teacher c ON a.t_id = c.t_id  
    GROUP BY   
        a.c_id, a.t_id, c.t_name   
    ORDER BY   
        avg_score DESC;
    
    登入後複製
    1. Query the information of students who rank second and third in all courses along with their scores
    (SELECT   
        d.*,  
        c.ranking,  
        c.s_score,  
        c.c_id  
    FROM   
        (SELECT   
            s_id,   
            s_score,   
            c_id,   
            @rank := IF(@prev_cid = c_id, @rank + 1, 1) AS ranking,  
            @prev_cid := c_id  
        FROM   
            score,   
            (SELECT @rank := 0, @prev_cid := NULL) AS var_init  
        WHERE   
            c_id = '01'  
        ORDER BY   
            c_id, s_score DESC  
        ) c  
    LEFT JOIN   
        student d ON c.s_id = d.s_id  
    WHERE   
        c.ranking BETWEEN 2 AND 3  
    )  
    UNION  
    (SELECT   
        d.*,  
        c.ranking,  
        c.s_score,  
        c.c_id  
    FROM   
        (SELECT similar structure as above but with c_id = '02' in the WHERE clause) c  
    LEFT JOIN   
        student d ON c.s_id = d.s_id  
    WHERE   
        c.ranking BETWEEN 2 AND 3  
    )  
    UNION  
    (SELECT similar structure as above but with c_id = '03' in the WHERE clause);
    
    登入後複製
    1. Count the number of students in each score range for each subject:
    select distinct f.c_name, a.c_id,
           b.`85-100`, b.Percentage as `[85-100] Percentage`,
           c.`70-85`, c.Percentage as `[70-85] Percentage`,
           d.`60-70`, d.Percentage as `[60-70] Percentage`,
           e.`0-60`, e.Percentage as `[0-60] Percentage`
    from score a
        left join (
            select c_id,
                   SUM(case when s_score > 85 and s_score <= 100 then 1 else 0 end) as `85-100`,
                   ROUND(100*(SUM(case when s_score > 85 and s_score <= 100 then 1 else 0 end)/count(*)),2) as Percentage
            from score GROUP BY c_id
        ) b on a.c_id = b.c_id
        left join (
            select c_id,
                   SUM(case when s_score > 70 and s_score <= 85 then 1 else 0 end) as `70-85`,
                   ROUND(100*(SUM(case when s_score > 70 and s_score <= 85 then 1 else 0 end)/count(*)),2) as Percentage
            from score GROUP BY c_id
        ) c on a.c_id = c.c_id
        left join (
            select c_id,
                   SUM(case when s_score > 60 and s_score <= 70 then 1 else 0 end) as `60-70`,
                   ROUND(100*(SUM(case when s_score > 60 and s_score <= 70 then 1 else 0 end)/count(*)),2) as Percentage
            from score GROUP BY c_id
        ) d on a.c_id = d.c_id
        left join (
            select c_id,
                   SUM(case when s_score >= 0 and s_score <= 60 then 1 else 0 end) as `0-60`,
                   ROUND(100*(SUM(case when s_score >= 0 and s_score <= 60 then 1 else 0 end)/count(*)),2) as Percentage
            from score GROUP BY c_id
        ) e on a.c_id = e.c_id
        left join course f on a.c_id = f.c_id;
    
    登入後複製
    1. Query average scores and their ranks for students:
    select a.s_id,
           @i:=@i+1 as 'No Gaps in Ranking',
           @k:=(case when @avg_score=a.avg_s then @k else @i end) as 'With Gaps in Ranking',
           @avg_score:=avg_s as 'Average Score'
    from (select s_id, ROUND(AVG(s_score),2) as avg_s from score GROUP BY s_id ORDER BY avg_s DESC) a,
         (select @avg_score:=0, @i:=0, @k:=0) b;
    
    登入後複製
    1. Query records of the top three students in each subject:
    select a.s_id, a.c_id, a.s_score from score a 
        left join score b on a.c_id = b.c_id and a.s_score < b.s_score
        group by a.s_id, a.c_id, a.s_score 
        having count(b.s_id) < 3
        order by a.c_id, a.s_score desc;
    
    登入後複製
    1. Query the number of students enrolled in each course:
    select c_id, count(s_id) from score group by c_id;
    
    登入後複製
    1. Query the student ID and name of students who have taken exactly two courses:
    select s_id, s_name from student 
        where s_id in (select s_id from score group by s_id having count(c_id) = 2);
    
    登入後複製
    1. Query the number of male and female students:
    select s_sex, count(s_sex) as Count from student group by s_sex;
    
    登入後複製
    1. Query student information whose name contains the character "Tom":
    select * from student where s_name like '%Tom%';
    
    登入後複製
    1. Query list of students with the same name and gender, and count of such names:
    select a.s_name, a.s_sex, count(*) as Count from student a  
        join student b on a.s_id != b.s_id and a.s_name = b.s_name and a.s_sex = b.s_sex
        group by a.s_name, a.s_sex;
    
    登入後複製
    1. Query list of students born in 1990:
    select s_name from student where s_birth like '1990%';
    
    登入後複製
    1. Query average scores for each course, ordered by average score descending, and course ID ascending if average scores are the same:
    select c_id, round(avg(s_score), 2) as avg_score from score group by c_id order by avg_score desc, c_id asc;
    
    登入後複製
    1. Query student ID, name, and average score of students with average score >= 85:
      select a.s_id, b.s_name, round(avg(a.s_score), 2) as avg_score from score a
          left join student b on a.s_id = b.s_id group by s_id having avg_score >= 85;
      
      登入後複製
      1. Query names and scores of students who scored less than 60 in the course "mathematics":
      select a.s_name, b.s_score from student a 
          join score b on a.s_id = b.s_id 
          where b.c_id = (select c_id from course where c_name = 'mathematics') 
          and b.s_score < 60;
      
      登入後複製
      1. Query course-wise scores and total scores of all students:
      select a.s_id, a.s_name,
          sum(case c.c_name when 'history' then b.s_score else 0 end) as 'history',
          sum(case c.c_name when 'mathematics' then b.s_score else 0 end) as 'mathematics',
          sum(case c.c_name when 'Politics' then b.s_score else 0 end) as 'Politics',
          sum(b.s_score) as 'Total score'
      from student a 
      left join score b on a.s_id = b.s_id 
      left join course c on b.c_id = c.c_id 
      group by a.s_id, a.s_name;
      
      登入後複製
      1. Query names, course names, and scores of students scoring above 70 in any course:
      select a.s_name, b.c_name, c.s_score from student a 
          left join score c on a.s_id = c.s_id 
          left join course b on c.c_id = b.c_id 
          where c.s_score >= 70;
      
      登入後複製
      1. Query courses where students failed:
      select a.s_id, a.c_id, b.c_name, a.s_score from score a 
          left join course b on a.c_id = b.c_id 
          where a.s_score < 60;
      
      登入後複製
      1. Query student ID and name of students who scored above 80 in course '01':
      select a.s_id, b.s_name from score a 
          left join student b on a.s_id = b.s_id 
          where a.c_id = '01' and a.s_score > 80;
      
      登入後複製
      1. Count number of students in each course:
      select count(*) from score group by c_id;
      
      登入後複製
      1. Query information of the highest scoring student in courses taught by teacher "Tom": -- Get teacher ID
      select c_id from course c, teacher d where c.t_id = d.t_id and d.t_name = 'Tom';
      
      登入後複製

      -- Get maximum score (could have ties)

      select max(s_score) from score where c_id = '02';
      
      登入後複製

      -- Get information

      select a.*, b.s_score, b.c_id, c.c_name from student a 
          left join score b on a.s_id = b.s_id 
          left join course c on b.c_id = c.c_id 
          where b.c_id = (select c_id from course c, teacher d where c.t_id = d.t_id and d.t_name = 'Tom')
          and b.s_score in (select max(s_score) from score where c_id = '02');
      
      登入後複製
      1. Query student ID, course ID, and score where different courses have the same score:
      select distinct b.s_id, b.c_id, b.s_score from score a, score b 
          where a.c_id != b.c_id and a.s_score = b.s_score;
      
      登入後複製
      1. Query top two scores for each course:
      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;
      
      登入後複製
      1. Count number of students enrolled in each course (courses with more than 5 students):
      select c_id, count(*) as total from score group by c_id having total > 5 order by total, c_id asc;
      
      登入後複製
      1. Query student IDs who have enrolled in at least two courses:
      select s_id, count(*) as sel from score group by s_id having sel >= 2;
      
      登入後複製
      1. Query information of students who have enrolled in all courses:
      select * from student where s_id in (select s_id from score group by s_id having count(*) = (select count(*) from course));
      
      登入後複製
      1. Query age of each student: -- Calculate age based on birthdate; subtract one if current month/day is before birthdate's month/day
      select s_birth, (date_format(now(), '%Y') - date_format(s_birth, '%Y') - 
          (case when date_format(now(), '%m%d') > date_format(s_birth, '%m%d') then 0 else 1 end)) as age
          from student;
      
      登入後複製
      1. Query students whose birthday is this week:
      select * from student where week(date_format(now(), '%Y%m%d')) = week(s_birth);
      
      登入後複製
      1. Query students whose birthday is next week:
      select * from student where week(date_format(now(), '%Y%m%d')) + 1 = week(s_birth);
      
      登入後複製
      1. Query students whose birthday is this month:
      select * from student where month(date_format(now(), '%Y%m%d')) = month(s_birth);
      
      登入後複製
      1. Query students whose birthday is next month:
      select * from student where month(date_format(now(), '%Y%m%d')) + 1 = month(s_birth);
      
      登入後複製

      OK,If you find this article helpful, feel free to share it with more people.

      If you want to find a SQL tool to practice, you can try our sqlynx, which has a simple interface and is easy to use. https://www.sqlynx.com/download/ Free download

      以上是MySQL 基本精選練習題及答案的詳細內容。更多資訊請關注PHP中文網其他相關文章!

來源:dev.to
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板