題目 現有班級、學生以及成績三張表： 備註：表名稱和欄位名稱可以參考表格內單詞設置 根據表格信息，按要求完成下麵SQL語句的編寫： 1、使用SQL分別創建班級表、學生表以及成績表的表結構，表內數據可以一條一條的插入也可以批量插入 2、查詢每個班級中每一科的平均成績，顯示數據包括班級名稱，課程以及平均 ...
題目
現有班級、學生以及成績三張表：
備註：表名稱和欄位名稱可以參考表格內單詞設置
根據表格信息，按要求完成下麵SQL語句的編寫：
1、使用SQL分別創建班級表、學生表以及成績表的表結構，表內數據可以一條一條的插入也可以批量插入
2、查詢每個班級中每一科的平均成績，顯示數據包括班級名稱，課程以及平均分數，並按照班ID升序排列
3、查詢所有同學的學生ID，姓名，性別以及總分，並按照成績從高到低排序
4、查詢課程成績小於75分的學生ID，姓名，班級，課程以及分數
5、將李米米的數學成績修改為88分
6、計算重點班中每一科的平均成績，顯示數據包括：重點班級ID，班級名稱，課程，平均分數，按照降序排列
解答
create database if not exists mooc default character set 'utf8';
use mooc;
--班級表
create table if not exists class(
c_id int(3) unsigned zerofill auto_increment key comment '編號',
name varchar(20) not null comment '班級',
descrip varchar(20) not null comment '備註'
)engine=innodb charset=utf8;
--插入班級數據
insert class(name,descrip)
values('一年級一班','重點班'),
('一年級二班','重點班'),
('二年級一班','重點班'),
('二年級二班','普通班');
--學生表
create table if not exists student(
s_id int unsigned auto_increment key comment '編號',
name varchar(20) not null comment '姓名',
gender varchar(5) not null comment '性別',
class int(3) unsigned zerofill not null comment '班級'
)engine=innodb charset=utf8;
alter table student auto_increment=1001; --修改主鍵初始值
--插入學生數據
insert student(name,gender,class)
values('趙曉明','男','001'),
('王曉紅','女','001'),
('張曉曉','女','001'),
('孫琪琪','女','003'),
('李米米','女','004'),
('趙曉剛','男','003'),
('張大寶','男','002'),
('張蘭','女','004'),
('孫好','男','001');
--成績表
create table if not exists score(
sc_id int(3) unsigned zerofill auto_increment key comment '編號',
s_id int unsigned not null comment '學生ID',
course varchar(10) not null comment '課程',
mark int unsigned not null comment '分數'
)engine=innodb charset=utf8;
--插入成績數據
insert score(s_id,course,mark)
values(1001,'數學',98),
(1001,'語文',90),
(1001,'英語',97),
(1002,'數學',96),
(1002,'語文',88),
(1003,'語文',88),
(1002,'英語',91),
(1003,'數學',96),
(1003,'英語',86),
(1004,'數學',89),
(1004,'語文',82),
(1004,'英語',83),
(1005,'數學',75),
(1005,'語文',86),
(1005,'英語',77),
(1006,'數學',81),
(1006,'語文',77),
(1006,'英語',60),
(1007,'數學',89),
(1007,'語文',56),
(1007,'英語',70),
(1008,'數學',87),
(1008,'語文',55),
(1008,'英語',66),
(1009,'數學',78),
(1009,'語文',60),
(1009,'英語',52);
--添加外鍵約束
alter table student add foreign key(class) references class(c_id);
alter table score add foreign key(s_id) references student(s_id);
--查詢每個班級中每一科的平均成績，顯示數據包括班級名稱，
--課程以及平均分數，並按照班ID升序排列
select c.name as 班級名稱,sc.course as 課程,avg(mark) as 平均分
from class as c
inner join student as st
on c.c_id = st.class
inner join score as sc
on st.s_id = sc.s_id
group by 班級名稱,課程
order by 班級名稱;
+------------+------+---------+
| 班級名稱 | 課程 | 平均分 |
+------------+------+---------+
| 一年級一班 | 數學 | 92.0000 |
| 一年級一班 | 英語 | 81.5000 |
| 一年級一班 | 語文 | 81.5000 |
| 一年級二班 | 數學 | 89.0000 |
| 一年級二班 | 英語 | 70.0000 |
| 一年級二班 | 語文 | 56.0000 |
| 二年級一班 | 數學 | 85.0000 |
| 二年級一班 | 英語 | 71.5000 |
| 二年級一班 | 語文 | 79.5000 |
| 二年級二班 | 數學 | 81.0000 |
| 二年級二班 | 英語 | 71.5000 |
| 二年級二班 | 語文 | 70.5000 |
+------------+------+---------+
--查詢所有同學的學生ID，姓名，
--性別以及總分，並按照成績從高到低排序
select st.s_id as 學生ID,st.name as 姓名,st.gender as 性別,sum(mark) as 總分
from student as st
inner join score as sc
on st.s_id = sc.s_id
group by 學生ID
order by 總分 desc;
+--------+--------+------+------+
| 學生ID | 姓名 | 性別 | 總分 |
+--------+--------+------+------+
| 1001 | 趙曉明 | 男 | 285 |
| 1002 | 王曉紅 | 女 | 275 |
| 1003 | 張曉曉 | 女 | 270 |
| 1004 | 孫琪琪 | 女 | 254 |
| 1005 | 李米米 | 女 | 238 |
| 1006 | 趙曉剛 | 男 | 218 |
| 1007 | 張大寶 | 男 | 215 |
| 1008 | 張蘭 | 女 | 208 |
| 1009 | 孫好 | 男 | 190 |
+--------+--------+------+------+
--查詢課程成績小於75分的學生ID，姓名，班級，課程以及分數
select st.s_id as 學生ID,st.name as 姓名,st.class as 班級,sc.course as 課程,sc.mark as 分數
from student as st
inner join class as c
on c.c_id = st.class
inner join score as sc
on sc.s_id = st.s_id
where sc.mark < 75;
+--------+--------+------+------+------+
| 學生ID | 姓名 | 班級 | 課程 | 分數 |
+--------+--------+------+------+------+
| 1009 | 孫好 | 001 | 語文 | 60 |
| 1009 | 孫好 | 001 | 英語 | 52 |
| 1007 | 張大寶 | 002 | 語文 | 56 |
| 1007 | 張大寶 | 002 | 英語 | 70 |
| 1006 | 趙曉剛 | 003 | 英語 | 60 |
| 1008 | 張蘭 | 004 | 語文 | 55 |
| 1008 | 張蘭 | 004 | 英語 | 66 |
+--------+--------+------+------+------+
--將李米米的數學成績修改為88分
update score set mark = 88 where s_id =
(select s_id from student where name = '李米米')
and course = '數學';
--計算重點班中每一科的平均成績，顯示數據包括：
--重點班級ID，班級名稱，課程，平均分數，按照降序排列
select c.c_id as 重點班級ID,c.name as 班級名稱,sc.course as 課程,avg(mark) as 平均分
from class as c
inner join student as st
on c.c_id = st.class
inner join score as sc
on st.s_id = sc.s_id
where c.descrip = '重點班'
group by 班級名稱,課程;
+------------+------------+------+---------+
| 重點班級ID | 班級名稱 | 課程 | 平均分 |
+------------+------------+------+---------+
| 001 | 一年級一班 | 數學 | 92.0000 |
| 001 | 一年級一班 | 語文 | 81.5000 |
| 001 | 一年級一班 | 英語 | 81.5000 |
| 002 | 一年級二班 | 數學 | 89.0000 |
| 002 | 一年級二班 | 語文 | 56.0000 |
| 002 | 一年級二班 | 英語 | 70.0000 |
| 003 | 二年級一班 | 數學 | 85.0000 |
| 003 | 二年級一班 | 語文 | 79.5000 |
| 003 | 二年級一班 | 英語 | 71.5000 |
+------------+------------+------+---------+
