大三 sql 课后习题答案

第第页大三sql课后习题答案大三sql课后习题答案

第二章

3.上机练习题

02程序代码如下：

CREATEDATABASESTUDENT1

ONPRIMARY

(NAME=STUDENT1_data,

FILENAME='E:\DATA\STUDENT1.mdf',

SIZE=3,

MA*SIZE=unlimited,

FILEGROWTH=15%)

LOGON

(NAME=STUDENT1_log,

FILENAME='E:\DATA\STUDENT1.ldf',

SIZE=2,

MA*SIZE=30,

FILEGROWTH=2)

03程序代码如下：

createdatabasestudents

onprimary

(name=students1,

filename='E:\DATA\students1.mdf',

size=5,

ma*size=75,

filegrowth=10%),

(name=students12,

filename='E:\DATA\students2.ndf',

size=10,

ma*size=75,

filegrowth=1)

logon

(name=studentslog1,

filename='E:\DATA\studentslog1.ldf',

size=5,

ma*size=30,

filegrowth=1),

(name=studentslog2,

filename='E:\DATA\studentslog2.ldf',

size=5,

大三sql课后习题答案

ma*size=30,

filegrowth=1)

第三章：

3上机练习题

01程序代码如下：

--创建表book的Transact-SQL语句：

USEtest01

GO

