SQL-Server實(shí)例(創(chuàng)建表-插入-查詢)

1、精品文檔 1歡迎下載 題目1 1、學(xué)校圖書館借書信息管理系統(tǒng)建立三個(gè)表: 學(xué)生信息表：stude nt 字段名稱 數(shù)據(jù)類型 說明 stuID char(10) 學(xué)生編號(hào)，主鍵 stuName Varchar(10) 學(xué)生名稱 major Varchar(50) 專業(yè) 圖書表：book 字段名稱 數(shù)據(jù)類型 說明 BID char(10) 圖書編號(hào)，主鍵 title char(50) 書名 author char(20) 作者 借書信息表：borrow 字段名稱 數(shù)據(jù)類型 說明 borrowID char(10) 借書編號(hào)，主鍵 stuID char(10) 學(xué)生編號(hào)，外鍵 BID char(10

2、) 圖書編號(hào)，外鍵 T_time datetime 借書日期 B_time datetime 還書日期 請(qǐng)編寫 SQL 語(yǔ)句完成以下的功能: 1) 查詢“計(jì)算機(jī)”專業(yè)學(xué)生在“ 2007-12-15 ”至“ 2008-1-8 ”時(shí)間段內(nèi)借書的學(xué)生編 號(hào)、學(xué)生名 稱、圖書編號(hào)、圖書名稱、借出日期；參考查詢結(jié)果如 下圖所示: 學(xué)生褊號(hào)學(xué)生褊號(hào)| | 學(xué)主名稱學(xué)主名稱| | 團(tuán)韋編號(hào)團(tuán)韋編號(hào)| | 圖書名稱圖書名稱 | |借出日期借出日期 $ 1001 林林林林 B031 人生若只如人生若只如初見初見 200712 26 00:00:00 000 1002 白楊白楊 B004 我不是數(shù)你詐我不是數(shù)你詐

3、 2007-12-22 OOtOftOQOOO 1002 白暢白暢 B003 感謝折磨你的人感謝折磨你的人 2007-12-30 OQOQOaOOO 2) 查詢所有借過圖書的學(xué)生編號(hào)、學(xué)生名稱、專業(yè)；參考查詢結(jié)果如下圖所示:精品文檔 2歡迎下載 學(xué)學(xué)生編生編號(hào)號(hào) 學(xué)生名學(xué)生名稱稱 專業(yè)專業(yè) | | :11001 林林林林 計(jì)算機(jī)計(jì)算機(jī) 1002 白暢白暢 計(jì)算機(jī)計(jì)算機(jī) 11004 北譚的北譚的雪雪 工工商著理商著理 1005 五月五月 數(shù)學(xué)數(shù)學(xué) 3）查詢借過作者為“安意如”的圖書的學(xué)生姓名、圖書名稱、借出日期、歸還日期; 參考查詢結(jié)果如下圖所示: 學(xué)生容稱學(xué)生容稱 圖書名稱圖書名稱 惜岀日期惜

4、岀日期 艇日期 |E林林林林 人主若只如初見人主若只如初見 200712-26 00:00:00.000 NULL 人生若只如初見人生若只如初見 2007-1008 00:00:00 000 2007-12-25 00:00:00.000 人生若只豹初見人生若只豹初見 2C07D9-11 OOOOCOCOO NULL 4）查詢目前借書但未歸還圖書的學(xué)生名稱及未還圖書數(shù)量；參考查詢結(jié)果如下圖所 示: 學(xué)主名稱學(xué)主名稱 借書數(shù)借書數(shù)量量 林林林林 1 白楊白楊 3 北譚的雪北譚的雪 2 五月五月 2 附加：建表語(yǔ)句精品文檔 3歡迎下載 USE master GO / ppppppppppppp 丿

