基本 MtSQL 精選練習題及答案

表名和欄位（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) 



<ol>
<li>Retrieve student IDs, names, and average scores for students with an average score of 60 or above
</li>
</ol>

<pre class="brush:php;toolbar:false">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) 



<ol>
<li>Retrieve student IDs, names, total courses selected, and total scores across all courses
</li>
</ol>

<pre class="brush:php;toolbar:false">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) 



<ol>
<li>Query information of students who have taken at least one course in common with student ID '01'
</li>
</ol>

<pre class="brush:php;toolbar:false">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 <> '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 = 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 



<ol>
<li>Display the scores of all courses and the average score for each student, ordered by their average score from highest to lowest.
</li>
</ol>

<pre class="brush:php;toolbar:false">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  85 and s_score  70 and s_score  70 and s_score  60 and s_score  60 and s_score = 0 and s_score = 0 and s_score 



<ol>
<li>Query average scores and their ranks for students:
</li>
</ol>

<pre class="brush:php;toolbar:false">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 



<ol>
<li>Query the number of students enrolled in each course:
</li>
</ol>

<pre class="brush:php;toolbar:false">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 



<ol>
<li>Query course-wise scores and total scores of all students:
</li>
</ol>

<pre class="brush:php;toolbar:false">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 



<ol>
<li>Query student ID and name of students who scored above 80 in course '01':
</li>
</ol>

<pre class="brush:php;toolbar:false">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) 



<ol>
<li>Count number of students enrolled in each course (courses with more than 5 students):
</li>
</ol>

<pre class="brush:php;toolbar:false">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

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