第五章作业

最新推荐文章于 2022-09-16 22:18:28 发布

西伯利亚大黑兔

最新推荐文章于 2022-09-16 22:18:28 发布

阅读量78

点赞数

原文链接：https://blog.csdn.net/cristove/article/details/116116973

版权

在这里插入图片描述

6.1
create table 部门(部门号 char(9) primary key ,
				  名称 char(9),
				  经理名 char(9),
				  电话 char(11));
create table 职工(
					职员号 char(9) primary key,
					姓名   char(9) ,
					年龄   smallint check (年龄<=60),
					职务   char(9),
					工资   smallint,
					部门号 char (9),
					foreign key(部门号) references 部门(部门号));


2.1
CREATE TABLE Divide(
	Grade CHAR(20),
	Num INT
)
INSERT INTO Divide
VALUES('[0,20)',0),('[20,40)',0),('[40,60)',0),('[60,80)',0),('[80,100]',0);
SELECT * FROM Divide


EXEC Proc_Grade;
SELECT *FROM SC WHERE Cno=8
SELECT *FROM Divide

2.2
CREATE TABLE AvgGrade(
Cname CHAR(20),
Avg INT
);
INSERT INTO AvgGrade VALUES('数据库',0),('数学',0),('信息系统',0),('离散数学',0);
SELECT * FROM AvgGrade

CREATE PROCEDURE Avg_GRADE
AS 
DECLARE 
@1 INT,
@2 INT,
@3 INT,
@8 INT;
SELECT @1=Avg(Grade) FROM SC WHERE Cno='1';
UPDATE AvgGRADE SET Avg=@1 WHERE Cname='数据库';
SELECT @2=Avg(Grade) FROM SC WHERE Cno='2';
UPDATE AvgGRADE SET Avg=@2 WHERE Cname='数学';
SELECT @3=Avg(Grade) FROM SC WHERE Cno='3';
UPDATE AvgGRADE SET Avg=@3 WHERE Cname='信息系统';
SELECT @8=Avg(Grade) FROM SC WHERE Cno='8';
UPDATE AvgGRADE SET Avg=@8 WHERE Cname='离散数学'

 EXEC Avg_GRADE
 SELECT * FROM AvgGRADE

2.3
alter table SC
add level char(1)

create procedure Proc_GetLevel
as
	update SC set level = 'A' where Grade >= 80;
	update SC set level = 'B' where Grade < 40 and Grade >= 20;
	update SC set level = 'C' where Grade < 60 and Grade >= 40;
	update SC set level = 'D' where Grade < 80 and Grade >= 60;
	update SC set level = 'E' where Grade < 20;