The most complete 50 Mysql database query practice questions

Time:2022-8-17

This database query statement is a question for 50 database query exercises on the Internet. Some versions on the Internet are written by oracle statements. Most companies still use the free MySQL database. The following are the MySQL versions, all of which have been verified.

table name and fields

–1. Student table
Student(s#, sname, sage,ssex) – student number, student name, date of birth, student gender
–2. Class Schedule
Course(c#,cname,t#) – – Course ID, Course Name, Teacher ID
–3. Teacher table
Teacher(t#,tname) – teacher number, teacher name
–4. Score sheet
Sc(s#,c#,score) – Student ID, Course ID, Score

Test Data

It is very convenient to do exercises with database visualization tools. It is recommended to use sqlyog. The software icon is a dolphin.

Fill in the local address, user name, password and port in the new connection type to connect directly to mysql.

All test data are as follows:

# --Insert student table test data
INSERT INTO student VALUES('01' , 'Zhao Lei' , '1990-01-01' , 'male');
INSERT INTO student VALUES('02' , 'electronic' , '1990-12-21' , 'male');
INSERT INTO student VALUES('03' , '孙风' , '1990-05-20' , 'male');
INSERT INTO student VALUES('04' , 'Li Yun' , '1990-08-06' , 'male');
INSERT INTO student VALUES('05' , 'Zhou Mei' , '1991-12-01' , 'Female');
INSERT INTO student VALUES('06' , 'Wu Lan' , '1992-03-01' , 'Female');
INSERT INTO student VALUES('07' , 'Zheng Zhu' , '1989-07-01' , 'Female');
INSERT INTO student VALUES('08' , 'Wang Ju' , '1990-01-20' , 'Female');
# --Insert curriculum test data
INSERT INTO course VALUES('01' , 'Chinese' , '02');
INSERT INTO course VALUES('02' , 'mathematics' , '01');
INSERT INTO course VALUES('03' , 'English' , '03');
 
# --Insert teacher table test data
INSERT INTO teacher VALUES('01' , 'Zhang San');
INSERT INTO teacher VALUES('02' , 'Li Si');
INSERT INTO teacher VALUES('03' , 'Wang Wu');
 
# --Insert test data into score sheet
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 grades in the "01" course are higher than those in the "02" course


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 " courses and " 02 " courses exist at the same time


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 there is no " 01 " course but there is a " 02 " course

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

2. Query the student number, student name and average grade of students whose average grade 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 information of students who have grades in the SC table

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

4. Query the student number, student name, total number of courses, and total grades of all courses of all classmates (if there is no grade, it will be displayed as NULL)


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 the information of students with grades

# When the outermost layer is selected, functions cannot be used
# If the two tables have the same field after connecting, then select needs 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. Check the number of teachers surnamed "Li"

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

6. Inquire about the information of students who have studied with teacher "Zhang San"

# Mr. 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. Check the information of students who did not take 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 that is the same as that of the student whose student number is &quot;01&quot;


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 the same course as the classmate &quot;01&quot;


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. Find the names of students who have not taken any course taught by Mr. Zhang San

# Zhang San is 01
# 01 teacher is teaching math, 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. Check the student numbers, names and average grades of students who have 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 information of students whose &quot;01&quot; course score is less than 60, sorted by score in descending order


SELECT `s#`, sname, score FROM 
   student AS a
    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. Displays all students' grades for all courses and grade point average from high to low grade point average

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

# 14. Query the highest, lowest and average scores of each subject:
# Display in the following form: course ID, course name, highest score, lowest score, average score, pass rate, average rate, excellent rate, excellent rate
# Pass is &gt;=60, Moderate is: 70-80, Excellent is: 80-90, Excellent is: &gt;=90
# Request to output the course number and the number of electives. The query results are sorted in descending order by number of people. If the number of people 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,excellent rate,excellent rateFROM 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 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 the scores of each subject, and display the ranking, and keep the ranking vacancy when the Score is repeated

# There is no rank() function in mysql
# This is to keep the ranking when repeating, so the final ranking and the number of people are the same
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
) r 
ORDER BY score DESC) s

# 15.1 Sort by the scores of each subject, and display the ranking, when the Score is repeated, the ranking will be merged

# This is when there are duplicate rankings, it becomes only one ranking, so the number of rankings will decrease
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) r
ORDER BY score DESC

# 16. Query the total score of the students and rank them. If the total score is repeated, the ranking will be 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) r 
ORDER BY sum_score DESC) s

# 16.1 Query the total score of the students and rank them. When the total score is repeated, the vacancy will not be reserved


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 people in each subject and grade: course number, course name, [100-85], [85-70], [70-60], [60-0] and the 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 scores 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 enrolled in 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 out the student number and name of the student who only took two courses


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

# 21. Check the number of boys and girls

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

# 22. Query the information of students whose name contains the word &quot;Wind&quot;

SELECT * FROM student WHERE sname LIKE ‘%风%’

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

SELECT a.*,b. Number of 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&gt;1

# 24. Query the list of students born in 1990

SELECT * FROM student WHERE YEAR(sage) = 1990

# 25. Query the average grades of each course, the results are sorted in descending order of average grades, and if the average grades are the same, they are sorted 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 numbers, names and average grades of all students with an average grade 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 &quot;Mathematics&quot; and whose scores are lower than 60

SELECT a.`s#`,a.sname,b.math, b.score FROM
student a
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. Check the courses and scores of all students (there are cases where students have no grades and no 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. Look up the name, course name and score of any course with a score of 70 or more


SELECT a.`s#`,a.`sname`,a.`sage`,a.`ssex`,b.`c#`,b.score FROM 
student a 
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 number and name of the student whose course number is 01 and whose course score is above 80


SELECT a.`s#`, a.sname ,b.score FROM 
  student a
    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 students with the highest grades and their grades among the students who took the courses taught by the teacher &quot;Zhang San&quot;

SELECT a.`s#`, a.`sname` ,b.`c#`, b.max_score FROM
student a
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. In the case of duplicate scores, query the information of the students with the highest scores and their scores among the students who took courses taught by teacher &quot;Zhang San&quot;

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 a 
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 grades of students with the same grades 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 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 electives for each course (only courses with more than 5 students are counted)

# Request to output the course number and the number of electives. The query results are sorted in descending order by number of people. If the number of people 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 student number 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. Check the age of each student, only by year

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

# 41. Find students who have birthdays 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. Find students with birthdays 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. Find students who have birthdays this month


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

# 44. Find students who have birthdays next month


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

So far, this article about the most complete 50 Mysql database query exercises is introduced. For more related Mysql database query content, please search for previous articles of developpaer or continue to browse the related articles below. I hope you will support developpaer more in the future!

Recommended Today

RabbitMQ study notes

Table of contents Related concepts RabbitMQ Install RabbitMQ core part Hello World Work Queues release confirmation switch fanout exchange (pub/sub) direct exchange topic exchange dead letter queue delay queue Release Confirmation Advanced The switch receives the message confirmation callback Fallback message to producer when switch is not routable Backup switch other idempotency priority queue lazy […]