数据库作业14 第五章习题

CREATE TABLE 部门
(	部门号 CHAR(20) PRIMARY KEY,		
	名称 CHAR(20),
	经理名 CHAR(20),
	电话 CHAR(20)
);
CREATE TABLE 职工
(	职工号 CHAR(20) PRIMARY KEY,		
	姓名 CHAR(20),
	年龄 INT CHECK(年龄<=60),		
	职务 CHAR(20),
	工资 INT,
	部门号 CHAR(20),
	FOREIGN KEY (部门号) REFERENCES 部门(部门号)	
);

CREATE TABLE NUM
(
  division CHAR(20),   /*成绩划分*/
  number      INT       /*人数*/
);
//创建储存分段和人数的表

IF (exists (select * from sys.objects where name = 'count_dispersemath'))
DROP PROCEDURE count_dispersemath0
GO
CREATE OR REPLACE PROCEDURE fenshu() 
--定义存储过程
AS
DECLARE  @a90 INT,  --定义大于90分的人数
    @a8090 INT,  --定义大于80分的人数
    @a7080 INT,  --定义大于70分的人数
    @a6070 INT, --定义大于60分的人数
    @a60 INT;  --定义小于60分的人数
BEGIN
SELECT Cno
INTO countcno 
FROM course
WHERE Cname = '离散数学';
SELECT @a60=COUNT(*)
FROM SC
WHERE Cno = '8' AND Grade < 60;
SELECT  @a6070=COUNT(*)
FROM SC
WHERE Cno = '8' AND Grade >= 60 AND Grade < 70;
SELECT @a7080=COUNT(*)
FROM SC
WHERE Cno = '8' AND Grade >= 70 AND Grade < 80;
SELECT @a8090=COUNT(*)
FROM SC
WHERE Cno = '8' AND Grade >= 80 AND Grade < 90;
SELECT @a90=COUNT(*)
FROM SC
WHERE Cno = '8' AND Grade >= 90;
create table countScore( scorestage char(10), number smallint);
insert into countscore values('x<60', 60);
insert into countscore values('60<=x<70', 6070);
insert into countscore values('70<=x<80', 7080);
insert into countscore values('80<=x<90', 8090);
insert into countscore values('x>=90', 90);
END

（2）

CREATE OR REPLACE PROCEDURE  P() 
AS
BEGIN 
declare curname char(40) default ‘*‘; 
declare curcno char(4) default ‘**‘; 
declare curavg char(4); 
declare  mycursor cursor for 
select Cno, Cname 
from course;
open mycursor;
loop 
fetch mycursor into curcno, curname;  
    select avg(grade) into curavg from sc 
    where Cno = curcno; 
    insert into scoreavg values(curcno, curname, curavg); 
    end loop; 
close mycursor; 
END 

（3）

ALTER TABLE SC ADD ABCDE CHAR(1);

IF (exists (select * from sys.objects where name = '成绩等级制')) 
	DROP PROCEDURE 成绩等级制
GO
CREATE PROCEDURE 成绩等级制
AS
BEGIN TRANSACTION TRANS     
   	UPDATE SC SET ABCDE='E' WHERE Grade <60 AND Grade >0;
	UPDATE SC SET ABCDE='D' WHERE Grade <70 AND Grade >=60;
	UPDATE SC SET ABCDE='C' WHERE Grade <80 AND Grade >=70;
	UPDATE SC SET ABCDE='B' WHERE Grade <90 AND Grade >=80;
	UPDATE SC SET ABCDE='A' WHERE Grade <100 AND Grade >=90;
	RETURN;