mysql常规面试题

求职路不易，希望能给你们帮助，给个赞吧！
create database school;
use school;
create table class
( class_no varchar(10),
st_no varchar(10),
st_name varchar(20));
insert into class
values (“c1”,“st1”,“sam”),
(“c1”,“st2”,“lucy”),
(“c2”,“st3”,“jean”),
(“c2”,“st5”,“lily”);
insert into class
values (“c2”,“st4”,“lock”);
select * from class;

create table sc
(st_no varchar(10),
subject_no int(10),
score int(10));

insert into sc
values
(“st1”,1,72),
(“st1”,2,89),
(“st1”,3,78),
(“st3”,1,87),
(“st3”,3,89),
(“st4”,1,98),
(“st4”,2,94);

select * from sc;

create table couse
(subject_no int(10),
subject_name varchar(20));

insert into couse
values
(1,“Chinses”),
(2,“mathmatics”),
(3,“English”);

select * from couse;

1. 查询各班级各科成绩总和
   select class_no,subject_name,sum(score) from class a
   inner join sc b on a.st_no=b.st_no
   inner join couse c on b.subject_no=c.subject_no
   group by class_no,subject_name;
   

2.查询每个班级语文成绩大于85分人数人数
select class_no,count(st_no) 人数 from class where st_no in
(select st_no from sc a left join couse b on a.subject_no=b.subject_no
where score>85 and subject_name=“Chinses”)
group by class_no;

3. 查询语文成绩大于数学成绩的姓名和归宿班级
   select st_name,st_no from class where st_no in
   (select t1.st_no from
   (select a.st_no,score from sc a left join couse b on a.subject_no=b .subject_no
   where subject_name=“Chinses” ) t1
   inner join
   (select a.st_no,score from sc a left join couse b on a.subject_no=b .subject_no
   where subject_name=“mathmatics” ) t2 on t1.st_no=t2.st_no and t1.score>t2.score);