发布时间 : 星期三 文章SQL数据库期末考试复习题更新完毕开始阅读
WHERE CJB.课程号 = KCB.课程号 AND CJB.学号 = XSB.学号 GROUP BY 课程名, 专业 WITH ROLLUP
【例4.58】 在PXSCJ数据库上产生一个结果集，包括每个专业的男生、女生人数、总人数及男生总数、女生总数、学生总人数。
SELECT 专业, 性别, COUNT(*) AS '人数' FROM XSB GROUP BY 专业,性别 WITH CUBE
【例4.59】 在PXSCJ数据库上产生一个结果集，包括各专业每门课程的平均成绩、每门课程的总平均成绩、每个专业的总平均成绩和所有课程的总平均成绩。 SELECT 课程名,专业,AVG(成绩) AS '平均成绩' FROM CJB, KCB, XSB WHERE CJB.课程号 = KCB.课程号 AND CJB.学号 = XSB.学号 GROUP BY 课程名,专业 WITH CUBE
【例4.61】 查找平均成绩在85分以上的学生的学号和平均成绩。
SELECT 学号, AVG(成绩) AS '平均成绩' FROM CJB GROUP BY 学号 HAVING AVG(成绩) > =85
【例4.62】 查找选修课程超过2门且成绩都在80分以上的学生的学号。 SELECT 学号 FROM CJB WHERE 成绩 >= 80 GROUP BY 学号 HAVING COUNT(*) > 2
【例4.63】 查找通信工程专业平均成绩在85分以上的学生的学号和平均成绩。 SELECT 学号, AVG(成绩) AS '平均成绩‘ FROM CJB WHERE 学号 IN (SELECT 学号 FROM XSB WHERE 专业 = '通信工程‘ ) GROUP BY 学号 HAVING AVG(成绩) > =85
【例4.65】 将计算机专业学生的“计算机基础”课程成绩按降序排列。 SELECT 姓名, 课程名, 成绩 FROM XSB, KCB, CJB WHERE XSB.学号 = CJB.学号 AND CJB.课程号 = KCB.课程号 AND 课程名= '计算机基础' AND 专业= '计算机' ORDER BY 成绩 DESC
【例4.66】 查找通信工程专业学生的学号、姓名、出生时间，并产生一个学生总人数行。 SELECT 学号, 姓名, 出生时间 FROM XSB
25
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