CREATETABLEbook(

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.authorWITHCHECK

ADDCONSTRAINTFK_book_authorFOREIGNKEY(book_id)

REFERENCESdbo.book(book_id)

02程序代码如下：

--利用Transact-SQL语句创建表booksales的代码。

USEtest01

GO

CREATETABLEbooksales(

book_idnchar(6)NOTNULL,sellnumintNOTNULL,selldatedatetimeNOTNULL

)ONPRIMARY

--利用insert语句为表booksales添加数据：

大三sql课后习题答案

INSERTINTObooksalesVALUES('m00011',7,20/12/2022)

INSERTINTObooksales(book_id,sellnum,selldate)VALUES

('m00017',3,17/11/2022)

--利用update语句为表booksales更新数据：

UPDATEbooksalesSETsellnum=11

WHEREbook_id='m00011'

--利用delete语句删除表booksales的数据：

DELETEFROMbooksales

WHEREbook_id='m00011'

03程序代码如下：

USEtest01

GO

CREATERULEsellnum_rule

AS@sellnum=0

E*ECsp_bindrule'sellnum_rule','booksales.sellnum'

04程序代码如下：

--删除年以前的数据

DELETEFROMbooksales

WHEREselldate'1/1/2022'

--删除全部数据

TruncateTablebooksales

第四章

3上机练习题

01程序代码如下：

DECLARE@booknamenchar(16)

set@bookname='SQLServer数据库编程'

02程序代码如下：

USEtest01

大三sql课后习题答案

GO

SETNOCOUNTON

DECLARE@startdatedatetime,@enddatedatetime

SET@startdate='1/7/202212:12AM'

SET@enddate='11/10/202212:00AM'

SELECTDATEDIFF(year,@startdate,@enddate)

SELECTDATEDIFF(month,@startdate,@enddate)

SELECTDATEDIFF(day,@startdate,@enddate)

SELECTDATEDIFF(minute,@startdate,'1/8/202212:17AM')

SELECTDATEDIFF(minute,@startdate,GETDATE())

SETNOCOUNTOFF

GO

03程序代码如下：

DECLARE@countINT,@SUMINT

SET@count=51

SET@SUM=0

WHILE@count=100

BEGIN

IF(CEILING(@count/3.0)=FLOOR(@count/3.0))

BEGIN

SET@SUM=@SUM+@count

END

SET@count=@count+2

END

PRINT'50到之间的全部能被整除的奇数之和'+CAST(@SUMASNCHAR(4))

第五章

3上机练习题

01程序代码如下：

--查询course表中的全部记录。

useteaching

go

select*fromcourse

go

大三sql课后习题答案

02程序代码如下：

--查询student表中的女生的人数。

useteaching

go

select*fromstudent

wherese*='女'

go

03程序代码如下：

--查询teacher表中每一位教授的老师号、姓名和专业名称。

useteaching

go

selectteacherno,tname,majorfromteacher

whereprof='教授'

go

04程序代码如下：

--按性别分组，求出student表中的每组同学的平均年龄。

useteaching

go

selectse*as'同学性别',avg(DATEDIFF(year,birthday,getdate()))as'平均年龄'

fromstudent

groupbyse*

go

05程序代码如下：

--利用现有的表生成新表，新表中包括学号、同学姓名、课程号和总评成果。

--其中：总评成果=final*0.8+usually*0.2

useteaching

go

select

student.studentno,student.sname,score.courseno,final*0.8+usually*0.2as'总评'

intostu_sorse

fromstudent,score

大三sql课后习题答案

wherestudent.studentno=score.studentno

go

select*fromstu_sorse

go

06程序代码如下：

--统计每个同学的期末成果平均分。

useteaching

go

selectstudentno,sname,avg(总评)

fromstu_sorse

groupbystudentno,sname

go

07程序代码如下：

--输出student表中年龄最大的男生的全部信息。

useteaching

go

select*fromstudent

wherebirthday=

(selectmin(birthday)

fromstudent)

go

08程序代码如下：

--查询teacher表中没有职称的职工的老师号、姓名、专业和部门。

useteaching

go

selectteacherno,tname,major,department

fromteacher

whereprofisNULL

go

第六章

3上机练习题

大三sql课后习题答案

01程序代码如下：

--查询每一位教授的老师号、姓名和讲授的课程名称。

useteaching

go

selectteacher.teacherno,tname,major,ame

fromteacher,course,teach_class

whereprof='教授'andteacher.teacherno=teach_class.teacherno

andteach_class.courseno=course.courseno

go

02程序代码如下：

--利用现有的表生成新表，新表中包括学号、同学姓名、课程名称和总评成果。

--其中：总评成果=final*0.9+usually*0.1

useteaching

go

selectDISTINCT

student.studentno,student.sname,ame,score.final*0.9+ually*0.1as'总评'

intostu_course

fromstudent,course,teach_class,score

wherestudent.studentno=score.studentno

andcourse.courseno=score.courseno

go

03程序代码如下：

--统计每个同学的期末成果高于分的课程门数。

useteaching

go

selectstudent.studentno,student.sname,count(*)as'课程门数'

fromstudent,score

wherescore.final75

andstudent.studentno=score.studentno

groupbystudent.studentno,student.sname

go

04程序代码如下：

大三sql课后习题答案

--输出student表中年龄大于女生平均年龄的男生的全部信息。

useteaching

go

select*fromstudent

wherese*='男'

andDATEDIFF(year,birthday,getdate())(

selectavg(DATEDIFF(year,birthday,getdate()))

fromstudent

wherese*='女')

go

05程序代码如下：

--计算每个同学获得的学分。

useteaching

go

selectstudent.studentno,student.sname,sum(credit)

fromstudentINNERJOINscore

ONstudent.studentno=score.studentno

INNERJOINcourse

ONcourse.courseno=score.courseno

wherescore.final60

groupbystudent.studentno,student.sname

go

06程序代码如下：

--猎取入学时间在年到年的全部同学中入学年龄小于岁的学号、姓名及所修课程的课程名称。

useteaching

go

selectstudent.studentno,student.sname,stu_ame

fromstudentinnerjoinstu_course

onstudent.studentno=stu_course.studentno

where(substring(student.studentno,1,2)='08'and

(datediff(year,birthday,'2022-01-01')19))

or(substring(student.studentno,1,2)='09'and

(datediff(year,birthday,'2022-01-01')19))

go

大三sql课后习题答案

07程序代码如下：

--查询级同学的学号、姓名、课程名及学分。

useteaching

go

selectstudent.studentno,student.sname,stu_ame

fromstudentinnerjoinstu_course

onstudent.studentno=stu_course.studentno

wheresubstring(student.studentno,1,2)='09'

go

08程序代码如下：

--查询选修课程的少于门、或期末成果含有分以下课程的同学的学号、姓名、电话和Email。

useteaching

go

selectstudentno,count(*)as'countNUM'

intocount1

fromscore

groupbystudentno

GO

selectstudent.studentno,sname,phone,Email

fromstudentinnerjoinscore

onstudent.studentno=score.studentno

innerjoincount1

onstudent.studentno=count1.studentno

wherescore.final60andcountNUM3

go

第七章

3上机题练习

01程序代码如下：

--在course表的cname列上创建非聚集索引ID*_cname。

USEteaching

GO

CREATENONCLUSTEREDINDE*ID*_cnameONcourse(cname)

大三sql课后习题答案

02程序代码如下：

USEteaching

GO

IFE*ISTS(SELECTnameFROMsysinde*esWHEREname='UQ_stu')

DROPINDE*student.UQ_stu

GO

CREATENONCLUSTEREDINDE*UQ_stuONstudent(studentno,classno)

GO

SELECT*FROMstudent

03程序代码如下：

USEteaching

GO

ALTERINDE*UQ_stuONstudentREBUILD

WITH(PAD_INDE*=ON,FILLFACTOR=80)

GO

04程序代码如下：

--创建一个视图v_teacher，查询全部“计算机学院”老师的信息。

USEteaching

GO

CREATEVIEWv_teacher

AS

SELECT*

FROMteacher

WHEREdepartment='计算机学院'

GO

SELECT*FROMv_teacher

05程序代码如下：

--创建一个视图v_avgstu，查询每个同学的学号、姓名及平均分，并且根据平均分降序排序。USEteaching

GO

CREATEVIEWv_avgstu

大三sql课后习题答案

SELECTTOP(100)PERCENTstudent.studentno,student.sname,

AVG(score.final)AS'average'

FROMstudent,score

WHEREstudent.studentno=score.studentnoANDscore.finalISNOTNULLGROUPBYstudent.studentno,student.sname

ORDERBYAVG(score.final)DESC

GO

SELECT*FROMv_avgstu

06程序代码如下：

--修改v_teacher的视图定义，添加WITHCHECKOPTION选项。

USEteaching

GO

ALTERVIEWv_teacher

AS

SELECT*

FROMteacher

WHEREdepartment='计算机学院'

WITHCHECKOPTION

GO

07程序代码如下：

--通过视图v_teacher向基本表teacher中分别插入数据('05039','张馨月','计算机应用','讲师','计算机学院')和('06018','李诚','机械制造','副教授','机械学院')，并查看插入数据状况。

USEteaching

GO

INSERTINTOv_teacherVALUES('05039','张馨月','计算机应用','讲师','计算机学院')

INSERTINTOv_teacherVALUES('06018','李诚','机械制造','副教授','机械学院')

GO

SELECT*FROMv_teacher

SELECT*FROMteacher

08程序代码如下：

--通过视图v_teacher将基本表teacher中老师编号为'05039'的老师职称修改为'副教授

大三sql课后习题答案

'。

USEteaching

GO

UPDATEv_teacher

SETprof='副教授'

WHEREteacherno='05039'

GO

SELECT*FROMteacher

第八章

3上机练习题

01程序代码如下：

--创建一个名称为StuInfo的存储过程，要求完成以下功能：

--在student表中查询级同学的学号、姓名、性别、诞生日期和电话个字段的内容

USEteaching

--查询是否已存在此存储过程，假如存在，就删除它

IFE*ISTS(SELECTnameFROMsysobjects

WHEREname='StuInfo'ANDtype='P')

DROPPROCEDUREStuInfo

GO

--创建存储过程

CREATEPROCEDUREStuInfo

AS

Selectstudentno,sname,se*,birthday,phone

FROMstudent

WHEREsubstring(studentno,1,2)='08'

GO

02程序代码如下：

--创建一个存储过程ScoreInfo，

--完成的功能是在表student、表course和表score中查询以下字段：学号、姓名、性别、课程名称、期末分数。

USEteaching

--查询是否已存在此存储过程，假如存在，就删除它

IFE*ISTS(SELECTnameFROMsysobjects

WHEREname='ScoreInfo'ANDtype='P')

大三sql课后习题答案

DROPPROCEDUREScoreInfo

GO

--创建存储过程

CREATEPROCEDUREScoreInfo

AS

Select

student.studentno,student.sname,student.se*,ame,Score.finalFROMstudent,course,score

WHEREstudent.studentno=score.studentno

andscore.courseno=course.courseno

GO

03程序代码如下：

--创建一个带有参数的存储过程Stu_Age，

--该存储过程依据输入的学号，在student表中计算此同学的年龄，

--并依据程序的执行结果返回不同的值，程序执行胜利，返回整数，假如执行出错，那么返回错误号。

--删除已存在的存储过程

USEteaching

IFE*ISTS(SELECTnameFROMsysobjects

WHEREname='Stu_Age'ANDtype='P')

DROPPROCEDUREStu_Age

GO

--创建存储过程

USEteaching

GO

CREATEPROCEDUREStu_Age

@studentNOnvarchar(10),@AgeintOUTPUT

AS

--定义并初始化局部变量，用于保存返回值

DECLARE@ErrorValueint

SET@ErrorValue=0

--求此同学的年龄

SELECT@Age=YEAR(GETDATE())-YEAR(birthday)

FROMstudent

WHEREstudentno=@studentNO

--依据程序的执行结果返回不同的值

IF(@@ERROR0)

SET@ErrorValue=@@ERROR

RETURN@ErrorValue

GO

大三sql课后习题答案

04程序代码如下：

--================================================

--TemplategeneratedfromTemplateE*plorerusing:

--CreateTrigger(NewMenu).SQL

--

--UsetheSpecifyValuesforTemplateParameters

--command(Ctrl-Shift-M)tofillintheparameter

--valuesbelow.

--

--SeeadditionalCreateTriggertemplatesformore

--e*amplesofdifferentTriggerstatements.

--

--Thisblockofcommentswillnotbeincludedin

--thedefinitionofthefunction.

--================================================

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

--=============================================

--Author:Name

--Createdate:

--Description:

--=============================================

CREATETRIGGERdbo.TR_Stu_Insert

ONdbo.student

AFTERINSERT

AS

BEGIN

DECLARE@msgnchar(30)

SET@msg='你插入了一条新记录！'

PRINT@msg

END

GO

05程序代码如下：

--创建一个AFTER触发器，要求实现以下功能：

--在score表上创建一个插入、更新类型的触发器TR_ScoreCheck，

--当在score字段中插入或修改考试分数后，触发该触发器，检查分数是否在-100之间。

USEteaching

大三sql课后习题答案

GO

CREATETRIGGERTR_ScoreCheck

ONscore

FORINSERT,UPDATE

AS

IFUPDATE(final)

PRINT'AFTER触发器开始执行……'

BEGIN

DECLARE@ScoreValuereal

SELECT@ScoreValue=(SELECTfinalFROMinserted)

IF@ScoreValue100OR@ScoreValue0

PRINT'输入的分数有误，请确认输入的考试分数！'

END

GO

06程序代码如下：

--创建一个INSTEADOF触发器，要求实现以下功能：

--在course表上创建一个删除类型的触发器TR_NotAllowDelete，

--当在course表中删除记录时，触发该触发器，显示不允许删除表中数据的提示信息。

USEteaching

GO

IFE*ISTS(SELECTnameFROMsysobjects

WHEREname='TR_NotAllowDelete'ANDtype='TR')

DROPTRIGGERTR_NotAllowDelete

GO

CREATETRIGGERTR_NotAllowDelete

ONcourse

INSTEADOFDELETE

AS

PRINT'INSTEADOF触发器开始执行……'

PRINT'本表中的数据不允许被删除！不能执行删除操作!'

GO

第九章

3上机练习题

01程序代码如下：

--创建在score表上执行UPDATE语句的事务UP_score，并执行。

大三sql课后习题答案

USEteaching

GO

BEGINTRAN

UPDATEdbo.score

SETusually=77,final=88

WHEREstudentno='0824113307'ANDcourseno='c05109'

COMMITTRAN

GO

02程序代码如下：

--练习运用ROLLBACKTRANSACTION语句回滚事务，并查看。

USEteaching

GO

BEGINTRAN

INSERTINTOcourse

VALUES('c05133','国际贸易','必修',32,2.0);

SAVETRANsave1;

DELETEFROMcourse

WHEREcourseno='c05137';

ROLLBACKTRANsave1;

COMMITTRAN

GO

03程序代码如下：

--练习在student表上创建嵌套事务，分别在内层和外层设置回滚点，检测回滚对表数据的影响。

USEteaching

GO

BEGINTRANtran1

SAVETRANsave1;

BEGINTRANtran2

UPDATEstudent

SETsname='赵平茵',point=997,phone='053179372727'

WHEREstudentno='0828261367';

SELECT*FROMstudentWHEREstudentno='0828261367';

SAVETRANsave2;

COMMITTRANtran2;

UPDATEstudent

SETsname='孙释远',point=987,phone='053179372727'

大三sql课后习题答案

WHEREstudentno='0828261367';

ROLLBACKTRANsave1;

COMMITTRANtran2;

SELECT*FROMstudentWHEREstudentno='0828261367';

GO

04程序代码如下：

--练习在student表上创建嵌套事务，并利用系统变量@@TRANCOUNT编程，

--检测嵌套事务的执行状况。

USEteaching

GO

BEGINTRANTR1

PRINT'1stBEGINTRAN:@@TRANCOUNT='+CAST(@@TRANCOUNTAS

NVARCHAR(10));

SAVETRANsave1;

BEGINTRANTR2

PRINT'2ndBEGINTRAN:@@TRANCOUNT='

+CAST(@@TRANCOUNTASNVARCHAR(10));

UPDATEstudent

SETsname='赵平茵',point=997,phone='053179372727'

WHEREstudentno='0828261367';

COMMITTRANTR2;

PRINT'1stCOMMITTRAN:@@TRANCOUNT='

+CAST(@@TRANCOUNTASNVARCHAR(10));

--ROLLBACKTRANsave1;

PRINT'ROLLBACKTRAN:@@TRANCOUNT='

+CAST(@@TRANCOUNTASNVARCHAR(10));

COMMITTRANTR1;

PRINT'AFTERCOMMITTRANTR1:@@TRANCOUNT='

+CAST(@@TRANCOUNTASNVARCHAR(10));

SELECT*FROMstudentWHEREstudentno='0828261367';

GO

05程序代码如下：

--练习在student表上进行查询、插入和更新，然后运用sys.dm_tran_locks视图查看锁的信息。

USEteaching;

GO

大三sql课后习题答案

BEGINTRAN

SELECTstudentno,sname

FROMstudent

--WITH(holdlock,rowlock)

WHEREstudentno='0828261367';

INSERTINTOstudent

VALUES('1028261001','孙释远','女

','1989-09-09','090512',777,'1328909876','sdqd@');

UPDATEstudent

SETsname='孙释嘉'

WHEREstudentno='0828261367';

--为了查看事务中运用的锁的信息，运用动态管理视图sys.dm_tran_locks。在查询窗口中键入并执行以下SELECT语句来猎取锁信息并提交事务。

SELECTresource_type,resource_associated_entity_id,

request_status,request_mode,request_session_id,

resource_description

FROMsys.dm_tran_locks

WHEREresource_database_id=DB_ID('teaching');

--提交事务

COMMITTRAN

第十章

3上机练习题

01程序代码如下：

USEmaster

GO

CREATELOGINUSER1WITHPASSWORD='Abc!@#213'

03程序代码如下：

--练习在teaching数据库中为SQLServer登录名USER1添加数据库用户，并取名为USER2，默认架构为TEAC。

USEteaching

GO

CREATEUSERUSER2FORLOGINUSER1

WITHDEFAULT_SCHEMA=TEAC

GO

大三sql课后习题答案

05程序代码如下：

--练习将teaching数据库中创建表的权限授予用户USER2。

USEteaching

GO

GRANTCREATETABLETOUSER2

GO

第十一章

3上机练习题

01程序代码如下：

--完整数据库备份

USEteaching

GO

BACKUPDATABASEteaching

TODISK=N'F:\sqlprogram_e*\第章\backteaching'

WITHE*PIREDATE=N'12/22/202200:00:00',

NAME=N'teaching-完整数据库备份',

STATS=10

GO

--差异备份

BACKUPDATABASEteaching

TODISK=N'F:\sqlprogram_e*\第章\backteaching'

WITHDIFFERENTIAL,

E*PIREDATE=N'12/22/202200:00:00',

NOFORMAT,NOINIT,

NAME=N'teaching-差异数据库备份',

SKIP,NOREWIND,NOUNLOAD,STATS=10

GO

declare@backupSetIdasint

select@backupSetId=position

frommsdb..backupset

wheredatabase_name=N'teaching'andbackup_set_id=(select

ma*(backup_set_id)frommsdb..backupsetwhere

database_name=N'teaching')

if@backupSetIdisnull

begin

raiserror(N'验证失败。找不到数据库“teaching”的备份信息。',16,1)end

大三sql课后习题答案

RESTOREVERIFYONLY

FROMDISK=N'F:\sqlprogram_e*\第章\backteaching'

WITHFILE=@backupSetId,NOUNLOAD,NOREWIND

GO

02程序代码如下：

--对数据库teaching进行复原

RESTOREDATABASEteaching

FROMDISK=N'F:\sqlprogram_e*\第章\backteaching'

WITHFILE=2,NORECOVERY,NOUNLOAD,

STATS=10

GO

RESTOREDATABASEteaching

FROMDISK=N'F:\sqlprogram_e*\第章\backteaching'

WITHFILE=3,NOUNLOAD,

STATS=10

GO

03程序代码如下：

--创建备份设备

USEmaster

GO

E*ECmaster.dbo.sp_addumpdevice

@devtype=N'disk',

@logicalname=N'device1',

@physicalname=N'F:\sqlprogram_e*\第章\back_device1.bak'

GO

--备份teaching数据库的事务日志

BACKUPLOGteaching

TOdevice1

WITHRETAINDAYS=3,NOFORMAT,NOINIT,

NAME=N'teaching-事务日志备份',

SKIP,N