SQL Learning Note 2

--------------the 6th Chapter---------------------the experimental question---1---Use stscgo Create View st_co_srasselect student.stno,stname,stsex,score.cno,cname,grade fromStudent,score,coursewhere Student.stno=score.stno and score.cno=Course.cno; Goselect*From St_co_sr;-----on the experimental problem of the machine2---Use stscgocreate View st_computeras Select stname,cname,grade fromStudent,course,score Where Student.stno=score.stno and Score.cno=course.cno and specialist='Computer'; Goselect* fromSt_computer;-----on the experimental problem of the machine3---gocreate View st_avasselect stname,avg (grade) Average score fromStudent,scorewhere Student.stno=Score.stnogroup by Stname; Goselect* fromSt_av;--------------the 7th Chapter---------------------the experimental question---1---Use stscgo--Delete the index first if it exists (unsuccessful)--if Exists (Selectobject_name (object_id) Tablename,name,type_desc fromSys.indexeswhereName='TNO')--Drop Index Teacher.tno--elsecreate Unique Clustered Index itno on Teacher (TNO);--Unique clustered index-----on the experimental problem of the machine2---Go Create nonclustered Index icredit on Teacher (credits);--Nonclustered Indexes Goalter index Icrediton teacherrebuildwith (pad_index=on,fillfactor= -) Go--------------the 8th Chapter---------------------the experimental question---1---Use STSC--Alter table score drop constraint ck_gradealter table scoreadd constraint ck_grade check (grade between0and -);-----on the experimental problem of the machine2---Use stscalter Table studentadd Constraint df_stsex Default ('male') forStsex;-----on the experimental problem of the machine3-----Delete ALTER TABLE Student drop constraint pk_stno;--add ALTER TABLE Student add constraint Pk_stno Primary Key (stno);-----on the experimental problem of the machine4---Alter table Scoreadd constraint Fk_stno foreign key (Stno) references Student (STNO);-----on the experimental problem of the machine5---(didn't do it)--Declare @credit;--Create Rule credit_rule-- as[Email protected] between1and4;-----on the experimental problem of the machine6---Alter Table teacheradd constraint school_dft Default ('School of Communication') forSchool;--------------the 9th Chapter---------------------the experimental question---1---If Exists (Select* fromsysobjectswhereName='score'and type='Table') Drop Table score;-----on the experimental problem of the machine2---using cursors to resolve use Stscdeclare @stnChar(8), @grint, @cnChar( -) Declare Cr_rank cursorfor (Selectstname,grade,cname from Student,score,course Where student.stno=score.stno and score.cno=course.cno GROUP by Stname,grade,cname) Open Cr_rankfetch Next from Cr_rank into @stn, @gr, @cnprint'Name score Course name'Print'----------------------------------------------'While @ @FETCH_STATUS=0--represents the execution success beginif(@gr < -) Print @stn +cast (@gr as Char(8)) [Email protected]+'inferior lattice'Else if(@gr < -) Print @stn +cast (@gr as Char(8)) [Email protected]+'D'Else if(@gr < the) Print @stn +cast (@gr as Char(8)) [Email protected]+'C'Else if(@gr < -) Print @stn +cast (@gr as Char(8)) [Email protected]+'B'ElsePrint @stn +cast (@gr as Char(8)) [Email protected]+'A'--non-character type to convert to character fetch Next from Cr_rank to @stn, @gr, @cnEndClose cr_rankdeallocate Cr_rank;-----on the experimental problem of the machine3---Use stscgocreate Function allcourse_avg (@_tnameChar(8)) Returns @tn Table (_tnameChar(8), _cnameChar( -), averagefloat) Asbegininsert @tnSelect Tname,cname,avg (grade) from Teacher,course,scorewhere Teacher.tno=course.tno and Course.cno=score.cno and Tname=@_tnamegroup by Tname,cname,gradereturnendgoselect* FROM Allcourse_avg ('Liu Linzhou');

SQL Learning Note 2