實例創(chuàng)建表插入查詢

1、題目11、學(xué)校圖書館借書信息管理系統(tǒng)建立三個表：學(xué)生信息表：student字段名稱數(shù)據(jù)類型說明stuIDchar(10)學(xué)生編號，主鍵stuNameVarchar(10)學(xué)生名稱majorVarchar(50)專業(yè)圖書表：book字段名稱數(shù)據(jù)類型說明BIDchar(10)圖書編號，主鍵titlechar(50)書名authorchar(20)作者借書信息表：borrow字段名稱數(shù)據(jù)類型說明borrowIDchar(10)借書編號，主鍵stuIDchar(10)學(xué)生編號，外鍵BIDchar(10)圖書編號，外鍵T_timedatetime借書日期B_timedatetime還書日期請編寫SQL語

2、句完成以下的功能：1) 查詢“計算機(jī)”專業(yè)學(xué)生在“2007-12-15”至“2008-1-8”時間段內(nèi)借書的學(xué)生編號、學(xué)生名稱、圖書編號、圖書名稱、借出日期；參考查詢結(jié)果如下圖所示：2) 查詢所有借過圖書的學(xué)生編號、學(xué)生名稱、專業(yè)；參考查詢結(jié)果如下圖所示：3) 查詢借過作者為“安意如”的圖書的學(xué)生姓名、圖書名稱、借出日期、歸還日期；參考查詢結(jié)果如下圖所示：4) 查詢目前借書但未歸還圖書的學(xué)生名稱及未還圖書數(shù)量；參考查詢結(jié)果如下圖所示：附加：建表語句:USE masterGO/*$建庫$*/-檢驗數(shù)據(jù)庫是否存在，如果為真，刪除此數(shù)據(jù)庫-IF exists(SELECT * FROM sysdat

3、abases WHERE name='BOOK') DROP DATABASE BOOKGOCREATE DATABASE BOOKGO-建數(shù)據(jù)表-USE BOOKGOCREATE TABLE student -學(xué)生信息表( stuID CHAR(10) primary key, -學(xué)生編號 stuName CHAR(10) NOT NULL , -學(xué)生名稱 major CHAR(50) NOT NULL -專業(yè))GOCREATE TABLE book -圖書表( BID CHAR(10) primary key, -圖書編號 title CHAR(50) NOT NULL,

4、-書名 author CHAR(20) NOT NULL, -作者)GOCREATE TABLE borrow -借書表( borrowID CHAR(10) primary key, -借書編號stuID CHAR(10) foreign key(stuID) references student(stuID), -學(xué)生編號BID CHAR(10) foreign key(BID) references book(BID),-圖書編號 T_time datetime NOT NULL, -借出日期 B_time datetime -歸還日期)GO-學(xué)生信息表中插入數(shù)據(jù)-INSERT INTO

5、 student(stuID,stuName,major)VALUES('1001','林林','計算機(jī)')INSERT INTO student(stuID,stuName,major)VALUES('1002','白楊','計算機(jī)')INSERT INTO student(stuID,stuName,major)VALUES('1003','虎子','英語')INSERT INTO student(stuID,stuName,major)VALUE

6、S('1004','北漂的雪','工商管理')INSERT INTO student(stuID,stuName,major)VALUES('1005','五月','數(shù)學(xué)')-圖書信息表中插入數(shù)據(jù)-INSERT INTO book(BID,title,author)VALUES('B001','人生若只如初見','安意如')INSERT INTO book(BID,title,author)VALUES('B002','入學(xué)那天

7、遇見你','晴空')INSERT INTO book(BID,title,author)VALUES('B003','感謝折磨你的人','如娜')INSERT INTO book(BID,title,author)VALUES('B004','我不是教你詐','劉庸')INSERT INTO book(BID,title,author)VALUES('B005','英語四級','白雪')-借書信息表中插入數(shù)據(jù)-INSERT IN

8、TO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T001','1001','B001','2007-12-26',null)INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T002','1004','B003','2008-1-5',null)INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time

9、)VALUES('T003','1005','B001','2007-10-8','2007-12-25')INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T004','1005','B002','2007-12-16','2008-1-7')INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES(

10、9;T005','1002','B004','2007-12-22',null)INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T006','1005','B005','2008-1-6',null)INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T007','1002','B001',

11、'2007-9-11',null)INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T008','1005','B004','2007-12-10',null)INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES('T009','1004','B005','2007-10-16','2007-12-18'

