数据库sql复习题库4.doc

文档介绍：,属性如下:CLASSNO,DEPARTNO,CLASSNAME;类型均为字符型;长度分别为8、2、20且均不允许为空。
CREATE ________ CLASS
(CLASSNO ______ (8) NOT NULL,
DEPARTNO CHAR (2) NOT NULL,
CLASSNAME CHAR (____) NOT NULL
)
,并利用游标遍历,显示整个结果集。
USE XK
DECLARE ***@COUNO VARCHAR(3),***@COUNAME VARCHAR(20)
_________________________
FOR SELECT COUNO,COUNAME FROM COURSE ORDER BY COUNO
_________________________
FETCH NEXT FROM CRSCOURSE INTO ***@COUNO,***@COUNAME
WHILE @***@FETCH_STATUS=0
BEGIN
PRINT ’课程号:’+ ***@COUNO +’课程名称:’+ ***@CouName
FETCH NEXT FROM CRSCOURSE INTO ***@COUNO,***@COUNAME
END
_________________________
DEALLOCATE CRSCOURSSE
,该视图仅查看“STUDENT”表中“00电子商务”班的学生信息。
USE XK
CREATE ____________ V_STUDENT
AS
SELECT *
FROM ____________
WHERE CLASSNO=’20000001’
5、删除stu_score表中的外键约束con_num
TABLE stu_score
CONSTRAINT con_num
6、为student_info表添加“总学分”列,并为该列建立默认对象df_credit,使其默认值为0
Create df_credit as 0
Alter table student_info add 总学分 real
Exec ‘df_credit’,’’
7、创建和执行带有输入和输出参数的存储过程proc_avg,查sc表中输入课程编号的最高分,最低分和平均分。
Create proc proc_avg
***@cid char(4),***@max_scr int ,***@min_scr int output,***@avg_scr int output
As
Select ***@max_scr=max(grade),***@min_scr=min(grade),***@avg_scr=
From grade
o=***@cid
Group o
执行:
Declare ***@maxs int,***@mins int,***@avgs int
Exec proc_avg ‘3’,***@maxs output, ,***@avgs output
Select ***@maxs,***@mins,***@avgs
8. 建立表的命令如下,请补全约束定义。
cr