数据库上机练习题及答案(2)

_____________________________________________________________________________________________________________________________________________________________________6001请创建一个教师表teacher其中包括教师号，教师名，教师性别，教师系，教师职称，教师出生日期，要求，教师号为唯一，各字段命名为tnochar(3),tnamechar(8),tsexchar(2),tdeptchar(20),tglasschar(10),tcsrqdatecreatetableteacher(tnochar(3)unique,tnamechar(8),tsexchar(2),tdeptchar(20),tglasschar(10),tcsrqdate)_____________________________________________________________________________________________________________________________________________________________________6002用定义语句创建“宿舍表”shuse1,要求宿舍号ssh文本3,宿舍类别sslb文本2,床位数cws数字整型,宿舍长ssz文本10位,宿舍号，宿舍号为主键，并且不为空createtableshuse1(sshchar(3)primarykeynotnull,sslbchar(2),cwsnumber,sszchar(10));_____________________________________________________________________________________________________________________________________________________________________6003在课程”表course中增加一列教师号tno文本3,缺省值000altertablecourseaddtnochar(3)default'000'notnull;_____________________________________________________________________________________________________________________________________________________________________6004将教师”表teacher所属系（tdept）长度改为50,约束条件为不为空，缺省值为计算机系altertableteacheraltertdeptchar(50)notnulldefault'计算机系'_____________________________________________________________________________________________________________________________________________________________________6005创建一个名字为v_stu视图，将计算机系平均成绩大于等于80的同学的姓名及平均成绩放入该视图中。（注该视图只有名字和平均成绩两个字段，平均成绩要用pjgrade)表示createviewv_stuasselectsname,avg(grade)aspjgradefromstudent,scwherestudent.sno=sc.snoandsdept='计算机系'groupbysnamehavingavg(grade)=80;_____________________________________________________________________________________________________________________________________________________________________6006以SC表创建一个索引文件idx_sc1，要求按CNO降降序，GRADE升序createindexidx_sc1onsc(cnodesc,gradeasc);_____________________________________________________________________________________________________________________________________________________________________6007请创建一个视图文件sss，要求其中包括计算机系所有成绩大于70分课程的学生姓名，课程名，成绩，,系名，任课教师姓名。createviewsssasselectstudent.sname,cname,sc.grade,sdept,tnamefromstudent,course,sc,teacherwherestudent.sno=sc.snoandsc.cno=course.cnoandcourse.tno=teacher.tnoandsdept='计算机系'andsc.grade70_____________________________________________________________________________________________________________________________________________________________________6008请将course表中的tno列删除altertablecoursedroptno;_____________________________________________________________________________________________________________________________________________________________________6009建一视图sss1，要求其中包含每个人的姓名，平均成绩，最高成绩，最低成绩等字段，并用中文（姓名，平均成绩，最高成绩，最低成绩表示出来）createviewsss1asselectsnameas姓名,avg(grade)as平均成绩,max(grade)as最高成绩,min(grade)as最低成绩fromstudent,scwherestudent.sno=sc.snogroupbysname_____________________________________________________________________________________________________________________________________________________________________6010删除索引文件idx_sc1dropindexidx_sc1onsccreatetableshuse1(sshchar(3)notnull,sslbchar(2),cwsinteger,sszchar(10),check(cws=1andcws=4),primarykey(ssh));