SQL存儲過程實例練習(xí)

字段名稱

數(shù)據(jù)類型

stuID

char(10)

stuName

major

Varchar(10)

Varchar(50)

專業(yè)

數(shù)據(jù)類型

BID

title

char(10)

char(50)

char(20)

author

數(shù)據(jù)類型

BID

T_time

B_time

1)查詢“計算機”專業(yè)學(xué)生在“2007-12-15”至“2008-1-8”時間段內(nèi)借書的學(xué)生編號、

學(xué)生名稱、圖書編號、圖書名稱、借出日期；參考查詢結(jié)果如下圖所示：

2)查詢所有借過圖書的學(xué)生編號、學(xué)生名稱、專業(yè)；參考查詢結(jié)果如下圖所示：

參考查詢結(jié)果如下圖所示：

示：

GO

/*$$$$$$$$$$$$$建庫$$$$$$$$$$$$$$$$$$$$$$$$*/

GO

GO

(

)

GO

CREATETABLEbook--圖書表

(

BIDCHAR(10)primarykey,

--圖書編號

)

GO

CREATETABLEborrow--借書表

(

borrowIDCHAR(10)primarykey,

--借書編號

B_timedatetime

--歸還日期

)

GO

--學(xué)生信息表中插入數(shù)據(jù)--

INSERTINTOstudent(stuID,stuName,major)VALUES('1001',林'林','計算機')

INSERTINTOstudent(stuID,stuName,major)VALUES('1002',白'楊','計算機')

INSERTINTOstudent(stuID,stuName,major)VALUES('1003',虎'子','英語')

INSERTINTOstudent(stuID,stuName,major)VALUES('1004',北'漂的雪','工商管理')

INTO

INTO

INTO

borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T001','1001','B001','2007-12-26',null)

INSERT

borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T002','1004','B003','2008-1-5',null)

INSERT

borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T003','1005','B001','2007-10-8','2007-12-25')

INSERTINTO

borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T004','1005','B002','2007-12-16','2008-1-7')

INTO

INTO

INTO

INTO

INTO

borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T005','1002','B004','2007-12-22',null)

INSERT

borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T006','1005','B005','2008-1-6',null)

INSERT

borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T007','1002','B001','2007-9-11',null)

INSERT

borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T008','1005','B004','2007-12-10',null)

INSERT

borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T009','1004','B005','2007-10-16','2007-12-18'

)

INSERT

INTO

borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T010','1002','B002','2007-9-15','2008-1-5')

INSERT

INTO

borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T011','1004','B003','2007-12-28',null)

INSERT

INTO

borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T012','1002','B003','2007-12-30',null)

標(biāo)準(zhǔn)答案:

--1)查詢“計算機”專業(yè)學(xué)生在“2007-12-15”至“2008-1-8”時間段內(nèi)借書的學(xué)生編號、學(xué)

生名稱、圖書編號、圖書名稱、借出日期—

select學(xué)生編號=stuID,學(xué)生名稱=(selectstuNamefromstudentwherestuID=borrow.stuID),圖

書編號=BID,圖書名稱=(selecttitlefrombookwhereBID=borrow.BID),借出日期=T_timefrom

borrowwherestuIDin(selectstuIDfromstudentwheremajor='計算機')and

題目2

程序員工資表：ProWage

數(shù)據(jù)類型

ID

請編寫T-SQL來實現(xiàn)如下功能：

1)創(chuàng)建存儲過程，查詢是否有一半程序員的工資在2200、3000、3500、4000、5000或6000

元之上，如果不到分別每次給每個程序員加薪100元，至之一半程序員的工資達到2200，

3000，3500，4000，5000或6000元。

2)創(chuàng)建存儲過程，查詢程序員平均工資在4500元，如果不到則每個程序員每次加200元，

至到所有程序員平均工資達到4500元。

建表語句

GO

/*$$$$$$$$$$$$$建庫$$$$$$$$$$$$$$$$$$$$$$$$*/

IFexists(SELECT*FROMsysdatabasesWHEREname='Wage')

GO

CREATEDATABASEWage

GO

--建數(shù)據(jù)表--

USEWage

GO

CREATETABLEProWage--程序員工資表

(

IDintidentity(1,1)primarykey,--工資編號

PNameCHAR(10)NOTNULL,

WageintNOTNULL--工資

--程序員姓名

)

GO

--插入數(shù)據(jù)--

INSERTINTOProWage(PName,Wage)VALUES(青'鳥',1900)

INSERTINTOProWage(PName,Wage)VALUES(張'三',1200)

INSERTINTOProWage(PName,Wage)VALUES(李'四',1800)

INSERTINTOProWage(PName,Wage)VALUES(二'月',3500)

INSERTINTOProWage(PName,Wage)VALUES(藍'天',2780)

標(biāo)準(zhǔn)答案：

--1、創(chuàng)建存儲過程--

ifexists(select*fromsysobjectswherename='Sum_wage')

dropprocedureSum_wage

GO

createprocedureSum_wage

begin

if(selectcount(*)fromProWage)>2*(selectcount(*)fromProWagewhereWage>=@PWage)

updateProWageset@total=@total+@AWage,Wage=Wage+@AWage

else

break

end

--2、創(chuàng)建存儲過程2--

ifexists(select*fromsysobjectswherename='Avg_wage')

dropprocedureAvg_wage

GO

