課后實訓(xùn)參考答案

單元1（SQLServer概述）

1、使用SQL語句。在Sale數(shù)據(jù)庫中創(chuàng)建名為MyDataType的用戶

定義數(shù)據(jù)類型，數(shù)據(jù)類型為NVARCHAR,長度為20,該列允許為

NULLo

USESale

GO

EXECsp_addtypeMyDataType,'NVARCHAR（20）\,NULL,

GO

單元2（單表數(shù)據(jù)）

使用查詢窗口或sqlcmd實施查詢。本實訓(xùn)使用Xk數(shù)據(jù)庫。

-1.查看系部編號為“03”的系部名稱。

USEXk

GO

SELECTDepartName

FROMDepartment

WHEREDepartNo='03,

GO

-2.查看系部名稱中包含有“工程”兩個字的系的全名。

USEXk

GO

SELECTDepartName

FROMDepartment

WHEREDepartNameLIKE‘％工程小

GO

-3.顯示共有多少個系部。

USEXK

GO

SELECT，系部總數(shù)-COUNT(*)

FROMDepartment

GO

-4.顯示“01”年級共有多少個班級。

USEXK

GO

SELECT*01級班級數(shù)=COUNT(,)

FROMClass

WHEREClassNoLIKEf2001%1

GO

SELECTf01級班級數(shù)'=COUNT(*)

FROMClass

WHEREClassNameLIKE101%1

GO

SELECT*01級班級數(shù)"OUNT(*)

FROMStudent

WHEREClassNoLIKE'2001%'

GO

-5.查看在“周二晚”上課的課程名稱和教師。

USEXK

GO

SELECT1課程名稱，=CouName,,任課教師，=Teacher

FROMCourse

WHERESchoolTime='周二晚，

GO

-6.查看姓“張”、“陳”、“黃”同學(xué)的基本信息，要求按照姓名

降序排序杳詢結(jié)果。

USEXK

GO

SELECT*

FROMStudent

WHEREStuNameLIKE'張％'OR

StuNameLIKE'陳％，OR

StuNameLIKE，黃3’

ORDERBYStuNameDESC

GO

一方法

USEXK

GO

SELECT*

FROMStudent

WHEREStuNamelike'［張，陳，黃］3'

ORDERBYStuNameDESC

GO

單元2(多表查詢)

-1.按系部統(tǒng)計課程的平均報名人數(shù)，耍求顯示系部名稱、平均報

名人數(shù)。

SELECT，系部名稱=DepartNameJ平均報名人數(shù)

'=AVG(WillNum)

FROMCourseC,DepartmentD

WHEREC.DepartNo=D.DepartNo

GROUPBYDepartName

GO

一如果小數(shù)點后只保留位

SELECT，系部名稱』DepartNameJ平均報名數(shù)

-CONVERT(DEC工MAL(5,2)zAVG(WillNum))

FROMCourseC,DepartmentD

WHEREC.DepartNoD.DepartNo

GROUPBYDepartName

GO

-2.統(tǒng)計各個系部的班級數(shù)，要求顯示系部編號、系部名稱和班級

數(shù)量。

SELECTC.DepartNo,DepartName,COUNT(*)

FROMClassCzDepartmentD

WHEREC.DepartNo=D.DepartN。

GROUPBYC.DepartNo,DepartName

GO

-3.查看“甘蕾”同學(xué)選修的課程名、學(xué)分、上課時間、志愿號,

按志愿號（升序）排序查詢結(jié)果。

SELECTCouName,Credit,SchoolTime,WillOrder

FROMStuCouSC,CourseC,StudentS

WHERESC.CouNo=C.CouNoANDSC.StuNo=S.StuNoAND

StuName='甘蕾'

ORDERBYWillOrder

GO

-4.查看“00電子商務(wù)”班的選修報名情況。要求顯示學(xué)號、姓

名、課程編號、課程名稱、志愿號，并按學(xué)號（升序）、志愿號排序

（升序）。

SELECTS.StuNo,StuName,C.CouNo,CouName,WillOrder

FROMStuCouSC,CourseCzStudentSfClassCL

WHERESC.COUNO=C.COUNOAND

SC.StuNo=S.StuNoAND

CL.ClassNo=S.ClassNoAND

ClassNaneLike'00電子商務(wù)'

ORDERBYS.StuNo,WillOrder

GO

-5.按系部統(tǒng)計各系的最少報名人數(shù)、最多報名人數(shù)、平均報名

