sql 学习笔记 2

---Use stsc Go Create View st_co_sr As Select Student.stno,stname,stsex,Score.cno,cname,grade from Student,Score,Course Where Student.stno=Score.stno and Score.cno=Course.cno; Go Select * From st_co_sr; --上机实验题---2--- Use stsc Go Create View st_computer As Select stname,cname,grade from Student,Course,Score Where Student.stno=Score.stno and Score.cno=Course.cno and specialist=‘计算机‘; Go Select * from st_computer; --上机实验题---3--- Go Create View st_av As Select stname,AVG(grade) 平均分 from Student,Score Where Student.stno=Score.stno Group by stname; Go Select * from st_av; --------------第7章------------------- --上机实验题---1--- Use stsc Go --如果存在索引先删除它(没成功) --If Exists(select object_name(object_id) tableName,name,type_desc from sys.indexes where name=‘tno‘) --Drop Index Teacher.tno --Else Create Unique Clustered Index itno On Teacher(tno);--唯一聚集索引 --上机实验题---2--- Go Create NonClustered Index icredit On Teacher(credit);--非聚集索引 Go Alter Index icredit On Teacher Rebuild With(Pad_Index=On,FillFactor=90) Go --------------第8章------------------- --上机实验题---1--- Use stsc --Alter table Score drop constraint CK_grade Alter Table Score Add Constraint CK_grade check(grade between 0 and 100); --上机实验题---2--- Use stsc Alter Table Student Add Constraint DF_stsex Default(‘男‘) for stsex; --上机实验题---3--- --删除 Alter table Student drop constraint PK_stno; --添加 Alter table Student add constraint PK_stno Primary Key(stno); --上机实验题---4--- Alter table Score add constraint FK_stno foreign key(stno) references Student(stno); --上机实验题---5---(没做出来) --Declare @credit; --Create Rule credit_rule --As --@credit between 1 and 4; --上机实验题---6--- Alter Table Teacher add constraint school_dft Default(‘通信学院‘) for school; --------------第9章------------------- --上机实验题---1--- If Exists(select * from sysobjects where name=‘Score‘ and type=‘Table‘) Drop Table Score; --上机实验题---2---使用游标解决 Use stsc Declare @stn char(8),@gr int,@cn char(16) Declare CR_Rank Cursor For (select stname,grade,cname From Student,Score,Course Where Student.stno=Score.stno and Score.cno=Course.cno group by stname,grade,cname) Open CR_Rank Fetch Next From CR_Rank Into @stn,@gr,@cn print ‘姓名 分数 课程名‘ print ‘----------------------------------------------‘ While @@FETCH_STATUS=0--代表执行成功 Begin if(@gr<60) print @stn+cast(@gr as char(8))+@cn+‘不及格‘ else if(@gr<70) print @stn+cast(@gr as char(8))+@cn+‘D‘ else if(@gr<80) print @stn+cast(@gr as char(8))+@cn+‘C‘ else if(@gr<90) print @stn+cast(@gr as char(8))+@cn+‘B‘ else print @stn+cast(@gr as char(8))+@cn+‘A‘--非字符型要转换成字符型 Fetch Next From CR_Rank Into @stn,@gr,@cn End Close CR_RAnk Deallocate CR_Rank; --上机实验题---3--- Use stsc GO Create Function allcourse_Avg(@_tname char(8)) Returns @tn Table ( _tname char(8), _cname char(16), average float ) As Begin Insert @tn Select tname,cname,AVG(grade) From Teacher,Course,Score Where Teacher.tno=Course.tno and Course.cno=Score.cno and tname=@_tname Group By tname,cname,grade Return End GO Select * From allcourse_Avg(‘刘林卓‘);

 

sql 学习笔记 2

标签：