答案
一
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
create default stu_d_df as '信息学院'
sp_bindefault stu_d_df,'student_info.院系' sp_unbindefault 'student_info.院系' drop default stu_d_df /* 4.5 */
create table stu_con (学号char(4), 姓名char(8), 性别char(2),
出生日期datetime,
constraint pk_sid primary key (学号), constraint uk_name unique (姓名),
constraint ck_bday check (出生日期>'1988-1-1') )
alter table stu_con
add constraint df_sex default '男' for 性别 /* 4.6 */
alter table grade
add constraint fk_sid foreign key (学号) references student_info(学号)
五
select 学号,分数,等级= case
when 分数>=90 then 'A'
when 分数>=80 and 分数<90 then 'B' when 分数>=70 and 分数<80 then 'C' when 分数>=60 and 分数<70 then 'D' else 'E' end
from grade
declare @a float,@b float,@c float
select @a=max(分数),@b=min(分数),@c=avg(分数)
from grade a inner join curriculum b on a.课程编号=b.课程编号 where 课程名称='高等数学' select @a,@b,@c
declare @n int,@s int set @s=0 set @n=1 while @n<=10 begin
set @s=@s+@n set @n=@n+1 end
print '1+2+3+...+10='+str(@s,2)
declare @i int,@n int,@s int set @i=1 set @n=0 set @s=0
while @i<=100 begin
if @i%7=0 begin
set @n=@n+1 set @s=@s+@i end
set @i=@i+1 end
select @n as 个数,@s as 总和
declare @sex char(2),@n1 int,@n2 int set @n1=0 set @n2=0
declare cur_stu cursor for select 性别from student_info open cur_stu
while @@fetch_status=0 begin
if @sex='男' set @n1=@n1+1 else
set @n2=@n2+1
fetch next from cur_stu into @sex end
select @n1 as 男,@n2 as 女 close cur_stu
deallocate cur_stu
六
declare @a float,@b float,@c float
select @a=max(分数),@b=min(分数),@c=avg(分数)
from grade a inner join curriculum b on a.课程编号=b.课程编号 where 课程名称='高等数学' select @a,@b,@c