发布时间 : 星期二 文章SQL Sever 2005 习题与答案更新完毕开始阅读ee28118681c758f5f71f676c
UPDATE stu2 SET 学号=REPLICATE('0',4-len(@max))+@max FROM stu2 INNER JOIN inserted on stu2.学号=inserted.学号
执行以上代码，查看studentsdb数据库中是否有stu2表，展开stu2，查看其触发器项中是否有stu_str触发器。
在查询设计器的编辑窗口输入以下代码：
INSERT INTO stu2(学号,姓名,性别) VALUES('0001','张主','女')
运行以上代码，查看stu2表的变化情况，为什么插入记录的学号值发生了改变？
答案
一
crate database studb on
(name=studb1_dat,
filename='g:\\students\\studb1.ndf', size=5Mb,
maxsize=10Mb, filegrowth=1Mb )
alter database studb add file
(name=studb1_dat,
filename='g:\\students\\studb1.ndf', size=5Mb,
maxsize=10Mb, filegrowth=1Mb )
use studentdb create table grade (学号char(4),
课程编号char(4), 分数char (5) )
alter table grade
alter column 学号char(4) not null
alter table grade add 备注 varchar(20)
insert into grade
(学号,课程编号,分数) values ('0004','0001','80')
delete from grade where 学号='0001'
update grade
set 备注='成绩良好' where 学号='0004'
二
select 学号,姓名,出生日期from student_info
select 姓名,家庭住址from student_info where 学号='0002' select 学号,姓名from student_info where 性别='男'
select 学号,分数from grade where 分数between 80 and 90 select avg(分数) from grade where 课程编号='0003'
select 课程编号,count(*) as 人数from grade group by 课程编号 select 学号,姓名from student_info where 姓名like '张%'
select 学号,姓名,性别,出生日期,家庭住址from student_info order by 性别,学号
select 学号,avg(分数) as 平均分from grade group by 学号
select 姓名,出生日期from student_info
where 性别=(select 性别from student_info where 姓名='刘卫平')
select 学号,姓名,性别from student_info where 学号in (select 学号from grade where 课程编号in ('0002','0005'))
select a.学号,b.姓名,a.课程编号,a.分数
from grade a inner join student_info b on a.学号=b.学号
where a.分数in (select max(分数) from grade group by 学号
select a.学号,姓名,sum(分数) as 总成绩
from student_info a left outer join grade b on a.学号=b.学号 group by a.学号,a.姓名
insert into grade values ('0004','0006',76)
select a.课程编号,b.课程名称,count(a.学号) as 选修人数
from grade a left outer join curriculum b on a.课程编号=b.课程编号 group by a.课程编号,b.课程名称
三
3.1 alter table student_info add constraint pk_no primary key(学号)
alter table curriculum add constraint pk_kc primary key(课程编号) 3.2 略
3.3 create index grade_index on grade(分数)
3.4 create unique index grade_id_ind on grade(学号,课程编号) 3.5 略
3.6 exec sp_helpindex grade
exec sp_helpindex student_info
3.7 drop index grade.grade_index 3.8 略
3.9 create view v_stu_c
as
select a.学号,a.姓名,b.课程编号
from student_info a inner join grade b on a.学号=b.学号
select * from v_stu_c 3.10 create view v_stu_g
as
SELECT a.学号,a.姓名,b.课程编号,b.分数,c.课程名称
FROM student_info a INNER JOIN grade b ON a.学号=b.学号 INNER JOIN curriculum c ON b.课程编号= c.课程编号
3.11 select 学号,姓名,课程名称,分数from v_stu_g where 学号='0001' 3.12 alter view v_stu_c
as
SELECT a.学号, a.姓名, COUNT(b.课程编号) AS 人数 FROM student_info a INNER JOIN grade b ON a.学号= b.学号 GROUP BY a.学号,a.姓名
3.13 insert into v_stu_i values ('0015','陈婷','女')
3.14 update v_stu_g set 分数=84 where 姓名='刘卫平' and 课程名称='高等数学' 3.15 DROP VIEW v_stu_c,v_stu_g
四
/* 4.1 */
SELECT * INTO stu_phone FROM student_info
ALTER TABLE stu_phone ADD 电话号码CHAR(7) NULL CREATE rule phone_rule AS
@phone LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' sp_bindrule phone_rule ,'stu_phone.电话号码'
INSERT INTO stu_phone(学号,姓名,电话号码) VALUES('0009','王国强','1234567') /* 4.2 */
CREATE rule stusex_rule AS
@sex in ('男','女')
sp_bindrule stusex_rule,'stu_phone.性别' /* 4.3 */
sp_helptext stusex_rule
sp_rename stusex_rule,stu_s_rule /* 4.4 */
ALTER TABLE student_info ADD 院系CHAR(10) NULL