SQl_Server上机实验_答案_华中科技大学.doc

实验二4.create database studbSp_helpdb5. alter database studbmodify file(name=studb,size=5MB,maxsize=20MB,filegrowth=1MB)7. ALTER DATABASE studbMODIFY NAME=student_db8. DROP DATABASE student_db实验三5. USE students_dbGOCREATE TABLE grade(学号 char(4),课程编号 char(4),分数 decimal(5)8.USE studentdbGOINSERT gradeVALUES(0004,0001,80)9. USE studentdbGOALTER TABLE curriculumALTER COLUMN 课程编号 char(4) NOT NULL10. USE studentdbGOALTER TABLE gradeALTER COLUMN 分数 real11. USE studentdbGOALTER TABLE student_infoALTER COLUMN 姓名 。不会12. USE studentdbGODELETE FROM gradeWHERE学号=000413. USE studentdbGOUPDATE grade SET 分数=90WHERE 学号=0003 and 课程编号=000514. USE studentdbGOALTER TABLE grade ADD 备注 VARCHAR(20) NULL15. USE studentdbGODROP TABLE grade实验四2（1） select 学号,姓名,出生日期 from student_info（2） select 姓名,家庭住址 from student_info where学号=0002（3） select 学号,姓名 from student_info where 性别=男3（1）select 学号,分数 from grade where分数 between 80 and 90（2）select avg(分数) from grade where 课程编号=0003（3）select 课程编号,count(课程编号) as 人数 from grade group by 课程编号（4）select 姓名,出生日期from student_info order by出生日期desc（5）select 学号,姓名 from student_info where 姓名like 张%4（1）SELECT 姓名,出生日期 FROM student_info WHERE性别=(SELECT 性别 FROM student_info WHERE 姓名=刘卫平)（2）SELECT 学号,姓名,性别 FROM student_info WHERE student_info.学号 IN (SELECT 学号 FROM grade WHERE 课程编号IN (0002, 0005)（3）SELECT 课程编号,分数 FROM grade WHERE 学号=0001 AND 分数ANY(SELECT 分数 FROM grade WHERE 学号=0002)（4）SELECT 课程编号,分数 FROM grade WHERE 学号=0001 AND 分数ALL(SELECT 分数 FROM grade WHERE 学号=0002)5（1）SELECT student_info.学号,姓名,分数FROM student_info,gradeWHERE student_info.学号=grade.学号 AND 分数 BETWEEN 80 AND 90（2）SELECT student_info.学号,姓名,分数FROM student_infoINNER JOIN grade ON student_info.学号=grade.学号INNER JOIN curriculum ON 课程名称=C语言程序设计（3）SELECT student_info.学号,student_info.姓名,curriculum.课程名称,grade.分数FROM student_infoINNER JOIN grade ON student_info.学号=grade.学号 and 性别=男INNER JOIN curriculum on curriculum.课程编号=grade.课程编号（4）SELECT student_info.学号,max(grade.分数)FROM student_infoINNER JOIN grade ON student_info.学号=grade.学号group by student_info.学号（5）SELECT student_info.学号,sum(grade.分数)FROM student_infoleft outer JOIN grade ON student_info.学号=grade.学号group by student_info.学号（6）第一步insert gradevalues (0004,0006,76)第二步SELECT curriculum.课程编号,curriculum.课程名称,count(grade.学号) as 选修人数FROM graderight outer JOIN curriculum on curriculum.课程编号=grade.课程编号group by curriculum.课程编号,curriculum.课程名称6 union select 课程编号 as u_编号 ,课程名称 as u_名称 from curriculum7delete from totalgradewhere 总成绩 is null 实验五1ALTER TABLE student_infoADD CONSTRAINT student_idxPRIMARY KEY CLUSTERED (学号)ALTER TABLE curriculumADD CONSTRAINT curriculum_idxPRIMARY KEY CLUSTERED (课程编号)2无3 CREATE NONCLUSTERED INDEX grade_index ON grade(分数)4CREATE UNIQUE INDEX grade_id_c_ind ON grade(学号,课程编号)5sp_helpindex grade6无7sp_rename grade.grade_index,grade_ind, INDEX8DROP INDEX grade.grade_indsp_helpindex grade9execute sp_fulltext_database enable10execute sp_fulltext_catalog FT_stu,createexec sp_fulltext_tablestudent_info,create,FT_stu,PK_student_info11execute sp_fulltext_column student_info,家庭住址,addexecute sp_fulltext_table student_info,activate12execute sp_fulltext_catalog FT_stu,start_full13SELECT 姓名,家庭住址FROM student_infoWHERE CONTAINS(家庭住址,25号)1415无16create view v_stu_casselect student_info.学号,student_info.姓名,grade.课程编号from student_info inner join grade onstudent_info.学号=grade.学号select 学号,姓名,课程编号from v_stu_c where 学号=000317create view v_stu_casselect student_info.学号,student_info.姓名,curriculum.课程名称,grade.分数from student_info inner join grade on student_info.学号=grade.学号inner join curriculum on grade.课程编号=curriculum.课程编号select 学号,姓名,课程名称,分数from v_stu_g where 学号=000118alter view v_stu_casselect grade.学号,姓名,count(grade.课程编号)as 课程数目 from student_info inner join grade on student_info.学号=grade.学号 group by grade.学号,姓名19ALTER VIEW v_stu_i(学号,姓名,性别)AS SELECT 学号,姓名,性别 FROM student_info20sp_rename v_stu_i,v_stu_info21insert into v_stu_ivalues (0015,陈婷,女)22delete from v_stu_gwhere 学号=001523update v_stu_gset 分数=84 where 姓名=刘卫平and 课程名称=高等数学24drop view v_stu_c,v_stu_g实验六1(1) SELECT*INTO stu_phone FROM student_infoALTER TABLE stu_phone ADD 电话号码 CHAR(7)NULL(2) CREATE rule phone_ruleASphone LIKE 0-90-90-90-90-90-90-9(3) sp_bindrule phone_rule,stu_phone.电话号码(4)应修改括号中的第三个字符串为7位09数字3Create rule stusex_ruleAs stusex in (男,女)sp_bindrule stusex_rule,stu_phone.性别4Sp_help stusex_ruleSp_helptext stusex_ruleSp_rename stusex_rule,stu_s_rule5sp_unbindrulestu_phone.性别Drop rule stu_s_rule6(1) -创建日期型默认对象df_dateCREATE default df_dateAS 2006-4-12GO-创建字符型默认对象df_charCREATE DEFAULT df_charas unknownGO-创建货币型默认对象df_moneyCREATE DEFAULT df_moneyAS $100GO(2) CREATE TABLE stu_fee(学号 char(10)NOT NULL,姓名 char(8)NOT NULL,学费 money,交费日期 datetime,电话号码 char(7)(3) Sp_bindefault df_money,stu_fee.学费GOSp_bindefault df_date,stu_fee.交费日期GOSp_bindefault df_char,stu_fee.电话号码GO(4) INSERT INTO stu_fee(学号,姓名)values(0001,刘卫平)INSERT INTO stu_fee(学号,姓名,学费)values(0001,张卫民,$120)INSERT INTO stu_fee(学号,姓名,学费,交费日期)VALUES(0001,马东,$110,2006-5-12)(5) sp_unbindefault stu_fee.电话号码drop DEFAULT df_charsp_unbindefault stu_fee.学费 gosp_unbindefault stu_fee.交费日期godrop DEFAULT df_date,df_moneygo8 ALTER TABLE student_info ADD 院系 CHAR(7) Gocreate default stu_d_dfas 信息院gosp_bindefault stu_d_df,student_info.院系gosp_unbindefault student_info.院系godrop default stu_d_dfgo9(1)create table stu_con(学号 char(4) constraint pk_sid primary key,姓名 char(8) constraint uk_name unique,性别 char(2) constraint df_sex default 男,出生日期 datetime constraint ck_beday check (出生日期1988-1-1),家庭住址 varchar(50)(2) insert stu_convalues(0009,张晓东,1989-4-6,)goinsert stu_convalues(0010,李梅,女,1983-8-5,)goinsert stu_convalues(0011,王强,1988-9-10,)goinsert stu_convalues(0012,王强,1989-6-3,)go所影响的行数为 1 行）服务器: 消息 547，级别 16，状态 1，行 1INSERT 语句与 COLUMN CHECK 约束 ck_beday 冲突。该冲突发生于数据库 studentsdb，表 stu_con, column 出生日期。语句已终止。（所影响的行数为 1 行）服务器: 消息 2627，级别 14，状态 2，行 1违反了 UNIQUE KEY 约束 uk_name。不能在对象 stu_con 中插入重复键。语句已终止。(3) ALTER TABLE stu_conDROP CONSTRAINT uk_name,df_sex,ck_beday11alter table grade with check addconstraint ufk_sid foreign key(学号) references student_info (学号)insert gradevalues(0100,0001,78)服务器: 消息 547，级别 16，状态 1，行 1INSERT 语句与 COLUMN FOREIGN KEY 约束 ufk_sid 冲突。该冲突发生于数据库 studentsdb，表 student_info, column 学号。语句已终止。ALTER TABLE gradeDROP CONSTRAINT ufk_sid实验七3declare row intset row=(select count(*) from grade)select row4两个全局变量只显示上一次操作的信息5declare boy char(2), girl char(2)select boy =(select count(性别) from student_info where 性别=男)select girl =(select count(性别) from student_info where 性别=女)select boy as 男, girl as 女6declare grademax int,grademin int,gradeavg int select grademax=max(分数) ,grademin=min(分数),gradeavg=avg(分数)from grade where 课程编号=(select 课程编号 from curriculum where 课程名称=高等数学)select grademax as 最高分,grademin as 最低分,gradeavg as 平均分7DECLARE student datetimeSET student = getdate() SELECT 姓名,year(student)-year(出生日期)AS年龄FROM student_info88 233 22510counter的值现在为：1counter的值现在为：2counter的值现在为：3counter的值现在为：4counter的值现在为：5counter的值现在为：6counter的值现在为：7counter的值现在为：8counter的值现在为：911SELECT 学号,分数,等级 = CASEWHEN 分数=90 THEN AWHEN 分数=80 AND 分数=70 AND 分数=60 AND 分数70 THEN D ELSE EENDFROM grade12WHILE (SELECT AVG(分数)FROM grade) 95 BREAKELSESELECT AVG(分数) from gradeEND13declare n int,i intset n=0set i=1while i=20 begin set n=n+i set i=i+1 endselect n14declare n int,i intset i=1set n=1while n=100 begin set i=i+1 select n set n=i*i End15declare n int,i int,a intset n=3while n=100 begin set i=2 set a=sqrt(n) while i=a begin if(n%i0) set i=i+1 else break end if(ia) select n set n=n+2end实验八2CREATE PROCEDURE stu_info name varchar(40) =刘卫平ASSELECT a.学号,姓名,课程编号,分数FROM