Some of the questions are consistent with those written by seniors, and some have been adjusted based on my own understanding.
refer to:
30sql_26~30_bilibili_bilibili
30 classic SQL written test questions and their answers analysis_sql written test questions and answers_Ma Yu’s blog-CSDN blog
Create data table
It’s slightly different from the online version, I changed a few data
create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex nvarchar(10)); insert into Student values('01' , 'Zhao Lei' , '1990-01-01' , 'Male'); insert into Student values('02' , 'Zhao Lei' , '1990-12-21' , 'Male'); insert into Student values('03' , 'Sun Feng' , '1990-05-20' , 'Male'); insert into Student values('04' , 'Li Yun' , '1990-08-26' , '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-09-01' , 'Female'); insert into Student values('08' , 'Wang Ju' , '1990-01-20' , 'Female'); create table Course(cid varchar(10),cname varchar(10),tid varchar(10)); insert into Course values('01', 'Chinese', '02'); insert into Course values('02' , 'Math' , '01'); insert into Course values('03' , 'English' , '03'); create table Teacher(tid varchar(10),tname varchar(10)); insert into Teacher values('01' , 'Zhang San'); insert into Teacher values('02' , '李思'); insert into Teacher values('03' , '王五'); create table SC(sid varchar(10),cid varchar(10),score decimal(18,1)); 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);
1. Query the student numbers and names of students whose grades in course 01 are higher than those in course 02
# Query the student numbers and names of students whose scores in course 01 are higher than those in course 02 select st.sid, st.sname from (select sc.sid, ifnull(sum(case sc.cid when '01' then sc.score end), 0) as c1_scorce, ifnull(sum(case sc.cid when '02' then sc.score end), 0) as c2_scorce from sc group by sc.sid having c1_scorce>c2_scorce and c1_scorce != 0 and c2_scorce != 0) sc join student st on sc.sid = st.sid;
2. Query the student ID and average score of students whose average score is greater than 60
# Query the student number and average score of students whose average score is greater than 60 select sid, avg(score) from sc group by sid having avg(score)>60
3. Query each student’s student ID number, name, number of courses taken, and total score
The first solution:
# Query each student’s student number, name, number of courses selected, and total score select s.sid, s.sname, count(sc.cid),sum(sc.score) from student s left join sc on s.sid = sc.sid group by sc.sid;
Second solution:
select t1.sid, t2.sname, t1.num_c, t1.sum_s from ( select sid, count(cid) num_c, sum(score) sum_s from sc group by sid ) t1 join student t2 on t1.sid = t2.sid;
4. Query the number of teachers with the surname “Li”
# Query the number of teachers with the surname "Li" select count(1) from teacher where tname like '李%';
5. Query the student numbers and names of students who have not taken the course of “Zhang San”
Perfect answer:
select sid, sname from student where sid not in( select sc.sid from course c left join teacher t on c.cid = t.tid left join sc on c.cid = sc.cid where t.tname = 'Zhang San');
The writing method given by the teacher. But it does not apply when one teacher teaches multiple courses
Reference: 30sql_26~30_bilibili_bilibili
select sid, sname from student where sid not in ( select sc.sid from teacher t left join course c on t.tid = c.tid left join sc on sc.cid = c.cid where t.tname = 'Zhang San' )
6. Query students who have studied course 01 and course 02
# Query students who have studied course 01 and course 02 select sid from sc where cid in ('01', '02') group by sid having count(sid) = 2;
7. Query the student numbers and names of students whose scores are less than 60 points in each course
# Query the student numbers and names of students whose scores in each course are less than 60 points select sid, sname from student where sid in ( select sid from sc group by sid having max(score) < 60);
8. Query the student numbers and names of students who have not taken all courses
# Query the student number and name of students who have not taken all courses select sid, sname from student where sid not in( select sid from sc group by sid having count(cid) = (select count(cid) from course));
9. Query the student number and name of students who have at least one course that is the same as the student with student number “01”
# Query the student ID and name of students who have at least one course that is the same as the student with student ID "01" select distinct s.sid, s.sname from sc join student s on s.sid = sc.sid where sc.cid in (select cid from sc where sid = '01');
9 Expansion. Query the student number and name of students who have at least two courses that are the same as the student with student number “01”
select s.sid, s.sname from sc left join student s on s.sid = sc.sid group by s.sid having count(if(sc.cid in (select cid from sc where sid = '01'), 1, null)) > 1;
10. Query the student numbers and names of other students studying the same course as the student with student number 01’
The first:
select s.sid, s.sname from (select cid from sc where sid = '01') t1 left join sc on t1.cid = sc.cid left join student s on sc.sid = s.sid where s.sid != '01' group by sc.sid having count(sc.cid) = (select count(cid) from sc where sid = '01');
Second type: Use group_concat to implement
select s.sid, s.sname from (select t1.sid from ( select sid, group_concat(cid order by cid) c from sc group by sid ) t1 where t1.c = (select group_concat(sc.cid order by sc.cid) from sc where sc.sid = '01') )t2 left join student s on t2.sid = s.sid;
11. Query the average grade of the courses taught by “Zhang San”
select cid, avg(score) from sc where cid in (select c.cid from teacher t join course c on t.tid = c.tid where t.tname = 'Zhang San') group by cid;
12. Query the names of students who have not studied any of the courses taught by “Zhang San”
select s.sname from students where s.sid not in (select sc.sid from teacher t join course c on t.tid = c.cid join sc on c.cid = sc.cid where t.tname = 'Zhang San');
13. Query the student number, name, and average grade of students who failed two or more courses
select s.sid, avg(sc.score) from student s inner join sc on s.sid = sc.sid group by sid having count(if(score<60,1,null)) > 1;
14. Query the course number, course name, highest grade, lowest grade, average grade, and passing rate of each course
select c.cid, c.cname, max(sc.score), min(sc.score), avg(sc.score), count(if(sc.score >60, 1, null))/count (score) pass_rate from course c join sc on c.cid = sc.cid group by c.cid ;
15. Query the employee number of each teacher, the average grade of the courses taught, and sort in descending order by the average grade of the courses taught
select t.tid, avg(score) avg_score from teacher t inner join course c on t.tid = c.tid inner join sc on c.cid = sc.cid group by c.cid order by avg_score desc;
16. Query the student IDs, course IDs, course grades, and rankings of students ranked 2nd and 3rd in each course
Note: Due to issues with your own mysql version, the following statements using the rank() function are not verified. You can verify by yourself.
select t1.sid, t1.cid, t1.score, t1.rank_num from (select sc.sid, sc.cid, sc.score, rank() over(partition by sc.cid order by sc.score desc) as rank_num from sc) t1 where t1.rank_num in (2, 3);
17. Query the percentage of students in each course in different grade ranges. The grade ranges are [85-100], [70-85], [60-70], [0-60]
select count(if(score between 85 and 100, 1, null))/count(*) as '85-100', count(if(score between 70 and 85, 1, null))/count(*) as '70-85', count(if(score between 60 and 70, 1, null))/count(*) as '60-70', count(if(score between 0 and 60, 1, null))/count(*) as '0-60' from sc group by cid;
18. Query each student’s student number, average grade, and average grade ranking
select t1.*, rank() over(order by t1.avg_score desc) as ranking from ( select sc.sid, avg(sc.score) as avg_score from sc group by sid ) t1;
19. Query the course number, student number, grade ranking, and course grade of the top three grades of each course
select t1.cid, t1.sid, t1.ranking, t1.score from ( select *, rank() over(partition by cid order by score desc) as ranking from sc ) t1 where t1.ranking between 1 and 3;
20. Query the number of boys and girls among all students
select ssex, count(1) from student group by ssex;
21. Query the student number and name of students whose names contain the word “风”
select sid, sname from student where sname like '%风%';
22. Query the name, gender, and number of students with the same name and same sex
select sname, ssex, count(sname) from student group by sname, ssex having count(sname) > 1;
23. Query the student number and date of birth of students born in 1990
the first method:
select sid, sage from student where year(sage) = '1990';
The second method:
select sid, sage from student where extract(year from sage)= '1990';
24. Query the name and course grade of the student with the highest grade among all the students who took the course taught by “Zhang San”
The first one does not use window functions and is relatively simple.
select s.sname, t1.score from( select sc.sid, sc.score from teacher t left join course c on t.tid = c.tid left join sc on c.cid = sc.cid where t.tname = 'Zhang San' order by score desc limit 1) t1 left join student s on t1.sid = s.sid;
The second way is to use the window function
select s.sname, t1.score from ( select sc.sid, sc.score, rank() over(order by sc.score) as ranking from teacher t left join course c on t.tid = c.tid left join sc on c.cid = sc.cid where t.tname = 'Zhang San')t1 join student s where t1.ranking = 1;
25. Query the student numbers of students who have taken all courses
select sid from sc group by sid having count(sc.cid) = (select count(cid) from course);
26. Query the student number and age of each student
select sid , year(curdate()) - year(sage) age from student;
27. Query the student ID number of students whose birthdays are this week
select sid from student where weekofyear(date_add(sage, interval (year(curdate()) - year(sage)) year)) = weekofyear(curdate());
28. Query the student ID number of students whose birthdays are next week
select sid from student where weekofyear(date_add(sage, interval (year(curdate()) - year(sage)) year)) = weekofyear(date_add(curdate(), interval 1 week));
29. Query the student number of students whose birthday is this month
select sid from student where month(sage) = month(curdate());
30. Query the student numbers of students whose birthdays are next month
select sid from student where month(sage) = month(date_add(curdate(), interval 1 month));