大三sql课后习题答案.docx

大三sql课后习题答案.docx

《大三sql课后习题答案.docx》由会员分享，可在线阅读，更多相关《大三sql课后习题答案.docx（39页珍藏版）》请在冰豆网上搜索。

大三sql课后习题答案

第二章

3.上机练习题

02程序代码如下：

CREATEDATABASESTUDENT1

ONPRIMARY

（NAME=STUDENT1_data,

FILENAME='E:

\DATA\',

SIZE=3,

MAXSIZE=unlimited,

FILEGROWTH=15%）

LOGON

（NAME=STUDENT1_log,

FILENAME='E:

\DATA\',

SIZE=2,

MAXSIZE=30,

FILEGROWTH=2）

03程序代码如下：

createdatabasestudents

onprimary

（name=students1,

filename='E:

\DATA\',

size=5,

maxsize=75,

filegrowth=10%）,

（name=students12,

filename='E:

\DATA\',

size=10,

maxsize=75,

filegrowth=1）

logon

（name=studentslog1,

filename='E:

\DATA\',

size=5,

maxsize=30,

filegrowth=1）,

（name=studentslog2,

filename='E:

\DATA\',

size=5,

maxsize=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语句：

CREATETABLE（

anthor_namenchar（4）NOTNULL,

book_idnchar（6）NOTNULL,

addressnchar（30）NOTNULL

）ON[PRIMARY]

--设置book中的book_id为主键，author表中的book_id为外键

ALTERTABLEWITHCHECK

ADDCONSTRAINTFK_book_authorFOREIGNKEY（book_id）

REFERENCES（book_id）

02程序代码如下：

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

USEtest01

GO

CREATETABLEbooksales（

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=11

WHEREbook_id='m00011'

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

DELETEFROMbooksales

WHEREbook_id='m00011'

03程序代码如下：

USEtest01

GO

CREATERULEsellnum_rule

AS@sellnum>=0

EXECsp_bindrule'sellnum_rule',''

04程序代码如下：

--删除年以前的数据

DELETEFROMbooksales

WHEREselldate<'1/1/2009'

--删除所有数据

TruncateTablebooksales

第四章

3上机练习题

01程序代码如下：

DECLARE@booknamenchar（16）

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

02程序代码如下：

USEtest01

GO

SETNOCOUNTON

DECLARE@startdatedatetime,@enddatedatetime

SET@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（））

SETNOCOUNTOFF

GO

03程序代码如下：

DECLARE@countINT,@SUMINT

SET@count=51

SET@SUM=0

WHILE@count<=100

BEGIN

IF（CEILING（@count/<=FLOOR（@count/）

BEGIN

SET@SUM=@SUM+@count

END

SET@count=@count+2

END

PRINT'50到之间的所有能被整除的奇数之和'+CAST（@SUMASNCHAR（4））

第五章

3上机练习题

01程序代码如下：

--查询course表中的所有记录。

useteaching

go

select*fromcourse

go

02程序代码如下：

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

useteaching

go

select*fromstudent

wheresex='女'

go

03程序代码如下：

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

useteaching

go

selectteacherno,tname,majorfromteacher

whereprof='教授'

go

04程序代码如下：

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

useteaching

go

selectsexas'学生性别',avg（DATEDIFF（year,birthday,getdate（）））as'平均年龄'

fromstudent

groupbysex

go

05程序代码如下：

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

--其中：

总评成绩=final*+usually*

useteaching

go

select,,,final*+usually*as'总评'

intostu_sorse

fromstudent,score

where=

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上机练习题

01程序代码如下：

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

useteaching

go

select,tname,major,

fromteacher,course,teach_class

whereprof='教授'and=

and=

go

02程序代码如下：

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

--其中：

总评成绩=final*+usually*

useteaching

go

selectDISTINCT,,,*+*as'总评'

intostu_course

fromstudent,course,teach_class,score

where=

and=

go

03程序代码如下：

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

useteaching

go

select,,count（*）as'课程门数'

fromstudent,score

where>75

and=

groupby,

go

04程序代码如下：

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

useteaching

go

select*fromstudent

wheresex='男'

andDATEDIFF（year,birthday,getdate（））>（

selectavg（DATEDIFF（year,birthday,getdate（）））

fromstudent

wheresex='女'）

go

05程序代码如下：

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

useteaching

go

select,,sum（credit）

fromstudentINNERJOINscore

ON=

INNERJOINcourse

ON=

where>60

