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