|
南陽(yáng)師范學(xué)院2013-2014（1）數(shù)據(jù)庫(kù)課程 教育技術(shù)學(xué)12.4班 SQL語(yǔ)句競(jìng)賽 （提示：所有的操作均用SQL語(yǔ)句完成。） 有某個(gè)學(xué)生運(yùn)動(dòng)會(huì)比賽信息的數(shù)據(jù)庫(kù)，保存了如下的表： 運(yùn)動(dòng)員（運(yùn)動(dòng)員編號(hào)，運(yùn)動(dòng)員姓名，運(yùn)動(dòng)員性別，所屬系名） 項(xiàng)目（項(xiàng)目編號(hào)，項(xiàng)目名稱(chēng)，項(xiàng)目比賽地點(diǎn)） 成績(jī)（運(yùn)動(dòng)員編號(hào)，項(xiàng)目編號(hào)，積分） 請(qǐng)用SQL語(yǔ)句完成如下功能： 1． 建立數(shù)據(jù)庫(kù)，數(shù)據(jù)庫(kù)名稱(chēng)為y_x_c（4分） Create database y_x_c 2. 建立數(shù)據(jù)表，并滿(mǎn)足如下條件：（30分） （1）定義各個(gè)表的主碼、外碼約束 （2）運(yùn)動(dòng)員的姓名和所屬系別不能為空值。 （3）積分要么為空值，要么為6,4,2,0,分別代表第一，二，三名和其他名次的積分。 Create table 運(yùn)動(dòng)員( 運(yùn)動(dòng)員編號(hào) char(8) primary key, 運(yùn)動(dòng)員姓名 char(20) not null, 運(yùn)動(dòng)員性別 check(運(yùn)動(dòng)員姓名 in('男','女'), 所屬系名 varchar(100) not null ) Create table 項(xiàng)目( 項(xiàng)目編號(hào) char(6) primary key, 項(xiàng)目名稱(chēng) varchar(50), 項(xiàng)目比賽地點(diǎn) varchar(50)) Create table 成績(jī)( 運(yùn)動(dòng)員編號(hào) char(8) foreign key references 運(yùn)動(dòng)員(運(yùn)動(dòng)員編號(hào)), 項(xiàng)目編號(hào) char(6) foreign key references 項(xiàng)目(項(xiàng)目編號(hào)), 積分 tinyint check(積分 in(6,4,2,0)) --注釋?zhuān)簺](méi)有寫(xiě)not null 就是允許空 ) 3．往表中插入數(shù)據(jù)：(提示: 每個(gè)表只需要書(shū)寫(xiě)插入前面兩行數(shù)據(jù)的命令即可)（6分） 運(yùn)動(dòng)員（1001，李明，男，計(jì)算機(jī)系 1002，張三，男，數(shù)學(xué)系 1003，李四，男，計(jì)算機(jī)系 1004，王二，男，物理系 1005，李娜，女，心理系 1006，孫麗，女，數(shù)學(xué)系） 項(xiàng)目 （ x001，男子五千米，1操場(chǎng) x002，男子標(biāo)槍?zhuān)?SPAN lang=EN-US>1操場(chǎng) x003，男子跳遠(yuǎn)，2操場(chǎng) x004，女子跳高，2操場(chǎng) x005，女子三千米，3操場(chǎng)） 成績(jī)（ 1001，x001，6 1002，x001，4 1003，x001，2 1004，x001，0 1001，x003，4 1002，x003，6 1004，x003，2 1005，x004，6 1006，x004，4） 這部分比較簡(jiǎn)單，省略答案 4．使用完成如下查詢(xún) （1）找出參加了張三所參加的所有項(xiàng)目的其他同學(xué)的姓名。（10分） select 運(yùn)動(dòng)員姓名 from 運(yùn)動(dòng)員 where 運(yùn)動(dòng)員姓名!='張三' and 運(yùn)動(dòng)員編號(hào) in(select distinct 運(yùn)動(dòng)員編號(hào) from 成績(jī) where 項(xiàng)目編號(hào) in(select 項(xiàng)目.項(xiàng)目編號(hào) from dbo.成績(jī),dbo.項(xiàng)目,dbo.運(yùn)動(dòng)員 where dbo.成績(jī).運(yùn)動(dòng)員編號(hào)=dbo.運(yùn)動(dòng)員.運(yùn)動(dòng)員編號(hào) and dbo.成績(jī).項(xiàng)目編號(hào)=dbo.項(xiàng)目.項(xiàng)目編號(hào) and 運(yùn)動(dòng)員編號(hào)=(select 運(yùn)動(dòng)員編號(hào) from 運(yùn)動(dòng)員 where 運(yùn)動(dòng)員姓名='張三'))) （2）求出目前總積分最高的系名，及其積分。（10分） select 所屬系名,sum(積分) from 運(yùn)動(dòng)員,成績(jī) where 運(yùn)動(dòng)員.運(yùn)動(dòng)員編號(hào)=成績(jī).運(yùn)動(dòng)員編號(hào) group by 所屬系名 having sum(積分)>=all --注釋?zhuān)?和any不同，>=all等同于大于等于子查詢(xún)中的最大值 (select sum(積分) from 運(yùn)動(dòng)員,成績(jī) where 運(yùn)動(dòng)員.運(yùn)動(dòng)員編號(hào)=成績(jī).運(yùn)動(dòng)員編號(hào) group by 所屬系名) （3）找出在1操場(chǎng)進(jìn)行比賽的各項(xiàng)目名稱(chēng)及其冠軍的姓名。（10分） select 項(xiàng)目名稱(chēng),運(yùn)動(dòng)員姓名 from 運(yùn)動(dòng)員,成績(jī),項(xiàng)目 where 運(yùn)動(dòng)員.運(yùn)動(dòng)員編號(hào)=成績(jī).運(yùn)動(dòng)員編號(hào) and 項(xiàng)目.項(xiàng)目編號(hào)=成績(jī).項(xiàng)目編號(hào) and項(xiàng)目比賽地點(diǎn)='1操場(chǎng)'and 積分>= (select max(積分) from 成績(jī),項(xiàng)目 where 項(xiàng)目比賽地點(diǎn)='1操場(chǎng)' and 成績(jī).項(xiàng)目編號(hào)=項(xiàng)目.項(xiàng)目編號(hào)) （4）找出每個(gè)項(xiàng)目的最高分及對(duì)應(yīng)的項(xiàng)目編號(hào)。（5分） Select項(xiàng)目編號(hào),max(積分) From 項(xiàng)目 Group by 項(xiàng)目編號(hào) 5．建立視圖xm_ydy_yx，查詢(xún)每個(gè)項(xiàng)目的項(xiàng)目名稱(chēng)、運(yùn)動(dòng)員姓名和所屬系名。（10分） create view xm_ydy_yx as select 項(xiàng)目名稱(chēng),運(yùn)動(dòng)員姓名,所屬系名 from 項(xiàng)目,運(yùn)動(dòng)員,成績(jī) where 項(xiàng)目.項(xiàng)目編號(hào)=成績(jī).項(xiàng)目編號(hào) and 運(yùn)動(dòng)員.運(yùn)動(dòng)員編號(hào)=成績(jī).運(yùn)動(dòng)員編號(hào) 6．經(jīng)查張三因?yàn)槭褂昧诉`禁藥品，其成績(jī)都記0分，請(qǐng)?jiān)跀?shù)據(jù)庫(kù)中做出相應(yīng)修改。（10分） Update 成績(jī) Set 積分=0 Where 運(yùn)動(dòng)員編號(hào)=( Select 運(yùn)動(dòng)員編號(hào) from 運(yùn)動(dòng)員 where 運(yùn)動(dòng)員姓名='張三') 7．經(jīng)組委會(huì)協(xié)商，需要?jiǎng)h除女子跳高比賽項(xiàng)目。（5分） 這道題看似只需要?jiǎng)h除項(xiàng)目中的數(shù)據(jù)，實(shí)際上并沒(méi)有這么簡(jiǎn)單。因?yàn)轫?xiàng)目表作為主表對(duì)外表成績(jī)表有影響，所以要想刪除女子跳高必須先將參加女子跳高的信息都刪除。 Delete from 成績(jī) Where 項(xiàng)目編號(hào)=(select 項(xiàng)目編號(hào) from 項(xiàng)目 where 項(xiàng)目名稱(chēng)='女子跳高') 然后刪除項(xiàng)目表中的數(shù)據(jù)： Delete from 項(xiàng)目 Where項(xiàng)目名稱(chēng)='女子跳高' 8．將男子標(biāo)槍項(xiàng)目調(diào)到2操場(chǎng)進(jìn)行比賽。 Update 項(xiàng)目 Set項(xiàng)目比賽地點(diǎn)='2操場(chǎng)' Where 項(xiàng)目名稱(chēng)='男子標(biāo)槍' |
|
|