createprocedureAvg_wage

@PWageint,

@AWageint,

@totalint

as

while(1=1)

begin

if((selectAvg(Wage)fromProWage)<=@PWage)

updateProWageset@total=@total+@AWage,Wage=Wage+@AWage

else

break

end

--調(diào)用存儲過程--

execAvg_wage@PWage=3000,@AWage=200,@total=0

execAvg_wage@PWage=4500,@AWage=200,@total=0

題目3：

學(xué)生成績信息三個表，結(jié)構(gòu)如下：

學(xué)生表：Member

字段名稱

數(shù)據(jù)類型

Char(10)

Char(50)

說明

說明

說明

MID

學(xué)生號，主鍵

姓名

MName

課程表：

字段名稱

數(shù)據(jù)類型

Char(10)

Char(50)

FID

課程，主鍵

課程名

FName

成績表：Score

字段名稱

數(shù)據(jù)類型

SID

自動編號，主鍵，成績記錄號

課程號，外鍵

FID

Char(10)

Char(10)

int

MID

Score

成績

請編寫T-SQL語句來實現(xiàn)如下功能：

1)查詢各個學(xué)生語文、數(shù)學(xué)、英語、歷史課程成績，例如下表：

姓名

張薩

王強

李三

李四

語文

78

數(shù)學(xué)

67

89

67

70

87

80

78

97

66

2)查詢四門課中成績低于70分的學(xué)生及相對應(yīng)課程名和成績。

3)統(tǒng)計各個學(xué)生參加考試課程的平均分，且按平均分數(shù)由高到底排序。

4)創(chuàng)建存儲過程，分別查詢參加1、2、3、4門考試及沒有參加考試的學(xué)生名單，要求顯

示姓名、學(xué)號。

建表語句:

GO

/*$$$$$$$$$$$$$建庫$$$$$$$$$$$$$$$$$$$$$$$$*/

IFexists(SELECT*FROMsysdatabasesWHEREname='Student')

GO

GO

CREATETABLEMember--學(xué)生表

(

MNameCHAR(50)NOTNULL--姓名

)

GO

CREATETABLEF--課程表

(

FIDchar(10)primarykey,

--課程號

FNameCHAR(50)NOTNULL--課程名

)

GO

CREATETABLEscore--學(xué)生成績表

(

--課程號

--學(xué)生號

)

GO

--課程表中插入數(shù)據(jù)--

INSERTINTOMember(MID,MName)VALUES('M001',張'薩')

INSERTINTOMember(MID,MName)VALUES('M002',王'強')

INSERTINTOMember(MID,MName)VALUES('M003',李'三')

INSERTINTOMember(MID,MName)VALUES('M004',李'四')

INSERTINTOMember(MID,MName)VALUES('M005',陽'陽')

INSERTINTOMember(MID,MName)VALUES('M006',虎'子')

INSERTINTOMember(MID,MName)VALUES('M007',夏'雪')

INSERTINTOMember(MID,MName)VALUES('M008',璐'璐')

INSERTINTOMember(MID,MName)VALUES('M009',珊'珊')

INSERTINTOMember(MID,MName)VALUES('M010',香'奈兒')

--成績表中插入數(shù)據(jù)--

INSERTINTOScore(FID,MID,Score)VALUES('F001','M001',78)

INSERTINTOScore(FID,MID,Score)VALUES('F002','M001',67)

INSERTINTOScore(FID,MID,Score)VALUES('F003','M001',89)

INSERTINTOScore(FID,MID,Score)VALUES('F004','M001',76)

INSERTINTOScore(FID,MID,Score)VALUES('F001','M002',89)

INSERTINTOScore(FID,MID,Score)VALUES('F002','M002',67)

INSERTINTOScore(FID,MID,Score)VALUES('F003','M002',84)

INSERTINTOScore(FID,MID,Score)VALUES('F004','M002',96)

INSERTINTOScore(FID,MID,Score)VALUES('F001','M003',70)

INSERTINTOScore(FID,MID,Score)VALUES('F002','M003',87)

INSERTINTOScore(FID,MID,Score)VALUES('F003','M003',92)

INSERTINTOScore(FID,MID,Score)VALUES('F004','M003',56)

INSERTINTOScore(FID,MID,Score)VALUES('F001','M004',80)

INSERTINTOScore(FID,MID,Score)VALUES('F002','M004',78)

INSERTINTOScore(FID,MID,Score)VALUES('F003','M004',97)

INSERTINTOScore(FID,MID,Score)VALUES('F004','M004',66)

INSERTINTOScore(FID,MID,Score)VALUES('F001','M006',88)

INSERTINTOScore(FID,MID,Score)VALUES('F002','M006',55)

INSERTINTOScore(FID,MID,Score)VALUES('F003','M006',86)

INSERTINTOScore(FID,MID,Score)VALUES('F004','M006',79)

INSERTINTOScore(FID,MID,Score)VALUES('F002','M007',77)

INSERTINTOScore(FID,MID,Score)VALUES('F003','M008',65)

INSERTINTOScore(FID,MID,Score)VALUES('F004','M007',48)

INSERTINTOScore(FID,MID,Score)VALUES('F004','M009',75)

INSERTINTOScore(FID,MID,Score)VALUES('F002','M009',88)

標(biāo)準(zhǔn)答案:

英語=SUM(CASEF.FNameWHEN'語文'THENScore.ScoreEN