作业十四

（1）

CREATE TABLE Staff
	(Sno CHAR(9) PRIMARY KEY,
	Sname CHAR(20),
	Sage SMALLINT,
	SJob CHAR(10),
	Sal INT,
	Dno CHAR(20)
	)

CREATE TABLE Department
	(Dno CHAR(20) PRIMARY KEY,
	Dname CHAR(20),
	Manager CHAR(20),
	Tel CHAR(10)
	)

（2）

CREATE TABLE Department
	(Dno CHAR(20) PRIMARY KEY,
	Dname CHAR(20),
	Manager CHAR(20),
	Tel CHAR(10)
	)
CREATE TABLE Staff
	(Sno CHAR(9) PRIMARY KEY,
	Sname CHAR(20),
	Sage SMALLINT,
	SJob CHAR(10),
	Sal INT,
	Dno CHAR(20),
	FOREIGN KEY(Dno) REFERENCES Department(Dno)
	)

（3）

CREATE TABLE Department
	(Dno CHAR(20) PRIMARY KEY,
	Dname CHAR(20),
	Manager CHAR(20),
	Tel CHAR(10)
	)
CREATE TABLE Staff
	(Sno CHAR(9) PRIMARY KEY,
	Sname CHAR(20),
	Sage SMALLINT CHECK(Sage<=60),
	SJob CHAR(10),
	Sal INT,
	Dno CHAR(20),
	FOREIGN KEY(Dno) REFERENCES Department(Dno)
	)

(1)
先在数据库的course表和sc表中添加关于离散数学的相关内容。
首先建立一个表存放执行后的结果
写存储过程

CREATE DEFINER=`root`@`localhost` PROCEDURE `countSoreP`()
BEGIN
    declare less60 smallint default 0; /*x <= 60*/
    declare b60a70 smallint default 0;
    declare b70a80 smallint default 0;
    declare b80a90 smallint default 0;
    declare more90 smallint default 0; 
    declare countcno char(4) default '****';

    SELECT cno
    INTO countcno FROM course
    WHERE cname = '离散数学';/*确定course表中有离散数学这门课*/

    SELECT COUNT(*)
    INTO less60 FROM sc
    WHERE cno = '8' AND grade < 60;
    SELECT COUNT(*)
    INTO b60a70 FROM sc
    WHERE cno = '8' AND grade >= 60 AND grade < 70;

    SELECT COUNT(*)
    INTO b70a80 FROM sc
    WHERE cno = '8' AND grade >= 70 AND grade < 80;

    SELECT COUNT(*)
    INTO b80a90 FROM sc
    WHERE cno = '8' AND grade >= 80 AND grade < 90;

    SELECT COUNT(*)INTO more90 FROM sc
    WHERE cno = '8' AND grade >= 90;
    create table countScore( scorestage char(10), number smallint);
    insert into countscore values('x<60', less60);
    insert into countscore values('60<=x<70', b60a70);
    insert into countscore values('70<=x<80', b70a80);
    insert into countscore values('80<=x<90', b80a90);
    insert into countscore values('x>=90', more90);
END

（2）统计任意一门课的平均成绩
创建表结构，用来存储执行结果

CREATE DEFINER=root@localhost PROCEDURE scoreavgP() 
BEGIN 
    declare curname char(40) default ‘*‘; 
    declare curcno char(4) default ‘**‘; 
    declare curavg char(4); 
    declare mycursor cursor for 
        select cno, cname from course;
        
    open mycursor;
        loop
            fetch mycursor into curcno, curname; 
            select avg(grade) into curavg from sc 
            where cno = curcno; 
            insert into scoreavg values(curcno, curname, curavg); 
            end loop; 
            close mycursor; 
END 

（3）

  CREATE PROCEDURE `changeStageP` ()
     BEGIN
    declare chgrade char(1) default 'A';
    declare grade smallint(6) default 0;
    declare ID int default 0;

    declare  mycursor2 cursor for
        select grade from sc;

    alter table sc add column(newgrade char(1));

    open mycursor2;
        loop /*当游标打开时进行下列循环处理*/
            fetch mycursor2 into grade; /*游标推进一行取结果送变量*/
            if grade < 60 then
                chgrade = 'E';
            elseif grade < 70 then
                chgrade = 'D';
            elseif grade < 80 then
                chgrade = 'C';
            elseif grade < 90 then
                chgrade = 'B';
            elseif
                chgrade = 'A';
            update id set id = id + 1 where id < 9
            update sc(id) set newgrade = chgrade;
        end loop;  
        close mycursor;
END