groupby,

go

06程序代码如下：

--获取入学时间在年到年的所有学生中入学年龄小于岁的学号、姓名及所修课程的课程名称。

useteaching

go

select,,

fromstudentinnerjoinstu_course

on=

where（substring,1,2）='08'and（datediff（year,birthday,'2008-01-01'）<19））

or（substring,1,2）='09'and（datediff（year,birthday,'2009-01-01'）<19））

go

07程序代码如下：

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

useteaching

go

select,,

fromstudentinnerjoinstu_course

on=

wheresubstring,1,2）='09'

go

08程序代码如下：

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

useteaching

go

selectstudentno,count（*）as'countNUM'

intocount1

fromscore

groupbystudentno

GO

select,sname,phone,Email

fromstudentinnerjoinscore

on=

innerjoincount1

on=

where<60andcountNUM<3

go

第七章

3上机题练习

01程序代码如下：

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

USEteaching

GO

CREATENONCLUSTEREDINDEXIDX_cnameONcourse（cname）

GO

02程序代码如下：

USEteaching

GO

IFEXISTS（SELECTnameFROMsysindexesWHEREname='UQ_stu'）

DROPINDEX

GO

CREATENONCLUSTEREDINDEXUQ_stuONstudent（studentno,classno）

GO

SELECT*FROMstudent

03程序代码如下：

USEteaching

GO

ALTERINDEXUQ_stuONstudentREBUILD

WITH（PAD_INDEX=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

AS

SELECTTOP（100）PERCENT,,AVGAS'average'

FROMstudent,score

WHERE=ANDISNOTNULL

GROUPBY,

ORDERBYAVGDESC

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'的教师职称修改为'副教授'。

USEteaching

GO

UPDATEv_teacher

SETprof='副教授'

WHEREteacherno='05039'

GO

SELECT*FROMteacher

第八章

3上机练习题

01程序代码如下：

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

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

USEteaching

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

IFEXISTS（SELECTnameFROMsysobjects

WHEREname='StuInfo'ANDtype='P'）

DROPPROCEDUREStuInfo

GO

--创建存储过程

CREATEPROCEDUREStuInfo

AS

Selectstudentno,sname,sex,birthday,phone

FROMstudent

WHEREsubstring（studentno,1,2）='08'

GO

02程序代码如下：

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

--完成的功能是在表student、表course和表score中查询以下字段：

学号、姓名、性别、课程名称、期末分数。

USEteaching

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

IFEXISTS（SELECTnameFROMsysobjects

WHEREname='ScoreInfo'ANDtype='P'）

DROPPROCEDUREScoreInfo

GO

--创建存储过程

CREATEPROCEDUREScoreInfo

AS

Select,,,,

FROMstudent,course,score

WHERE=

and=

GO

03程序代码如下：

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

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

--并根据程序的执行结果返回不同的值，程序执行成功，返回整数，如果执行出错，则返回错误号。

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

USEteaching

IFEXISTS（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（@@ERROR<>0）

SET@ErrorValue=@@ERROR

RETURN@ErrorValue

GO

04程序代码如下：

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

--TemplategeneratedfromTemplateExplorerusing:

--CreateTrigger（NewMenu）.SQL

--

--UsetheSpecifyValuesforTemplateParameters

--command（Ctrl-Shift-M）tofillintheparameter

--valuesbelow.

--

--SeeadditionalCreateTriggertemplatesformore

--examplesofdifferentTriggerstatements.

--

--Thisblockofcommentswillnotbeincludedin

--thedefinitionofthefunction.

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

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

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

--Author:

Name

--Createdate:

--Description:

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

CREATETRIGGER

ON

AFTERINSERT

AS

BEGIN

DECLARE@msgnchar（30）

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

'

PRINT@msg

END

GO

05程序代码如下：

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

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

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

USEteaching

GO

CREATETRIGGERTR_ScoreCheck

ONscore

FORINSERT,UPDATE

AS

IFUPDATE（final）

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

BEGIN

DECLARE@ScoreValuereal

SELECT@ScoreValue=（SELECTfinalFROMinserted）

IF@ScoreValue>100OR@ScoreValue<0

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

'

END

GO

06程序代码如下：

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

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

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

USEteaching

GO

IFEXISTS（SELECTnameFROMsysobjects

WHEREname='TR_NotAllowDelete'ANDtype='TR'）

DROPTRIGGERTR_NotAllowDelete

GO

CREATETRIGGERTR_NotAll