5、庫(kù) pppppppppppppppppppppppp / -檢驗(yàn)數(shù)據(jù)庫(kù)是否存在，如果為真，刪除此數(shù)據(jù)庫(kù) - IF exists(SELECT * FROM sysdatabases WHERE name=BOOK) DROP DATABASE BOOK GO CREATE DATABASE BOOK GO -建數(shù)據(jù)表- USE BOOK GO CREATE TABLE stude nt - 學(xué)生信息表 ( stuID CHAR(10) primary key,- 學(xué)生編號(hào) stuName CHAR(10) NOT NULL ,- 學(xué)生名稱 major CHAR(50) NOT NULL - 專

6、業(yè) ) GO CREATE TABLE book -圖書表 ( BID CHAR(10) primary key,- 圖書編號(hào) title CHAR(50) NOT NULL,- 書名 author CHAR(20) NOT NULL,- 作者 ) GO CREATE TABLE borrow - 借書表 ( borrowID CHAR(10) primary key,- 借書編號(hào) stuID CHAR(10) foreig n key(stuID) refere nces stude nt(stulD),- BID CHAR(10) foreign key(BID) references b

7、ook(BID),- T_time datetime NOT NULL,- 借出日期 B_time datetime - 歸還日期 ) GO -學(xué)生信息表中插入數(shù)據(jù)- INSERT INTO stude nt(stulD,stuName,major)VALUES(1001, INSERT INTO stude nt(stulD,stuName,major)VALUES(1002, INSERT INTO stude nt(stulD,stuName,major)VALUES(1003, INSERT INTO stude nt(stulD,stuName,major)VALUES(1004,

8、學(xué)生編號(hào) 圖書編號(hào) 林林,計(jì)算機(jī)) 白楊,計(jì)算機(jī)) 虎子,英語(yǔ)) 北漂的雪,工商管理) 五月,數(shù)學(xué)) 精品文檔 4歡迎下載 INSERT INTO stude nt(stulD,stuName,major)VALUES(1005,精品文檔 5歡迎下載 -圖書信息表中插入數(shù)據(jù)- 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

9、,title,author)VALUES(B004, INSERT INTO book(BID,title,author)VALUES(B005, -借書信息表中插入數(shù)據(jù)- 人生若只如初見,安意如) 入學(xué)那天遇見你,晴空) 感謝折磨你的人,如娜) 我不是教你詐,劉庸) 英語(yǔ)四級(jí),白雪) INSERT borrow(borrowlD,stulD,BID,T_time,B_time)VALUES(T001,1001,B001,2007-12-26 ,n ull) INSERT borrow(borrowlD,stulD,BID,T_time,B_time)VALUES(T002,1004,B003

10、,2008-1-5, null) INSERT INTO INTO INTO borrow(borrowlD,stulD,BID,T_time,B_time)VALUES(T003,1005,B001,2007-10-8 ,2007-12-25) INSERT borrow(borrowlD,stulD,BID,T_time,B_time)VALUES(T004,1005,B002,2007-12-16 ,2008-1-7) INSERT borrow(borrowlD,stulD,BID,T_time,B_time)VALUES(T005,1002,B004,2007-12-22 ,n ul

11、l) INSERT borrow(borrowlD,stulD,BID,T_time,B_time)VALUES(T006,1005,B005,2008-1-6, null) INSERT INTO INTO INTO INTO borrow(borrowlD,stulD,BID,T_time,B_time)VALUES(T007,1002,B001,2007-9-11 ,n ull) INSERT borrow(borrowlD,stulD,BID,T_time,B_time)VALUES(T008,1005,B004,2007-12-10 ,n ull) INSERT borrow(bor

12、rowlD,stulD,BID,T_time,B_time)VALUES(T009,1004,B005,2007-10-16 ,2007-12-18) INSERT borrow(borrowlD,stulD,BID,T_time,B_time)VALUES(T010,1002,B002,2007-9-15 ,2008-1-5) INSERT borrow(borrowlD,stulD,BID,T_time,B_time)VALUES(T011,1004,B003,2007-12-28 ,n ull) INSERT borrow(borrowlD,stulD,BID,T_time,B_time

13、)VALUES(T012,1002,B003,2007-12-30 INTO INTO INTO INTO INTO ,n ull) 精品文檔 6歡迎下載 標(biāo)準(zhǔn)答案： -1)查詢“計(jì)算機(jī)”專業(yè)學(xué)生在“ 2007-12-15 ”至“ 2008-1-8 ”時(shí)間段內(nèi)借書的學(xué)生編號(hào)、 學(xué)生名稱、圖書編號(hào)、圖書名稱、借出日期 一 select 學(xué)生編號(hào)=stulD, 學(xué)生名稱=(select stuName from student where stulD=borrow.stulD), 圖書編號(hào)=BID,圖書名稱=(select title from book where BID=borrow.BID)

14、, 借出日期 =T_time from borrow where stuID in (select stuID from student where major= 計(jì)算機(jī))and T_time2007-12-15 and T_time2*(select count(*) from ProWage where Wage=PWage) update ProWage set total=total+AWage,Wage=Wage+AWage else break end print 共加薪：+co nvert(varchar,total)+ 元 print加薪后的程序員工資列表： select *

15、from ProWage -調(diào)用存儲(chǔ)過程 1- exec Sum_wage PWage=2000,AWage=100,total=0 exec Sum_wage PWage=2200,AWage=100,total=0 exec Sum_wage PWage=3000,AWage=100,total=0 exec Sum_wage PWage=4000,AWage=100,total=0 exec Sum_wage PWage=5000,AWage=100,total=0 exec Sum_wage PWage=6000,AWage=100,total=0 -2、創(chuàng)建存儲(chǔ)過程 2- if ex

16、ists (select * from sysobjects where n ame=Avg_wage) drop procedure Avg_wage GO create procedure Avg_wage PWage int, AWage int, total int as 精品文檔 9歡迎下載 while (1=1) begi n if (select Avg(Wage) from ProWage)=PWage) update ProWage set total=total+AWage,Wage=Wage+AWage else break end print 共加薪：+co nvert

17、(varchar,total)+ 元 print加薪后的程序員工資列表： select * from ProWage -調(diào)用存儲(chǔ)過程- exec Avg_wage PWage=3000,AWage=200,total=0 exec Avg wage PWage=4500,AWage=200,total=0 題目3： 學(xué)生成績(jī)信息三個(gè)表，結(jié)構(gòu)如下: 學(xué)生表：Member 字段名稱 數(shù)據(jù)類型 說明 MID Char(10) 學(xué)生號(hào)，主鍵 MName Char(50) 姓名 課程表: 字段名稱 數(shù)據(jù)類型 說明 FID Char(10) 課程，主鍵 FName Char(50) 課程名 成績(jī)表：Sco

