SQL存储过程实例Word下载.docx

资源描述

SQL存储过程实例Word下载.docx

《SQL存储过程实例Word下载.docx》由会员分享，可在线阅读，更多相关《SQL存储过程实例Word下载.docx（14页珍藏版）》请在冰点文库上搜索。

SQL存储过程实例Word下载.docx

datetime

借书日期

B_time

还书日期

请编写SQL语句完成以下的功能：

1）查询“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的学生编号、学生名称、图书编号、图书名称、借出日期；

参考查询结果如下图所示：

2）查询所有借过图书的学生编号、学生名称、专业；

3）查询借过作者为“安意如”的图书的学生姓名、图书名称、借出日期、归还日期；

4）查询目前借书但未归还图书的学生名称及未还图书数量；

附加：

建表语句:

USEmaster

/*$$$$$$$$$$$$$建库$$$$$$$$$$$$$$$$$$$$$$$$*/

--检验数据库是否存在，如果为真，删除此数据库--

IFexists（SELECT*FROMsysdatabasesWHEREname='

BOOK'

）

DROPDATABASEBOOK

CREATEDATABASEBOOK

--建数据表--

USEBOOK

CREATETABLEstudent--学生信息表

（

stuIDCHAR（10）primarykey,--学生编号

stuNameCHAR（10）NOTNULL,--学生名称

majorCHAR（50）NOTNULL--专业

CREATETABLEbook--图书表

BIDCHAR（10）primarykey,--图书编号

titleCHAR（50）NOTNULL,--书名

authorCHAR（20）NOTNULL,--作者

CREATETABLEborrow--借书表

borrowIDCHAR（10）primarykey,--借书编号

stuIDCHAR（10）foreignkey（stuID）referencesstudent（stuID）,--学生编号

BIDCHAR（10）foreignkey（BID）referencesbook（BID）,--图书编号

T_timedatetimeNOTNULL,--借出日期

B_timedatetime--归还日期

--学生信息表中插入数据--

INSERTINTOstudent（stuID,stuName,major）VALUES（'

1001'

林林'

计算机'

1002'

白杨'

1003'

虎子'

英语'

1004'

北漂的雪'

工商管理'

1005'

五月'

数学'

--图书信息表中插入数据--

INSERTINTObook（BID,title,author）VALUES（'

B001'

人生若只如初见'

安意如'

B002'

入学那天遇见你'

晴空'

B003'

感谢折磨你的人'

如娜'

B004'

我不是教你诈'

刘庸'

B005'

英语四级'

白雪'

--借书信息表中插入数据--

INSERTINTOborrow（borrowID,stuID,BID,T_time,B_time）VALUES（'

T001'

2007-12-26'

null）

T002'

2008-1-5'

T003'

2007-10-8'

2007-12-25'

T004'

2007-12-16'

2008-1-7'

T005'

2007-12-22'

T006'

2008-1-6'

T007'

2007-9-11'

T008'

2007-12-10'

T009'

2007-10-16'

2007-12-18'

T010'

2007-9-15'

T011'

2007-12-28'

T012'

2007-12-30'

标准答案:

--1）查询“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的学生编号、学生名称、图书编号、图书名称、借出日期—

select学生编号=stuID,学生名称=（selectstuNamefromstudentwherestuID=borrow.stuID）,图书编号=BID,图书名称=（selecttitlefrombookwhereBID=borrow.BID）,借出日期=T_timefromborrowwherestuIDin（selectstuIDfromstudentwheremajor='

）andT_time>

2007-12-15'

andT_time<

2008-1-8'

--2）查询所有借过图书的学生编号、学生名称、专业--

select学生编号=stuID,学生名称=stuName,专业=majorfromstudentwherestuIDin（selectstuIDfromborrow）

--3）查询借过作者为“安意如”的图书的学生姓名、图书名称、借出日期--

select学生名称=（selectstuNamefromstudentwherestuID=borrow.stuID）,图书名称=（selecttitlefrombookwhereBID=borrow.BID）,借出日期=T_time,归还日期=B_timefromborrowwhereBIDin（selectBIDfrombookwhereauthor='

--4）查询目前借书但未归还图书的学生名称及未还图书数量--

select学生名称=（selectstuNamefromstudentwherestuID=borrow.stuID）,借书数量=count（*）fromborrowwhereB_timeisnullgroupbystuID

题目2

程序员工资表：

ProWage

int

自动编号，主键

PName

Char（10）

程序员姓名

Wage

工资

