Essential MtSQL 50 Selected Practice Questions with Answers

Claudio Ctin1 month ago37 mins

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

Please follow and like us:

Stiri similare

IaC Security Analysis: Checkov vs. tfsec vs. Terrascan – A Comparative Evaluation

Claudio Ctin2 hours ago2 hours ago

Traditional, manual security processes can’t keep up with the speed of modern development, which leaves systems vulnerable to attacks. That’s where Security as Code (SaC) comes in. SaC automates security checks and policies, making them an integral part of the development pipeline. This ensures that security is built into every step without slowing down progress….

Terraform – Overview

Claudio Ctin2 hours ago2 hours ago

What is Terraform? 🤔 Terraform is an open-source, cloud-agnostic, one of the most popular Infrastructure-as-code (IaC) tool developed by HashiCorp. It is used by DevOps teams to automate infrastructure tasks such as provisioning of your cloud resources. Terraform supported immutable infrastructure, a declarative language, a masterless and agentless architecture, and had a large community and…