人數(shù)和報名總數(shù)，并匯總顯示所有系部的報名總數(shù)。要求平均報名人

數(shù)保留兩位小數(shù)位。

SELECT，系部名稱=Depa工tNameJ最少報名人數(shù)

*=MIN(WillNum),，最多報名人數(shù)，=MAX(WillNum)J平均報名

人數(shù)，=CONVERT(DECIMAL(5,2)zAVG(WillNum))，'報名總數(shù)

f=SUM(WillNum)

FROMCourseC,DepartmentD

WHEREC.DepartNo=D.DepartNo

GROUPBYDepartNameWITHCUBE

GO

單元3（維護數(shù)據(jù)）

-12.學(xué)號為"00000025"的同學(xué)第一志愿報名選修“001”課程,

請在數(shù)據(jù)庫中進行處理。

INSERTStuCou(StuNo,CouNo,WillOrder,State)

VALUES「00000025',‘00111J報名1)

GO

/*課程報名人數(shù)增加人*/

UPDATECourse

SETWi1INum-Wi1lNum+1

WHERECouNo='0311

GO

-2.刪除學(xué)號為“00000025”的學(xué)生的選課報名信息。

DELETEStuCou

WHEREStuNo='03000025,

GO

/*課程表'00000025,同學(xué)報名的那些課程報名人數(shù)都要減少1*/

一方法：

UPDATECourse

SETWillNum=WillNum-l

FROMCourse,StuCou

WHEREStuNo='03000025'and

Course.CouNo=StuCou.CouNo

GO

-方法2：(游標(biāo))

DECLARE@CouNochar(3)

DECLAREMYCursorcursor

for

SELECTCouNofromStuCou

WHEREStuNo='00000025,

OPENMYCursor

FETCHNEXTFROMMYCursorINTO@CouNo

WHILE@@FETCH_STATUS=0

BEGIN

UPDATECourseSETWillNum=WillNum-lWHERE

CouNo=@CouNo

FETCHNEXTFROMMYCursorINTO@CouNo

END

CLOSEMYCursor

DEALLOCATEMYCursor

GO

-3.需要將“00多媒體”班級“杜曉靜”同學(xué)的名字修改為“杜

小靜”。

UPDATEStudent

SETStuName='杜小靜'

FROMStudentSzClassC

WHERES.ClassNo=C.ClassNoAND

StuName=L杜曉靜，ANDClassName='00多媒體'

GO

-"00電子商務(wù)”班的“林斌”同學(xué)申請將已選修的“網(wǎng)絡(luò)信息檢

索原理與技術(shù)”課程修改為“Linux操作系統(tǒng)，

UPDATEStuCou

SETCouNo=(SELECTCouNoFROMCourseWHERE

CouNamc-*Linux操作系統(tǒng),)

FROMStuCouSC,StudentSzCourse0,ClassCl

WHERESC.StuNo=S.StuNoAND

SC.CouNo=C.CouNoAND

S.ClassNo=Cl.ClassNoAND

S.StuName=，林斌，AND

Cl.ClassName='00電子商務(wù)，AND

CouName=1網(wǎng)絡(luò)信息檢索原理與技術(shù)1

GO

單元4（數(shù)據(jù)庫設(shè)計）

某公司計劃對產(chǎn)品的銷售情況進行計算機管理。產(chǎn)品表具有產(chǎn)

品編號、產(chǎn)品名稱、單價、庫存數(shù)量4個屬性。客戶表包括有客戶編

號、客戶姓名、住址、聯(lián)系電話4個屬性。產(chǎn)品的入庫表包括有入庫

日期、產(chǎn)品編號、產(chǎn)品名稱、入庫數(shù)量、單價屬性。銷售表包括有銷

售日期、產(chǎn)品編號、產(chǎn)品名稱、客戶編號、客戶姓名、單價、銷售數(shù)

量。

1.繪出產(chǎn)品銷售的E-R圖。

2、將產(chǎn)品銷售的E-R圖轉(zhuǎn)換為關(guān)系數(shù)據(jù)模型。

產(chǎn)品表：

產(chǎn)品編號產(chǎn)品名稱單價庫存數(shù)量

客戶表:

客戶編號客戶名稱住址聯(lián)系電話

入庫表:

入庫日期產(chǎn)品編號產(chǎn)品名稱入庫數(shù)量單價

銷售表:

銷售產(chǎn)品產(chǎn)品客戶客戶單價銷售

日期編號名稱編號姓名數(shù)量

