SQL练习

面试过程中大家经常遇到求“每个年级不同科目的平均分”，如何写sql呢？今天我带大家来写一次，面试就不怕啦！

创建学生表信息：

create table student(id varchar(20),name varchar(20),class int,primary key(id));
1

插入学生表信息：

insert into Student values('01' , '赵雷',1);
insert into Student values('02' , '钱电',1);
insert into Student values('03' , '孙风',2);
insert into Student values('04' , '李云',2);
insert into Student values('05' , '周梅',2);
insert into Student values('06' , '吴兰',3);
insert into Student values('07' , '郑竹',4) ;
insert into Student values('08' , '王菊',4);
1
2
3
4
5
6
7
8

创建课程表：

create table course(cid int,cname varchar(20));
alter table course add primary key course(cid); 
1
2

插入课程表信息：

insert into course values(1 , '语文'),('02' , '数学'),('03' , '英语');
1

创建成绩表：

create table score(sid varchar(20),cid int,score int(3),primary key(sid,cid));

1
2

插入成绩表：

insert into score values('01' , 1 , 80);
insert into score values('01' , 2 , 90);
insert into Score values('01' , 3 , 99);
insert into score values('02' , 1 , 70);
insert into score values('02' , 2 , 60);
insert into score values('02' , 3 , 80);
insert into score values('03' , 1 , 80);
insert into score values('03' , 2 , 80);
insert into score values('03' , 3 , 80);
insert into score values('04' , 1 , 50);
insert into score values('04' , 2 , 30);
insert into score values('04' , 3 , 20);
insert into score values('05' , 1 , 76);
insert into score values('05' , 2 , 87);
insert into score values('06' , 1 , 31);
insert into score values('06' , 3 , 34);
insert into score values('07' , 2 , 89);
insert into score values('07' , 3 , 98);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

需求：
每个年级不同科目的平均分
表：
student(id,name,class)
course(cid,cname)
score(sid,cid,score)
结果：
class,cname,avg_score;
分析：
联结表
以年纪、科目分组，在年级、科目分组下分数求和/在年级、科目分组下的学生数目

select class,cname,sum(sc)/count(sid)
from
(
select sid,cid,score.score sc,class
from student
join score
on score.sid=student.id
) middle
join 
course
on course.cid=middle.cid
group by class,cname;
1
2
3
4
5
6
7
8
9
10
11
12