数据库精选习题

存储过程-习题

1. 创建不带参数的存储过程count_procedure()，统计年龄在25岁以上的学生人数。

delimiter $$
create PROCEDURE count_procedure()
BEGIN
select count(*)
from students
where year(NOW())-year(sbirthday)>25;
END $$
delimiter ;

call count_procedure();

2. 创建带一个输入参数的存储过程name_procedure()，根据学生学
   号查询该学生的姓名、年龄、所在部门。

delimiter $$
create PROCEDURE name_procedure(in xh char(12))
BEGIN
SELECT sname,year(NOW())-year(sbirthday),sdept
from students
where sno=xh;
END $$
delimiter ;

call name_procedure('2015001');

3. 创建带一个输入参数的存储过程stu_procedure()，根据学生学
   号查询该学生查询该学生不及格课程的数目 。

delimiter $$
CREATE PROCEDURE stu_procedure(in xh char(12))
BEGIN
select count(*)
from score
WHERE sno=xh and grade<60;
END $$
delimiter ;

call stu_procedure('2015005');

4. 创建一个存储过程zhicheng _procedure（），用参数指定的职称的值查询具有该职称的所有老师。

delimiter $$
CREATE PROCEDURE zhicheng _procedure(in zc char(3))
BEGIN
select * from teachers where title=zc;
END $$
delimiter ;

5. 创建一个存储过程grade_avg_procedure（），用参数指定的课程编号查询该课程的平均成绩。

delimiter $$
create PROCEDURE grade_avg_procedure(in kch char(6))
BEGIN
select avg(grade)
from score
where cno=kch;
END $$
delimiter ;

6. 在学生表上创建存储过程stu_info_procedure()。该存储过程的输入参数type，输出参数是info。当type的值是1时，计算所有男同学的人数，然后通过参数info输出。当type的值是2时，计算所有女同学的人数，然后通过info输出；当type为1和2以外的任何值时，
   将字符串“Error Input!”赋值给info输出。

delimiter $$
CREATE PROCEDURE stu_info_procedure
(
in type char(1),
out info char(14)
)
BEGIN
IF type='1' THEN
	SELECT count(*) into info from students where sex='男';
ELSEIF type='2' THEN
	SELECT count(*) into info from students where sex='女';
ELSE
	SET info='Error Input!';
END IF;
END $$
delimiter ;

CALL stu_info_procedure('3',@msg);
SELECT @msg;

7. 在成绩表中添加一列style，表示成绩等级，然后创建一个存储过程grade_style ()，根据给定的学号和课程号，判断该生该课程的成绩等级，并在style列填入相应的等级值:
   100=>成绩>=90,等级：优秀；
   90>成绩>=80,等级：良好；
   80>成绩>=70,等级：中等；
   70>成绩>=60,等级：及格；
   60>成绩,等级：不及格。

delimiter $$
CREATE PROCEDURE grade_style
(
in xh char(12),
in kch char(6)
)
BEGIN
declare cj decimal(5,2);
SELECT grade into cj from score where sno=xh and cno=kch;
if cj>=90 and cj<=100 THEN
	update score set style='优秀' where sno=xh and cno=kch;
elseif cj>=80 and cj<90 THEN
	update score set style='良好' where sno=xh and cno=kch;
elseif cj>=70 and cj<80 THEN
	update score set style='中等' where sno=xh and cno=kch;
elseif cj>=60 and cj<70 THEN
	update score set style='及格' where sno=xh and cno=kch;
ELSE
	update score set style='不及格' where sno=xh and cno=kch;
end IF;
END $$
delimiter ;

call grade_style('2015006','c05');

[如果不定义cj将grade赋值给cj，则要每次比较都执行一遍select语句：
if (select grade from score where sno=xh and cno=kch)>=90 and (select grade from score where sno=xh and cno=kch)<=100 THEN...]

