SQL数据库期末考试复习题(9)

WHERE 专业= '通信工程' COMPUTE COUNT(学号)

【例4.67】 将学生按专业排序，并汇总各专业人数和平均学分。 SELECT 学号, 姓名, 出生时间, 总学分 FROM XSB ORDER BY 专业 COMPUTE COUNT(学号), AVG(总学分) BY 专业

【例4.74】 创建CS_KC视图，包括计算机专业各学生的学号、其选修的课程号及成绩。要保证对该视图的修改都要符合专业为计算机这个条件。

CREATE VIEW CS_KC WITH ENCRYPTION AS SELECT XSB.学号, 课程号, 成绩 FROM XSB, CJB WHERE XSB.学号 =CJB.学号 AND 专业 = '计算机' WITH CHECK OPTION

【例4.75】 创建计算机专业学生的平均成绩视图CS_KC_AVG，包括学号（在视图中列名为num）和平均成绩（在视图中列名为score_avg）。 CREATE VIEW CS_KC_AVG(num,score_avg) AS SELECT 学号, AVG(成绩) FROM CJB GROUP BY 学号

【例4.77】 查找平均成绩在80分以上的学生的学号和平均成绩。

本例首先创建学生平均成绩视图XS_KC_AVG，包括学号（在视图中列名为num）和平均成绩（在视图中列名为score_avg）。

CREATE VIEW XS_KC_AVG ( num,score_avg ) AS SELECT 学号, AVG(成绩) FROM CJB GROUP BY 学号

第5章要求掌握例题：

【例5.18】 将学号为081101的学生的总学分使用循环修改到大于等于60，每次只加2，并判断循环了多少次。 DECLARE @num INT SET @num=0

WHILE (SELECT 总学分 FROM XSB WHERE 学号='081101')<60 BEGIN UPDATE XSB SET 总学分=总学分+2 WHERE 学号= '081101' SET @num=@num+1 END

SELECT @num AS 循环次数

26

【例5.31】 创建用户定义函数，实现计算全体学生某门功课的平均成绩的功能。 CREATE FUNCTION average(@cnum char(20)) RETURNS int AS BEGIN DECLARE @aver int SELECT @aver= ( SELECT avg(成绩) FROM CJB WHERE 课程号=@cnum GROUP BY 课程号 ) RETURN @aver END

【例5.32】 调用例5.31定义的函数average。新建一个查询，输入以下语句并执行： USE PXSCJ /*假设用户函数average在此数据库中已定义*/ GO

DECLARE @course1 char(20) /*定义局部变量*/ DECLARE @aver1 int

SELECT @course1 = '101' /*给局部变量赋值*/ /*调用用户函数将返回值赋给局部变量*/ SELECT @aver1=dbo.average(@course1)

SELECT @aver1 AS '101课程的平均成绩' /*显示局部变量的值*/

第6章要求掌握例题： 【例6.11】 首先在PXSCJ数据库中定义表book及名为 today 的默认值，然后将其绑定到 book表的hire_date列。 CREATE TABLE book ( book_id char(6), name varchar(20) NOT NULL, hire_date datetime NOT NULL ) GO

CREATE DEFAULT today AS getdate() GO

EXEC sp_bindefault 'today', 'book.hire_date'

【例6.13】 建立表KCB2，同时定义总学分的约束条件为0～60。 CREATE TABLE KCB2

( 课程号 char(6) NOT NULL, 课程名 char(8) NOT NULL, 学分 tinyint CHECK (学分>=0 AND 学分<=60) NULL /*通过CHECK子句定义约束条件*/)

【例6.16】 修改XSB2表，向其中添加一个“身份证号码”字段，对该字段定义UNIQUE约束。对“出生时间”字段定义UNIQUE约束。

27

ALTER TABLE XSB2 add 身份证号码 char(20)

constraint sf_uk unique nonclustered(身份证号码) GO

ALTER TABLE XSB2

add constraint CJSJ_UK UNIQUE NONCLUSTERED(出生时间)

【例6.19】 创建一个表student1，只考虑学号和出生日期两列，出生日期必须大于1980年1月1日，并命名CHECK约束。 CREATE TABLE student1 ( 学号 char(6) NOT NULL, 出生时间 datetime NOT NULL,

CONSTRAINT DF_student1_cjsj

CHECK(出生时间>'1980-01-01') )

【例6.21】 通过修改PXSCJ数据库的CJB表，增加成绩字段的CHECK约束。

ALTER TABLE CJB

ADD CONSTRAINT cj_constraint

CHECK(成绩>=0 AND 成绩<=100)

【例6.23】 如下程序创建一个规则，并绑定到表KCB的课程号列，用于限制课程号的输入范围。

CREATE RULE kc_rule AS @range like '[1-5][0-9][0-9]' GO

EXEC sp_bindrule 'kc_rule', 'KCB.课程号' GO

【例6.24】 创建一个规则，用以限制输入到该规则所绑定的列中的值只能是该规则中列出的值。

CREATE RULE list_rule AS @list IN ('C语言', '离散数学', '微机原理') GO

EXEC sp_bindrule 'list_rule', 'KCB.课程名' GO

【例6.25】 定义CJB外键。 create table cjb1

( 学号 char(6) not null foreign key (学号) references XSB(学号) ,

课程号 char(3) not null foreign key (课程号) references KCB(课程号) ,

28

成绩 int null default 0 check(成绩>=0 and 成绩<=100), primary key(学号,课程号))

alter table CJB

add foreign key (学号) references XSB(学号)

alter table CJB

add foreign key (课程号) references KCB(课程号)

第7章要求掌握例题： 【例7.2】 从PXSCJ1数据库的三个表中查询某人指定课程的成绩和学分。该存储过程接受与传递参数精确匹配的值。

CREATE PROCEDURE student_info1 @name char (8), @cname char(16) AS SELECT a.学号, 姓名, 课程名, 成绩, t.学分 FROM XSB a INNER JOIN CJB b ON a.学号 = b.学号 INNER JOIN KCB t ON b.课程号= t.课程号 WHERE a.姓名=@name and t.课程名=@cname 或

CREATE PROCEDURE student_info1 @name char (8), @cname char(16) AS SELECT a.学号, 姓名, 课程名, 成绩, t.学分 FROM XSB a ， CJB b，KCB t WHERE a.姓名=@name and t.课程名=@cname and a.学号 = b.学号and b.课程号= t.课程号

【例7.4】 从三个表的连接中返回指定学生的学号、姓名、所选课程名称及该课程的成绩。该存储过程在参数中使用了模式匹配，如果没有提供参数，则使用预设的默认值。 CREATE PROCEDURE st_info @name varchar(30) = '李%' AS SELECT a.学号,a.姓名,c.课程名,b.成绩 FROM XSB a INNER JOIN CJB b ON a.学号 =b.学号 INNER JOIN KCB c ON c.课程号= b.课程号 WHERE 姓名 LIKE @name EXECUTE st_info '王%' …… 此处隐藏：1778字，全部文档内容请下载后查看。喜欢就下载吧 ……