12、)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(borrowI

13、D,stuID,BID,T_time,B_time)VALUES('T012','1002','B003','2007-12-30',null)標(biāo)準(zhǔn)答案:- 1)查詢“計算機(jī)”專業(yè)學(xué)生在“2007-12-15”至“2008-1-8”時間段內(nèi)借書的學(xué)生編號、學(xué)生名稱、圖書編號、圖書名稱、借出日期select 學(xué)生編號=stuID,學(xué)生名稱=(select stuName from student where stuID=borrow.stuID),圖書編號=BID,圖書名稱=(select title from book where

14、 BID=borrow.BID),借出日期=T_time from borrow where stuID in (select stuID from student where major='計算機(jī)') and T_time>'2007-12-15' and T_time<'2008-1-8'- 2)查詢所有借過圖書的學(xué)生編號、學(xué)生名稱、專業(yè)-select 學(xué)生編號=stuID,學(xué)生名稱=stuName,專業(yè)=major from student where stuID in (select stuID from borrow)- 3

15、)查詢借過作者為“安意如”的圖書的學(xué)生姓名、圖書名稱、借出日期-select 學(xué)生名稱=(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)查詢目前借書但未歸還圖書的學(xué)生名稱及未還圖書數(shù)量-select 學(xué)生名稱=(select stu

16、Name from student where stuID=borrow.stuID),借書數(shù)量=count(*) from borrow where B_time is null group by stuID 題目2程序員工資表：ProWage字段名稱數(shù)據(jù)類型說明IDint自動編號，主鍵PNameChar(10)程序員姓名Wageint工資創(chuàng)建一個存儲過程，對程序員的工資進(jìn)行分析，月薪1500到10000不等，如果有百分之五十的人薪水不到2000元，給所有人加薪，每次加100，再進(jìn)行分析，直到有一半以上的人大于2000元為止，存儲過程執(zhí)行完后，最終加了多少錢？ 例如：如果有百分之五十的人薪水

17、不到2000，給所有人加薪，每次加100元，直到有一半以上的人工資大于2000元，調(diào)用存儲過程后的結(jié)果如圖：請編寫T-SQL來實現(xiàn)如下功能：1) 創(chuàng)建存儲過程，查詢是否有一半程序員的工資在2200、3000、3500、4000、5000或6000元之上，如果不到分別每次給每個程序員加薪100元，至之一半程序員的工資達(dá)到2200，3000，3500，4000，5000或6000元。2) 創(chuàng)建存儲過程，查詢程序員平均工資在4500元，如果不到則每個程序員每次加200元，至到所有程序員平均工資達(dá)到4500元。建表語句USE masterGO/*$建庫$*/-檢驗數(shù)據(jù)庫是否存在，如果為真，刪除此數(shù)據(jù)庫

18、-IF exists(SELECT * FROM sysdatabases WHERE name='Wage') DROP DATABASE WageGOCREATE DATABASE WageGO-建數(shù)據(jù)表-USE WageGOCREATE TABLE ProWage -程序員工資表( ID int identity(1,1) primary key, -工資編號 PName CHAR(10) NOT NULL , -程序員姓名 Wage int NOT NULL -工資)GO-插入數(shù)據(jù)-INSERT INTO ProWage(PName,Wage)VALUES('青

19、鳥',1900)INSERT INTO ProWage(PName,Wage)VALUES('張三',1200)INSERT INTO ProWage(PName,Wage)VALUES('李四',1800)INSERT INTO ProWage(PName,Wage)VALUES('二月',3500)INSERT INTO ProWage(PName,Wage)VALUES('藍(lán)天',2780)標(biāo)準(zhǔn)答案：-1、創(chuàng)建存儲過程-if exists (select * from sysobjects where name=&#

20、39;Sum_wage')drop procedure Sum_wageGOcreate procedure Sum_wage PWage int,AWage int,total intas while (1=1)beginif (select count(*) from ProWage)>2*(select count(*) from ProWage where Wage>=PWage)update ProWage set total=total+AWage,Wage=Wage+AWageelsebreakendprint'一共加薪：'+convert(v

21、archar,total)+'元'print'加薪后的程序員工資列表：'select * from ProWage-調(diào)用存儲過程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

