SQL练习

数据库练习1

-- 期末成绩表(学号(21个字符的字符串，全数字，无重复),姓名，学校，年级，语数英成绩)
create table score(
	sid char(21) primary key,
	name varchar(20) ,
	school varchar(50),
	grade int,
	class int,
	chinese_score int,
	math_score int,
	english_score int,
	total_score int 
);

-- 每个学校、每个年级、每个班的各科平均分，最高分，及格人数
SELECT 
    school,
    grade,
    class,
    AVG(chinese_score) AS avg_chinese_score,
    MAX(chinese_score) AS max_chinese_score,
    SUM(CASE WHEN chinese_score >= 60 THEN 1 ELSE 0 END) AS pass_chinese_count,
    
    AVG(math_score) AS avg_math_score,
    MAX(math_score) AS max_math_score,
    SUM(CASE WHEN math_score >= 60 THEN 1 ELSE 0 END) AS pass_math_count,
    
    AVG(english_score) AS avg_english_score,
    MAX(english_score) AS max_english_score,
    SUM(CASE WHEN english_score >= 60 THEN 1 ELSE 0 END) AS pass_english_count
    
FROM 
    score
GROUP BY 
    school, grade, class
ORDER BY 
    school, grade, class;

-- 找到高二年级总分前100的学生，按分数降序排序，分数相同按学号，列出学号、姓名、科目、科目分数、学校、班级
select sid,name,chinese_score,math_score,english_score,total_score,school,grade
from score
where grade = 2
order by total_score desc,sid asc
limit 0 100;

-- 建立触发器，在插入数据时统计总分
CREATE TRIGGER before_insert_score
BEFORE INSERT ON score
FOR EACH ROW
SET NEW.total_score = NEW.chinese_score + NEW.math_score + NEW.english_score;


-- 建立索引：一般来说统计成绩有以下几种方式：
-- 1. 以班为单位(这里学校内部的班级)
-- 2. 以年级为单位(这里是每个学校的同一年级的对比)
-- 3. 以总分成绩排名统计

-- 1和2 可以通过建立联合索引(school,grade,class) 解决
-- 3 可以在总分成绩这里建立索引(total_score) 解决
create INDEX idx_school_grade_class on score(school,grade,class) ;
create INDEX idx_total_score on score(total_score);