Staff(Sno,Sname,Sage,Job,Pay,Dno)
Depart(Dno,Dname,Mname,Add,Tel)
(1)显然,主码应为编号(不会重复)。
CREATE TABLE Staff
(Sno CHAR(20) PRIMARY KEY,
Sname CHAR(20),
Sage SMALLINT,
Job CHAR(20),
Pal INT,
Dno CHAR(20)
)
CREATE TABLE Depart
(Dno CHAR(20) PRIMARY KEY,
Dname CHAR(20),
Mname CHAR(20),
Tel CHAR(20)
)
(2)
CREATE TABLE Depart
(Dno 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,
Dno CHAR(20),
FOREIGN KEY(Dno) REFERENCES Depart(Dno)
)
(3)
CREATE TABLE Depart
(Dno 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,
Dno CHAR(20),
FOREIGN KEY(Dno) REFERENCES Depart(Dno)
)
(1)先来建立需要的表吧
CREATE TABLE SS
(
Score CHAR(20),
Num INT
)
INSERT INTO SS VALUES('[0,60)',0)
INSERT INTO SS VALUES('[60,80)',0)
INSERT INTO SS VALUES('[80,100]',0)
虽然我不喜欢验证的过程(其实就是懒得截图),但毕竟是第一次正经的SQL编程,就尊敬一下吧。
IF (exists (select * from sys.objects where name = 'Statistic'))
DROP PROCEDURE Statistic
GO
CREATE PROCEDURE Statistic
AS
BEGIN
DECLARE
@0until60 INT,
@60until80 INT,
@80until100 INT,
@CNO CHAR(8);
SELECT @CNO=Cno
FROM Course
WHERE Cname='离散数学';/*获取离散数学课程号*/
SELECT @80until100=COUNT(*)/*符合筛选条件开始计数*/
FROM SC
WHERE Grade>80 AND Cno=@CNO
SELECT @60until80=COUNT(*)
FROM SC
WHERE Grade>60 AND Cno=@CNO
SELECT @0until60=COUNT(*)
FROM SC
WHERE Grade>0 AND Cno=@CNO
/*更新计数*/
UPDATE SS SET Num=@0until60 WHERE Score='[0,60)';
UPDATE SS SET Num=@60until80 WHERE Score='[60,80)';
UPDATE SS SET Num=@80until100 WHERE Score='[80,100]';
END
EXEC Statistic
结果:
(2)
还是先建表
CREATE TABLE AvSC
(
Cno CHAR(20),
Cname CHAR(20),
AScore FLOAT
)
INSERT INTO AvSC VALUES('1','离散数学',0)
INSERT INTO AvSC VALUES('2','高等数学',0)
INSERT INTO AvSC VALUES('3','英语',0)
IF(exists(select * from sys.objects where name='Statistic'))
DROP PROCEDURE Statistic
GO
CREATE PROCEDURE Statistic
AS
BEGIN
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'
EXEC Statistic
(3)
ALTER TABLE SC ADD NewGrade CHAR(1);
IF(exists(select * from sys.objects where name='Statistic'))
DROP PROCEDURE Statistic
GO
CREATE PROCEDURE Statistic
AS
UPDATE SC SET NewGrade='D' WHERE Grade<60;
UPDATE SC SET NewGrade='C' WHERE Grade>=60 AND Grade<70;
UPDATE SC SET NewGrade='B' WHERE Grade>=70 AND Grade<80;
UPDATE SC SET NewGrade='A' WHERE Grade>=80;
事实证明编程的思路都是相似的,但是由于语言规定不同,会出现不少未曾设想的错误。
以及不知不觉又退回了面向过程的习惯,将来探索一下面向对象的办法吧。