SQL-Server实例创建表-插入-查询

1、精品文档题目 11、学校图书馆借书信息管理系统建立三个表:学生信息表：stude nt字段名称数据类型说明stuIDchar(10)学生编号，主键stuNameVarchar(10)学生名称majorVarchar(50)专业图书表：book字段名称数据类型说明BIDchar(10)图书编号，主键titlechar(50)书名authorchar(20)作者借书信息表：borrow字段名称数据类型说明borrowIDchar(10)借书编号，主键stuIDchar(10)学生编号，外键BIDchar(10)图书编号，外键T_timedatetime借书日期B_timedatetime还书日期请

2、编写SQL语句完成以下的功能:1)查询“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的学生编号、学生名 称、图书编号、图书名称、借出日期；参考查询结果如下图所示:学生褊号学生褊号| 学主名称学主名称|團韦编号團韦编号| 图书名称图书名称|借出日期借出日期$ 1001林林林林B031人生若只如初见人生若只如初见200712 26 00:00:00 0001002白杨白杨B004我不是數你诈我不是數你诈2007-12-22 OOtOftOQOOO1002白畅白畅B003感谢折磨你的人感谢折磨你的人2007-12-30 OQOQOaOOO2)查询所有借过图书的学生编号

3、、学生名称、专业；参考查询结果如下图所示:1欢迎下载精品文档学学生编号生编号 学生名称学生名称 专业专业|:11001林林林林计算机计算机 1002白畅白畅计算机计算机11004北谭的雪北谭的雪 工工商着理商着理1005五月五月数学数学3）查询借过作者为“安意如”的图书的学生姓名、图书名称、借出日期、归还日期;参考查询结果如下图所示:学生容称学生容称图书名称图书名称惜岀日期惜岀日期艇日期E林林林林人主若只如初见人主若只如初见200712-26 00:00:00.000NULL人生若只如初见人生若只如初见2007-1008 00:00:00 0002007-12-25 00:00:00.000人

4、生若只豹初见人生若只豹初见2C07D9-11 OOOOCOCOONULL4）查询目前借书但未归还图书的学生名称及未还图书数量；参考查询结果如下图所示:学主名称学主名称借书数量借书数量林林林林1白杨白杨3北谭的雪北谭的雪2五月五月2附加：建表语句2欢迎下载精品文档USE masterGO/ ppppppppppppp 丿库 pppppppppppppppppppppppp /-检验数据库是否存在，如果为真，删除此数据库-IF exists(SELECT * FROM sysdatabases WHERE name=BOOK)DROP DATABASE BOOKGOCREATE DATABASE

5、BOOKGO-建数据表-USE BOOKGOCREATE TABLE stude nt -学生信息表(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) p

6、rimary key,-借书编号stuID CHAR(10) foreig n key(stuID) refere nces stude nt(stulD),- BIDCHAR(10) foreign key(BID) references book(BID),-T_time datetime NOT NULL,-借出日期B_time datetime -归还日期)GO-学生信息表中插入数据-INSERT INTO stude nt(stulD,stuName,major)VALUES(1001, INSERT INTOstude nt(stulD,stuName,major)VALUES(1

7、002, INSERT INTO student(stulD,stuName,major)VALUES(1003, INSERT INTO student(stulD,stuName,major)VALUES(1004, INSERT INTO student(stulD,stuName,major)VALUES(1005,学生编号图书编号林林,计算机)白杨,计算机)虎子,英语)北漂的雪,工商管理)五月,数学)3欢迎下载精品文档-图书信息表中插入数据-INSERT INTO book(BID,title,author)VALUES(B001,INSERT INTO book(BID,title

8、,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, -借书信息表中插入数据-人生若只如初见,安意如)入学那天遇见你,晴空)感谢折磨你的人,如娜)我不是教你诈,刘庸)英语四级,白雪)INSERT borrow(borrowlD,stulD,BID,T_time,B_time)VALUES(T001,1001,B001,2007-12-

9、26,n ull)INSERT borrow(borrowlD,stulD,BID,T_time,B_time)VALUES(T002,1004,B003,2008-1-5,null)INSERTINTOINTOINTOborrow(borrowlD,stulD,BID,T_time,B_time)VALUES(T003,1005,B001,2007-10-8 ,2007-12-25)INSERTborrow(borrowlD,stulD,BID,T_time,B_time)VALUES(T004,1005,B002,2007-12-16,2008-1-7)INSERTborrow(borro

10、wlD,stulD,BID,T_time,B_time)VALUES(T005,1002,B004,2007-12-22 ,n ull)INSERT borrow(borrowlD,stulD,BID,T_time,B_time)VALUES(T006,1005,B005,2008-1-6,null)INSERTINTOINTOINTOINTOborrow(borrowlD,stulD,BID,T_time,B_time)VALUES(T007,1002,B001,2007-9-11 ,n ull)INSERTborrow(borrowlD,stulD,BID,T_time,B_time)VA

