CREATE TABLE DEPT
(Deptno NUMBER(2),
Deptname VARCHAR(10),
Manager VARCHAR(10),
PhoneNumber Char(12)
CONSTRAINT PK_SC PIMARY KEY(Deptno));
CREATE TABLE EMP
(Empno NUMBER(4),
ENAME VARCHAR(10),
Age NUMBER(2),
CONSTARINT C1 CHECK(Aage<=60),
Job VARCHAR(9),
Sal NUMBER(7,2),
Deptno NUMBER(2),
CONSTRAINT FK_DEPTNO
FOREIGN KEY(Deptno)
REFERENCES DEPT(Deptno));
(1)
建表过程:
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]'
(2)
建表:
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
(3)
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
我觉得第二题有点。。。。。难,时间仓促,还没来得及实验,实验结果正确,我会把截图补上。