大三-sql-课后习题答案

第二章3.上机练习题02程序代码如下：CREATEDATABASESTUDENT1ONPRIMARY(NAME=STUDENT1_data,FILENAME='E:\DATA\STUDENT1.mdf',SIZE=3,MAXSIZE=unlimited,FILEGROWTH=15%)LOGON(NAME=STUDENT1_log,FILENAME='E:\DATA\STUDENT1.ldf',SIZE=2,MAXSIZE=30,FILEGROWTH=2)03程序代码如下：createdatabasestudentsonprimary(name=students1,filename='E:\DATA\students1.mdf',size=5,maxsize=75,filegrowth=10%),(name=students12,filename='E:\DATA\students2.ndf',size=10,maxsize=75,filegrowth=1)logon(name=studentslog1,filename='E:\DATA\studentslog1.ldf',size=5,maxsize=30,filegrowth=1),(name=studentslog2,filename='E:\DATA\studentslog2.ldf',size=5,maxsize=30,filegrowth=1)第三章：3上机练习题01程序代码如下：--创建表book的Transact-SQL语句：USEtest01GOCREATETABLEbook(book_idnchar(6)NOTNULL,book_namenchar(30)NULL,pricenumeric(10,2)NULL,CONSTRAINTPK_bookPRIMARYKEYCLUSTERED(book_idASC))ONPRIMARY--创建表uthor的Transact-SQL语句：CREATETABLEdbo.author(anthor_namenchar(4)NOTNULL,book_idnchar(6)NOTNULL,addressnchar(30)NOTNULL)ON[PRIMARY]--设置book中的book_id为主键，author表中的book_id为外键ALTERTABLEdbo.authorWITHCHECKADDCONSTRAINTFK_book_authorFOREIGNKEY(book_id)REFERENCESdbo.book(book_id)02程序代码如下：--利用Transact-SQL语句创建表booksales的代码。USEtest01GOCREATETABLEbooksales(book_idnchar(6)NOTNULL,sellnumintNOTNULL,selldatedatetimeNOTNULL)ONPRIMARY--利用insert语句为表booksales添加数据：INSERTINTObooksalesVALUES('m00011',7,20/12/2008)INSERTINTObooksales(book_id,sellnum,selldate)VALUES('m00017',3,17/11/2008)--利用update语句为表booksales更新数据：UPDATEbooksalesSETsellnum=11WHEREbook_id='m00011'--利用delete语句删除表booksales的数据：DELETEFROMbooksalesWHEREbook_id='m00011'03程序代码如下：USEtest01GOCREATERULEsellnum_ruleAS@sellnum=0EXECsp_bindrule'sellnum_rule','booksales.sellnum'04程序代码如下：--删除年以前的数据DELETEFROMbooksalesWHEREselldate'1/1/2009'--删除所有数据TruncateTablebooksales第四章3上机练习题01程序代码如下：DECLARE@booknamenchar(16)set@bookname='SQLServer数据库编程'02程序代码如下：USEtest01GOSETNOCOUNTONDECLARE@startdatedatetime,@enddatedatetimeSET@startdate='1/7/200812:12AM'SET@enddate='11/10/200912:00AM'SELECTDATEDIFF(year,@startdate,@enddate)SELECTDATEDIFF(month,@startdate,@enddate)SELECTDATEDIFF(day,@startdate,@enddate)SELECTDATEDIFF(minute,@startdate,'1/8/200712:17AM')SELECTDATEDIFF(minute,@startdate,GETDATE())SETNOCOUNTOFFGO03程序代码如下：DECLARE@countINT,@SUMINTSET@count=51SET@SUM=0WHILE@count=100BEGINIF(CEILING(@count/3.0)=FLOOR(@count/3.0))BEGINSET@SUM=@SUM+@countENDSET@count=@count+2ENDPRINT'50到之间的所有能被整除的奇数之和'+CAST(@SUMASNCHAR(4))第五章3上机练习题01程序代码如下：--查询course表中的所有记录。useteachinggoselect*fromcoursego02程序代码如下：--查询student表中的女生的人数。useteachinggoselect*fromstudentwheresex='女'go03程序代码如下：--查询teacher表中每一位教授的教师号、姓名和专业名称。useteachinggoselectteacherno,tname,majorfromteacherwhereprof='教授'go04程序代码如下：--按性别分组，求出student表中的每组学生的平均年龄。useteachinggoselectsexas'学生性别',avg(DATEDIFF(year,birthday,getdate()))as'平均年龄'fromstudentgroupbysexgo05程序代码如下：--利用现有的表生成新表，新表中包括学号、学生姓名、课程号和总评成绩。--其中：总评成绩=final*0.8+usually*0.2useteachinggoselectstudent.studentno,student.sname,score.courseno,final*0.8+usually*0.2as'总评'intostu_sorsefromstudent,scorewherestudent.studentno=score.studentnogoselect*fromstu_sorsego06程序代码如下：--统计每个学生的期末成绩平均分。useteachinggoselectstudentno,sname,avg(总评)fromstu_sorsegroupbystudentno,snamego07程序代码如下：--输出student表中年龄最大的男生的所有信息。useteachinggoselect*fromstudentwherebirthday=(selectmin(birthday)fromstudent)go08程序代码如下：--查询teacher表中没有职称的职工的教师号、姓名、专业和部门。useteachinggoselectteacherno,tname,major,departmentfromteacherwhereprofisNULLgo第六章3上机练习题01程序代码如下：--查询每一位教授的教师号、姓名和讲授的课程名称。useteachinggoselectteacher.teacherno,tname,major,course.cnamefromteacher,course,teach_classwhereprof='教授'andteacher.teacherno=teach_class.teachernoandteach_class.courseno=course.coursenogo02程序代码如下：--利用现有的表生成新表，新表中包括学号、学生姓名、课程名称和总评成绩。--其中：总评成绩=final*0.9+usually*0.1useteachinggoselectDISTINCTstudent.studentno,student.sname,course.cname,score.final*0.9+score.usually*0.1as'总评'intostu_coursefromstudent,course,teach_class,scorewherestudent.studentno=score.studentnoandcourse.courseno=score.coursenogo03程序代码如下：--统计每个学生的期末成绩高于分的课程门数。useteachinggoselectstudent.studentno,student.sname,count(*)as'课程门数'fromstudent,scorewherescore.final75andstudent.studentno=score.studentnogroupbystudent.studentno,student.snamego04程序代码如下：--输出student表中年龄大于女生平均年龄的男生的所有信息。useteachinggoselect*fromstudentwheresex='男'andDATEDIFF(year,birthday,getdate())(selectavg(DATEDIFF(year,birthday,getdate()))fromstudentwheresex='女')go05程序代码如下：--计算每个学生获得的学分。useteachinggoselectstudent.studentno,student.sname,sum(credit)fromstudentINNERJOINscoreONstudent.studentno=score.studentnoINNERJOINcourseONcourse.courseno=score.coursenowherescore.final60groupbystudent.studentno,student.snamego06程序代码如下：--获取入学时间在年到年的所有学生中入学年龄小于岁的学号、姓名及所修课程的课程名称。useteachinggoselectstudent.studentno,student.sname,stu_course.cnamefromstudentinnerjoinstu_courseonstudent.studentno=stu_course.studentnowhere(substring(student.studentno,1,2)='08'and(datediff(year,birthday,'2008-01-01')19))or(substring(student.studentno,1,2)='09'and(datediff(year,birthday,'2009-01-01')19))go07程序代码如下：--查询级学生的学号、姓名、课程名及学分。useteachinggoselectstudent.studentno,student.sname,s