18、re 字段名稱 數(shù)據(jù)類型 說明 SID int 自動(dòng)編號(hào)，主鍵，成績(jī)記錄號(hào) FID Char(10) 課程號(hào)，外鍵 MID Char(10) 學(xué)生號(hào)，外鍵 Score int 成績(jī) 請(qǐng)編寫 T-SQL 語(yǔ)句來(lái)實(shí)現(xiàn)如下功能： 1）查詢各個(gè)學(xué)生語(yǔ)文、數(shù)學(xué)、英語(yǔ)、歷史課程成績(jī)，例如下表: 精品文檔 10歡迎下載 姓名 語(yǔ)文 數(shù)學(xué) 英語(yǔ) 歷史 張薩 78 67 89 76 王強(qiáng) 89 67 84 96 李三 70 87 92 56 李四 80 78 97 66 2) 查詢四門課中成績(jī)低于 70 分的學(xué)生及相對(duì)應(yīng)課程名和成績(jī)。 3) 統(tǒng)計(jì)各個(gè)學(xué)生參加考試課程的平均分，且按平均分?jǐn)?shù)由高到底排序。 4) 創(chuàng)

19、建存儲(chǔ)過程，分別查詢參加 1、2、3、4 門考試及沒有參加考試的學(xué)生名單，要求顯 示姓名、學(xué)號(hào)。 建表語(yǔ)句： USE master GO / ppppppppppppp 丿庫(kù) pppppppppppppppppppppppp / -檢驗(yàn)數(shù)據(jù)庫(kù)是否存在，如果為真，刪除此數(shù)據(jù)庫(kù) - IF exists(SELECT * FROM sysdatabases WHERE name=Stude nt) DROP DATABASE Stude nt GO CREATE DATABASE Stude nt GO -建數(shù)據(jù)表- USE Stude nt GO CREATE TABLE Member -學(xué)生表

20、( MID char(10) primary key,- 學(xué)生號(hào) MName CHAR(50) NOT NULL - 姓名 ) GO CREATE TABLE F -課程表 ( FID char(10) primary key,- 課程號(hào) FName CHAR(50) NOT NULL -課程名 ) GO CREATE TABLE score -學(xué)生成績(jī)表 ( SID i nt ide ntity(1,1) primary key,- 成績(jī)記錄號(hào) FID char(10) foreig n key(FID) refere nces F(FID),- 課程號(hào) 精品文檔 11歡迎下載 MID ch

21、ar(10) foreig n key(MID) refere nces Member(MID),- Score int NOT NULL - 成績(jī) ) GO -課程表中插入數(shù)據(jù)- INSERT INTO F(FID,FName)VALUES(F001, 語(yǔ)文) INSERT INTO F(FID,FName)VALUES(F002, 數(shù)學(xué)) INSERT INTO F(FID,FName)VALUES(F003, 英語(yǔ)) INSERT INTO F(FID,FName)VALUES(F004, 歷史) -學(xué)生表中插入數(shù)據(jù)- INSERT INTO Member(MID,MName)VALUE

22、S(M001,張薩) 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,陽(yáng)陽(yáng)) INSERT INTO Member(MID,MName)VALUES(M006,虎子) INSERT INTO Member(MID,MName)VALUES(M007,夏雪) INSERT INTO Member(

23、MID,MName)VALUES(M008,璐璐) INSERT INTO Member(MID,MName)VALUES(M009,珊珊) INSERT INTO Member(MID,MName)VALUES(M010,香奈兒) -成績(jī)表中插入數(shù)據(jù)- 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 S

24、core(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)VALUES(F003,M002,84) INSERT INTO Score(FID,MID,Score)VALUES(F004,M002,96) INSERT INTO Score(FID,MID,Score)VALUES(F001,M003

25、,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)VALUES(F001,M004,80) INSERT INTO Score(FID,MID,Score)VALUES(F002,M004,78) INSERT INTO Score(FID,MID,Scor

26、e)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 INTO Score(FID,MID,Score)VALUES(F003,M006,86) INSERT INTO Score(FID,MID,Score)VALUES(F004,M006,79) INSERT INTO

27、Score(FID,MID,Score)VALUES(F002,M007,77) 學(xué)生號(hào) 精品文檔 12歡迎下載 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,Score)VALUES(F004,M009,75) INSERT INTO Score(FID,MID,Score)VALUES(F002,M009,88) 標(biāo)準(zhǔn)答案： -1)查詢各個(gè)學(xué)生語(yǔ)文、數(shù)學(xué)、英語(yǔ)、歷史課程成績(jī) - SELECT Member.MName AS 姓名, 英語(yǔ)=SUM(CASE F.FName WHEN 語(yǔ)文THEN Score.Score END), 數(shù)學(xué)=SUM(CASE F.FName WHEN