30 classic SQL questions (including perfect answers to birthday questions)

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