11、LUES(T008,1005,B004,2007-12-10 ,n ull)INSERTborrow(borrowlD,stulD,BID,T_time,B_time)VALUES(T009,1004,B005,2007-10-16,2007-12-18)INSERTborrow(borrowlD,stulD,BID,T_time,B_time)VALUES(T010,1002,B002,2007-9-15 ,2008-1-5)INSERTborrow(borrowlD,stulD,BID,T_time,B_time)VALUES(T011,1004,B003,2007-12-28 ,n ul

12、l)INSERTborrow(borrowlD,stulD,BID,T_time,B_time)VALUES(T012,1002,B003,2007-12-30INTOINTOINTOINTOINTO,null)4欢迎下载标准答案：-1)查询“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的学生编号、 学生名称、图书编号、图书名称、借出日期一select学生编号=stulD,学生名称=(select stuName from student where stulD=borrow.stulD),图书编号=BID,图书名称=(select title from boo

13、k where BID=borrow.BID),借出日期=T_time from borrow where stuID in(select stuID fromstudent where major=计算机)and T_time2007-12-15 and T_time2*(selectcount(*)from ProWage whereWage=PWage)update ProWage set total=total+AWage,Wage=Wage+AWageelsebreakendprint共加薪：+co nvert(varchar,total)+元print加薪后的程序员工资列表：sel

14、ect * from ProWage-调用存储过程1-exec Sum_wage PWage=2000,AWage=100,total=0exec Sum_wage PWage=2200,AWage=100,total=0exec Sum_wage PWage=3000,AWage=100,total=0exec Sum_wage PWage=4000,AWage=100,total=0exec Sum_wage PWage=5000,AWage=100,total=0exec Sum_wage PWage=6000,AWage=100,total=0-2、创建存储过程2-if exists

15、(select * from sysobjects where n ame=Avg_wage)drop procedure Avg_wageGOcreate procedure Avg_wagePWage int,AWage int,total intaswhile (1=1)begi n7欢迎下载精品文档if (select Avg(Wage) from ProWage)=PWage)update ProWage set total=total+AWage,Wage=Wage+AWage elsebreakendprint共加薪：+co nvert(varchar,total)+元print

16、加薪后的程序员工资列表：select * from ProWage-调用存储过程-exec Avg_wage PWage=3000,AWage=200,total=0exec Avg wage PWage=4500,AWage=200,total=0题目 3：学生成绩信息三个表，结构如下:学生表：Member字段名称数据类型说明MIDChar(10)学生号，主键MNameChar(50)姓名课程表:字段名称数据类型说明FIDChar(10)课程，主键FNameChar(50)课程名成绩表：Score字段名称数据类型说明SIDint自动编号，主键，成绩记录号FIDChar(10)课程号，外键MI

17、DChar(10)学生号，外键Scoreint成绩请编写T-SQL语句来实现如下功能：1）查询各个学生语文、数学、英语、历史课程成绩，例如下表:8欢迎下载精品文档姓名语文数学英语历史张萨78678976王强89678496李三70879256李四807897662)查询四门课中成绩低于70分的学生及相对应课程名和成绩。3)统计各个学生参加考试课程的平均分，且按平均分数由高到底排序。4)创建存储过程，分别查询参加1、2、3、4门考试及没有参加考试的学生名单，要求显示姓名、学号。建表语句：USE masterGO/ ppppppppppppp丿库 pppppppppppppppppppppppp

