数据库作业13：第五章课后题

数据库作业13：第五章课后题

第六题

CREATE TABLE 部门
	(部门号 CHAR(9) PRIMARY KEY,
	名称 CHAR(9),
	经理名 CHAR(9),
	电话 CHAR(11)
);

CREATE TABLE 职工
	(职工号 CHAR(20) PRIMARY KEY,
	姓名 CHAR(9),
	年龄 SMALLINT CHECK (年龄<=60),
	职务 CHAR(9),
	工资 SMALLINT,
	部门号 CHAR(9),
	FOREIGN KEY(部门号) REFERENCES 部门(部门号)
);

第二题

2.1

IF (EXISTS(SELECT *FROM SYS.OBJECTS WHERE NAME='LSSX'))
BEGIN
	DROP PROCEDURE LSSX
END

GO
CREATE PROCEDURE LSSX
@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 LSSX
@NAME = '离散数学';

2.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='离散数学';

2.3

ALTER TABLE SC
ADD LEVEL CHAR(9)
GO
CREATE PROCEDURE DEJ
AS
	UPDATE SC SET LEVEL='A' WHERE Grade>=90;
	UPDATE SC SET LEVEL='B' WHERE Grade>=80 AND Grade<90;
	UPDATE SC SET LEVEL='C' WHERE Grade>=70 AND Grade<80;
	UPDATE SC SET LEVEL='D' WHERE Grade>=60 AND Grade<70;
	UPDATE SC SET LEVEL='E' WHERE Grade<60;

EXEC DEJ
SELECT * FROM SC;

以上为本次作业的全部内容！

感谢阅读~