创建一个存储过程，对程序员的工资进行分析，月薪1500到10000不等，如果有百分之五十的人薪水不到2000元，给所有人加薪，每次加100，再进行分析，直到有一半以上的人大于2000元为止，存储过程执行完后，最终加了多少钱？

例如：

如果有百分之五十的人薪水不到2000，给所有人加薪，每次加100元，直到有一半以上的人工资大于2000元，调用存储过程后的结果如图：

请编写T-SQL来实现如下功能：

1）创建存储过程，查询是否有一半程序员的工资在2200、3000、3500、4000、5000或6000元之上，如果不到分别每次给每个程序员加薪100元，至之一半程序员的工资达到2200，3000，3500，4000，5000或6000元。

2）创建存储过程，查询程序员平均工资在4500元，如果不到则每个程序员每次加200元，至到所有程序员平均工资达到4500元。

建表语句

Wage'

DROPDATABASEWage

CREATEDATABASEWage

USEWage

CREATETABLEProWage--程序员工资表

IDintidentity（1,1）primarykey,--工资编号

PNameCHAR（10）NOTNULL,--程序员姓名

WageintNOTNULL--工资

--插入数据--

INSERTINTOProWage（PName,Wage）VALUES（'

青鸟'

1900）

张三'

1200）

李四'

1800）

二月'

3500）

蓝天'

2780）

标准答案：

--1、创建存储过程--

ifexists（select*fromsysobjectswherename='

Sum_wage'

dropprocedureSum_wage

createprocedureSum_wage

@PWageint,

@AWageint,

@totalint

while（1=1）

begin

if（selectcount（*）fromProWage）>

2*（selectcount（*）fromProWagewhereWage>

=@PWage）

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

else

break

end

print'

一共加薪：

+convert（varchar,@total）+'

元'

加薪后的程序员工资列表：

select*fromProWage

--调用存储过程1--

execSum_wage@PWage=2000,@AWage=100,@total=0

execSum_wage@PWage=2200,@AWage=100,@total=0

execSum_wage@PWage=3000,@AWage=100,@total=0

execSum_wage@PWage=4000,@AWage=100,@total=0

execSum_wage@PWage=5000,@AWage=100,@total=0

execSum_wage@PWage=6000,@AWage=100,@total=0

--2、创建存储过程2--

Avg_wage'

dropprocedureAvg_wage

createprocedureAvg_wage

if（（selectAvg（Wage）fromProWage）<

--调用存储过程--

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

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

题目3：

学生成绩信息三个表，结构如下：

学生表：

Member

MID

学生号，主键

MName

Char（50）

姓名

课程表：

FID

课程，主键

FName

课程名

成绩表：

Score

SID

自动编号，主键，成绩记录号

课程号，外键

学生号，外键

成绩

请编写T-SQL语句来实现如下功能：

1）查询各个学生语文、数学、英语、历史课程成绩，例如下表：

语文

数学

英语

历史

张萨

王强

李三

李四

2）查询四门课中成绩低于70分的学生及相对应课程名和成绩。

3）统计各个学生参加考试课程的平均分，且按平均分数由高到底排序。

4）创建存储过程，分别查询参加1、2、3、4门考试及没有参加考试的学生名单，要求显示姓名、学号。

Student'

DROPDATABASEStudent

CREATEDATABASEStudent

USEStudent

CREATETABLEMember--学生表

MIDchar（10）primarykey,--学生号

MNameCHAR（50）NOTNULL--姓名

CREATETABLEF--课程表

FIDchar（10）primarykey,--课程号

FNameCHAR（50）NOTNULL--课程名

CREATETABLEscore--学生成绩表

SIDintidentity（1,1）primarykey,--成绩记录号

FIDchar（10）foreignkey（FID）referencesF（FID）,--课程号

MIDchar（10）foreignkey（MID）referencesMember（MID）,--学生号

ScoreintNOTNULL--成绩

--课程表中插入数据--

INSERTINTOF（FID,FName）VALUES（'

F001'

语文'

F002'

F003'

F004'

历史'

--学生表中插入数据--

INSERTINTOMember（MID,MName）VALUES（'

M001'

张萨'

M002'

王强'

M003'

李三'

M004'

M005'

阳阳'

M006'

M007'

夏雪'

M008'

璐璐'

M009'

珊珊'

M010'

香奈儿'

--成绩表中插入数据--

INSERTINTOScore（FID,MID,Score）VALUES（'

78）

67）

89）

76）

84）

96）

70）

87）

92）

56）

80）

97）

66）

88）

55）

86）

79）

77）

65）

48）

展开阅读全文