SQL SERVER 实验_习题课

p77.10createtablestudent(snochar(7)notnullunique,snamevarchar(20)notnull,ssexchar(2)notnull,sagesmallint,clnochar(5)notnull);createtablecourse(cnochar(1)notnullunique,cnamevarchar(20)notnull,creditsmallint);createtableclass(clnochar(5)notnullunique,specialityvarchar(20)notnull,inyearchar(4)notnull,numberinteger,monitorchar(7));createtablegrade(snochar(7)notnull,cnochar(1)notnull,gmarknumeric(4,1));11.1altertablestudentaddnationvarchar(20);2altertablestudentdropcolumnnation;3insertintogradevalues(‘2001110’,’3’,80);4updategradesetgmark=70wheresno=’2001110’;5deletefromgradewheresno=’2001110’;6createindexix_classonstudent(clno);7dropindexstudent.ix_class;12.1selectdistinctcnofromgrade;2select*fromstudentwhereclno=’01311’andssex=’女’;3selectsname,ssex,year(getdate())–sagefromstudentwhereclnoin(‘01311’,’01312’);或：selectsname,ssex,inyear-sagebirthyearfromstudent,classwherestudent.clno=class.clnoandclnoin(‘01311’,’01312’);4select*fromstudentwheresnamelike‘李%’;5<一>selectcount(sno)fromstudentwhereclno=(selectclnofromstudentwheresname=’李勇’);<二>selectcount(second.sno)fromstudentfirst,studentsecondwherefirst.clno=second.clnoandfirst.sname=’李勇’;或：<一>selectnumberfromstudent,classwheresname=’李勇’andstudent.clno=class.clno;<二>selectnumberfromclasswhereclno=(selectclnofromstudentwheresname=’李勇’); 6selectavg(gmark),max(gmark),min(gmark)fromcourse,gradewherecourse.cno=grade.cnoandcname=’操作系统’;⑦selectcount(distinctsno)fromgrade;⑧selectcount(sno)fromgrade,coursewherecourse.cno=grade.cnoandcname=’操作系统’;⑨<一>selectsnamefromstudent,grade,classwherestudent.sno=grade.snoandstudent.clno=class.clnoandgmarkisnullandspeciality=’计算机软件’andinyear=’2000’;<二>selectsnamefromstudentwhereclnoin(selectclnofromclasswherespeciality='计算机软件'andinyear='2000')andsnoin(selectsnofromgradewheregmarkisnull);13.①select*fromstudentwhereclnoin(selectclnofromstudentwheresname='李勇')andsname<>'李勇';②select*fromstudentwheresnoin(selectsnofromgrade 课程号在李勇所选课程范围内的学号wherecnoin(selectcnofromgrade    李勇的课程号wheresnoin(selectsnofromstudent   李勇的学号wheresname='李勇')))andsname<>'李勇';③select*fromstudentwheresagebetween(selectsagefromstudentwheresname='李勇') and 25;④selectsno,snamefromstudentwheresnoin(selectsnofromgradewherecnoin(selectcnofromcoursewherecname=’操作系统’));⑤selectsnamefromstudentwheresnonotin(selectsnofromgradewherecno=’1’);或：selectsnamefromstudentwherenotexists     子查询查不到则返回.t.(select*fromgradewheresno=student.snoandcno=’1’);或：selectsnamefromstudentwheresno<>all(selectsnofromgradewherecno=’1’);6selectsnamefromstudentwherenotexists(select*fromcoursewherenotexists(select*fromgradewheresno=student.snoandcno=course.cno));或：selectsnamefromstudentwheresnoin(selectsnofromgradegroupbysnohavingcount(cno)=(selectcount(cno)fromcourse));14.1selectsno,gmarkfromgradewherecno=’3’orderbygmarkdesc;2select*fromstudentorderbyclno,sagedesc;3selectcno,count(sno)fromgradegroupbycno;或：selectcno,count(cno)fromgradegroupbycno;或：selectcno,count(*)fromgradegroupbycno;4selectsnofromgradegroupbysnohavingcount(*)>=3;15①updategradesetgmark=0wheresnoin(selectsnofromstudentwhereclno=’01311’);updategradesetgmark=0     wheregrade.sno=student.sno   ？？？andstudent.clno=’01311’;②deletefromgradewheresnoin(selectsnofromstudentwhereclnoin(selectclnofromclasswherespeciality=’计算机软件’andinyear=’2001’));或：deletefromgradewheresnoin(selectsnofromstudent,classwherestudent.clno=class.clnoandspeciality=’计算机软件’andinyear=’2001’);3deletefromgradewheresnoin(selectsnofromstudentwheresname=’李勇’);updateclasssetnumber=number–1                                                                          whereclno=         李勇所在班人数减1(selectclnofromstudentwheresname=’李勇’);updateclasssetmonitor=nullwheremonitor=      李勇是班长的班，班长置为空   (selectsnofromstudentwheresname=’李勇’);deletefromstudentwheresname=’李勇’;④<方法一>createtableclassage(clnochar(5),avgagesmallint);insertintoclassageselectclno,avg(sage)fromstudentgroupbyclno;/*因student表中的sage为smallint类型，其聚合函数avg(sage)返回值仍为smallint类型，若年龄为20、21，得到的平均年龄为20（小数位无条件截断），若要精确的平均值，则将sage定义为带小数位的类型,如numeric(3,1)*/<方法二>altertableclassaddavgageint;goupdateclasssetavgage=(selectavg(sage)fromstudentwhereclass.clno=student.clnogroupbyclno);<方法三>createview v_classageasselectclno,avg(sage)avgagefromstudentgroupbyclno（注意sqlserver中创建视图命令不能加’;’)<方法四>selectclno,avg(sage)avgageintoclass_age      创建一新表class_agefromstudentgroupbyclno;16．①createviewstu_01312_1asselectstudent.*,grade.gmark   此处加一gmark便于下一 题 快递公司问题件快递公司问题件货款处理关于圆的周长面积重点题型关于解方程组的题及答案关于南海问题 使用fromstudent,gradewhere student.sno=grade.snoandclno='01312'andcno='1'②createviewstu_01312_2asselect*fromstu_01312_1wheregmark<60③createviewstu_yearasselectsno,sname,2012-sagebirthyearfromstudent④selectsnamefromstu_yearwherebirthyear>=1990;⑤selectsno,sname,birthyearfromstu_yearwheresnoin(selectsnofromstu_01312_2);或用连接写：selectstu_year.*fromstu_year,stu_01312_2wherestu_year.sno=stu_01312_2.sno;.p98：10一．student表先不建外部码clno，因clno参照class表，而class表又反过来参照student表。可最后添加外部码clno。createtablestudent(snochar(7) primarykey,snamevarchar(20)notnull,ssexchar(2)notnullcheck(ssexin('男','女'))default'男',sagesmallint check(sage>14andsage<65),clnochar(5)notnull);二．course表可象上表那样创建时加上完整性约束，也可在创建完的表上后加完整性约束：altertablecourseaddprimarykey(cno);[或：altertablecourseaddconstraintpk_courseprimarykey(cno);]altertablecourseaddconstraintchk_credcheck(creditin(1,2,3,4,5,6,));三．class表altertableclassaddprimarykey(clno);altertableclassaddconstraintck_numbercheck(number>1andnumber<100);altertableclassaddforeignkey(monitor)referencesstudent(sno);建好class表之后，再添加student表的外部码clno：altertablestudentaddforeignkey(clno)referencesclass(clno);四．grade表altertablegradeaddprimarykey(sno,cno);altertablegradeaddforeignkey(sno)referencesstudent(sno);altertablegradeaddforeignkey(cno)referencescourse(cno);altertablegradeaddconstraintck_gmarkcheck(gmark>=0andgmark<=100);或创建表时加约束：createtablegrade(snochar(7)notnull,cnochar(1)notnull,gmarkdecimal(4,1) check(gmark>0andgmark<=100),primarykey(sno,cno),foreignkey(sno)referencesstudent(sno),foreignkey(cno)referencescourse(cno));或createtablegrade(snochar(7)notnullreferencesstudent(sno),cnochar(1)notnullreferencescourse(cno),gmarkdecimal(4,1) check(gmark>0andgmark<=100),primarykey(sno,cno));课堂演示createtablestudent(snochar(7)notnullunique,snamevarchar(20)notnull,ssexchar(2)notnull,sagesmallint,clnochar(5)notnull);insertintostudentvalues('2000101','李勇','男',20,'00311');insertintostudentvalues('2000102','刘诗晨','女',22,'00311');insertintostudentvalues('2000103','王一鸣','男',20,'00312');insertintostudentvalues('2000104','张婷婷','女',22,'00312');createtablegrade(snochar(7)notnull,cnochar(1)notnull,gmarkdecimal(4,1));insertintogradevalues('2000101','1',92);insertintogradevalues('2000101','3',88);insertintogradevalues('2000102','1',78);insertintogradevalues('2000102','2',100);