subject

Answer

create database if not exists mooc default character set 'utf8';
use mooc;

--Class table
create table if not exists class(
    c_id int(3) unsigned zerofill auto_increment key comment 'number',
    name varchar(20) not null comment 'class',
    descrip varchar(20) not null comment 'Remarks'
)engine=innodb charset=utf8;

--Insert Class Data
insert class(name,descrip) 
values('Class one grade one','tracked class for gifted students'),
('Class two, grade one','tracked class for gifted students'),
('Class one grade two','tracked class for gifted students'),
('Class two, grade two','Regular class');


--Student list
create table if not exists student(
    s_id int unsigned auto_increment key comment 'number',
    name varchar(20) not null comment 'Full name',
    gender varchar(5) not null comment 'Gender',
    class int(3) unsigned zerofill not null comment 'class'
)engine=innodb charset=utf8;
alter table student auto_increment=1001; --Modify primary key initial value

--Insert student data
insert student(name,gender,class) 
values('Zhao Xiao Ming','male','001'),
('Xiao Hong Wang','female','001'),
('Xiao Xiao Zhang','female','001'),
('Sun Qi Qi','female','003'),
('Li Mi Mi','female','004'),
('Zhao Xiao Gang','male','003'),
('Da Bao Zhang','male','002'),
('Zhang Lan','female','004'),
('SUN Hao','male','001');


--Score sheet
create table if not exists score(
    sc_id int(3) unsigned zerofill auto_increment key comment 'number',
    s_id int unsigned not null comment 'Student ID',
    course varchar(10) not null comment 'curriculum',
    mark int unsigned not null comment 'Fraction'
)engine=innodb charset=utf8;

--Insert score data
insert score(s_id,course,mark)
values(1001,'Mathematics',98),
(1001,'Chinese',90),
(1001,'English?',97),
(1002,'Mathematics',96),
(1002,'Chinese',88),
(1003,'Chinese',88),
(1002,'English?',91),
(1003,'Mathematics',96),
(1003,'English?',86),
(1004,'Mathematics',89),
(1004,'Chinese',82),
(1004,'English?',83),
(1005,'Mathematics',75),
(1005,'Chinese',86),
(1005,'English?',77),
(1006,'Mathematics',81),
(1006,'Chinese',77),
(1006,'English?',60),
(1007,'Mathematics',89),
(1007,'Chinese',56),
(1007,'English?',70),
(1008,'Mathematics',87),
(1008,'Chinese',55),
(1008,'English?',66),
(1009,'Mathematics',78),
(1009,'Chinese',60),
(1009,'English?',52);


--Adding foreign key constraints
alter table student add foreign key(class) references class(c_id);
alter table score add foreign key(s_id) references student(s_id);

--Query the average scores of each subject in each class and display the data including the class name.
--Courses and average scores, in accordance with the class ID Ascending order
select c.name as Class name,sc.course as curriculum,avg(mark) as Average
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 Class name,curriculum
order by Class name;
+------------+------+---------+
| Class name   | curriculum | Average  |
+------------+------+---------+
| Class one grade one | Mathematics | 92.0000 |
| Class one grade one | English? | 81.5000 |
| Class one grade one | Chinese | 81.5000 |
| Class two, grade one | Mathematics | 89.0000 |
| Class two, grade one | English? | 70.0000 |
| Class two, grade one | Chinese | 56.0000 |
| Class one grade two | Mathematics | 85.0000 |
| Class one grade two | English? | 71.5000 |
| Class one grade two | Chinese | 79.5000 |
| Class two, grade two | Mathematics | 81.0000 |
| Class two, grade two | English? | 71.5000 |
| Class two, grade two | Chinese | 70.5000 |
+------------+------+---------+

--Query all students ID，Full name,
--Gender and total score and rank from high to low according to grades.
select st.s_id as Student ID,st.name as Full name,st.gender as Gender,sum(mark) as Total score
from student as st
inner join score as sc
on st.s_id = sc.s_id
group by Student ID
order by Total score desc;
+--------+--------+------+------+
| Student ID | Full name   | Gender | Total score |
+--------+--------+------+------+
|   1001 | Zhao Xiao Ming | male   |  285 |
|   1002 | Xiao Hong Wang | female   |  275 |
|   1003 | Xiao Xiao Zhang | female   |  270 |
|   1004 | Sun Qi Qi | female   |  254 |
|   1005 | Li Mi Mi | female   |  238 |
|   1006 | Zhao Xiao Gang | male   |  218 |
|   1007 | Da Bao Zhang | male   |  215 |
|   1008 | Zhang Lan   | female   |  208 |
|   1009 | SUN Hao   | male   |  190 |
+--------+--------+------+------+

--Inquiry for students with a course score of less than 75 ID，Name, class, course and score
select st.s_id as Student ID,st.name as Full name,st.class as class,sc.course as curriculum,sc.mark as Fraction
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;
+--------+--------+------+------+------+
| Student ID | Full name   | class | curriculum | Fraction |
+--------+--------+------+------+------+
|   1009 | SUN Hao   |  001 | Chinese |   60 |
|   1009 | SUN Hao   |  001 | English? |   52 |
|   1007 | Da Bao Zhang |  002 | Chinese |   56 |
|   1007 | Da Bao Zhang |  002 | English? |   70 |
|   1006 | Zhao Xiao Gang |  003 | English? |   60 |
|   1008 | Zhang Lan   |  004 | Chinese |   55 |
|   1008 | Zhang Lan   |  004 | English? |   66 |
+--------+--------+------+------+------+

--Modify Li Mimi's math score to 88
update score set mark = 88 where s_id = 
(select s_id from student where name = 'Li Mi Mi')
and course = 'Mathematics';

--The average scores of each subject in the key class are calculated, and the data are displayed as follows:
--Key class ID，Class name, course, average score, in descending order
select c.c_id as Key class ID,c.name as Class name,sc.course as curriculum,avg(mark) as Average
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 = 'tracked class for gifted students'
group by Class name,curriculum;
+------------+------------+------+---------+
| Key class ID | Class name   | curriculum | Average  |
+------------+------------+------+---------+
|        001 | Class one grade one | Mathematics | 92.0000 |
|        001 | Class one grade one | Chinese | 81.5000 |
|        001 | Class one grade one | English? | 81.5000 |
|        002 | Class two, grade one | Mathematics | 89.0000 |
|        002 | Class two, grade one | Chinese | 56.0000 |
|        002 | Class two, grade one | English? | 70.0000 |
|        003 | Class one grade two | Mathematics | 85.0000 |
|        003 | Class one grade two | Chinese | 79.5000 |
|        003 | Class one grade two | English? | 71.5000 |
+------------+------------+------+---------+