3、將產(chǎn)品銷售關(guān)系數(shù)據(jù)模型規(guī)范為in范式。

可參考“創(chuàng)建和管理表”實訓(xùn)題。

4、請分析主鍵、外鍵、公共鍵。

主鍵：產(chǎn)品表的產(chǎn)品編號，客戶表的客戶編號。

外鍵：入庫表的產(chǎn)品編號，銷售表的產(chǎn)品編號、客戶編號。

5、舉例說明如何保證產(chǎn)品銷售數(shù)據(jù)的完整性。

表數(shù)據(jù)完整性：給產(chǎn)品表、客戶表創(chuàng)建主鍵。

列數(shù)據(jù)的完整性：可以給指定的列創(chuàng)建約束（參見實施數(shù)據(jù)完整性

實訓(xùn)題目）。

參照完整性：給入庫表、銷售表創(chuàng)建外鍵。

單元5（創(chuàng)建與管理數(shù)據(jù)庫）口，

寫出實現(xiàn)如下功能的SQL語句。（實訓(xùn)參考答案）

1.創(chuàng)建名字為Sale的銷售數(shù)據(jù)庫。該數(shù)據(jù)庫有一個名為Sale.mdf

的主數(shù)據(jù)文件和名字為Sale」og.klf的事務(wù)日志文件。主數(shù)據(jù)文件容

量為4MB,日志文件容量為10MB,數(shù)據(jù)文件和日志文件的最大容量

為20MB,文件增長量為1MB。

CREATEDATABASESale

ON

PRIMARY(NAMESale,

FILENAME='C:\Sale.mdf',

SIZE=4,

MAXSIZE=10,

FILEGROWTH=1)

LOGON

(NAME=Sale_logz

FILENAME=?D:\Sale_log.Idf*,

S工ZE=2,

MAXSIZE=10,

FILEGROWTH=1)/*注意結(jié)尾處無逗號*/

GO

2.在Sale數(shù)據(jù)庫下新增名字為UserGroup的文件組。

USESale

Go

ALTERDATABASESale

ADDFILEGROUPJserGroup

Go

3.以增加次數(shù)據(jù)文件的方式擴充Sale數(shù)據(jù)的容量。次數(shù)據(jù)文件容

量為5MB,最大容量為10MB,文件增量為1MB。耍求將次數(shù)據(jù)文

件保存在與事務(wù)日志文件不同的存儲設(shè)備上，次數(shù)據(jù)文件作為

UserGroup文件組的成員。

USEMASTER

GO

ALTERDATABASESale

ADDFILE

(NAME=Sale2,

FILENAME=,C:\Sale2.ndf,,

S工ZE=5MB,

MAXS工ZE=10MB,

FILEGROWTH=1MB)

TOFILEGROUPUserGroup

GO

4.使用存儲過程顯示Sale數(shù)據(jù)庫的信息。

SP_helpdbSale

GO

單元6（創(chuàng)建與管理數(shù)據(jù)表）

1.實訓(xùn)項目都是圍繞Sale數(shù)據(jù)庫展開，進銷存系統(tǒng)通常包括客戶

資料、產(chǎn)品信息、進貨記錄、銷售記錄等。所以針對Sale數(shù)據(jù)庫，

設(shè)計表了表1-4,數(shù)據(jù)請見表5-8,并將在后續(xù)章節(jié)逐步完善。使用

Transcact-SQL語句，在Sale數(shù)據(jù)庫下創(chuàng)建客戶表、產(chǎn)品表、入庫表

和銷售表并輸入數(shù)據(jù)。請將腳本保存為.sql文件。

表1Customer（客戶表）結(jié)構(gòu)

列"數(shù)據(jù)類是否允備注

型及長度許為空

nvarcharNOT

CusNo客戶編號

(3)NULL

CusNamnvarcharNOT

客戶姓名

e(10)NULL

nvarchar

AddressNULL地址

(20)

nvarchar

TelNULL聯(lián)系電話

(20)

表2Product（產(chǎn)品表）結(jié)構(gòu)

數(shù)據(jù)類是否允

列名備注

型及長度許為空

nvarcharNOT

ProNo產(chǎn)品編號

(5)NULL

ProNamnvarcharNOT

產(chǎn)品名

e(20)NULL

DecimalNOT

Price單價

(8,2)NULL

DecimalNOT

Stocks庫存數(shù)量

(8,0)NULL

表3ProIn（入庫表）結(jié)構(gòu)

列名數(shù)據(jù)類是否允備注

型及長度許為空

InputDatNOT

