/*
总体说明：课程表用表course或表C，都可以
1.表结构修改及应用（10分）
·在STUDY表中增加时间字段TM，类型为DATE：将所有课程成绩的时间修改为'2019-04-01'；将数据库原理课程的成绩时间修改为'2019-04-07'
·在STU表中增加总成绩字段TSCORE，类型为numeric (4, 1)*/
alter table study add TM date;（2分）
update STUDY set TM='2019-04-01';（2分）
update STUDY set TM='2019-04-08' where cno in (select cno from course where cnm='数据库原理');（4分）
alter table STU add tscore numeric(4,1);（2分）
评分说明：
修改数据库原理成绩时间，没有where条件的，扣3分；没有用“数据库原理”名称的，扣2分；语法不正确，本语句得分不超过1分
/*
2.创建二个索引（Stu表的SNM字段升序；Study表的SCORE字段降序）（5分）
·写出创建语句并执行
·检查结果并截图*/
create index idx_snm on stu(snm);
create index idx_score on study(score desc);
/*
3.视图应用：（10分）
建视图：建立学生成绩信息视图V_STU，包括学号、姓名、年龄、籍贯、课程名、成绩
·基于V_STU查询年龄在19岁以上的学生成绩信息*/
create or replace view V_STU
as
select
stu.SNO,
snm,
floor(DATEDIFF(CURDATE(),birthday)/365) as yearsold,
NATNM,
CNM,
SCORE
from stu,STUDY,COURSE
where stu.SNO = study.SNO and study.CNO = course.CNO;
/*
评分说明：
①“or replace”可以省略
②没有以365天为计算依据的，扣1分；没有用floor或其它函数取整的，扣1分；
③每缺少1个等值连接条件扣1分；
④每少1张表扣2分；
⑤语法上有错误的，总分最高不超过6分*/
/*
4.分支语句运用（10分）
·查询5组的学生的成绩信息，包括学号、课程号、成绩，其中对成绩作如下处理：
大于等于85分，显示“优秀”
大于等于70分，小于85分，显示“良好”
大于等于60分，小于70分，显示“及格”
小于60分，显示“不及格”*/
select study.SNO,cno,case
when score >= 85 then '优秀'
when score >= 70 and score < 85 then '良好'
when score >= 60 and score < 70 then '及格'
else '不及格'
end as grade
from STU,STUDY where stu.SNO=study.SNO and GRPNO=5;
/*评分说明：
①结果不正确，得分不超过8分；语法不正确，得分不超过6分；
②条件范围不正确，扣2分；*/
/*
5.存储过程和触发器(针对STUDY表)：（10分 + 20分）
①写一个存储过程，在成绩表里面插入一个成绩记录（输入参数有学号、课程号、成绩），时间默认为系统当前时间，要求判断学号和课程号是否存在，分数是否超过100分
②写一个触发器，如果插入的成绩大于0，则自动计算该学生的总分；否则取消本次插入*/
DELIMITER @@
create procedure ins_study(
_sno char(10),
_cno char(3),
_score int
)
lable_p: BEGIN
if not exists (select * from stu where SNO=_sno) then
LEAVE lable_p; /*学号不存在*/
end if;
if not exists (select * from course where CNO=_cno) then
LEAVE lable_p; /*课程号不存在*/
end if;
if exists (select * from STUDY where SNO=_sno and CNO=_cno) then
LEAVE lable_p; /*成绩已经存在*/
end if;
if _score > 100 then
LEAVE lable_p; /*分数超过100分*/
end if;
insert into STUDY(SNO,CNO,SCORE,TM) values(_sno,_cno,_score,CURDATE());
END@@
DELIMITER ;
/*评分说明：
①忘记DELIMITER标记扣1分
②总共4个条件，每少1个条件扣2分
③可以用if语句嵌套实现 符合条件的数据 执行插入（不用标签和leave语句），但是也必须考虑到4个条件，否则对应扣分
④参数不全扣1分；没有insert语句扣4分，insert语句不正确扣2分*/
###################################
DELIMITER $$
create trigger t_ins_study before insert on study
FOR EACH ROW
begin
if new.score < 0 then
set new.score = 0;
end if;
update STU set tscore = ifnull(tscore,0) + new.score where SNO=new.sno;
end$$
DELIMITER ;
/*评分说明：
①忘记DELIMITER标记扣1分
②没有判断new.score扣2分
③如果缺少update语句扣6分，否则：
写错表名扣4分；
没有用ifnull扣1分；
没有加上new.score扣2分；
缺少where条件扣2分
④没有用before触发器扣1分
⑤update语句可以写成：
update STU set tscore = (select sum(score) from study where sno=new.sno and cno=new.cno);
思考题：用“SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Can not insert that value'”代替“set new.score = 0”
*/
/*
6.查询选修了全部课程的学生学号、姓名、籍贯、平均分（扩展上周的上机题目）*/（20分）
/*第1步，查询选修了全部课程的学生信息*/
select sno,snm,natnm from stu S
where not exists (
select * from COURSE C
where not exists(
select * from STUDY SC where S.SNO=SC.SNO AND C.CNO=SC.CNO
)
)
/*第2步，在此基础上扩展平均分的计算*/
select sno,snm,natnm,avgscore=(select AVG(score) from STUDY where SNO=s.sno) from stu S
where not exists (
select * from COURSE C
where not exists(
select * from STUDY SC where S.SNO=SC.SNO AND C.CNO=SC.CNO
)
)
/*评分说明：
①如果只实现了第1步，给16分（第1步如果有错误，根据情况酌情给分）
②如果正确获取了stu中的几个信息（包括平均分，如第2步中），给6分，其他情况酌情给分*/
/*
7、综合查询（函数及分组）
·查询全部学生信息，包括学号、姓名、性别、年龄、城市（用LOCATE函数结合right或substring；如果籍贯当中不包含字符“-”,则将籍贯当作一个城市看待）（5分）
·查询学霸城市排名（按城市分组计算平均分，由大到小排序），显示城市，平均分 （10分）
提示：第2问可以在第1问的基础上分步实现：先将stu和study连接起来查询，显示各学生信息及各科分数，看信息是否正确；然后仅显示城市和成绩；最后再处理分组；
*/
①select sno,snm,sex,
floor(DATEDIFF(CURDATE(),birthday)/365) as yearsold,
right(natnm,CHAR_LENGTH(natnm) - locate('-',natnm)) as city
from stu;
/*评分说明：
①年龄只要用了某种算法，能得到近似值就可以，否则扣1分；
②城市如果没有正确计算出来，扣2分；*/
②select
right(natnm,CHAR_LENGTH(natnm) - locate('-',natnm)) as city,
avg(score) as avgs
from stu,study where stu.sno=study.sno
group by city
order by avgs desc;
/*评分说明：
①没有用avg函数扣2分；
②没有group by扣4分；group by 错误扣2分
③城市计算错误扣1分；
④没有order by扣2分；order by不正确扣1分
*/
1011
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
