Soalan Amalan Terpilih MtSQL Penting dengan Jawapan

Nama Jadual dan Medan（MySQL）

1. Meja Pelajar
   Pelajar(s_id, s_name, s_birth, s_sex)
   ID Pelajar, Nama Pelajar, Tarikh Lahir, Jantina Pelajar

2. Meja Kursus
   
   Kursus(c_id, c_name, t_id)
   ID Kursus, Nama Kursus, ID Guru

3. Meja Guru
   
   Guru(t_id, t_name)
   ID Guru, Nama Guru

4. Jadual Skor
   
   Skor(s_id, c_id, s_score)
   ID Pelajar, ID Kursus, Skor

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 <> '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

      Atas ialah kandungan terperinci Soalan Amalan Terpilih MtSQL Penting dengan Jawapan. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!