DateTime入庫日期

eNULL

nvarcharNOT

ProNo產(chǎn)品編號

(5)NULL

DecimalNOT

Quantity入庫數(shù)量

(6,0)NULL

表4ProOut（銷售表）結(jié)構(gòu)

列名數(shù)據(jù)類是否允備注

型及長度許為空

NOT

SaleDateDateTime銷售日期

NULL

nvarcharNOT

CusNo客戶編號

(3)NULL

nvarcharNOT

ProNo產(chǎn)品編號

r(5)NULL

DecimalNOT

Quantity銷售數(shù)量

(6,0)NULL

表5Customer（客戶表）數(shù)據(jù)

CusNoCusNameAddressTel

0755-222211

001楊婷深圳

11

0755-222233

002陳萍深圳

33

0755-222255

003李東深圳

55

004葉合廣州

7

005譚新廣州

9

表6Product（產(chǎn)品表）數(shù)據(jù)

ProNoProNamePriceStocks

00001電視3000.00800

00002空調(diào)2000.00500

00003床1000.00300

00004餐桌1500.00200

00005音響5000.00600

00006沙發(fā)6000.00100

表7ProIn（入庫表）數(shù)據(jù)

InputDate（入Quantity（入庫數(shù)

ProNo（產(chǎn)品編號）

庫日期）量）

nvarchar（5）

DateTimeDecimal（6,0）

notnull

notnullnotnull

2006-1-10000110

2006-1-1000025

2006-1-2000015

2006-1-20000310

2006-1-30000110

2006-2-10000320

2006-2-20000110

2006-2-30000430

2006-3-30000320

表8ProOut（銷售表）數(shù)據(jù)

SaleDateCusNoProNoQuantity

2006-1-10010000110

2006-1-2001000025

2006-1-3002000015

2006-2-10020000310

2006-2-20010000110

2006-2-30010000320

2006-3-20030000110

2006-3-20030000430

2006-3-30020000320

一該題創(chuàng)建表時同時創(chuàng)建主鍵、外鍵。先創(chuàng)建表，再創(chuàng)建主鍵、

外鍵的方法參見照實訓(xùn)|5_3

UseSale

Go

CREATETABLECustomer

(CusNonvarchar(3)Primarykey,

CusNamenvarchar(10)notnull,

Addressnvarchar(20),

TelChar(20))

Go

/*創(chuàng)建產(chǎn)品表★/

CREATETABLEProduct

(ProNonvarchar(5)Primarykey,

ProNamenvarchar(20)notnullz

PriceDecimal(8z2)notnull,

StocksDecimal(8,0)notnull)

Go

/*創(chuàng)建入庫表*/

CREATETABLEProIn

(InputDateDateTimenotnullz

ProNonvarchar(5)ReferencesProduct(ProNo),

QuantityDecimal(6,0)notnull)

Go

/*創(chuàng)建銷售表*/

CREATETABLEProOut

(SaleDateDateTimenotnull,

CusNonvarchar(3)notnullReferences

Customer(CusNo),

ProNonvarchar(5)notnullReferences

Product(ProNo),

QuantityDecimal(6,0)notnull)

Go

一向Customer表輸入數(shù)據(jù)--

INSERTINTO

CustomerVALUES「0011，楊婷l，深加

I)

INSERTINTO

CustomerVALUES-002—陳萍一深以I

I)

INSERTINTO

CustomerVALUES('003I，李東,，,深加

','0755-222255551)

INSERTINTO

CustomerVALUES('004'葉合',1廣州

,,

*

INSERTINTO

CustomerVALUE3coe)5I，譚新,J廣州

)

Go

--向Product表中插入數(shù)據(jù)一

INSERTINTO

1

