SQL存储过程实例(练习和答案)
题目11、学校图书馆借书信息管理系统建立三个表学生信息表:student字段名称数据类型说明stulDchar(10)学生编号,主键stuNameVarchar(IO)学生名称majorVarchar(50)专业图书表: book字段名称数据类型说明BIDchar(10)图书编号,主键titlechar(50)书名authorchar(20)作者借书信息表: borrow字段名称数据类型说明borrowIDchar(10)借书编号,主键stuIDchar(10)学生编号,外键BIDchar(10)图书编号,外键T_timedatetime借书日期B_timedatetime还书日期请编写 SQL 语句完成以下的功能:1) 查询“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的学生编号学生名称、图书编号、图书名称、借出日期;参考查询结果如下图所示:学生编寻学生名称團书编号借岀日期1001林林B001人生若只如初见2007-12-26 00:00:00.0001002白杨B004我不是教你诈2007-12-22 00;00;00.0001002白杨B003感谢新眶怖的人2007-12-30 00:00:00.0002) 查询所有借过图书的学生编号、学生名称、专业;参考查询结果如下图所示:学圭編号学生名称专业| 1001林林计算杭1002白畅计算机1004北標的雪工商管理1005五月歡学3) 查询借过作者为“安意如”的图书的学生姓名、图书名称、借出日期、归还日期参考查询结果如下图所示:学生名称圏书名称|惜出日期归还日期林林人生若只如初见2007-12-26 00:00:00.000NULL五月人生若只如初见2007-10-08 00:00:00.0002007-12-25 00:00:00.000白杨人生若只如初见2007-09-11 00:00:00.000NULL4) 查询目前借书但未归还图书的学生名称及未还图书数量;参考查询结果如下图所示:附加:建表语句:USE masterGO/*$ 建库 $*/-检验数据库是否存在,如果为真,删除此数据库-IF exists(SELECT * FROM sysdatabases WHERE name='BOOK')DROP DATABASE BOOKGOCREATE DATABASE BOOKGO-建数据表-USE BOOKGOCREATE TABLE student -学生信息表(stuID CHAR(10) primary key,-学生编号stuName CHAR(10) NOT NULL ,-学生名称major CHAR(50) NOT NULL-专业)GOCREATE TABLE book -图书表(BID CHAR(10) primary key,-图书编号title CHAR(50) NOT NULL,-书名author CHAR(20) NOT NULL,-作者)GOCREATE TABLE borrow -借书表(borrowID CHAR(10) primary key, -借书编号stuID CHAR(10) foreign key(stuID) references student(stulD),-学生编号BID CHAR(10) foreign key(BID) references book(BID),-图书编号T_time datetime NOT NULL,-借出日期B_time datetime -归还日期)GO-学生信息表中插入数据-INSERT INTO student(stulD,stuName,major)VALUES('1001',林林','计算机')INSERT INTO student(stulD,stuName,major)VALUES('1002',白杨','计算机')INSERT INTO student(stuID,stuName,major)VALUES('1003',虎子','英语')INSERT INTO student(stuID,stuName,major)VALUES('1004',北漂的雪','工商管理') INSERT INTO student(stulD,stuName,major)VALUES('1005',五月','数学')-图书信息表中插入数据-INSERT INTO book(BID,title,author)VALUES('B001',人生若只如初见','安意如')INSERT INTO book(BID,title,author)VALUES('B002',入学那天遇见你','晴空')INSERT INTO book(BID,title,author)VALUES('B003',感谢折磨你的人','如娜')INSERT INTO book(BID,title,author)VALUES('B004',我不是教你诈','刘庸')INSERT INTO book(BID,title,author)VALUES('B005',英语四级','白雪')-借书信息表中插入数据-INSERTINTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T001','1001','B001','2007-12-26',null)INSERTINTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T002','1004','B003','2008-1-5',null)INSERTINTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T003','1005','B001','2007-10-8','2007-12-25')INSERTINTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T004','1005','B002','2007-12-16','2008-1-7')INSERTINTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T005','1002','B004','2007-12-22',null)INSERTINTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T006','1005','B005','2008-1-6',null)INSERTINTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T007','1002','B001','2007-9-11',null)INSERTINTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T008','1005','B004','2007-12-10',null)INSERTINTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T009','1004','B005','2007-10-16','2007-12-18' )INSERTINTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T010','1002','B002','2007-9-15','2008-1-5')INSERTINTOborrow(borrowID,stuID,BID,T_time,B_time)VALUES('T011','1004','B003','2007-12-28',null)INSERTINTOborrow(borrowID,stuID,BID,T time,B time)VALUES('T012','1002','B003','2007-12-30',null)标准答案:-1)查询“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的学生编号、学 生名称、图书编号、图书名称、借出日期一select 学生编号=stuID,学生名称=(select stuName from student where stuID=borrow.stuID),图 书编号=BID,图书名称=(select title from book where BID=borrow.BID),借出日期=T_time from borrow where stuID in (select stuID from student where major计算机')andT_time>'2007-12-15' and T_time<'2008-1-8'-2)查询所有借过图书的学生编号、学生名称、专业-select 学生编号=stulD,学生名称=stuName,专业=major from student where stulD in (select stulD from borrow)-3)查询借过作者为“安意如”的图书的学生姓名、图书名称、借出日期-select 学生名称=(select stuName from student where stuID=borrow.stuID),图书名称=(select title from book where BID=borrow.BID),借出日期=T_time,归还日期=B_time from borrow where BID in (select BID from book where author=安意如')-4)查询目前借书但未归还图书的学生名称及未还图书数量-select 学生名称=(select stuName from student where stuID=borrow.stuID),借书数量=count(*) from borrow where B_time is null group by stuID题目2程序员工资表:ProWage字段名称数据类型说明IDint自动编号,主键PNameChar(10)程序员姓名Wageint工资创建一个存储过程,对程序员的工资进行分析,月薪1500到 10000不等,如果有百分之五十的人薪水不到2000 元,给所有人加薪,每次加100,再进行分析,直到有一半以上的人大于 2000元为止,存储过程执行完后,最终加了多少钱? 例如:如果有百分之五十的人薪水不到2000,给所有人加薪,每次加100 元,直到有一半 以上的人工资大于2000 元,调用存储过程测试。请编写T-SQL来实现如下功能:1) 创建存储过程,查询是否有一半程序员的工资在2200、3000、3500、4000、