数据库作业14：第五章： 数据库完整性 习题 + 存储过程

6、（1）

CREATE TABLE Staff 	
(Sno CHAR(20) PRIMARY KEY, 	
 Sname CHAR(20), 	
 Sage SMALLINT, 	
 Job CHAR(20), 	
 Pal INT, 	
 Num CHAR(20) 	
 );
 
 CREATE TABLE Depart 	
 (Num CHAR(20) PRIMARY KEY, 	
  Dname CHAR(20), 	
  Mname CHAR(20), 	
  Tel CHAR(20) 
 );

(2)

CREATE TABLE Depart 	
 (Num CHAR(20) PRIMARY KEY, 	
  Dname CHAR(20), 	
  Mname CHAR(20), 	
  Tel CHAR(20) 
 );
 
CREATE TABLE Staff 	
(Sno CHAR(20) PRIMARY KEY, 	
 Sname CHAR(20), 	
 Sage SMALLINT, 	
 Job CHAR(20), 	
 Pal INT, 	
 Num CHAR(20),
 FOREIGN KEY(Num) REFERENCES Depart(Num) 	
 );

(3)

CREATE TABLE Depart 	
 (Num CHAR(20) PRIMARY KEY, 	
  Dname CHAR(20), 	
  Mname CHAR(20), 	
  Tel CHAR(20) 
 );
 
CREATE TABLE Staff 	
(Sno CHAR(20) PRIMARY KEY, 	
 Sname CHAR(20), 	
 Sage SMALLINT CHECK(Sage<=60), 	
 Job CHAR(20), 	
 Pal INT, 	
 Num CHAR(20),
 FOREIGN KEY(Num) REFERENCES Depart(Num) 	
 );

2、（1）

CREATE TABLE S
( Grades CHAR(20), 			
  Num INT 
);

INSERT INTO S
VALUES('>=90',0); 
INSERT INTO S
VALUES('[80,90)',0); 
INSERT INTO S  
VALUES('[70,80)',0); 
INSERT INTO S 
VALUES('[60,70)',0); 
INSERT INTO S  
VALUES('<60',0);

SELECT *
FROM S;

IF (exists (select * from sys.objects where name = 'math')) 
DROP PROCEDURE math
GO 
CREATE PROCEDURE math 
AS 
BEGIN 
DECLARE  @beyond90 INT, 
         @80UNTIL90 INT, 		 
         @70UNTIL80 INT, 
         @60UNTIL70 INT,  
         @below60 INT; 
         
         SELECT  @beyond90=COUNT(*) 		
         FROM SC 		
         WHERE Grade>=90 AND Cno='2'  		
         SELECT @80UNTIL90=COUNT(*) 		
         FROM SC	
         WHERE Grade>80 AND Cno='2' 		
         SELECT @70UNTIL80=COUNT(*) 		
         FROM SC 		
         WHERE Grade>70 AND Cno='2' 		
         SELECT @60UNTIL70=COUNT(*) 		
         FROM SC		
         WHERE Grade>60 AND Cno='2' 		
         SELECT @below60=COUNT(*) 		
         FROM SC 		
         WHERE Grade<=60 AND Cno='2' 

         UPDATE S
         SET Num=@beyond90 
         WHERE Grades='>=90';
         UPDATE S 
         SET Num=@80UNTIL90
         WHERE Grades='[80,90)';
         UPDATE S 
         SET Num=@70UNTIL80
         WHERE Grades='[70,80)'; 
         UPDATE S 
         SET Num=@60UNTIL70 
         WHERE Grades='[60,70)'; 
         UPDATE S 
         SET Num=@below60
         WHERE Grades='<=60'; 
         END; 
         EXEC math 

不截图啦，我写的人不是很多欸
(2)

CREATE TABLE A 
(Cno CHAR(20), 
 Cname CHAR(20), 
 Score FLOAT 
);

INSERT INTO A VALUES('1','数据库',0) ;
INSERT INTO A VALUES('2','离散数学',0); 
INSERT INTO A VALUES('3','信息系统',0);

IF (exists (select * from sys.objects where name = 'AVG')) 
DROP PROCEDURE AVG 
GO 
AS 
BEGIN 	
DECLARE 	
@Avg1 FLOAT, 	
@Avg2 FLOAT, 	
@Avg3 FLOAT; 	

SELECT @Avg1=AVG(Grade) 	
FROM SC 	
WHERE Cno ='1' 	

UPDATE A 
SET Score=@Avg1 
WHERE Cno ='1' 	

SELECT @Avg2=AVG(Grade) 	
FROM SC 	
WHERE Cno ='2' 	

UPDATE A 
SET Score=@Avg2 
WHERE Cno ='2' 	

SELECT @Avg3=AVG(Grade) 	
FROM SC 	
WHERE Cno ='3' 	

UPDATE A
SET Score=@Avg3 
WHERE Cno ='3' 


EXEC AVG

(3)

ALTER TABLE SC ADD LEVEL CHAR(2);

IF(exists(select * from sys.objects where name='B')) 	
DROP PROCEDURE B 
GO
CREATE PROCEDURE B
AS
UPDATE SC 
SET LEVEL='A' 
WHERE Grade>90 
UPDATE SC 
SET LEVEL='B' 
WHERE Grade BETWEEN 80 AND 90 
UPDATE SC 
SET LEVEL='C' 
WHERE Grade BETWEEN 70 AND 80 
UPDATE SC 
SET LEVEL='D' 
WHERE Grade BETWEEN 60 AND 70 
UPDATE SC 
SET LEVEL='E' 
WHERE Grade<60  

EXEC B