8. 创建存储过程：根据输入的学号，实现以下功能：
   如果该生有不及格的成绩，输出“该生无参评奖学金资格”;
   如果无不及格成绩，且平均成绩小于75，输出“该生无参评奖学金资格”;
   如果无不及格成绩，且平均成绩不小于75，输出“该生可参评奖学金”。

delimiter $$
CREATE PROCEDURE scholarship
(
in xh char(12),
out msg varchar(20)
)
BEGIN
if exists(select * from score where sno=xh and grade<60) THEN
	SET msg='该生无参评奖学金资格';
ELSEIF (select avg(grade) from score where sno=xh)<75 THEN
	SET msg='该生无参评奖学金资格';
ELSE
	SET msg='该生可参评奖学金';
END IF;
END $$
delimiter ;


call scholarship('2015006',@str);
select @str;

9. 假设score表中是以前选修的课程并已考试取得成绩，现在有一个selcourses表用于保存本次选课的信息，向selcourses表中添加一行数据（一个学号，对应一个课程号）就表示该生选修该课程，请创建存储过程，实现选课功能的控制：输入：学号、课程号 ，表示某学生选修某课程。满足以下条件，
   1.以前选修过的课程，本次不能再选；
   2.本次也不能重复选课；
   3.本次选课总数不能超过5门,否则，输出 你已经超过5门；
   4.不违反以上规则则保存该选课数据。

delimiter $$
CREATE PROCEDURE xuanke
(
in xh char(12),
in kch char(6),
out msg varchar(20)
)
BEGIN
IF exists(select * from score where sno=xh and cno=kch) THEN
	set msg='以前选过，不能再选！';
ELSEIF exists(select * from selcourses where sno=xh and cno=kch) THEN
	set msg='本次选过，不能再选！';
ELSEIF (select count(*) from selcourses where sno=xh)>=5 THEN
	SET msg='已经达到5门，不能再选';
ELSE
	INSERT INTO selcourses(sno,cno)
	VALUES(xh,kch);
END IF;
END $$
delimiter ;

call xuanke('2015001','c09',@str);
select @str

触发器-习题

1. 创建触发器，当删除某学生信息时，该生的成绩信息随之删除

delimiter $$
CREATE trigger delete_stu
after delete on students
for each ROW
begin
	delete from score
	where sno=old.sno;
END $$
delimiter ;

2. 创建触发器，实现对成绩的修改的控制：1.不允许将成绩从及格改为不及格； 2.不允许提高超过20%。如果违反上述规则，存储原成绩

delimiter $$
CREATE trigger update_score
before update on score
for each ROW
BEGIN
	IF old.grade>=60 and new.grade<60 THEN
		SET new.grade=old.grade;
	ELSEIF new.grade>old.grade*1.2 THEN
		SET new.grade=old.grade;
	END if;
END $$
delimiter ;

测试：
update score set grade=70
where sno='2015003' and cno='c04';

3. 创建触发器，向成绩表添加数据时保证成绩在0-100之间小于0取0，大于100取100。

delimiter $$
CREATE trigger ssss
before insert on score
for each ROW
BEGIN
	IF new.grade>100 THEN
		SET new.grade=100;
	ELSEIF new.grade<0 THEN
		set new.grade=0;
	END if;
END $$
delimiter ;

4. 如果学生成绩及格，则获得相应课程的学分

delimiter $$
CREATE trigger insert_score
after INSERT on score
for each ROW
BEGIN
	declare xf int;
	IF new.grade>=60 THEN
		BEGIN
			select credit into xf from courses where cno=new.cno;
			IF (SELECT scredit from students where sno=new.sno)is NULL THEN
			update students set scredit=xf where sno=new.sno;
			ELSE
			update students set scredit=scredit+xf where sno=new.sno;
			END if;
		END ;
	END if;
END $$
delimiter ;

测试：
insert into score(sno,cno,grade)
values('2015005','c02',80);