The most complete 50 Mysql database query exercises

The most complete 50 Mysql database query exercises

This database query statement is one of 50 database query practice questions on the Internet. Some versions on the Internet are written in Oracle statements. Most companies still use the free MySQL database. The following are all MySQL versions, all of which have been verified.

Table name and fields

–1. Student List
Student(s#, sname, sage, ssex) – student number, student name, date of birth, student gender – 2. Course schedule
Course(c#,cname,t#) – –Course number, course name, teacher number – 3.Teacher table
Teacher(t#,tname) – teacher number, teacher name – 4. Grade table
Sc(s#,c#,score) – student number, course number, score

Test Data

It is very convenient to use database visualization tools to do exercises. It is recommended to use sqlyog, and the software icon is a dolphin.

Fill in the local address, username, password and port in the new connection to connect directly to MySQL.

All test data are as follows:

# --Insert test data into the student table INSERT INTO student VALUES('01', '赵雷', '1990-01-01', '男');
INSERT INTO student VALUES('02' , '钱电' , '1990-12-21' , '男');
INSERT INTO student VALUES('03' , '孙风' , '1990-05-20' , '男');
INSERT INTO student VALUES('04' , '李云' , '1990-08-06' , '男');
INSERT INTO student VALUES('05' , '周梅' , '1991-12-01' , '女');
INSERT INTO student VALUES('06' , '吴兰' , '1992-03-01' , '女');
INSERT INTO student VALUES('07' , '郑竹' , '1989-07-01' , '女');
INSERT INTO student VALUES('08' , '王菊' , '1990-01-20' , '女');
# --Insert course test data INSERT INTO course VALUES('01', 'Chinese', '02');
INSERT INTO course VALUES('02', 'Mathematics', '01');
INSERT INTO course VALUES('03', 'English', '03');
 
# --Insert test data into the teacher table INSERT INTO teacher VALUES('01', '张三');
INSERT INTO teacher VALUES('02' , '李四');
INSERT INTO teacher VALUES('03' , '王五');
 
# --Insert test data into the score table INSERT INTO sc VALUES('01', '01', 80);
INSERT INTO sc VALUES('01' , '02' , 90);
INSERT INTO sc VALUES('01' , '03' , 99);
INSERT INTO sc VALUES('02' , '01' , 70);
INSERT INTO sc VALUES('02' , '02' , 60);
INSERT INTO sc VALUES('02' , '03' , 80);
INSERT INTO sc VALUES('03' , '01' , 80);
INSERT INTO sc VALUES('03' , '02' , 80);
INSERT INTO sc VALUES('03' , '03' , 80);
INSERT INTO sc VALUES('04' , '01' , 50);
INSERT INTO sc VALUES('04' , '02' , 30);
INSERT INTO sc VALUES('04' , '03' , 20);
INSERT INTO sc VALUES('05' , '01' , 76);
INSERT INTO sc VALUES('05' , '02' , 87);
INSERT INTO sc VALUES('06' , '01' , 31);
INSERT INTO sc VALUES('06' , '03' , 34);
INSERT INTO sc VALUES('07' , '02' , 89);
INSERT INTO sc VALUES('07' , '03' , 98);

Finally, there are 50 database query exercises, which have been verified and are the MySQL version.

1. Query the information and course scores of students whose course "01" has a higher score than "02"

SELECT * FROM
  (SELECT `s#` AS sno1, `c#` AS cno1, score FROM sc WHERE `c#`=01) a
    LEFT JOIN
  (SELECT `s#` AS sno2, `c#` AS cno2, score FROM sc WHERE `c#`=02) b
    ON a.sno1 = b.sno2
    WHERE a.score > b.score

1.1 Query the situation where both "01" and "02" courses exist

SELECT * FROM
  (SELECT `s#` AS sno1, `c#` AS cno1, score FROM sc WHERE `c#`=01) a
    LEFT JOIN
  (SELECT `s#` AS sno2, `c#` AS cno2, score FROM sc WHERE `c#`=02) b
    ON a.sno1 = b.sno2
    WHERE sno2 IS NOT NULL

1.2 Query the case where the "01" course exists but the "02" course may not exist (displayed as null when it does not exist)

SELECT * FROM
  (SELECT `s#` AS sno1, `c#` AS cno1, score FROM sc WHERE `c#`=01) a
    LEFT JOIN
  (SELECT `s#` AS sno2, `c#` AS cno2, score FROM sc WHERE `c#`=02) b
    ON a.sno1 = b.sno2

1.3 Query the situation where the course "01" does not exist but the course "02" exists

SELECT * FROM
sc WHERE `c#`='02' AND `s#` NOT IN (SELECT `s#` FROM sc WHERE `c#`='01')

2. Query the student ID, name and average score of students whose average score is greater than or equal to 60 points

SELECT a.`s#`,b.`sname`, a.avg_score FROM 
  (SELECT `s#` ,AVG(score) AS avg_score FROM sc GROUP BY `s#`) AS a
    LEFT JOIN student AS b
    ON a.`s#` = b.`s#`
    WHERE a.avg_score >=60

3. Query the student information with grades in the SC table

SELECT * FROM student WHERE `s#` IN (SELECT DISTINCT `s#` FROM sc)

4. Query the student ID, name, total number of courses, and total scores of all courses for all students (null will be displayed if no score is available)

SELECT `s#` ,sname , course_num , score_sum FROM 
  (SELECT `s#`, sname FROM student ) AS a
    LEFT JOIN
  (SELECT `s#` AS sno ,COUNT(`c#`) AS course_num ,SUM(score) AS score_sum FROM sc GROUP BY sno) AS b
    ON a.`s#` = b.sno

4.1 Check student information with grades

# When selecting in the outermost layer, you cannot use functions
# If the two tables have the same fields after being connected, select will need to rename one of the fields

SELECT `s#` ,sname , course_num , score_sum FROM 
  (SELECT `s#`, sname FROM student ) AS a
    LEFT JOIN
  (SELECT `s#` AS sno ,COUNT(`c#`) AS course_num ,SUM(score) AS score_sum FROM sc GROUP BY sno) AS b
    ON a.`s#` = b.sno
    WHERE course_num IS NOT NULL

5. Query the number of teachers with the surname "Li"

SELECT COUNT(*) FROM teacher WHERE tname LIKE '李%'

6. Query the information of students who have taken the course taught by teacher "Zhang San"

# Teacher Zhang San is No. 01 SELECT * FROM student WHERE `s#` IN 
  (SELECT `s#` FROM sc WHERE `c#` =
    (SELECT `c#` FROM course WHERE `t#` = 
      (SELECT `t#` FROM teacher WHERE tname='张三')))

# 7. Query the information of students who have not completed all courses

SELECT `s#`,COUNT(`c#`) AS course_num FROM sc GROUP BY `s#`
HAVING course_num < (SELECT COUNT(*) FROM course)

# 8. Query the information of students who have at least one course in the same class as the student with student number "01"

SELECT * FROM student WHERE `s#` IN 
  (SELECT DISTINCT `s#` FROM sc WHERE `c#` IN
    (SELECT `c#` FROM sc WHERE `s#`=01))
  AND `s#` != 01

# 9. Query the information of other students who have taken the same course as student No. "01"

SELECT `s#` FROM 
  (SELECT * FROM sc 
    LEFT JOIN 
  (SELECT `c#` AS cno FROM sc WHERE `s#` =01) a
    ON sc.`c#` = a.cno) AS b
GROUP BY `s#`    
HAVING COUNT(b.`s#`) = (SELECT COUNT(`c#`) AS cno FROM sc WHERE `s#` =01)

# 10. Query the names of students who have not studied any course taught by teacher "Zhang San"

# Zhang San is 01
# 01 teacher teaches mathematics, c# is 02
SELECT * FROM student WHERE `s#` NOT IN 
  (SELECT DISTINCT `s#` FROM sc WHERE `c#` IN 
    (SELECT `c#` FROM course WHERE `t#` IN 
      (SELECT `t#` FROM teacher WHERE tname = '张三')))

# 11. Query the student ID, name and average score of students who failed two or more courses

SELECT `s#`, sname, avg_score FROM 
  (SELECT `s#`, sname FROM student WHERE `s#` IN
    (SELECT a.`s#` FROM 
      (SELECT `s#`,COUNT(`c#`) AS num FROM sc WHERE score <60 GROUP BY `s#`) a
      WHERE num >= 2)) AS b
    LEFT JOIN
  (SELECT `s#` AS sno ,AVG(score) AS avg_score FROM sc GROUP BY `s#`) AS c
    ON b.`s#` = c.sno

# 12. Retrieve the student information for the course "01" whose score is less than 60, sorted by score in descending order

SELECT `s#`, sname, score FROM 
   student AS
    LEFT JOIN 
  (SELECT `s#` AS sno,`c#`,score FROM sc WHERE `c#`= 01 AND score <60 )b
    ON a.`s#` = b.sno
  WHERE score IS NOT NULL
  ORDER BY score DESC

# 13. Display the grades and average grades of all courses for all students from high to low by average grade

SELECT `s#` ,AVG(score) AS avg_score FROM sc GROUP BY `s#` ORDER BY avg_score DESC

# 14. Query the highest score, lowest score and average score of each subject:
# Display in the following format: course ID, course name, highest score, lowest score, average score, pass rate, medium rate, good rate, excellent rate
# Passing score is >=60, medium score is 70-80, good score is 80-90, and excellent score is >=90
# Requires output of course number and number of electives. The query results are sorted in descending order by number of students. If the number of students is the same, they are sorted in ascending order by course number.

SELECT DISTINCT a.`c#`,cname,highest score,lowest score,average score,pass rate,medium rate,good rate,excellent rate FROM sc a
LEFT JOIN course ON a.`c#`=course.`c#`
LEFT JOIN (SELECT `c#`, MAX(score)highest score, MIN(score)lowest score, AVG(score)average score FROM sc GROUP BY `c#`)b ON a.`c#`=b.`c#`
LEFT JOIN (SELECT `c#`, ROUND( r1 /cnt * 100, 2 ) AS pass rate FROM
  (SELECT `c#`, (SUM(CASE WHEN score >=60 THEN 1 ELSE 0 END)*1.00) AS r1 , COUNT(*) AS cnt FROM sc GROUP BY `c#`) c1) c ON a.`c#`=c.`c#`
LEFT JOIN (SELECT `c#`, ROUND( r2 /cnt * 100, 2 ) AS medium rate FROM
  (SELECT `c#`, (SUM(CASE WHEN score >=70 AND score<80 THEN 1 ELSE 0 END)*1.00) AS r2 , COUNT(*) AS cnt FROM sc GROUP BY `c#`) d1) d ON a.`c#`=d.`c#`  
LEFT JOIN (SELECT `c#`, ROUND( r3 /cnt * 100, 2 ) AS good/good rate FROM
  (SELECT `c#`, (SUM(CASE WHEN score >=80 AND score<90 THEN 1 ELSE 0 END)*1.00) AS r3 , COUNT(*) AS cnt FROM sc GROUP BY `c#`) e1) e ON a.`c#`=e.`c#`
LEFT JOIN (SELECT `c#`, ROUND( r4 /cnt * 100, 2 ) AS excellent rate FROM
  (SELECT `c#`, (SUM(CASE WHEN score >=90 THEN 1 ELSE 0 END)*1.00) AS r4 , COUNT(*) AS cnt FROM sc GROUP BY `c#`) f1) f ON a.`c#`=f.`c#`

# 15. Sort by scores in each subject and display the ranking. If the score is repeated, leave the rank vacant.

# There is no rank() function in mysql # This method keeps the ranking when repeated, so the final ranking is the same as the number of people SELECT `s#`, `c#`, score, rank FROM
(SELECT `s#`, `c#`, score,
@currank := IF(@prevrank = score, @currank, @incrank) AS rank, 
@incrank := @incrank + 1, 
@prevrank := score
FROM sc , (
SELECT @currank := 0, @prevrank := NULL, @incrank := 1
) 
ORDER BY score DESC) s

# 15.1 Sort by scores of each subject and display the ranking. Merge the ranking when the score is repeated

# This is when there are duplicate ranks, there will be only one rank, so the number of ranks will be reduced SELECT `s#`, `c#`, score, 
CASE 
WHEN @prevrank = score THEN @currank 
WHEN @prevrank := score THEN @currank := @currank + 1
END AS rank
FROM sc, 
(SELECT @currank := 0, @prevrank := NULL)
ORDER BY score DESC

# 16. Query the total scores of students and rank them. If the total scores are repeated, leave the rank vacant.

# No need to add table alias after from SELECT `s#`, sum_score, rank FROM
(SELECT `s#`, sum_score,
@currank := IF(@prevrank = sum_score, @currank, @incrank) AS rank, 
@incrank := @incrank + 1, 
@prevrank := sum_score
FROM 
(SELECT `s#`, SUM(score) AS sum_score FROM sc GROUP BY `s#`) c , 
(SELECT @currank := 0, @prevrank := NULL, @incrank := 1) 
ORDER BY sum_score DESC) s

# 16.1 Query the total scores of students and rank them. If the total scores are repeated, no vacancies will be left.

SELECT c.*,
CASE 
WHEN @prevrank = c.sum_score THEN @currank 
WHEN @prevrank := c.sum_score THEN @currank := @currank + 1
END AS rank
FROM 
(SELECT a.`s#`,a.sname,SUM(score) AS sum_score
FROM (student AS a RIGHT JOIN sc AS b ON a.`s#` = b.`s#`) 
GROUP BY a.`s#` ) c , 
(SELECT @currank := 0 , @prevrank :=NULL ) d 
ORDER BY sum_score DESC

# 17. Count the number of students in each score range for each subject: course number, course name, [100-85], [85-70], [70-60], [60-0] and percentage

SELECT a.`c#` , b.cname, 
  SUM(CASE WHEN score >=85 AND score <=100 THEN 1 ELSE 0 END ) '[100-85]',
  SUM(CASE WHEN score >=85 AND score <=100 THEN 1 ELSE 0 END )*1.00/COUNT(*) AS '[100-85]percent',
    SUM(CASE WHEN score < 85 AND score >= 70 THEN 1 ELSE 0 END ) '(85-70]',
    SUM(CASE WHEN score < 85 AND score >= 70 THEN 1 ELSE 0 END )*1.00/COUNT(*) AS '(85-70]percent',
    SUM(CASE WHEN score < 70 AND score >= 60 THEN 1 ELSE 0 END ) '(70-60]',
    SUM(CASE WHEN score < 70 AND score >= 60 THEN 1 ELSE 0 END )*1.00/COUNT(*) AS '(85-70]percent',
    SUM(CASE WHEN score < 60 AND score >= 0 THEN 1 ELSE 0 END ) '(60-0]',
    SUM(CASE WHEN score < 60 AND score >= 0 THEN 1 ELSE 0 END )*1.00/COUNT(*) AS '(85-70]percent',
    COUNT(*) AS counts
FROM sc a LEFT JOIN course b ON a.`c#` = b.`c#`
GROUP BY `c#`

# 18. Query the records of the top three students in each subject

SELECT * FROM sc a WHERE 
  (SELECT COUNT(*) FROM sc WHERE `c#`=a.`c#` AND score>a.score)<3 
  ORDER BY a.`c#`, a.score DESC;

# 19. Query the number of students who have chosen each course

SELECT `c#`, COUNT(`s#`) FROM 
(SELECT `s#`,`c#` FROM sc ORDER BY `c#`)a
GROUP BY `c#` 

SELECT a.`c#` , b.cname ,COUNT(*) AS num FROM sc a LEFT JOIN course b ON a.`c#` = b.`c#`
GROUP BY a.`c#`;

# 20. Find the student ID and name of students who only take two courses

SELECT a.`s#`, a.sname ,cnt FROM 
student
LEFT JOIN 
(SELECT `s#`,COUNT(`c#`) AS cnt FROM sc GROUP BY `s#`) b
ON a.`s#`=b.`s#`
WHERE cnt=2

# 21. Query the number of boys and girls

SELECT ssex,COUNT(ssex) FROM student GROUP BY ssex

# 22. Query the student information whose name contains the word "风"

SELECT * FROM student WHERE sname LIKE '%风%'

# 23. Query the list of students with the same name and gender, and count the number of students with the same name

SELECT a.*,b.number of people with the same name FROM student a
LEFT JOIN (SELECT sname,ssex,COUNT(*) AS number of people with the same name FROM student GROUP BY sname,ssex)b 
ON a.sname=b.sname AND a.ssex=b.ssex
WHERE b. Number of people with the same name > 1

# 24. Query the list of students born in 1990

SELECT * FROM student WHERE YEAR(sage) = 1990

# 25. Query the average score of each course, and sort the results in descending order by average score. If the average scores are the same, sort them in ascending order by course number.

SELECT `c#`, ROUND(AVG(score),2) AS avg_score FROM sc GROUP BY `c#` ORDER BY `c#` ASC

# 26. Query the student ID, name and average score of all students whose average score is greater than or equal to 85

SELECT c.`s#`,sname ,avg_score FROM
(student c 
LEFT JOIN
(SELECT `s#`, avg_score FROM 
(SELECT `s#` ,ROUND(AVG(score),2) AS avg_score FROM sc 
GROUP BY `s#` ORDER BY avg_score DESC)a 
WHERE avg_score >=85) b
ON c.`s#` =b.`s#`)
WHERE avg_score IS NOT NULL

# 27. Query the names and scores of students whose course name is "Mathematics" and whose scores are less than 60

SELECT a.`s#`,a.sname,b.math, b.score FROM
student
LEFT JOIN
(SELECT `s#`,`c#` AS math ,score FROM sc WHERE `c#` IN 
  (SELECT `c#` FROM course WHERE cname = '数学')
  AND sc.score <60) b
ON a.`s#`=b.`s#`
WHERE b.score IS NOT NULL

# 28. Query the courses and scores of all students (there are students who have no scores and have not chosen courses)

SELECT a.`s#`,a.`sname`,a.`sage`,a.`ssex`,b.`c#`,b.score FROM 
student a LEFT JOIN sc b ON a.`s#` = b.`s#`
LEFT JOIN course c ON c.`c#` = b.`c#`

# 29. Query the name, course name and score of any course with a score above 70

SELECT a.`s#`,a.`sname`,a.`sage`,a.`ssex`,b.`c#`,b.score FROM 
student 
LEFT JOIN 
(SELECT `s#`,`c#`,score FROM sc WHERE score >70) b ON a.`s#`=b.`s#`
LEFT JOIN course c 
ON c.`c#`=b.`c#`
WHERE score IS NOT NULL

# 30. Check for failed courses

SELECT * FROM sc WHERE score < 60

# 31. Query the student ID and name of the student whose course number is 01 and whose course score is above 80 points

SELECT a.`s#`, a.sname ,b.score FROM 
  student
    LEFT JOIN
  (SELECT * FROM sc WHERE `c#`='01' AND score >= 80) b
    ON a.`s#` = b.`s#`
  WHERE score IS NOT NULL

# 32. Find the number of students in each course

SELECT `c#`,COUNT(`c#`) FROM sc GROUP BY `c#`

# 33. The grades are not repeated. Query the information of the student with the highest grade among the students who have chosen the course taught by teacher "Zhang San" and their grades

SELECT a.`s#`, a.`sname` ,b.`c#`, b.max_score FROM
student
LEFT JOIN
(SELECT `s#` AS sid,`c#` ,MAX(score) AS max_score FROM sc WHERE `c#` IN 
  (SELECT `c#` FROM course WHERE `t#` IN 
    (SELECT `t#` FROM teacher WHERE tname = '张三'))) b
ON a.`s#`=b.sid
WHERE max_score IS NOT NULL

# 34. If there are duplicate scores, query the information of the student with the highest score among the students who have chosen the course taught by teacher "Zhang San" and their scores

SELECT * FROM
(SELECT dd.*,
CASE 
WHEN @prevrank = dd.score THEN @currank 
WHEN @prevrank := dd.score THEN @currank := @currank + 1
END AS rank
 FROM (SELECT a.*,b.score FROM
student 
LEFT JOIN sc b ON a.`s#` = b.`s#`
LEFT JOIN course c ON b.`c#` = c.`c#`
LEFT JOIN teacher d ON c.`t#` = d.`t#` WHERE d.tname = '张三' ) dd,(SELECT @currank := 0 , @prevrank :=NULL ) ff 
ORDER BY score DESC) AS dddddddd
WHERE rank = 1;

# 35. Query the student ID, course ID, and student score of students with the same scores in different courses

SELECT DISTINCT a.`s#`, a.`c#`, a.score FROM sc AS a JOIN sc AS b 
WHERE a.`c#` != b.`c#` AND a.score = b.score AND a.`s#` != b.`s#`
ORDER BY a.`s#`, a.`c#`, a.score

# 36. Find the top two students with the best grades in each subject

# This question is the same as question 18 SELECT * FROM sc a WHERE 
  (SELECT COUNT(*) FROM sc WHERE `c#`=a.`c#` AND score>a.score)<2 
  ORDER BY a.`c#`, a.score DESC;

# 37. Count the number of students taking each course (only courses with more than 5 students are counted)

# Requires output of course number and number of electives. The query results are sorted in descending order by number of students. If the number of students is the same, they are sorted in ascending order by course number. SELECT a.`c#`, COUNT(*) AS num FROM 
course a LEFT JOIN sc b ON a.`c#` = b.`c#`
GROUP BY a.`c#` HAVING num > 5
ORDER BY num,a.`c#`

# 38. Retrieve the student IDs of students who have taken at least two courses

SELECT DISTINCT `s#`, COUNT(`c#`) AS num FROM sc GROUP BY `s#` HAVING num >= 2

# 39. Query the information of students who have taken all courses

SELECT * FROM 
  (SELECT `s#`,COUNT(*) AS num FROM sc GROUP BY `s#` ) b
  WHERE num = (SELECT COUNT(*) FROM course)

# 40. Query the age of each student, only by year

SELECT *, YEAR(NOW()) - YEAR(sage) AS age FROM student

# 41. Query students whose birthdays are this week

SELECT * FROM
(SELECT * , WEEK(sage), MONTH(sage), DAY(sage),
WEEK(STR_TO_DATE(CONCAT_WS(',',YEAR(NOW()),MONTH(sage),DAY(sage)),'%y,%m,%d')) AS w FROM student) a
WHERE w = WEEK(NOW())

# 42. Query students whose birthdays are next week

SELECT * FROM
(SELECT * , WEEK(sage), MONTH(sage), DAY(sage), WEEK(NOW()),
WEEK(STR_TO_DATE(CONCAT_WS(',',YEAR(NOW()),MONTH(sage),DAY(sage)),'%y,%m,%d')) AS w FROM student) a
WHERE w + 2 = WEEK(NOW())

# 43. Query students whose birthdays are this month

SELECT * , MONTH(sage),MONTH(NOW()) FROM student
WHERE MONTH(sage) = MONTH(NOW())

# 44. Query students whose birthdays are next month

SELECT * , MONTH(sage),MONTH(NOW()) FROM student
WHERE MONTH(sage) = MONTH(NOW()) + 1

This is the end of this article about the most complete 50 MySQL database query exercises. For more relevant MySQL database query content, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • Mysql Sql statement exercises (50 questions)

<<:  Implementation of converting between underline and camel case in js (multiple methods)

>>:  Analysis of the principle of Rabbitmq heartbea heartbeat detection mechanism

Recommend

Detailed explanation of CSS counter related attributes learning

The CSS counter attribute is supported by almost ...

How to use crontab to add scheduled tasks in Linux

Preface The Linux system is controlled by the sys...

Linux type version memory disk query command introduction

1. First, let’s have a general introduction to th...

How to start jar package and run it in the background in Linux

The Linux command to run the jar package is as fo...

MySQL 8.0.18 installation and configuration graphic tutorial

Learning objectives: Learn to use Windows system ...

Implementing Markdown rendering in Vue single-page application

When rendering Markdown before, I used the previe...

Implementation of mysql data type conversion

1. Problem There is a table as shown below, we ne...

How to install and deploy gitlab server on centos7

I am using centos 7 64bit system here. I have tri...

How to solve the background tiling and border breaking of CSS style div or li in IE6

The solution to the background tiling or border br...

How to write asynchronous tasks in modern JavaScript

Preface In this article, we'll explore the ev...

How to configure anti-hotlinking for nginx website service (recommended)

1. Principle of Hotlinking 1.1 Web page preparati...

Tutorial on installing mysql5.7.18 on mac os10.12

I searched the entire web and found all kinds of ...

HTML+CSS merge table border sample code

When we add borders to table and td tags, double ...

Summary of how to use the MySQL authorization command grant

How to use the MySQL authorization command grant:...

Detailed explanation of mkdir command in Linux learning

Table of contents Preface 1. Basic knowledge of f...