cunchuguoc

请写出下列创建存储过程的MySQL语句，存储过程名命名为PROC_StuGradeNum_学号最后两位

统计指定班级指定课程的考试情况，要求显示出不同分值段（10分为一个分值段）以及尚未考试的学生人数，如（100~90,2人；89~80,7人；…未考,2人）。

DELIMITER //

CREATE PROCEDURE PROC_StuGradeNum(

CLS CHAR(8), CN VARCHAR(20))

BEGIN

    SELECT (CASE

            WHEN Grade BETWEEN 90 AND 100 THEN '90~100'

            WHEN Grade BETWEEN 80 AND 89 THEN '80~89'

            WHEN Grade BETWEEN 70 AND 79 THEN '70~79'

            WHEN Grade BETWEEN 60 AND 69 THEN '60~69'

            WHEN Grade BETWEEN 0 AND 59 THEN '0~59'

            ELSE '未考'

            END

) AS "成绩分段", COUNT(Sno) AS "分段人数"

FROM Reports R,Courses C

WHERE R.Cno=C.Cno AND LEFT(Sno,8)=CLS AND Cname=CN

GROUP BY (CASE

          WHEN Grade BETWEEN 90 AND 100 THEN '90~100'

          WHEN Grade BETWEEN 80 AND 89 THEN '80~89'

          WHEN Grade BETWEEN 70 AND 79 THEN '70~79'

          WHEN Grade BETWEEN 60 AND 69 THEN '60~69'

          WHEN Grade BETWEEN 0 AND 59 THEN '0~59'

          ELSE '未考'

          END);

END //

DELIMITER ;

请写出下列操作的SQL语句："创建一个教师工作量统计表TeaQuantity，其中包含统计编号Qid、教师编号Tno、学年Tacademicyear和工作量Tquantity，其中Tno和Tacademicyear的数据类型与Tutors表中的设置相同，Qid的数据类型定义为Binary(16)，默认输入UNIQUEINDETIFIER(UUID)的二进制数，且设置为该表的主键，Tquantity的数据类型设置为整数，并且在属性列Tno和Tacademicyear上设置唯一性约束，即约束该表中每个教师编号，每个学年只统计一个工作量"

CREATE TABLE TeaQuantity

(Qid Binary(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID())),

Tno CHAR(4),

 Tacademicyear YEAR,

 Tquantity INT,

 CONSTRAINT UK_Tno UNIQUE KEY(Tno,Tacademicyear)

);