22、_wage PWage=6000,AWage=100,total=0-2、創(chuàng)建存儲過程2-if exists (select * from sysobjects where name='Avg_wage')drop procedure Avg_wageGOcreate procedure Avg_wage PWage int,AWage int,total intas while (1=1)beginif (select Avg(Wage) from ProWage)<=PWage)update ProWage set total=total+AWage,Wage=Wag

23、e+AWageelsebreakendprint'一共加薪：'+convert(varchar,total)+'元'print'加薪后的程序員工資列表：'select * from ProWage-調(diào)用存儲過程-exec Avg_wage PWage=3000,AWage=200,total=0exec Avg_wage PWage=4500,AWage=200,total=0題目3：學(xué)生成績信息三個表，結(jié)構(gòu)如下： 學(xué)生表：Member字段名稱數(shù)據(jù)類型說明MIDChar(10)學(xué)生號，主鍵MNameChar(50)姓名課程表：字段名稱數(shù)據(jù)類型說明

24、FIDChar(10)課程，主鍵FNameChar(50)課程名成績表：Score字段名稱數(shù)據(jù)類型說明SIDint自動編號，主鍵，成績記錄號FIDChar(10)課程號，外鍵MIDChar(10)學(xué)生號，外鍵Scoreint成績請編寫T-SQL語句來實現(xiàn)如下功能：1) 查詢各個學(xué)生語文、數(shù)學(xué)、英語、歷史課程成績，例如下表：姓名語文數(shù)學(xué)英語歷史張薩78 678976王強(qiáng) 89 67 84 96李三70 87 92 56李四 8078 97662) 查詢四門課中成績低于70分的學(xué)生及相對應(yīng)課程名和成績。3) 統(tǒng)計各個學(xué)生參加考試課程的平均分，且按平均分?jǐn)?shù)由高到底排序。4) 創(chuàng)建存儲過程，分別查詢參

25、加1、2、3、4門考試及沒有參加考試的學(xué)生名單，要求顯示姓名、學(xué)號。建表語句:USE masterGO/*$建庫$*/-檢驗數(shù)據(jù)庫是否存在，如果為真，刪除此數(shù)據(jù)庫-IF exists(SELECT * FROM sysdatabases WHERE name='Student') DROP DATABASE StudentGOCREATE DATABASE StudentGO-建數(shù)據(jù)表-USE StudentGOCREATE TABLE Member -學(xué)生表( MID char(10) primary key, -學(xué)生號 MName CHAR(50) NOT NULL -姓名

26、)GOCREATE TABLE F -課程表( FID char(10) primary key, -課程號FName CHAR(50) NOT NULL -課程名)GOCREATE TABLE score -學(xué)生成績表( SID int identity(1,1) primary key, -成績記錄號 FID char(10) foreign key(FID) references F(FID) , -課程號 MID char(10) foreign key(MID) references Member(MID) , -學(xué)生號 Score int NOT NULL -成績)GO-課程表中插

27、入數(shù)據(jù)-INSERT INTO F(FID,FName)VALUES('F001','語文')INSERT INTO F(FID,FName)VALUES('F002','數(shù)學(xué)')INSERT INTO F(FID,FName)VALUES('F003','英語')INSERT INTO F(FID,FName)VALUES('F004','歷史')-學(xué)生表中插入數(shù)據(jù)-INSERT INTO Member(MID,MName)VALUES('M001'

28、,'張薩')INSERT INTO Member(MID,MName)VALUES('M002','王強(qiáng)')INSERT INTO Member(MID,MName)VALUES('M003','李三')INSERT INTO Member(MID,MName)VALUES('M004','李四')INSERT INTO Member(MID,MName)VALUES('M005','陽陽')INSERT INTO Member(MID,MName)V

29、ALUES('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','香奈兒')-成績表中插入數(shù)據(jù)

30、-INSERT INTO Score(FID,MID,Score)VALUES('F001','M001',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

31、 Score(FID,MID,Score)VALUES('F001','M002',89)INSERT INTO Score(FID,MID,Score)VALUES('F002','M002',67)INSERT INTO Score(FID,MID,Score)VALUES('F003','M002',84)INSERT INTO Score(FID,MID,Score)VALUES('F004','M002',96)INSERT INTO Score(FID,M

32、ID,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,MID,Score)VAL

33、UES('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('F00

34、1','M006',88)INSERT INTO Score(FID,MID,Score)VALUES('F002','M006',55)INSERT INTO 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)INSERT INTO Score(FID,MID,Scor