第五章习题

6.假设有下面两个关系模式：

职工（职工号，姓名，年龄，职务，工资，部门号），其中职工号为主码
部门（部门号，名称，经理名，电话）其中部门号为主码
用SQL语句定义这两个关系模式，要求在模式中完成一下完整性约束条件的定义：
1.定义每个模式的主码 2.定义参照完整性 3.定义职工年龄不得超过60岁

CREATE TABLE DEPARTMENT          --建立部门表
(DEPARTMENTNUMBER CHAR(9),       --部门号
DEPTNAME CHAR(10),               --名称
BOSS CHAR(10),                   --经理名
PHON CHAR(15)NOT NULL,           --电话
PRIMARY KEY(DEPARTMENTNUMBER));  --定义主键部门号
CREATE TABLE WORK                --建立职工表
(WNO CHAR(9),                    --职工号
WNAME CHAR(10),                  --姓名
WAGE SMALLINT CONSTRAINT NIAN CHECK(WAGE<=60),  --年龄不大于60岁
POST CHAR(10),                  --职务
DEPARTMENTNUMBER CHAR(9),       --工资
PRIMARY Key(WNO),               --定义主键
FOREIGN KEY(DEPARTMENTNUMBER)REFERENCES DEPARTMENT(DEPARTMENTNUMBER),--添加外键
);
SELECT * FROM DEPARTMENT;
SELECT * FROM WORK;

2.对学生-课程数据可编写存储过程，完成下述功能：

1.统计离散数学的成绩分布情况，即按照各分段统计人数

CREATE PROCEDURE tj
@NAME CHAR(9)
AS
BEGIN TRANSACTION AC
DECLARE @ONE int,@TWO INT,@THREE INT,@FOUR INT,@FIVE INT,@Cno CHAR(10), @CNT INT
SELECT @Cno=Cno FROM Course WHERE @NAME=Cname
SELECT @Cnt=COUNT(*) FROM SC WHERE @Cno=Cno;
IF @CNT=0
BEGIN
PRINT'NOT HAVE STUDENT OR NOT HAVE CLASS'
ROLLBACK TRANSACTION AC;
END;
SELECT @ONE=COUNT(*) FROM SC WHERE @Cno=SC.Cno AND SC.Grade<60;
SELECT @TWO=COUNT(*) FROM SC WHERE @Cno=SC.Cno AND SC.Grade<70 AND SC.Grade>=60;
SELECT @THREE=COUNT(*) FROM SC WHERE @Cno=SC.Cno AND SC.Grade<80 AND SC.Grade>=70;
SELECT @FOUR=COUNT(*) FROM SC WHERE @Cno=SC.Cno AND SC.Grade<90 AND SC.Grade>=80;
SELECT @FIVE=COUNT(*) FROM SC WHERE @Cno=SC.Cno AND SC.Grade<90 AND SC.Grade>=100;
PRINT'[0,60):'PRINT @ONE;
PRINT'[60,70):'PRINT @TWO;
PRINT'[70,80):'PRINT @THREE;
PRINT'[80,90):'PRINT @FOUR;
PRINT'[90,100]:'PRINT @FIVE;
COMMIT TRANSACTION AC;

EXEC tj
@NAME='离散数学';

2.统计任何一门课程的平均成绩

CREATE PROCEDURE AG
@NAME CHAR(9)
AS
BEGIN TRANSACTION AC
DECLARE @Cno CHAR(4),@SAVG SMALLINT
SELECT @Cno=Cno FROM Course WHERE @NAME=Cname;
IF @Cno IS NULL
BEGIN 
PRINT 'NOT HAVE THE CLASS'
ROLLBACK TRANSACTION AC;
END;
SELECT @SAVG=AVG(Grade) FROM SC WHERE @Cno=CNO;
PRINT'THE AVG IS:'+CONVERT(VARCHAR,@SAVG)
COMMIT;

EXEC AG
@NAME='离散数学';

3.将学生成绩由百分制改为等级制：

ALTER TABLE SC
ADD LEAVE CHAR(1)
CREATE PROCEDURE LE
AS 
UPDATE SC SET LEAVE='A' WHERE Grade>=90;
UPDATE SC SET LEAVE='B' WHERE Grade>=80 AND Grade<90;
UPDATE SC SET LEAVE='C' WHERE Grade>=60 AND Grade<80;
UPDATE SC SET LEAVE='D' WHERE Grade>=50 AND Grade<60;
UPDATE SC SET LEAVE='E' WHERE Grade<50;

EXEC LE
SELECT * FROM SC;