6、
（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)
	)

进行检测：

INSERT INTO Staff(Sno,Sname,Sage,SJob,Sal)
VALUES('20121501','佟陆陆',26,'经理',5000)
SELECT * FROM Staff

INSERT INTO Staff(Sno,Sname,Sage,SJob,Sal)
VALUES('20121502','张三',63,'董事长',10000)
SELECT * FROM Staff

（1）首先根据题目的要求为表添加内容

/*建立新表SCount，记录离散数学成绩的分布*/
DROP TABLE IF EXISTS SCount;
CREATE TABLE SCount
(
Score CHAR(20),
Num INT 
)
INSERT INTO SCount VALUES('[0,60)',0)
INSERT INTO SCount VALUES('[60,70)',0)
INSERT INTO SCount VALUES('[70,80)',0)
INSERT INTO SCount VALUES('[80,90)',0)
INSERT INTO SCount VALUES('[90,100]',0)
SELECT * FROM SCount

/*建立存储过程*/
IF(exists(select * from sys.objects where name='Proc_TRANSFER1'))
	DROP PROCEDURE Proc_TRANSFER1
GO
CREATE PROCEDURE Proc_TRANSFER1
AS
BEGIN TRANSACTION TRANS
	DECLARE /*定义变量*/
	@less60 INT,
	@l70m60 INT,
	@l80m70 INT,
	@l90m80 INT,
	@m90    INT,
	@CNO CHAR(4);
	SELECT @CNO =Cno FROM Course WHERE Cname='离散数学'

	/*小于60*/
	SELECT @less60=COUNT (*)
	FROM SC
	WHERE Grade<60 AND Cno=@CNO
	UPDATE SCount SET Num=@less60 WHERE Score='[0,60)'

	/*大于等于60小于70*/
	SELECT @l70m60=COUNT (*)
	FROM SC
	WHERE Grade<70 AND Grade>=60 AND Cno=@CNO
	UPDATE SCount SET Num=@l70m60 WHERE Score='[60,70)'

	/*大于等于70小于80*/
	SELECT @l80m70=COUNT (*)
	FROM SC
	WHERE Grade<80 AND Grade>=70 AND Cno=@CNO
	UPDATE SCount SET Num=@l80m70 WHERE Score='[70,80)'

	/*大于等于80小于90*/
	SELECT @l90m80=COUNT (*)
	FROM SC
	WHERE Grade<90 AND Grade>=80  AND Cno=@CNO
	UPDATE SCount SET Num=@l90m80 WHERE Score='[80,90)'

	/*大于等90*/
	SELECT @m90=COUNT (*)
	FROM SC
	WHERE Grade>=90 AND Cno=@CNO
	UPDATE SCount SET Num=@m90 WHERE Score='[90,100]'

检测：

EXEC Proc_TRANSFER1/*这句话一定要写！！！*/

SELECT * FROM SCount

（2）

/*建立新表SC_Avg，记录任意一门课的平均成绩*/
DROP TABLE IF EXISTS SC_Avg;
CREATE TABLE SC_Avg
(
Cno CHAR(4),
Cname CHAR(10),
Avg_Score FLOAT 
)
INSERT INTO SC_Avg VALUES('1','高等数学',0)
INSERT INTO SC_Avg VALUES('2','计算机基础',0)
INSERT INTO SC_Avg VALUES('3','离散数学',0)
SELECT * FROM SC_Avg

/*建立存储过程*/
IF(exists(select * from sys.objects where name='Proc_TRANSFER1'))
	DROP PROCEDURE Proc_TRANSFER1
GO
CREATE PROCEDURE Proc_TRANSFER1
AS
BEGIN TRANSACTION TRANS
	DECLARE /*定义变量*/
	@Avg1 FLOAT,
	@Avg2 FLOAT,
	@Avg3 FLOAT;
	
	SELECT @Avg1=AVG(Grade)
	FROM SC
	WHERE Cno ='1'
	UPDATE SC_Avg SET Avg_Score=@Avg1 WHERE Cno ='1'

	SELECT @Avg2=AVG(Grade)
	FROM SC
	WHERE Cno ='2'
	UPDATE SC_Avg SET Avg_Score=@Avg2 WHERE Cno ='2'

	SELECT @Avg3=AVG(Grade)
	FROM SC
	WHERE Cno ='3'
	UPDATE SC_Avg SET Avg_Score=@Avg3 WHERE Cno ='3'
	COMMIT TRANSACTION TRANS
	/*这行也很重要，没有会报错*/

检测：

EXEC Proc_TRANSFER1

SELECT * FROM SC_Avg

（3）

/*为SC表增加字段NewGrade*/
ALTER TABLE SC ADD NewGrade CHAR(1);
/*建立存储过程*/
IF(exists(select * from sys.objects where name='Proc_TRANSFER1'))
	DROP PROCEDURE Proc_TRANSFER1
GO
CREATE PROCEDURE Proc_TRANSFER1
AS
BEGIN TRANSACTION TRANS
	
	UPDATE SC SET NewGrade='D' WHERE Grade<60;
	UPDATE SC SET NewGrade='C' WHERE Grade>=60 AND Grade<75;
	UPDATE SC SET NewGrade='B' WHERE Grade>=75 AND Grade<85;
	UPDATE SC SET NewGrade='A' WHERE Grade>=85;
	ALTER TABLE SC DROP COLUMN Grade
	/*删除字段Grade*/
	COMMIT TRANSACTION TRANS

检测：

EXEC Proc_TRANSFER1

SELECT * FROM SC

心得：

这次作业又写了好久，报错了好多次，最后终于写出来了