18、/-检验数据库是否存在，如果为真，删除此数据库-IF exists(SELECT * FROM sysdatabases WHERE name=Stude nt)DROP DATABASE Stude ntGOCREATE DATABASE Stude ntGO-建数据表-USE Stude ntGOCREATE TABLE Member -学生表(MID char(10) primary key,-学生号MName CHAR(50) NOT NULL -姓名)GOCREATE TABLE F -课程表(FID char(10) primary key,-课程号FName CHAR(50) N

19、OT NULL -课程名)GOCREATE TABLE score -学生成绩表(SID i nt ide ntity(1,1) primary key,-成绩记录号FID char(10) foreig n key(FID) refere nces F(FID),-课程号9欢迎下载精品文档MID char(10) foreig n key(MID) refere nces Member(MID),-Score int NOT NULL -成绩)GO-课程表中插入数据-INSERT INTO F(FID,FName)VALUES(F001,语文)INSERT INTO F(FID,FName)

20、VALUES(F002,数学)INSERT INTO F(FID,FName)VALUES(F003,英语)INSERT INTO F(FID,FName)VALUES(F004,历史)-学生表中插入数据-INSERT INTO Member(MID,MName)VALUES(M001,张萨)INSERT INTO Member(MID,MName)VALUES(M002,王强)INSERT INTO Member(MID,MName)VALUES(M003,李三)INSERT INTO Member(MID,MName)VALUES(M004,李四)INSERT INTO Member(MI

21、D,MName)VALUES(M005,阳阳)INSERT INTO Member(MID,MName)VALUES(M006,虎子)INSERT INTO Member(MID,MName)VALUES(M007,夏雪)INSERT INTO Member(MID,MName)VALUES(M008,璐璐)INSERT INTO Member(MID,MName)VALUES(M009,珊珊)INSERT INTO Member(MID,MName)VALUES(M010,香奈儿) -成绩表中插入数据-INSERT INTO Score(FID,MID,Score)VALUES(F001,M

22、001,78)INSERT INTO Score(FID,MID,Score)VALUES(F002,M001,67)INSERT INTO Score(FID,MID,Score)VALUES(F003,M001,89)INSERT INTO Score(FID,MID,Score)VALUES(F004,M001,76)INSERT INTO Score(FID,MID,Score)VALUES(F001,M002,89)INSERT INTO Score(FID,MID,Score)VALUES(F002,M002,67)INSERT INTO Score(FID,MID,Score)V

23、ALUES(F003,M002,84)INSERT INTO Score(FID,MID,Score)VALUES(F004,M002,96)INSERT INTO Score(FID,MID,Score)VALUES(F001,M003,70)INSERT INTO Score(FID,MID,Score)VALUES(F002,M003,87)INSERT INTO Score(FID,MID,Score)VALUES(F003,M003,92)INSERT INTO Score(FID,MID,Score)VALUES(F004,M003,56)INSERT INTO Score(FID

24、,MID,Score)VALUES(F001,M004,80)INSERT INTO Score(FID,MID,Score)VALUES(F002,M004,78)INSERT INTO Score(FID,MID,Score)VALUES(F003,M004,97)INSERT INTO Score(FID,MID,Score)VALUES(F004,M004,66)INSERT INTO Score(FID,MID,Score)VALUES(F001,M006,88)INSERT INTO Score(FID,MID,Score)VALUES(F002,M006,55)INSERT IN

25、TO Score(FID,MID,Score)VALUES(F003,M006,86)INSERT INTO Score(FID,MID,Score)VALUES(F004,M006,79)INSERT INTO Score(FID,MID,Score)VALUES(F002,M007,77)学生号INSERT INTO Score(FID,MID,Score)VALUES(F003,M008,65)INSERT INTO Score(FID,MID,Score)VALUES(F004,M007,48)10欢迎下载精品文档INSERT INTO Score(FID,MID,Score)VALUES(F004,M009,75)INSERT INTO Score(FID,MID,Score)VALUES(F002,M009,88)标准答案：-1)查询各个学生语文、数学、英语、历史课程成绩-SELECT Member.MName AS姓名,英语=SUM(CASE F.FName WHEN 语文THEN Score.Score END),数学=SUM(CASE F.FName WHEN 数学THENScore.Score