tom

Posted on Jul 30

Table Names and Fields（MySQL）

Student Table
Student(s_id, s_name, s_birth, s_sex)
Student ID, Student Name, Date of Birth, Student Gender
Course Table

Course(c_id, c_name, t_id)
Course ID, Course Name, Teacher ID
Teacher Table

Teacher(t_id, t_name)
Teacher ID, Teacher Name
Score Table

Score(s_id, c_id, s_score)
Student ID, Course ID, Score

Test Data - Creating Tables

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`)  
);

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`)  
);

Teacher Table

CREATE TABLE  `Teacher`(  
`t_id`  VARCHAR(20),  
`t_name`  VARCHAR(20) NOT NULL DEFAULT '',  
PRIMARY KEY(`t_id`)  
);

Score Table

CREATE TABLE  `Score`(  
`s_id`  VARCHAR(20),  
`c_id`  VARCHAR(20),  
`s_score`  INT(3),  
PRIMARY KEY(`s_id`,`c_id`)  
);

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

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

Inserting Test Data into Teacher Table

INSERT INTO Teacher VALUES('01', 'Andrew');  
INSERT INTO Teacher VALUES('02', 'Bethany');  
INSERT INTO Teacher VALUES('03', 'Charlie');

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

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;

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

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;

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);

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;

Query the number of teachers with the surname "Smith"

SELECT  COUNT(t_id) FROM teacher WHERE t_name LIKE  'Smith%';

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

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

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

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

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)  
);

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

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

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

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;

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;

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;

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;

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;

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;

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;

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);

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;

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;

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;

Query the number of students enrolled in each course:

select c_id, count(s_id) from score group by c_id;

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);

Query the number of male and female students:

select s_sex, count(s_sex) as Count from student group by s_sex;

Query student information whose name contains the character "Tom":

select * from student where s_name like '%Tom%';

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;

Query list of students born in 1990:

select s_name from student where s_birth like '1990%';

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;

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;

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;

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;

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;

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;

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;

Count number of students in each course:

select count(*) from score group by c_id;

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

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;

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;

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;

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;

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));

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;

Query students whose birthday is this week:

select * from student where week(date_format(now(), '%Y%m%d')) = week(s_birth);

Query students whose birthday is next week:

select * from student where week(date_format(now(), '%Y%m%d')) + 1 = week(s_birth);

Query students whose birthday is this month:

select * from student where month(date_format(now(), '%Y%m%d')) = month(s_birth);

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

DEV Community

Table Names and Fields（MySQL）

Test Data - Creating Tables

Exercise questions and SQL statements

Top comments (0)

Read next

Advanced Wi-Fi Hacking: Cracking WPA3 and Modern Wireless Security

Introduction to Big Data Analysis

Patching the Cracks

Landing Page