ProductVALUES(00001'z?電視30C0.00,800)

INSERTINTO

ProductVALUES(100002'J空調(diào)120co.00,500)

INSERTINTO

ProductVALUES(‘00003一床'，1000.00,300)

INSERTINTO

11

ProductVALUES(100004',z15C0.00,200)

INSERTINTO

ProductVALUES('00005'J音響',50C0.00,600)

INSERTINTO

ProductVALUES(1000061'沙發(fā)I60C0.00,100)

Go

一向Pro工n表中插入數(shù)據(jù)一

INSERTINTO

f1

ProInVALUES(2006-l-lz'00001,,10)

INSERTINTO

ProInVALUES(12006-1-11,100002',5)

INSERTINTO

ProInVALUES(12006-1-21,'00001',5)

INSERTINTO

ProInVALUES('2006-1-2—0000310)

INSERTINTO

ProInVALUES-2006-1-3','00001110)

INSERTINTO

ProInVALUES('2006-2-11‘00003120)

INSERTINTO

111

ProInVALUES(2006-2-2z00001,,10)

INSERTINTO

ProInVALUES(,2006-2-3,,100004,,30)

INSERTINTO

ProInVALUES('2006-3-3'0000320)

Go

一向ProOut表中插入數(shù)據(jù)一

INSERTINTO

1111

ProOutVALUES(,2006-1-1z001z00001,,10)

INSERTINTO

!!

ProOutVALUES(,2006-1-21,001z*00002',5)

INSERTINTO

111

ProOutVALUES('2006-1-3\002z00001',5)

INSERTINTO

ProOutVALUES(,2006-2-11,10021,100003,,10)

INSERTINTO

ProOutVALUES('2006-2-2-001—00001',10)

INSERTINTO

111

ProOutVALUES('2006-2-31,001z00003,,20)

INSERTINTO

ProOutVALUES('2006-3-21,'0030000110)

INSERTINTO

ProOutVALUES('2006-3-2\1003\100004,,30)

INSERTINTO

1111

ProOutVALUES('2006-3-31,002z00003,20)

Go

單元7（實施數(shù)據(jù)完整性）

1.根據(jù)你的理解，請在實訓(xùn)5_2的表1-4的備注欄中標(biāo)出主鍵、

外鍵。

表1Customer（客戶表）結(jié)構(gòu)

列名數(shù)據(jù)類是否允備注

型及長度許為空

nvarcharNOT

CusNo客戶編號主鍵

(3)NULL

CusNamnvarcharNOT

客戶姓名

e(10)NULL

nvarchar

AddressNULL地址

(20)

nvarchar

TelNULL聯(lián)系電話

(20)

表2Product（產(chǎn)品表）結(jié)構(gòu)

數(shù)據(jù)類是否允

列名備注

型及長度許為空

nvarcharNOT

ProNo產(chǎn)品編號主鍵

(5)NULL

ProNamnvarcharNOT

產(chǎn)品名

e(20)NULL

DecimalNOT

Price單價

(8,2)NULL

DecimalNOT

Stocks庫存數(shù)量

(8,0)NULL

表3ProIn（入庫表）結(jié)構(gòu)

列名數(shù)據(jù)類是否允備注

型及長度許為空

InputDatNOT

DateTime入庫日期

eNULL

ProNoNvarcharNOT產(chǎn)品編號外鍵

(5)NULL

DecimalNOT

Quantity入庫數(shù)量

(6,0)NULL

表4ProOut（銷售表）結(jié)構(gòu)

列名數(shù)據(jù)類是否允備注

型及長度許為空

NOT

SaleDateDateTime銷售日期

NULL

nvarcharNOT

CusNo客戶編號外鍵

(3)NULL

nvarcharNOT

ProNo產(chǎn)品編號外鍵

r(5)NULL

DecimalNOT

Quantity銷售數(shù)量

(6,0)NULL

-該題適用于創(chuàng)建表時沒有創(chuàng)建主鍵、外鍵的情況

/*(23$t。1116工表的主鍵為(：3$]^0*/

ALTERTABLECustomer

ADDCONSTRAINTPK_CustomerPRIMARYKEY(CusNo)

GO

/*Product表的主鍵為ProNo*/

ALTERTABLEProduct

ADDCONSTRAINTPK_ProductPRIMARYKEY(ProNo)

GO

/*外鍵：ProOut表的ProNo*/

ALTERTABLEProOut

ADDCONSTRAINTFK_ProOut_ProductFOREIGNKEY

(ProNo)

REFERENCESProduct(ProNo)

/*外鍵：ProOut表的CusNo*/

ALTERTABLEProOut

ADDCONSTRAINTFK_ProOut_CustomerFOREIGNKEY

(CusNo)

REFERENCESCustomer(CusNo)

/*外鍵：ProIn表的ProNo*/

ALTERTABLEProIn

ADDCONSTRAINTFK_ProIn_ProductFOREIGNKEY

(ProNo)

REFERENCESProduct(ProNo)

—3.約束客戶表Customer的CusNo列值長度為3；產(chǎn)品表Product

的ProNo列值長度為5。

ALTERTABLECustomer

ADDCONSTRAINTCK_Customer_CusNoCHECK

(LEN(CusNo)=3)

GO

ALTERTABLEProduct

ADDCONSTRAINTCK_Product_ProNoCHECK

(LEN(ProNo)=5)

GO

-測試:

INSERTINTO

ProductVALUES('000071平板電腦I3000.00,800)

GO

■一系統(tǒng)會自動截斷為長度5位。

--測試：

INSERTINTO

1

ProductVALUES(009'z'DVD*,3000.00,800)

GO

4.對產(chǎn)品表Product的Stocks歹ij、Price歹U、入庫表ProIn的Quantity

列、銷售表ProOut的Quantity列值進行約束，使其值必須大于0。

ALTERTABLEProduct

ADDCONSTRAINTCK_Product_StocksCHECK(Stocks>0)

GO

ALTERTABLEProduct

ADDCONSTRAINTCK_Product_PriceCHECK(Price>0)

GO

ALTERTABLEProIn

ADDCONSTRAINTCK_ProIn_QuantityCHECK

(Quantity>0)

GO

ALTERTABLEProOut

ADDCONSTRAINTCK_ProOut_QuantityCHECK

(Quantity〉。)

GO

5.對銷售表ProOut的SaleDate列進行約束，當(dāng)不輸入值時，系統(tǒng)

默認其值為系統(tǒng)當(dāng)前口期。

CREATEDEFAULTCurrentDate

ASGETDATE()

GO

EXECsp_bindefaultCurrentDate,*ProOut.SaleDate,

GO

單元8（提高查詢速度）

本單元實訓(xùn)使用Sale數(shù)據(jù)庫。

1.用戶按照CusName（客戶姓名）查詢客戶信息，希望提高查詢

速度。

USESale

GO

CREATEINDEXIX_Customer

ONCustomer（CasName）

GO

2.用戶按照ProName（產(chǎn)品名稱）查詢產(chǎn)品信息，希望提高查詢

速度。

USESale

GO

CREATEINDEXIX_Product

ONProduct（ProName）

GO

3.用戶按照SaleDate（銷售日期）查詢銷售信息，希望提高查詢

速度。

USESale

GO

CREATEINDEXIX_ProOut

ONProOut(SaleDate)

GO

單元9(Transcact-SQL語言編程基礎(chǔ))

本單元實訓(xùn)使用Sale數(shù)據(jù)庫。

1.計算有多少種產(chǎn)品(假設(shè)為@x),然后顯示一條信息：共有x

種產(chǎn)品。

USESale

GO

DECLARE@xint

SET@x=(SELECTCOUNT(*)FROMProduct)

1

PRINT'ftW+convert(char(2)A@x)+，種產(chǎn)品。*

GO

2.編寫計算n!(n=20)的程序，并顯示計算結(jié)果。

DECLARE@iint,@PRODUCTFLOAT一定義整型變量用來

計數(shù)；?Product為存放結(jié)果的單元

SELECT@i-l,0PRODUCT-1―給整型變量@i和@sum賦值

WHILE@i<=20一當(dāng)@i小于等于時，執(zhí)行循環(huán)

體

BEGIN一定義語句塊

SELECT@PRODUCT=@PRODUCT*@i一求和

SELECT一計數(shù)單元加

END一語句塊定義結(jié)束

PRINTf20!=f+convert(char(11)PRODUCT)一顯示

結(jié)果

GO

3、創(chuàng)建一自定義函數(shù)，能夠利用該函數(shù)計算出銷售總金額(數(shù)量

Quantity*單價Price)o

CREATEFUNCTIONGETProduct

(?Quantitydecimal(6,0),

@Pricedecimal(8,2))

RETURNSdecimal(10,2)

AS

BEGIN

RETURN(@Quantity*@Price)

END

一測試

SELECT

Price,Quantity,dbo.GETProduct(Price,Quantity)

'ProductQu,

FROMProduct,ProOut

WHEREProduct.ProNo=ProOut.ProNo

GO

單元10(保證數(shù)據(jù)安全性.視圖)

本實訓(xùn)使用Sale數(shù)據(jù)庫。

1.創(chuàng)建視圖V_Salel,顯示銷售日期、客戶編號、客戶姓名、產(chǎn)

品編號、產(chǎn)品名稱、單價、銷售數(shù)量和銷售金額。

CREATEVIEWV_3alel

AS

SELECT

SaleDate,CusName,ProName,Price,Quantity,SaleMoney

=(Price*Quantity)

FROMProOutPO,CustomerC,ProductP

WHEREP.ProNo=PO.ProNoANDC.CusNo=PO.CusNo

GO

2.創(chuàng)建視圖V_Sale2,統(tǒng)計每種產(chǎn)品的銷售數(shù)量和銷售金額。統(tǒng)

計結(jié)果包括：產(chǎn)品編號、產(chǎn)品名稱、單價、銷售數(shù)量和銷售金額。

CREATEVIEWV_Sale2

AS

SELECT

P.ProNo,P.ProNama,SaleQuantity=SUM(Quantity),Sale

Money=SUM(Price*2uantity)

FROMProOutPO,ProductP

WHEREP.ProNo=PO.ProNo

GROUPBYP.ProNo,P.ProName

GO

3.創(chuàng)建視圖V_Sale3,統(tǒng)計銷售金額在10萬以下的產(chǎn)品信息。

CREATEVIEWV_Sale3

AS

SELECTP.ProNo,P.ProName

FROMProOutP0,ProductP

WHEREP.ProNo=PO.ProNo

GROUPBYP.ProNozP.ProName

HAVINGSUM(Price*Quantity)<100000

GO

單元11(保證數(shù)據(jù)安全性.存儲過程)

本實訓(xùn)使用Sale數(shù)據(jù)庫。

1.創(chuàng)建存儲過P_Salel,統(tǒng)計每種產(chǎn)品的銷售數(shù)量和銷售金額。

CREATEPROCEDUREP_Salel

AS

SELECT

Product.ProNo,ProName,Price,S1=SUM(Quantity),Je=S

UM(Price*Quantity)

FROMProOut,Product

WHEREProOut.ProNo=Product.ProNo

GROUPBYProduct.ProNo,ProName,Price

GO

—調(diào)用

EXECP_Salel

2.創(chuàng)建存儲過程P_Sale2,能夠根據(jù)指定的客戶統(tǒng)計匯總該客戶

購買每種產(chǎn)品的數(shù)量和花費金額。

CREATEPROCEDUREP_Sale2

@CusNonvarchar(3)

AS

SELECT

Product.ProNo,ProName,Price,S1=SUM(Quantity),Je=S

UM(Price*Quantity)

FROMProOut,Product

WHEREProOut.ProNo=Product.ProNoAND

CusNo=@CusNo

GROUPBYProduct.ProNo,ProName,Price

GO

―調(diào)用

EXECP_Sale2'301'

EXECP_Sale21302'

GO

3.創(chuàng)建存儲過程P_Sale3,能夠根據(jù)指定的產(chǎn)品編號和口期，以

輸出參數(shù)的形式得到該產(chǎn)品的銷售金額。

USESALE

GO

CREATEPROCEDUREP_Sale3

QProNonvarchar(5),@SaleDateDateTime,@Je

Decimal(8,2)OUTPUT

AS

SET@Je=

(SELECTSUM(Price*Quantity)

FROMProOut,Product

WHEREProOut.ProNo=Product.ProNoAND

Product.ProNo=@ProNoANDSaleDate=@SaleDate

GROUPBYProduct.ProNo,ProName,Price

)

print@je

GO

--調(diào)用

DECLARE@JeDecimal(8,2)

EXECP_Sale3!30001',,2006-1-1',@Je

GO

單元12（實現(xiàn)業(yè)務(wù)邏輯（觸發(fā)器））

本實訓(xùn)使用Sale數(shù)據(jù)庫。

1.創(chuàng)建觸發(fā)器，實現(xiàn)即時更新每種產(chǎn)品的庫存數(shù)量。

―對入庫表進行增加、修改入庫數(shù)量、刪除數(shù)據(jù)時

--向ProIn表輸入數(shù)據(jù)時（入庫），Product表庫存Stocks=庫存+入

庫的數(shù)量Quantity

CREATETRIGGERT_INSERT_ProIn

ONProIn

FORINSERT

AS

UPDATEProduct

SETStocks=Stocks+（SELECTQuantityFROM

INSERTED）

WHEREProNo=JSELECTProNoFROMINSERTED）

PRINT，已更新產(chǎn)品表的庫存數(shù)量，

GO

一測試

INSERTProIn

values(12006-3-51,'00003',100)

GO

一刪除ProIn一行數(shù)據(jù)時，Product表庫存Stocks;庫存-入庫表的數(shù)

量Quantity

CREATETRIGGERT_DELETE_ProIn

ONProIn

FORDELETE

AS

UPDATEProduct

SETStocks=Stocks-(SELECTQuantityFROMDELETED)

WHEREProNo=(SELECTProNoFROMDELETED)

PRINT，已更新產(chǎn)品表的庫存數(shù)量，

GO

一測試

DELETEProIn

WHEREInputDate=*2006-3-31and

ProNo=100303'

GO

―修改Pro工n表的Quantity入庫數(shù)量時

CREATETRIGGERT_UPDATE_ProIn

ONProIn

FORUPDATE

AS

IFUPDATE(Quantity)

BEGIN

UPDATEProduct

SETStocksStocks+(SELECTQuantityFROM

INSERTED)

WHEREProNo=(SELECTProNoFROMINSERTED)

UPDATEProduct

—測試

UPDATEProIn

SETQuantity=530

WHEREInputDate=,2006-3-51andProNo=1000031

GO

一在銷售表上創(chuàng)建觸發(fā)器

CREATETRIGGERTINSERTProOut

ONProOut

FORINSERT

AS

IF(SELECTQuantityFROMINSERTED)<=

(SELECTStocksFROMProduct

WHEREProNo=(SELECTProNoFROMINSERTED))

BEGIN

UPDATEProduct

SETStocks=Stocks-(SELECTQuantityFROM

INSERTED)

寫至lj這里了WHEREProNo=(SELECTProNoFROM

INSERTED)

PRINT'已更新產(chǎn)品表的庫存數(shù)量，

END

ELSE

BEGIN

PRINT,銷售數(shù)量低于庫存量，不能銷售！，

ROLLBACKTRAN

END

GO

一測試

INSERTProOut

!

VALUES('2006-3-6'f001\'00003,,1000)

GO

CREATETRIGGERT_DELETE_ProOut

ONProOut

FORDELETE

AS

UPDATEProduct

SETStocks=Stocks+(SELECTQuantityFROMDELETED)

WHEREProNo=(SELECTProNoFROMDELETED)

PRINT，已更新產(chǎn)品表的庫存數(shù)量，

GO

―測試

DELETEProOut

WHERESaleDate=,2006-3-6'and

ProNo=f003031ANDQUANTITY=1000

GO

2.使用IFUPDATE(column)盡可能優(yōu)化上題中的觸發(fā)器以提高系

統(tǒng)效率。

CREATETRIGGERT_UPDATE_ProOut

ONProOut

FORUPDATE

AS

IFUPDATE(Quantity)

BEGIN

UPDATEProductSETStocks=Stocks-(SELECTQuantity

FROMINSERTED)

WHEREProNo=(SELECTProNoFROMINSERTED)

UPDATEProductSETStocks=Stocks+(SELECTQuantity

FROMDELETED)

WHEREProNo=(SELECTProNoFROMDELETED)

PRINT，已更新產(chǎn)品表的庫存數(shù)量，

END

GO

UPDATEProOut

SETQuantity=50

WHERESaleDate=12006-l-l'andCusNo=,001'and

ProNo=1000011andQuantity=10

GO

CREATETRIGGERT3

ONProInFORUPDATE

AS

IFUPDATE(Quantity)

BEGIN

UPDATEProductSETStocks=Stocks+(SELECT

QuantityFROMINSERTED)

WHEREProNo=[SELECTProNoFROMINSERTED)

UPDATEProductSETStocks=Stocks-(SELECT

QuantityFROMDELETED)

WHEREProNo=JSELECTProNoFROMINSERTED)

END

GO

CREATETRIGGERT6

ONProOutFORUPDATE

AS

IFUPDATE(Quantity)

BEGIN

UPDATEProductSETStocks=Stocks-(SELECT

QuantityFROMINSERTED)

WHEREProNo=[SELECTProNoFROMINSERTED)

UPDATEProductSETStocks=Stocks+(SELECT

QuantityFROMDELETED)

WHEREProNo=[SELECTProNoFROMDELETED)

END

GO

單元13（逐行處理查詢結(jié)果_游標(biāo)）

本實訓(xùn)使用Sale數(shù)據(jù)庫。

1.創(chuàng)建存儲過程P.SelProduct,逐行顯示產(chǎn)品銷售信息，內(nèi)容包

括產(chǎn)品編號、產(chǎn)品名稱、銷售日期、銷售數(shù)量、銷售金額，要求顯示

格式如下：

產(chǎn)品編號產(chǎn)品名稱銷售日期銷售數(shù)量銷售

金額

00001電視2006-1-110

30000

產(chǎn)品編號產(chǎn)品名稱銷售日期銷售數(shù)量銷售

金額

00002空調(diào)2006-1-25