求职路不易,希望能给你们帮助,给个赞吧!
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;
- 查询各班级各科成绩总和
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;
- 查询语文成绩大于数学成绩的姓名和归宿班级
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);